sqoop数据同步工具的使用示例

sqoop数据同步工具的使用示例

1. sqoop帮助
sqoop help
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.

2. 常用的sqoop命令
eval (查询工具:可以查询各种数据库)
export(数据导出:一般是从hdfs导出到数据库)
import(数据导入:一般是从数据库导入到hdfs)
version (查询版本号)

3. sqoop eval 查询示例(--query可以传sql语句,也可以传call 存储过程名):
${sqoop_home}/sqoop eval \
-D mapreduce.map.memory.mb=2048 \
-D mapreduce.reduce.memory.mb=2048 \
--connect "${url}" \
--username "${user}" \
--password "${pwd}" \
--driver com.mysql.jdbc.Driver \
--query "$sql"


4. sqoop import 导入示例:
${sqoop_home}/sqoop import \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:oracle:thin:@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=11.22.33.44\)\(PORT=1521\)\)\(CONNECT_DATA=\(SERVER=DEDICATED\)\(SERVICE_NAME=TEST\)\)\) \
--username test \
--password 'test#pwd' \
--fields-terminated-by '|' \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-delims-replacement ' ' \
--outdir /data/sqoopcode \
--delete-target-dir \
--fetch-size 200 \
--map-column-hive PROPOSALNO=String,PRINTNO=String \
--target-dir /user/hive/warehouse/bak.db/test01/etl_date=${etldate} \
--query "select PROPOSALNO,PRINTNO,rownum as ROWKEY from test.test01 t where \$CONDITIONS" \
--split-by rowkey -m 1 

5. sqoop export 导出示例::
${sqoop_home}/sqoop export \
--connect jdbc:postgresql://11.22.33.44:11000/igistest?sslmode=disable&currentSchema=uat \
--username uat \
--password 'uat#test' \
--input-fields-terminated-by '|' \
--input-lines-terminated-by '\n' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--outdir /tpdata/data/sqoopcode \
--export-dir /user/hive/warehouse/bak.db/test01/etl_date=${etldate}/* \
--table uat.test01 \
--columns PROPOSALNO,PRINTNO \
-m 1 

6.sqoop version 查询版本号
sqoop version 
Sqoop 1.4.7