Sqoop使用和简介

Sqoop 工具是hadoop环境下连接关系数据库，和hadoop存储系统的桥梁，支持多种关系数据源和hive,hdfs,hbase的相互导入。一般情况下，关系数据表存在于线上环境的备份环境，需要每天进行数据导入，根据每天的数据量而言，sqoop可以全表导入，对于每天产生的数据量不是很大的情形可以全表导入，但是sqoop也提供了增量数据导入的机制。

下面介绍几个常用的sqoop的命令，以及一些参数：

 

 

序号	命令/command	类	说明
1	impor	ImportTool	从关系型数据库中导入数据(来自表或者查询语句)到HDFS中
2	export	ExportTool	将HDFS中的数据导入到关系型数据库中
3	codegen	CodeGenTool	获取数据库中某张表数据生成Java并打成jar包
4	create-hive-table	CreateHiveTableTool	创建Hive表
5	eval	EvalSqlTool	查看SQL执行结果
6	import-all-tables	ImportAllTablesTool	导入某个数据库下所有表到HDFS中
7	job	JobTool
8	list-databases	ListDatabasesTool	列出所有数据库名
9	list-tables	ListTablesTool	列出某个数据库下所有表
10	merge	MergeTool
11	metastore	MetastoreTool
12	help	HelpTool	查看帮助
13	version	VersionTool	查看版本

 

 

接着列出Sqoop的各种通用参数,然后针对以上13个命令列出他们自己的参数.Sqoop通用参数又分

Common arguments

Incrementalimport arguments

Outputline formatting arguments

Inputparsing arguments,Hive arguments

HBasearguments

GenericHadoop command-line arguments

 

 1.Common arguments通用参数,主要是针对关系型数据库链接的一些参数

序号	参数	说明	样例
1	connect	连接关系型数据库的URL	jdbc:mysql://localhost/sqoop_datas
2	connection-manager	连接管理类,一般不用
3	driver	连接驱动
4	hadoop-home	hadoop目录	/home/hadoop
5	help	查看帮助信息
6	password	连接关系型数据库的密码
7	username	链接关系型数据库的用户名
8	verbose	查看更多的信息,其实是将日志级别调低	该参数后面不接值

 Importcontrol arguments:

Argument	Description
--append	Append data to an existing dataset in HDFS
--as-avrodatafile	Imports data to Avro Data Files
--as-sequencefile	Imports data to SequenceFiles
--as-textfile	Imports data as plain text (default)
--boundary-query <statement>	Boundary query to use for creating splits
--columns <col,col,col…>	Columns to import from table
--direct	Use direct import fast path
--direct-split-size <n>	Split the input stream every n bytes when importing in direct mode
--inline-lob-limit <n>	Set the maximum size for an inline LOB
-m,--num-mappers <n>	Use n map tasks to import in parallel
-e,--query <statement>	Import the results of statement.
--split-by <column-name>	Column of the table used to split work units
--table <table-name>	Table to read
--target-dir <dir>	HDFS destination dir
--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
--null-non-string <null-string>	The string to be written for a null value for non-string columns

 

 

Incrementalimport arguments:

Argument	Description
--check-column (col)	Specifies the column to be examined when determining which rows to import.
--incremental (mode)	Specifies how Sqoop determines which rows are new. Legal values for mode include append and lastmodified.
--last-value (value)	Specifies the maximum value of the check column from the previous import.

 

 

Output lineformatting arguments:

Argument	Description
--enclosed-by <char>	Sets a required field enclosing character
--escaped-by <char>	Sets the escape character
--fields-terminated-by <char>	Sets the field separator character
--lines-terminated-by <char>	Sets the end-of-line character
--mysql-delimiters	Uses MySQL’s default delimiter set: fields: , lines: \n escaped-by: \ optionally-enclosed-by: '
--optionally-enclosed-by <char>	Sets a field enclosing character

 

 

Hivearguments:

Argument	Description
--hive-home <dir>	Override $HIVE_HOME
--hive-import	Import tables into Hive (Uses Hive’s default delimiters if none are set.)
--hive-overwrite	Overwrite existing data in the Hive table.
--create-hive-table	If set, then the job will fail if the target hive
	table exits. By default this property is false.
--hive-table <table-name>	Sets the table name to use when importing to Hive.
--hive-drop-import-delims	Drops \n, \r, and \01 from string fields when importing to Hive.
--hive-delims-replacement	Replace \n, \r, and \01 from string fields with user defined string when importing to Hive.
--hive-partition-key	Name of a hive field to partition are sharded on
--hive-partition-value <v>	String-value that serves as partition key for this imported into hive in this job.
--map-column-hive <map>	Override default mapping from SQL type to Hive type for configured columns.

 

 

HBasearguments:

Argument	Description
--column-family <family>	Sets the target column family for the import
--hbase-create-table	If specified, create missing HBase tables
--hbase-row-key <col>	Specifies which input column to use as the row key
--hbase-table <table-name>	Specifies an HBase table to use as the target instead of HDFS

 

 

Codegeneration arguments:

Argument	Description
--bindir <dir>	Output directory for compiled objects
--class-name <name>	Sets the generated class name. This overrides --package-name. When combined with --jar-file, sets the input class.
--jar-file <file>	Disable code generation; use specified jar
--outdir <dir>	Output directory for generated code
--package-name <name>	Put auto-generated classes in this package
--map-column-java <m>	Override default mapping from SQL type to Java type for configured columns.