sqoop基础使用

2019-03-24

sqopp基础命令简单使用

sqoop安装配置文件

1.解压
2.修改配置文件
vim sqoop-env.sh

#添加hadoop家目录  
#添加hbase家目录（hbase也有类sqoop的工具，此处不配置）  
#添加hive的家目录  
#不需配置zookeeper的家目录  
export HADOOP_COMMON_HOME=/opt/hadoop-2.9.2  
export HADOOP_MAPRED_HOME=/opt/hadoop-2.9.2  
#export HBASE_HOME=  
export HIVE_HOME=/opt/hive-1.2.1

3.测试数据库连接

#列出mysql数据库中的所有数据库
sqoop-list-databases --connect jdbc:mysql://localhost:3306 --username root --password 123456
#显示mysql的数据库hive中的表
sqoop-list-tables --connect jdbc:mysql://localhost:3306/hive --username root --password 123456
#命令中的test为mysql数据库中的test数据库名称 username password分别为mysql数据库的用户密码

将mysql的表导入 hdfs

sqoop import \
--connect jdbc:mysql://master:3306/userdb \
--username root \
--password root \
--target-dir \
/sqooptest \
--fields-terminated-by ',' \
--table emp \
--split-by id \
--m 2

将mysql数据到hbase

注意：sqoop目前支持的hbase版本 Hbase <= 0.95.2 ，高版本hbase环境下会报错

sqoop import \
--connect jdbc:mysql://master:3306/userdb \
--table emp \
--username root \
--password 123456 \
--hbase-create-table  \
--hbase-table t_emp \
--hbase-row-key id \
--column-family info \
--m 1

将关系型数据的表结构复制到hive中,只是复制表的结构，表中的内容没有复制过去。会在hive中创建表结构

sqoop create-hive-table --connect jdbc:mysql://localhost:3306/userdb --table emp_add --username root --password 123456 --hive-table cp_emp_add
#其中 -table emp_add为mysql中的数据库test中的表 -hive-cp_emp_add
#cp_emp_add为hive中新建的表名称

从数据库增量导入表数据到hdfs中

sqoop import \
--connect jdbc:mysql://master:3306/userdb \
--target-dir /sqooptest  \
--username root \
--password 123456 \
--table emp \
--m 1 \
--incremental append \
--check-column id \
--last-value 1205

按需导入表数据子集

我们可以导入表的使用Sqoop导入工具，”where”子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。
where子句的语法如下。
–where

下面的命令用来导入emp_add表数据的子集。子集查询检索员工ID和地址，居住城市为：sec-bad

sqoop import \
--connect jdbc:mysql://master:3306/userdb \
--username root \
--password 123456 \
--where "city ='sec-bad'" \
--target-dir /sqooptest/wherequery \
--table emp_add \
--m 1

按需导入2

sqoop import --connect jdbc:mysql://master:3306/userdb --username root --password 123456 --target-dir /sqooptest/wherequery2 --query 'select id,name,deg from emp WHERE id>1206 and $CONDITIONS'  --split-by id  --fields-terminated-by '\t' --m 2

sqoop import \
--connect jdbc:mysql://master:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqooptest/emp_where \
--query 'select id,name,deg from emp WHERE id>1207 and $CONDITIONS' \
--split-by id \
--fields-terminated-by '\t' \
--m 2

–query 比where很强大
–query 后面跟sql语句每个map task需要执行sql查询语句的副本，结果会根据sqoop推测的边界条件分区。query必须包含$CONDITIONS。
这样每个scoop程序都会被替换为一个独立的条件。同时你必须指定–split-by.分区

从关系数据库导入文件到hive中

–会自己在hive中创建表和mysql表名相同，然后回默认存在hive的hdfs目录下

sqoop import   --connect jdbc:mysql://master:3306/userdb  --username root --password 123456 --table emp --hive-import --fields-terminated-by '|'  --m 1

–指定表名
指定hive保存的数据库命–hive-table emp_from_mysql 为emp_from_mysql

sqoop import   --connect jdbc:mysql://master:3306/userdb  --username root --password 123456 --table emp --hive-import --fields-terminated-by '|'  --hive-table emp_from_mysql -m1

将hive中的表数据导入到mysql中,在进行导入之前，mysql中的表hive_test必须已经提起创建好了。

导出到mysql（mysql已经建好表）
关键字：export export-dir
–input-fields-terminated-by ‘,’ 指定输出的切分格式（可以不需要指定）

sqoop export --connect jdbc:mysql://master:3306/userdb --username root --password 123456 --table dim_user_active_day --export-dir /user/hive/warehouse/app.db/dim_user_active_day/day=2017-09-21/dim=0000 --input-fields-terminated-by \\001

将hdfs的文件数据导出到mysql

sqoop export \
--connect jdbc:mysql://mster:3306/userdb \
--username root \
--password 123456 \
--input-fields-terminated-by ',' \
--table emp \
--export-dir /sqooptest/

将hive的表数据（hdfs的文件）导出到mysql

sqoop export \
--connect jdbc:mysql://master:3306/userdb \
--username root \
--password 123456 \
--input-fields-terminated-by ',' \
--table t_from_hive \
--export-dir /user/hive/warehouse/t_a/

案例从MySQL数据库服务器中的emp表导入HDFS

sqoop import   \
--connect jdbc:mysql://master:3306/userdb  \
--username root  \
--password 123456 \
--table emp   \
--m 1  

sqoop import   --connect jdbc:mysql://master:3306/userdb  --username root --password 123456 --table emp   --m 1  

##指定切分方式为|
sqoop import   --connect jdbc:mysql://master:3306/userdb  --username root --password 123456 --table emp --fields-terminated-by '|'  --m 1

#解释
--connect jdbc:mysql://master:3306/userdb  指定jdbc url 
--table emp 指定的表
--split-by id --m 2指定分片的字段（如果用多个maptask就要指定字段，一个可以省略此操作）
--fields-terminated-by ','  指定使用逗号切分
--target-dir 指定切分后收据存储在HDFS的位置，不指定默认位置在/user/root/表名

#技巧
常用sqoop import  --help 查方法
##导入hive表

###关键词--hive-import 不需要指定目录呢
sqoop import   --connect jdbc:mysql://master:3306/userdb  --username root --password 123456 --table emp --hive-import --fields-terminated-by '|'  --m 1

增量导入

增量导入

--名词解释：增量导入是仅导入新添加的表中的行的技术。
sqoop支持两种增量MySql导入到hive的模式，
 	一种是append，即通过指定一个递增的列，比如：
--incremental append  --check-column num_id --last-value 0 
另种是可以根据时间戳，比如：
--incremental lastmodified --check-column created --last-value '2012-02-01 11:0:00' 
就是只导入created 比'2012-02-01 11:0:00'更大的数据。

--应用场景：一般指mysql数据库更新数据后 我们可以只将更新后的数据文件导入到hdfs中
条件导入

--incremental <mode>     incremental 英语意思：增加的 
--check-column <column name>
--last value <last check column value>  告诉上一次递增到哪一个值


sqoop import --connect jdbc:mysql://master:3306/userdb --username root --password 123456 --table emp --m 1 --incremental append --check-column id --last-value 1205 --target-dir  /user/hive/warehouse/emp

mysql编码问题

一、mysql中的编码
mysql> show variables like 'collation_%';  
mysql> show variables like 'character_set_%';
  
缺省是latin1编码，会导致中文乱码。
修改库的编码：
mysql> alter database db_name character set utf8;
修改表的编码：
mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

可以在mysql中设置编码，单个设置 
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
但重启后会失效。

可以修改配置文件：

[root@Hadoop48 ~]# vi /etc/my.cnf  
[mysql]  
default-character-set=gbk 
[client]  
default-character-set=utf8  
[mysqld]  
default-character-set=utf8  
character_set_server=utf8  
init_connect='SET NAMES utf8' 
重启mysql，这样确保缺省编码是utf8

从MySQL数据库服务器中的emp表导入HDFS

将日新用户维度表改为一级分区

由于dim_user_new_day
含有二级分区表，所以第一步将二级分区的不同需求合并到2019-03-21。
sqoop解决不了二级分区的问题

create table dim_user_new_day_1p(
os_name string
,city string
,release_channel string
,app_ver_name string
,cnts int,
dt string
) partitioned by (day string);

insert into table dim_user_new_day_1p partition (day='2019-03-21')
select
os_name
,city
,release_channel
,app_ver_name
,cnts
,day
from
dim_user_new_day
where day='2019-03-21';

--TODO  -----
将app数据仓库中的 日新用户维度统计报表：dim_user_new_day  导出到mysql的表中去

-- 1 在mysql中建库建表
create database appdata;
create table dim_user_new_day(
os_name varchar(20),city varchar(20),release_channel varchar(20),app_ver_name varchar(20),cnts int,dt varchar(20)
);

--注意：将库和表的编码集改成utf8,命令如下：
修改库的编码：
mysql> alter data_name appdata character set utf8;
mysql> alter database appdata character set utf8;
修改表的编码：
mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 
ALTER TABLE dim_user_new_day CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

sqoop export  \
--connect "jdbc:mysql://master:3306/appdata?useUnicode=true&characterEncoding=utf-8"  \
--username root --password 123456 \
--input-fields-terminated-by '\001'  \
--table dim_user_new_day \
--export-dir  /user/hive/warehouse/appdata.db/dim_user_new_day_1p/day=2019-03-21/


shell开发

-- 2 用sqoop将hive中的 dim_user_new_day 中的指定日分区的数据导出到mysql 的dim_user_new_day
#!/bin/bash
day=`date -d '-1 day' +'%Y-%m-%d'`

/root/apps/sqoop/bin/sqoop export \
--connect "jdbc:mysql://master:3306/app?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 123456\
--input-fields-terminated-by '\001' \
--table dim_user_new_day \
--export-dir /user/hive/warehouse/app.db/dim_user_new_day_1p/day=${day} /