Apache Sqoop job 作业
创建job
创建一个名为testjob01的job，它可以从RDBMS表的数据导入到HDFS作业。
下面的命令用于创建一个从DB数据库的emp表导入到HDFS文件的作业。
sqoop job --create testjob01 -- import --connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoopresult333 \
--table emp --m 1
注意import前要有空格
这里遇到一个bug，缺少JSON的jar包
[root@hadoop01 sbin]# sqoop job --create jobtest -- import --connect jdbc:mysql://hadoop01:3306/userdb \
> --username root \
> --password 123456 \
> --target-dir /sqoopresult333 \
> --table emp --m 1
19/12/09 08:05:54 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
19/12/09 08:05:55 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
Exception in thread "main" java.lang.NoClassDefFoundError: org/json/JSONObject
at org.apache.sqoop.util.SqoopJsonUtil.getJsonStringforMap(SqoopJsonUtil.java:43)
at org.apache.sqoop.SqoopOptions.writeProperties(SqoopOptions.java:785)
at org.apache.sqoop.metastore.hsqldb.HsqldbJobStorage.createInternal(HsqldbJobStorage.java:399)
at org.apache.sqoop.metastore.hsqldb.HsqldbJobStorage.create(HsqldbJobStorage.java:379)
at org.apache.sqoop.tool.JobTool.createJob(JobTool.java:181)
at org.apache.sqoop.tool.JobTool.run(JobTool.java:294)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.lang.ClassNotFoundException: org.json.JSONObject
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 12 more
[root@hadoop01 sbin]#
在这个网站下载jar包，放到sqoop的lib目录下即可。
验证job
‘–list’ 参数是用来验证保存的作业。下面的命令用来验证保存Sqoop作业的列表。
sqoop job --list
[root@hadoop01 profile.d]# sqoop job --list
19/12/09 08:25:41 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Available jobs:
jobtest
testjob
[root@hadoop01 profile.d]#
检查job
‘–show’ 参数用于检查或验证特定的工作，及其详细信息。以下命令和样本输出用来验证一个名为testjob01的作业。
sqoop job --show testjob01
[root@hadoop01 lib]# sqoop job --show testjob01
Warning: /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /export/servers/sqoop-1.4.7.bin__hadoop-2.6.0/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
19/12/09 08:30:56 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password:
Job: testjob01
Tool: import
Options:
----------------------------
verbose = false
hcatalog.drop.and.create.table = false
db.connect.string = jdbc:mysql://hadoop01:3306/userdb
codegen.output.delimiters.escape = 0
codegen.output.delimiters.enclose.required = false
codegen.input.delimiters.field = 0
split.limit = null
hbase.create.table = false
mainframe.input.dataset.type = p
db.require.password = true
skip.dist.cache = false
hdfs.append.dir = false
db.table = emp
codegen.input.delimiters.escape = 0
accumulo.create.table = false
import.fetch.size = null
codegen.input.delimiters.enclose.required = false
db.username = root
reset.onemapper = false
codegen.output.delimiters.record = 10
import.max.inline.lob.size = 16777216
sqoop.throwOnError = false
hbase.bulk.load.enabled = false
hcatalog.create.table = false
db.clear.staging.table = false
codegen.input.delimiters.record = 0
enable.compression = false
hive.overwrite.table = false
hive.import = false
codegen.input.delimiters.enclose = 0
accumulo.batch.size = 10240000
hive.drop.delims = false
customtool.options.jsonmap = {}
codegen.output.delimiters.enclose = 0
hdfs.delete-target.dir = false
codegen.output.dir = .
codegen.auto.compile.dir = true
relaxed.isolation = false
mapreduce.num.mappers = 1
accumulo.max.latency = 5000
import.direct.split.size = 0
sqlconnection.metadata.transaction.isolation.level = 2
codegen.output.delimiters.field = 44
export.new.update = UpdateOnly
incremental.mode = None
hdfs.file.format = TextFile
sqoop.oracle.escaping.disabled = true
codegen.compile.dir = /tmp/sqoop-root/compile/46be1c1ea67765eb1ae0d9ed97252d51
direct.import = false
temporary.dirRoot = _sqoop
hdfs.target.dir = /sqoopresult333
hive.fail.table.exists = false
db.batch = false
[root@hadoop01 lib]#
执行job
‘–exec’ 选项用于执行保存的作业。下面的命令用于执行名称为testjob01的job
sqoop job --exec testjob01
[root@hadoop01 lib]# sqoop job --exec testjob01
19/12/09 08:33:18 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password:
19/12/09 08:33:23 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
19/12/09 08:33:23 INFO tool.CodeGenTool: Beginning code generation
19/12/09 08:33:23 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `emp` AS t LIMIT 1
19/12/09 08:33:23 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `emp` AS t LIMIT 1
19/12/09 08:33:23 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /export/servers/hadoop-2.6.0-cdh5.14.0
注: /tmp/sqoop-root/compile/d6c4c284e3a92591a5f929630597abb5/emp.java使用或覆盖了已过时的 API。
注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。
19/12/09 08:33:28 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/d6c4c284e3a92591a5f929630597abb5/emp.jar
19/12/09 08:33:28 WARN manager.MySQLManager: It looks like you are importing from mysql.
19/12/09 08:33:28 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
19/12/09 08:33:28 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
19/12/09 08:33:28 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
19/12/09 08:33:28 INFO mapreduce.ImportJobBase: Beginning import of emp
19/12/09 08:33:28 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
19/12/09 08:33:28 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
19/12/09 08:33:29 INFO client.RMProxy: Connecting to ResourceManager at hadoop01/192.168.100.201:8032
19/12/09 08:33:33 WARN hdfs.DFSClient: Caught exception
java.lang.InterruptedException
at java.lang.Object.wait(Native Method)
at java.lang.Thread.join(Thread.java:1252)
at java.lang.Thread.join(Thread.java:1326)
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)
19/12/09 08:33:33 WARN hdfs.DFSClient: Caught exception
java.lang.InterruptedException
at java.lang.Object.wait(Native Method)
at java.lang.Thread.join(Thread.java:1252)
at java.lang.Thread.join(Thread.java:1326)
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)
19/12/09 08:33:34 WARN hdfs.DFSClient: Caught exception
java.lang.InterruptedException
at java.lang.Object.wait(Native Method)
at java.lang.Thread.join(Thread.java:1252)
at java.lang.Thread.join(Thread.java:1326)
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)
19/12/09 08:33:35 INFO db.DBInputFormat: Using read commited transaction isolation
19/12/09 08:33:35 INFO mapreduce.JobSubmitter: number of splits:1
19/12/09 08:33:35 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1575849577977_0001
19/12/09 08:33:36 INFO impl.YarnClientImpl: Submitted application application_1575849577977_0001
19/12/09 08:33:36 INFO mapreduce.Job: The url to track the job: http://hadoop01:8088/proxy/application_1575849577977_0001/
19/12/09 08:33:36 INFO mapreduce.Job: Running job: job_1575849577977_0001
19/12/09 08:33:52 INFO mapreduce.Job: Job job_1575849577977_0001 running in uber mode : true
19/12/09 08:33:52 INFO mapreduce.Job: map 100% reduce 0%
19/12/09 08:33:54 INFO mapreduce.Job: Job job_1575849577977_0001 completed successfully
19/12/09 08:33:54 INFO mapreduce.Job: Counters: 32
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=0
FILE: Number of read operations=0
FILE: Number of large read operations=0
FILE: Number of write operations=0
HDFS: Number of bytes read=100
HDFS: Number of bytes written=174172
HDFS: Number of read operations=140
HDFS: Number of large read operations=0
HDFS: Number of write operations=6
Job Counters
Launched map tasks=1
Other local map tasks=1
Total time spent by all maps in occupied slots (ms)=0
Total time spent by all reduces in occupied slots (ms)=0
TOTAL_LAUNCHED_UBERTASKS=1
NUM_UBER_SUBMAPS=1
Total time spent by all map tasks (ms)=715
Total vcore-milliseconds taken by all map tasks=0
Total megabyte-milliseconds taken by all map tasks=0
Map-Reduce Framework
Map input records=7
Map output records=7
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=0
CPU time spent (ms)=530
Physical memory (bytes) snapshot=318537728
Virtual memory (bytes) snapshot=3091116032
Total committed heap usage (bytes)=252182528
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=202
19/12/09 08:33:54 INFO mapreduce.ImportJobBase: Transferred 170.0898 KB in 25.9669 seconds (6.5503 KB/sec)
19/12/09 08:33:54 INFO mapreduce.ImportJobBase: Retrieved 7 records.
[root@hadoop01 lib]# hadoop fs -ls /
Found 10 items
drwxr-xr-x - root supergroup 0 2019-12-05 21:27 /allowinsert_1
drwxr-xr-x - root supergroup 0 2019-12-05 21:38 /allowinsert_2
drwxr-xr-x - root supergroup 0 2019-11-07 16:12 /config
drwxr-xr-x - root supergroup 0 2019-11-20 15:13 /hivedatas
drwxr-xr-x - root supergroup 0 2019-12-09 08:33 /sqoopresult333
drwxr-xr-x - root supergroup 0 2019-11-06 14:21 /system
drwx------ - root supergroup 0 2019-12-05 21:04 /tmp
drwxr-xr-x - root supergroup 0 2019-12-05 20:57 /updateonly_1
drwxr-xr-x - root supergroup 0 2019-12-05 21:08 /updateonly_2
drwxr-xr-x - root supergroup 0 2019-11-20 14:20 /user
[root@hadoop01 lib]# hadoop fs -ls /sqoopresult333
Found 2 items
-rw-r--r-- 2 root supergroup 0 2019-12-09 08:33 /sqoopresult333/_SUCCESS
-rw-r--r-- 2 root supergroup 202 2019-12-09 08:33 /sqoopresult333/part-m-00000
[root@hadoop01 lib]# hadoop fs -cat /sqoopresult333/part-m-00000
1201,gopal,manager,50000,TP
1202,manisha,Proof reader,50000,TP
1203,khalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
1206,allen,admin,30000,tp
1207,woon,admin,40000,tp
[root@hadoop01 lib]#
免密执行job
sqoop在创建job时，使用–password-file参数，可以避免输入mysql密码，如果使用–password将出现警告，并且每次都要手动输入密码才能执行job，sqoop规定密码文件必须存放在HDFS上，并且权限必须是400。
并且检查sqoop的sqoop-site.xml是否存在如下配置：
(sqoop目录/conf/sqoop-site.xml)
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
<description>If true, allow saved passwords in the metastore.
</description>
</property>
创建一个密码文件（这里不能用vi）
echo -n "123456" > pwmysql.pwd
创建hdfs的目录
hadoop fs -mkdir -p /input/sqoop/pwd/
上传密码
hadoop fs -put pwmysql.pwd /input/sqoop/pwd/
修改权限为400
hadoop fs -chmod 400 /input/sqoop/pwd/pwmysql.pwd
测试免密
sqoop job --create testjob02 -- import --connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password-file /input/sqoop/pwd/pwmysql.pwd \
--target-dir /sqoopresult333 \
--table emp --m 1
sqoop job --exec testjob02
