Sqoop job
is used to create a job that can
a.
save
the command
b.
Execute
the command
c.
Store
the past state of the command, if the command relates to incremental imports
Let’s
create a sqoop job, execute and check the job configuration etc.,
How to
create a sqoop job?
We can
create a sqoop job with below syntax.
Syntax
sqoop job --create
{JOB_NAME} -- {COMMAND_TO_EXECUTE}
Example
sqoop job \
--create customer_import \
-- import \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username "root" \
--password "cloudera" \
--table "customers" \
--warehouse-dir /job-demo-1 \
--incremental append \
--check-column customer_id \
--last-value 0
[cloudera@quickstart ~]$ sqoop job \
> --create customer_import \
> -- import \
> --connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
> --username "root" \
> --password "cloudera" \
> --table "customers" \
> --warehouse-dir /job-demo-1 \
> --incremental append \
> --check-column customer_id \
> --last-value 0
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
22/04/08 18:57:46 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
22/04/08 18:57:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
How to
print all the sqoop jobs?
‘sqoop job
--list' command print all the sqoop jobs.
[cloudera@quickstart ~]$ sqoop job --list
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
22/04/08 18:54:58 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
Available jobs:
customer_import
How to
execute the job?
Syntax
sqoop job
--exec {job_name}
Example
sqoop job --exec customer_import
[cloudera@quickstart ~]$ sqoop job --exec customer_import
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
22/04/08 18:58:29 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.13.0
Enter password:
22/04/08 18:58:40 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
22/04/08 18:58:40 INFO tool.CodeGenTool: Beginning code generation
22/04/08 18:58:40 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `customers` AS t LIMIT 1
22/04/08 18:58:40 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `customers` AS t LIMIT 1
22/04/08 18:58:40 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/lib/hadoop-mapreduce
Note: /tmp/sqoop-cloudera/compile/d4e92057e12f71bb02c170e9c6aa826a/customers.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
22/04/08 18:58:42 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-cloudera/compile/d4e92057e12f71bb02c170e9c6aa826a/customers.jar
22/04/08 18:58:43 INFO tool.ImportTool: Maximal id query for free form incremental import: SELECT MAX(`customer_id`) FROM `customers`
22/04/08 18:58:43 INFO tool.ImportTool: Incremental import based on column `customer_id`
22/04/08 18:58:43 INFO tool.ImportTool: Lower bound value: 0
22/04/08 18:58:43 INFO tool.ImportTool: Upper bound value: 12440
22/04/08 18:58:43 WARN manager.MySQLManager: It looks like you are importing from mysql.
22/04/08 18:58:43 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
22/04/08 18:58:43 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
22/04/08 18:58:43 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
22/04/08 18:58:43 INFO mapreduce.ImportJobBase: Beginning import of customers
22/04/08 18:58:43 INFO Configuration.deprecation: mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address
22/04/08 18:58:43 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
22/04/08 18:58:43 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
22/04/08 18:58:43 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
22/04/08 18:58:44 WARN hdfs.DFSClient: Caught exception
java.lang.InterruptedException
at java.lang.Object.wait(Native Method)
at java.lang.Thread.join(Thread.java:1281)
at java.lang.Thread.join(Thread.java:1355)
at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.closeResponder(DFSOutputStream.java:967)
at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.endBlock(DFSOutputStream.java:705)
at org.apache.hadoop.hdfs.DFSOutputStream$DataStreamer.run(DFSOutputStream.java:894)
22/04/08 18:58:45 INFO db.DBInputFormat: Using read commited transaction isolation
22/04/08 18:58:45 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`customer_id`), MAX(`customer_id`) FROM `customers` WHERE ( `customer_id` > 0 AND `customer_id` <= 12440 )
22/04/08 18:58:45 INFO db.IntegerSplitter: Split size: 3109; Num splits: 4 from: 1 to: 12440
22/04/08 18:58:45 INFO mapreduce.JobSubmitter: number of splits:4
22/04/08 18:58:45 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1649172504056_0008
22/04/08 18:58:45 INFO impl.YarnClientImpl: Submitted application application_1649172504056_0008
22/04/08 18:58:45 INFO mapreduce.Job: The url to track the job: http://quickstart.cloudera:8088/proxy/application_1649172504056_0008/
22/04/08 18:58:45 INFO mapreduce.Job: Running job: job_1649172504056_0008
22/04/08 18:58:52 INFO mapreduce.Job: Job job_1649172504056_0008 running in uber mode : false
22/04/08 18:58:52 INFO mapreduce.Job: map 0% reduce 0%
22/04/08 18:59:06 INFO mapreduce.Job: map 25% reduce 0%
22/04/08 18:59:10 INFO mapreduce.Job: map 50% reduce 0%
22/04/08 18:59:11 INFO mapreduce.Job: map 100% reduce 0%
22/04/08 18:59:12 INFO mapreduce.Job: Job job_1649172504056_0008 completed successfully
22/04/08 18:59:12 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=690728
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=487
HDFS: Number of bytes written=953915
HDFS: Number of read operations=16
HDFS: Number of large read operations=0
HDFS: Number of write operations=8
Job Counters
Launched map tasks=4
Other local map tasks=4
Total time spent by all maps in occupied slots (ms)=52463
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=52463
Total vcore-milliseconds taken by all map tasks=52463
Total megabyte-milliseconds taken by all map tasks=53722112
Map-Reduce Framework
Map input records=12440
Map output records=12440
Input split bytes=487
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=682
CPU time spent (ms)=4120
Physical memory (bytes) snapshot=562405376
Virtual memory (bytes) snapshot=6044942336
Total committed heap usage (bytes)=243007488
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=953915
22/04/08 18:59:12 INFO mapreduce.ImportJobBase: Transferred 931.5576 KB in 29.0003 seconds (32.1223 KB/sec)
22/04/08 18:59:12 INFO mapreduce.ImportJobBase: Retrieved 12440 records.
22/04/08 18:59:12 INFO util.AppendUtils: Creating missing output directory - customers
22/04/08 18:59:12 INFO tool.ImportTool: Saving incremental import state to the metastore
22/04/08 18:59:12 INFO tool.ImportTool: Updated data for job: customer_import
[cloudera@quickstart ~]$
Points
to note
a. Even
though I supplied the password while creating the job, it still prompt me to
enter the password. We can address by supplying a password file while creating
the job. I will explain in later post.
b. From
the log messages, you can see the statement ‘Saving incremental import state to
the metastore’. Since we configured job to execute an incremental import
command, sqoop job stores the state (like last-value) in the metastore.
Location
of metastore
$HOME/.sqoop
[cloudera@quickstart ~]$ ls -lart $HOME/.sqoop
total 20
drwxrwxr-x 28 cloudera cloudera 4096 Apr 8 18:53 ..
-rw-rw-r-- 1 cloudera cloudera 6757 Apr 8 18:59 metastore.db.script
-rw-rw-r-- 1 cloudera cloudera 419 Apr 8 18:59 metastore.db.properties
drwxrwxr-x 2 cloudera cloudera 4096 Apr 8 18:59 .
‘sqoop’
use hsqldb to persist the job metadata.
Sample
content of metastore.db.script file
[cloudera@quickstart ~]$ cat $HOME/.sqoop/metastore.db.script
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE SQOOP_ROOT(VERSION INTEGER,PROPNAME VARCHAR(128) NOT NULL,PROPVAL VARCHAR(256),CONSTRAINT SQOOP_ROOT_UNQ UNIQUE(VERSION,PROPNAME))
CREATE MEMORY TABLE SQOOP_SESSIONS(JOB_NAME VARCHAR(64) NOT NULL,PROPNAME VARCHAR(128) NOT NULL,PROPVAL VARCHAR(1024),PROPCLASS VARCHAR(32) NOT NULL,CONSTRAINT SQOOP_SESSIONS_UNQ UNIQUE(JOB_NAME,PROPNAME,PROPCLASS))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO SQOOP_ROOT VALUES(NULL,'sqoop.hsqldb.job.storage.version','0')
INSERT INTO SQOOP_ROOT VALUES(0,'sqoop.hsqldb.job.info.table','SQOOP_SESSIONS')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqoop.tool','import','schema')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqoop.property.set.id','0','schema')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','verbose','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hcatalog.drop.and.create.table','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','incremental.last.value','12440','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','db.connect.string','jdbc:mysql://quickstart.cloudera:3306/retail_db','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.output.delimiters.escape','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.output.delimiters.enclose.required','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.input.delimiters.field','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','mainframe.input.dataset.type','p','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','split.limit','null','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hbase.create.table','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','db.require.password','true','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hdfs.append.dir','true','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','db.table','customers','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.input.delimiters.escape','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','accumulo.create.table','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','import.fetch.size','null','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.input.delimiters.enclose.required','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','db.username','root','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','reset.onemapper','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.output.delimiters.record','10','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','import.max.inline.lob.size','16777216','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqoop.throwOnError','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hbase.bulk.load.enabled','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hcatalog.create.table','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','db.clear.staging.table','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','incremental.col','customer_id','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.input.delimiters.record','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hdfs.warehouse.dir','/job-demo-1','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','enable.compression','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hive.overwrite.table','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hive.import','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.input.delimiters.enclose','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','accumulo.batch.size','10240000','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hive.drop.delims','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','customtool.options.jsonmap','{}','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.output.delimiters.enclose','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hdfs.delete-target.dir','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.output.dir','.','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.auto.compile.dir','true','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','relaxed.isolation','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','mapreduce.num.mappers','4','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','accumulo.max.latency','5000','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','import.direct.split.size','0','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqlconnection.metadata.transaction.isolation.level','2','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.output.delimiters.field','44','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','export.new.update','UpdateOnly','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','incremental.mode','AppendRows','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hdfs.file.format','TextFile','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqoop.oracle.escaping.disabled','true','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','codegen.compile.dir','/tmp/sqoop-cloudera/compile/d4e92057e12f71bb02c170e9c6aa826a','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','direct.import','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','temporary.dirRoot','_sqoop','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','hive.fail.table.exists','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','db.batch','false','SqoopOptions')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqoop.job.storage.implementations','com.cloudera.sqoop.metastore.hsqldb.HsqldbJobStorage,com.cloudera.sqoop.metastore.hsqldb.AutoHsqldbStorage','config')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','mapreduce.client.genericoptionsparser.used','true','config')
INSERT INTO SQOOP_SESSIONS VALUES('customer_import','sqoop.oracle.escaping.disabled','true','config')
Sample
content of metastore.db.properties file
[cloudera@quickstart ~]$ cat $HOME/.sqoop/metastore.db.properties
#HSQL Database Engine 1.8.0.10
#Fri Apr 08 18:59:12 PDT 2022
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=memory
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=no
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0
[cloudera@quickstart ~]$
At last
let’s confirm whether data is imported to HDFS or not by comparing the count of
records.
Number
of records in customers table.
mysql> select count(*) from customers;
+----------+
| count(*) |
+----------+
| 12440 |
+----------+
1 row in set (0.00 sec)
Let’s
count the same in HDFS
[cloudera@quickstart ~]$ hadoop fs -ls /job-demo-1
Found 1 items
drwxr-xr-x - cloudera supergroup 0 2022-04-08 18:59 /job-demo-1/customers
[cloudera@quickstart ~]$
[cloudera@quickstart ~]$ hadoop fs -ls /job-demo-1/customers
Found 4 items
-rw-r--r-- 1 cloudera cloudera 237222 2022-04-08 18:59 /job-demo-1/customers/part-m-00000
-rw-r--r-- 1 cloudera cloudera 238044 2022-04-08 18:59 /job-demo-1/customers/part-m-00001
-rw-r--r-- 1 cloudera cloudera 238238 2022-04-08 18:59 /job-demo-1/customers/part-m-00002
-rw-r--r-- 1 cloudera cloudera 240411 2022-04-08 18:59 /job-demo-1/customers/part-m-00003
[cloudera@quickstart ~]$
[cloudera@quickstart ~]$ hadoop fs -cat /job-demo-1/customers/* | wc -l
12440
Previous
Next
Home
