AI驱动大数据开发（1）_mysql ddl转hive-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45011176/article/details/133043111

本文借助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;

第一轮提示

提示

将上面的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设置：

现在，这个表的数据将存储在指定的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表有如下字段，

回答

以下是一个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个临时表（monthly、quarterly和yearly），分别计算每月、每季度和每年的营业收入和计划收入。然后，通过将这些临时表连接在一起，并使用窗口函数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在不同date1的modify_money（条件：rk2不等于max_login）与add_money的非空条目之和的比率。

第二个子查询计算了seven_day_rate，即每个id在不同date1的modify_sev_money（条件：max_login-rk2大于等于6）与add_money的非空条目之和的比率。

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

计算每个id和date的行号rk。

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

对每个id和date1，计算新的行号rk2。

计算每个id和date1的max_login，即rk2的最大值。

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

计算modify_money（第一个子查询）和modify_sev_money（第二个子查询）。

计算two_day_rate和seven_day_rate。

最后，这个HQL将这两个子查询的结果连接在一起，返回two_day_rate和seven_day_rate。

关于简化：这个查询的主要问题是它的冗余性。许多操作在两个子查询中都执行了。你可以尝试将这些操作合并到一个子查询中，然后使用聚合函数SUM和IF计算two_day_rate和seven_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。