完美- sql题

用户活跃数据

请进行批处理
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;