Hive表结构设计

①请求日志

log_type|adx_id|device_id|device_type|package_name|req_num|req_time|package_name_cn|device_id_md5

请求日志表结构

CREATE EXTERNAL TABLE IF NOT EXISTS req_log
(
log_type string comment '类型',
adx_id int comment 'adx渠道id',
device_id string comment '设备ID',
device_type string comment '设备类型',
package_name string comment '包名',
req_num bigint comment '请求次数',
req_time string comment '请求时间',
package_name_cn string comment '包名(中文)',
device_id_md5 string comment '设备ID的MD5值'
)
COMMENT '请求日志表'
PARTITIONED BY (log_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/data/logs/req';

请求日志表按照log_date分区，|隔开每列的值，使用TEXTFILE存储的数据，hdfs的路径为'/data/logs/req'。

②用户行为日志

collect_day|device_id_md5|device_type|appid|activity_name|event_id|times|activity_times|duration|params

用户行为数据表结构

CREATE EXTERNAL TABLE IF NOT EXISTS pad_log
(
  collect_day date comment '采集日期',
  device_id_md5 string comment '设备MD5值',
  device_type int comment '设备类型 1 表示 IOS 2 表示安卓',
  appid int comment '包名ID',
  activity_name array<string>,
  event_id array<string>,
  times int,
  activity_times int,
  duration int,
  params string comment '备用字段JSON字符串'
)
COMMENT '用户行为数据表'
PARTITIONED BY (log_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/data/logs/pad';

Hive表知识点

①STORED AS TEXTFILE/SEQUENCE

如果文本数据是纯文本，使用STORED AS TEXTFILE。如果数据需要压缩，使用STORED AS SEQUENCE。

该设置项只针对内部表有效，写入的时候压缩文件。外部表就算是压缩文件，设置为TEXTFILE也会交由MR来解压的。

②ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'

如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

FIELDS TERMINATED BY '|' 表示数据列按照|隔开。

③PARTITIONED BY  (log_date string)

指定分区字段，按照日期来分区。

④ADD PARTITION 添加分区

ALTER TABLE req_log ADD PARTITION (log_date='20170430') location '/data/logs/req/20170430'
                        PARTITION (log_date='20170430') location '/data/logs/req/20170501';

⑤array_type 数组类型

定义：activity_name array

查询：select activity_name[0] from PAD_LOG;

条件：select * from PAD_LOG where array_contains(activity_name,value1);

⑥get_json_object JSON数据查询

定义：params string

查询：select get_json_object(params, '$.mobile') from PAD_LOG;

条件：select * from PAD_LOG where get_json_object(params, '$.mobile') = '18812345678';