Sqoop 操作笔记

URL :Sqoop安装配置

Sqoop执行select语句MYSQL导入HDFS

sqoop import --connect jdbc:mysql://192.168.2.251:3306/sys_app_user --username root --password root --query 'select * from SYS_USER where id < 5 and $CONDITIONS' --splis-byid --target-dir '/yuqi/sqoop/sys_user' -m 2
sqoop import --connect jdbc:mysql://192.168.2.251:3306/sys_app_user?characterEncoding=UTF-8 --username root --password root --splis-myid --target-dir '/yuqi/sqoop/' -m 2 --null-string '' --null-non-string ''

sqoop import-all-tables 导出指定库的所有table表
--query:执行SQL语句查询满足结果则导出
--splis-byid:排序
--target-dir:指定导出到HDFS上的路径
-m:指定启动的map数量
--null-String:当从数据导出数据出现NULL值的时候,如果是String类型则替换成 '' 使用方法:--null-String ''
--null-non-String:出现的NULL不是String类型的
--columns 指定导入导出某列


HDFS数据导入到MYSQL
sqoop export --connect jdbc:mysql://192.168.2.251:3306/sys_app_user --username root --password root --table SYS_USER --fields-teminated-by ',' --export-dir -m 1 --null-String '' --null-non-String ''
--table:指定的sys_app_user 库中的表
--fields-teminated-by:数据源的分隔符

--export-dir:指定数据存储在HDFS上的位置

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

sqoop是hadoop技术支持Cloudera公司开发的一个在关系数据库和hdfs,hive之间数据导入导出的一个工具




1.常用的sqoop命令
1)列出mysql数据库中的所有数据库


语法:
sqoop list-databases --connect jdbc:mysql://IP:PORT/ --username USER --password PASSWD 
sqoop list-databases --connect jdbc:mysql://IP:PORT/ --username USER -P   (此方式更加安全一点)


username、password分别为mysql数据库的用户密码


实例:
[hduser@master ~]$ sqoop list-databases --connect jdbc:mysql://slave1.hadoop:3306/ --username root --password 123456


[hduser@master ~]$ sqoop list-databases --connect jdbc:mysql://slave1.hadoop:3306/ --username root -P
Warning: $HADOOP_HOME is deprecated.


Enter password: 


以上的两命令输出结果相同，如下：
13/09/07 10:59:48 INFO manager.MySQLManager: Executing SQL statement: SHOW DATABASES
information_schema
hive
mysql
performance_schema
sqooptest
test






2)连接mysql并列出数据库中的表


语法：
sqoop list-tables --connect jdbc:mysql://IP:PORT/database --username USER -P


实例：
[hduser@master ~]$ sqoop list-tables --connect jdbc:mysql://slave1.hadoop:3306/mysql --username root -P
Warning: $HADOOP_HOME is deprecated.


Enter password: 
columns_priv
db
event
func
general_log


命令中的mysql为数据库中数据库名称




3)将关系型数据库表中的数据导到DFS上


sqoop import : RMDBS——>DFS
语法：
sqoop import --verbose --fields-terminated-by ','  --connect jdbc:mysql://IP:PORT/database --username root --password PASSWD --table tablename --target-dir /dfsdir/dfsdir --split-by 'SPLIT BREAK'  


?--connect    : 要连接的数据库JDBC-URL
?--username   ：登录数据库的用户名
?--password   ：登录数据库的密码
?--table      ：需要导出的表
?--target-dir ：DFS目标目录
?--split-by   ：字段的分隔符


实例：
将mysql的sqooptest库中的表sqooptable的数据导到DFS上


mysql> create database sqooptest;


mysql> create table sqooptable(id int,name varchar(100));


mysql> insert into sqooptable values(100,'yinliqing');
Query OK, 1 row affected (0.06 sec)


mysql> select * from sqooptable;
+------+-----------+
| id   | name      |
+------+-----------+
|  100 | yinliqing |
+------+-----------+
1 row in set (0.00 sec)






[hduser@master ~]$ sqoop import --verbose --fields-terminated-by ','  --connect jdbc:mysql://slave1.hadoop:3306/sqooptest --username root --password 123456 --table sqooptable --target-dir /sqoop/sqooptable --split-by 'name'  


其中
--table sqooptable为mysql数据库sqooptest的表,
--hive-table hivetest1为hive中新建的表名称


导入数据过程由MapReduce执行。查看DFS上的数据是否和mysql中的数据一致：
[hduser@master ~]$ hadoop dfs -ls /sqoop/sqooptable/
[hduser@master ~]$ hadoop dfs -cat /sqoop/sqooptable/part-m-00000
Warning: $HADOOP_HOME is deprecated.


100,yinliqing
[hduser@master ~]$ 




4)将关系型数据库表及表中的数据复制到hive中


sqoop import : RMDBS——>hive
语法：
sqoop import --connect jdbc:mysql://IP:PORT/database --username root --password PWD --table tablename --hive-import --hive-table hivetable -m 1


[hduser@master ~]$ sqoop import --connect jdbc:mysql://slave1.hadoop:3306/sqooptest --username root --password 123456 --table sqooptable --hive-import --hive-table hivetest1 -m 1






5)将关系型数据库的表结构复制到hive中,只是复制表的结构，表中的内容没有复制
[hduser@master ~]$ sqoop create-hive-table --connect jdbc:mysql://slave1.hadoop:3306/sqooptest --table sqooptable --username root --password 123456 --hive-table hivetest1
Warning: $HADOOP_HOME is deprecated.


13/09/07 11:24:09 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/09/07 11:24:09 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
13/09/07 11:24:09 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
13/09/07 11:24:09 INFO hive.HiveImport: Loading uploaded data into Hive
13/09/07 11:24:10 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `sqooptable` AS t LIMIT 1
13/09/07 11:24:10 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `sqooptable` AS t LIMIT 1
13/09/07 11:24:12 INFO hive.HiveImport: Logging initialized using configuration in file:/usr/local/hive/conf/hive-log4j.properties
13/09/07 11:24:12 INFO hive.HiveImport: Hive history file=/tmp/hduser/hive_job_log_hduser_201309071124_1491994202.txt
13/09/07 11:24:19 INFO hive.HiveImport: OK
13/09/07 11:24:19 INFO hive.HiveImport: Time taken: 5.66 seconds
13/09/07 11:24:19 INFO hive.HiveImport: Hive import complete.


查看DFS上的数据是否和mysql中的数据一致：
[hduser@master ~]$ hadoop dfs -ls /user/hive/warehouse/
Warning: $HADOOP_HOME is deprecated.


Found 7 items
drwxr-xr-x   - hduser supergroup          0 2013-09-06 23:13 /user/hive/warehouse/hivetest
drwxr-xr-x   - hduser supergroup          0 2013-09-07 11:24 /user/hive/warehouse/hivetest1
drwxr-xr-x   - hduser supergroup          0 2013-08-29 11:16 /user/hive/warehouse/logs
drwxr-xr-x   - hduser supergroup          0 2013-08-29 11:14 /user/hive/warehouse/new_table
drwxr-xr-x   - hduser supergroup          0 2013-08-29 11:13 /user/hive/warehouse/pokes
drwxr-xr-x   - hduser supergroup          0 2013-09-04 15:26 /user/hive/warehouse/testhivedrivertable
drwxr-xr-x   - hduser supergroup          0 2013-08-29 16:31 /user/hive/warehouse/xp


查看hive中的hivetest1表
[hduser@master ~]$ hive 
Logging initialized using configuration in file:/usr/local/hive/conf/hive-log4j.properties
Hive history file=/tmp/hduser/hive_job_log_hduser_201309071248_125397244.txt
hive> describe hivetest1;
OK
id      int
name    string
Time taken: 4.596 seconds
hive> 


6)将hive中的表数据导入到mysql中


sqoop export : hive——>RMDBS
语法：
sqoop export --connect jdbc:mysql://IP:PORT/database --username root --password PWD --table rdbmstablename --export-dir /user/hive/warehouse/new_test_partition/dt=date


在进行导入之前，mysql中的表rdbmstablename必须已经提前创建好了。




[hduser@master ~]$ sqoop export --connect jdbc:mysql://slave1.hadoop:3306/sqooptest --username root --password 123456 --table sqooptable --export-dir /user/hive/warehouse/hivetest1/dt=2012-03-05




7)将数据从DFS导入到MySQL


sqoop export : HDFS——>RMDBS
语法：
sqoop export --connect jdbc:mysql://IP:PORT/database --username USER --password PASSWD --table mysql_table --export-dir /dfsdir/dfsdir/ --input-fields-terminated-by '\t'    


mysql> select * from database.mysql_table; 


查看DFS上的数据是否已经导入mysql：


[hduser@master ~]$ hadoop dfs -cat /dfsdir/dfsdir/part-m*  


mysql> select * from database.mysql_table;


8)选取一定条件的数据导入DFS
sqoop import --connect jdbc:mysql://IP:PORT/database --username USER --password PASSWD --query 'SELECT * FROM mysql_table WHERE field=value AND $CONDITIONS' -m 1 --target-dir /dfsdir/dfsdir


如果--query后面的SQL语句带有WHERE，则一定要加上（AND $CONDITIONS），否则会出错。
 
到目前为止已经可以实现MySQL和DFS之间相互导数据了。
 
9)使用sqoop将MySQL数据库中的数据导入Hbase


sqoop import --connect jdbc:mysql://IP:PORT/database --username USER --password PASSWD  --table mysql_table --hbase-table hbase_table --column-family column_familyname --hbase-row-key id --hbase-create-table


到hbase中查看结果：


hbase(main):004:0> list
hbase(main):005:0> scan 'hbase_table'














可能遇到的问题：
 


$ sqoop create-hive-table --connect jdbc:mysql://localhost:3306/dev--username root --password 123456 --table person --hive-table person


……


INFO hive.HiveImport: FAILED: Error in metadata: MetaException(message:Got exception: java.io.FileNotFoundException File file:/user/hive/warehouse/person does not exist.)


…… 




解决方法：
 
hadoop dfs -mkdir /user/hive/warehouse/


hadoop dfs -chown username /user/hive/warehouse/




第二次执行如下命令会报以下异常：
[hduser@master ~]$ sqoop import --connect jdbc:mysql://slave1.hadoop:3306/sqooptest --username root --password 123456 --table sqooptable --hive-import --hive-table hivetest1 -m 1


13/09/07 12:40:13 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `sqooptable` AS t LIMIT 1
13/09/07 12:40:17 INFO mapred.JobClient: Cleaning up the staging area hdfs://master.hadoop:9000/app/hadoop/tmp/mapred/staging/hduser/.staging/job_201309071052_0007
13/09/07 12:40:17 ERROR security.UserGroupInformation: PriviledgedActionException as:hduser cause:org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory sqooptable already exists
13/09/07 12:40:17 ERROR tool.ImportTool: Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory sqooptable already exists


解决方法：


hadoop 由于进行的是耗费资源的计算，生产的结果默认是不能被覆盖的，


因此中间结果输出目录一定不能存在，否则出现这个错误。

在操作中遇到的BUG

MYSQL导出到HDFS出现API问题程序没有运行

HDFS导入到MYSQL出现中文字符乱码

以后将更新

SQOOP <---------->HIVE 

sqoop导入表/数据到Hive

Hbase Oracle