用户活跃数据
请进行批处理
Q1: 将附件中 ip_china.csv.zip文件加载为 Hive 内部表，保持格式与 csv header一致，表需要开启压缩
Q2: 将附件中 login_data.csv.zip文件加载为 Hive 外部表，保持格式与csv header一致，表需要开启压缩，需要按日分区
Q3: 通过Q1，Q2加载的数据，将用户登陆表中的ip转化为对应的国家地区并落表（避免笛卡尔积）
Q4: 请输出每个分区下，每个province的去重登陆人数。输出结构为 pt，province，cnt_login
Q5: 请输出总量数据下，存在登陆数据的各个province中，登陆时间最早的前3人及对应的登陆时间，若不满3人，需要留空。输出结构为 province，account_id_1, login_time_1, account_id_2, login_time_2, account_id_3, login_time_3
Q1 问题解决及思路
-- 查看数据库
show databases ;
-- 切换数据库
use wanmei;
/*
hive中 常见的文件格式
Textfile
SequenceFile
RCFile
ORCFile 列示存储文件 支持二级索引
Parquet
Avro
常见的压缩算法
snappy
zlib
gzip
*/
-- 问题1:
-- Q1: 将附件中 ip_china.csv.zip文件加载为 Hive 内部表，保持格式与 csv header一致，表需要开启压缩
/*
todo 分析:
step1: 首先创建表
内部表
` 格式于 csv header 一样
表需要压缩 -- 压缩算法 snappy zlmb Gzip
字段信息
ip_start,ip_end,long_ip_start,long_ip_end,country,province
1.0.1.0,1.0.3.255,16777472,16778239,中国,福建
step2: 加载数据到表中
使用lode data inpath
注意 lode 命令 是纯复制粘贴命令,不会对文件做任何更改,所以即使是加载后的
todo 创建hive内部表 将数据load data 导入
创建 hive内部表 使用 inset+select 开启 snappy 压缩 导入
*/
--todo 1.创建hive内部表 将数据load data 导入
-- a 创建一个 中间表,将数据 导入进去
create table ip_china
(
ip_start char(20),
ip_end char(20),
long_ip_start char(20),
long_ip_end char(20),
country char(20),
province char(20)
)row format delimited fields terminated by ","
stored AS TEXTFILE ;
-- todo 2 加载数据
-- 因为load data 命令是纯粘贴 复制 操作,原理是text文件,现在也是 没有经过压缩的text文件
load data local inpath "/root/wanmei/ip_china.csv" into table ip_china;
-- 查询样本数据
select * from wanmei.ip_china limit 10;
-- todo 3 创建正式表
create table if not exists wanmei.ip_china_user(
ip_start STRING,
ip_end STRING,
long_ip_start BIGINT,
long_ip_end BIGINT,
country STRING,
province STRING
)
row format delimited fields terminated by ","
stored as textfile;
-- todo 4设置MapReduce运行是,Reduce 输出数据压缩格式为snappy
-- 开启压缩算法
--1）开启hive最终输出数据压缩功能
set hive.exec.compress.output = true;
--2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress = true;
--3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
--4）设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type = BLOCK;
-- 开启本地执行模式
SET hive.exec.mode.local.auto = true ;
-- todo 4采用inset+select 语句将临时表数据查询插入正式表
insert into wanmei.ip_china_user select * from ip_china;
-- 查询样本
select * from wanmei.ip_china_user limit 10;
Q2 问题解决及思路
/*
Q2: 将附件中 login_data.csv.zip文件加载为 Hive 外部表，保持格式与csv header一致，表需要开启压缩，需要按日分区
分析
表数据
logtime account_id ip
2019/7/15 0:00 102325 223.116.97.23
2019/7/15 0:00 221977 223.104.247.162
建表 分区表 , 外部表 ,开启压缩
todo 思路
一 创建临时表 load data 导入数据
文本文件数据
二 开启动态分区,非严格模式
采用 insert+select 导入
*/
//todo 1.创建临时表 login_data_tmp
create table if not exists wanmei.login_data_tmp(
logtime string,
account_id bigint,
ip string
)row format delimited fields terminated by ","
stored as TEXTFILE;
// 加载数据
load data local inpath "/root/wanmei/login_data.csv" into table wanmei.login_data_tmp;
// 查询数据
select * from login_data_tmp limit 10;
//todo 2.创建分区表
create external table if not exists wanmei.login_data(
account_id bigint,
ip string
)partitioned by (logtime string)
row format delimited fields terminated by ","
stored as TEXTFILE;
//todo 3.开启压缩
--1）开启hive最终输出数据压缩功能
set hive.exec.compress.output = true;
--2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress = true;
--3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
--4）设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type = BLOCK;
//todo 4.开启动态分区 非严格模式
-- 1) 开启动态分区功能
set hive.exec.dynamic.partition = true;
-- 2) 指定动态分区模式：非严格模式
set hive.exec.dynamic.partition.mode = nonstrict;
//todo 5. 插入 查询数据 指定分区
insert into wanmei.login_data partition (logtime) select account_id,ip,logtime from wanmei.login_data_tmp;
-- 查询样本数据
select * from login_data limit 10;
Q3 问题解决及思路
/*
Q3: 通过Q1，Q2加载的数据，将用户登陆表中的ip转化为对应的国家地区并落表（避免笛卡尔积）
需求
用户表 ip_start, ip_end, long_ip_start, long_ip_end, country,province
登录表 account_id , ip ,logtime
宽表 account_id , ip ,province
todo ip_china_user (大表 ) left join login_data(小表 维度表) ->得到宽表数据
通过 CTAS create table as select 方式 创建表
todo 只需要判断 ip 再那个范围内就能确定省份 (ip_start, ip_end)
ip_start <= ip <= ip_end
long_ip_start<= ip_long <= long_ip_end
todo 只需要将 string类型的ip 转成 long类型
步骤
一 将ip string 转成long 类型
-- 第1种：直接相乘256数字
SELECT
t.*,
cast(split(t.ip, "\\.")[0] as bigint)*256*256*256
+ cast(split(t.ip, "\\.")[1] as bigint)*256*256
+ cast(split(t.ip,"\\.")[2] as bigint)*256
+ cast(split(t.ip,"\\.")[3] as bigint) AS ip_long
FROM db_interview.tbl_login_data t
LIMIT 10 ;
-- 第2种：使用左位移函数
SELECT
t.*,
(
shiftleft(cast(split(t.ip, "\\.")[0] as bigint), 24) +
shiftleft(cast(split(t.ip, "\\.")[1] as bigint), 16) +
shiftleft(cast(split(t.ip, "\\.")[2] as bigint), 8) +
cast(split(t.ip, "\\.")[3] as bigint)
) AS ip_long
FROM db_interview.tbl_login_data t
LIMIT 10 ;
二 用户登录表关联维度表 ,字段ip_long 与 long_ip_start long_ip_end 对比
*/
-- todo step1:用户表数据 ip地址转成long类型
-- 测试
select ip, cast(split(ip,"\\.")[0] as bigint)*256*256*256
+ cast(split(ip,"\\.")[1] as bigint)*256*256
+ cast(split(ip,"\\.")[2] as bigint)*256
+ cast(split(ip,"\\.")[3] as bigint) from wanmei.login_data limit 20;
-- 方式二
SELECT
t.*,
(
shiftleft(cast(split(t.ip, "\\.")[0] as bigint), 24) +
shiftleft(cast(split(t.ip, "\\.")[1] as bigint), 16) +
shiftleft(cast(split(t.ip, "\\.")[2] as bigint), 8) +
cast(split(t.ip, "\\.")[3] as bigint)
) AS ip_long
FROM wanmei.login_data t
LIMIT 10 ;
-- 户登录表关联维度表 ,字段ip_long 与 long_ip_start long_ip_end 对比
with ip_long_tmp as(
SELECT
t.*,
(
shiftleft(cast(split(t.ip, "\\.")[0] as bigint), 24) +
shiftleft(cast(split(t.ip, "\\.")[1] as bigint), 16) +
shiftleft(cast(split(t.ip, "\\.")[2] as bigint), 8) +
cast(split(t.ip, "\\.")[3] as bigint)
) AS ip_long
FROM wanmei.login_data t
)
select ip_long_tmp.account_id,ip_long_tmp.ip_long,c.province
from ip_long_tmp left join ip_china_user c
where ip_long_tmp.ip_long >= c.long_ip_start and ip_long_tmp.ip_long <= c.long_ip_end ;
-- todo 通过 CTAS create table as select 语法 创建新的表
create table if not exists chain_uesr_dwd
row format delimited fields terminated by ","
stored as TEXTFILE
as
with ip_long_tmp as(
SELECT
t.*,
(
shiftleft(cast(split(t.ip, "\\.")[0] as bigint), 24) +
shiftleft(cast(split(t.ip, "\\.")[1] as bigint), 16) +
shiftleft(cast(split(t.ip, "\\.")[2] as bigint), 8) +
cast(split(t.ip, "\\.")[3] as bigint)
) AS ip_long
FROM wanmei.login_data t
)
select ip_long_tmp.account_id,ip_long_tmp.ip_long,c.province
from ip_long_tmp left join ip_china_user c
where ip_long_tmp.ip_long >= c.long_ip_start and ip_long_tmp.ip_long <= c.long_ip_end
;
-- 查看 数据
select * from chain_uesr_dwd limit 10;
网友评论