sqoop 导入到hive字段全是null_Sqoop使用-实战案例

一.====sqoop使用========

1. sqoop命令：

显示某个子命令的帮助信息，

list-databases是一个子命令

$ bin/sqoop help

$ bin/sqoop list-databases --help

2.示例

--显示出对应节点上的所有数据库，用来测试

RDBMS[mysql]是否能够连接，

''表示一条命令语句中的换行

$ bin/sqoop list-databases

--connect jdbc:mysql://li:3306

--username root

--password root

二.====mysql导入HDFS=====

案例一： 利用sqoop把mysql里面的数据导入到HDFS

1、在mysql里面创建测试表

CREATE TABLE `my_user` (

`id` tinyint(4) NOT NULL AUTO_INCREMENT,

`account` varchar(255) DEFAULT NULL,

`passwd` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

);

INSERT INTO `my_user` VALUES ('1', 'admin', 'admin');

INSERT INTO `my_user` VALUES ('2', 'pu', '12345');

INSERT INTO `my_user` VALUES ('3', 'system', 'system');

INSERT INTO `my_user` VALUES ('4', 'zxh', 'zxh');

INSERT INTO `my_user` VALUES ('5', 'test', 'test');

INSERT INTO `my_user` VALUES ('6', 'pudong', 'pudong');

INSERT INTO `my_user` VALUES ('7', 'qiqi', 'qiqi');

INSERT INTO `my_user` VALUES ('8', 'congcong', 'congcong');

2、使用sqoop导入数据到HDFS

$ bin/sqoop import --help

$ bin/sqoop import

--connect jdbc:mysql://li:3306/mydb

--username root

--password root

--table my_user

--target-dir /user/hive/warehouse/my_user

--delete-target-dir

--num-mappers 1

--fields-terminated-by ""

target-dir：HDFS输出目录

delete-target-dir：如果上面输出目录存在,就先删除

num-mappers：设置map个数为1,默认情况下map个数是4，

fields-terminated-by ""：指定列分隔符为

查看：$ hdfs dfs -cat /user/hive/warehouse/my_user/p*

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

3、指定具体列(num-mappers为2，生成2个文件)

$ bin/sqoop import

--connect jdbc:mysql://blue01.mydomain:3306/mydb

--username root

--password root

--table my_user

--target-dir /user/hive/warehouse/my_user

--delete-target-dir

--num-mappers 2

--fields-terminated-by ""

--columns id,passwd

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

4、用where指定条件

$ bin/sqoop import

--connect jdbc:mysql://blue01.mydomain:3306/mydb

--username root

--password root

--table my_user

--target-dir /user/hive/warehouse/my_user

--delete-target-dir

--num-mappers 1

--fields-terminated-by ""

--columns id,passwd

--where "id<=3"

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

5、把select语句的查询结果导入，必需包含

'$CONDITIONS'在WHERE子句，否则报错

--query "select id,name from my_user

where id>=3 and $CONDITIONS"

$ bin/sqoop import

--connect jdbc:mysql://blue01.mydomain:3306/mydb

--username root

--password root

--target-dir /user/hive/warehouse/my_user

--delete-target-dir

--num-mappers 1

--fields-terminated-by ""

--query 'select id,account from my_user

where id>=3 and $CONDITIONS'

=====mysql导入Hive=======

案例二： 把mysql里的数据导入到Hive

1、先要创建好Hive表

hive> create database mydb;

hive> use mydb;

CREATE TABLE mydb.my_user (

id int,

account string,

passwd string

)row format delimited fields terminated by "";

2、导入数据到Hive

$ bin/sqoop import

--connect jdbc:mysql://blue01.mydomain:3306/mydb

--username root

--password root

--table my_user

--num-mappers 1

--hive-import

--hive-database mydb

--hive-table my_user

--fields-terminated-by ""

--delete-target-dir

--hive-overwrite

3、查看结果：hive> select * from my_user;

====Hive或HDFS导出到mysql===

案例三：从Hive或HDFS中把数据导出到mysql

1、先创建一个mysql表

CREATE TABLE `hive2mysql` (

`id` tinyint(4) PRIMARY KEY AUTO_INCREMENT,

`account` varchar(255),

`passwd` varchar(255)

);

2、从hive或者hdfs导入到Mysql表

$ bin/sqoop export

--connect jdbc:mysql://blue01.mydomain:3306/mydb

--username root

--password root

--table hive2mysql

--num-mappers 1

--export-dir /user/hive/warehouse/mydb.db/my_user

--input-fields-terminated-by ""

====脚本文件执行=======

3、创建一个opt脚本文件(注意：必需要换行)

vi job1.opt

export

--connect

jdbc:mysql://li:3306/mydb

--username

root

--password

root

--table

hive2mysql

--num-mappers

1

--export-dir

/user/hive/warehouse/mydb.db/my_user

--input-fields-terminated-by

""

4、使用sqoop执行这个文件

** 删除掉表中数据，避免主键重复

$ bin/sqoop --options-file job1.opt

案例四

统计某个网站每天每个小时的PV、UV(参见图)

PV(访问量)：即Page View, 即页面浏览量或点击量，

在一定统计周期内用户每次刷新网页一次即被计算一次。

UV(独立访客)：即Unique Visitor,访问您网站的一台电

脑客户端为一个访客。00:00-24:00内相同的客户端只

被计算一次。

IP(独立IP)：即Internet Protocol,指独立IP数。

00:00-24:00内相同IP地址之被计算一次。

** Hive分析流程：

** 1、把日志文件从linux本地上传到HDFS

** 2、把上传的文件导入对应的Hive表[表1]

** 3、利用Hive进行业务需求分析

** 4、Hive语句的分析结果临时存储在Hive表[表2]

** 5、利用sqoop把Hive表[表2]的数据导入mysql永久存储

数据源文件：

2019082818 --自定义数据

2019082819 --自定义数据

1、创建Hive分区表　　[表1]

** create database if not exists mydb;

create table if not exists mydb.track_log(

id string,

url string,

referer string,

keyword string,

type string,

guid string,

pageId string,

moduleId string,

linkId string,

attachedInfo string,

sessionId string,

trackerU string,

trackerType string,

ip string,

trackerSrc string,

cookie string,

orderCode string,

trackTime string,

endUserId string,

firstLink string,

sessionViewNo string,

productId string,

curMerchantId string,

provinceId string,

cityId string,

fee string,

edmActivity string,

edmEmail string,

edmJobId string,

ieVersion string,

platform string,

internalKeyword string,

resultSum string,

currentPage string,

linkPosition string,

buttonPosition string

)

partitioned by (date string,hour string)

row format delimited fields terminated by '';

2、上传数据HDFS,再创建Hive表去关联

2.1 创建目录 在hadoop cdh 那个目录下

$ bin/hdfs dfs -mkdir -p /user/hive/warehouse

/mydb.db/track_log/date=20190828/hour=18

$ bin/hdfs dfs -mkdir -p /user/hive/warehouse

/mydb.db/track_log/date=20190828/hour=19

2.2 上传文件到对应的目录

$ bin/hdfs dfs -put /home/tom/2015082818 /user/hive

/warehouse/mydb.db/track_log/date=20190828/hour=18

$ bin/hdfs dfs -put /home/tom/2019082819 /user/hive

/warehouse/mydb.db/track_log/date=20190828/hour=19

2.3 给分区表关联数据 在hive里执行

alter table track_log add partition(date='20190828',hour='18')

location "/user/hive/warehouse/mydb.db/track_log

/date=20190828/hour=18";

alter table track_log add partition(date='20150828',hour='19')

location "/user/hive/warehouse/mydb.db/track_log

/date=20150828/hour=19";

3、利用Hive分析每天每个小时PV、UV

** 创建临时表用于临时保存当天的分析记录

create table mydb.tracklog_pvuv_hourly(

date string,

hour string,

pv string,

uv string

)row format delimited fields terminated by '';

** 把分析结果保存到临时表

** url为链接，guid为唯一用户ID

insert overwrite table mydb.tracklog_pvuv_hourly

select date,hour,count(url),count(distinct guid)

from track_log where date='20190828'

group by date,hour;

4、利用sqoop把分析结果导入mysql表存储

4.1在mysql里面创建表：

create table mydb.tracklog_pvuv_hourly(

date int,

hour int,

pv bigint,

uv bigint

);

4.2 创建一个opt文件

vi job2.opt

export

--connect

jdbc:mysql://li:3306/mydb

--username

root

--password

root

--table

tracklog_pvuv_hourly

--num-mappers

1

--export-dir

/user/hive/warehouse/mydb.db/tracklog_pvuv_hourly

--input-fields-terminated-by

""

4.3 执行脚本文件

$ bin/sqoop --options-file job2.opt

以上操作完称之后在mysql里面执行

mysql> select * from mydb.tracklog_pvuv_hourly

查看分析的数据.

hello