Importing data from RDBMS into Hive using Sqoop and oozie (hive-import)

In the How to run Sqoop command from oozie entry i talked about how you can use Oozie and Sqoop to import data into HDFS. I wanted to change it to use sqoop's hive-import option, which in addition to importing data into HDFS also creats Hive table on top of the data. These are the steps that i followed

• First i changed the workflow.xml to take out as-avrodatafile and added hive-import option and i re-ran the workflow that looks like this When i did that the oozie workflow failed with following error

  
  7936 [uber-SubtaskRunner] WARN  org.apache.sqoop.mapreduce.JobBase  - SQOOP_HOME is unset. May not be able to find all job dependencies.
  9202 [uber-SubtaskRunner] DEBUG org.apache.sqoop.mapreduce.db.DBConfiguration  - Fetching password from job credentials store
  9207 [uber-SubtaskRunner] INFO  org.apache.sqoop.mapreduce.db.DBInputFormat  - Using read commited transaction isolation
  9210 [uber-SubtaskRunner] DEBUG org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat  - Creating input split with lower bound '1=1' and upper bound '1=1'
  25643 [uber-SubtaskRunner] INFO  org.apache.sqoop.mapreduce.ImportJobBase  - Transferred 931.1768 KB in 17.6994 seconds (52.6107 KB/sec)
  25649 [uber-SubtaskRunner] INFO  org.apache.sqoop.mapreduce.ImportJobBase  - Retrieved 12435 records.
  25649 [uber-SubtaskRunner] DEBUG org.apache.sqoop.hive.HiveImport  - Hive.inputTable: customers
  25650 [uber-SubtaskRunner] DEBUG org.apache.sqoop.hive.HiveImport  - Hive.outputTable: customers
  25653 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Execute getColumnInfoRawQuery : SELECT t.* FROM `customers` AS t LIMIT 1
  25653 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - No connection paramenters specified. Using regular API for making connection.
  25658 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Using fetchSize for next query: -2147483648
  25658 [uber-SubtaskRunner] INFO  org.apache.sqoop.manager.SqlManager  - Executing SQL statement: SELECT t.* FROM `customers` AS t LIMIT 1
  25659 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_id of type [4, 11, 0]
  25659 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_fname of type [12, 45, 0]
  25659 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_lname of type [12, 45, 0]
  25660 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_email of type [12, 45, 0]
  25660 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_password of type [12, 45, 0]
  25660 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_street of type [12, 255, 0]
  25660 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_city of type [12, 45, 0]
  25660 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_state of type [12, 45, 0]
  25660 [uber-SubtaskRunner] DEBUG org.apache.sqoop.manager.SqlManager  - Found column customer_zipcode of type [12, 45, 0]
  25663 [uber-SubtaskRunner] DEBUG org.apache.sqoop.hive.TableDefWriter  - Create statement: CREATE TABLE IF NOT EXISTS `customers` ( `customer_id` INT, `customer_fname` STRING, `customer_lname` STRING, `customer_email` STRING, `customer_password` STRING, `customer_street` STRING, `customer_city` STRING, `customer_state` STRING, `customer_zipcode` STRING) COMMENT 'Imported by sqoop on 2016/12/22 21:18:39' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' LINES TERMINATED BY '\012' STORED AS TEXTFILE
  25664 [uber-SubtaskRunner] DEBUG org.apache.sqoop.hive.TableDefWriter  - Load statement: LOAD DATA INPATH 'hdfs://quickstart.cloudera:8020/user/cloudera/customers' INTO TABLE `customers`
  25667 [uber-SubtaskRunner] INFO  org.apache.sqoop.hive.HiveImport  - Loading uploaded data into Hive
  25680 [uber-SubtaskRunner] DEBUG org.apache.sqoop.hive.HiveImport  - Using in-process Hive instance.
  25683 [uber-SubtaskRunner] DEBUG org.apache.sqoop.util.SubprocessSecurityManager  - Installing subprocess security manager
  Intercepting System.exit(1)
  
  <<< Invocation of Main class completed <<<
  
  Failing Oozie Launcher, Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]
  
  Oozie Launcher failed, finishing Hadoop job gracefully
  
  Oozie Launcher, uploading action data to HDFS sequence file: hdfs://quickstart.cloudera:8020/user/cloudera/oozie-oozi/0000007-161222163830473-oozie-oozi-W/sqoop-52c0--sqoop/action-data.seq
  
  Oozie Launcher ends
  
  

• As you can see from the log the Sqoop job was able to import data into HDFS in /user/cloudera/customers directory and i could actually see the data in the directory. But when Sqoop tried to create the table in hive it failed and the table did not get created in hive, this is the log statement that i am referring to CREATE TABLE IF NOT EXISTS `customers` ( `customer_id` INT, `customer_fname` STRING, `customer_lname` STRING, `customer_email` STRING, `customer_password` STRING, `customer_street` STRING, `customer_city` STRING, `customer_state` STRING, `customer_zipcode` STRING) COMMENT 'Imported by sqoop on 2016/12/22 21:18:39' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' LINES TERMINATED BY '\012' STORED AS TEXTFILE
• So it seems the problem is Sqoop needs hive-site.xml so that it knows how to talk to hive service, for that first i search my sandbox to figure out where hive-site.xml is located, i executed following command to first find the hive-site.xml and then uploading it to HDFS sudo find / -name hive-site.xml hdfs dfs -put /etc/hive/conf.dist/hive-site.xml
• After that i went back to the workflow.xml and modified it to look like this

Now when i ran the oozie workflow it was successful and i could query customer data

How to run Sqoop command from oozie

In the Importing data from Sqoop into Hive External Table with Avro encoding updated i blogged about how you can use sqoop to import data from RDBMS into Hadoop. I wanted to test if i can use Oozie for invoking Sqoop command and i followed these steps for doing that.

1. First i tried executing this command from my command line on Hadoop cluster to make sure that i can actually run sqoop without any problem

   
   sqoop import --connect jdbc:mysql://localhost/test 
   --username root 
   --password cloudera 
   --table CUSTOMER 
   --as-avrodatafile
   

2. Once the sqoop command was successfully executed i went back and deleted the CUSTOMER directory from HDFS to make sure that i could re-import data using following command

   
   hdfs dfs -rm -R CUSTOMER
   

3. Next i went to Hue to create oozie workflow with single sqoop command that i had executed before
   
   But if your not using the Hue console you can create workflow.xml manually like this Also make sure to create job.properties file like this Take a look at Enabling Oozie console on Cloudera VM 4.4.0 and executing examples for information on how to run oozie job from command line
4. Next when i ran the Oozie workflow, the job failed with following error, which indicates that Oozie does not have the MySQL JDBC driver.

   
   java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver
    at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:875)
    at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
    at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763)
    at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
    at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
    at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
    at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
    at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:327)
    at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1846)
    at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1646)
    at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
    at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
    at org.apache.oozie.action.hadoop.SqoopMain.runSqoopJob(SqoopMain.java:197)
    at org.apache.oozie.action.hadoop.SqoopMain.run(SqoopMain.java:177)
    at org.apache.oozie.action.hadoop.LauncherMain.run(LauncherMain.java:49)
   

5. So first thing i did was to check if mysql driver is there in the oozie shared lib by executing following commands

   
   export OOZIE_URL=http://localhost:11000/oozie
   oozie admin -shareliblist sqoop
   

   I noticed that the mysql-connector-java.jar was not there in the list of shared libs for Oozie + sqoop
6. Next step was to find the mysql-connector-java.jar in my sandbox that i could do by finding it like this

   
   sudo find / -name mysql*
   

   I found mysql-connector-java.jar on my local machine at /var/lib/sqoop/mysql-connector-java.jar
7. I wanted to update the Oozie shared lib to include the mysql driver jar. So i executed following command to figure out the directory where the oozie sqoop shared lib is

   
   oozie admin -sharelibupdate
   

   
   From this output i got HDFS directory location for Oozie shared lib which is /user/oozie/share/lib/lib_20160406022812
8. Then i used following two commands to first copy the db driver into the oozie shared lib and making sure it is accessible to other users hdfs -copyFromLocal /var/lib/sqoop/mysql-connector-java.jar /user/oozie/share/lib/sqoop/. hdfs dfs -chmod 777 /user/oozie/share/lib/sqoop/mysql-connector-java.jar
9. Now the last step was to let Oozie know that it should reload the sharedlib and i did that by executing following two commands

   
   oozie admin -sharedlibupdate
   oozie admin -shareliblist sqoop | grep mysql*
   

   The second command queries oozie to get current list of shared jars and i could see mysql-connector-java.jar listed in it like this
   

When i re-executed the ooize job again this time it ran successfully.

Importing data from Sqoop into Hive External Table with Avro encoding updated

In the Importing data from Sqoop into Hive External Table with Avro encoding i had details on how you can import a table from RDBMS into Hive using Sqoop in Avro format. In that blog i went through few steps to get the avsc file, but i realized there is easier way to do it following these steps

1. First execute the sqoop import command like this, make sure that you pass --outdir schema as parameters to the sqoop import command, what that does is it generates the CUSTOMER.avsc and CUSTOMER.java in the schema directory on your local machine

   
   sqoop import --connect jdbc:mysql://localhost/test 
   --username root 
   --password cloudera 
   --table CUSTOMER 
   --as-avrodatafile 
   --outdir schema
   

2. You can verify that CUSTOMER.avsc file got created as you expected by executing ls -ltrA schema
   
3. Next create schema directory in HDFS by executing hdfs mkdir command like this

   
   hdfs dfs -mkdir /user/cloudera/schema
   

4. Copy the CUSTOMER.avsc from your local schema directory to HDFS in schema directory by executing following command

   
   hdfs dfs -copyFromLocal schema/CUSTOMER.avsc /user/cloudera/schema/.
   

5. Last step is to create Hive table with CUSTOMER.avsc as schema using following command

   
   CREATE EXTERNAL TABLE CUSTOMER
   ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
   STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
   LOCATION '/user/cloudera/CUSTOMER'
   TBLPROPERTIES ('avro.schema.url'='/user/cloudera/schema/CUSTOMER.avsc');
   

Now if you go to hive and execute "SELECT * FROM CUSTOMER;" query then you should see 1 record in it like this

Importing data from Sqoop into Hive External Table with Avro encoding

I wanted to figure out how to import content of RDBMS table into Hive with Avro encoding, during this process i wanted to use external hive tables so that i have complete control over the location of files.
Note: I have a different/easier method for doing this in Importing data from Sqoop into Hive External Table with Avro encoding updated
First i did create following table in the mysql database which is on the same machine as that of my HortonWorks Sandbox

1. First create CUSTOMER table like this in mysql

   
   CREATE TABLE CUSTOMER (       
   contactid INTEGER NOT NULL ,       
   firstname VARCHAR(50),       
   lastname  VARCHAR(50),       
   email varchar(50) );
   

2. After creating table add couple of records in it by executing following insert statement insert into customer values(1,'Sachin','Tendulark','sachin@gmail.com');
3. Next step is to run sqoop query that downloads records of the table into HDFS at /tmp/customer/sample. In real world you might want to download only first 10 records or so into Hive, because you need few sample records just to create avro schema

   
   sqoop import --connect jdbc:mysql://localhost/test --table CUSTOMER --username sqoop1 
   --password sqoop -m 1 --create-hive-table 
   --hive-table CONTACT --as-avrodatafile  --target-dir /tmp/customer/sample
   

4. Running sqoop command it will dump records in HDFS, so first download the avro file generated by sqoop

   
   hdfs dfs -get /tmp/customer/sample/part-m-00000.avro
   

5. Use the avro-tools-*.jar, to read schema of the file generated by sqoop. by executing following command

   
   java -jar avro-tools-1.7.5.jar getschema part-m-00000.avro > customer.avsc
   

   This is how the customer.avsc file looks like in my case

   
   {
     "type" : "record",
     "name" : "CUSTOMER",
     "doc" : "Sqoop import of CUSTOMER",
     "fields" : [ {
       "name" : "contactid",
       "type" : [ "int", "null" ],
       "columnName" : "contactid",
       "sqlType" : "4"
     }, {
       "name" : "firstname",
       "type" : [ "string", "null" ],
       "columnName" : "firstname",
       "sqlType" : "12"
     }, {
       "name" : "lastname",
       "type" : [ "string", "null" ],
       "columnName" : "lastname",
       "sqlType" : "12"
     }, {
       "name" : "email",
       "type" : [ "string", "null" ],
       "columnName" : "email",
       "sqlType" : "12"
     } ],
     "tableName" : "CUSTOMER"
   }
   

6. Next step is to upload the avro schema file that you created in the last step back to HDFS, in my case i had HDFS folder called /tmp/customer/schema and i uploaded the avro schema file in it

   
   hdfs dfs -put customer.avsc /tmp/customer/schema/
   

7. Now go to hive and execute the following command to define External Customer Hive table with avro schema defined in last step

   
   CREATE EXTERNAL TABLE CUSTOMER
   ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
   STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
   LOCATION '/tmp/customer/data'
   TBLPROPERTIES ('avro.schema.url'='hdfs:///tmp/customer/schema/customer.avsc');
   

8. Last step is to run sqoop again but this time with all the data in the external directory that Customer hive table is pointing to.

   
   sqoop import --connect jdbc:mysql://localhost/test --table CUSTOMER --username sqoop1 
   --password sqoop -m 1 --as-avrodatafile 
    --target-dir /tmp/customer/data --compression-codec snappy
   

Now if you run select query on the CUSTOMER table you should be able to get all the data that you see in your RDBMS

Exporting data from Hive table to RDBMS

In the Importing data from RDBMS into Hadoop using sqoop i blogged about how to import data from RDBMS to Hive, but now i wanted to figure out how to export data from Hive back to RDBMS, Sqoop has export feature that allows you to export data from Hadoop directory(CSV files in a directory) to RDBMS, I wanted to try exporting data from sqoop so first i created a simple contact_hive table and populated some data in it, then i used sqoop to export the content of contact_hive table into contact table in MySQL, i followed these steps, if you already have a hive table populated then you can skip first 5 steps and go to step 6.

1. Create contacthive.csv file which has simple data with 4 columns separated by comma

   
   1,MahendraSingh,Dhoni,mahendra@bcci.com
   2,Virat,Kohali,virat@bcci.com
   5,Sachin,Tendulkar,sachin@bcci.com
   

2. Upload the contacthive.csv that you created in last step in HDFS at /tmp folder using following command

   
   hdfs dfs -put contacthive.csv /tmp
   

3. Define a contact_hive table that will have 4 columns, contactId, firstName, lastName and email, execute this command in hive console

   
   CREATE TABLE contact_hive(contactId Int, firstName String, lastName String, email String) row format delimited fields terminated by "," stored as textfile;
   

4. In this step populate the contact_hive table that you created in the last step with the data from contacthive.csv file created in step 1. Execute this command in Hive console to populate contact_hive table

   
   LOAD DATA INPATH  "/tmp/contacthive.csv" OVERWRITE INTO TABLE contact_hive;
   

5. Since i am using Hive managed table, it will move the contacthive.csv file to Hive managed directory in case of Hortonworks that directory is /apps/hive/warehouse, You can verify that by executing following command on HDFS

   
   hdfs dfs -ls /apps/hive/warehouse/contact_hive
   

6. Before you export data into RDBMS, you will have to create the table in mysql, use following command to create the CONTACT table in mysql.

   
   
   CREATE TABLE CUSTOMER (
         contactid INTEGER NOT NULL ,
         firstname VARCHAR(50),
         lastname  VARCHAR(50),
         email varchar(50)
   );
   

7. Now last step is to execute sqoop export command that exports data from hive/hdfs directory to database

   
   sqoop export --connect jdbc:mysql://localhost/test --table CONTACT --export-dir /apps/hive/warehouse/contact_hive
   

Importing data from RDBMS into Hive using create-hive-table of sqoop

In the Importing data from RDBMS into Hive i blogged about how to import data from RDBMS into Hive using Sqoop. In that case the import command took care of both creating table in Hive based on RDMBS table as well as importing data from RDBMS into Hive. But Sqoop can also be used to import data stored in HDFS text file into Hive. I wanted to try that out, so what i did is i created the contact table in Hive manually and then used the contact table that i exported as text file into HDFS as input

1. First i used sqoop import command to import content of Contact table into HDFS as text file. By default sqoop will use , for separating columns and newline for separating

   
   sqoop import --connect jdbc:mysql://macos/test --table contact -m 1
   

   After import is done i can see content of the text file by executing hdfs dfs -cat contact/part-m-00000 like this
   
2. After that you can use sqoop to create table into hive based on schema of the CONTACT table in RDBMS. by executing following command

   
   sqoop create-hive-table --connect jdbc:mysql://macos/test --table Address --fields-terminated-by ','
   

3. Last step is to use Hive for loading content of contact text file into contact table. by executing following command.

   
   LOAD DATA INPATH 'contact' into table contact;
   

Importing data from RDBMS into Hive using sqoop

In the Importing data from RDBMS into Hadoop i blogged about how to import content of RDBMS into Hadoop Text file using Sqoop. But its more common to import the content of RDMBS into Hive. I wanted to try that out, so i decided to import content of the Contact table that i created in the Importing data from RDBMS into Hadoop entry in Contact table in Hive on my local machine. I followed these steps

1. First take a look at content of Contact table in my local MySQL like this (SELECT * from CONTACT)
   
2. Next step is to use sqoop import command like this

   
   sqoop import --connect jdbc:mysql://macos/test --table Address -m 1 --hive-import
   

   As you will notice this command is same as hive import command that i used in last blog entry to import content of RDMBS into text file, only difference is i had to add --hive-import switch
3. This command takes care of first creating Contact table into Hive and then importing content of CONTACT table from RDMBS into CONTACT table in Hive. Now i can see content of Contact table in Hive like this
   

Importing data from RDBMS into Hadoop using sqoop

Apache Sqoop lets you import content of RDBMS into Hadoop. By default it will import content of a table into hadoop text file with columns separated by , and rows separated by new line. I wanted to try this feature out so i decided to import table from MySQL database on my local machine into HDFS using Sqoop

1. First i created a CONTACT table in my local like this

   
   CREATE TABLE `CONTACT` (
     `contactid` int(11) NOT NULL,
     `FNAME` varchar(45) DEFAULT NULL,
     `LNAME` varchar(45) DEFAULT NULL,
     `EMAIL` varchar(45) DEFAULT NULL,
     PRIMARY KEY (`contactid`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

3. Then i had to add few records into CONTACT table by using this syntax

   
   INSERT INTO `test`.`CONTACT`(`contactid`,`FNAME`,`LNAME`,`EMAIL`)VALUES(1,'Sunil','Patil','sdpatil@gmail.com');
   

4. Then on the command line i had to execute following command to run Sqoop so that it imports content of

   
   sqoop import --connect jdbc:mysql://localhost/test --table Contact 
   

   This command tells sqoop to connect to test database in mysql on localhost jdbc:mysql://localhost/test and import content of CONTACT table.
5. After executing the command when i looked into the HDFS i could see that there is Contact directory (same as table name, if you want to use different directory name then table name pass --target-dir argument ), that directory contains 4 files.
   
6. Now if i look inside one of the part-m files i could see it has content of CONTACT table dumped inside it like this
   
7. By default sqoop opens multiple threads to import content of the table. If you want you can control number of map jobs it runs. In my case the CONTACT table has only 12 rows so i want sqoop to run only 1 map job, so i used following command

   
   sqoop import --connect jdbc:mysql://localhost/test --table Contact --target-dir contact1 -m 1