sqoop 参数

sqoop import工具
 
        sqoop数据导入,依赖hadoop的mapreduce功能实现,如果在配置hadoop时,没有配置mapreduce,则会导致导入数据失败。
        支持导入数据到 hadoop,hbase,hive
 
sqoop import 指令参数如下所示
Argument
Description
--append
将数据追加到HDFS中已经存在的DataSet中
--as-avrodatafile
Imports data to Avro Data Files
--as-sequencefile
Imports data to SequenceFiles
--as-textfile
Imports data as plain text (default)
--as-parquetfile
Imports data to Parquet Files
--boundary-query <statement>
Boundary query to use for creating splits
--columns <col,col,col…>
指定要导入的关系数据表中的字段
--delete-target-dir
向指定目录导入数据时,如果目录存在,则删除之
--direct
使用自带的数据库工具,导出数据,增加效率
--fetch-size <n>
Number of entries to read from database at once.
--inline-lob-limit <n>
Set the maximum size for an inline LOB
-m,--num-mappers <n>
启动n个map并行导入数据  <并行mapreducre任务>
-e,--query <statement>
根据查询结果做数据导入
--split-by <column-name>
Column of the table used to split work units. Cannot be used with --autoreset-to-one-mapper option.
--split-limit <n>
Upper Limit for each split size. This only applies to Integer and Date columns. For date or timestamp fields it is calculated in seconds.
--autoreset-to-one-mapper
Import should use one mapper if a table has no primary key and no split-by column is provided. Cannot be used with --split-by <col> option.
--table <table-name>
关系数据库的数据表名
--target-dir <dir>
指定存储在HDFS上的存储路径,直接写行如 /usr/lsl这种格式即可,不需要事先创建文件夹
--temporary-rootdir <dir>
在使用sqoop import导入数据时,会生成临时文件目录,可以通过才参数修改默认的_sqoop目录
--warehouse-dir <dir>
HDFS parent for table destination
--where <where clause>
WHERE clause to use during import
-z,--compress
Enable compression
--compression-codec <c>
Use Hadoop codec (default gzip)
--null-string <null-string>
The string to be written for a null value for string columns
string类型空值的替换符(hive中null用\n标识)
--null-non-string <null-string>
The string to be written for a null value for non-string columns
非字符串类型的空值替换符
 
sqoop并发导入
    --num-mappers   
            默认情况下,该参数指定的并发数目,并使用数据表的主键作为其并发的依据。如指定数目为4,sqoop发现准备导入表的主键id在1-2000,则每一个map任务会均匀分配,第一个任务操作目标为1-500,第二个501-1000,第三个1001-1500,第四个1501-2000.
                但是假如在主键id的分配中,数据并非均匀分配的,中间存在缺漏数据的情况,这时就需要手动指定其它列做为并发任务的拆分依据。参数为--split-by
    --split-by
                在数据导入时,指定多个map任务并发时的分配依据列。假如一张表在导入时没有主键,也必须指定该参数。
    --split-limit
                限制每次操作的数据量大小,根据map限制的大小与--num-mappers设置的冲突,则以该参数为准
 
操作实例
将数据库中的一张表导入到hdfs
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.6.68:33060/testdata  --username root --password lovelsl  --table nodes -m 1
Warning: /lovelsl/sqoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
....
此为默认导入,没有指定导入后的具体存储位置,一般默认保存在HDFS上,具体目录为/user/root/nodes
 
以下是默认导入的数据,352,353,354是每一行记录的第一列,为自增的id
352,lsltest_abcd18,8,1108b8e8-a3c0-11e9-a692-005056b7924b,11577f3c-a3c0-11e9-a692-005056b7924b,TestClient-00018,work_group,{"eth": [{"mac": "mac", "ipv6": "ipv6", "name": "testinterface", "ipv4": "c0a80a13"}]},TestClient-00018,6,sys version = microtest,2019-07-16 22:42:16.0,2019-07-16 22:42:16.0,2019-07-16 22:42:16.0,mac,192.168.10.19,ipv6,2019-07-16 22:42:16.0,,3232238099
353,lsltest_abcd19,8,1108b8e8-a3c0-11e9-a692-005056b7924b,11577f3c-a3c0-11e9-a692-005056b7924b,TestClient-00019,work_group,{"eth": [{"mac": "mac", "ipv6": "ipv6", "name": "testinterface", "ipv4": "c0a80a14"}]},TestClient-00019,6,sys version = microtest,2019-07-16 22:42:16.0,2019-07-16 22:42:16.0,2019-07-16 22:42:16.0,mac,192.168.10.20,ipv6,2019-07-16 22:42:16.0,,3232238100
354,lsltest_abcd20,8,1108b8e8-a3c0-11e9-a692-005056b7924b,11577f3c-a3c0-11e9-a692-005056b7924b,TestClient-00020,work_group,{"eth": [{"mac": "mac", "ipv6": "ipv6", "name": "testinterface", "ipv4": "c0a80a15"}]},TestClient-00020,6,sys version = microtest,2019-07-16 22:42:16.0,2019-07-16 22:42:16.0,2019-07-16 22:42:16.0,mac,192.168.10.21,ipv6,2019-07-16 22:42:16.0,,3232238101
 
向表中插入数据,继续向hdfs中追加数据
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.6.68:33060/testdata  --username root --password lovelsl  --table nodes -m 1  --append
Warning: /lovelsl/sqoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
.....
追加发数据后,会发现在hdfs中新增了一个文件,与原文件相比增加了后来插入的数据。路径同上
 
将数据库中的一张表内容导入到hdfs中的指定目录
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.6.68:33060/testdata  --username root --password lovelsl  --table nodes -m 1  --target-dir /usr/lsl/testdata
Warning: /lovelsl/sqoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
 
使用数据库自带的导出工具,将数据库中的一张表内容导入到hdfs中的指定目录
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.6.68:33060/testdata  --username root --password lovelsl  --table node_products -m 1  --target-dir /usr/lsl/testdata/node_products  --direct
 
向表中插入数据,如果目标文件夹存在则删除之
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.6.68:33060/testdata  --username root --password lovelsl  --table node_products -m 1  --target-dir /usr/lsl/testdata/node_products  --direct  --delete-target-dir
 
 
根据查询结果向HDFS中插入数据
    注意一定要在最后带上$CONDITIONS标记,sql语句为select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8
      68数据库没有数据了,换个数据库继续
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.10.48:33060/testdata  --username root --password lovelsl --query 'select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and  $CONDITIONS'  -m 1  --target-dir /usr/lsl/testdata/test1  --direct  --delete-target-dir
Warning: /lovelsl/sqoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /lovelsl/sqoop/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /lovelsl/sqoop/sqoop/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
19/07/18 03:40:34 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
19/07/18 03:40:34 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/07/18 03:40:34 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
19/07/18 03:40:34 INFO tool.CodeGenTool: Beginning code generation
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Thu Jul 18 03:40:35 EDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
19/07/18 03:40:35 INFO manager.SqlManager: Executing SQL statement: select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 0)
19/07/18 03:40:35 INFO manager.SqlManager: Executing SQL statement: select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 0)
19/07/18 03:40:35 INFO manager.SqlManager: Executing SQL statement: select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 0)
19/07/18 03:40:35 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /lovelsl/hadoop/hadoop
Note: /tmp/sqoop-root/compile/daccbe9a1ca7c2d776346b2a8778dc13/QueryResult.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
19/07/18 03:40:39 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/daccbe9a1ca7c2d776346b2a8778dc13/QueryResult.jar
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/lovelsl/hadoop/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/lovelsl/hbase/hbase/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
19/07/18 03:40:41 INFO tool.ImportTool: Destination directory /usr/lsl/testdata/test1 deleted.
19/07/18 03:40:41 INFO mapreduce.ImportJobBase: Beginning query import.
19/07/18 03:40:41 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
19/07/18 03:40:41 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
19/07/18 03:40:41 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
Thu Jul 18 03:40:44 EDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
19/07/18 03:40:44 INFO db.DBInputFormat: Using read commited transaction isolation
19/07/18 03:40:44 INFO mapreduce.JobSubmitter: number of splits:1
19/07/18 03:40:44 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1562845994825_0030
19/07/18 03:40:45 INFO impl.YarnClientImpl: Submitted application application_1562845994825_0030
19/07/18 03:40:45 INFO mapreduce.Job: The url to track the job: http://localhost:8088/proxy/application_1562845994825_0030/
19/07/18 03:40:45 INFO mapreduce.Job: Running job: job_1562845994825_0030
19/07/18 03:40:59 INFO mapreduce.Job: Job job_1562845994825_0030 running in uber mode : false
19/07/18 03:40:59 INFO mapreduce.Job:  map 0% reduce 0%
19/07/18 03:41:08 INFO mapreduce.Job:  map 100% reduce 0%
19/07/18 03:41:08 INFO mapreduce.Job: Job job_1562845994825_0030 completed successfully
19/07/18 03:41:09 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=139581
        FILE: Number of read operations=0
        FILE: Number of large read operations=0
        FILE: Number of write operations=0
        HDFS: Number of bytes read=87
        HDFS: Number of bytes written=16193
        HDFS: Number of read operations=4
        HDFS: Number of large read operations=0
        HDFS: Number of write operations=2
    Job Counters
        Launched map tasks=1
        Other local map tasks=1
        Total time spent by all maps in occupied slots (ms)=6161
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=6161
        Total vcore-milliseconds taken by all map tasks=6161
        Total megabyte-milliseconds taken by all map tasks=6308864
    Map-Reduce Framework
        Map input records=42
        Map output records=42
        Input split bytes=87
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=65
        CPU time spent (ms)=1170
        Physical memory (bytes) snapshot=155136000
        Virtual memory (bytes) snapshot=2085097472
        Total committed heap usage (bytes)=125763584
    File Input Format Counters
        Bytes Read=0
    File Output Format Counters
        Bytes Written=16193
19/07/18 03:41:09 INFO mapreduce.ImportJobBase: Transferred 15.8135 KB in 27.5259 seconds (588.283 bytes/sec)
19/07/18 03:41:09 INFO mapreduce.ImportJobBase: Retrieved 42 records.
[root@localhost ~]#
 
    说明,以上查询使用双引号会报错,推荐查询使用单引号。只有当使用了where a = 'abc'时,才使用双引号
              sqoop本身不支持台复杂的查询
            
                以上语句修改成如下所示时报错
                    'select nodes.*,node_products.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and  $CONDITIONS
                    报错显示nodes表和node_products表都有id,造成数据冲突。导入后的数据,必须保证对应列只有一列
 
 
根据查询结果向HDFS中插入数据,并指定任务数
[root@localhost ~]# sqoop import --connect jdbc:mysql://192.168.10.80:33060/testdata  --username root --password lovelsl --query 'select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and  $CONDITIONS'  -m 2  --target-dir /usr/lsl/testdata/test1  --direct  --delete-target-dir  --split-by nodes.id
Warning: /lovelsl/sqoop/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /lovelsl/sqoop/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /lovelsl/sqoop/sqoop/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
19/07/19 00:40:28 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
19/07/19 00:40:28 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/07/19 00:40:28 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
19/07/19 00:40:28 INFO tool.CodeGenTool: Beginning code generation
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Fri Jul 19 00:40:28 EDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
19/07/19 00:40:29 INFO manager.SqlManager: Executing SQL statement: select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 0)
19/07/19 00:40:29 INFO manager.SqlManager: Executing SQL statement: select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 0)
19/07/19 00:40:29 INFO manager.SqlManager: Executing SQL statement: select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 0)
19/07/19 00:40:29 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /lovelsl/hadoop/hadoop
Note: /tmp/sqoop-root/compile/af4efcc4ed33a1d18df880aa1860c393/QueryResult.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
19/07/19 00:40:33 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/af4efcc4ed33a1d18df880aa1860c393/QueryResult.jar
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/lovelsl/hadoop/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/lovelsl/hbase/hbase/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
19/07/19 00:40:34 INFO tool.ImportTool: Destination directory /usr/lsl/testdata/test1 deleted.
19/07/19 00:40:34 INFO mapreduce.ImportJobBase: Beginning query import.
19/07/19 00:40:34 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
19/07/19 00:40:35 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
19/07/19 00:40:35 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
Fri Jul 19 00:40:37 EDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
19/07/19 00:40:37 INFO db.DBInputFormat: Using read commited transaction isolation
19/07/19 00:40:37 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(t1.id), MAX(t1.id) FROM (select nodes.*  from nodes JOIN node_products ON nodes.uid=node_products.uid  where node_products.flag=1 and nodes.node_flag=8  and   (1 = 1) ) AS t1
19/07/19 00:40:37 INFO db.IntegerSplitter: Split size: 50; Num splits: 2 from: 311 to: 411
19/07/19 00:40:38 INFO mapreduce.JobSubmitter: number of splits:2
19/07/19 00:40:38 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1562845994825_0033
19/07/19 00:40:38 INFO impl.YarnClientImpl: Submitted application application_1562845994825_0033
19/07/19 00:40:38 INFO mapreduce.Job: The url to track the job: http://localhost:8088/proxy/application_1562845994825_0033/
19/07/19 00:40:38 INFO mapreduce.Job: Running job: job_1562845994825_0033
19/07/19 00:40:52 INFO mapreduce.Job: Job job_1562845994825_0033 running in uber mode : false
19/07/19 00:40:52 INFO mapreduce.Job:  map 0% reduce 0%
19/07/19 00:41:05 INFO mapreduce.Job:  map 50% reduce 0%
19/07/19 00:41:07 INFO mapreduce.Job:  map 100% reduce 0%
19/07/19 00:41:08 INFO mapreduce.Job: Job job_1562845994825_0033 completed successfully
19/07/19 00:41:08 INFO mapreduce.Job: Counters: 31
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=280100
        FILE: Number of read operations=0
        FILE: Number of large read operations=0
        FILE: Number of write operations=0
        HDFS: Number of bytes read=221
        HDFS: Number of bytes written=37923
        HDFS: Number of read operations=8
        HDFS: Number of large read operations=0
        HDFS: Number of write operations=4
    Job Counters
        Killed map tasks=1
        Launched map tasks=2
        Other local map tasks=2
        Total time spent by all maps in occupied slots (ms)=24279
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=24279
        Total vcore-milliseconds taken by all map tasks=24279
        Total megabyte-milliseconds taken by all map tasks=24861696
    Map-Reduce Framework
        Map input records=101
        Map output records=101
        Input split bytes=221
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=314
        CPU time spent (ms)=2520
        Physical memory (bytes) snapshot=302219264
        Virtual memory (bytes) snapshot=4169879552
        Total committed heap usage (bytes)=251527168
    File Input Format Counters
        Bytes Read=0
    File Output Format Counters
        Bytes Written=37923
19/07/19 00:41:08 INFO mapreduce.ImportJobBase: Transferred 37.0342 KB in 33.8512 seconds (1.094 KB/sec)
19/07/19 00:41:08 INFO mapreduce.ImportJobBase: Retrieved 101 records.
[root@localhost ~]#
 
 
 
posted @ 2020-12-19 22:46  dos_hello_world  阅读(548)  评论(0)    收藏  举报