详解bigquery数据格式
1.数据采集
BigQuery是谷歌云旗下的一个企业数据仓库，主要面向查询分析的企业需求，它使用了 Google 基础架构的强大处理能力实现超快速的查询分析，BQ的查询语言跟SQL类似但不尽相同。
上一篇文章主要讲firebase Analytic的数据指标，这些指标背后的原始数据就存储在BQ中；如果你的应用安装了firebase SDK，那firebase会自动采集一些核心的事件进行上报，上文中提到的几个核心事件均是firebase自动采集的事件，事件上报一般需要明确两个机制，一个是触发机制，再上篇文章中事件定义里已有相关描述（传送门：Firebase Analytics知多少），另一个是上报机制，客户端产生的数据会在事件触发之后立即传给firebase SDK，SDK遵守每5min或者定量batch的机制上报数据到服务器，但你会发现，很多离线场景的数据也会按时在BQ中查询到，因为数据上报不是一定通过你们客户端里的SDK，它是通过谷歌集成的服务来上报的，只要你手机中任意一个谷歌的应用在线使用，其他应用的数据就可以上报到服务器。
当然，安装了firebase SDK之后，我们也可以通过自定义事件的方式来收集数据，但上报的数据格式需要满足BQ的要求，接下来就具体讲下BQ的数据格式。
2.BigQuery的事件数据格式
BigQuery会给每应用建立一个数据集，数据集中可以放多张表，除了原始的两张日志收集表外，也可以放自己建的临时表。
数据集
对于每个关联到 BigQuery 的 Firebase 应用，系统都会在 BigQuery 应用中添加一个数据集，名字一般是“analytics_<propertyid>”。property_id是应用在firebase里面的一个媒体资源id，系统自动分配的。
表
数据集中会有“events_YYYYMMDD”格式命名的表，就是按日期分区的日志数据表。当天的日志是以“events_intraday_YYYYMMDD”的格式命名，这个表会随着数据的收集而实时填充。
行
表中的每一行分别对应着 Analytics（分析）SDK 上传的一个事件。
列
即数据字段，以一个user_engagement事件的json格式为例，字段的释义和获取原理附在每一行后面，ps：数据均做了脱敏处理，不是真实数据：
{
[
{
"event_date": "20210226", //事件分区日期，时区与配置的时区相同
"event_timestamp": "1587917881026000", //事件发生时刻时间戳
"event_name": "user_engagement", //事件名称
"event_params": [ //事件主体map
{
"key": "ga_session_id", //会话id,当应用在前台启动或者用户访问网页时就开始一个会话，应用退到后台或离开网页开始计时，超过30min分钟未继续访问，会话生命周期结束；超时时长可以自定义设置，一般手机应用的超时时长设置30s-5min范围内
"value": {
"string_value": null,
"int_value": "1587915555",
"float_value": null,
"double_value": null
}
},
{
"key": "firebase_screen_class", //屏幕类名称
"value": {
"string_value": "MaxActivity",
"int_value": null,
"float_value": null,
"double_value": null
}
},
{
"key": "engaged_session_event", //无意义
"value": {
"string_value": null,
"int_value": "1",
"float_value": null,
"double_value": null
}
},
{
"key": "firebase_event_origin",//无意义
"value": {
"string_value": "auto",
"int_value": null,
"float_value": null,
"double_value": null
}
},
{
"key": "ga_session_number", //第几个会话
"value": {
"string_value": null,
"int_value": "12",
"float_value": null,
"double_value": null
}
},
{
"key": "engagement_time_msec", //距离上一个user_engagement事件的时间间隔，单位是毫秒
"value": {
"string_value": null,
"int_value": "30000",
"float_value": null,
"double_value": null
}
},
{
"key": "firebase_screen_id", //屏幕id，即页面id，一个屏幕类包含多个页面，如个人主页屏幕类中有个人信息修改页和绑定手机号等页面
"value": {
"string_value": null,
"int_value": "2222222222222222",
"float_value": null,
"double_value": null
}
}
],
"event_previous_timestamp": "1587887553028000",//上一次记录此事件的时间戳
"event_value_in_usd": null, //事件的“value”参数在换算至以美元为单位后的值。
"event_bundle_sequence_id": "11111", // 上传这些事件的软件包的序列 ID。
"event_server_timestamp_offset": "-55555555", //事件发生时刻到落盘时刻的时间差
"user_id": "11111111-1111-1111-1111-111111",//用户id，该值是可以配置的，如将用户在app上注册的账号赋值给user_id
"user_pseudo_id": "ffffffffffff1111111111111111",//用户id，firebase自己定义伪id，32位，数字和字母组合，唯一用户id
"user_properties": [
{
"key": "ga_session_id",//会话id，不知道为什么这里也有。。
"value": {
"string_value": null,
"int_value": "1587915555",
"float_value": null,
"double_value": null,
"set_timestamp_micros": "1587917879151000" //会话生成时刻时间戳
}
},
{
"key": "first_open_time",//首次启动app时间
"value": {
"string_value": null,
"int_value": "1574766000000",
"float_value": null,
"double_value": null,
"set_timestamp_micros": "1574763609495000"
}
},
{
"key": "ga_session_number",//该用户的第几个会话
"value": {
"string_value": null,
"int_value": "12",
"float_value": null,
"double_value": null,
"set_timestamp_micros": "1587917879151000"
}
}
],
"user_first_touch_timestamp": "1574763609495000", //用户首次打开应用或访问网站的时间
"user_ltv": null,//用户价值，firbase没有做填充值，可认为无效字段
"device": { //设备信息
"category": "mobile", //设备类型，这里是手机，可能有pc，平板等
"mobile_brand_name": "Samsung",//手机品牌
"mobile_model_name": "SM-C7000",//型号
"mobile_marketing_name": "Galaxy C7",// 手机市场名
"mobile_os_hardware_model": "SM-C7000",//系统型号
"operating_system": "ANDROID",//系统类型
"operating_system_version": "7.0",//系统版本
"vendor_id": null,//IDFV,应用开发商标识符（仅在未收集到 IDFA 时存在），在同一个设备上来自同一个应用开发商的所有App，IDFV的值是相同的；不同的应用开发商运行在同一个设备上IDFV值不同；如果用户将属于该应用开发商的所有App都卸载，则IDFV的值会被重置，即使再重装此开发商的App，IDFV的值也和之前不同
"advertising_id": null,//谷歌广告id，即IDFA，一般具有谷歌服务的设备才有，可认为重置
"language": "en-us",//手机系统语言
"is_limited_ad_tracking": "No",//是否屏蔽广告追踪；目前ios14.0版本已经默认屏蔽广告追踪了
"time_zone_offset_seconds": "10800",//与格林尼治标准时间 (GMT) 的时差（以秒为单位）
"browser": null,//浏览器，适用于H5应用和web端
"browser_version": null,//浏览器版本，适用于H5应用和web端
"web_info": null//网站的信息记录，适用于H5应用和web端
},
"geo": { //地理位置
"continent": "Asia",//事件所在的大洲(基于IP地址解析)
"country": "China",//事件所在的国家/地区(基于IP地址)
"region": "HongKong",//事件所在的区域(基于IP地址)
"city": "HongKong",//事件所在的城市(基于IP地址)
"sub_continent": "Eastern Asia",//报告的事件所在的次大陆，如亚洲又分东亚，东南亚等
"metro": "(not set)" //事件所在的都是(基于IP地址)
},
"app_info": { //app信息
"id": "com.tencent.mm",//应用软件包的包名
"version": "3.4.0",//版本号
"install_store": null,//表示该应用是通过哪个应用商店安装的。
"firebase_app_id": "1:1111111111:android:11111111111",//与应用关联的Firebase应用ID
"install_source": "com.android.vending" //安装商店包名
},
"traffic_source": { //流量来源
"name": null,//最先获取到用户的营销广告系列的名称
"medium": "organic",//最先获取到用户的媒介名称
"source": "google-play"//最先获取到用户的广告联盟的名称
},
"stream_id": "1181257779", //数据流的数字ID
"platform": "ANDROID",//应用的开发平台
"event_dimensions": null,//事件维度，未使用
"ecommerce": null,//电子商务相关的信息，这里不适用
"items": []
}
根据上面互动事件的的上报字段json结构，我们可以知道事件一共由以下模块组成
- 用户信息 (user_properties),map结构，嵌套key-value
- 事件主体（event_params），map结构，嵌套key-value
- 应用信息（app_info)
- 设备信息（device)
- 广告系列（traffic_source）
- 地理位置(geo)
- 数据流和平台,stream_id和platform
- 电子商务(ecommerce),电商相关应用记录订单信息
- 商品信息(items)，电商相关应用记录商品信息
- 网站信息(web_info)，H5和web端应用记录网站信息
在设计自定义事件的结构时，只有用户属性和事件主体两个模块可以自定义，其他模块都是firebase自动收集的，用户属性一般放跟用户相关的信息，比如用户id，用户信息配置好后，后续的所有事件都会自动继承；事件主体就放事件的事实数据，以点击事件为例，事件的event params 结构中可能会包括点击位置，点击跳转链接，客户端按照key-value的格式进行上报就可以了。BQ的数据结构中因为有两组map格式，并且所有的value需要明确是什么格式的value（int，string等），所以查询起来会比一般的二维大宽表要麻烦一些，可能会用到cross join语法，实际应用的时候可以在数据集中建一些直接返回响应key的value值的函数，查询起来会更方便。
来源 https://zhuanlan.zhihu.com/p/352677969
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69999831/viewspace-3131750/ ，如需转载，请注明出处，否则将追究法律责任。
