HIVE开发注意事项
1 HIVE开发注意事项
1.1 始终保持怀疑的态度来使用Hive
1.2 HIVE环境中账户分类
1) 目前hive分为logana和payods两个应用用户,前者用作日志分析,后者用作内容分析,与gp保持一致
1.3 数据存储及物理文件目录
1) 目录结构:payods作为“内容”这边的总目录,下面二级目录以数据层次划分,数据层次下面三级目录以主题划分,如下
payods
payods/ods(dwd、dwb、dws、dm、st、dim、t、tmp)
payods/ods/trd(fnd、crd、chg、pay、cbz、bbz、ctu、crm、mob、tbd、tec),此处没有log主题
2) 每日全量一律用表分区存储,不再新建表,并且这样的表以“_dd”作为后缀(表示每个分区即时一份全量),分区字段命名见1.3-4)节
1.4 建表及命名
1) 除tmp表外其他表一律用外部表(external),(字段长度如何体现,建表系统),
create external table tabname
(
col1 int
,col2 string
)
partitioned by(dt string)
row format delimited fields terminated by ','lines terminated by '\n'
stored as textfile
location '/group/alipay/alipaydw/payods/ods/trd/tabname';
alter table tabname setserdeproperties('serialization.null.format' = '');
说明:external建表模式下,表分区删除后,数据文件仍保留,而tmp表属于加载过程中临时性质,用完以后需要将数据删除。所以tmp表不能建为external表
建表语句之后,必须通过ALTER TABLE 来设置set serdeproperties属性,将使的NULL值在文本中存储为空字符,符合目前使用的习惯及要求
2) 表数据存储格式一律用textfile,非压缩
3) 字段间用逗号(,)分割,行间隔用换行符(\n)分割
4) 日期分区字段统一采用dt命名,格式如下“yyyymmdd”
partitioned by(dt string),
5) dim表一律用非分区表
6) tmp表一律用分区表(用来清空数据,insert overwrite时select表数据为空时不会覆盖目标表数据,因此须采用清分区的方法,另外由于tmp是脚本执行过程中临时性质使用的表,所以通过清分区的方式可以释放空间),无后缀
tcl中tmp命名格式:tmp_tcl名字_序号
注意:每个tcl执行完成后,需要在最后进行drop清除分区的动作
如:alter table tmp_dim_trade_notetype drop partition (dt =${tmp_yyyymmdd}
7) ods、dwd,dwb表一律采用分区表,以DD结尾,(需要和GP上的表区别,GP上的表带有YYYYMMDD后缀),表示日明细表
8) dws表一律采用分区表,以DS结尾,表示日汇总表
1.5 数据类型及函数使用
1) 使用四种类型:string(字符串)、int(短整型)、bigint(长整型,用于金额等字段类型)、double(仅用于服务费率等含小数字段类型)
2) 日期格式字段统一使用yyyymmdd的格式,类型使用string
3) Timestamp格式字段统一使用yyyy-mm-dd hh24:mi:ss的格式,类型使用string
4) 排序是如果要求对所有结果有顺序用order by ,否则用distribute by ... sort by ...
其中 order by 是对结果的全局排序,而distribute by ... sort by是对分发到不同服务器上的数据进行局部排序
5) order by 1;类似的写法不正确,要用列名称排序
6) 需要对查询结果赋予自增序列,需要使用rownumber ()函数,它是一个UDF函数(淘宝内的自定义函数),在使用这个函数时,需要通过如下命令方式进入HIVE:
hive -i /data1/alipaydw/alitools/lib/ali_hive_function
7) 实现类似于row_number() over(partition bytrade_no order by modified_date) = 1的功能,排重并且取第一条
Select trade_no
rownumber(trade_no)
From (select trade_no
From table1
Distribute by trade_no
Sort by trade_no,modified_date) p
8) 目前HIVE暂时不支持不等值连接(需要二次开发来实现功能)
9) Hive中浮点数运算会存在结果不准确的问题
1.6 HIVE中的SQL 编写
1) Hive不支持一条一条的用insert语句进行插入操作,也不支持update的操作。数据是以load的方式,加载到建立好的表中。数据一旦导入,则不可修改。要么drop掉整个表,要么建立新的表,导入新的数据
2) SQL编写尽量不要太复杂,尽量拆成多个相对简单的SQL
3) SQL中group by中的字段如果直接用在聚集函数中会报错
4) Insert 或者 load 时返回的操作记录数有时会和实际值不一样(BUG,不过我们现在不使用该信息)
5) 常用Explain来查看执行计划
6) 事实表(大表)与多个维度表left outer join的时候要用 MAPJOIN 提高性能
select/*+ MAPJOIN(d1,d2)*/
from facttablename a
leftouter join
dim_1d1
on a.id = d1.id
left outer join
dim_2 d2
on a.id = d2.id ;
MAPJOIN注意事项:
a、如果MAPJOIN中,如果表的JOIN字段存在NULL值,那么将发生错误
b、MAPjoin不要用在子查询中
c、MAPjoinjoin字段不能出现函数(如trim(d1.id))
7) HIVE中大表一般都采用分区表,使用时需要注意限制分区(分区裁剪),避免全表扫表。HIVE的分区裁减原则在join时采取逻辑主表裁减原则
8) Join时始终记得小表要放在左边,可以减少内存的使用量