hive:处理JSON格式(get_json_object()函数),连续登录题型,快速生成表(explode,stack),迭代计算

处理JSON格式

Json格式的数据是由键值对组成的非结构化数据

示例

{"id": 1, "name": "Alice", "age": 25, "address": {"city": "New York", "state": "NY", "zipcode": "10001"}, "skills": ["Java", "SQL", "Hive"], "active": true}
{"id": 2, "name": "Bob", "age": 30, "address": {"city": "Los Angeles", "state": "CA", "zipcode": "90001"}, "skills": ["Python", "Spark", "Hadoop"], "active": false}
{"id": 3, "name": "Charlie", "age": 28, "address": {"city": "Chicago", "state": "IL", "zipcode": "60601"}, "skills": ["Scala", "SQL", "Kafka"], "active": true}

在对非结构化数据进行数据清洗时，先创建一张表存储这类数据

CREATE TABLE json_table (
    json_col STRING
)
STORED AS TEXTFILE
location 'hdfs:///user/hive/warehouse/yzd1031_lvzx.db/json_table';

insert into json_table values
('{"id": 1, "name": "Alice", "age": 25, "address": {"city": "New York", "state": "NY", "zipcode": "10001"}, "skills": ["Java", "SQL", "Hive"], "active": true}'),
('{"id": 2, "name": "Bob", "age": 30, "address": {"city": "Los Angeles", "state": "CA", "zipcode": "90001"}, "skills": ["Python", "Spark", "Hadoop"], "active": false}'),
('{"id": 3, "name": "Charlie", "age": 28, "address": {"city": "Chicago", "state": "IL", "zipcode": "60601"}, "skills": ["Scala", "SQL", "Kafka"], "active": true}');

将上述的表中Json格式数据进行拆分，拆分成表中的多个字段的值，可以使用Hive函数get_json_object()

SELECT GET_JSON_OBJECT(json_col, '$.id')              AS id,
       GET_JSON_OBJECT(json_col, '$.name')            AS name,
       GET_JSON_OBJECT(json_col, '$.age')             AS age,
       GET_JSON_OBJECT(json_col, '$.address.city')    AS city,
       GET_JSON_OBJECT(json_col, '$.address.state')   AS state,
       GET_JSON_OBJECT(json_col, '$.address.zipcode') AS zipcode,
       GET_JSON_OBJECT(json_col, '$.active')          AS active
FROM json_table;

补充:

JSON格式

JSON（JavaScript Object Notation，JavaScript对象表示法）是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。它基于JavaScript（一种广泛使用的网页脚本语言,Script 脚本）的一个子集，但是它是独立于语言的，许多不同的编程语言都支持生成和解析JSON数据。

数据类型是对象的JSON格式示例

{
  "person": {
    "name": "Kimi",
    "age": 30,
    "is_student": false,
    "address": {
      "street": "123 Main St",
      "city": "Anytown",
      "state": "CA",
      "zip": "12345"
    },
    "hobbies": ["reading", "coding", "traveling"],
    "skills": {
      "languages": ["Python", "Java", "JavaScript"],
      "frameworks": ["React", "Django", "Spring"]
    }
  }
}

location

location 'hdfs:///user/hive/warehouse/yzd1031_lvzx.db/json_table';

在HDFS（Hadoop Distributed File System）的URI（Uniform Resource Identifier）中，hdfs://后面跟着三个斜杠///是为了明确指定协议和指定HDFS文件系统的根目录。

• hdfs://：这是协议部分，告诉系统这是一个HDFS资源。

• 三个斜杠///：第一个斜杠是协议和主机名之间的分隔符，后两个斜杠//表示HDFS的根目录。在HDFS中，/是根目录的标识，而//则明确指定了这是HDFS的根目录，而不是其他文件系统的根目录。

URL部分：

hdfs:///user/hive/warehouse/yzd1031_lvzx.db/json_table

• 协议：hdfs:// 表示这是一个HDFS协议的URL，用于访问Hadoop分布式文件系统中的资源。

• 路径：/user/hive/warehouse/yzd1031_lvzx.db/json_table 是在HDFS中的具体路径，其中：

  • /user/hive/warehouse/ 是Hive的默认仓库目录。

  • yzd1031_lvzx.db/ 是数据库名称。

  • json_table 是表的名称。

get_json_object()

连续登录题型

当前有一份用户登录数据, 数据中有两个字段，分别是user_id和login_date。

user_id表示唯一的用户ID，login_date表示用户的登录日期 , 现在需要对用户的登录次数进行统计，得到连续登陆N（N>=2）天的用户。

建表

DROP TABLE user_login;
CREATE TABLE user_login
(
    user_id    int,
    login_date string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LOCATION 'hdfs:///user/hive/warehouse/yzd1031_lvzx.db/user_login';

SELECT *
FROM user_login;

-- 查询其中连续3天登录的用户

WITH DistinctLogins AS (
    SELECT DISTINCT user_id, login_date
    FROM user_login
),
RankedLogins AS (
    SELECT 
        user_id, 
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM DistinctLogins
),
DateDifferences AS (
    SELECT 
        user_id,
        login_date,
        date_sub(login_date, rn) AS grp
    FROM RankedLogins
),
ConsecutiveLogins AS (
    SELECT 
        user_id,
        COUNT(*) AS consecutive_days
    FROM DateDifferences
    GROUP BY user_id, grp
)
SELECT DISTINCT user_id
FROM ConsecutiveLogins
WHERE consecutive_days >= 3;

要查询连续3天登录的用户，步骤

1.去重处理：首先去除同一用户在同一天内的重复登录记录

2.排序和分组：对每个用户的登录日期进行排序。

3.计算连续登录天数：通过窗口函数计算每个用户的连续登录天数。

4筛选连续3天登录的用户：筛选出连续登录天数达到3天的用户。

4个CTE

1.表DistinctLogins

用于对数据去重>>DISTINCT user_id, login_date>>确保每个用户每天只保留一条登录记录

2.表RankedLogins

对用户id(user_id)分区且按照登陆日期(login_date)对数据进行排序

>>ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn

目的：为每个用户的登录日期分配一个连续的行号（rn），以便后续计算连续登录天数

3.表DateDifferences

目的：通过登陆日期减去对应行号( date_sub(login_date, rn))，生成一个新的日期作为分组标识（grp），用于标识连续的登录日期。

• 对于每个用户，如果登录日期是连续的，grp 的值会相同。例如：

  • 用户A在2023-10-01、2023-10-02、2023-10-03登录，grp 的值都是2023-09-30（因为 2023-10-01 - 1 = 2023-09-30，2023-10-02 - 2 = 2023-09-30，2023-10-03 - 3 = 2023-09-30）。

  • 如果用户A在2023-10-01、2023-10-03登录（中间断了一天），grp 的值会不同（2023-10-01 - 1 = 2023-09-30，2023-10-03 - 2 = 2023-10-01）。

4.表ConsecutiveLogins 

计算每个用户在每个分组中的连续登录天数。

• GROUP BY user_id, grp 会将每个用户的登录记录按 grp 进行分组。

• 对于每个分组，COUNT(*) 会计算该分组中的记录数，即连续登录的天数。

• 例如：

  • 用户A在2023-10-01、2023-10-02、2023-10-03登录，grp 的值都是2023-09-30，COUNT(*) 的结果是3。

  • 用户A在2023-10-01、2023-10-03登录，grp 的值分别是2023-09-30和2023-10-01，COUNT(*) 的结果分别是1和1。

主查询

用consecutive_days >= 3过滤出连续登录天数大于3的用户, 使用DISTINCT user_id对用户进行去重

为什么前面的CTE临时表ConsecutiveLogins已经对用户ID进行分组, 主查询还需要对用户ID进行分组?

• 在 ConsecutiveLogins 中，GROUP BY user_id, grp 会对每个用户的连续登录天数进行分组计算。

• 如果一个用户在不同的时间段内有多次连续登录达到3天的情况，那么在 ConsecutiveLogins 的结果中，这个用户ID可能会出现多次。

快速生成表  

生成一列数据的表

-- 需求1: 请生成一列数据, 内容为 1,2,3,4,5

select explode(array(1,2,3,4,5)) as num;

返回

返回的 num 列是数值类型（整数）。这是因为 array(1,2,3,4,5) 中的元素本身就是数值类型

select explode(split('1,2,3,4,5',',')) as num;

返回

这里，num 是每一行中数组元素的列名。每行显示一个数组元素的值，从 "1" 到 "5"。注意，这些值是字符串类型，因为原始字符串 '1,2,3,4,5' 包含了字符串格式的数字。如果需要将这些值转换为数值类型，可以使用 CAST() 函数进行转换，例如：

SELECT explode(split('1,2,3,4,5',',')) as str_num, CAST(str_num AS INT) as num;

返回

-- 需求2: 请生成一列数据, 内容为 1~100:

返回

这里，col 是默认生成的列名，你可以通过 AS 关键字来指定一个更有意义的列名，例如：

SELECT explode(sequence(1, 100)) AS num;

 生成多行多列的表

--需求1: 生成一行两行两列的数据, 一行 放置   男  M   女 F

函数: stack(N, 数据列表...)

stack 函数用于将多个列“堆叠”成多行, n把数据分成n份>>n行

stack 函数的单独使用 

SELECT stack(2, '男', 'M', '女', 'F');

stack函数和侧视图

SELECT id, stacked_col
FROM example_table
LATERAL VIEW stack(3, col1, col2, col3) stacked_table AS stacked_col;

• stack(3, col1, col2, col3)：将 col1, col2, col3 堆叠成3行。

• LATERAL VIEW：用于将 stack 函数生成的多行结果与原始表的每一行进行连接。

Hive 的语法规定，在使用 LATERAL VIEW 时，必须为生成的结果集指定表别名/列别名。

表数据的存储

• 方式一: 可以通过子查询的方式, 将SQL作为一个临时结果集使用

• 方式二: 通过 with as 的方式

• 方式三: 通过构建表的方式

• 方式四: 通过创建视图的方式(临时视图和永久视图)

• 方式五: 基于缓存表的方式来构建: 将数据放置到缓存(内存)中

-- 方式一: 可以通过子查询的方式, 将SQL作为一个临时结果集使用
select
    *
from (select  stack(2,'男','M','女','F')) as t1;

-- 方式二: 通过 with as 的方式
with t2 as (
    select  stack(2,'男','M','女','F')
)
select * from t2;

-- 方式三: 通过构建表的方式:
create table if not exists t3 as
select  stack(2,'男','M','女','F');

select  * from t3;

-- 方式四: 通过创建视图的方式(临时视图和永久视图)
-- 演示临时视图
create or replace temporary view  t4 as
select  stack(2,'男','M','女','F');

select  * from t4;

-- 演示 永久视图:
create or replace  view  t5 as
select  stack(2,'男','M','女','F');

select * from t5;

-- 临时视图和永久视图的区别:
--      临时视图只能在当前会话有效, 关闭这个会话就无法使用, 而永久视图是可以跨越多个会话的

cache table t6 as
select  stack(2,'男','M','女','F');

返回

表示一个二维数组，其中每个内部数组包含两个字符串元素

迭代计算

横向迭代

需求: 已知 c1列数据, 计算出 c2 和 c3列数据

-- 初始化c1列数据

create or replace  temporary  view  t1 as
select  explode(array(1,2,3)) as c1;

-- 计算c2列

with t2 as (
    select
        c1,
        c1 +2 as c2
    from  t1
)

-- 计算c3列

select
    c1,
    c2,
    c1 * (c2+3) as c3
from  t2 ;

横向迭代计算, 一列一列的进行计算即可, 计算完成一列后, 将结果临时保存一下(with as 和 视图方式 ), 然后基于这个结果计算下一列的数据

纵向迭代

需求: 计算 c4

计算逻辑: 当c2=1 , 则 c4=1 ; 否则 c4 = (上一个c4 + 当前的c3)/2

-- 初始化数据集

-- 创建表
CREATE TABLE t2 (
    c1 INT,
    c2 INT,
    c3 INT
);

-- 插入数据
INSERT INTO t2 VALUES
    (1, 1, 6),
    (1, 2, 23),
    (1, 3, 8),
    (1, 4, 4),
    (1, 5, 10),
    (2, 1, 23),
    (2, 2, 14),
    (2, 3, 17),
    (2, 4, 20);

create or replace  view t2_1 as
select
    c1,
    c2,
    c3,
    if(c2 = 1, 1,null) as c4
from t2;

create or replace  temporary  view  t2_2 as
select
   c1,
   c2,
   c3,
   if(c2=1,1, (lag(c4,1) over(partition by c1 order by c2) + c3)/2 ) as c4
from t2_1;

• 这里的“上一个 c4”需要根据某种顺序来确定，而需求中并没有明确说明顺序，但结合上下文可以推断出顺序。

• 由于 c2 是决定 c4 值的关键字段，可以推断出“上一个 c4”应该按照 c2 的顺序来确定。