Saving Hive Query Result to HIVE TABLE or FILESYSTEM

In this post, we will see how to save hive query result to a HIVE TABLE or FILE SYSTEM.

We can achieve this by using the INSERT clause.

Let say we have customer table
hive> select * from customer;

Saving Query Results to a Table
If we want to load customers who are from city_1 to a table:
hive> INSERT OVERWRITE TABLE customers_city1 SELECT * FROM customers WHERE city='city_1';

hive> select * from customers_city1;

If we don't want to delete existing data, then use below query:
hive> INSERT INTO TABLE customers_city1 SELECT * FROM customers WHERE city='city_1';

Note: Destination table must already exists

Saving Query Results to a FileSystem

If we want to save query result to a Hadoop FileSystem (HDFS), use below query:
hive> INSERT OVERWRITE DIRECTORY '/home/cloudera/result/customers' SELECT * FROM customers WHERE city='city_1';

Add LOCAL keyword to save query result to local filesystem:
hive> INSERT OVERWRITE LOCAL DIRECTORY '/home/cloudera/customers' SELECT * FROM customers WHERE city='city_1';


Note: Writes query result in text files with default delimiter '\t'.

Passing values to Hive script at runtime time to make reusable script

When we are play around with data warehousing it's very common to pass values at runtime. Normally write our series of hive queries in a file and run it using hive -f option from UNIX shell or bash or schedule with workflow management systems like Oozie, Airflow, etc.

Let say we want see top 10 records of a table, we will write below query to a file say get_top_records.hql.
$ cat get_top_records.hql
SELECT * FROM CUSTOMERS LIMIT 10;

Running hive script using hive -f option from UNIX shell
$ hive -f get_top_records.hql

In the above script, table name and limit are hard coded, if you want to retrieve top 10 records of different table or to change the limit then we have to write new script or modify the script. To make reusable script table name and limit to be passed when you are running the script.

We can achieve this with the help of -hiveconf. We will see how to use -hiveconf to avoid hard coding and make reusable script.

Change above script with parametarised that to be passed while running the script.
$ cat get_top_records.hql
SELECT * FROM ${hiveconf:tablename} limit ${hiveconf:limit}

Now we need to pass two parameters tablename and limit while running the script like
$ hive -f get_top_records.hql -hiveconf tablename=CUSTOMERS -hiveconf limit 5

Now its reusable script, you can get top records of any table by passing table name and top records count
$ hive -f get_top_records.hql -hiveconf tablename=SALES -hiveconf limit 10

We can also set hive config parameters also by using -hiveconf. Let say we need to enable compression and set compress codec to SNAPPY:
$ hive -f create_table.hql  -hiveconf hive.exec.compress.output=true -hiveconf mapred.output.compress=true -hiveconf mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec

Load XML file into Hive Table using xpath

Here is a sample input XML file: 

$cat employees.xml
<employee>
<id>1</id>
<name>Satish Kumar</name>
<designation>Technical Lead</designation>
</employee>
<employee>
<id>2</id>
<name>Ramya</name>
<designation>Testing</designation>
</employee>

Step:1 Bring each record to one line, by executing below command

$cat employees.xml | tr -d '&' | tr '\n' ' ' | tr '\r' ' ' | sed 's|</employee>|</employee>\n|g' | grep -v '^\s*$' > employees_records.xml

$cat employees_records.xml

<employee> <id>1</id> <name>Satish Kumar</name> <designation>Technical Lead</designation> </employee>

<employee> <id>2</id> <name>Ramya</name> <designation>Testing</designation> </employee>

Step:2 Load the file to HDFS

$hadoop fs -mkdir /user/hive/sample-xml-inputs

$hadoop fs -put employees_records.xml /user/hive/sample-xml-inputs

$hadoop fs -cat /user/hive/sample-xml-inputs/employees_records.xml
<employee> <id>1</id> <name>Satish Kumar</name><designation>Technical Lead</designation> </employee>
<employee> <id>2</id> <name>Ramya</name> <designation>Testing</designation> </employee>

Step:3 Create a Hive table and point to xml file

hive>create external table xml_table_org( xmldata string) LOCATION '/user/hive/sample-xml-inputs/';

hive> select * from xml_table_org;
OK
<employee> <id>1</id> <name>Satish Kumar</name> <designation>Technical Lead</designation> </employee>
<employee> <id>2</id> <name>Ramya</name> <designation>Testing</designation> </employee>

Time taken: 0.179 seconds

Step 4: From the stage table we can query the elements and load it to other table.

hive> CREATE TABLE xml_table AS SELECT xpath_int(xmldata,'employee/id'),xpath_string(xmldata,'employee/name'),xpath_string(xmldata,'employee/designation') FROM xml_table_org;
Total MapReduce jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201506301103_0001, Tracking URL = http://0.0.0.0:50030/jobdetails.jsp?jobid=job_201506301103_0001
Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill job_201506301103_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2015-06-30 11:23:10,969 Stage-1 map = 0%,  reduce = 0%
2015-06-30 11:23:18,040 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.95 sec
2015-06-30 11:23:19,058 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.95 sec
2015-06-30 11:23:20,067 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.95 sec
2015-06-30 11:23:21,079 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 0.95 sec
MapReduce Total cumulative CPU time: 950 msec
Ended Job = job_201506301103_0001
Ended Job = 1716336105, job is filtered out (removed at runtime).
Ended Job = -578743888, job is filtered out (removed at runtime).
Moving data to: hdfs://localhost.localdomain:8020/tmp/hive-cloudera/hive_2015-06-30_11-22-59_851_2446083450544691385-1/-ext-10001
Moving data to: hdfs://localhost.localdomain:8020/user/hive/warehouse/xml_table
chgrp: changing ownership of 'hdfs://localhost.localdomain:8020/user/hive/warehouse/xml_table': User does not belong to hive
Table default.xml_table stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 46, raw_data_size: 0]
2 Rows loaded to hdfs://localhost.localdomain:8020/tmp/hive-cloudera/hive_2015-06-30_11-22-59_851_2446083450544691385-1/-ext-10000
MapReduce Jobs Launched: 
Job 0: Map: 1   Cumulative CPU: 0.95 sec   HDFS Read: 435 HDFS Write: 46 SUCCESS
Total MapReduce CPU Time Spent: 950 msec
OK
Time taken: 21.649 seconds

hive> select * from xml_table;                                       OK
1 Satish Kumar Technical Lead
2 Ramya Testing

Time taken: 0.143 seconds

Very Useful Hive CLI settings

hive.cli.print.current.db [Show the current database as part of the prompt]

The USE command sets a database as your working database, analogous to changing working directories in a filesystem:

hive> USE workshop;

There is no command to show you which database is your current working database.

To show the current database as part of the prompt, set the property 'hive.cli.print.current.db' to show the current database as part of the prompt:

hive> set hive.cli.print.current.db=true;
hive (workshop)> USE default;
hive (default)> set hive.cli.print.current.db=false;
hive> 

hive.cli.print.header [Print table columns header]

CLI to print column headers, which is disabled by default. We can enable this feature by setting

hive> set hive.cli.print.header=true;
hive> SELECT * FROM employee LIMIT 2;
employee.id employee.name employee.age
101 Satish 30
102 kumar 29

Databases in Hive

In Hive, database is just a catalog or namespace of tables. However, they are very useful for larger clusters with multiple teams and users, as a way of avoiding table name collisions. It’s also common to use databases to organize production tables into logical groups.

If you don’t specify a database, the default database is used.

Create a DataBase

hive> CREATE DATABASE workshop;

Throw an error if workshop database already exists. 

You can suppress these warnings with below variation:

hive> CREATE DATABASE IF NOT EXISTS workshop;

IF NOT EXISTS clause is useful for scripts that should create a database on-the-fly, if necessary, before proceeding.

Note:

Hive will create a directory for each database under a top-level directory specified by the property hive.metastore.warehouse.dir. (default value is /user/hive/warehouse).

Eg: when the 'workshop' database is created, Hive will create the directory /user/hive/warehouse/workshop.db. (Note the .db extension).

All the tables of the database will be stored in sub-directories of the database directory. The exception is tables in the default database, which doesn't have its own directory.

You can override the default location for the new directory as shown in this example:

hive> CREATE DATABASE workshop LOCATION '/hive_workshop’;

You can add a descriptive comment to the database, which will be shown by the

hive> CREATE DATABASE workshop COMMENT 'Holds all my exercises in Hive';

Database Schema

hive> DESCRIBE DATABASE workshop;

OK

workshop Holds all my exercises in Hive hdfs://satishkumar/user/hive/warehouse/workshop.db satishkumar

Time taken: 0.026 seconds, Fetched: 1 row(s)

DESCRIBE DATABASE also shows the directory location for the database. If you are running in pseudo-distributed mode, then the master server will be localhost. For local mode, the path will be a local path, file:///user/hive/warehouse/workshop.db.

You can associate key-value properties with the database, although their only function currently is to provide a way of adding information to the output of DESCRIBE DATABASE EXTENDED <database>:

hive> CREATE DATABASE workshop WITH DBPROPERTIES ('creator' = 'Satish Kumar', 'date' = '2014-12-28');

hive> DESCRIBE DATABASE workshop;                                                                    

OK

workshop hdfs://satishkumar/user/hive/warehouse/workshop.db satishkumar

Time taken: 0.026 seconds, Fetched: 1 row(s)

List of DataBases

At any time, you can see the databases that already exist as follows:

hive> SHOW DATABASES;

default

workshop

If you have a lot of databases, you can restrict the ones listed using a regular expression like

hive> SHOW DATABASES LIKE 'w.*';

workshop 

It lists only those databases that start with the letter ‘w’ and end with any other characters.

Set Working Database

The USE command sets a database as your working database, analogous to changing working directories in a filesystem:

hive> USE employee_db;

Note: There is no command to show you which database is your current working database.

Drop a DataBase

hive> DROP DATABASE IF EXISTS workshop;

The IF EXISTS is optional and suppresses warnings if workshop doesn't exist.

By default, Hive won’t permit you to drop a database if it contains tables. You can either drop the tables first or append the CASCADE keyword to the command, which will cause the Hive to drop the tables in the database first:

hive> DROP DATABASE IF EXISTS financials CASCADE;

Using the RESTRICT keyword instead of CASCADE is equivalent to the default behavior, where existing tables must be dropped before dropping the database. When a database is dropped, its directory is also deleted.

Alter a DataBase

You can set key-value pairs in the DBPROPERTIES associated with a database using the

ALTER DATABASE command. No other metadata about the database can be changed,

Including its name and directory location:

hive> ALTER DATABASE workshop SET DBPROPERTIES ('edited-by' = 'Teja');

There is no way to delete or “unset” a DBPROPERTY.

Setup and Configure Hive

Step 1: 

Download latest Hive from Apache Download Mirrors and derby db.

https://archive.apache.org/dist/hive/hive-0.13.1/

http://db.apache.org/derby/releases/release-10.10.2.0.cgi

Step 2: 

Un-tar the files to your working directory.

Step 3: 

Set environment variable  for the Hive home directory in .bashrc file.

export HIVE_HOME="/home/satishkumar/WORK/apache-hive-0.13.1-bin"

export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HIVE_HOME

Step 4: 

Close the terminal and open to verify the environment variable for the Hive is configured or not.

$echo $HIVE_HOME

Step 5: Start derby

Here we are using default db as derby for storing Hive Metadata. Start derby by using following commands

./startNetworkServer -h 0.0.0.0 &   

    or in case of any exception  

./startNetworkServer -noSecurityManager 

Step 6: 

Add/Update Configurations in hive-site.xml

<configuration>  

<property>  

  <name>javax.jdo.option.ConnectionURL</name>  

  <value>jdbc:derby://localhost:1527/myderby1;create=true</value>  

 <description>JDBC connect string for a JDBC metastore</description>

</property>  

  <property>  

 <name>javax.jdo.option.ConnectionDriverName</name>  

 <value>org.apache.derby.jdbc.ClientDriver</value>  

 <description>Driver class name for a JDBC metastore</description>  

</property>  

</configuration>

Step 7:

Copy derby librarires to hive/lib. (Here we are using derby as MetaData, so copying all the derby libraries to Hive)

1) Delete if any derby lib is already there in hive/lib folder. 

2) copy derby.jar, derbyclient.jar, derbytools.jar from derby/lib into hive/lib.

Step 8:

Start Hive with following command

$> bin/hive 

Why go for Hive When Pig is There?

• Pig 
  • Procedural data-flow language 
  • Pig is used by Programmers and Researchers. 
  • Pig is on the client side. 
  • For managing and querying unstructured data. 
    
• Hive 
  • Declarative SQLish Language 
  • Hive is used by analysts generating data reports. 
  • Hive is on cluster side. 
  • For managing and querying structured data. 
  
  

Features	Hive	Pig
Language	SQL-like	PigLatin
Schemas/Type	Yes (explicit)  We have to create "tables" beforehand and stores the schema in a either shared or local database for metadata.	Yes (implicit)  No need to create table.
Partitions	Yes	No
Server	Optional (Thrift)	No
UDF	Yes (Java)	Yes (Java)
Custom Serialize/Deserializer	Yes	Yes
DFS Direct Access	Yes (implicit)  We never point to the actual HDFS folder.	Yes (explict)  We explicitly point to HDFS folder.
Join/Order/Sort	Yes	Yes
Shell	Yes	Yes
Streaming	Yes	Yes
WebInterface	Yes	No
JDBC/ODBC	Yes (limited)	No