Hive Create Table Examples

Our HadoopTpoint App is now available in google play store,please rate and comment it in play store : W3Schools

We can create table in hive in two different approaches,one approach is hive external table and another approach is hive managed table.In this post we explained the detailed information about Hive create table and real time examples of hive create tables. Here is the two types of hive table .

i) Hive External Table

ii) Hive Managed Table

Create External Table Hive

Generally Hive Create Table came from SQL and If you add the option IF NOT EXISTS, Hive will silently ignore the statement if the table already exists. This is useful in scripts that should create a table the first time they run.In the above table EXTERNAL keyword indicates that table is a hive external table and the LOCATION keyword indicates the path of hive external table location.

Load Data From HDFS

In hive external table we can only load data from hdfs only.First of all you have to create a proper data file to load hive insert into table . Here is the hive insert into table example .

 

When hive load data from hdfs  then automatically the data file which in the path of  ‘/companydata/company.txt’ moved into specified location,If you not set the location keyword in your hive external table then external table data stored in /user/hive/warehouse/company

If you mentioned specific location for your table data then it will stored in the path of

hadoop fs -ls /hive_external_table_company

hive create table

hive create table examples

Create Managed Table in Hive

Hive managed tables are also called as hive internal tables.In Managed Table also we can use LOCATION keyword to specify the storage path of managed table.The default path location of Hive managed and external table is

hadoop fs -ls /user/hive/warehouse/your_table_name

Hive stores the data for these tables in a subdirectory under the directory defined by hive.metastore.warehouse.dir (e.g., /user/hive/warehouse), by default.

Load data Local inpath

The storage path of this above managed table is

hadoop fs -ls /user/hive/warehouse/empolyeetab

 

Hive Managed Table Load Data From HDFS

Hive warehouse path
————–
hadoop fs -ls /user/hive/warehouse/empolyeetab

hadoop fs -cat /user/hive/warehouse/empolyeetab/part-m-00000

In this post we clearly discussed about Hive create table types and that hive create table syntax

Also Read Hive Data Types with Examples

Hive Create Table Differences between Managed table and External Table

  • A Hive table that’s not external is called a managed table.
  • One of the main differences between an external and a managed table in Hive is that when an external table is dropped, the data associated with it doesn’t get deleted, only the metadata (number of columns, type of columns, terminators,etc.) gets dropped from the Hive metastore. When a managed table gets dropped, both the metadata and data get dropped.
  • I have so far always preferred making tables external because if the schema of my Hive table changes,
    I can just drop the external table and re-create another external table over the same HDFS data with the new schema.
  • However, most (if not all) of the changes to schema can now be made through ALTER TABLE or similar commands so my recommendation/preference to use external tables over managed ones might be more of a legacy concern than a contemporary one.

This is the main basics of Hive Create table examples.If you have any queries or want to add your tip, let me know via comments.

Comments

  1. Good one, nice examples to differentiate managed and external tables.

  2. Hi Mahesh,
    Is it possible to create partition on Hive external table?

    Regards,
    Nilesh

  3. Nice explanation about Hive create tables and its examples

  4. Good explanation in managed and external table differences.

  5. Thanks you Mahesh…Good one from beginners…
    Have a question: If an external table is dropped, its metadata in meta store is only dropped not data. So if I query a hive external table that is dropped what would I get.

Speak Your Mind

*