I’ve spent over half a decade working with the Big Data Technology stack and consulting with clients across various domains. One thing I have noticed is how frequently Hive is used as a warehousing solution across business domains.
You simply can’t ignore Apache Hive when you are learning Apache Hadoop.
Hive is a part of the large Hadoop Ecosystem that lets you provide a schema to large data residing in HDFS. Most of you will be aware of RDBMS and its tables. We use them so often that it has become a part of our lives now. And here’s the thing – tables in Hive are no different.
Have you ever wondered what might be the different types of tables in Hive? That’s what we’ll discuss in this article!
Apache Hive is a data warehouse system for Apache Hadoop. It provides SQL-like access for data in HDFS so that Hadoop can be used as a warehouse structure. Hive allows you to provide structure on largely unstructured data. After you define the structure, you can use Hive to query the data without knowledge of Java or Map Reduce.
The Hive Query Language (HQL) has similar semantics and functions as standard SQL in the relational database so that experienced database analysts can easily access the data.
Apache Hive provides the following features:
Here are the types of tables in Apache Hive:
In a managed table, both the table data and the table schema are managed by Hive. The data will be located in a folder named after the table within the Hive data warehouse, which is essentially just a file location in HDFS.
The location is user-configurable when Hive is installed. By managed or controlled we mean that if you drop (delete) a managed table, then Hive will delete both the Schema (the description of the table) and the data files associated with the table. Default location is /user/hive/warehouse).
Syntax to Create Managed Table
CREATE TABLE IF NOT EXISTS stocks (exchange STRING, symbol STRING, price_open FLOAT, price_high FLOAT, price_low FLOAT, price_adj_close FLOAT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
As for managed tables, you can also copy the schema (but not the data) of an existing table:
CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3 LIKE mydb.employees LOCATION '/path/to/data';
External Tables
An external table is one where only the table schema is controlled by Hive. In most cases, the user will set up the folder location within HDFS and copy the data file(s) there. This location is included as part of the table definition statement. When an external table is deleted, Hive will only delete the schema associated with the table. The data files are not affected.
Syntax to Create External Table
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (exchange STRING, symbol STRING, price_open FLOAT, price_high FLOAT, price_low FLOAT, price_adj_close FLOAT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/data/stocks';
| Managed Table | External Table |
| Hive assumes that it owns the data for managed tables. | For external tables, Hive assumes that it does not manage the data. |
| If a managed table or partition is dropped, the data and metadata associated with that table or partition are deleted. | Dropping the table does not delete the data, although the metadata for the table will be deleted. |
| For Managed tables, Hive stores data into its warehouse directory | For External Tables, Hive stores the data in the LOCATION specified during creation of the table(generally not in warehouse directory) |
| Managed table provides ACID/transnational action support. | External Table does not provide ACID/transactional action support. |
| Statements: ARCHIVE, UNARCHIVE, TRUNCATE, MERGE, CONCATENATE supported | Not supported. |
| Query Result Caching supported(saves the results of an executed Hive query for reuse ) | Not Supported |
You can tell whether or not a table is managed or external using the output of DESCRIBE EXTENDED table name.
Near the end of the Detailed Table Information output, you will see the following for managed tables:
... tableType: MANAGED_TABLE)
For external tables, you will see the following:
... tableType: EXTERNAL_TABLE)
Note: If you omit the EXTERNAL keyword and the original table is external, the new table will also be external. If you omit EXTERNAL and the original table is managed, the new table will also be managed. However, if you include the EXTERNAL keyword and the original table is managed, the new table will be external. Even in this scenario, the LOCATION clause will still be optional.
In this article, we learned about Apache Hive and its table types. Hopefully, you might have got a good overview of the types of tables in Hive. The differences stated are not exhaustive. Please feel free to add more in the comment section below.
The following are some additional Data Engineering resources that I recommend you explore-
I hope you might have liked the article. If you have any questions related to this article do let me know in the comments section below.
https://cwiki.apache.org/confluence/display/Hive/Home
Lorem ipsum dolor sit amet, consectetur adipiscing elit,
very good explanation, tq.