AI驱动大数据开发(1)

本文借助ChatGPT4.0进行Hive SQL的编写与优化。涵盖业务库ddl转数仓ddl、编写SQL算指标、解释与优化SQL三个场景。通过多轮提示与反馈,完成SQL编写与优化,如将200多行SQL优化至33行,提升了可读性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

工具:ChatGPT4.0

编写Hive SQL 

场景一:业务库ddl转数仓ddl

下面是一个Mysql中的ddl,尝试将它转为Hive中带有分区字段的ddl。

CREATE TABLE `district` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `create_time` datetime DEFAULT NULL,
 `deleted` bit(1) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 `version` int(11) DEFAULT NULL,
 `area_code` bigint(20) DEFAULT NULL,
 `city_code` varchar(255) DEFAULT NULL,
 `lat` decimal(19,2) DEFAULT NULL,
 `level` int(11) DEFAULT NULL,
 `lng` decimal(19,2) DEFAULT NULL,
 `merger_name` varchar(255) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `parent_code` bigint(20) DEFAULT NULL,
 `pinyin` varchar(255) DEFAULT NULL,
 `short_name` varchar(255) DEFAULT NULL,
 `zip_code` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

第一轮提示
提示

CREATE TABLE `district` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `create_time` datetime DEFAULT NULL,
 `deleted` bit(1) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 `version` int(11) DEFAULT NULL,
 `area_code` bigint(20) DEFAULT NULL,
 `city_code` varchar(255) DEFAULT NULL,
 `lat` decimal(19,2) DEFAULT NULL,
 `level` int(11) DEFAULT NULL,
 `lng` decimal(19,2) DEFAULT NULL,
 `merger_name` varchar(255) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `parent_code` bigint(20) DEFAULT NULL,
 `pinyin` varchar(255) DEFAULT NULL,
 `short_name` varchar(255) DEFAULT NULL,
 `zip_code` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

将上面的ddl转为hive 3.0里可用的ddl。

所有的字段都尽可能地加上建议性的comment。

表要使用orc存储格式,snappy压缩,并加上一个名为dt的分区字段。

回答

以下是要求将MySQL表DDL转换为适用于Hive 3.0的DDL:

CREATE EXTERNAL TABLE district (
  id BIGINT COMMENT '主键ID',
  create_time TIMESTAMP COMMENT '创建时间',
  deleted BOOLEAN COMMENT '是否删除',
  update_time TIMESTAMP COMMENT '更新时间',
  version INT COMMENT '版本号',
  area_code BIGINT COMMENT '区域代码',
  city_code STRING COMMENT '城市代码',
  lat DECIMAL(19,2) COMMENT '纬度',
  level INT COMMENT '层级',
  lng DECIMAL(19,2) COMMENT '经度',
  merger_name STRING COMMENT '合并名称',
  name STRING COMMENT '名称',
  parent_code BIGINT COMMENT '父级代码',
  pinyin STRING COMMENT '拼音',
  short_name STRING COMMENT '简称',
  zip_code BIGINT COMMENT '邮政编码'
)
COMMENT '地区信息表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');
 

这个DDL创建了一个名为district的外部表,并为所有字段添加了建议性的comment。表使用了ORC存储格式、Snappy压缩,并添加了一个名为dt的分区字段。请注意,由于Hive不支持BIT类型,因此我将deleted字段的数据类型从bit(1)更改为BOOLEAN

反馈

如果发现SQL不是很合适,可以继续迭代提示来对SQL进行局部修改。

第二轮提示
提示

为刚才的HiveSQL加上一个location的设置,它的路径是/warehouse/gmall/

回答

在Hive中创建表时,可以通过LOCATION子句指定表的HDFS存储路径。这里是更新后的Hive DDL,包含了您指定的LOCATION设置:

CREATE EXTERNAL TABLE district (
  id BIGINT COMMENT '主键ID',
  create_time TIMESTAMP COMMENT '创建时间',
  deleted BOOLEAN COMMENT '是否删除',
  update_time TIMESTAMP COMMENT '更新时间',
  version INT COMMENT '版本号',
  area_code BIGINT COMMENT '区域代码',
  city_code STRING COMMENT '城市代码',
  lat DECIMAL(19,2) COMMENT '纬度',
  level INT COMMENT '层级',
  lng DECIMAL(19,2) COMMENT '经度',
  merger_name STRING COMMENT '合并名称',
  name STRING COMMENT '名称',
  parent_code BIGINT COMMENT '父级代码',
  pinyin STRING COMMENT '拼音',
  short_name STRING COMMENT '简称',
  zip_code BIGINT COMMENT '邮政编码'
)
COMMENT '地区信息表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY')
LOCATION '/warehouse/gmall/';

现在,这个表的数据将存储在指定的HDFS路径/warehouse/gmall/中。请注意,这是一个外部表,因此Hive不会在删除表时自动删除HDFS上的数据。这有助于在删除和重新创建表时保留数据。

反馈

已经满足需求。

场景二 编写 SQL算指标

有一张hive表有如下字段,

hl_code, --总公司ID 
hl_name, --总公司名称 
comp_id, --分公司ID 
comp_nm, --分公司名称 
year_id, --年份 
month_id, --月份 
yysr, --实际月度营业收入 
ydjhz, --计划月度营业收入 
ndjhz, --计划年度营业收入 

hiveSQL计算出来一张宽表 字段包括(总公司月度收入,总公司计划月度收入,总公司去年同期月度收入,总公司季度收入,总公司计划季度收入,总公司去年同期季度收入,总公司年度收入,总公司去年同期年度收入)

第一轮提示
提示

有一张hive表有如下字段,

hl_code, --总公司ID
hl_name, --总公司名称
comp_id, --分公司ID
comp_nm, --分公司名称
year_id, --年份
month_id, --月份
yysr, --实际月度营业收入
ydjhz, --计划月度营业收入
ndjhz, --计划年度营业收入

hiveSQL计算出来一张宽表 字段包括(总公司月度收入,总公司计划月度收入,总公司去年同期月度收入,总公司季度收入,总公司计划季度收入,总公司去年同期季度收入,总公司年度收入,总公司去年同期年度收入)

回答

以下是一个Hive SQL查询,用于计算您要求的各个指标。请根据您的实际表名替换your_table_name

WITH monthly AS (
  SELECT hl_code, hl_name, year_id, month_id,
         SUM(yysr) AS yysr_sum, SUM(ydjhz) AS ydjhz_sum
  FROM your_table_name
  GROUP BY hl_code, hl_name, year_id, month_id
),
quarterly AS (
  SELECT hl_code, hl_name, year_id, CEIL(month_id / 3) AS quarter_id,
         SUM(yysr) AS yysr_sum, SUM(ydjhz) AS ydjhz_sum
  FROM your_table_name
  GROUP BY hl_code, hl_name, year_id, CEIL(month_id / 3)
),
yearly AS (
  SELECT hl_code, hl_name, year_id,
         SUM(yysr) AS yysr_sum, SUM(ndjhz) AS ndjhz_sum
  FROM your_table_name
  GROUP BY hl_code, hl_name, year_id
)
SELECT m.hl_code, m.hl_name, m.year_id, m.month_id,
       m.yysr_sum AS month_yysr, m.ydjhz_sum AS month_ydjhz,
       lag(m.yysr_sum) OVER (PARTITION BY m.hl_code ORDER BY m.year_id) AS month_yysr_ly,
       q.yysr_sum AS quarter_yysr, q.ydjhz_sum AS quarter_ydjhz,
       lag(q.yysr_sum) OVER (PARTITION BY q.hl_code ORDER BY q.year_id) AS quarter_yysr_ly,
       y.yysr_sum AS year_yysr, y.ndjhz_sum AS year_ndjhz,
       lag(y.yysr_sum) OVER (PARTITION BY y.hl_code ORDER BY y.year_id) AS year_yysr_ly
FROM monthly m
JOIN quarterly q ON m.hl_code = q.hl_code AND m.year_id = q.year_id AND CEIL(m.month_id / 3) = q.quarter_id
JOIN yearly y ON m.hl_code = y.hl_code AND m.year_id = y.year_id;

这个查询首先创建了3个临时表(monthlyquarterlyyearly),分别计算每月、每季度和每年的营业收入和计划收入。然后,通过将这些临时表连接在一起,并使用窗口函数lag()计算上年同期收入,生成了您所要求的宽表。请注意,这个查询使用了窗口函数,因此需要Hive 0.11.0或更高版本。

反馈

我们很快就得到了一个SQL,这个时候应当对SQL进行充分的测试。如果当前没有测试环境,那么其实可以让GPT来为你生成测试用的模拟数据。

第二轮提示
提示

请为刚才的表写一个hive ddl。并使用hive sql向里面插入足够的测试数据

回答

首先,这是一个示例DDL,用于创建包含所需字段的表。请注意,根据您的需求,您可能需要根据实际情况调整数据类型和COMMENT。

CREATE EXTERNAL TABLE company_financials (
  hl_code STRING COMMENT '总公司ID',
  hl_name STRING COMMENT '总公司名称',
  comp_id STRING COMMENT '分公司ID',
  comp_nm STRING COMMENT '分公司名称',
  year_id INT COMMENT '年份',
  month_id INT COMMENT '月份',
  yysr DOUBLE COMMENT '实际月度营业收入',
  ydjhz DOUBLE COMMENT '计划月度营业收入',
  ndjhz DOUBLE COMMENT '计划年度营业收入'
)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

接下来,我们将使用Hive SQL插入一些示例数据。请注意,这些数据是随机生成的,可能需要根据实际情况进行调整。

INSERT INTO company_financials (hl_code, hl_name, comp_id, comp_nm, year_id, month_id, yysr, ydjhz, ndjhz) VALUES
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 1, 10000, 11000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 2, 12000, 13000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 3, 15000, 16000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 4, 18000, 19000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 5, 20000, 21000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 6, 24000, 25000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 7, 26000, 27000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 8, 28000, 29000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 9, 31000, 32000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 10, 33000, 34000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 11, 35000, 36000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2021, 12, 38000, 39000, 120000),
('HL001', '总公司1', 'COMP1', '分公司1', 2022, 1, 11000, 12000, 130000

反馈

这个时候回答已经中断了,最后一个SQL缺少了);做结尾。现在,如果觉得数据量不够,测试场景不够全面,那么还可以让它继续生成数据。

第三轮提示
提示

继续生成数据,添加一些额外的总公司和分公司

回答

以下是一些包含额外总公司和分公司的示例数据,可以将其添加到先前插入的数据中以进行测试。

INSERT INTO company_financials (hl_code, hl_name, comp_id, comp_nm, year_id, month_id, yysr, ydjhz, ndjhz) VALUES
('HL001', '总公司1', 'COMP1', '分公司1', 2022, 2, 13000, 14000, 130000),
('HL001', '总公司1', 'COMP1', '分公司1', 2022, 3, 16000, 17000, 130000),
('HL001', '总公司1', 'COMP2', '分公司2', 2021, 1, 9000, 10000, 110000),
('HL001', '总公司1', 'COMP2', '分公司2', 2021, 2, 11000, 12000, 110000),
('HL001', '总公司1', 'COMP2', '分公司2', 2021, 3, 14000, 15000, 110000),
('HL002', '总公司2', 'COMP3', '分公司3', 2021, 1, 8000, 9000, 100000),
('HL002', '总公司2', 'COMP3', '分公司3', 2021, 2, 10000, 11000, 100000),
('HL002', '总公司2', 'COMP3', '分公司3', 2021, 3, 13000, 14000, 100000),
('HL002', '总公司2', 'COMP4', '分公司4', 2021, 1, 7000, 8000, 90000),
('HL002', '总公司2', 'COMP4', '分公司4', 2021, 2, 9000, 10000, 90000),
('HL002', '总公司2', 'COMP4', '分公司4', 2021, 3, 12000, 13000, 90000);

这些示例数据包括两个总公司(总公司1和总公司2)以及四个分公司(分公司1、分公司2、分公司3和分公司4)。这些数据可以用于测试查询以确保它们可以正确处理多个总公司和分公司的情况。

反馈

现在,可以在本地的虚拟机中来测试之前的计算逻辑是否正确。

场景三 解释与优化SQL

下面有一个超长SQL等待去维护

SELECT 
  two_day_rate, 
  seven_day_rate 
from 
  (
    SELECT 
      two_day_rate 
    from 
      (
        SELECT 
          id, 
          rk, 
          `date`, 
          date1, 
          rk2, 
          max_login, 
          add_money, 
          modify_money, 
          sum(
            if(modify_money is not null, 1, 0)
          )/ sum(
            if(add_money is not null, 1, 0)
          ) two_day_rate 
        from 
          (
            SELECT 
              id, 
              rk, 
              `date`, 
              date1, 
              rk2, 
              max_login, 
              add_money, 
              if(rk2 = max_login, null, add_money) modify_money 
            from 
              (
                SELECT 
                  tt1.id, 
                  rk, 
                  tt1.`date`, 
                  date1, 
                  rk2, 
                  max_login, 
                  add_money 
                from 
                  (
                    SELECT 
                      id, 
                      rk, 
                      `date`, 
                      date1, 
                      rk2, 
                      max_login 
                    from 
                      (
                        SELECT 
                          id, 
                          rk, 
                          `date`, 
                          date1, 
                          rk2, 
                          max(rk2) over(partition by id, date1) max_login 
                        from 
                          (
                            SELECT 
                              id, 
                              rk, 
                              `date`, 
                              date1, 
                              ROW_NUMBER() over (partition by id, date1) rk2 
                            FROM 
                              (
                                SELECT 
                                  id, 
                                  rk, 
                                  `date`, 
                                  DATE_SUB(`date`, INTERVAL rk day) date1 
                                from 
                                  (
                                    SELECT 
                                      id, 
                                      `date`, 
                                      ROW_NUMBER() over (
                                        PARTITION by id 
                                        ORDER BY 
                                          `date`
                                      ) rk 
                                    FROM 
                                      demo3
                                  ) t1
                              ) t2
                          ) t3
                      ) t4
                  ) tt1 
                  LEFT JOIN demo3_red dr on tt1.id = dr.id 
                  and tt1.`date` = dr.`date`
              ) tt2
          ) tt3
      ) tt4
  ) ttt1 
  join (
    SELECT 
      seven_day_rate 
    from 
      (
        SELECT 
          id, 
          rk, 
          `date`, 
          date1, 
          rk2, 
          max_login, 
          add_money, 
          modify_money, 
          modify_sev_money, 
          sum(
            IF(modify_sev_money is not null, 1, 0)
          ) / sum(
            if(add_money is not null, 1, 0)
          ) seven_day_rate 
        from 
          (
            SELECT 
              id, 
              rk, 
              `date`, 
              date1, 
              rk2, 
              max_login, 
              add_money, 
              modify_money, 
              if(
                max_login - rk2 & gt;= 6, 
                modify_money, 
                null
              ) modify_sev_money 
            from 
              (
                SELECT 
                  id, 
                  rk, 
                  `date`, 
                  date1, 
                  rk2, 
                  max_login, 
                  add_money, 
                  if(rk2 = max_login, null, add_money) modify_money 
                from 
                  (
                    SELECT 
                      tt1.id, 
                      rk, 
                      tt1.`date`, 
                      date1, 
                      rk2, 
                      max_login, 
                      add_money 
                    from 
                      (
                        SELECT 
                          id, 
                          rk, 
                          `date`, 
                          date1, 
                          rk2, 
                          max_login 
                        from 
                          (
                            SELECT 
                              id, 
                              rk, 
                              `date`, 
                              date1, 
                              rk2, 
                              max(rk2) over(partition by id, date1) max_login 
                            from 
                              (
                                SELECT 
                                  id, 
                                  rk, 
                                  `date`, 
                                  date1, 
                                  ROW_NUMBER() over (partition by id, date1) rk2 
                                FROM 
                                  (
                                    SELECT 
                                      id, 
                                      rk, 
                                      `date`, 
                                      DATE_SUB(`date`, INTERVAL rk day) date1 
                                    from 
                                      (
                                        SELECT 
                                          id, 
                                          `date`, 
                                          ROW_NUMBER() over (
                                            PARTITION by id 
                                            ORDER BY 
                                              `date`
                                          ) rk 
                                        FROM 
                                          demo3
                                      ) t1
                                  ) t2
                              ) t3
                          ) t4
                      ) tt1 
                      LEFT JOIN demo3_red dr on tt1.id = dr.id 
                      and tt1.`date` = dr.`date`
                  ) tt2
              ) tt3
          ) tt4
      ) tt5
  ) ttt2

现在,你可以尝试用GPT来优化这段SQL

第一轮提示
提示

下面是一个HQL。能帮我说一下它干了什么事吗,还有能简化的地方吗?

回答

这个HQL查询包含了两个子查询。首先,我将逐步解释它们各自的作用,然后描述它们如何组合在一起。

第一个子查询计算了two_day_rate,即每个id在不同date1modify_money(条件:rk2不等于max_login)与add_money的非空条目之和的比率。

第二个子查询计算了seven_day_rate,即每个id在不同date1modify_sev_money(条件:max_login-rk2大于等于6)与add_money的非空条目之和的比率。

这两个子查询的结构非常相似。它们都包含了以下几个操作:

计算每个iddate的行号rk

使用DATE_SUB函数创建一个新列date1,该列是date减去行号rk的结果。

对每个iddate1,计算新的行号rk2

计算每个iddate1max_login,即rk2的最大值。

demo3表与demo3_red表之间执行左连接。

计算modify_money(第一个子查询)和modify_sev_money(第二个子查询)。

计算two_day_rateseven_day_rate

最后,这个HQL将这两个子查询的结果连接在一起,返回two_day_rateseven_day_rate

关于简化: 这个查询的主要问题是它的冗余性。许多操作在两个子查询中都执行了。你可以尝试将这些操作合并到一个子查询中,然后使用聚合函数SUMIF计算two_day_rateseven_day_rate。这将使查询更简洁、更易于阅读。

反馈

ChatGPT已经帮我们解释了这个HIVE SQL的分析逻辑,这是一个200行的SQL,人要读上半天,但是对于ChatGPT来说只需1分钟。这种情况下,我一般会要求GPT把SQL优化一版,然后拿去测一下执行结果和原先的是否一致。如果能保持一致,就用新的可读性更好的优GPT优化出来的SQL。

第二轮提示
提示

 请帮我写一个更简单的,谢谢

回答

SELECT 
    id, date1,
    SUM(IF(modify_money IS NOT NULL, 1, 0)) / SUM(IF(add_money IS NOT NULL, 1, 0)) AS two_day_rate,
    SUM(IF(modify_sev_money IS NOT NULL, 1, 0)) / SUM(IF(add_money IS NOT NULL, 1, 0)) AS seven_day_rate
FROM (
    SELECT 
        tt1.id, tt1.`date`, date1, rk2, max_login, add_money,
        IF(rk2 = max_login, NULL, add_money) AS modify_money,
        IF(max_login - rk2 >= 6, add_money, NULL) AS modify_sev_money
    FROM (
        SELECT
            id, `date`, date1, rk2,
            MAX(rk2) OVER (PARTITION BY id, date1) AS max_login
        FROM (
            SELECT
                id, `date`, date1,
                ROW_NUMBER() OVER (PARTITION BY id, date1) AS rk2
            FROM (
                SELECT
                    id, `date`, DATE_SUB(`date`, INTERVAL rk DAY) AS date1
                FROM (
                    SELECT
                        id, `date`,
                        ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date`) AS rk
                    FROM demo3
                ) t1
            ) t2
        ) t3
    ) tt1
    LEFT JOIN demo3_red dr
    ON tt1.id = dr.id AND tt1.`date` = dr.`date`
) t4
GROUP BY id, date1;

这个简化的查询首先计算了每个id和date的行号rk,接着创建了一个新列date1,该列是date减去行号rk的结果。然后,对每个id和date1,计算了新的行号rk2,以及每个id和date1的max_login,即rk2的最大值。在demo3表与demo3_red表之间执行左连接后,根据条件计算modify_money和modify_sev_money。最后,使用聚合函数SUM和IF,对每个id和date1计算two_day_rate和seven_day_rate。  

反馈

原先的SQL没有使用IF函数,从而导致每次需要条件分支的时候,直接重写where逻辑并增加一个子查询。现在优化后的SQL只有33行,比原版200+行的SQL可读性不要好太多哈哈哈!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值