Sqoop Import Part 3

Sqoop Import Part 3

Sqoop can be used to import data seamlessly into HDFS from RDBMS systems. Sqoop provides many options to handle different scenarios. Many of them will be explained in multiple tutorials with examples. This Sqoop tutorial will cover import with compression, import with boundary query, import incremental data and Import data from Teradata.

Generic Arguments to import command

Attribute	Description
--target-dir	This is used to specify HDFS directory where data need to be imported.
--table	This is used to specify RDBMS table name from where data need to be imported.
--append	This is used to append imported data to the existing HDFS directory.
--delete-target-dir	This is used to delete target HDFS directory(if already exist) before importing data.

→ Import with compression: There are many compression techniques are available such Gzip, Snappy & Bzip.

• Gzip: Compress imported data as gunzip. All the three import examples below are same where " --compress", "--compression-codec Gzip" and "--compression-codec org.apache.hadoop.io.compress.GzipCodec" is used to create gunzip file.

    sqoop import  \
   --connect jdbc:mysql://localhost:3306/retail_db \
   --username root \
   --password mysqlroot \
   --driver com.mysql.cj.jdbc.Driver \
   --table orders \
   --target-dir hdfs://localhost:9000/user/username/scoop_import/avro_zip \
   --append \
   --split-by order_id \
    --bindir /Users/username/others/Hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/lib/ \
   --compress
  
  sqoop import  \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --username root \
    --password mysqlrootpassword \
    --driver com.mysql.cj.jdbc.Driver \
    --table orders \
    --target-dir hdfs://localhost:9000/user/username/scoop_import/avro_zip \
   --append \
   --compression-codec Gzip
   
   
   sqoop import  \
   --connect jdbc:mysql://localhost:3306/retail_db \
   --username root \
   --password mysqlrootpassword \
   --driver com.mysql.cj.jdbc.Driver \
   --table orders \
   --target-dir hdfs://localhost:9000/user/username/scoop_import/avro_zip \
   --append \
   --compression-codec org.apache.hadoop.io.compress.GzipCodec
  

  
  
• Snappy: Compress imported data with snappy compression. Both the below import examples are same where "--compression-codec snappy" is used and "--compression-codec org.apache.hadoop.io.compress.SnappyCodec"

   sqoop import  \
   --connect jdbc:mysql://localhost:3306/retail_db \
   --username root \
   --password mysqlrootpassword \
   --driver com.mysql.cj.jdbc.Driver \
   --table orders \
   --target-dir hdfs://localhost:9000/user/username/scoop_import/avro_snaapy \
   --as-avrodatafile \
   --append \
   --compression-codec snappy
   
   
    sqoop import  \
   --connect jdbc:mysql://localhost:3306/retail_db \
   --username root \
   --password mysqlrootpassword \
   --driver com.mysql.cj.jdbc.Driver \
   --table orders \
   --target-dir hdfs://localhost:9000/user/username/scoop_import/avro_snaapy \
   --as-avrodatafile \
   --append \
   --compression-codec org.apache.hadoop.io.compress.SnappyCodec
  

  
  
• Bzip: Compress imported data with Bzip compression.

  
  sqoop import  \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --username root \
    --password mysqlrootpassword \
    --driver com.mysql.cj.jdbc.Driver \
    --table orders \
    --target-dir hdfs://localhost:9000/user/username/scoop_import/avro_zip \
   --append \
   --compression-codec org.apache.hadoop.io.compress.BZip2Codec
  

→ Import Using Boundary Query: There are 2 ways to import data using boundary query. Either primary key should be defined for this work or --spilt-by / single mapper('-m 1') should be used.

• Option 1: Getting boundary i.e. minimum and maximum values from DB. In the below example, Only records with order item id > 9999 will be fetched.

  sqoop import  \
   --connect jdbc:mysql://localhost:3306/retail_db \
   --username root \
   --password mysqlrootpassword \
   --driver com.mysql.cj.jdbc.Driver \
   --table order_items \
   --split-by order_item_id \
   --target-dir hdfs://localhost:9000/user/username/scoop_import/boundary_query3 \
   --boundary-query 'SELECT MIN(order_item_id),MAX(order_item_id) FROM order_items where order_item_id>9999'
  

  
  
• Option 2: Getting boundary by hard coding minimum and maximum values. In the below example, only records with order item id > 100000 will be fetched).

   sqoop import  \
   --connect jdbc:mysql://localhost:3306/retail_db \
   --username root \
   --password mysqlrootpassword \
   --driver com.mysql.cj.jdbc.Driver \
   --table order_items \
   --split-by order_item_id \
   --target-dir hdfs://localhost:9000/user/username/scoop_import/boundary_query_other \
   --boundary-query 'SELECT 100000,172198'
  

→ Import Incremental Data: Incremental import will require "-last-value", "--incremental" & "--check-column" attributes.

• "--check-column" argument can be used to specify column from which incremental data need to be pulled.
• "--incremental" can take 1 of two argument: 'append' or 'lastmodified'"--incremental" can take 1 of two argument: 'append' or 'lastmodified'.
  lastmodified can be used only for date and timestamp columns.
• "--last-value" is used to specify last value.
• Output of the incremental Sqoop import will return "--last-value" to be used for next run.

sqoop import  \
 --connect jdbc:mysql://localhost:3306/retail_db \
 --username root \
 --password mysqlrootpassword \
 --driver com.mysql.cj.jdbc.Driver \
 --table orders \
 --target-dir hdfs://localhost:9000/user/username/scoop_import/partial_column_where \
 --check-column order_id \
 --incremental append \
 --last-value 1022 \
 --bindir $SQOOP_HOME/lib/

→ Import with specified Number of Executor: "-m " attribute can be used to specify number of executor to import data with parallellism using many executors. In the example below, 2 executors are specified

sqoop import \
 --connect jdbc:teradata://127.0.0.1/database=tutorial_db \
 --driver com.teradata.jdbc.TeraDriver \
 --username tutorial_user \
 --password tutorial_user \
 --table order_item \
 --target-dir hdfs://localhost:9000/user/username/scoop_import/TDImport \
 --bindir /Users/username/other/Hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/lib \
 --delete-target-dir \
 -m 2

→ Import Data from Teradata: Importing data is same as MySql but teradata specific driver & URL need to be used

sqoop import \
 --connect jdbc:teradata://127.0.0.1/database=tutorial_db \
 --driver com.teradata.jdbc.TeraDriver \
 --username tutorial_user \
 --password tutorial_user \
 --table order_item \
 --target-dir hdfs://localhost:9000/user/username/scoop_import/TDImport \
 --bindir /Users/username/other/Hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/lib \
 --delete-target-dir \
 -m 1