1.测试Oracle连接
bin/sqoop list-databases --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password
2.导入hdfs
3.1 导入
bin/sqoop import --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --target-dir /sqoop/oracle/esb_service_param --split-by PARAM_ID
3.2验证
hdfs dfs -ls /sqoop/oracle/esb_service_param
hdfs dfs -cat /sqoop/oracle/esb_service_param/part-m-00000
4.导入hbase
4.0直接新建并导入
bin/sqoop import --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --split-by PARAM_ID --hbase-create-table --hbase-table orcl_esb_service_param --hbase-row-key PARAM_ID --column-family info
报错
16/09/07 14:05:30 INFO mapreduce.HBaseImportJob: Creating missing HBase table orcl_esb_service_param
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.hadoop.hbase.HTableDescriptor.addFamily(Lorg/apache/hadoop/hbase/HColumnDescriptor;)V
原因
版本不匹配，环境上版本固定，所以选用手动新建hbase表，再导入数据
4.1新建hbase表
hbase shell
create 'orcl_esb_service_param', 'region'
4.2导入mysql数据到hbase
bin/sqoop import --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --split-by PARAM_ID --hbase-table orcl_esb_service_param --hbase-row-key PARAM_ID --column-family region
额外例子，导入hbase生成复合的rowKey
bin/sqoop import -D sqoop.hbase.add.row.key=true --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --split-by PARAM_ID --hbase-table lsy_test_multiple_table --hbase-row-key PARAM_ID,SERVICE_ID --column-family data
4.3验证
scan 'orcl_esb_service_param'
count 'orcl_esb_service_param'
5.导入hive
5.1新建hive表，并导入数据
bin/sqoop import --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --split-by PARAM_ID --hive-import --create-hive-table --target-dir /user/hive/warehouse/orcl_esb_service_param --hive-table orcl_esb_service_param
5.2 验证
show tables;
select * from orcl_esb_service_param;
select count(*) from orcl_esb_service_param;
6.增量
增量到hdfs
bin/sqoop import --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --target-dir /sqoop/oracle/esb_service_param --split-by PARAM_ID --incremental append --check-column PARAM_ID --last-value 33
返回
16/09/07 15:27:17 INFO tool.ImportTool: --incremental append
16/09/07 15:27:17 INFO tool.ImportTool: --check-column PARAM_ID
16/09/07 15:27:17 INFO tool.ImportTool: --last-value 34
16/09/07 15:27:17 INFO tool.ImportTool: (Consider saving this with 'sqoop job --create')
校验
hdfs dfs -cat /sqoop/oracle/esb_service_param/part-m-00004
增量到hbase
bin/sqoop import --connect jdbc:oracle:thin:@192.168.16.223:1521/orcl --username sitts --password password --table SITTS.ESB_SERVICE_PARAM --split-by PARAM_ID --hbase-table orcl_esb_service_param --hbase-row-key PARAM_ID --column-family region --incremental append --check-column PARAM_ID --last-value 33
返回
16/09/07 15:59:49 INFO tool.ImportTool: --incremental append
16/09/07 15:59:49 INFO tool.ImportTool: --check-column PARAM_ID
16/09/07 15:59:49 INFO tool.ImportTool: --last-value 34
16/09/07 15:59:49 INFO tool.ImportTool: (Consider saving this with 'sqoop job --create')
校验
scan 'orcl_esb_service_param'
