Learning Notes 2022/10/14 DAY3

一、Work Summary

今日依旧是熟悉数据库，但是今日有所进步，不重要的、历史原因遗留的表放弃暂时不看，提升了速度，同时对于用户画像的标签设计进行学习，周末的话，学习下标签体系的设计流程。

二、Individual learning

依旧是MaxCompute，一共是三个技术点。

1.导出SQL的运行结果

MC对与SQL结果的导入一共有四种方法：SQLTask方式导出，Tunnel方式导出，SQLTask配合Tunnel方式导出，DataWorks数据同步方式导出。那么SQLTask涉及到JAVA，所以暂时不考虑，因此主要学习Tunnel方式导出和DataWorks。

1）Tunnel

Tunnel有五个常用子命令：upload(u)、download(d)、resume(r)、show(s)、purge(p)、help(h).

upload：上传数据到MaxCompute的表中。支持文件或目录（指一级目录）的上传，每一次上传只支持数据上传到一张表或表的一个分区。分区表一定要指定上传的分区，多级分区一定要指定到末级分区。

-- 将log.txt（在MaxCompute客户端的bin目录中）中的数据上传至项目空间test_project的表test_table（二级分区表）中的p1="b1",p2="b2"分区。
tuunel upload log.txt test_project.test_talbe/p1="b1",p2="b2"

-- 将log.txt中的数据上传至表test_table中。scan参数表示需要扫描log.txt中的数据是否符合test_table的定义，如果不符合报错，并停止上传数据。
tunnel upload log.txt test_table --scan=true

-- 将其他路径下的log.txt中的数据上传至项目空间test_project的表test_table（二级分区表）中的p1="b1",p2="b2"分区。
tunnel upload D:\test\log.txt test_project.test_table/p1="b1",p2="b2";

download：从MaxCompute的表中下载数据。只支持下载到单个文件，每一次下载只支持下载一张表或一个分区到一个文件。分区表一定要指定下载的分区，多级分区一定要指定到末级分区。

resume：因为网络或Tunnel服务的原因出错，支持续传文件或目录。如果上传数据失败，通过resume命令进行断点续传，仅支持上传数据的续传。每次上传、下载数据被称为一个Session。在resume后指定session id完成续传。

命令格式：tunnel resume [session_id] [-force]

session_id：上传失败的Session ID。必选参数

-f：指定是否强制修复执行历史记录。默认忽略。

tunnel resume 20150610xxxxxxxxxxx70a002ec60c -force;

show：查看历史运行信息。

--命令格式
tunnel show history [-n <number>];
--显示历史记录，默认保存500条数据。
tunnel show history;
--显示前5次上传或下载数据的详细命令。
tunnel show history -n 5;
--显示最后一次上传或下载数据的日志。
tunnel show log;

purge：清理Session目录，默认清理3天内的日志。

--清理前5天的日志
tunnel purge 5;

help：输出Tunnel帮助信息。

tunnel help upload;
tunnel help download;
tunnel help resume;
tunnel help purge;
tunnel help show;
tunnel help;

当然对于各个子命令还有其他参数，这里不在一一赘述，可点击链接Tunnel命令查看。

2）DataWorks数据同步方式导出

DataWorks支持运行SQL并配置数据同步任务，以完成数据生成和导出需求。

1. 登录DataWorks控制台。
2. 在左侧导航栏，单击工作空间列表。
3. 单击相应工作空间后的进入数据开发。
4. 新建业务流程。
   1. 右键单击业务流程，选择新建业务流程
   2. 输入业务名称。
   3. 单击新建。
5. 创建SQL节点。
   1. 右键单击业务流程，选择新建 > MaxCompute > ODPS SQL。
   2. 填写节点名称为runsql，单击提交。
   3. 配置ODPS SQL节点，配置完成后单击保存。
6. 创建数据同步节点。
   1. 右键单击业务流程，选择新建 > 数据集成 > 离线同步。
   2. 填写节点名称为sync2mysql，单击提交。
   3. 选择数据来源以及去向。
   4. 配置字段映射。
   5. 配置通道控制。
   6. 单击保存。
7. 将数据同步节点和ODPS SQL节点连线配置成依赖关系，ODPS SQL节点作为数据的产出节点，数据同步节点作为数据的导出节点。
8. 工作流调度配置完成后（可以直接使用默认配置），单击运行。

2.多行数据合并为一行数据、行转列及列转行

1)多行合并一行wm_concat(合并分割符,合并字段名)，可以加distinct进行去重

select class,wm_concat(distinct',',name) from students group by class;

2）行转列及列转行

create table rowtocolumn (name string, subject string, result bigint);
insert into table rowtocolumn values 
('张三' , '语文' , 74),
('张三' , '数学' , 83),
('张三' , '物理' , 93),
('李四' , '语文' , 74),
('李四' , '数学' , 84),
('李四' , '物理' , 94);

create table columntorow (name string, chinese bigint, mathematics bigint, physics bigint);
insert into table columntorow values 
('张三' , 74, 83, 93),
('李四' , 74, 84, 94);

方法一：使用case when表达式，灵活提取各科目（subject）的值作为单独的列，命令示例如下。

select name as 姓名,
       max(case subject when '语文' then result end) as 语文,
       max(case subject when '数学' then result end) as 数学,
       max(case subject when '物理' then result end) as 物理 
from rowtocolumn 
group by name;

方法二：借助MaxCompute提供的内建函数实现，先基于CONCAT和WM_CONCAT函数合并科目和成绩为一列，然后通过KEYVALUE函数解析科目（subject）的值作为单独的列。命令示例如下。

select name as 姓名,
       keyvalue(subject, '语文') as 语文,
       keyvalue(subject, '数学') as 数学,
       keyvalue(subject, '物理') as 物理
from(
     select name, wm_concat(';',concat(subject,':',result))as subject 
     from rowtocolumn
     group by name);

2）行转列及列转行

方法一：使用union all，将各科目（chinese、mathematics、physics）整合为一列，命令示例如下。

--解除order by必须带limit的限制，方便列转行SQL命令对结果按照姓名排序。
set odps.sql.validate.orderby.limit=false;
--列转行SQL。
select name as 姓名, subject as 科目, result as 成绩 
from(
     select name, '语文' as subject, chinese as result from columntorow 
     union all 
     select name, '数学' as subject, mathematics as result from columntorow 
     union all 
     select name, '物理' as subject, physics as result from columntorow) 
order by name;

方法二：借助MaxCompute提供的内建函数实现，先基于CONCAT函数拼接各科目和成绩，然后基于TRANS_ARRAY和SPLIT_PART函数逐层拆解科目和成绩作为单独的列。命令示例如下。

select name as 姓名,
       split_part(subject,':',1) as 科目,
       split_part(subject,':',2) as 成绩
from(
       select trans_array(1,';',name,subject) as (name,subject) 
       from(
            select name,
        concat('语文',':',chinese,';','数学',':',mathematics,';','物理',':',physics) as subject 
            from columntorow)tt)tx;

3.MaxCompute SQL中的关联操作

MC关联操作中内外连接和其他数据库操作方式相同，不在一一赘述，有两个新的JOIN类型，可以学习一下

LEFT SEMI JOIN            对于左表中的一条数据，如果右表存在符合关联条件的行，则输出左表。

LEFT ANTO JOIN           对于左表中的一条数据，如果右表中不存在符合关联条件的数据，则输出左表。

---

There is no royal road to size, there is no end to learning work boat.
欢迎各位朋友指导、交流、评论！