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

Tables in Hive

A Hive table is logically made up of the data being stored and the associated metadata describing the layout of the data in the table. The data typically associated resides in HDFS, although it may reside in any Hadoop file system, including the local file system or s3. Hive stores the metadata in relational databases and not in HDFS.

Each table has a corresponding directory in HDFS and the data is Serialized and stores in files within the directory.

Different types of Tables

Managed Tables

Managed Tables are nothing but when you create the table in hive, by default hive will manage the data, which means that hive controls the lifecycle of the data into its warehouse directory.

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.

hive> CREATE TABLE workshop.sample(key int, value string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

If you’re not currently working in the target database, then you can prefix the database name. In the above statement we mentioned workshop as database.

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.

Note: 

When a managed table gets dropped, both the metadata and data get dropped. However, managed tables are less convenient for sharing with other tools.

External Tables

Suppose we have data that is created and used primarily by Pig or other tools, but we want to run some queries against it, but not give Hive ownership of the data. We can define an external table that points to that data, but doesn't take ownership of it.

External table is nothing but when you create the table in hive, the data stored at an existing location outside the warehouse directory.

Create an External table and points the location of the data like

hive>CREATE EXTERNAL TABLE sample (key int, value string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/data/sample';

An EXTERNAL table points to any HDFS location for its storage, rather than being stored in a folder specified by the configuration property hive.metastore.warehouse.dir.

Note:

• External table are virtual tables, not physically shown in HDFS.
• External table won’t load data; just it points the location of the data.
• 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 you want to do process on other module data, external table are useful. Since External table create a virtual table points to data and process on the data without modifying the original data.

How do you check existing table is managed or external table?

To check that we use describe command like below

describe formatted tablename;

It displays complete meta data of a table. 

You can find row key as Table Type which will display either MANAGED_TABLE OR EXTERNAL_TABLE

Eg:

If it is managed table, you will see

Table Type:             MANAGED_TABLE

If it is external table ,you will see

Table Type:             EXTERNAL_TABLE

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