HIVE开发注意事项

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时始终记得小表要放在左边，可以减少内存的使用量