Hadoop - Hive - Load data from csv/xls files

We know that Hadoop helps to store mass data, process and analyze the same very fast. It is not easy for non java developers to extract and analyze the data from Hadoop framework but with the development of Hive any non java database developers can easily do the data analysis quickly. Hive is developed in Facebook labs and the syntax is more similar to all other Structured Query Languages such as Oracle , SQL server, MySQL etc.

Pig which I have explained in my previous post can consider as alternative to Hive. Pig was developed at Yahoo! about the same time Facebook was developing Hive. Hive is used for OLAP purpose than the OLTP whereas Pig is considered as ETL language for Hadoop.

To illustrate the Hive syntax and use, I thought it is best to load the data from Adventureworks DW database.

I followed below steps to load data from Adventureworks database to file and to Hadoop eco system.

1. Pull the records from required tables to xlsx files
2. Browse the csv file from Hadoop as shown in Fig 1

Fig 1

3. Select the file that need to be uploaded

Fig 2

4. This will load the xls file into Hadoop

Fig 3

5. Now we need to create metadata for this loaded data into Hadoop using HCatalog. Click on the create table option in left side of the HCatalog.

Fig 4

6.  This will open the below window where you can select the already loaded file

Fig 5

7.  choose Product.xlsx

Fig 6

8. Once you enter, you can see below screen where you can provide the column name and the data type for each field. HCatalog automatically fixes data type for each field based on the data but we can change it based on our requirement. Finally you can hit create table button to generate the final table.

Fig 7

Once the file is uploaded in Hadoop and created metadata using HCatalog, you can browse or analyze the data using hive.