Hi, everyone. Thanks for joining me today for this tutorial.

In the last tutorial, we saw how to create a hive table using the SELECT query. In this tutorial, we are going to see how to create a hive table which stores the data in the ORC File Format.

The process of creating this table is similar to the internal table creation process in this tutorial with only one change in the InputFileFormat of the hive table.

Let us get started then.

The process is shown in the following infographics.

Apache Hive: ORC File Format table
Apache Hive: ORC File Format table

As can be seen from the above screenshot, it is a 4-step process.

Let us start the process with executing each step one by one.

  • CHECK IF THE HIVE TABLE NAME ALREADY EXISTS

We have done for almost all the tutorials for Apache Hive.

show tables;

The output of the above command looks as follows.

Step 1: Checking the pre-existence of the hive table with ORC format
Step 1: Checking the pre-existence of the hive table with ORC format

As you can see from the above screenshot, the table with name post34 does not exist. It means that we can go ahead and create this table with name post34.

  • CREATING AND EXECUTING HIVE INTERNAL TABLE WITH ORC FILE FORMAT

The command to create this hive internal table is similar to this tutorial. Therefore, if you want a detailed explanation about schema and the design process, you can refer to this tutorial.

I have uploaded the SQL file to create this hive table in my GitHub profile under HDPCD repository with name “45_hive_table_with_ORC.sql“. You can download this file by clicking here and it looks as follows.

The only different thing that you can see is the line number 8: “stored as orc;” which indicates that the input format should be ORC.

If the above schema is clear, let us create the hive table with the defined schema.

Step 2: Creating the hive table with ORC format
Step 2: Creating the hive table with ORC format

The above screenshot shows that the schema worked perfectly and we got the expected output i.e. “OK” message after executing this command.

Now, let us check whether this OK message correct or not.

  • VERIFY THAT THE NEW TABLE GOT CREATED SUCCESSFULLY

The updated list of tables in the “default” database confirms whether the hive table post34 got created or not.

show tables;

The output of the above command looks as follows.

Step 3: Confirming the existence of hive table with ORC Format
Step 3: Confirming the existence of hive table with ORC Format

As you can see from the above screenshot, the hive table post34 was created successfully and we got to see it in the updated list of tables in “default” database.

Now, it is time to check the schema of this post34 hive table.

  • CHECKING THE SCHEMA OF THE HIVE ORC TABLE

We are going to use the following command to check the schema of this newly created hive table.

desc formatted post34;

The output of the above command looks like this.

Step 4: Checking the schema and file format of newly created hive table
Step 4: Checking the schema and file format of newly created hive table

As you can see from the above screenshot, the Input and Output file format is ORC unlike Text in this tutorial.

This completes the objective of the tutorial. Hope you guys like the content.

In the next tutorial, we are going to extend this one and load the data into an ORC table from an NON-ORC table.

Till then, stay tuned for the further updates.

You can visit my website at www.milindjagre.com.

You can check out my LinkedIn profile here. Please like my Facebook page here. Follow me on Twitter here and subscribe to my YouTube channel here for the video tutorials.

Cheers!

 

 

Advertisements

One thought on “Post 34 | HDPCD | Defining Hive Table using an ORC File Format

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s