# list Databases
sqoop list-databases \
--connect jdbc:mysql://[Link] \
--username retail_dba \
--password cloudera
# list Tables
sqoop list-tables \
--connect jdbc:mysql://[Link]/retail_db \
# Eval query order items
sqoop eval \
--query "select * from orders limit 10"
# Eval create a temp table
sqoop eval \
--query "create table temp (i int, val varchar(10))"
# Eval insert a row into temp table
sqoop eval \
--query "insert into temp values( 52410, 'SabeerPH')"
# Import use target directory
sqoop import \
--table orders \
--target-dir /home/cloudera/Final/orders
# Import use warehouse directory
sqoop import \
--table order_items \
--warehouse-dir /home/cloudera/Final/
# Import the data as one file
--num-mappers 1
# try to import data in same directory - should get error
# delete target directory if exists - before import
--delete-target-dir
# append data to the existing directory
--append
# import a table with no Primary Key
--autoreset-to-one-mapper
# import a table with no primary with mentioned column
--split-by order_status
# import data in text, Avro, sequence, parquet format
--as-textfile (default )
--as-avrodatafile \
--as-sequencefile \
--as-parquetfile \
# import data in text format with GZ compression (default)
--compress \
# import data in text format with SnappyCodec compression (tested and it works)
--compress \
--compression-codec [Link] \
# filter using boundary query - import data with order_id greater than 9999
--table orders \
--boundary-query "select min(order_id), max(order_id) from orders where order_id > 9999"
# use --query to import data with a filter (when using query no need --table)
--query "select * from orders where \$CONDITIONS and order_id > 9999"
# use --columns to import data selected columns
--columns order_id,order_date
# Field and line termination (tab and colon)
--fields-terminated-by '\t' \
--lines-terminated-by ':'
# import data for 2014 Jan using –where [--table is needed]
--where "order_date like '2014-01%'" \
# Official incremental import -
--check-column order_date \
--incremental append \
--last-value 2014-01-31 (No need of quotes n all here evenought the column is timestamp here date
worked)
# hive import orders [running hive import twice will append data duplicate records]
--hive-import \
--hive-database shabz_retail_db \
--hive-table orders \
# overwrite data instaed of appending
--hive-overwrite
# crash if the table you are trying to import already exits.(error shows as 'Table orders already exists')
--create-hive-table
# import all tables at once
sqoop import-all-tables \
--autoreset-to-one-mapper
# export daily revenue table from hive to mysql (7 rows )
sqoop export \
--export-dir /user/hive/warehouse/shabz_retail_db.db/daily_revenue \
--table daily_revenue \
--input-fields-terminated-by '\001'
# export daily revenue table with selective columns
--columns order_date,daily_revenue [no spaces allower inbetween coumn names]
# set the mysql daily_revenue column of daily revenue table to zero
# try to update the data again (hero update happens successfully.)
--update-key order_date \
# set the daily_revenue column in mysql to zero
# now try both update and insert
--update-key order_date \
--update-mode allowinsert \
# replace String and Non String column NULL values with
--null-non-string -1 \
--null-string "NOT-AVAILABLE" \
# Merge 2 datas based on the key
sqoop merge \
--class-name products_replica \
--jar-file /tmp/sqoop-cloudera/compile/c6820fa79ec5ef1115e4d432b4c881b3/products_replica.jar \
--new-data /home/cloudera/problem5/products-text-part2/ \
--onto /home/cloudera/problem5/products-text-part1/ \
--target-dir /user/cloudera/problem5/products-text-both-parts \
--merge-key product_id
/tmp/sqoop-cloudera/compile/c6820fa79ec5ef1115e4d432b4c881b3/products_replica.jar
/tmp/sqoop-cloudera/compile/d36b9663c9738a3b6d53f93ea7795528/products_replica.jar
Create JOB to import data and the delta every time
sqoop job \
--create sqoop_first_job \
-- import \ # note there is a space after --
--connect jdbc:mysql://[Link]/retail_db \
--username retail_dba \
--password cloudera \
--table products_replica \
--target-dir /home/cloudera/problem5/products-incremental/ \
--check-column product_id \
--incremental append \
--last-value 0 # note last value will be remembered automatically
sqoop job --exec first_sqoop_job
sqoop job –list
sqoop job –show job_name