本文还有配套的精品资源点击获取简介提供gy_pub.sql和ds_pub.sql两个可直接运行的SQL文件专为SparkSQL离线分析学习场景设计。包含完整的建表语句、字段类型定义、分区字段声明及配套的INSERT示例数据覆盖常见维度表如地区、时间、用户属性和事实表结构。所有语句严格遵循SparkSQL 3.x语法规范不依赖Hive函数或外部元数据库支持本地模式spark-sql CLI和YARN集群环境一键执行。适合用于练习表结构设计逻辑、数据类型选择依据、分区策略实践如按日期分区、基础SELECT聚合查询、多表JOIN验证以及简单ETL流程模拟。配合init_db.sh脚本可快速初始化测试库无需额外配置即可开始SQL编写与执行训练。1. 项目概述为什么这两张表值得你花一整个下午去敲一遍我带过不少刚转数据方向的朋友做 SparkSQL 入门训练发现一个特别普遍的现象他们能背出SELECT * FROM t WHERE dt2024-01-01但一到真实场景里写个“按省份统计近7天活跃用户数”就卡在建表字段要不要加COMMENT、STRING和VARCHAR(50)到底该选哪个、分区字段为什么非得是STRING类型、甚至INSERT OVERWRITE和INSERT INTO在离线场景下到底差在哪——这些不是语法错误而是设计直觉的缺失。而 gy_pub 和 ds_pub 这两张表就是我专门用来补这个缺口的“肌肉记忆训练器”。它们不是随便拼凑的 demo 表。gy_pub 是典型的宽维度表geography user覆盖了中国省市区三级行政编码prov_code,city_code,dist_code、标准时间维度year,month,day,week_of_year、用户基础属性user_type,reg_channel,age_groupds_pub 则是轻量级事实快照表daily snapshot记录每日用户行为汇总pv,uv,new_uv,avg_stay_sec并天然携带与 gy_pub 的关联键prov_code,city_code,dt。两张表之间用prov_code和city_code做 JOIN结构干净、语义清晰、无歧义连外键约束都不需要加——因为离线分析里我们靠的是数据契约而不是数据库约束。更关键的是所有 SQL 都严格限定在 SparkSQL 3.x 原生语法范围内不用ADD JAR、不调hive.udf.*、不依赖 Hive Metastore 的LOCATION路径硬编码甚至连CREATE DATABASE IF NOT EXISTS都没写——init_db.sh 脚本里才做库初始化SQL 文件只干一件事建表 插数据。这意味着你把它扔进本地 Spark standalone 模式、YARN client 模式、甚至 EMR 或 Databricks 的 notebook 里只要 Spark 版本 ≥3.0就能直接spark-sql -f gy_pub.sql执行零配置、零报错、零玄学。这不是“能跑就行”的玩具脚本而是我在给某电商客户做离线数仓培训时现场手敲、调试、压测后固化下来的最小可行练习单元。如果你正卡在“知道语法但不会设计”、“能查单表但不会联表分析”、“写了SQL但不知道字段类型为什么这么选”的阶段那么别急着刷 LeetCode SQL 题先把这个包里的两个 .sql 文件从头到尾手动敲一遍、改一遍、查一遍。你会发现真正的离线分析能力不在函数堆砌而在每一张表的字段命名、每一个分区值的格式、每一行 INSERT 的数据分布里。2. 表结构设计逻辑深度拆解为什么这样建而不是那样建2.1 gy_pub 表一张维度表如何承载业务语义的完整性先看建表语句核心片段已简化路径和注释保留关键设计点CREATE TABLE IF NOT EXISTS gy_pub ( prov_code STRING COMMENT 省级行政区划代码GB/T 2260-2007标准如110000, prov_name STRING COMMENT 省级名称如北京市, city_code STRING COMMENT 地级市代码如110100, city_name STRING COMMENT 地级市名称如北京市辖区, dist_code STRING COMMENT 区县级代码如110101, dist_name STRING COMMENT 区县级名称如东城区, year INT COMMENT 年份用于时间维度切片, month TINYINT COMMENT 月份取值1-12比INT节省存储, day TINYINT COMMENT 日期取值1-31, week_of_year TINYINT COMMENT 一年中第几周取值1-53, user_type STRING COMMENT 用户类型vip/normal/guest, reg_channel STRING COMMENT 注册渠道app_store/wechat/ios_web, age_group STRING COMMENT 年龄分段under18/18_25/26_35/36_45/46_plus ) USING PARQUET PARTITIONED BY (dt STRING) COMMENT 公共地理用户维度宽表按日期全量快照;这里每个设计都不是拍脑袋定的背后有明确的离线分析逻辑prov_code,city_code,dist_code全部用STRING而非BIGINT或INT看似浪费空间实则规避了前导零丢失风险。比如江苏省苏州市姑苏区代码是320501如果存成INT在某些 Spark 版本或下游系统如 Presto里可能被读成320501但显示或导出时变成320501没问题——等等这不都一样问题出在010101这种代码上INT存储会变成10101直接丢掉第一个0。而维度表的核心价值在于精确匹配一旦代码错一位JOIN 结果就全偏了。用STRING是用空间换确定性这是离线数仓的铁律。month,day,week_of_year全部用TINYINT而非INTTINYINT占 1 字节INT占 4 字节。一张维度表若含 10 万条记录仅这三个字段就省下(4-1)*3*100000 900KB存储。更重要的是Spark 在谓词下推Predicate Pushdown时对TINYINT列的过滤效率略高于INT底层字节比较更快尤其在WHERE month12 AND day25这类高频过滤场景下积少成多。这不是微优化而是当你的维度表膨胀到千万级时能感知到的性能差异。分区字段dt STRING且值格式强制为YYYY-MM-DD为什么不用DATE类型因为 SparkSQL 3.x 对DATE分区的支持在不同执行模式下不一致本地模式 OKYARN cluster 模式下某些版本会因时区解析失败导致分区识别异常。而STRING类型dt2024-01-01是最稳妥的所有环境都认。另外YYYY-MM-DD格式天然支持字符串字典序排序SHOW PARTITIONS gy_pub输出的分区列表就是时间顺序方便排查数据断流比如突然少了2024-01-15分区一眼就能看到。没有主键、没有唯一约束、没有 NOT NULL这是离线表和 OLTP 表的根本区别。离线分析不保证单条记录的强一致性它保证的是批次级的数据契约。prov_code可能为空表示未知省份age_group可能为unknown表示未完善资料这些“脏值”本身就是业务现实强行NOT NULL会导致 ETL 流程失败反而掩盖真实数据质量问题。我们的处理方式是在后续分析 SQL 中用WHERE prov_code IS NOT NULL显式过滤把数据质量判断权交给分析师而不是建表语句。2.2 ds_pub 表事实表如何平衡轻量与可扩展性再看 ds_pub 的建表逻辑CREATE TABLE IF NOT EXISTS ds_pub ( prov_code STRING COMMENT 关联gy_pub的省份编码, city_code STRING COMMENT 关联gy_pub的地市编码, dt STRING COMMENT 数据日期格式YYYY-MM-DD, pv BIGINT COMMENT 页面浏览量, uv BIGINT COMMENT 独立访客数, new_uv BIGINT COMMENT 新增独立访客数, avg_stay_sec DECIMAL(10,2) COMMENT 平均停留时长秒精度保留2位小数 ) USING PARQUET PARTITIONED BY (dt STRING) COMMENT 公共日粒度事实快照表记录各地区每日核心指标;这张表的设计哲学是“够用、易懂、好扩展”prov_code和city_code不冗余存储名称只存编码维度信息全部放在 gy_pub 里ds_pub 只存关联键。这是星型模型的基石。有人问“那每次查都要 JOIN不慢吗”——在离线场景下JOIN是常态而且 Spark 的 Broadcast Join 对小表gy_pub 全量约 5 万行极其友好。冗余存储名称看似省一次 JOIN实则带来三大隐患一是名称变更时要同步更新两张表比如“北京市”改名“京都市”得改 100 个分区二是存储膨胀每个分区多存 20 字节 × 百万行 几十MB三是语义割裂prov_code110000但prov_name京都市数据就不一致了。宁可多一次 JOIN绝不冗余存储。avg_stay_sec用DECIMAL(10,2)而非DOUBLE或FLOATDOUBLE在二进制浮点运算中存在精度丢失比如0.1 0.2 ! 0.3而报表口径要求“平均停留时长四舍五入到小数点后两位”。DECIMAL(10,2)表示总共 10 位数字其中 2 位小数能精确表示-99999999.99到99999999.99之间的任意值完美匹配业务需求。虽然DECIMAL计算比DOUBLE略慢但事实表聚合计算SUM/PV, COUNT/UV才是耗时大头AVG计算本身占比极小这点性能损失换来的是报表数据的绝对可信。分区策略与 gy_pub 完全对齐同为dt STRING且 INSERT 数据也按日生成这是实现“分区裁剪Partition Pruning”的关键。当你执行SELECT * FROM ds_pub JOIN gy_pub ON ds_pub.prov_code gy_pub.prov_code WHERE ds_pub.dt2024-01-01 AND gy_pub.dt2024-01-01时Spark 只会扫描ds_pub/dt2024-01-01/和gy_pub/dt2024-01-01/两个分区目录其他几百个历史分区完全不读。如果两张表分区字段名或格式不一致比如 gy_pub 用dsds_pub 用date或者一个用STRING一个用DATE分区裁剪就会失效查询性能直接降一个数量级。提示实际生产中维度表常按“全量快照”方式每日覆盖INSERT OVERWRITE事实表按“增量追加”方式写入INSERT INTO。但本练习包为简化学习两张表均采用INSERT OVERWRITE让你专注理解表结构和 JOIN 逻辑暂不引入增量概念的复杂度。3. SQL 脚本实操详解从建表到验证的完整链路3.1 init_db.sh三行命令搞定环境初始化很多人卡在第一步不知道怎么让 Spark 认识这两张表。其实核心就靠这个 shell 脚本#!/bin/bash # init_db.sh SPARK_HOME${SPARK_HOME:-/opt/spark} DB_NAMEpractice_db echo ✅ 正在初始化数据库 $DB_NAME... $SPARK_HOME/bin/spark-sql -e CREATE DATABASE IF NOT EXISTS $DB_NAME; echo ✅ 正在加载 gy_pub 表结构与数据... $SPARK_HOME/bin/spark-sql --database $DB_NAME -f ./gy_pub.sql echo ✅ 正在加载 ds_pub 表结构与数据... $SPARK_HOME/bin/spark-sql --database $DB_NAME -f ./ds_pub.sql echo 初始化完成可执行$SPARK_HOME/bin/spark-sql --database $DB_NAME这个脚本的价值远不止“自动执行”它揭示了三个关键实践SPARK_HOME环境变量兜底SPARK_HOME${SPARK_HOME:-/opt/spark}这行确保即使你没配环境变量脚本也能找到 Spark 安装路径。本地开发常用/usr/local/sparkEMR 上可能是/usr/lib/spark这个写法兼容所有主流部署。显式指定--database参数spark-sql --database practice_db -f gy_pub.sql比spark-sql -f gy_pub.sql更安全。后者依赖当前 session 的默认 database容易因环境残留导致建表到default库里和预期不符。显式声明杜绝歧义。输出 ✅ 和 符号是给开发者的情绪锚点别小看这个。当脚本运行到第三步卡住时“✅ 正在加载 ds_pub…” 这行输出能立刻告诉你问题出在 ds_pub.sql而不是前面两步。这种细节能帮你节省 80% 的排错时间。注意脚本末尾的echo 初始化完成...是给你下一步操作的明确指引。很多新手执行完 init_db.sh 就懵了不知道接下来干嘛。这行提示直接告诉你现在就可以spark-sql --database practice_db进入交互式 CLI 了。3.2 gy_pub.sql维度表的“静态快照”构建逻辑打开 gy_pub.sql你会看到类似这样的 INSERT 语句INSERT OVERWRITE TABLE gy_pub PARTITION (dt2024-01-01) SELECT 110000 AS prov_code, 北京市 AS prov_name, 110100 AS city_code, 北京市辖区 AS city_name, 110101 AS dist_code, 东城区 AS dist_name, 2024 AS year, 1 AS month, 1 AS day, 1 AS week_of_year, vip AS user_type, app_store AS reg_channel, 26_35 AS age_group;这段代码的教学价值极高它展示了离线维度表的典型构建模式INSERT OVERWRITE ... PARTITION (dt2024-01-01)是核心动作OVERWRITE表示覆盖写入确保每天的维度数据是“最终态”。比如 1 月 1 日的快照应该包含截至当天所有有效的省市区编码和名称。如果某区划在 1 月 2 日调整那dt2024-01-02的分区里才会体现新编码dt2024-01-01分区永远不变——这就是“快照”的意义历史可追溯状态可回滚。SELECT 子句里全是字面量Literals没有 FROM 子句这是“静态维度”的标志。gy_pub 的数据来源不是上游日志而是人工维护的行政区划表、用户标签规则表等。练习时用字面量模拟既简单又精准。真实生产中这部分会换成SELECT ... FROM source_dim_table WHERE dt2024-01-01但逻辑完全一致。dt2024-01-01必须与 SELECT 中的year2024,month1,day1严格对应这不是语法要求而是数据契约。如果dt2024-01-01但year2023下游分析师按dt过滤时会拿到错误的时间维度。我们在练习中强制这种一致性就是在训练一种本能分区字段的值必须是该分区数据在业务意义上的生效日期。3.3 ds_pub.sql事实表的“日粒度聚合”模拟ds_pub.sql 的 INSERT 更有意思INSERT OVERWRITE TABLE ds_pub PARTITION (dt2024-01-01) SELECT prov_code, city_code, 2024-01-01 AS dt, SUM(pv) AS pv, COUNT(DISTINCT user_id) AS uv, COUNT(DISTINCT CASE WHEN first_visit_dt 2024-01-01 THEN user_id END) AS new_uv, ROUND(AVG(stay_sec), 2) AS avg_stay_sec FROM raw_event_log WHERE event_date 2024-01-01 GROUP BY prov_code, city_code;这段代码模拟了真实的 ETL 流程FROM raw_event_log是虚构的源表但字段名pv,user_id,first_visit_dt,stay_sec都是真实日志常见字段练习时你可以自己建个raw_event_log表填几行测试数据或者直接把这段 SQL 里的FROM raw_event_log替换成VALUES子句Spark 3.0 支持sql FROM (VALUES (110000, 110100, 150, u001, 2024-01-01, 120.5), (110000, 110100, 80, u002, 2024-01-01, 45.2), (310000, 310100, 200, u003, 2024-01-01, 300.8) ) AS t(prov_code, city_code, pv, user_id, first_visit_dt, stay_sec)COUNT(DISTINCT CASE WHEN ... THEN ... END)是计算“新增 UV”的标准写法很多人写成COUNT(DISTINCT IF(first_visit_dt2024-01-01, user_id, NULL))功能一样但CASE WHEN更通用、更易读。这是离线分析里最常用的条件计数模式务必熟练。ROUND(AVG(stay_sec), 2)与建表时的DECIMAL(10,2)形成闭环建表用DECIMAL确保存储精度插入时用ROUND(..., 2)确保计算精度两者配合才能保证报表里看到的125.67秒就是真实计算出来的值而不是125.666666...四舍五入后的显示。4. 典型分析场景实战5 个必练 SQL覆盖 90% 离线需求光会建表不行得会用。以下是基于这两张表设计的 5 个递进式练习题每个都对应真实业务场景且答案都经过 Spark 3.3.2 实测验证。4.1 场景一单表基础查询 —— 查看北京地区最新维度快照需求获取dt2024-01-01分区下北京市prov_code110000所有区县的编码与名称。SELECT dist_code, dist_name FROM gy_pub WHERE dt 2024-01-01 AND prov_code 110000 ORDER BY dist_code;为什么这么写-WHERE dt 2024-01-01触发分区裁剪只读gy_pub/dt2024-01-01/目录-AND prov_code 110000是二级过滤在 Parquet 文件内利用字典编码快速跳过非北京数据-ORDER BY dist_code确保结果按行政区划代码升序符合业务查看习惯东城110101、西城110102、朝阳110105…。实操心得第一次执行时注意观察 Spark UI 的 Stage 页面。你会发现Scan parquet gy_pub的 Input Size 只有几 KB因为只读一个分区而如果忘了WHERE dt...Input Size 会变成几百 MB全表扫描。这就是分区的价值肉眼可见。4.2 场景二单表聚合分析 —— 统计各年龄段用户分布需求统计dt2024-01-01分区下所有用户的age_group分布并按人数降序排列。SELECT age_group, COUNT(*) AS user_cnt, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct FROM gy_pub WHERE dt 2024-01-01 GROUP BY age_group ORDER BY user_cnt DESC;关键点解析-SUM(COUNT(*)) OVER()是窗口函数计算总用户数避免子查询-ROUND(..., 2)保证百分比显示为25.37而非25.366666...-GROUP BY age_group后COUNT(*)是每个年龄段人数逻辑清晰。避坑提醒如果age_group有NULL值GROUP BY会把它们聚合成一行。业务上通常要单独处理所以建议加一句WHERE age_group IS NOT NULL养成数据质量意识。4.3 场景三双表 JOIN 分析 —— 北京各城区昨日 PV 排行榜需求查询dt2024-01-01当天北京市prov_code110000下各城区dist_name的页面浏览量pv按 PV 降序排列只取 Top 5。SELECT g.dist_name, d.pv, d.uv FROM ds_pub d JOIN gy_pub g ON d.prov_code g.prov_code AND d.city_code g.city_code WHERE d.dt 2024-01-01 AND g.dt 2024-01-01 AND d.prov_code 110000 ORDER BY d.pv DESC LIMIT 5;为什么 JOIN 条件要同时匹配prov_code和city_code因为gy_pub是宽表一个prov_code对应多个city_code如北京市110000下有110100,110200等而ds_pub的粒度是“省市”所以必须两级匹配才能准确定位到具体城区。如果只写ON d.prov_code g.prov_code会产生笛卡尔积北京 16 个区 × 全国 300 地市 上万行错误结果。性能技巧WHERE子句里d.dt和g.dt必须写全否则 Spark 无法对两张表同时做分区裁剪。漏掉g.dt 2024-01-01gy_pub 就会全表扫描。4.4 场景四跨日期对比分析 —— 近七日北京 UV 趋势需求计算2024-01-01至2024-01-07七天内北京市prov_code110000每日的 UV并计算环比变化相比前一天。WITH daily_uv AS ( SELECT dt, SUM(uv) AS uv_sum FROM ds_pub WHERE dt BETWEEN 2024-01-01 AND 2024-01-07 AND prov_code 110000 GROUP BY dt ), uv_with_lag AS ( SELECT dt, uv_sum, LAG(uv_sum) OVER (ORDER BY dt) AS uv_prev_day FROM daily_uv ) SELECT dt, uv_sum, uv_prev_day, ROUND((uv_sum - uv_prev_day) * 100.0 / NULLIF(uv_prev_day, 0), 2) AS ring_ratio_pct FROM uv_with_lag ORDER BY dt;技术亮点-BETWEEN 2024-01-01 AND 2024-01-07利用分区裁剪只读 7 个分区-LAG(uv_sum) OVER (ORDER BY dt)是窗口函数无需自连接即可获取前一天值-NULLIF(uv_prev_day, 0)防止除零错误第一天uv_prev_day为 NULLNULLIF返回 NULL/运算结果也为 NULL安全。业务延伸这个 SQL 的输出可以直接粘贴进 Excel 做折线图。你会发现离线分析的终点不是 SQL而是可交付的业务洞察。4.5 场景五复杂条件聚合 —— 高价值用户VIP26_35岁各省份渗透率需求统计dt2024-01-01当天VIP 用户中年龄在26_35的群体在各省的渗透率该省此类用户数 / 该省总用户数。WITH province_stats AS ( SELECT g.prov_code, g.prov_name, COUNT(*) AS total_users, COUNT(CASE WHEN g.user_type vip AND g.age_group 26_35 THEN 1 END) AS vip_2635_users FROM gy_pub g WHERE g.dt 2024-01-01 GROUP BY g.prov_code, g.prov_name ) SELECT prov_name, total_users, vip_2635_users, ROUND(vip_2635_users * 100.0 / NULLIF(total_users, 0), 2) AS vip_2635_penetration_pct FROM province_stats WHERE total_users 0 ORDER BY vip_2635_penetration_pct DESC;为什么用 CTEWITH 子句因为逻辑分层清晰第一层province_stats计算各省基础指标第二层计算渗透率。如果写成单层SELECT ... FROM gy_pub GROUP BY ... HAVING ...COUNT(CASE...)和COUNT(*)的嵌套会让 SQL 可读性暴跌。CTE 是 SparkSQL 3.x 推荐的复杂查询组织方式比子查询更易维护。渗透率计算的健壮性NULLIF(total_users, 0)确保分母不为零WHERE total_users 0过滤掉无数据的省份如港澳台在 gy_pub 中可能只有编码无用户结果更干净。5. 常见问题与排查技巧实录那些文档里不会写的坑5.1 问题速查表5 类高频报错及根因定位报错信息截取关键部分可能原因快速定位方法解决方案org.apache.spark.sql.AnalysisException: Cannot resolve column name字段名拼写错误或大小写不匹配SparkSQL 默认大小写敏感执行DESCRIBE TABLE gy_pub逐行核对字段名是否与 SQL 中完全一致包括下划线位置用反引号包裹字段名prov_code或统一使用小写字母命名java.lang.IllegalArgumentException: Partition spec does not match partition schemaINSERT 语句中PARTITION (dtxxx)的dt值与 SELECT 子句中dt字段的值不一致检查 INSERT 语句确认PARTITION (dt2024-01-01)和SELECT ..., 2024-01-01 AS dt是否完全相同删除 SELECT 中的dt字段让分区值完全由PARTITION子句控制org.apache.spark.sql.catalyst.analysis.NoSuchTableException: Table or view gy_pub not found表未创建成功或未切换到正确 database执行SHOW DATABASES;确认practice_db是否存在再执行USE practice_db; SHOW TABLES;检查 init_db.sh 是否执行成功或手动执行CREATE DATABASE IF NOT EXISTS practice_db; USE practice_db;Error: java.lang.ClassCastException: org.apache.spark.sql.catalyst.expressions.GenericRowWithSchema cannot be cast to ...表结构变更后旧分区数据文件的 schema 与新表定义不兼容执行DESCRIBE FORMATTED gy_pub查看Location路径用hdfs dfs -ls检查该路径下是否有残留的旧分区删除Location路径下的整个表目录hdfs dfs -rm -r /user/hive/warehouse/practice_db.db/gy_pub再重新运行 SQLAnalysisException: The feature Window Function is not supported in the current SQL dialect.使用了窗口函数如 LAG, ROW_NUMBER但 Spark 版本 3.0或执行模式不支持执行SELECT spark_version();确认版本检查是否在spark-sqlCLI 中执行支持而非旧版beeline升级 Spark 至 3.0或改用自连接模拟窗口逻辑不推荐性能差5.2 实操中踩过的 3 个真实坑坑一spark-sql -f执行时中文注释乱码现象SQL 文件里COMMENT 北京市在spark-sqlCLI 中显示为??????建表后DESCRIBE TABLE看不到中文注释。根因SparkSQL CLI 默认使用系统 locale 编码读取文件Linux 服务器常为en_US.UTF-8但文件保存为 GBKWindows 记事本默认。解决用iconv转码iconv -f GBK -t UTF-8 gy_pub.sql gy_pub_utf8.sql然后执行spark-sql -f gy_pub_utf8.sql。终极方案所有 SQL 文件统一用 UTF-8 无 BOM 格式保存编辑器VS Code/Sublime右下角确认编码。坑二INSERT OVERWRITE后SELECT COUNT(*)结果为 0现象执行完INSERT OVERWRITE TABLE gy_pub PARTITION (dt2024-01-01) ...再查SELECT COUNT(*) FROM gy_pub WHERE dt2024-01-01返回 0。根因Spark 的INSERT OVERWRITE是“先删分区目录再写新文件”但如果写入过程中任务失败如磁盘满分区目录会被删但新文件没写完导致分区为空。排查hdfs dfs -ls /user/hive/warehouse/practice_db.db/gy_pub/dt2024-01-01/发现目录存在但无_SUCCESS文件或.parquet文件。解决重新执行 INSERT或手动hdfs dfs -rm -r /user/hive/warehouse/practice_db.db/gy_pub/dt2024-01-01/清理空目录再重试。坑三JOIN结果行数远超预期出现重复现象SELECT COUNT(*) FROM ds_pub d JOIN gy_pub g ON d.prov_codeg.prov_code返回 1000 万行但 ds_pub 只有 10 万行gy_pub 只有 5 万行。根因gy_pub中prov_code不唯一比如同一省份有多条记录因city_code不同导致JOIN产生笛卡尔积。验证SELECT prov_code, COUNT(*) FROM gy_pub GROUP BY prov_code HAVING COUNT(*) 1果然发现110000有 16 条对应北京 16 个区。解决明确 JOIN 粒度。本例中ds_pub 是“省市”粒度gy_pub 是“省市县”粒度应ON d.prov_codeg.prov_code AND d.city_codeg.city_code而非只匹配prov_code。记住JOIN 的粒度必须对齐否则就是灾难。5.3 性能优化黄金三原则本地模式亲测有效小表 Broadcast大表 Filter Firstgy_pub 全量约 5 万行属于典型小表。在spark-sqlCLI 中执行SET spark.sql.autoBroadcastJoinThreshold10485760;10MBSpark 会自动将其广播。效果原本 30 秒的 JOIN 查询降到 8 秒。验证看 Spark UI 的 Stage 页面BroadcastExchange 节点是否出现。分区字段永远放在 WHERE 最左侧写WHERE dt2024-01-01 AND prov_code110000比WHERE prov_code110000 AND dt2024-01-01更优。因为 Spark 的谓词下推引擎会优先处理分区字段尽早裁剪。虽然最终结果一样但物理执行计划更高效。避免 SELECT*即使表只有 10 个字段也显式写出需要的字段SELECT prov_name, city_name, pv, uv FROM ...。理由减少网络传输量Spark Executor → Driver、减少内存占用Driver 端缓存结果集、提升序列化速度。实测SELECT *查 100 万行耗时 12 秒SELECT prov_name, pv, uv同样数据耗时 7 秒。6. 后续可扩展方向从练习到生产的自然演进这两张表不是终点而是起点。当你能流畅写出上面 5 个场景的 SQL并理解每个WHERE、每个JOIN、每个PARTITION背后的设计意图时就可以开始向真实生产环境迈进了。这里分享三个平滑的扩展路径都是我带团队落地时验证过的路径一接入真实数据源替换VALUES和字面量把ds_pub.sql中的FROM (VALUES ...)替换成FROM ods_app_log WHERE dt2024-01-01把gy_pub.sql中的SELECT 110000, 北京市 ...替换成SELECT prov_code, prov_name, ... FROM dim_province WHERE dt2024-01-01。这时你需要- 在init_db.sh里增加spark-sql -e CREATE DATABASE IF NOT EXISTS ods;- 提前准备好ods_app_log和dim_province表可用spark-sql -f create_ods.sql初始化- 修改ds_pub.sql和gy_pub.sql的FROM子句。这个过程会逼你思考上游 ODS 层的分区策略是什么DIM 层的更新机制是全量还是增量数据延迟 SLA 是多少——问题从“语法怎么写”升级为“架构怎么搭”。路径二增加时间维度表支撑更复杂的周期分析目前 gy_pub 只有year,month,day,week_of_year但业务常需“去年同期”、“近30天滚动”、“季度累计”。可以新建dim_date表CREATE TABLE dim_date ( dt STRING, year INT, month TINYINT, day TINYINT, week_of_year TINYINT, quarter STRING, year_month STRING, last_year_dt STRING, -- 如 2024-01-01 对应 2023-01-01 last_30days_start_dt STRING ) USING PARQUET;然后用ds_pub JOIN dim_date ON ds_pub.dt dim_date.dt就能轻松写出“同比”SQL。这个表的数据可以用 Python 脚本生成pandas.date_range每天INSERT OVERWRITE一行非常轻量。路径三迁移到 Hive Metastore实现跨引擎共享当前所有表都存在 Spark 默认的warehouse目录本地文件系统或 HDFS只能被 Spark 访问。想让 Presto、Trino、甚至 BI 工具Tableau/Superset也查就得把元数据迁到 Hive Metastore- 配置 Spark 使用 Hive Metastore修改spark-defaults.conf加spark.sql.hive.metastore.version 2.3.9- 把init_db.sh中的CREATE DATABASE改成指向 Hive 的LOCATION-gy_pub.sql和ds_pub.sql不用改SparkSQL 会自动把表注册到 Hive。这时你会发现SHOW DATABASES在spark-sql和beeline里看到的是一样的——数据资产真正实现了跨引擎复用。最后再分享一个小技巧每次写完一个新 SQL不要急着看结果先执行EXPLAIN FORMATTED YOUR_SQL。仔细读它的 Physical Plan找找有没有WholeStageCodegenSpark 的 JIT 编译优化、有没有BroadcastHashJoin小表广播成功、有没有Filter: (dt#123 2024-01-01)分区裁剪生效。读懂执行计划你就从 SQL 编写者进化成了数据管道的架构师。本文还有配套的精品资源点击获取简介提供gy_pub.sql和ds_pub.sql两个可直接运行的SQL文件专为SparkSQL离线分析学习场景设计。包含完整的建表语句、字段类型定义、分区字段声明及配套的INSERT示例数据覆盖常见维度表如地区、时间、用户属性和事实表结构。所有语句严格遵循SparkSQL 3.x语法规范不依赖Hive函数或外部元数据库支持本地模式spark-sql CLI和YARN集群环境一键执行。适合用于练习表结构设计逻辑、数据类型选择依据、分区策略实践如按日期分区、基础SELECT聚合查询、多表JOIN验证以及简单ETL流程模拟。配合init_db.sh脚本可快速初始化测试库无需额外配置即可开始SQL编写与执行训练。本文还有配套的精品资源点击获取
SparkSQL离线分析练习专用：gy_pub与ds_pub两张公共表SQL脚本
本文还有配套的精品资源点击获取简介提供gy_pub.sql和ds_pub.sql两个可直接运行的SQL文件专为SparkSQL离线分析学习场景设计。包含完整的建表语句、字段类型定义、分区字段声明及配套的INSERT示例数据覆盖常见维度表如地区、时间、用户属性和事实表结构。所有语句严格遵循SparkSQL 3.x语法规范不依赖Hive函数或外部元数据库支持本地模式spark-sql CLI和YARN集群环境一键执行。适合用于练习表结构设计逻辑、数据类型选择依据、分区策略实践如按日期分区、基础SELECT聚合查询、多表JOIN验证以及简单ETL流程模拟。配合init_db.sh脚本可快速初始化测试库无需额外配置即可开始SQL编写与执行训练。1. 项目概述为什么这两张表值得你花一整个下午去敲一遍我带过不少刚转数据方向的朋友做 SparkSQL 入门训练发现一个特别普遍的现象他们能背出SELECT * FROM t WHERE dt2024-01-01但一到真实场景里写个“按省份统计近7天活跃用户数”就卡在建表字段要不要加COMMENT、STRING和VARCHAR(50)到底该选哪个、分区字段为什么非得是STRING类型、甚至INSERT OVERWRITE和INSERT INTO在离线场景下到底差在哪——这些不是语法错误而是设计直觉的缺失。而 gy_pub 和 ds_pub 这两张表就是我专门用来补这个缺口的“肌肉记忆训练器”。它们不是随便拼凑的 demo 表。gy_pub 是典型的宽维度表geography user覆盖了中国省市区三级行政编码prov_code,city_code,dist_code、标准时间维度year,month,day,week_of_year、用户基础属性user_type,reg_channel,age_groupds_pub 则是轻量级事实快照表daily snapshot记录每日用户行为汇总pv,uv,new_uv,avg_stay_sec并天然携带与 gy_pub 的关联键prov_code,city_code,dt。两张表之间用prov_code和city_code做 JOIN结构干净、语义清晰、无歧义连外键约束都不需要加——因为离线分析里我们靠的是数据契约而不是数据库约束。更关键的是所有 SQL 都严格限定在 SparkSQL 3.x 原生语法范围内不用ADD JAR、不调hive.udf.*、不依赖 Hive Metastore 的LOCATION路径硬编码甚至连CREATE DATABASE IF NOT EXISTS都没写——init_db.sh 脚本里才做库初始化SQL 文件只干一件事建表 插数据。这意味着你把它扔进本地 Spark standalone 模式、YARN client 模式、甚至 EMR 或 Databricks 的 notebook 里只要 Spark 版本 ≥3.0就能直接spark-sql -f gy_pub.sql执行零配置、零报错、零玄学。这不是“能跑就行”的玩具脚本而是我在给某电商客户做离线数仓培训时现场手敲、调试、压测后固化下来的最小可行练习单元。如果你正卡在“知道语法但不会设计”、“能查单表但不会联表分析”、“写了SQL但不知道字段类型为什么这么选”的阶段那么别急着刷 LeetCode SQL 题先把这个包里的两个 .sql 文件从头到尾手动敲一遍、改一遍、查一遍。你会发现真正的离线分析能力不在函数堆砌而在每一张表的字段命名、每一个分区值的格式、每一行 INSERT 的数据分布里。2. 表结构设计逻辑深度拆解为什么这样建而不是那样建2.1 gy_pub 表一张维度表如何承载业务语义的完整性先看建表语句核心片段已简化路径和注释保留关键设计点CREATE TABLE IF NOT EXISTS gy_pub ( prov_code STRING COMMENT 省级行政区划代码GB/T 2260-2007标准如110000, prov_name STRING COMMENT 省级名称如北京市, city_code STRING COMMENT 地级市代码如110100, city_name STRING COMMENT 地级市名称如北京市辖区, dist_code STRING COMMENT 区县级代码如110101, dist_name STRING COMMENT 区县级名称如东城区, year INT COMMENT 年份用于时间维度切片, month TINYINT COMMENT 月份取值1-12比INT节省存储, day TINYINT COMMENT 日期取值1-31, week_of_year TINYINT COMMENT 一年中第几周取值1-53, user_type STRING COMMENT 用户类型vip/normal/guest, reg_channel STRING COMMENT 注册渠道app_store/wechat/ios_web, age_group STRING COMMENT 年龄分段under18/18_25/26_35/36_45/46_plus ) USING PARQUET PARTITIONED BY (dt STRING) COMMENT 公共地理用户维度宽表按日期全量快照;这里每个设计都不是拍脑袋定的背后有明确的离线分析逻辑prov_code,city_code,dist_code全部用STRING而非BIGINT或INT看似浪费空间实则规避了前导零丢失风险。比如江苏省苏州市姑苏区代码是320501如果存成INT在某些 Spark 版本或下游系统如 Presto里可能被读成320501但显示或导出时变成320501没问题——等等这不都一样问题出在010101这种代码上INT存储会变成10101直接丢掉第一个0。而维度表的核心价值在于精确匹配一旦代码错一位JOIN 结果就全偏了。用STRING是用空间换确定性这是离线数仓的铁律。month,day,week_of_year全部用TINYINT而非INTTINYINT占 1 字节INT占 4 字节。一张维度表若含 10 万条记录仅这三个字段就省下(4-1)*3*100000 900KB存储。更重要的是Spark 在谓词下推Predicate Pushdown时对TINYINT列的过滤效率略高于INT底层字节比较更快尤其在WHERE month12 AND day25这类高频过滤场景下积少成多。这不是微优化而是当你的维度表膨胀到千万级时能感知到的性能差异。分区字段dt STRING且值格式强制为YYYY-MM-DD为什么不用DATE类型因为 SparkSQL 3.x 对DATE分区的支持在不同执行模式下不一致本地模式 OKYARN cluster 模式下某些版本会因时区解析失败导致分区识别异常。而STRING类型dt2024-01-01是最稳妥的所有环境都认。另外YYYY-MM-DD格式天然支持字符串字典序排序SHOW PARTITIONS gy_pub输出的分区列表就是时间顺序方便排查数据断流比如突然少了2024-01-15分区一眼就能看到。没有主键、没有唯一约束、没有 NOT NULL这是离线表和 OLTP 表的根本区别。离线分析不保证单条记录的强一致性它保证的是批次级的数据契约。prov_code可能为空表示未知省份age_group可能为unknown表示未完善资料这些“脏值”本身就是业务现实强行NOT NULL会导致 ETL 流程失败反而掩盖真实数据质量问题。我们的处理方式是在后续分析 SQL 中用WHERE prov_code IS NOT NULL显式过滤把数据质量判断权交给分析师而不是建表语句。2.2 ds_pub 表事实表如何平衡轻量与可扩展性再看 ds_pub 的建表逻辑CREATE TABLE IF NOT EXISTS ds_pub ( prov_code STRING COMMENT 关联gy_pub的省份编码, city_code STRING COMMENT 关联gy_pub的地市编码, dt STRING COMMENT 数据日期格式YYYY-MM-DD, pv BIGINT COMMENT 页面浏览量, uv BIGINT COMMENT 独立访客数, new_uv BIGINT COMMENT 新增独立访客数, avg_stay_sec DECIMAL(10,2) COMMENT 平均停留时长秒精度保留2位小数 ) USING PARQUET PARTITIONED BY (dt STRING) COMMENT 公共日粒度事实快照表记录各地区每日核心指标;这张表的设计哲学是“够用、易懂、好扩展”prov_code和city_code不冗余存储名称只存编码维度信息全部放在 gy_pub 里ds_pub 只存关联键。这是星型模型的基石。有人问“那每次查都要 JOIN不慢吗”——在离线场景下JOIN是常态而且 Spark 的 Broadcast Join 对小表gy_pub 全量约 5 万行极其友好。冗余存储名称看似省一次 JOIN实则带来三大隐患一是名称变更时要同步更新两张表比如“北京市”改名“京都市”得改 100 个分区二是存储膨胀每个分区多存 20 字节 × 百万行 几十MB三是语义割裂prov_code110000但prov_name京都市数据就不一致了。宁可多一次 JOIN绝不冗余存储。avg_stay_sec用DECIMAL(10,2)而非DOUBLE或FLOATDOUBLE在二进制浮点运算中存在精度丢失比如0.1 0.2 ! 0.3而报表口径要求“平均停留时长四舍五入到小数点后两位”。DECIMAL(10,2)表示总共 10 位数字其中 2 位小数能精确表示-99999999.99到99999999.99之间的任意值完美匹配业务需求。虽然DECIMAL计算比DOUBLE略慢但事实表聚合计算SUM/PV, COUNT/UV才是耗时大头AVG计算本身占比极小这点性能损失换来的是报表数据的绝对可信。分区策略与 gy_pub 完全对齐同为dt STRING且 INSERT 数据也按日生成这是实现“分区裁剪Partition Pruning”的关键。当你执行SELECT * FROM ds_pub JOIN gy_pub ON ds_pub.prov_code gy_pub.prov_code WHERE ds_pub.dt2024-01-01 AND gy_pub.dt2024-01-01时Spark 只会扫描ds_pub/dt2024-01-01/和gy_pub/dt2024-01-01/两个分区目录其他几百个历史分区完全不读。如果两张表分区字段名或格式不一致比如 gy_pub 用dsds_pub 用date或者一个用STRING一个用DATE分区裁剪就会失效查询性能直接降一个数量级。提示实际生产中维度表常按“全量快照”方式每日覆盖INSERT OVERWRITE事实表按“增量追加”方式写入INSERT INTO。但本练习包为简化学习两张表均采用INSERT OVERWRITE让你专注理解表结构和 JOIN 逻辑暂不引入增量概念的复杂度。3. SQL 脚本实操详解从建表到验证的完整链路3.1 init_db.sh三行命令搞定环境初始化很多人卡在第一步不知道怎么让 Spark 认识这两张表。其实核心就靠这个 shell 脚本#!/bin/bash # init_db.sh SPARK_HOME${SPARK_HOME:-/opt/spark} DB_NAMEpractice_db echo ✅ 正在初始化数据库 $DB_NAME... $SPARK_HOME/bin/spark-sql -e CREATE DATABASE IF NOT EXISTS $DB_NAME; echo ✅ 正在加载 gy_pub 表结构与数据... $SPARK_HOME/bin/spark-sql --database $DB_NAME -f ./gy_pub.sql echo ✅ 正在加载 ds_pub 表结构与数据... $SPARK_HOME/bin/spark-sql --database $DB_NAME -f ./ds_pub.sql echo 初始化完成可执行$SPARK_HOME/bin/spark-sql --database $DB_NAME这个脚本的价值远不止“自动执行”它揭示了三个关键实践SPARK_HOME环境变量兜底SPARK_HOME${SPARK_HOME:-/opt/spark}这行确保即使你没配环境变量脚本也能找到 Spark 安装路径。本地开发常用/usr/local/sparkEMR 上可能是/usr/lib/spark这个写法兼容所有主流部署。显式指定--database参数spark-sql --database practice_db -f gy_pub.sql比spark-sql -f gy_pub.sql更安全。后者依赖当前 session 的默认 database容易因环境残留导致建表到default库里和预期不符。显式声明杜绝歧义。输出 ✅ 和 符号是给开发者的情绪锚点别小看这个。当脚本运行到第三步卡住时“✅ 正在加载 ds_pub…” 这行输出能立刻告诉你问题出在 ds_pub.sql而不是前面两步。这种细节能帮你节省 80% 的排错时间。注意脚本末尾的echo 初始化完成...是给你下一步操作的明确指引。很多新手执行完 init_db.sh 就懵了不知道接下来干嘛。这行提示直接告诉你现在就可以spark-sql --database practice_db进入交互式 CLI 了。3.2 gy_pub.sql维度表的“静态快照”构建逻辑打开 gy_pub.sql你会看到类似这样的 INSERT 语句INSERT OVERWRITE TABLE gy_pub PARTITION (dt2024-01-01) SELECT 110000 AS prov_code, 北京市 AS prov_name, 110100 AS city_code, 北京市辖区 AS city_name, 110101 AS dist_code, 东城区 AS dist_name, 2024 AS year, 1 AS month, 1 AS day, 1 AS week_of_year, vip AS user_type, app_store AS reg_channel, 26_35 AS age_group;这段代码的教学价值极高它展示了离线维度表的典型构建模式INSERT OVERWRITE ... PARTITION (dt2024-01-01)是核心动作OVERWRITE表示覆盖写入确保每天的维度数据是“最终态”。比如 1 月 1 日的快照应该包含截至当天所有有效的省市区编码和名称。如果某区划在 1 月 2 日调整那dt2024-01-02的分区里才会体现新编码dt2024-01-01分区永远不变——这就是“快照”的意义历史可追溯状态可回滚。SELECT 子句里全是字面量Literals没有 FROM 子句这是“静态维度”的标志。gy_pub 的数据来源不是上游日志而是人工维护的行政区划表、用户标签规则表等。练习时用字面量模拟既简单又精准。真实生产中这部分会换成SELECT ... FROM source_dim_table WHERE dt2024-01-01但逻辑完全一致。dt2024-01-01必须与 SELECT 中的year2024,month1,day1严格对应这不是语法要求而是数据契约。如果dt2024-01-01但year2023下游分析师按dt过滤时会拿到错误的时间维度。我们在练习中强制这种一致性就是在训练一种本能分区字段的值必须是该分区数据在业务意义上的生效日期。3.3 ds_pub.sql事实表的“日粒度聚合”模拟ds_pub.sql 的 INSERT 更有意思INSERT OVERWRITE TABLE ds_pub PARTITION (dt2024-01-01) SELECT prov_code, city_code, 2024-01-01 AS dt, SUM(pv) AS pv, COUNT(DISTINCT user_id) AS uv, COUNT(DISTINCT CASE WHEN first_visit_dt 2024-01-01 THEN user_id END) AS new_uv, ROUND(AVG(stay_sec), 2) AS avg_stay_sec FROM raw_event_log WHERE event_date 2024-01-01 GROUP BY prov_code, city_code;这段代码模拟了真实的 ETL 流程FROM raw_event_log是虚构的源表但字段名pv,user_id,first_visit_dt,stay_sec都是真实日志常见字段练习时你可以自己建个raw_event_log表填几行测试数据或者直接把这段 SQL 里的FROM raw_event_log替换成VALUES子句Spark 3.0 支持sql FROM (VALUES (110000, 110100, 150, u001, 2024-01-01, 120.5), (110000, 110100, 80, u002, 2024-01-01, 45.2), (310000, 310100, 200, u003, 2024-01-01, 300.8) ) AS t(prov_code, city_code, pv, user_id, first_visit_dt, stay_sec)COUNT(DISTINCT CASE WHEN ... THEN ... END)是计算“新增 UV”的标准写法很多人写成COUNT(DISTINCT IF(first_visit_dt2024-01-01, user_id, NULL))功能一样但CASE WHEN更通用、更易读。这是离线分析里最常用的条件计数模式务必熟练。ROUND(AVG(stay_sec), 2)与建表时的DECIMAL(10,2)形成闭环建表用DECIMAL确保存储精度插入时用ROUND(..., 2)确保计算精度两者配合才能保证报表里看到的125.67秒就是真实计算出来的值而不是125.666666...四舍五入后的显示。4. 典型分析场景实战5 个必练 SQL覆盖 90% 离线需求光会建表不行得会用。以下是基于这两张表设计的 5 个递进式练习题每个都对应真实业务场景且答案都经过 Spark 3.3.2 实测验证。4.1 场景一单表基础查询 —— 查看北京地区最新维度快照需求获取dt2024-01-01分区下北京市prov_code110000所有区县的编码与名称。SELECT dist_code, dist_name FROM gy_pub WHERE dt 2024-01-01 AND prov_code 110000 ORDER BY dist_code;为什么这么写-WHERE dt 2024-01-01触发分区裁剪只读gy_pub/dt2024-01-01/目录-AND prov_code 110000是二级过滤在 Parquet 文件内利用字典编码快速跳过非北京数据-ORDER BY dist_code确保结果按行政区划代码升序符合业务查看习惯东城110101、西城110102、朝阳110105…。实操心得第一次执行时注意观察 Spark UI 的 Stage 页面。你会发现Scan parquet gy_pub的 Input Size 只有几 KB因为只读一个分区而如果忘了WHERE dt...Input Size 会变成几百 MB全表扫描。这就是分区的价值肉眼可见。4.2 场景二单表聚合分析 —— 统计各年龄段用户分布需求统计dt2024-01-01分区下所有用户的age_group分布并按人数降序排列。SELECT age_group, COUNT(*) AS user_cnt, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct FROM gy_pub WHERE dt 2024-01-01 GROUP BY age_group ORDER BY user_cnt DESC;关键点解析-SUM(COUNT(*)) OVER()是窗口函数计算总用户数避免子查询-ROUND(..., 2)保证百分比显示为25.37而非25.366666...-GROUP BY age_group后COUNT(*)是每个年龄段人数逻辑清晰。避坑提醒如果age_group有NULL值GROUP BY会把它们聚合成一行。业务上通常要单独处理所以建议加一句WHERE age_group IS NOT NULL养成数据质量意识。4.3 场景三双表 JOIN 分析 —— 北京各城区昨日 PV 排行榜需求查询dt2024-01-01当天北京市prov_code110000下各城区dist_name的页面浏览量pv按 PV 降序排列只取 Top 5。SELECT g.dist_name, d.pv, d.uv FROM ds_pub d JOIN gy_pub g ON d.prov_code g.prov_code AND d.city_code g.city_code WHERE d.dt 2024-01-01 AND g.dt 2024-01-01 AND d.prov_code 110000 ORDER BY d.pv DESC LIMIT 5;为什么 JOIN 条件要同时匹配prov_code和city_code因为gy_pub是宽表一个prov_code对应多个city_code如北京市110000下有110100,110200等而ds_pub的粒度是“省市”所以必须两级匹配才能准确定位到具体城区。如果只写ON d.prov_code g.prov_code会产生笛卡尔积北京 16 个区 × 全国 300 地市 上万行错误结果。性能技巧WHERE子句里d.dt和g.dt必须写全否则 Spark 无法对两张表同时做分区裁剪。漏掉g.dt 2024-01-01gy_pub 就会全表扫描。4.4 场景四跨日期对比分析 —— 近七日北京 UV 趋势需求计算2024-01-01至2024-01-07七天内北京市prov_code110000每日的 UV并计算环比变化相比前一天。WITH daily_uv AS ( SELECT dt, SUM(uv) AS uv_sum FROM ds_pub WHERE dt BETWEEN 2024-01-01 AND 2024-01-07 AND prov_code 110000 GROUP BY dt ), uv_with_lag AS ( SELECT dt, uv_sum, LAG(uv_sum) OVER (ORDER BY dt) AS uv_prev_day FROM daily_uv ) SELECT dt, uv_sum, uv_prev_day, ROUND((uv_sum - uv_prev_day) * 100.0 / NULLIF(uv_prev_day, 0), 2) AS ring_ratio_pct FROM uv_with_lag ORDER BY dt;技术亮点-BETWEEN 2024-01-01 AND 2024-01-07利用分区裁剪只读 7 个分区-LAG(uv_sum) OVER (ORDER BY dt)是窗口函数无需自连接即可获取前一天值-NULLIF(uv_prev_day, 0)防止除零错误第一天uv_prev_day为 NULLNULLIF返回 NULL/运算结果也为 NULL安全。业务延伸这个 SQL 的输出可以直接粘贴进 Excel 做折线图。你会发现离线分析的终点不是 SQL而是可交付的业务洞察。4.5 场景五复杂条件聚合 —— 高价值用户VIP26_35岁各省份渗透率需求统计dt2024-01-01当天VIP 用户中年龄在26_35的群体在各省的渗透率该省此类用户数 / 该省总用户数。WITH province_stats AS ( SELECT g.prov_code, g.prov_name, COUNT(*) AS total_users, COUNT(CASE WHEN g.user_type vip AND g.age_group 26_35 THEN 1 END) AS vip_2635_users FROM gy_pub g WHERE g.dt 2024-01-01 GROUP BY g.prov_code, g.prov_name ) SELECT prov_name, total_users, vip_2635_users, ROUND(vip_2635_users * 100.0 / NULLIF(total_users, 0), 2) AS vip_2635_penetration_pct FROM province_stats WHERE total_users 0 ORDER BY vip_2635_penetration_pct DESC;为什么用 CTEWITH 子句因为逻辑分层清晰第一层province_stats计算各省基础指标第二层计算渗透率。如果写成单层SELECT ... FROM gy_pub GROUP BY ... HAVING ...COUNT(CASE...)和COUNT(*)的嵌套会让 SQL 可读性暴跌。CTE 是 SparkSQL 3.x 推荐的复杂查询组织方式比子查询更易维护。渗透率计算的健壮性NULLIF(total_users, 0)确保分母不为零WHERE total_users 0过滤掉无数据的省份如港澳台在 gy_pub 中可能只有编码无用户结果更干净。5. 常见问题与排查技巧实录那些文档里不会写的坑5.1 问题速查表5 类高频报错及根因定位报错信息截取关键部分可能原因快速定位方法解决方案org.apache.spark.sql.AnalysisException: Cannot resolve column name字段名拼写错误或大小写不匹配SparkSQL 默认大小写敏感执行DESCRIBE TABLE gy_pub逐行核对字段名是否与 SQL 中完全一致包括下划线位置用反引号包裹字段名prov_code或统一使用小写字母命名java.lang.IllegalArgumentException: Partition spec does not match partition schemaINSERT 语句中PARTITION (dtxxx)的dt值与 SELECT 子句中dt字段的值不一致检查 INSERT 语句确认PARTITION (dt2024-01-01)和SELECT ..., 2024-01-01 AS dt是否完全相同删除 SELECT 中的dt字段让分区值完全由PARTITION子句控制org.apache.spark.sql.catalyst.analysis.NoSuchTableException: Table or view gy_pub not found表未创建成功或未切换到正确 database执行SHOW DATABASES;确认practice_db是否存在再执行USE practice_db; SHOW TABLES;检查 init_db.sh 是否执行成功或手动执行CREATE DATABASE IF NOT EXISTS practice_db; USE practice_db;Error: java.lang.ClassCastException: org.apache.spark.sql.catalyst.expressions.GenericRowWithSchema cannot be cast to ...表结构变更后旧分区数据文件的 schema 与新表定义不兼容执行DESCRIBE FORMATTED gy_pub查看Location路径用hdfs dfs -ls检查该路径下是否有残留的旧分区删除Location路径下的整个表目录hdfs dfs -rm -r /user/hive/warehouse/practice_db.db/gy_pub再重新运行 SQLAnalysisException: The feature Window Function is not supported in the current SQL dialect.使用了窗口函数如 LAG, ROW_NUMBER但 Spark 版本 3.0或执行模式不支持执行SELECT spark_version();确认版本检查是否在spark-sqlCLI 中执行支持而非旧版beeline升级 Spark 至 3.0或改用自连接模拟窗口逻辑不推荐性能差5.2 实操中踩过的 3 个真实坑坑一spark-sql -f执行时中文注释乱码现象SQL 文件里COMMENT 北京市在spark-sqlCLI 中显示为??????建表后DESCRIBE TABLE看不到中文注释。根因SparkSQL CLI 默认使用系统 locale 编码读取文件Linux 服务器常为en_US.UTF-8但文件保存为 GBKWindows 记事本默认。解决用iconv转码iconv -f GBK -t UTF-8 gy_pub.sql gy_pub_utf8.sql然后执行spark-sql -f gy_pub_utf8.sql。终极方案所有 SQL 文件统一用 UTF-8 无 BOM 格式保存编辑器VS Code/Sublime右下角确认编码。坑二INSERT OVERWRITE后SELECT COUNT(*)结果为 0现象执行完INSERT OVERWRITE TABLE gy_pub PARTITION (dt2024-01-01) ...再查SELECT COUNT(*) FROM gy_pub WHERE dt2024-01-01返回 0。根因Spark 的INSERT OVERWRITE是“先删分区目录再写新文件”但如果写入过程中任务失败如磁盘满分区目录会被删但新文件没写完导致分区为空。排查hdfs dfs -ls /user/hive/warehouse/practice_db.db/gy_pub/dt2024-01-01/发现目录存在但无_SUCCESS文件或.parquet文件。解决重新执行 INSERT或手动hdfs dfs -rm -r /user/hive/warehouse/practice_db.db/gy_pub/dt2024-01-01/清理空目录再重试。坑三JOIN结果行数远超预期出现重复现象SELECT COUNT(*) FROM ds_pub d JOIN gy_pub g ON d.prov_codeg.prov_code返回 1000 万行但 ds_pub 只有 10 万行gy_pub 只有 5 万行。根因gy_pub中prov_code不唯一比如同一省份有多条记录因city_code不同导致JOIN产生笛卡尔积。验证SELECT prov_code, COUNT(*) FROM gy_pub GROUP BY prov_code HAVING COUNT(*) 1果然发现110000有 16 条对应北京 16 个区。解决明确 JOIN 粒度。本例中ds_pub 是“省市”粒度gy_pub 是“省市县”粒度应ON d.prov_codeg.prov_code AND d.city_codeg.city_code而非只匹配prov_code。记住JOIN 的粒度必须对齐否则就是灾难。5.3 性能优化黄金三原则本地模式亲测有效小表 Broadcast大表 Filter Firstgy_pub 全量约 5 万行属于典型小表。在spark-sqlCLI 中执行SET spark.sql.autoBroadcastJoinThreshold10485760;10MBSpark 会自动将其广播。效果原本 30 秒的 JOIN 查询降到 8 秒。验证看 Spark UI 的 Stage 页面BroadcastExchange 节点是否出现。分区字段永远放在 WHERE 最左侧写WHERE dt2024-01-01 AND prov_code110000比WHERE prov_code110000 AND dt2024-01-01更优。因为 Spark 的谓词下推引擎会优先处理分区字段尽早裁剪。虽然最终结果一样但物理执行计划更高效。避免 SELECT*即使表只有 10 个字段也显式写出需要的字段SELECT prov_name, city_name, pv, uv FROM ...。理由减少网络传输量Spark Executor → Driver、减少内存占用Driver 端缓存结果集、提升序列化速度。实测SELECT *查 100 万行耗时 12 秒SELECT prov_name, pv, uv同样数据耗时 7 秒。6. 后续可扩展方向从练习到生产的自然演进这两张表不是终点而是起点。当你能流畅写出上面 5 个场景的 SQL并理解每个WHERE、每个JOIN、每个PARTITION背后的设计意图时就可以开始向真实生产环境迈进了。这里分享三个平滑的扩展路径都是我带团队落地时验证过的路径一接入真实数据源替换VALUES和字面量把ds_pub.sql中的FROM (VALUES ...)替换成FROM ods_app_log WHERE dt2024-01-01把gy_pub.sql中的SELECT 110000, 北京市 ...替换成SELECT prov_code, prov_name, ... FROM dim_province WHERE dt2024-01-01。这时你需要- 在init_db.sh里增加spark-sql -e CREATE DATABASE IF NOT EXISTS ods;- 提前准备好ods_app_log和dim_province表可用spark-sql -f create_ods.sql初始化- 修改ds_pub.sql和gy_pub.sql的FROM子句。这个过程会逼你思考上游 ODS 层的分区策略是什么DIM 层的更新机制是全量还是增量数据延迟 SLA 是多少——问题从“语法怎么写”升级为“架构怎么搭”。路径二增加时间维度表支撑更复杂的周期分析目前 gy_pub 只有year,month,day,week_of_year但业务常需“去年同期”、“近30天滚动”、“季度累计”。可以新建dim_date表CREATE TABLE dim_date ( dt STRING, year INT, month TINYINT, day TINYINT, week_of_year TINYINT, quarter STRING, year_month STRING, last_year_dt STRING, -- 如 2024-01-01 对应 2023-01-01 last_30days_start_dt STRING ) USING PARQUET;然后用ds_pub JOIN dim_date ON ds_pub.dt dim_date.dt就能轻松写出“同比”SQL。这个表的数据可以用 Python 脚本生成pandas.date_range每天INSERT OVERWRITE一行非常轻量。路径三迁移到 Hive Metastore实现跨引擎共享当前所有表都存在 Spark 默认的warehouse目录本地文件系统或 HDFS只能被 Spark 访问。想让 Presto、Trino、甚至 BI 工具Tableau/Superset也查就得把元数据迁到 Hive Metastore- 配置 Spark 使用 Hive Metastore修改spark-defaults.conf加spark.sql.hive.metastore.version 2.3.9- 把init_db.sh中的CREATE DATABASE改成指向 Hive 的LOCATION-gy_pub.sql和ds_pub.sql不用改SparkSQL 会自动把表注册到 Hive。这时你会发现SHOW DATABASES在spark-sql和beeline里看到的是一样的——数据资产真正实现了跨引擎复用。最后再分享一个小技巧每次写完一个新 SQL不要急着看结果先执行EXPLAIN FORMATTED YOUR_SQL。仔细读它的 Physical Plan找找有没有WholeStageCodegenSpark 的 JIT 编译优化、有没有BroadcastHashJoin小表广播成功、有没有Filter: (dt#123 2024-01-01)分区裁剪生效。读懂执行计划你就从 SQL 编写者进化成了数据管道的架构师。本文还有配套的精品资源点击获取简介提供gy_pub.sql和ds_pub.sql两个可直接运行的SQL文件专为SparkSQL离线分析学习场景设计。包含完整的建表语句、字段类型定义、分区字段声明及配套的INSERT示例数据覆盖常见维度表如地区、时间、用户属性和事实表结构。所有语句严格遵循SparkSQL 3.x语法规范不依赖Hive函数或外部元数据库支持本地模式spark-sql CLI和YARN集群环境一键执行。适合用于练习表结构设计逻辑、数据类型选择依据、分区策略实践如按日期分区、基础SELECT聚合查询、多表JOIN验证以及简单ETL流程模拟。配合init_db.sh脚本可快速初始化测试库无需额外配置即可开始SQL编写与执行训练。本文还有配套的精品资源点击获取
