Kettle生成日期维表写入Hive：完整实操指南

做数据仓库的应该都有这种经历需求方上来就是“按周汇总一下”“按月做个环比”但你翻遍整个数仓连一张能用的日期表都找不到。临时用group by拼日期逻辑第一版看着没问题第二版要附带“剔除周末”“区分月初月末”的时候SQL就开始失真了。这个场景我遇到过太多次最终解决方案就是老老实实生成一张日期维表而用 Kettle现在更多叫 PDI批量生成日期维度数据并落到 Hive 表里是我试过最省心的一条路。这篇文章不是讲概念就是记录一次完整的实操用 Kettle 生成 2019 年全年 365 天日期数据写入 Hive 表。整个过程包括表结构怎么设计、Kettle 和 Hive 怎么连、转换里的 JavaScript 脚本怎么写、数据写进去之后怎么验证以及我踩过的几个坑。适合正在做数仓建模、刚接触 Kettle 的读者对会 Kettle 但没往 Hive 写过数据的人也有参考价值。1. 日期维度数据为什么要专门用 Kettle 来生成1.1 一张独立的日期维表能解决什么问题先明确一点日期维表不是简单把一年 365 天列出来就完了。它是数仓星型模型里的公共维度所有事实表在做时间维度分析时都要 join 它。如果没有这张表每个报表各写各的日期逻辑就会遇到几个典型问题周一到底是算一周的开始还是周一当天“上周”这个边界各写各的涉及工作日统计时谁也不知道该不该把某个休息日算进去想做“月初首日”“季度末日”这类标记临时用 SQL 算又丑又慢图表工具想要一个统一的时间层级年-季-月-周-日没有维表就无从谈起。有了日期维表这些逻辑都收敛到表里的字段上报表端只做join和where口径自然就统一了。1.2 为什么不用 Excel、SQL 或 Python 硬生生造数生成一年日期的方案其实不少但每个方案都有让我不舒服的地方。用 Excel 下拉填充 365 行日期很简单但这只是“一次性的数据文件”。到了明年要生成 2020 年、后年要生成 2021 年还得重新拉一遍而且最终要落到 Hive 表里Excel 文件还得转 CSV、上传、加载整个链路很碎。用 SQL 递归也可以。比如在支持递归 CTE 的数据库里WITH RECURSIVE能生成连续日期。但问题在于我这份数据最终要落在 Hive 表里Hive 对递归 CTE 的支持远没有 MySQL、PostgreSQL 那么友好硬写递归 SQL 能跑但维护成本不低。用 Python 脚本生成日期再写 Hive本质上没问题前提是环境里得有 Python 环境、要有 Hive 的客户端依赖而且脚本出了问题改起来不像 Kettle 那样可视化。我最终选 Kettle 的原因很朴素它在图形界面里把一个过程拆成“生成行 - 计算字段 - 写表”每一步都能单独预览它天然支持参数化可以把起始日期、结束日期做成变量明年直接改参数重跑它和 Hadoop/Hive 生态有现成的连接方式写完直接落到目标表里。365 行这种量级对 Kettle 来说毫无压力但它的工程化管理价值远大于手工方案。2. 动手之前先做好准备Hive 表结构与 Kettle 连接2.1 先确认 HiveServer2 是可用的在用 Kettle 连接 Hive 之前我建议先在命令行用 Hive 客户端确认环境状态。我们的目标很明确Kettle 作为客户端工具通过 JDBC 连接 HiveServer2往表里写数据。所以你需要知道四个信息HiveServer2 的地址、端口默认 10000、数据库名称、账号密码。我实际踩过的第一个坑就是“先用了 Hive 1.x 的旧驱动怎么连都报错”。如果你面对的集群是 Hive 1.0 以前的版本驱动类是org.apache.hadoop.hive.jdbc.HiveDriver如果是 Hive 1.0 之后的 HiveServer2驱动类是org.apache.hive.jdbc.HiveDriver。现在绝大多数环境用的都是后者但驱动机制不一样连接串和类名也完全不同先确认版本能省一大半排错时间。2.2 Kettle 的 Hive JDBC 驱动配置Kettle 默认自带的驱动并不总能覆盖你集群里的 Hive 版本最稳妥的方法是手动把对应版本的 JDBC 驱动 jar 包放进 Kettle 的lib目录然后重启 Spoon。以常见的 Hive 2.x 为例需要放进去的核心 jar 包括hive-jdbc-x.x.x.jarhive-service-x.x.x.jarhive-common-x.x.x.jarhive-metastore-x.x.x.jarhadoop-common-x.x.x.jarlibfb303-x.x.x.jar严格来说不同版本依赖的 jar 组合会有差异。最省事的方法是找到你集群对应版本的hive-jdbc包它会带依赖列表把缺失的依赖一并补上。连接类型在 Kettle 里选Hadoop Hive 2填写的信息类似Host namehive-server-hostPort number10000Database nameods你目标表所在的库User name / Password对应账号测试连接时如果提示“No suitable driver found for jdbc:hive2://”基本就是 jar 包没放对位置或者驱动类名选错了。2.3 目标表结构怎么设计日期维表不需要造得很夸张但常用字段建议一次想清楚。我设计的 2019 年日期维表结构如下CREATE TABLE IF NOT EXISTS dim_date_2019 ( date_key STRING COMMENT 日期主键格式 yyyyMMdd, date_value STRING COMMENT 完整日期格式 yyyy-MM-dd, year INT COMMENT 年份, month INT COMMENT 月份, day INT COMMENT 日, quarter INT COMMENT 季度, week_of_year INT COMMENT 一年中的第几周简单算法, day_of_week INT COMMENT 星期几周一为1周日为7, is_workday INT COMMENT 是否工作日1是0否, is_month_start INT COMMENT 是否月初1是0否, is_month_end INT COMMENT 是否月末1是0否 ) STORED AS TEXTFILE;我特意把date_key设计成字符串而不是 int因为20190101这种写法在 int 里虽然也能存但某些 BI 工具在展示时可能自动做数学处理字符串更稳。要不要分区日期维表一年的数据才 365 条完全不需要分区。如果引入分区反而因为每个分区一个小文件在 HDFS 上产生一堆零碎文件纯属给自己找麻烦。is_workday这里我只按周一到周五来判断没考虑法定节假日调休。节假日逻辑建议放到二期用一张“节假日补偿表”来扩展这样日期维表保持简单调休数据不污染主表。3. Kettle 转换核心设计把 1 天变成 365 天3.1 第一步生成 365 行记录并加上递增序号整个转换的核心思路是行号就是距 2019-01-01 的天数偏移量。在 Kettle 里我用了三个步骤串联生成记录 - 增加序列 - JavaScript。“生成记录”Generate Rows步骤把“行数限制”Limit设为 365随便加个字段dummy作为占位。这个步骤本身只会复制 365 行完全相同的记录。接着接“增加序列”Add Sequence步骤字段名设为seq起始值为 0步长为 1。这样每一行会拿到从 0 到 364 的编号正好对应 2019-01-01 之后的 364 天。你可能会问为什么不在“生成记录”里直接生成日期因为 Kettle 的“生成记录”步骤生成的是静态值没法按行号动态变化。加上“增加序列”后面用 JavaScript 按seq计算日期才是通用做法。3.2 第二步JavaScript 步骤计算日期字段这是整个转换的核心。我在转换里拖入一个“JavaScript 代码”步骤不同版本翻译可能叫“脚本”或“Script”输入字段就是上一步的seq。Kettle 9.x 里我实测可用的一段脚本如下// 起始日期2019-01-01 var startYear 2019; var startMonth 1; // JavaScript 中月份从0开始1就代表2月 var startDay 1; var startDate new Date(startYear, startMonth - 1, startDay); // 从输入行获取序列号不同版本可用直接写 var seq parseInt(seq); 代替 var seq parseInt(get(Fields.In, seq)); // 当前行对应的日期 var currentDate new Date(startDate.getTime() seq * 24 * 60 * 60 * 1000); var y currentDate.getFullYear(); var m currentDate.getMonth() 1; var d currentDate.getDate(); // 日期主键 yyyyMMdd var dateKey y (m 10 ? 0 m : m) (d 10 ? 0 d : d); // 日期值 yyyy-MM-dd var dateValue y - (m 10 ? 0 m : m) - (d 10 ? 0 d : d); // 星期的计算getDay() 返回 0 表示周日6 表示周六 var jsDay currentDate.getDay(); var dayOfWeek (jsDay 0) ? 7 : jsDay; // 一年中的第几周按天数粗略计算 var firstJan new Date(y, 0, 1); var dayOfYear Math.floor((currentDate - firstJan) / (24 * 60 * 60 * 1000)) 1; var weekOfYear Math.ceil(dayOfYear / 7); // 季度 var quarter Math.floor((m - 1) / 3) 1; // 是否工作日周一至周五 var isWorkday (dayOfWeek 1 dayOfWeek 5) ? 1 : 0; // 是否月初 var isMonthStart (d 1) ? 1 : 0; // 是否月末判断当前月的最后一天 var lastDayOfMonth new Date(y, m, 0).getDate(); // m 表示下个月的月份日传0表示上个月最后一天 var isMonthEnd (d lastDayOfMonth) ? 1 : 0; // 构造输出行 var row createRow(11); row[0] dateKey; row[1] dateValue; row[2] y; row[3] m; row[4] d; row[5] quarter; row[6] weekOfYear; row[7] dayOfWeek; row[8] isWorkday; row[9] isMonthStart; row[10] isMonthEnd; putRow(dataOutput, row);这段脚本里有几个细节值得展开。get(Fields.In, seq)是 Kettle 脚本步骤里读取输入字段的标准写法。如果你用的版本兼容性不好也可以直接写var seq parseInt(seq);但前提是变量名不能和脚本内置函数冲突。我自己习惯用get(Fields.In, ...)因为字段名即使叫year、month也不会出问题。createRow(11)创建了一个长度 11 的数组然后用putRow输出。这一步的关键是Kettle 脚本步骤需要知道输出行的字段结构。最稳妥的做法是在脚本步骤的“字段”Tab 页里提前声明 11 个输出字段的名称和类型比如第一个字段名date_key类型 String第二个字段名date_value类型 String后面依次是 Integer。不同版本的 UI 位置略有区别但思路一样。3.3 第三步字段选择与表输出配置JavaScript 步骤输出的字段顺序是固定的但字段名在后续“表输出”步骤里不一定原样保留。我会在中间加一个“字段选择”Select Values步骤只保留 11 个目标字段并且顺便把字段类型改成 Hive 表对应的类型date_key和date_value是 String其余全部是 Integer。然后拖一个“表输出”Table Output步骤配置连接上前面建好的 Hive 数据库数据库连接选刚才配置好的Hadoop Hive 2连接。目标表填dim_date_2019。勾选“使用批量插入”Use batch inserts。提交大小Commit size设为 500 或 1000。点击“SQL”按钮Kettle 会自动生成建表脚本可以比对一下和刚才手写的表结构是否一致。这一步强烈建议做因为 Kettle 生成的字段类型有时候会和你预期不一致提前比对能避免写入时报错。3.4 参数化让年份变成可变输入如果每年都要生成日期数据硬编码 2019 在脚本里肯定不行。我在实际项目中会把年份和日期范围做成变量。在 Kettle 转换的设置里定义两个变量start_date和end_date。JavaScript 步骤读取变量var startDateStr getVariable(start_date, 2019-01-01); var startDateArr startDateStr.split(-); var startDate new Date(parseInt(startDateArr[0]), parseInt(startDateArr[1]) - 1, parseInt(startDateArr[2]));同时“增加序列”步骤的结束值不能写死 364要计算start_date到end_date的天数差。Kettle 的“增加序列”本身不支持动态表达式所以我一般使用一个前置 JavaScript 步骤先算出day_count然后传给后续或者在作业里先用一个“执行 SQL 脚本”步骤算出天数再传参。更工程化的做法是写一个作业Job在作业开始处用“JavaScript”步骤计算数据跨度然后传给转换。这样每年只需要改两个变量跑一次就能生成完整年份的日期维表。4. 完整执行过程与 Hive 写入验证4.1 转换执行流程记录整个转换执行起来的链路是这个样子“生成记录”生成 365 行“增加序列”给每行打上 0~364 的序号“JavaScript 代码”按序号偏移起始日期计算全部日期字段“字段选择”整理字段顺序与类型“表输出”写入 Hive 表dim_date_2019。我在 Spoon 里点击运行先看“预览”功能。在 JavaScript 步骤上右键 - “预览”能直接看到前 100 行数据。这一步我是必做的因为如果脚本里有字段类型错误预览阶段就能暴露出来不用等写入 Hive 再返工。预览时重点看三个值第一行是不是20190101最后一行跑完后是不是20191231中间某个日期比如 2019-02-28的is_month_end是否等于 1。如果这三点都对了再继续往下跑。4.2 Hive 端验证数据跑完转换之后最直接的数据验证是在 Hive 命令行执行SELECT COUNT(*) AS total_cnt, MIN(date_key) AS min_date, MAX(date_key) AS max_date FROM dim_date_2019;结果是365、20190101、20191231说明数据条数和跨度没问题。再抽查几个特殊日期SELECT * FROM dim_date_2019 WHERE date_key IN (20190228, 20190301, 20191231);这里重点看月末标记20190228的is_month_end 1因为 2019 年不是闰年2 月最后一天是 28 日20190301的is_month_start 1且month 320191231的is_month_end 1且day_of_week 2周二。我还会顺手统计工作日天数SELECT COUNT(*) AS workday_cnt FROM dim_date_2019 WHERE is_workday 1;2019 年是平年365 天周一至周五合计应该是 261 天。如果统计出来不是这个数说明周几判断脚本有 bug得回头查。4.3 如果跑重复了怎么办“表输出”默认是追加模式。如果你连续跑两次同样的转换Hive 表里会有 730 条数据验证时count(*)就不是 365 了。这个问题我刚开始也踩过。解决方式有三种在执行转换前先清空表在 Hive 里执行TRUNCATE TABLE dim_date_2019;或者DELETE FROM dim_date_2019;Hive 对事务表的支持有限建议直接用 truncate。在表输出步骤勾选“清空表”Truncate table每次写入前自动清掉旧数据。重建表如果表结构改了直接DROP TABLE再建。我这里推荐第一种数据量只有 365 条跑之前先TRUNCATE简单直观也不会因为 Kettle 和 Hive 表之间存在数据格式差异导致清空动作失败。5. Kettle 写 Hive 时常见的坑与排查过程5.1 JavaScript 步骤只输出了第一行这种情况非常典型。症状是预览时只有一条数据或者 Hive 表里只有 1 行。原因通常是在脚本里写了类似if (seq 0) { ... putRow(...); }的条件或者把putRow(dataOutput, row);放进了循环外。Kettle 的 JavaScript 步骤默认对每一输入行执行一次脚本如果你用if限制了输出其他行就会被“吞掉”。排查办法把脚本里的return false;或者return true;删干净确保每个输入行都走到putRow。如果是条件判断写错可以先不判断直接把所有输入行原样输出再逐步加条件。5.2 Hive 驱动类找不到或连不上这个坑我在前面提过症状包括No suitable driver found for jdbc:hive2://ClassNotFoundException: org.apache.hive.jdbc.HiveDriverUnable to load authentication plugin遇到这类问题第一步去查 Kettle 的lib目录里是否有对应版本的hive-jdbcjar。第二步确认连接串格式是jdbc:hive2://host:10000/dbname。第三步如果集群开了 Kerberos连接就需要额外配置 keytab 和 principal本地测试环境建议先关掉 Kerberos 做验证规则通了再往生产环境迁移。我还遇到过一次奇怪的报错日志里提示hive-site.xml找不到。这是因为 Kettle 连接 Hive 时想加载集群配置。最简单的处理方式是在 Kettle 应用目录下放一个hive-site.xml里面写上 HiveServer2 的地址或者直接在连接界面把信息写全让它不依赖hive-site.xml。5.3 写入 Hive 奇慢无比365 条数据按理说是秒级但如果表输出步骤没有勾选批量插入Kettle 会逐条执行 insert每条 insert 都是一次远程 JDBC 调用实际跑下来可能要几分钟甚至十几分钟。解决办法是勾选“使用批量插入”并把提交大小设到 500 以上。另外Hive 对单条 insert 本身的代价就很高如果数据量更大比如一次性生成几十年的日期数据我更推荐“先落 CSV 文件再 LOAD DATA 进 Hive”的方式。具体做法是Kettle 的“文本文件输出”步骤把结果写成 CSV文件名可以带日期后缀写到 HDFS 的临时目录然后在作业里执行LOAD DATA INPATH /tmp/dim_date_2019.csv OVERWRITE INTO TABLE dim_date_2019;这种方式写 Hive 的速度是 JDBC 写入的几倍也更接近生产环境的 ETL 姿态。当前 365 条数据用表输出没毛病但你要换成 10 年数据文件落地再 load 的优势就出来了。5.4 日期偏移和类型对不上有时候数据写进去了但日期整体差一天比如 2019-01-01 变成了 2018-12-31或者每天往前偏了 8 小时。这种情况多半是时区问题。JavaScript 里的new Date()使用的是 Kettle 运行环境的本地时区而 Hive 服务器时区可能是 UTC。如果两边的时区不一致JDBC 传输 timestamp 类数据时就会偏移。我们的表里date_key、date_value都是用本地时间的getFullYear()、getMonth()取出来拼的字符串不受 JDBC 时区转换影响所以整体偏一天的概率不高。但如果后续你在表里加了“创建时间”这类字段并让 Hive 或 Kettle 直接写入now()偏移问题就很容易暴露。解决办法让 Kettle 所在的服务器时区和 Hive 环境保持一致或者在 JDBC URL 后面追加;timeZoneAsia/Shanghai之类的参数不同驱动写法不同。最保险的方案是——日期维表里所有日期字段都用字符串类型保存不要用 timestamp。字段类型对不上是另一类问题。比如 JavaScript 里is_workday计算结果可能是字符串1但 Hive 表里字段是 INT写入时就会报invalid type或静默转成 null。所以我强调要做“字段选择”步骤把输出字段类型显式转成 Integer。Kettle 里修改字段类型的位置在“字段选择”步骤的“元数据”Tab把类型选成 Integer 就行。5.5 中文字段名引发的麻烦Kettle 本身支持中文字段名但 Hive JDBC 对中文字段名的支持有些环境并不好。我在实际开发中遇到过“字段名在 Kettle 里正常显示写入 Hive 后字段名变成乱码”的情况。所以我强烈建议日期维表的所有字段名一律用英文字母字段注释中文可以字段名本身不要用中文。这个习惯也方便后续的 BI 工具直接读取。6. 从日期维度生成延展开的几点个人经验日期维表虽然叫“基础表”但它牵扯的细节比想象中多。我在这里把自己做这类需求时的一些习惯一并分享一下。第一日期维表最好做成外部表。比如CREATE EXTERNAL TABLE IF NOT EXISTS dim_date_2019 ( ... ) STORED AS TEXTFILE LOCATION /data/warehouse/dim/dim_date_2019;这样做的原因是如果哪天表结构要重建直接DROP TABLE不会把 HDFS 上的数据文件删掉数据还在重新挂 metadata 就能恢复。管理表一个误删日期数据文件就跟着没了虽然数据能重新生成但没必要冒这个险。第二字段设计一定要预留扩展位。比如后面要加“是否节假日”“农历日期”“连续工作日编号”这些字段最好在设计时就留下字段位置或单独扩展表。日期值不要用int存统一用string格式yyyy-MM-dd是最通用的BI 工具和报表平台基本上都能自动识别。第三转换 vs 作业的使用边界要想清楚。Kettle 里单条数据处理链路叫“转换”调度层面的执行单元叫“作业”。日期维表这种固定批处理任务我会包一个作业作业里先执行验证 SQL比如检查昨天数据是否已存在再跑转换。这样即使调度平台重复触发也不会产生重复数据。第四Kettle 脚本写日期逻辑时不要自己手算闰年。JavaScript 的new Date(year, month, 0).getDate()能自动返回当月的最后一天比写if (year % 4 0 ...)靠谱得多。我第一版写过手算逻辑后来发现各种边界算错改成 Date API 之后一次通过。这个系列到这只是把日期数据本身生成出来了后续要做的节假日、调休、农历对应、财年财季这些内容我打算再单独整理。日期维表这种东西看着简单真的要做成一个“一次生成、多年复用、口径统一”的公共表还是有不少学问在里面的。