sqoop 参数

最新推荐文章于 2024-08-31 17:30:01 发布

逗点儿

最新推荐文章于 2024-08-31 17:30:01 发布

阅读量672

点赞数

分类专栏： hive 文章标签：数据库 hive

本文链接：https://blog.youkuaiyun.com/weixin_39216383/article/details/79145733

版权

hive 专栏收录该内容

13 篇文章

订阅专栏

DBMS导入到hive表

sqoop import

–connect jdbc:mysql://localhost:3306/wl 连接数据库，指定数据库
–username root 用户名
–password 123456 密码
–table table_name 指定表名
–hive-import 判断导入到hive
–hive-database database_name 导入hive的库名
–hive-overwrite 覆盖的写入
–hive-table table_name 指定hive的表名
–delete-target-dir 如果文件夹存在，就删除
–fields-terminated-by ‘\t’ 判断分隔符为\t
-m 1 当没有主键的时候，指定map为1

ps：–create-hive不建议使用，应先创建好表，再做导入。因为生产中，我们所需要的类型往往与机器自动生成的类型不同

hive表到DBMS

sqoop export
–connect jdbc:mysql://localhost:3306/wl 连接数据库，指定数据库
–username root 用户名
–password 123456 密码
–table table_name 表名
–mapreduce-job-name job_name 指定job的名字
–export-dir /user/hive/warehouse/emp 指定导出的hive所在HDFS的路径

将查询语句导入到HDFS
sqoop import \
–connect jdbc:mysql://localhost:3306/vincent_hive \
–username root –password vincent \
–mapreduce-job-name FromMySQL2HDFS \
–delete-target-dir \
–fields-terminated-by ‘\t’ \
-m 1 –null-non-string 0 \
–target-dir EMP_COLUMN_QUERY \
–query ‘select * from emp where empno>=7900 and $CONDITIONS’ 指定查询语句

ps:当有查询语句的时候，指定字段和指定表这两个参数都不需要存在了。

sqoop eval

执行sql语句，并显示其结果
1.执行语句

sqoop eval \
--connect jdbc:mysql://localhost:3306/wl \
--username root --password 123456 \
--query "select * from emp"

2.显示结果

Warning: /home/hadoop/app/sqoop-1.4.6-cdh5.7.0/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /home/hadoop/app/sqoop-1.4.6-cdh5.7.0/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /home/hadoop/app/sqoop-1.4.6-cdh5.7.0/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /home/hadoop/app/sqoop-1.4.6-cdh5.7.0/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
18/01/23 08:44:01 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.7.0
18/01/23 08:44:01 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/01/23 08:44:02 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
----------------------------------------------------------------------------------------------
| empno | ename      | job       | mgr   | hiredate            | sal       | comm      | deptno | 
----------------------------------------------------------------------------------------------
| 7369  | SMITH      | CLERK     | 7902  | 1980-12-17 00:00:00.0 | 800.00    | (null)    | 20  | 
| 7499  | ALLEN      | SALESMAN  | 7698  | 1981-02-20 00:00:00.0 | 1600.00   | 300.00    | 30  | 
| 7521  | WARD       | SALESMAN  | 7698  | 1981-02-22 00:00:00.0 | 1250.00   | 500.00    | 30  | 
| 7566  | JONES      | MANAGER   | 7839  | 1981-04-02 00:00:00.0 | 2975.00   | (null)    | 20  | 
| 7654  | MARTIN     | SALESMAN  | 7698  | 1981-09-28 00:00:00.0 | 1250.00   | 1400.00   | 30  | 
| 7698  | BLAKE      | MANAGER   | 7839  | 1981-05-01 00:00:00.0 | 2850.00   | (null)    | 30  | 
| 7782  | CLARK      | MANAGER   | 7839  | 1981-06-09 00:00:00.0 | 2450.00   | (null)    | 10  | 
| 7788  | SCOTT      | ANALYST   | 7566  | 1982-12-09 00:00:00.0 | 3000.00   | (null)    | 20  | 
| 7839  | KING       | PRESIDENT | (null) | 1981-11-17 00:00:00.0 | 5000.00   | (null)    | 10  | 
| 7844  | TURNER     | SALESMAN  | 7698  | 1981-09-08 00:00:00.0 | 1500.00   | 0.00      | 30  | 
| 7876  | ADAMS      | CLERK     | 7788  | 1983-01-12 00:00:00.0 | 1100.00   | (null)    | 20  | 
| 7900  | JAMES      | CLERK     | 7698  | 1981-12-03 00:00:00.0 | 950.00    | (null)    | 30  | 
| 7902  | FORD       | ANALYST   | 7566  | 1981-12-03 00:00:00.0 | 3000.00   | (null)    | 20  | 
| 7934  | MILLER     | CLERK     | 7782  | 1982-01-23 00:00:00.0 | 1300.00   | (null)    | 10  | 
----------------------------------------------------------------------------------------------

执行文件 sqoop –options-file

sqoop –options-file emp.opt

import
--connect 
jdbc:mysql://localhost:3306/sqoop 
--username 
root 
--password 
root 
--table 
emp 
-m 
1 
--delete-target-dir  
--target-dir 
EMP_OPTIONS_FILE

执行作业 job

sqoop job –create job_name
sqoop job –list 列出创建的sqoopjob
sqoop job –show myjob
sqoop job –exec myjob 执行创建的job
hadoop fs -text emp/part* 查看HDFS上的数据

sqoop 导入分区表
导出mysql到hive分区表

1.新建分区表：

create table emp8(
empno int,
ename string, 
job string,                                  
mgr int,                                     
hiredate string,                             
sal double,                                  
comm double,                                  
deptno int       
)
partitioned by (month string)
row format delimited fields terminated by "\t"

2.从DBMS导入

sqoop import \
--connect jdbc:mysql://localhost:3306/wl \
--username root --password 123456 \
--table emp \
--hive-import \
--hive-database default \
--hive-overwrite \
--hive-table emp8 \
--delete-target-dir \
--fields-terminated-by '\t' \
-m 1 \
--hive-partition-key month \
--hive-partition-value '2015.5'

查看结果

hive> select * from emp8;
OK
7369    SMITH   CLERK   7902    1980-12-17 00:00:00.0   800.0   NULL    20  2015.5
7499    ALLEN   SALESMAN    7698    1981-02-20 00:00:00.0   1600.0  300.0   30  2015.5
7521    WARD    SALESMAN    7698    1981-02-22 00:00:00.0   1250.0  500.0   30  2015.5
7566    JONES   MANAGER 7839    1981-04-02 00:00:00.0   2975.0  NULL    20  2015.5
7654    MARTIN  SALESMAN    7698    1981-09-28 00:00:00.0   1250.0  1400.0  30  2015.5
7698    BLAKE   MANAGER 7839    1981-05-01 00:00:00.0   2850.0  NULL    30  2015.5
7782    CLARK   MANAGER 7839    1981-06-09 00:00:00.0   2450.0  NULL    10  2015.5
7788    SCOTT   ANALYST 7566    1982-12-09 00:00:00.0   3000.0  NULL    20  2015.5
7839    KING    PRESIDENT   NULL    1981-11-17 00:00:00.0   5000.0  NULL    10  2015.5
7844    TURNER  SALESMAN    7698    1981-09-08 00:00:00.0   1500.0  0.0 30  2015.5
7876    ADAMS   CLERK   7788    1983-01-12 00:00:00.0   1100.0  NULL    20  2015.5
7900    JAMES   CLERK   7698    1981-12-03 00:00:00.0   950.0   NULL    30  2015.5
7902    FORD    ANALYST 7566    1981-12-03 00:00:00.0   3000.0  NULL    20  2015.5
7934    MILLER  CLERK   7782    1982-01-23 00:00:00.0   1300.0  NULL    10  2015.5
Time taken: 0.815 seconds, Fetched: 14 row(s)

若泽大数据交流群：671914634