sqoop：数据转换工具

朴灿烈づ我的快乐病毒、 2022-05-16 12:04 226阅读 0赞

要点如下：

1.  安装配置
2.  关系型数据库<-->hadoop类型数据：互相转换
3.  使用 sqoop job

#测试jdbc连接
    sqoop list-databases --connect 'jdbc:mysql://localhost:3306' --username 'root' --password 123456
    
    #查询jdbc数据
    sqoop eval --connect jdbc:mysql://localhost:3306/test --username root --password 123456 --query 'select * from b'

--------------------

![70][]

出现的异常： ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf

( ---->解决办法： cp /soft/hive/lib/hive-common-\*\*.jar    /soft/sqoop/lib/)

![70 1][]

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class org.apache.derby.jdbc.AutoloadedDriver40
    	at java.lang.Class.forName0(Native Method)
    	at java.lang.Class.forName(Class.java:348)
    	at java.sql.DriverManager.isDriverAllowed(DriverManager.java:556)
    	at java.sql.DriverManager.getConnection(DriverManager.java:661)
    	at java.sql.DriverManager.getConnection(DriverManager.java:247)
    	at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904)
    	at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:59)
    	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763)
    	at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
    	at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
    	at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
    	at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
    	at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:327)
    	at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1872)
    	at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1671)
    	at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
    	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:501)
    	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
    	at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
    	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
    	at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

解决\[缺少derby jar包\]： cp /soft/jdk1.8/db/lib/derby.jar /soft/sqoop/lib/

--------------------

mysql 数据--> hadoop( hdfs, hive, hbase)

#mysql: 表结构
    CREATE TABLE `mysql_music` (
    	  `id` int(11) NOT NULL AUTO_INCREMENT,
    	  `mname` varchar(100) DEFAULT NULL,
    	  `mtime` varchar(20) DEFAULT NULL,
    	  `malbum` varchar(100) DEFAULT NULL,
    	  `msinger` varchar(100) DEFAULT NULL,
    	  `mstyle` varchar(20) DEFAULT NULL,
    	  `mlanguage` varchar(255) DEFAULT NULL,
    	  `misfree` int(11) DEFAULT NULL,
    	  PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

mysql-->hdfs

--数据导入到hdfs文件系统===========================
    ---delete模式
    sqoop import \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456  \
    --target-dir /user/centos/music \
    --delete-target-dir -m 2 --table music --fields-terminated-by '\t'
    
    
    ---append模式
    sqoop import \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 \
    --target-dir /user/centos/music \
    --append -m 2 --table music --fields-terminated-by '\t'
    
    ---使用查询： 转储
    sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 \
    --target-dir /user/centos/music222 \
    --append -m 2 --fields-terminated-by '\t' \
    -e 'select mname,malbum,msinger from music where id <10 and $CONDITIONS' \
    --split-by 'mname'
    
    
    --指定文件存储格式
    sqoop import \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456  \
    --target-dir /user/centos/music \
    --append -m 2 --table music --fields-terminated-by '\t' \
    --as-sequencefile
    
    
    --指定压缩格式
    sqoop import --connect jdbc:mysql://localhost:3306/db --username root --password 123456 \
    --target-dir /user/centos/musicCompressed \
    --append -m 1 --fields-terminated-by '\t' \
    --table music --fields-terminated-by '\t' \
    --compression-codec 'org.apache.hadoop.io.compress.GzipCodec'

mysql-->hive

--数据导入到hive表===========================
    hdfs dfs -rmr music
    sqoop import \
    --hive-drop-import-delims \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 \
    --table music \
    --create-hive-table --fields-terminated-by '\t' \
    --hive-database db1 \
    --hive-table music3 \
    --hive-import
    
    字段映射
    sqoop import \
    --hive-drop-import-delims \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 \
    --table music \
    --fields-terminated-by '\t' \
    --hive-database db1 \
    --hive-table music9  \
    --hive-import  \
    --map-column-hive id=string
    
    使用sql查询，覆盖导入
    sqoop import \
    --hive-drop-import-delims \
    --hive-overwrite   --hive-import  --hive-table hive_tt \
    --connect jdbc:mysql://host1:3306/test  --username root --password 123456 \
    --query 'SELECT * from per WHERE $CONDITIONS' \
    --target-dir  /tmp/a  -m 1
    
    
    --2、增量导入：
    sqoop import \
    --hive-drop-import-delims \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 --table music \
    --fields-terminated-by '\t' \
    --hive-database db1 --hive-table music3 \
    --hive-import \
    --incremental append --check-column id --last-value 624
    
    --3，分区导入
    sqoop import \
    --hive-drop-import-delims \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 --table music \
    --hive-overwrite --hive-partition-key day \
    --fields-terminated-by '\t' \
    --hive-database db1 --hive-table music7 \
    --hive-import \
    --hive-partition-value 2018-08-20

--------------------

mysql --> hbase:

##1, 根据sql语句，选择性导入
    sqoop import \
    --connect jdbc:mysql://localhost:3306/spark --username root --password 12345  \
    --query "
    select  HBASE_TABLE, CREATE_TIME, HBASE_COUNT  from CDR_HBASE_COUNT
    where \$CONDITIONS
    " \
    --hbase-create-table  \
    --hbase-table c3 \
    --hbase-row-key HBASE_TABLE \
    --column-family f \
    --driver  com.mysql.jdbc.Driver \
    -m 1 # 因为mysql表没有主键id, 使用一个mapreduce
    
    
    ##2, 根据表名，全量导入
    sqoop import \
    --connect jdbc:mysql://192.168.56.161:3306/t1 \
    --username root --password 123456 \
    --table 数据源表 \
    --hbase-table obj \
    --column-family f --split-by 数据源表的主键字段ID \
    --hbase-row-key id \
    --hbase-bulkload --verbose \
    --map-column-java mytxt=String,mydata=String \
    --check-column 表字段 --last-value="1993-02-03 12:34:21" --incremental append
    
    # Sqoop currently serializes all values to HBase by converting each field to its string representation (as if you were importing to HDFS in text mode), and then inserts the UTF-8 bytes of this string in the target cell. Sqoop will skip all rows containing null values in all columns except the row key column. To decrease the load on hbase, Sqoop can do bulk loading as opposed to direct writes. To use bulk loading, enable it using --hbase-bulkload.
    # --map-column-java  <name of column>=<new type> : Override default mapping from SQL type to Java type for configured columns. Sqoop is preconfigured to map most SQL types to appropriate Java or Hive representatives. However the default mapping might not be suitable for everyone and might be overridden by --map-column-java...
    
    #1，如果没有主键ID字段　--split-by ID  (ID这个字段可替换为 -m 1
    #2，以前用的是--query 现在替换为  --table 数据源表名
    #3，没有字段别名的话，就直接向上面那种一样，只要把--query "select id,name from test" 替换为--table test; 字段需要起别名，就是select查询的别名全部 在视图/或表里面 定义好， 全表抽取数据
    #4，允许blob/clob数据抽取: --hbase-bulkload
    #5，增量抽数（可选)：--check-column 表字段 --last-value="1993-02-03 12:34:21" --incremental append/lastmodified (append:源数据一直递增追加，lastmodified:源数据可能修改并更新检查字段的值)

sqoop 定义job

===job
    sqoop job --create myjob -- import \
    --connect jdbc:mysql://localhost:3306/db --username root --password 123456 \
    --table music \
    --create-hive-table --fields-terminated-by '\t' \
    --hive-database db1 \
    --hive-table jobmusic \
    --hive-import
    
    --执行job
    sqoop job --exec myjob

创建mysql表： mysql\_music2, 从hdfs中插入数据

====hdfs数据导入mysql: (--update-mode allowinsert 当key不存在则插入)
    sqoop export --export-dir /user/hive/warehouse/db1/music/day=2018-08-20/  \
    --fields-terminated-by '\t' \
    --columns id,mname,mtime,malbum,msinger,mstyle,mlanguage,misfree \
    --connect jdbc:mysql://localhost:3306/db  \
    --username root --password root \
    --table mysql_music2 \
    --update-key id \
    --update-mode allowinsert

[70]: /images/20220516/fbc6d4c4acdd41e7a69dbcb417f10597.png
[70 1]: /images/20220516/02fdd54b1e424d9ea87c372b9fcd0b5c.png