通过 Impala 和 Hive 访问集群版历史数据

最后更新于:2017-11-24 11:24:17

本文简述如何结合 API 通过 Impala 和 Hive 直接读取并解析神策分析集群版中的历史事件数据表。

需要说明,直接访问底层数据表有很强的风险,如果后端升级可能会导致相关访问方式失效,因此建议在选择这种访问方式之前先确认 JDBC访问 是否可以满足需求,并优先使用这种方式导出数据。

1. 元数据

通过 Hive 和 Impala 读取集群版的历史数据前,需要获取一些必要的元数据,获取元数据需要使用神策分析的 API。

A. 项目/事件/属性 元数据获取

请先了解 API 的基本使用。

熟悉 API 基本使用后,请用以下三个 API 获取项目/事件/属性的元数据

  • /api/projects
  • /api/events/all
  • /api/property/all

其中 projects 返回结果中 name 是 project 名, id 是要查询的 impala 表的表名后缀(如id 为 1 则表名为 event_ros_p1)。此 API 返回内容大致如下:

curl http://....../api/projects...

[
    {
        "cname": "\u6d4b\u8bd5\u9879\u76ee", # 项目中文名
        "create_time": "2016-11-08 16:26:46",
        "id": 1,                             # 是 对应 impala 表 表名的后缀
        "name": "default",                   # 项目英文名 / 标识符
        "status": "AVAILABLE"
    },
    {
        "cname": "\u6b63\u5f0f\u9879\u76ee",
        "create_time": "2016-11-08 16:27:00",
        "id": 2,
        "name": "production",
        "status": "AVAILABLE"
    },

也可以使用多项目管理工具来查看项目 ID 信息,例如:

project_tools -m list

------------------------------------------
project.id:  1 
name:        default
cname:       default
super_token: null
normal_token:    null
status:      AVAILABLE
create_time: 2016-11-26 18:39:59.0
------------------------------------------

其中 events/all 返回结果中 id 是后面要用到重要字段,举例如下:

curl http://....../api/events/all...

[
    {
        "cname": "$_sign_up",
        "id": 34,                     # impala 表中 event_id 字段,对应此处的 id 值,结合此处元数据可解析出对应何种事件.
        "name": "$_sign_up",
        "pinyin": "$_sign_up",
        "tag": [
            3
        ],
        "virtual": false,
        "visible": true
    },
    {
        "cname": "$AppEnd",
        "id": 38,
        "name": "$AppEnd",
        "pinyin": "$AppEnd",
        "tag": [
            3,
            13
        ],
        "virtual": false,
        "visible": true
...

其中 property/all 返回结果中 db_column_name 和 data_type 是解析属性数据的重要字段.

此处举例说明属性元数据中重要字段含义,例如:

curl http://....../api/property/all...

[
    {
        "cardinality": 0,
        "cname": "iOS\u6e20\u9053\u8ffd\u8e2a\u5339\u914d\u6a21\u5f0f",
        "data_type": "string",                     # 字段类型,取值有 bool, number, string, list, date, datetime,存储格式各异,详见后文描述
        "db_column_name": "p__utm_matching_type",  # 属性对应的 impala 表的列名,需要依据此字段读取 impala 表中的属性数据
        "event_id": -1,
        "event_name": "$Anything",
        "has_db_column_name": true,
        "has_dict": false,
        "id": 95,
        "is_dimension": true,
        "is_in_use": true,
        "is_measure": false,
        "name": "$utm_matching_type",
        "pinyin": "iOS qu dao zhui zong pi pei mo shi"
    },

...

2. 访问历史数据

A. 查询

数据表使用 impala 或 hive 查询。

请注意 impala/hive 对 parquet 数据 timestamp 类型(time字段使用)处理不同,详见后文 “time 字段时区问题” 一节。

为正常访问神策分析的 Parquet 文件,Impala 需要升级到 2.6.0 以上,通过打开 PARQUET_FALLBACK_SCHEMA=true 支持神策的 Parquet 文件,具体请参考: https://www.cloudera.com/documentation/enterprise/5-8-x/topics/impala_parquet_fallback_schema_resolution.html

Hive 需要升级到 0.13.0 以上,保留默认设置 PARQUET_FALLBACK_SCHEMA=true,具体可以参考:https://cwiki.apache.org/confluence/display/Hive/Parquet#Parquet-Hive0.13.0

B. 库名 / 表名

库统一为 rawdata

event 历史数据表的表名为 event_ros_p{project_id} profile 数据表的表名为 profile_wos_p{project_id}

例如:

项目 default 的 id 为 1,对应的 event 历史数据表为 event_ros_p1,所属的库为 rawdata (以下简称 event 表)

C. time 字段时区问题

impala/hive 对 parquet 数据 timestamp 类型(time字段使用)处理不同

例如,神策系统设定时区为 Asia/Chongqing,同一条数据,用 Impala 查询,时间为 2016-12-12 08:33:57.930000000,而用 hive 查询则为 2016-12-12 16:33:57.93

time 字段存储的是 UTC 时间的数据,impala 按 UTC 时区读取,并换为 Asia/Chongqing 的本地时间表示后返回,而 hive 则直接按 Asia/Chongqing 读取事件数据,不做时区转换.

结论是:

  • 从 impala 中直接读出的 time,可以认为是正确的。
  • 想从 hive 中读出正确的 time 值,最简单的方法是把结果 -8 小时(假设本地时区是 Asia/Chongqing 东8区).

Impala 文档中对这一点有详细解释,详见:http://www.cloudera.com/documentation/enterprise/latest/topics/impala_timestamp.html#timestamp

D. 表结构

下面举例说明 event 表包含的字段和含义

+---------------------------+-----------+---------+
| name                      | type      | comment |
+---------------------------+-----------+---------+
| event_id                  | int       |         | 事件id,即 event_define.id
| month_id                  | int       |         | 月份 id,1970-01-01 所属 id 为 0,往后每个自然月 +1
| week_id                   | int       |         | 星期 id,1970-01-01 所属 id 为 0,往后每个自星期 +1
| user_id                   | bigint    |         | 神策系统内部使用的用户标识
| distinct_id               | string    |         | 此事件发生时,所使用的用户标识 
| time                      | timestamp |         | 事件发生的时间,注意 impala 和 hive 读出的不同
| day                       | int       |         | 日期 id, 1970-01-01 所属 id 为 0, 往后每个自然天 +1
| event_bucket              | int       |         | 事件分桶 id,过滤数据时,可作为过滤条件加快过滤
| p__app_version            | string    |         | 属性字段,均以 p_ 开头,下同。 property 元数据中 db_column_name 既对应此处的列名,属性字段存储格式详见后文
| p__city                   | string    |         |
| p__country                | string    |         |
| p__ip                     | string    |         |
| p__lib                    | string    |         |
| p__lib_version            | string    |         |
| p__manufacturer           | string    |         |
| p__model                  | string    |         |
| p__os                     | string    |         |
| p__os_version             | string    |         |
| p__province               | string    |         |
| p__screen_height          | bigint    |         |
| p__screen_width           | bigint    |         |
| p__wifi                   | bigint    |         |
| p__carrier                | string    |         |
| p__is_first_time          | bigint    |         |
| p__network_type           | string    |         |
| p__resume_from_background | bigint    |         |
| p__screen_name            | string    |         |
| p_event_duration          | bigint    |         |
| p_allowanceamount         | bigint    |         |
| p_channel                 | string    |         |
| p_keyword                 | string    |         |
| p_orderid                 | string    |         |
+---------------------------+-----------+---------+

eventid, month_id, week_id, user_id, distinct_id, time, day, event_bucket 这些字段所有 event 表均包含. p* 字段个数由各个项目的属性个数决定。

3. 属性值解析

NUMBER(1), STRING(2), LIST(3), DATE(4), DATETIME(5), BOOL(6)

属性字段命名为 p_*,与属性英文名相近,查询请以 property API 中 db_column_name 记录的为准.

不同类型的的存储格式和类型有所不同,具体如下:

Sensors Analytics 数据类型 中文名 data_type id impala/hive 表字段类型 存储格式
NUMBER 数值型 1 bigint 原始值 * 1000 取整存储。读取时需要 /1000 得到原始值。最终范围:-9E15 到 9E15 小数点后最多保留3位
STRING 字符串 2 string UTF-8 编码的字符串
LIST 字符串集合 3 string UTF-8 编码的,"\n" 分割的多个字符串
DATE 日期 4 bigint 按天取整的毫秒时间戳,例如日期为 2015/6/1 则,取当天北京时间 0:0:0,即 2015/6/1 0:0:0 计算对应的时间戳为 1433088000000。读取时,解析时间戳后取日期部分即可
DATETIME 日期时间 5 bigint 存储毫秒时间戳
BOOL 布尔值 6 bigint 真为 1 假为 0