支付宝数据仓库HIVE开发注意事项

1、 HIVE开发注意事项1 HIVE开发注意事项1.1 始终保持怀疑的态度来使用Hive1.2 HIVE环境中账户分类1） 目前hive分为logana和payods两个应用用户，前者用作日志分析，后者用作内容分析，与gp保持一致1.3 数据存储及物理文件目录1） 目录结构:payods作为“内容”这边的总目录，下面二级目录以数据层次划分，数据层次下面三级目录以主题划分，如下payodspayods/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、主题2） 每日全量一律用表分区存储，不再新建表，并且这样的表以“_dd”作为后缀（表示每个分区即时一份全量），分区字段命名见1.3-4）节1.4 建表及命名1） 除tmp表外其他表一律用外部表（external），（字段长度如何体现，建表系统），create external table tabname( col1int ,col2string)partitioned by(dt string)row format delimited fields terminated by ',' lines terminated by 'n'stored as textfi

3、lelocation '/group/alipay/alipaydw/payods/ods/trd/tabname'alter table tabname set serdeproperties('serialization.null.format' = ''); 说明：external建表模式下，表分区删除后，数据文件仍保留，而tmp表属于加载过程中临时性质，用完以后需要将数据删除。所以tmp表不能建为external表建表语句之后，必须通过ALTER TABLE 来设置set serdeproperties属性，将使的NULL值在文本中存储为

4、空字符，符合目前使用的习惯及要求2） 表数据存储格式一律用textfile，非压缩3） 字段间用逗号（，）分割，行间隔用换行符（n）分割4） 日期分区字段统一采用dt命名，格式如下“yyyymmdd”partitioned by(dt string)，5） dim表一律用非分区表6） tmp表一律用分区表（用来清空数据，insert overwrite时select表数据为空时不会覆盖目标表数据，因此须采用清分区的方法，另外由于tmp是脚本执行过程中临时性质使用的表，所以通过清分区的方式可以释放空间），无后缀 tcl中tmp命名格式：tmp_tcl名字_序号 注意：每个tcl执行完成后，需要在

5、最后进行drop清除分区的动作 如：alter table tmp_dim_trade_notetype drop partition (dt = $tmp_yyyymmdd7） ods、dwd，dwb表一律采用分区表，以DD结尾，（需要和GP上的表区别，GP上的表带有YYYYMMDD后缀），表示日明细表8） dws表一律采用分区表，以DS结尾，表示日汇总表1.5 数据类型及函数使用1） 使用四种类型：string（字符串）、int（短整型）、bigint（长整型，用于金额等字段类型）、double（仅用于服务费率等含小数字段类型）2） 日期格式字段统一使用yyyymmdd的格式，类型使用st

6、ring3） Timestamp格式字段统一使用yyyy-mm-dd hh24:mi:ss的格式，类型使用string4） 排序是如果要求对所有结果有顺序用order by ，否则用distribute by . sort by .其中 order by 是对结果的全局排序，而distribute by . sort by是对分发到不同服务器上的数据进行局部排序5） order by 1;类似的写法不正确，要用列名称排序6） 需要对查询结果赋予自增序列，需要使用rownumber ()函数，它是一个UDF函数（自定义函数），在使用这个函数时，需要通过如下命令方式进入HIVE：hive -i /

7、data1/alipaydw/alitools/lib/ali_hive_function7） 实现类似于row_number() over(partition by trade_no order by modified_date) = 1的功能，排重并且取第一条Select trade_norownumber(trade_no)From (select trade_noFrom table1 Distribute by trade_noSort by trade_no,modified_date) p8） 目前HIVE暂时不支持不等值连接（需要二次开发来实现功能）9） Hive中浮点数运算会

8、存在结果不准确的问题1.6 HIVE中的SQL 编写1） Hive不支持一条一条的用insert语句进行插入操作，也不支持update的操作。数据是以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据2） SQL编写尽量不要太复杂,尽量拆成多个相对简单的SQL3） SQL中group by中的字段如果直接用在聚集函数中会报错 4） Insert 或者 load 时返回的操作记录数有时会和实际值不一样(BUG,不过我们现在不使用该信息)5） 常用Explain来查看执行计划6） 事实表（大表）与多个维度表left outer join的时候要用 MAPJOIN 提高性能select /*+ MAPJOIN(d1,d2)*/ from facttablename a left outer join dim_1 d1 on a.id = d1.id left outer join dim_2 d2 on a.id = d2.id ;MAPJOIN注意事项：a、如果MAPJOIN中，如果表的JOIN字段存在NULL值，那么将发生错误 b、MAPjoin不要用在子查询中 c、MAPjoin join字段