1、下载
http://archive.apache.org/dist/sqoop/1.4.7/
2、安装
tar zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
cp mysql-connector-java-5.1.46.jar sqoop-1.4.7.bin__hadoop-2.6.0/lib/
3、配置环境
1) 配置依赖
sqoop需要hadoop2.x环境,所以在配置环境里加相应的配置(感觉配置HADOOP_COMMON_HOME和HADOOP_MAPRED_HOME不太有效)
conf]$ cp sqoop-env-template.sh sqoop-env.sh
vim sqoop-env.sh
# Set Hadoop-specific environment variables here.
export HADOOP_HOME=${HOME}/tools/hadoop-2.6.0-cdh5.16.2
export HADOOP_CONF_DIR=${HADOOP_HOME}/etc/hadoop
#Set path to where bin/hadoop is available
#export HADOOP_COMMON_HOME=
#Set path to where hadoop-*-core.jar is available
#export HADOOP_MAPRED_HOME=
#set the path to where bin/hbase is available
#export HBASE_HOME=
#Set the path to where bin/hive is available
#export HIVE_HOME=
#Set the path for where zookeper config dir is
#export ZOOCFGDIR=
4、常用命令
https://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html
[work@host01-client sqoop-1.4.7.bin__hadoop-2.6.0]$ bin/sqoop help
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /home/work/tools/temp/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
22/08/20 09:03:41 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
usage: sqoop COMMAND [ARGS]
Available commands:
codegen Generate code to interact with database records
create-hive-table Import a table definition into Hive
eval Evaluate a SQL statement and display the results
export Export an HDFS directory to a database table
help List available commands
import Import a table from a database to HDFS
import-all-tables Import tables from a database to HDFS
import-mainframe Import datasets from a mainframe server to HDFS
job Work with saved jobs
list-databases List available databases on a server
list-tables List available tables in a database
merge Merge results of incremental imports
metastore Run a standalone Sqoop metastore
version Display version information
See 'sqoop help COMMAND' for information on a specific command.
5、导出
1) 查看数据库列表
bin/sqoop list-databases \
--connect jdbc:mysql://host02:3306/miniapp_data?useUnicodeCharactor=true \
--username data \
--password 123456
2) 查看库表
bin/sqoop list-tables \
--connect jdbc:mysql://host02:3306/miniapp_data?useUnicodeCharactor=true \
--username data \
--password 123456
3) mysql数据导入到hdfs
export HADOOP_COMMON_HOME=${HOME}/tools/hadoop-2.6.0-cdh5.16.2
export HADOOP_MAPRED_HOME=${HOME}/tools/hadoop-2.6.0-cdh5.16.2
bin/sqoop import \
--mapreduce-job-name sqoop_mysql_2_hdfs \
--connect jdbc:mysql://host02:3306/miniapp_data?useUnicodeCharactor=true \
--username data \
--password 123456 \
--table monitor_alarm_record_log \
--columns id,alarm_key \
--where 'id < 200' \
--hadoop-mapred-home ${HOME}/tools/hadoop-2.6.0-cdh5.16.2 \
--hadoop-home ${HOME}/tools/hadoop-2.6.0-cdh5.16.2 \
--target-dir hdfs://hadoop01:8020/data/sqoop_data/monitor_alarm_record_log \
--delete-target-dir \
--fields-terminated-by '\t' \
--num-mappers 2 \
--split-by id \
4) 压缩导入
(1) 把lzo的jar包复制到sqoop_home的lib下: sqoop-1.4.7.bin__hadoop-2.6.0]$ cp hadoop-lzo-0.4.20.jar lib/
(2) 复制命令:
bin/sqoop import \
--mapreduce-job-name sqoop_mysql_2_hdfs \
--connect jdbc:mysql://host02:3306/miniapp_data?useUnicodeCharactor=true \
--username data \
--password 123456 \
--table monitor_alarm_record_log \
--columns id,alarm_key \
--where 'id < 200' \
--boundary-query 'SELECT MIN(`id`)+10, MAX(`id`)-10 FROM `monitor_alarm_record_log` WHERE ( id < 200 )' \
--target-dir hdfs://hadoop01:8020/data/sqoop_data/monitor_alarm_record_log \
--delete-target-dir \
--fields-terminated-by '\t' \
--num-mappers 2 \
--split-by id \
--compress \
--compression-codec lzop \
--null-non-string '\\N' \
--null-string '\\N'
5) # query:
bin/sqoop import \
--mapreduce-job-name sqoop_mysql_2_hdfs \
--connect jdbc:mysql://host02:3306/miniapp_data?useUnicodeCharactor=true \
--username data \
--password 123456 \
--query 'select id, alarm_key from monitor_alarm_record_log where id < 200 and $CONDITIONS ' \
--target-dir hdfs://hadoop01:8020/data/sqoop_data/monitor_alarm_record_log \
--delete-target-dir \
--fields-terminated-by '\t' \
--num-mappers 1 \
--split-by id \
--compress \
--compression-codec lzop \
--null-non-string '\\N' \
--null-string '\\N'
注意:
1) --hadoop-mapred-home ${HOME}/tools/hadoop-2.6.0-cdh5.16.2
--hadoop-home ${HOME}/tools/hadoop-2.6.0-cdh5.16.2
这两个参数需要进一步验证有效性
1) --append and --delete-target-dir can not be used together.
2) --autoreset-to-one-mapper and --split-by cannot be used together.
6、其它命令
sqoop-1.4.7.bin__hadoop-2.6.0]$ bin/sqoop import --help
usage: sqoop import [GENERIC-ARGS] [TOOL-ARGS]
Common arguments:
--connect <jdbc-uri> Specify JDBC connect string
--connection-manager <class-name> Specify connection manager class name
--connection-param-file <properties-file> Specify connection parameters file
--driver <class-name> Manually specify JDBC driver class to use
--hadoop-home <hdir> Override $HADOOP_MAPRED_HOME_ARG
--hadoop-mapred-home <dir> Override $HADOOP_MAPRED_HOME_ARG
--help Print usage instructions
--metadata-transaction-isolation-level <isolationlevel> Defines the transaction isolation level for metadata queries. For more details check java.sql.Connection javadoc or the JDBC specificaiton
--oracle-escaping-disabled <boolean> Disable the escaping mechanism of the Oracle/OraOop connection managers
-P Read password from console
--password <password> Set authentication password
--password-alias <password-alias> Credential provider password alias
--password-file <password-file> Set authentication password file path
--relaxed-isolation Use read-uncommitted isolation for imports
--skip-dist-cache Skip copying jars to distributed cache
--temporary-rootdir <rootdir> Defines the temporary root directory for the import
--throw-on-error Rethrow a RuntimeException on error occurred during the job
--username <username> Set authentication username
--verbose Print more information while working
Import control arguments:
--append Imports data in append mode
--as-avrodatafile Imports data to Avro data files
--as-parquetfile Imports data to Parquet files
--as-sequencefile Imports data to SequenceFiles
--as-textfile Imports data as plain text
(default)
--autoreset-to-one-mapper Reset the
number of
mappers to
one mapper
if no split
key
available
--boundary-query <statement> Set boundary query for retrieving max and min value of the primary key
--columns <col,col,col...> Columns to import from table
--compression-codec <codec> Compression codec to use for import
--delete-target-dir Imports data in delete mode
--direct Use direct import fast path
--direct-split-size <n> Split the
input stream
every 'n'
bytes when
importing in
direct mode
-e,--query <statement> Import results of SQL 'statement'
--fetch-size <n> Set number 'n' of rows to fetch
from the database when more rows are needed Set the maximum size for an inline LOB
-m,--num-mappers <n> Use 'n' map tasks to import in parallel
--mapreduce-job-name <name> Set name for generated mapreduce job Key column to use to join results
--split-by <column-name> Column of the table used to split work units
--split-limit <size> Upper Limit of rows per split for split columns of Date/Time/Timestamp and integer types. For date or timestamp fields it is calculated in seconds. split-limit should be greater than 0
--table <table-name> Table to read HDFS plain table destination
--validate Validate the copy using the configured validator
--validation-failurehandler <validation-failurehandler> Fully qualified class name for ValidationFailureHandler
--validation-threshold <validation-threshold> Fully qualified class name
for ValidationThreshold
--validator <validator> Fully qualified class name for the Validator
--warehouse-dir <dir> HDFS parent for table destination
--where <where clause> WHERE clause to use during import
-z,--compress Enable compression
Incremental import arguments:
--check-column <column> Source column to check for incremental change
--incremental <import-type> Define an incremental import of type 'append' or 'lastmodified'
--last-value <value> Last imported value in the incremental check column
Output line formatting arguments:
--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
Input parsing arguments:
--input-enclosed-by <char> Sets a required field encloser
--input-escaped-by <char> Sets the input escape character
--input-fields-terminated-by <char> Sets the input field separator
--input-lines-terminated-by <char> Sets the input end-of-line char
--input-optionally-enclosed-by <char> Sets a field enclosing character
Hive arguments:
--create-hive-table Fail if the target hive
table exists
--external-table-dir <hdfs path> Sets where the external
table is in HDFS
--hive-database <database-name> Sets the database name to
use when importing to hive
--hive-delims-replacement <arg> Replace Hive record \0x01
and row delimiters (\n\r)
from imported string fields
with user-defined string
--hive-drop-import-delims Drop Hive record \0x01 and
row delimiters (\n\r) from
imported string fields
--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
--hive-partition-key <partition-key> Sets the partition key to
use when importing to hive
--hive-partition-value <partition-value> Sets the partition value to
use when importing to hive
--hive-table <table-name> Sets the table name to use
when importing to hive
--map-column-hive <arg> Override mapping for
specific column to hive
types.
HBase arguments:
--column-family <family> Sets the target column family for the
import
--hbase-bulkload Enables HBase bulk loading
--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> Import to <table> in HBase
HCatalog arguments:
--hcatalog-database <arg> HCatalog database name
--hcatalog-home <hdir> Override $HCAT_HOME
--hcatalog-partition-keys <partition-key> Sets the partition keys to use when
importing to hive
--hcatalog-partition-values <partition-value> Sets the partition values to use when importing to hive
--hcatalog-table <arg> HCatalog table name
--hive-home <dir> Override $HIVE_HOME
--hive-partition-key <partition-key> Sets the partition key to use when importing to hive
--hive-partition-value <partition-value> Sets the partition value to use when importing to hive
--map-column-hive <arg> Override mapping for specific column to hive types.
HCatalog import specific options:
--create-hcatalog-table Create HCatalog before import
--drop-and-create-hcatalog-table Drop and Create HCatalog before
import
--hcatalog-storage-stanza <arg> HCatalog storage stanza for table
creation
Accumulo arguments:
--accumulo-batch-size <size> Batch size in bytes
--accumulo-column-family <family> Sets the target column family for
the import
--accumulo-create-table If specified, create missing
Accumulo tables
--accumulo-instance <instance> Accumulo instance name.
--accumulo-max-latency <latency> Max write latency in milliseconds
--accumulo-password <password> Accumulo password.
--accumulo-row-key <col> Specifies which input column to use as the row key
--accumulo-table <table> Import to <table> in Accumulo
--accumulo-user <user> Accumulo user name.
--accumulo-visibility <vis> Visibility token to be applied to all rows imported
--accumulo-zookeepers <zookeepers> Comma-separated list of
zookeepers (host:port)
Code generation arguments:
--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.
--escape-mapping-column-names <boolean> Disable special characters
escaping in column names
--input-null-non-string <null-str> Input null non-string representation
--input-null-string <null-str> Input null string representation
--jar-file <file> Disable code generation; use specified jar
--map-column-java <arg> Override mapping for specific columns to java types
--null-non-string <null-str> Null non-string representation
--null-string <null-str> Null string representation
--outdir <dir> Output directory for generated code
--package-name <name> Put auto-generated classes in this package
Generic Hadoop command-line arguments:
(must preceed any tool-specific arguments)
Generic options supported are
-conf <configuration file> specify an application configuration file
-D <property=value> use value for given property
-fs <local|namenode:port> specify a namenode
-jt <local|resourcemanager:port> specify a ResourceManager
-files <comma separated list of files> specify comma separated files to be copied to the map reduce cluster
-libjars <comma separated list of jars> specify comma separated jar files to include in the classpath.
-archives <comma separated list of archives> specify comma separated archives to be unarchived on the compute machines.
The general command line syntax is
bin/hadoop command [genericOptions] [commandOptions]
At minimum, you must specify --connect and --table
Arguments to mysqldump and other subprograms may be supplied
after a '--' on the command line.