sqoop 常用写法

 

 

0 参考链接:

 

http://www.cnblogs.com/cenyuhai/p/3306037.html    sqoop 常用命令整理（一）

 

http://www.cnblogs.com/cenyuhai/p/3306056.html    sqoop 常用命令整理（二）

 

如下是 我工作中遇到的东西，每条语句都本机测试通过：

 

 

1 查看所有数据库:

 

sqoop list-databases --connect  jdbc:mysql://192.168.1.1:3306/ --username root --password 123456

 

2 查看具体数据库内的表

 

sqoop list-tables --connect jdbc:mysql://localhost:3306/kellte --username root --password 123456

 

3  将关系型数据表结构users复制到Hive默认库的表users(此时如果不创建users表的话，那么表是内部表)中：

 

sqoop create-hive-table --connect jdbc:mysql://localhost:3306/test --table users 

--username root  --password 123456  --hive-table users

  --fields-terminated-by "\0001"  --lines-terminated-by "\n";

 

参数说明：  这两个参数可以不加

--fields-terminated-by "\0001"  是设置每列之间的分隔符，"\0001"是ASCII码中的1，

它也是hive的默认行内分隔符， 而sqoop的默认行内分隔符为"，" 

--lines-terminated-by "\n"  设置的是每行之间的分隔符，此处为换行符，也是默认的分隔符；

 

 

 

4  将mysql表student数据拷贝到hive默认库的student表中：  这里使用一个mapreduce来执行

 

sqoop import --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --passwd 123456

 --table  student  --hive-import --hive-table student -m 1;

 

5 根据列导入数据:   将kettle库下student表的name属性 写入到 hive表的student1中

 

 

 sqoop import --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

   --table student  --column 'name'  --hive-import --hive-table student1 -m1;

 

 

 5.1  column 和  where合用在 mysql导出到hive中：

 

这是写到hive中的

 sqoop import  --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

    --table student --columns "id,name"  --where "id > 3 and (age = 88 or age = 80)"  -m 1 

    --target-dir /user/hive/warehouse/userinfos2 --fields-terminated-by ",";

 

 

 这是写到hdfs中的

 sqoop import  --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

    --table student --columns "id,name"  --where "id > 3 and (age = 88 or age = 80)"  -m 1 

    --hive-import   --hive-table student5;

注意：--target-dir /user/hive/warehouse/userinfos2   可以用  --hive-import --hive-table userinfos2 进行替换

 

 

  6  增量添加数据：    将Mysql表student5从在主键大于2的基础上导出数据到hive表student5中，其中

                                     mysql表student5的id必须是自增，否则无法执行

 

 

 sqoop import  --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

    --table student5  --hive-import --hive-table student5 -m 1 --incremental append --check-column 'id'

 --last-value 2;

 

 

7    query过滤条件使用:    将mysql表3个字段写到hdfs中，写入到hdfs后，字段以:间隔，各行之间以id间隔

 

 sqoop import  --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

    --query "select id,name,kc_name from student5 where \$CONDITIONS"

   --target-dir /opt/hadoop/hive/warehouse/student5

  --fields-terminated-by  “:”  --split-by student5.id;

注意：   上述代码在命令行中需要写在一行， 并且 $CONDITIONS 在 ""内的时候，需要\来转义

               使用' 则不需要\转义，  $CONDITIONS和 query必须同时出现。

               在oozie 拼接的 sqoop  workflow.xml中 拼接的 sqoop query 时， $CONDITIONS 不需要转义

 

 

8  query 将数据写到Hive中  ： 

 

 

 sqoop import  --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

    --query "select id,name,kc_name from student5 where \$CONDITIONS  and id>3"

   --target-dir /opt/hadoop/hive/warehouse/student5

  --fields-terminated-by  “,”  --split-by student5.id

  --hive-import  --hive-table student5 -m 1;

 

  需要注意的：   --query 必须和 --target-dir 一起使用，   

                         在写出到hive表时， --target-dir即使不指定hive表student5所在的HDFS路径，

                           hive表数据也会写进来，不过不建议这样吧，至少我试验时会出现这种现象

                           hive 表创建时用什么分隔符分割字段，那么--target-dir 也需要指定同样的分隔符。

 query方式导入(hdfs/hive)数据要比 import+column方式复杂，但是query方式提供了在其内写复杂sql的能

 

9   没有主键表插入:

 

其中hive标student创建为：

create table student(address string,name string,s_id string) row format delimited fields terminated by '\t';

 这里以 s_id作为行间隔

sqoop import --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --passwd 123456

--table student  --split-by s_id --hive-import --hive-table student -m 1

--fields-terminated-by "\t"  --null-string "**"  --null-non-string "##";

 

10 分区导入:

 

 

14 关联多表无关系全量进行查询：

sqoop import --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --passwd 123456

--query 'select name,age from student1 union all select name,age from student2 where $CONDITIONS'

--target-dir /user/hdfs/test1 --split-by name  --fields-terminated-by  "\t"

--null-string "**"  --null-non-string "##"  -m  1;

 

15 关联多张表关系查询:

sqoop import --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --passwd 123456

--query 'select name,kc_name,c_cj from student1 join cjb on s_id = student1.student_id

join kcb on kc_id = cjb.c_id where $CONDITIONS'

--target-dir /user/hdfs/test1 --split-by name  --fields-terminated-by  "\t"

--null-string "**"  --null-non-string "##"  -m  1;

 

 

 

 

 

 

 

16 hdfs导出到mysql:

sqoop export --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

--table student  --export-dir '/user/hdfs/part/part-m-00000'  --fields-terminated-by ":" -m 1;

 

19 从 oracle导入数据:

sqoop import --connect 'jdbc:oracle:thin:@192.168.1.1:1521:dbname' --username root --passwd 123456

--table student   -m 1

--target-dir /user/hdfs/oracle/student

--fields-terminated-by "\t"  --null-string "**"  --null-non-string "##";

 

20 从sqlserver导入数据:

sqoop import --connect 'jdbc:sqlserver://192.168.1.1;database=test;username=sa;password=123' 

--query 'select top 10 * from zm where $CONDITIONS'

 -m 1

--target-dir /user/hdfs/sqlserver/student

--split-by id

--hive-table zm

--fields-terminated-by "\t"  --null-string "**"  --null-non-string "##";

 

 

sqoop 将mysql表生成 javabean

sqoop codegen  --connect jdbc:mysql://192.168.1.1:3306/kettle --username root --password 123456

--table student --outdir /opt/sqoopbean

 

这样会在linux的/opt/sqoopbean 下生成 表同名的javabean  student.java

同时会把生成的class文件自动放在：

/tmp/sqoop-root/compile/e4......7277/student.jar中

 

生成了 javabean后 可以修改javabean 比如 name为Null时 默认值为 zm,

 

然后使用生成的 jar对应的class 反编译后，得到java 修改添加自己的逻辑后，

在编译成class jar 然后利如下命令来执行提交 sqoop任务：

 

sqoop import --connect jdbc:mysql://172.16.252.131:3306/etl

--username root --password root --table  user --jar-file /tmp/sqoop-root/compile/31adf......557f/user.jar

--class-name user --target-dir /user/root/sqoopout --fields-terminated-by "\t"

-m 1;