写在前面
数据分析在产品工作中的价值:
- 产品经理可以通过数据洞察到问题。
- 产品经理可以在通过各种渠道发现问题时,快速反应量化问题。
- 产品经理可以在项目上线后检验成果。(PS.当然为了公平,如果你要公开结果,最好能让专业的同事先认可你的分析过程是OK的。)
一、初识数据
1. 数据结构
首先,数据库有多种【管理系统】,就像你电脑的Windows、macOS系统,数据分为了几种类型:
我们可以查的是Hive,MySQL查不了,别的不用考虑,用不到
其次,数据的存储方式是【库+表】,就像你电脑里的本地excel文件,库=文件夹,表=excel文件,一般记录方式是A.B(A=库名、B=表名)
最后,数据表中存储的是【字段】,就像你excel文件的列名(姓名、性别、年龄……)
2. 表的类型
1)静态数据(=excel,数据不会自动更新):收集静态数据 → 上传并创建Hive表 → OneSelect查询
eg. 职类信息(职类表)、城市信息(城市表)、学校信息(学校表)
2)动态数据(!=excel,数据会定期自动更新):埋点 → 上报埋点数据 → MySql记录数据 → Hive表读取数据 → Hive表更新数据 → OneSelect查询
eg. 用户信息(用户表)、A开聊了B(开聊表)、C买了畅聊卡(订单表)
3. 表的权限
与excel不同的是,数据表我们无权增删改,只能查。
而且,查询也需要有权限才行
如果没有权限就需要申请,申请时需留意:Hive表有5个安全级别 S1<S2<S3<S4<S5
S5级别hive表一般存的用户个人隐私数据(手机号、地址、附件等…),非特殊情况不要申请这种表。
S5的表一般会被清洗成一份「降级脱敏表」,需要的话可以申请
当然,申请前是可以看【字段信息】、【样例数据】的,这是为了确认表中数据确实是你需要的,避免乱提申请
- 查询语言
我们通常使用的查询语言时Trino,因为它足够快
语言 | 查询库表 | 优点 | 缺点 |
Trino SQL前身Presto | Hive | 快 | 不稳定,不能支持大规模运算只能查询,不能把结果保存为表 |
Hive SQL | Hive | 稳定,支持大规模运算支持结果存储、调度任务、灯河数据集 | 慢 |
二、查询数据
1. 简单查询
想象以下,你想要知道后现代主义艺术流派中国有哪些艺术家和作品,为此你需要去图书馆借一本《后现代主义艺术》。那么:
首先,你需要找到图书馆(数据管理系统Hive)
然后,你需要找到艺术类书架(库名database_name)和书名(表名table_name)
再者,你需要在书中找到目录-中国篇(字段名 field_name)
最后,你需要到借阅台登记并借走它(数据查询平台OneSelect)
在这个过程中,你跟图书管理员用的是口语表达。
但对数据库来说,你需要用它能听懂的语言:SQL → Structured Query Language → 结构化查询语言,你需要最后在借阅台(OneSelect)写下你的查询需求:
Select 艺术家,作品 ——我要查什么?
from 艺术类.《后现代主义艺术》—— 我要从哪查?
where 国籍=中国 —— 有哪些限制条件?
group by 艺术家,作品 ——按照哪些分类展示查询结果?
order by 作品——按照谁排序以展示查询结果?
limit 5 ——限制输出5条,不限制的话可能会跑出来几十万条数据,系统会崩溃
这样你就会得到一个查询结果的表格:
Select artist,artsfrom art.post_modernwhere nation='china'group by artist,artsorder by artslimit 5 | artistarts张三《AAA》张三《BBB》李四《CCC》王五《DDD》赵六《EEE》 |
2. 函数
是不是很简单?那么让我们数一数有多少个艺术家?
Select nation,count artist as numfrom art.post_modern?where nation='china'group by nation | nationnumchina271 |
所以,使用函数 count 可以计算有多少表中出现了多少行数据。
但是,也请思考下这里的两个问题:
1)为什么不用「limit」了?——因为limit限制的是输出数据行数,而不是查询行数
2)为什么是271?不是5?——因为限制了limit之后只会输出5行数据,而实际表里可能不止5行数据
但你也知道,“张三”同时出现了两次,表中可能还会更多重复数据,那么就需要做数据去重
Select nation,count(distinct artist) as numfrom art.post_modernwhere nation='china'group by nation | nationnumchina5 |
所以,使用函数 distinct 可以计算有多少表中出现了多少去重后的数据。
想一想:如何查询每个艺术家重复出现的次数?
3. 联表查询
想象一下,还是在图书馆,你想知道出现在《后现代艺术》中的中国艺术家,有谁也登上过《时代周刊》。
这时单纯查《后现代艺术》就不够了,你也需要《时代周刊》这本书,并且还需要有一个关键信息——人名,这可以让你查询同时出现这两本书中的艺术家。那么:
Select b.artist from (select distinct artist from art.post_modern where nation='china' )ainner join (select distinct artist from times.artist )b on a.artist=b.artist | artist张三李四 |
所以:
1)使用连接语句 inner join 可以把两个表重叠的信息链接起来。
当然,除了inner join,还有更多种类型的链接方式,感觉有点像布尔运算不是嘛……
2)联表查询时需保证字段类型的一致性,字段是有类型的(如下),如果字段类型不一致,需要用CAST(x as INT)来转换字符类型。转化后方可限制链接条件 on a.artist=b.artist
- string、varchar:字符串
- int:32位整数
- bigint:64位整数
- float:单精度浮点数
- double:双精度浮点数
- boolean:布尔类型
- date:日期
- timestamp:时间戳
3)如果需要从不同的表里取数据,需要有嵌套查询结构(select…from…where…),写为:
select …
from
(select…from…where…) a
left join
(select…from…where…)b on a.…=b.…
group by …
order by …
limit …
三、实践应用 🔺
1. 用Hive表查询
发现问题 → 找hive表 → 看hive表 → 定条件 → 查数据 → 看结果
发现问题 | |
找Hive表 | 打开数星平台-元数据管理: |
看hive表 | |
定条件 | 思考:有部分用户存在残疾信息乱填的问题,那么活跃用户中有多少人的残疾信息是乱填的?我们要把残疾信息取出来。那么,我们结合表内容来看:命中问题的人群在表中是如何记录的,应该取哪些字段,即select什么?geek_id(牛人ID)type_code (残疾类型)应该限制哪些条件?即where限制什么?level_code =’101′ |
查数据 | |
看结果 |
2.用埋点查询
发现问题 → 圈行为 → 测埋点 → 看埋点 → 查数据 → 看结果
发现问题 | |
圈行为 | 思考命中这个问题的用户,在使用产品时一般会有哪些行为表现:访问残疾信息填写页面,勾选多条数据并提交 |
测埋点 | |
看埋点 | |
查数据 | |
看结果 |
3.埋点设计
若也没有埋点(那我没招了……当然是先补埋点啊,数据又不会凭空产生……)
定指标 → 想参数 → 写规则 → 跟进开发 → 上线回测
定指标 | |
想参数 | 思考,用户点击跳转button的时候是否需要记录其他信息,在这个场景中,需要记录用户点击的是哪个选项后提交的,所以actionp设计为:actionp=0 —— 不需要actionp=1 —— 需要 |
写规则 | |
跟进开发 | 沟通开发,同需求改动一起上线 |
上线收集 | 上线1~2周回看数据收集情况 |
四、数据延用
1.建Hive表
路径:数星平台 → 元数据管理 → 数据表 → 右侧按钮「+新建表」
字段 | 选项与含义 |
写入类型 | 默认HIVE |
数据层级 | TMP(临时数据层)、DIM |
生命周期 | 数星平台有治理机制,超过N天没人用的表会触发下线处理,酌情选择 |
安全等级 | 数仓有对安全等级S1-S5的说明,一般选S2,包含了用户部分信息(工作信息、教育信息、基本信息等)的表选S3,包含更多敏感信息的话请停止建表,并问问数仓BP这事咋整 |
资产可见性 | 公共可见(谁都能看到这张表并申请)、部门/用户可见(谁都能看到这张表,但只有部分用户有权申请,不建议用,嘲讽值略高) |
说明 | 写点啥,防止下次看到这张表时已经不记得表内容和逻辑是啥了,以及造福大众 |
建表方式一:DDL(不教,建议也不要会,电子表的时代戴机械手表。。。)
建表方式二:可视化(用来创建需要被周期性写入内容的表,创建完毕后是一张空表)
字段 | 选项与含义 |
表名称 | 建议自己创建的表,用自己的特殊标记mark一下,比如我建的表后缀都叫_zy |
储存类型 | 默认PARQUET |
表类型 | 根据字段备注视情况而定 |
分区类型 | 拍黑板注意啊,如果表需要分区,那么就选分区表,用来分区的字段会单独出现在下方。一定不要选了非分区表,把用来分区的字段搞成了普通字段,然后还纳闷咋没分区呢? |
压缩区间 | 超过N个分区之后表内容会被压缩,写个180/365都行 |
+新增字段 | 要什么字段就加什么字段,规定好格式和字段先后顺序 |
方式三:文件上传(用来创建不需要被写入的一次性表)
2.Hive表数据写入
- Part 1 一次性数据表
- Step 1 在数星平台上创建表结构,比如我们希望有一张表来存储每天的活跃用户数量,包含两个字段:ds、active_users(都是正常字段,没有分区字段)
- Step 2 编写输出想要的结果的SQL,在select结果前增加一句insert into table table_name,用「Hive、Hive、Hive」运行
insert into table dac_fifteen.sql_class_test_zyselect ds, count(distinct uid) as active_usersfrom dwd_boss_flow.dwd_active_user_diwhere ds >= '2024-09-01'group by ds;
- Step 3 漫长的等待
- Step 4 编写检验结果的SQL,验证目标表中是否已经有了自己期待的结果——作业
- **Step 5 如果发现写错了,用这个语句可以保留表和表结构、清空表内容:truncate table table_name,同样需要用「Hive」执行
- Part 2 周期性更新的数据表
- Step 1 同上,创建表结构,这次需要设定好分区字段,分区字段一般与你的更新频次相关,比如:内容字段active_users,分区字段
- Step 2 数据研发计算平台 → 任务开发 → 新建任务,给任务起一个名字(英文),任务类型为HIVE,业务流程为「你自己的默认业务流程」
- Step 3 编写输出结果的SQL,这次不是直接向表里注入数据,而是向表的某一个分区里注入数据,同时SQL中需要设置一些用来周期性更新的参数
insert overwrite table dac_fifteen.sql_class_test2_zy partition(ds='${yesterday}')-- overwrite的意思是不管目标分区中有没有数据,都覆盖掉他,所以如果数据跑错了,可以不删,直接覆盖就完了select count(distinct uid) as active_usersfrom dwd_boss_flow.dwd_active_user_diwhere ds = '${yesterday}';
- Step 4 保存、冒烟测试、上线
- 保存:单纯save一下代码,防丢
- 冒烟测试:试运行一下
- 只能验证写入程序是可以运行的,但结果肯定是空、因为数据被写入表了,需要通过查询表来验证
- 上线:
- 执行时间:不要在凌晨0-2点,时间尽量往后;避免整点……
- Step 5 回溯数据:运维中心 → 任务运维 → 重跑 → 设置重跑时间区间 → 在「实例运维」中查看重跑进度
3.建BI报表
建BI报表是为了让我们每次需要最新数据时,不用重复跑SQL,建表的平台有两种,所以方式有两种:
1)短期报表:新干线-埋点分析-查询-保存
此方法多用于埋点直接查询,如果需要长期观察,可以点击【看板管理-我的看板-发布至灯河】
2)长期报表:灯河-数据集-新建数据集-可视化分析-保存