Hive开发规范、最佳实践

数据库命名规范
数据库层 Report工作层 Report层 OLAP工作层 OLAP层 DW 工作层
DW 层
库名 Htl_TmpDB Report_HtlDB Htl_TmpDB OLAP_HtlDB Htl_TmpDB
DW_HtlDB
Source 层
Source_HtlDB
Dimenstion层
– 避免使用关键字 – 实在不能避免，使用`转义
例如 `From`
最佳实践
• 总体原则：
– 不同角色，不同开发工具 – 与时间相关的表，按照时间做分区 – 支持多日的job同时运行，互不影响 – 良好、可维护的目录结构
最佳实践—角色
• 两大类：
– ETL – 分析师
最佳实践—角色
• ETL
• 自动运行Hive上ETL Job • 推荐使用环境：
• 建库语句
– 指定location
• create database if not exists DW_AdServerDB location '${location}/DW_AdServerDB.db';
– 建表时，按照字符类型的日期分区
PARTITIONED BY (d string); 格式为 YYYY-MM-DD 例如： 2013-07-23
• 判断参数格式 • 内部使用相对目录 • 内部不包含配置信息
最佳实践—ETL Job开发实例
shell脚本实例：
#!/bin/bash curDir=$(cd `dirname $0`; pwd) cd $curDir source common.sh
properties="../etc/db.abtest.properties" hiveDb=${hive_source_abtestdb} sourceDb=${mysql_abtestdb}
• 开发 HiveSQL • 使用 Shell 封装 SQL
项目开发流程 – 数据导入
• 需要从外部数据源导入数据 • 几种模式：
– 用SQOOP从数据库导入到Hive – Hbase 提供的RC File，直接创建为外部表 – CSV/TSV文件上传到HDFS，创建外部表
• kettle
项目开发流程– 开发
Dim_Hot
el
UBT
OLAP AbTest Hotel
Dimension
UBT
DW AbTest Hotel
Dim_AbT est
UBT
Source AbTest Hotel
Dim_UBT
数据库命名规范
• 总体原则：
– 数据库名以DB结尾 – 采取首字母大写 – 用下划线_风格的拼写规范 – 专门的临时库用于中间表
Hive应用现状
• 已经上线的项目
– UBT 数据进hive – ABTest – OrderSearch – 。。。 • 后续项目
• UBT 横向业绩 • Mysql汇总表切hive V1.0
Hive应用现状
• 涉及到的组
• Data Infrastructure • 基础分析服务组 • 酒店组
• shell脚本 .sh • 配置文件 .properties • 建库、建表语句 .sql • hive脚本 .sql • 日志文件 .log
最佳实践—ETL Job开发实例
shell脚本
• 使用BASH解释执行
– #!/bin/bash
• 智能判断当前目录
– curDir=$(cd `dirname $0`; pwd) – cd $curDir
• Developer 读、写、
• Reporter 读、clone
• Guest
读
项目开发流程 --- 数据库设计
• 设计数据库、表结构
– 根据业务需求，设计非结构化的表结构 – 是否需要分区 – 只初始化一次 – 避免在SQL中
• 创建数据库 • 建表 • drop表
项目开发流程 --- 开发Hive SQL
Query & analysis层
Dim_UbtDB Tmp_UbtQueryDB
表命名规范
• Source 层
– 基本原则： 与源表名一致 – 缺陷： 会有多种风格的表名存在
• Dimension 层
• 表名必须以Dim为前缀
• 其他层
• 首字母大写、不用下划线的驼峰命名规范 • 事实表以Fact为前缀
./commonFullTable.sh $properties $hiveDb experiment $sourceDb experiment
最佳实践—ETL Job开发实例
配置文件内容：
数据库用户 数据库密码 数据库驱动 数据库 jdbc url
最佳实践—ETL Job开发实例
配置文件实例： db.dw.properties
– 更新Release Notes 到Confluence – 监控job运行
项目开发流程 – 部署
• 存在的问题:
– 开发环境集群计算能力不足
– 熟悉过程
数据库层
Report OLAP DW
Tmp
DIM Tmp query
Source
每层内按照业务线切分
UBT
Report AbTest Hotel
– bipub 公共组
– bitrain
无线/新业务组
– bicorp
商旅组
– bianalysis 基础分析服务组
– bimob
无线/新业务组
最佳实践—角色
• Hive 自定义配置
– UDF 自动加载 .hiverc
最佳实践—角色
– 分析师
• AdHoc Job • 推荐使用环境：
– Squirrel GUI界面查询
最佳实践—ETL Job开发实例
– 部署到Leabharlann home/bianalyais/abTest
• bin shell脚本 • data 数据文件 • etc 配置文件 • schema 建库、建表语句 • script hive脚本 • logs 日志文件
最佳实践—ETL Job开发实例
– 文件后缀名规范
Hive应用现状
• 项目开发领先于规范制定 • 摸石头过河的过程 • 不断提炼、完善规范的过程
规范
• 项目开发流程 • 命名规范
• 数据库、表、视图
• 用户规范
项目开发流程
• 创建Git项目 • 设计数据库、表结构 • 开发需要的HiveSQL、Shell脚本 • 数据导入Hive（可选）
项目开发流程
最佳实践—ETL Job开发
• 小的Git项目
– 管理代码、脚本 – 目录结构与运行环境完全一致
• 专门的建库、建表脚本 • 剥离与环境相关的配置文件 • 在开发环境上完成开发 • 用crontab部署试运行
最佳实践—ETL Job开发实例
• Git项目目录结构
• bin shell脚本 • data 数据文件 • lib 放置jar包 • etc 配置文件 • schema 建库、建表语句 • script hive脚本 • logs 日志文件
表命名规范实例
• Fact_UBT_Order_Online X
• FactUBTOrderOnline √
• ubt_dim上的维表 Platform X
• 维表 DimPlatform
√
列命名规范
• Source层，保持原有列名不变 • 其他层
– 首字母大写、其余字母小写、不用下划线的驼 峰风格
• 在开发Hadoop集群上开发、测试 • 审核规范 • 性能测试 • 在生产环境部署
项目开发流程 --- Git
• Git 公司统一的版本管理工具
– 推荐使用的客户端
• msysgit Git + TortoiseGit
项目开发流程 --- Git
• Git 公司统一的版本管理工具
– 角色：
• Master 全部权限
• 权限：
– 业务数据库，读权限 – 个人临时库，写权限
最佳实践—多日job
• 支持多日job并发运行，要求： – 所有中间表都是分区表 – 预先创建好TmpDB以及对应的Tmp表 – 采用 INSERT OVERWRITE TABLE ttt PARTITION (d=‘YYYY-MM-DD’) DML语句覆盖旧 数据
– SSH 客户端 (Secure SSH Client) – Hive 命令行客户端
» Shell脚本内部调用 Hive –f sqlFile.sql
• 权限：
– 业务线内部数据库，写权限 – 业务线外数据库，读权限
最佳实践—角色
• ETL 账户列表
– bihtl 酒店组
– bipkg 度假组
– biflt 机票组
#!/bin/bash user='' password='' driver='com.microsoft.sqlserver.jdbc.SQLServerDriver' dbUrl='jdbc:sqlserver://192.168.99.37:28747;databaseName='
最佳实践-建库
• 在开发Hadoop集群上开发、测试
– Job是否需要多日重跑？ – 对应数据是否需要修复？ – 配置信息单独维护 – 目录结构与生产环境一致 – 目录结构与Git项目的目录结构一致 – Windows上的文件上传至linux机器上：
• Dos2unix避免\r的问题
项目开发流程– 审核
• 审核规范