阿里云 ACP -大数据认证学习-MaxCompute

厨神

已于 2025-03-16 17:31:14 修改

阅读量557

点赞数 12

CC 4.0 BY-SA版权

分类专栏：大数据文章标签：阿里云

于 2025-02-08 11:28:17 首次发布

本文链接：https://blog.youkuaiyun.com/caocry/article/details/145509905

大数据专栏收录该内容

18 篇文章

订阅专栏

解决方案

数据集成（canal、kafka、filebeat）－数据处理（flink)－数据服务－离线处理－数据应用
flink（关联 hologress 维表）+maxcompute+hologres(代替 hbase)
maxcompute：
规模，安全，业务效能
发展：基于 hadoop－基于云服务器的 CDW－基于云原生 saas（slowflake、maxcompute、bigquery）
serverless 架构是最佳方案
大数据计算服务 naxcompute ：开箱即用（云数据仓库）（计算引擎 mapreduce）
阿里云－控制台－maxcompute－region 设置－创建项目－test_001-一对一或一对二－创建 datawork s－创建 maxcompute－test0001-按量－2.0－创建
通过 dataworks－控制台－导航－dataworks－工作空间列表－region－创建工作空间－设置全区域唯一的名字－单环境－下一步－选择计算引擎 maxcompute－按量－名称－按量－创建
有编辑器－客户端－dataworks－idea 插件 maxcompute

安装odpscmd

安装java8
安装客户端－设置odps.conf.ini:access_id,access_key,end_point（maxcompute服务地址）－tunnel_endpoint=http://dt.odps.aliyu.com-log_view_host=http://logview.odps.ali.com-启动./bin/odpscmd
odpscmd

命令	作用
show tables	展示表
create table a(name string)	创建表
odpscmd -e “show tables;”	命令模式
odpscmd -f test.txt	文本运行
odpscmd -s 脚本文件	脚本模式(代码嵌入式udf脚本或 sql Function脚本)

数据迁移

批量数据通道/流式数据通道/实时数据通道（dataHub）
tunnle客户端：批量+本地
maxCompute studio：批量+本地
dataWorks：批数据+阿里
MMA：hadoop集群到maxCompute
Java SDK：基于tunnle SDK，定制化数据迁移
流式：dataHub（FLUME、FLuentd、LogStash、OGG（oracle数据增量到datahub到maxcompute））

数据迁移

tunnel命令上传下载
tunnel SDK上传下载
MMA迁移工具上传数据
其他

tunnle 命令

命令	作用
tunnel help 【upload/download/upsert/resume/show/purge/help】【path】 <[project.]table[/partition]>	帮助
tunnel upload 【acp自动创建表目录/dbr丢掉脏数据/fd指定列分隔符/c指定字符集】	上传
tunnel help download 【cn指定下载列名称/limit下载记录数量/h是否包含列名信息（默认不包含）】【project.table/partition】【路径】	下载

tunnel SDK主要接口

table tunnel
uploadSession/downloadSession
instanceTunnel
TunnelBufferedWriter
流程：创建阿里账号 - 创建表通道- 创建上传会话- 读取文件 - 写入记录 - 上传会话提交

//阿里云账号
Account account=new AliyunAccount(accessID,accessKey)
Odps odps=new Odps(account);
odps.setEndpoint(odpsUrl)
odps.setDefaultProject(project)
try{
//表通道
TableTunnel tunnel=new TableTunnel(odps)
tunnel.setEndpoint(tunnelUrl);
PartitionSpec partitionSpec=new PartitionSpec(partition);
//上传会话
UploadSession uploadSession=tunnel.createUploadSession(project,table,partitionSpec);
TableSchema schema=uploadSession.getSchema();
//获取writer对象写入数据
RecordWriter recordWriter=uploadSession.openRecordWriter(0)
Record record=uploadSession.newRecord();
for(int i=0;i<schema.getColumns().size();i++){
	//设置record里每列的数据类型
}
//写入文件
for(int i=0;i<10;i++){
	recordWriter.write(record);
}
recordWriter.close();
//提交，开写
uploadSession.commit(new Long[]{}0L);
}

流程：创建阿里账号 - 创建表通道- 创建下载会话- 读取记录 - 写入文件

//阿里云账号
Account account=new AliyunAccount(accessID,accessKey)
Odps odps=new Odps(account);
odps.setEndpoint(odpsUrl)
odps.setDefaultProject(project)
try{
//表通道
TableTunnel tunnel=new TableTunnel(odps)
tunnel.setEndpoint(tunnelUrl);
PartitionSpec partitionSpec=new PartitionSpec(partition);
//下载会话
DownloadSession downloadSession=tunnel.createDownloadSession(project,table,partitionSpec);
long count=downloadSession.getRecordCount();
//获取数据
RecordReader recordReader=uploadSession.openRecordReader(0,count)
Record record；
while((record=recordReader.read()!=null)){
	consumeRecord(record,downloadSession.getSchema());
}
recordReader.close()
}

MMA迁移上传数据

MMA是一款MaxCompute数据迁移工具，支持Hive和OSS数据。
UDTF—MMAclient—MMAServer—Task Scheduler—Task Runner—ODPS SDK—MAxcompute–HiveSDK—HiveServer
1.确定hive版本 hive -version 下载mma-0.1.1 hive-1/2/3.x.zip
2.curl <Maxcompute endpoint>确认hive集群各个接电脑具备访问Maxcompute能力
3.hive开启hiveserver2和metasrote服务
4.确认maxcompute已配置2.0数据类型

命令	含义
configure	配置引导工具
gen-job-conf	生成任务配置工具
mma-client	客户端命令行工具
mma-server	服务端命令行工具
sql-checker	SQL兼容性检查

hive配置
- metasroteURI
  thrift://localhost:9083
- JDBC connection string
  jdbc:hive2://localhost:10000
- JDBC用户名
  xxx
- JDBC连接密码
  xxxx
hive安全配置（直接n即可，可选）
- jams-gss.conf 文件路径
- krbs.conf文件路径
- Kerberosprincipal属性
- kerberos keytab文件路径
maxcompute参数
- maxcompute endpoint
  http://service.cn-shanghai.maxcompute.aliuyn.com/api
- maxcompute项目名
- accesskey id
- accesskey secret
  最后生成json格式配置文件

创建Hive函数：
需要有对应jar包： ${MMA_HOME}/res/data-transfer-hive-udtf-$ {}下有jar包
hdfs dfs -put <jar包> hdfs:///tmp/
使用beeline创建Hive函数

drop function if exists default.odps_data_dump_multi;
create function default.odps_data_dump_multi as 'com.aliyun.odps.mma.io.McDataTransmissionUDTF' using jar 'hdfs:///tmp/data-transfer-hive-${}'

启动MMA Server：
$MMA_HOME/bin/mma-server即可启动

生成任务配置：

表级别
-进入conf的table_mapping.txt文件：source_cata.source_table1:dest_pjt.dest_table1
- 执行./gen-job-conf --objecttype TABLE --tablemapping …/conf/table_mapping.txt
- 可编辑上述文件，添加分区属性
库级别 -整库迁移
- ./gen-job-conf -objecttype CATALOG --sourcecatalog [sourcecatalog_name] --destcatalog [destcatalog_name]

提交迁移任务：

向mmaserver提交迁移任务
./mma-client --action SunmitJOb --conf []
查看状态
./mma-client --action GetJobInfo --jobid [id]
查看任务列表：ListJObs
删除： DeleteJob --jobid [jobid]
重置：RestJob --jobid [jobid]

其他工具

MaxCompute Setdio数据导入

界面操作：选择表–import data into table
tunnel 命令

Data Hub同步数据到MaxCompute
maxcompute建表–新建DataHub项目–新建Topic–向datahub topic写入数据–新建connector–确认同步结果

MaxComputeSQL

数据仓库工具，兼容大部分标准SQL，但不支持事务、主外键约束、索引等。作业准备提交耗时，实时性不高，适用于海量数据离线批量计算场景

数据类型：
- 基本数据类型
  bigint、string、double、decimal、datetime、boolean(1,0)
  int、smallint、varchar、float(2,0)
- 复杂数据类型
  array、map、sruct（2，0）
  使用2.0新增数据类型：

set odps.sql.type.system.odps2=true
set odps.sql.decimal.odps2=true

使用hive兼容版本数据类型：

set odps.sql.type.system.odps2=true;
set odps.sql.decimal.odps2.true;
set odps.sql.hive.compatible=true;

隐式转换：无需干预
显示转换：cast转换

运算符：

关系运算符：> <>= <= = <> is null, is not null .like, rlike(% _),in, between and
关系运算符：+ - * / %
逻辑运算符：and or not
位运算符：& | ||

DDL语句

创建删除表、生命周期管理、分区增删改合并、修改表属性、视图操作、其他

建表：

create [external] table [if not exists] <表名>
([字段名 字段类型 [not null] [default 默认值] [comment 注释]])
partitioned by (字段名 字段类型 [comment 注释],...) --分区表
clustered by |range clustered by (字段名,...)--分桶表
sorted by (字段名 [asc|desc],...) into [几个桶] buckets
[tblproperties("transactional"="true")] --指定为transactional(事务)表,设置表属性
[lifecycle 日期] --生命周期，默认永久

查看表信息

desc [extended更详细] 表名;

查看建表语句

show create table 表名;

删除表

drop table [if exists] 表名;

添加分区

alter table 表名 add [if not exists] partition 分区字段 [partition 分区字段..];

删除分区

alter table 表名 drop [if exists] partition 分区[partition 分区 ...];

修改分区值

alter table 表名 partition(分区) rename to partition(分区字段=分区值[,分区字段=分区值...]);

合并分区

alter table 表名 merge [if exists] partition()[,partition()...] overwrite partition() [purge];

修改表生命周期

alter table 表名 set lifecycle 日期;

禁止/恢复生命周期

alter table 表名 [具体到分区] {enable | disable } lifecycle;

修改表/分区的修改时间（改lastModifiedTime）

alter table 表名 touch [partition (partition_col='partition_col_value',...)]

修改表名

alter table 表名 rename to 新表名;

添加列

alter table 表名 add columns(列名 类型,...);

修改列名

alter table 表名 change column 旧列名 rename to 新列名;

修改表注释

alter table 表名 set comment '表注释'

修改列/分区注释

alter table 表名 change column 列名 comment '注释';

快速建表

//基于已经存在的表
create table [if not exists] 表名 [lifecycle days] as select语句结果
//基于已经存在的表结构
create table [if not exists] 表名 like 已存在表名

创建视图

视图可以引用其他视图，但不能引用自己，也不能循环引用
不允许向视图写入数据，例如insert into或insert overwrite操作视图
对查询语句select进行查询改写：利用物化视图进行查询改写：
set odps.sql.materialized.view.enable.auto.rewriting=true
5个不支持，1个默认不支持：
不允许物化视图名称和已存在的表和视图名重复
不支持基于存在的物化视图创建新的物化视图
不支持基于外部表创建物化视图
不支持窗口函数
不支持udtf函数
默认不支持非确定性函数（udf、udaf），需要设置：set odps.sql.materialized.view.suppport.nondeterministic.function=true;

create [or replace ] view [if not exists] 视图名(字段 [comment 注释],...) [comment 视图注释] as select语句结果;

删除视图

drop view if exists 视图名

重命名视图

alter view 视图名 rename to 新视图名;

创建物化视图

物化视图是一种特殊的物理表，会存储实际的数据，占用存储资源，但能加速查询
查看物化视图状态：explain或者logview的作业执行图或json summary页签查看

create materialized view [if not exists] [project_name].物化视图名
[lifecycle 天]
[列名 [comment 注释],...]
[{disable | enable } rewrite]
[partitioned on (列名[,列名...])]
[clustered by |range clustered by(列名[,列名...])]
[sorted by(列名 [asc| desc],...)
into 几个桶 buckets]
[tblproperties("compressionstrategy"="normal|high|extreme","enable_auto_subtitute"="true")]
as select 查询语句;

更新物化视图

alter materialized view 项目名.物化视图名 rebuild [partition(表达式1,表达式n...)]

DML语句

select\insert\other其他

查询select

最多10000行，查询分区时禁止全表扫描，若全表：set odps.sql.allow.fullscan=true,整个项目全表：setproject.odps.sql.allow.fullscan=true
join:不支持cross join（无on条件）连接
只允许and连接的on等值条件
最多16路join操作
////
mapjoin hint:一个大表和一个或多个小表执行join
left outer join:左表为大表
right outer join：右表为大表
inner join：左右均可
full ouer join：全外连接不可使用mapjoin
引用小表或子查询：需要使用别名
可以使用不等值或 or 连接多个条件
最多支持128张小表
所有小表占用内存总和不超过512M
////
groiuping sets
针对多维度聚合分析，既需要对a列聚合有需要b列聚合

select [all|distinct] 字段/聚合函数,...
from 表名
[where 筛选]
[group by 分组字段]
[order by 排序字段]
[distribute by 局部分组字段 sort by 局部排序字段]
[limit 行数]

//join：
select 字段,..
from 表1 t1
[left outer 左| right outer右 | full outer全| inner内 | natural自然 |隐式连接] join 表2 t2
on <表1连接字段=表2连接字段 [and ...]>

//mapjoin hint让小表提前加入内存，性能更优
select /* +mapjojin(小表1,小表2,...) */ 表字段
from 表名1 别名 join 表名2 别名
on 字段<字段 or 字段+字段<值 on 字段=字段;

//grouping sets
select 字段 from 表
group by 分组字段
grouping sets((分组字段,分组字段),(分组字段),()) 
//等价于：
select 字段 from 表
union all
select 字段 from 表 group by 分组字段,分组字段
union all 
select 字段 from 表 group by 分组字段

values插值

insert values 适用于一个表几条或十几条数据
values table从values列表中推导
insert overwrite覆盖/into追加更新数据到静态/动态分区

分布式环境中，单个进程最多输出512个动态分区
任意动态分区SQL不可以生成超过2000个动态分区
多级分区，在运行insert语句时允许指定部分为静态，但是静态必须在高级分区(前面)
动态分区的分区值不可以为NULL
///多路输出multi insert
单个sql最多可以写128路输出
对于分区表，同一个目标分区不可以出现多次
对于未分区表，该表不能作为目标表出现多次
对于同一张分区表的不同分区，不能同时有insert overwrite和insert into 操作

插入表的常见问题：

静态分区字段不能出现在后面的select 语句中
同一个分区不可以出现多次
insert overwrite 和insert into 不能同时有
静态分区在高级部分，动态分区在select语句的最后一个部分

insert values
insert into table 表名 values (),(字段值)..
insert into table 表名 partition(分区字段p)(key,p)values (值),()

//values table
insert into table表名 partition(p)
select concat(a,b),length(a)+length(b),'20170102' from values(),()..;
//insert overwrite / into
 //到静态分区
insert [overwrite|into]table 表名 [partition(分区字段＝值,...)]
select语句
 //到动态分区
 select overwrite table 表名 [partition(分区字段＝值)]
 select 语句
 //multi insert
 from 表名
 insert overwrite/into table 表名[partition(字段＝值,...)]
 select 字段
 insert overwrite/into table 表名[partition...]
 select 字段

数据导入load

通过load命令从外部存储(例如oss)将数据导入表或表分区。

load {overwrite | into} table 表名[partition(静态分区/动态分区)]
from location 外部路径
stored by 解析库'com.aliyun.odps.CsvStorageHandler'
[with serdeproperties(kv键值对)]

归档表数据

表的archive功能，可以将存储空间压缩50%左右，将数据存为Raid File,数据不是简单存三份，而是6份数据+3份校验块方法，这样有限的存储比约为1:3提高到1:1.5，可以节省一半的物理空间，另外也采用了更高压缩比的压缩算法。
压缩过程会降多个小文件自动合并
恢复数据块比原来方法更长，读的性能也会有一定损失

alter table 表名 <partition(分区字段＝值)> archive;
//示例：
alter table 表名 partition(ds='20170101') archive;
最后得到一个输出信息，显示压缩前和压缩后的物理大小

清空表数据

//清空非分区表
truncate table 表名;
//清空分区表
alter table 表名 drop partition(partition_spec);
//强制删除表数据
drop table 表名 purge;
alter table 表名 drop partition(part_spec) purge;
//其他方式
insert overwrite table 表名 select * from 表名 where 1=2;

SQL相关参数

set xxxx=xxx表示只对当前会话有效
setproject xxx=xxx 表示对整个project有效
|命令|含义|
|–|–|
odps.sql.allow.fullscan|是否允许对分区表进行全表扫描
|odps.sql.type.system.odps2|是否开启2.0版新增数据类型支持
read_table_max_row|设置1－10000，设置select查询返回结果的最大值行数

maxcompute内建函数

maxcompute内置了诸多函数，满足大部分业务场景的数据处理需求

 多行合并一行：相同class的将name合并为一行
 select class wm_concat(distinct ',',name) from students group by class;
行列转换
行转列：
 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 表名
 group by name;
 列转行：
 select name as 姓名,subject as 科目,result as 成绩
 from 
 (
 select name,'语文' as subject,chinese as result from 表名
 union all
 select name,'数学' as subject,mathematicsas result from 表名
 union all
 select name,'物理' as subject,physics as result from 表名
 )
 order by name;
 topN：
 select * from 
 (
 select empno,ename,depno,sal,row_number() over(partition by deptno order by sal desc)as 'rank'
 from emp
 )tmp
 where 'rank'<=3;
 近似数目估算APPROX_DISTINCT
 select approx_distinct(sal) from emp;//统计薪资列重复值的近似数目
 select deptno,approx_distinct(sal) from emp group by deptno;//按照部门deptno分组，统计薪资sal列非重复值的近似数目
 复杂类型函数map：将给定的列形成kv，挨着的两列为kv,重复的kv以后面的为主
 select map(c2,c4,c3,c5) from test_arr1;
 复杂类型函数array：使用指定的值构建数组
 select array(c2,c4,c3,c5) from test_arr1;
 复杂类型函数transfrom+lambbda表达式
 select transform(array(c2,c4,c3,c5),x->toupper(x)) from test_arr1;

用户自定义函数
udf：适用于一进一出业务场景，输入一行数据，输出一个值。
udtf：一进多出业务场景，读入一行数据，输出多个值可视为一张表。
udaf：多进一出业务场景，多条输入记录聚合成一个输出值。
特殊场景函数：
代码嵌入式udf：在脚本里面嵌入一段代码
sql语言定义函数：通过sql自定义函数实现，提高代码复用率的同时可以简化操作流程
开源地理空间udf：支持在maxcompute中使用hive地理空间函数分析空间数据（性能低于内建函数，自定义函数与内建函数名称相同，自定义函数会覆盖同名的内建函数，要使用内建函数，需要添加双冒号：select ::concat('ab','c')）

JAVA UDF开发

配置环境
代码编写
调试代码
导出JAR包
添加资源
注册函数
调用UDF

maxcompute/java/javawritable类型映射关系

在UDF中输入或输出参数的类型比如是java Type或者Java Writable类型，否则报错：ODPS－0130071
Java数据类型和返回值数据类型是对象，数据类型首字母需要大写
不允许Java Primitive Type（因为无法表示SQL中的NULL值）必须使用Java引用数据类型
MC复杂类型Array，对应的java类型是List，而不是数组

MaxCompute	Java	Java Writable
int	java.lang.Integer	IntWritable
bigint	java.lang.Long	LongWritable
double	java.lang.Double	DoubleWritable
boolean	java.lang.Boolean	BooleanWritable
string	java.lang.Sting	Text
varchar	java.aliyuno.odps.data.Varchar	VarcharWritable
binary	com.aliyun.odps.data.Binary	BytesWritable
datetime	java.util.Date	DatetimeWritable
timestamp	java.sql.Timestamp	TimestampWritable

java udf实现逻辑

继承com.aliyun.odps.udf.UDF类，实现evaluate方法
evaluate必须是非static的public方法
evaluate的参数和返回值将作为sql中udf的函数签名
用户可以实现多个evaluate方法
调用udf时框架会依据udf调用的参数类型匹配正确的evaluate方法（同名不同参）

import com.aliyun.odps.udf.UDF;//加载UDF
public final class Lower extends UDF{ //继承UDF类
	public String evaluate(String s){ //定义evaluate方法
	@override
	<body> //实现evaluate方法
	}
}

java udaf实现逻辑

实现udaf需要继承com.aliyun.odps.udf.Aggregator类
需要实现setup(初始化变量),newBuffer(定义数据读写通道),iterate(每条记录聚合到结果中),terminate(中间结果转化最终结果),merge(分片结果合到中间结果),close(关闭、清理)等接口
用户实现自定义Writable buffer
先iterate针对当前分片计算，写入buffer里面，merge针对每一个分区进行汇总得到中间结果，最后通过terminate得到最终结果
完成后达成jar包到maxCompute：add jar jar包名，-f强制版本覆盖
基于上唇的jar包创建自定义函数：create function test1 as '类名' using 'myudf.jarjar包文件名'，list resources查看资源，list functions 查看自定义函数
测试自定义函数是否能够正常使用:select 函数名(字段名) from 表名

python udf开发流程

配置环境－代码编写－调试代码－添加资源－注册函数－调用UDF
maxcompute python udf只支持python3
python udf @annotate函数签名传入的数据类型为maxcompute SQL类型，不是python的

maxcompute SQL类型	Python3 类型
bigint	int
string	str
double	float
boolean	bool
datetime	datetime.datetime
float	float
binary	binary
date	datetime.date
decima	decimal.decimal
array	list
map	dict
struct	collections.namedtuple

pythonudf 实现逻辑

通过python的pyodps包，实现evaluate方法,一个python只有一个evaluate方法

from odps.udf import annotate
# 函数签名，类型是maxcompute的
@annotate('biging.bigint->bigint')
class MyPlus(object)
	def evaluate(self,arg0,arg1):
		if None in (arg0,arg1):
			return None;
		return arg0+arg1

python udtf实现逻辑

class odps.udfBaseUDTF:是python udtf的基类，可以继承此类实现process，close方法（和java一样），所有输出参数都将被视为string类型

#coding:utf-8
#explode.py
from odps.udf import annotate
from odps.udf import BaseUDTF
@annotate('string->string')
class Explode(BaseUDTF):
	def process(self,arg):
		props=arg.split(",") #按照逗号进行分割
		for p in props:
			self.forward(p) #输出

udaf实现逻辑

class odps.udf.BaseUDAF:继承后实现Python UDAF
需要实现newBuffer,iterate,terminae,merge等方法
和java的udaf一样

@annotate('double->double')
class Average(BaseUDAF):
	def new_buffer(self):
		return [0,0]
	def iterate(self,buffer,number):
		if number is not None:
			buffer[0]+=number;
			buffer[1]+=1;
	def merge(self,buffer,pbuffer):
		buffer[0]+=pbuffer[0]
		buffer[1]+=pbuffer[1]
	def terminate(self,buffer):
		if buffer[1]===0:
			return 0,0
		return buffer[0]/buffer[1]

代码嵌入式UDF

将Java或Python代码嵌入SQL脚本，但是只能以脚本模式运行
因为是java JDK的子集，所以有使用限制：
不支持lambda、不支持catch多种exception、不支持自动泛型推导<>,会被忽略，必须显示Cast((String)myMap.get(key))、Assert会强制开启，不受JVM－ea参数控制，不支持Java8及以上版本的语言功能

create temporary function foo as 'com.mypackage.Reverse' USING
#code('lang'='JAVA')
package com.mypackage;
import com.aliyun.odps.udf.UDF
public class Reverse extends UDF{
//实现evaluate方法
	public String evaluate(String input){
		if(input == null) return null;
		StringBuilder ret=new StringBuilder();
		for(int i=input.toCharArray().length-1;i>=0;i--){
			ret.append(input.toCharArray()[i]);
		}
		return ret.toString();
	}
}
#END CODE;
select foo('abdc');

python嵌入自定义函数

create temporary function foo as 'embedded.UDFTest' using
#CODE('lang'='PYTHON','filename'='embedded')
from odps.udf import annotate
@annotate("bigint->bigint")
class UDFTest(object):
	def evaluate(self,a):
		return a*a;
#END CODE;
select foo(4);

SQL语言定义函数

SQL Function:SQL语言是用户自定义函数，弥补了MaxCompute只能用JAVA或Python创建UDF的不足，还扩展了函数类型的参数和匿名函数特性，提升表达业务逻辑的灵活性
用户权限：在创建、查询、调用、删除SQL自定义函数时，需要具备Function级别的权限
写入多条语句，需要脚本模式
永久sqlFUNCTION所有查询操作都可以调用该函数，临时SqlFunction只能运行在当前脚本模式
调用SQLFunction时，可以传入内建函数，UDF或SQL语言定义函数及匿名函数
create SQL function…永久函数
function…临时函数

create SQL function 方法名
(@参数 类型,...)
[returns @返回参数 类型]
as 
[begin] 方法表达式
[end];

在这里插入图片描述

性能调优

执行计划与运行信息查看
SQL调优
长尾问题优化

查看作业执行计划

在实际开发过程中，通常需要分析查询语句或表结构来分析性能瓶颈，MaxCompute提供explain语句实现此功能
可以查看job、task之间的依赖关系，task中所有operator的依赖结构

explain <查询语句>

命令	含义
job0 is root job	只有一个job
in job job0	job0含义
root tasks：M1	M1是root job，1代表task id为1
R2_1 depends on:M1	R2_1 job依赖于M1，_1表示直接依赖1
M	map task
R	reduce Task
J	Join Task
L	Local work
data source	数据源
TS	table scan operator 查询中form的逻辑
FIL	filter operator ，查询中where的逻辑
RS	reduce sink operator，task间数据分发操作逻辑
FS	File sink operator

查看作业运行信息

logView就是Job提交后查看任务和Debug任务的工具
用于查看作业运行详细日志信息：运行状态、运行结果、任务细节和每个步骤进度
在这里插入图片描述
用浏览器查看：

SQL调优

是否慢？是否资源短缺？是什么作业消耗的最多的资源？
是否慢？不是资源短缺？explain看看或logview看看是否数据倾斜
或者看看优化参数，看看状态并优化自定义项或表达式
在这里插入图片描述

分区裁剪

分区裁剪过滤分区列，以选择在执行SQL语句期间要读取的表中的某些区分，避免完整表扫描，可以使用explain语句检查datasource来看分区裁剪是否有效
失效原因：使用了自定义函数

哈希聚集

clustered | range clustered by分区聚集/范围聚集

create table [if not exists] 表名
[clustered | range clustered by(字段名,...)]
[sorted by(排序字段名 [asc|desc],...)
into 桶编号 buckets
]

多个去重语句场景优化

多个去重语句坑你发生数据膨胀，输入少于输出，使用特定的SQL语句缓解此问题：
用group by代替distinct

Mapper优化

使用一个多路输出读取一次，多次数据写入目标，防止重读取。

from (select * from src_table) as src
insert overwrite table 表名 select表达式
insert overwrite table 表名 select表达式

使用set odps.sql.mapper.split.size=N控制Mapper程序的数量，让每个mapper处理的数据量调小

Reduce优化

调整reduce数量set odps.sql.reduce.instances=N
调整动态分区参数，动态分区默认打开，为了防止动态分区生成过多的小文件，调为false关闭set odps.sql.reshuffle.dynamicpt=false/true

group by语句优化

启用group by语句优化set odps.sql.groupby.skewindata=true
拆分group by语句为两个部分（distinct换为group by,让她们在不同reduce）

长尾产生原因

长尾：task的大部分instance实例已经执行完成，但少数几个instance一直处于执行中。
原因就是在map reduce中按照key决定那个reduce处理，若出现数据倾斜（某个key很多数据）就会出现此问题。
在logview李米娜的long-tails查看。

优化之大小表join

如果某路输入比较小（大小表join），采用mapjoin hint/*+mapjoin(表名)*/，默认小表不超过512M，可以增大：set odps.sql.mapjoin.memory.max=N

大量热点值

将数据划分为具有热键的分区和具有非热键的分区，分别处理这两种分区，然后合并处理结果
2.1.判断哪些键为热点键

//热点key阈值
select k from 表名 group by 分组字段 having sum(1)>50000;
//完整
insert overwrite table 表名 partition(datatype)
select /*+mapjoin(b)*/ a.k,a.col1,
case when b.k is not null then 'hot' 
else 'normal'
end
as datatype
from 表名 a
left outer join
(
	select k from 表名
	group by k
	having sum(1) >50000;
)b
on a.k=b.k;

2.2对热键和普通键进行区分,热键用mapjoin，普通键直接左外连接

//热点key处理，mapjoin
insert overwrite table 表名 partition(datatype='hot')
select /*+mapjoin(b)*/
a.k,b.col1
from 表名 a
left outer join
(select * from 表名 where datatype='hot')b
on a.k=b.k
//普通key处理
insert overwrite table 表名 partition(datatype='normal')
select a.k,b.col1 from 表名 a
left outer join
(select * from 表名 where datatype='normal') b
on a.k=b.k;

少量热点值
通过设置odps.sql.skewjoin参数解决长尾问题

3.1 先开启功能
set odps.sql.skewjoin=true
3.2 再设置倾斜的Key及对应的值
set odps.sql.skewinfo=skewed_sec;(skewed_key)[(“skewed_value”)]

通过设置SkewJoin Hint避免热值倾斜

select /*+skewjoin(a)*/ from 表名1 a join 表名2 b
on a.c0=b.c0 and a.c1=b.c1;

//表名+可能产生倾斜的列
select /*+ skewjoin(a(c0,c1))*/ from 表名1 a join 表名2 b
on a.c0=b.c0 and a.c1=b.c1 and a.c2=v.c2

//表名+列+可能发生倾斜的key
select /*+skewjoin(a(c0,c1)((1,"2"),(3,"4")))*/
from 表名1 a join 表名2 b
on a.c0=b.c0 and a.c1=b.c1 and a.c2=b.c2;

null值引起的长尾问题优化

给空值加上一个随机的数，这样就是打散

select 字段
from 表名1 a join 表名2 b
on
case when 
a.k 
is null then
concat('invalid-data',int(rand(0)*10000))
else a.k
end
=b.k
limit 100;

安全与权限

maxCompute安全体系包括：

项目空间安全配置：鉴权模型

MaxCompute 安全模型

MaxCompute支持多种正交的授权机制，用户可通过设置下列参数来指定项目空间的健全模型，对相关鉴权模型设置进行调整后，Project可能会存在缓存，待10-15分钟后，设置会生效

参数	含义
security.CheckPermissionUsingACL	激活/冻结ACL授权机制，默认为true
security.CheckPermissionUsingPolicy	激活/冻结Policy授权机制,默认true
security.LabelSecurity	开启/关闭LabelSecurity安全策略,默认false
ProjectProtection	开启/关闭项目空间的数据保护机制,默认false
security.ObjectCreatorHasAccessPermission	允许/禁止对象创建者默认拥有访问权限，默认true
security.ObjectCreatorHasGrantPermission	允许/禁止对象创建者默认拥有授权权限，默认true
show SecurityConfiguration	查看当前授权模型

show

用户认证与授权管理

用户认证：在接入层完成

检查请求发送者的真实身份
正确验证接收到的消息在途中是否被篡改
云账号认证使用消息签名机制，可以保证消息在传输过程中的完整性Integrity和真实性Authenticiy

LVS负载均衡转发给空闲的Http Server，这个server会练习一个云账号服务器进行消息验证，返回对应AccountID，然后进入下一个层

项目空间怎么进行用户管理？

张三创建prj1项目，张三成为Owner，没有张三的授权，其他任何人都无法访问prj1的项目
张三授权李四允许访问prj1中的一些对象，赋一些对象的权限给李四
张三禁止李四访问项目，直接将李四云账号从项目中移除即可（前提是李四没有被赋予任何角色）

李四虽然被移除项目，但之前被授予的权限仍然保留在项目中，一旦下次加入同一项目，原有的权限将会被自动激活

授权三要素：主体Subject、客体Object、操作Action
授权两方法：ACL基于对象的授权、Policy基于策略的授权
ACL：访问控制列表

角色管理：
角色是一组访问权限的集合。用于简化授权操作
报表产生者：给角色一些权限
系统内部有两个默认角色：Super_Administer超级管理员（拥有操作项目内所有类型资源的权限和管理类权限）和Admin管理员（拥有操作项目内所有资源的权限和部分基础管理类权限）。
相比项目生产者，Admin不能：
1. 将Admin角色赋给别的用户
2. 不能设定项目空间的安全配置
3. 不能修改项目空间的鉴权模型
4. 不能创建删除修改package包
对于角色的限制：
1. Admin角色的权限不能被修改
2. 没被使用的角色才可以被删除

ACL授权的基本语法：
授权grant <权限> on <项目> to <用户>
撤销授权revoke <权限> on <项目> to <用户>

例子：
云账号lisi@aliyun.com是新加入到项目空间prj1的成员，他有提交作业、创建数据库、查看项目空间已存在的对象的需求。

use prj1;
add user ALIYUN$lisi@aliyun.com;
grant CreateInstance on project prj1 to user ALIYUN$lisi@aliyun.com;

Policy授权
Policy主要解决ACL无法实现的一些复杂授权场景：

一次操作对一组对象进行授权
带限制条件的授权

使用get policy获得授权设置
put policy修改授权设置
以最后一次提交的policy为准

get policy;
put policy <授权文件>;
get policy on role <角色名>
put policy <授权文件> on role <角色名>

//授权文件:{Statement:[{Effect,principal（主体，操作对象）,action（对应权限List代表查看）,condition,resource（可以操作的资源）}]}
{
	"Version":"1",
	"Statement":
	[{
		"Effect":"Allow",
		"Principal":"ALIYUN$lisi@aliyun.com",
		"Action":["odps:CreateTable","odps:CreateInstance","odps:List"],
		"Resource":"acs:odps:*:projects/prj1",
		"Condition":{
			"DateLessThan":{
				"acs:CurrentTime":"2013-11-11T23:59:58Z"
			},
			"IpAddress":{
				"acs:SourceIp":"10.32.180.0/23"	
			}
		}
	},{
		"Effect":"Deny",
		"Principal":"ALIYUN$lisi@aliyun.com",
		"Action":"odps:Drop",
		"Resource":"acs:odps:*:projects/prj1/tables/*"
	}]
}

使用grant进行policy授权
只支持授权给角色（Role），不支持授权给用户（User）
设置policy=true表示当前为policy授权
allow=true|false为白名单授权
deny=true|false为黑名单授权
revoke只有allow、objectName、rolename三个参数对应时才会生效

//获得权限
grant [权限] on <客体类型project> <客体名称（项目名）> to role <角色名> privilegeproperties("policy"="true","allow"="true|false","conditions"="acs:SourceIP in ('ip地址/子网号','ip地址/子网号') and 'odps:instanceId'='aaaaa'");

//收回权限
revoke [权限] on <客体类型project> <客体名称（项目名）> from role <角色名> privilegeproperties("policy"="true","allow"="true|false");

//--------
//例子
//1.授权dataworks_test读权限给aliyun_test--Read
grant Read on project dataworks_test to role aliyun_test privilegeproperties("policy"="true","allow"="true");

//2.授权所有表的只读权限给aliyun_test---Select
grant Select on table * to role aliyun_test privilegeproperties("policy"="true","allow"="true");

//禁止aliyun_test用户删除表---Drop
grant Drop on table * to role aliyun_test privileges("policy"="true","allow"="false");

Policy 优于 ACL：

查看权限：show grants;
查看指定用户权限：show grants for <用户名>
查看指定角色权限：describe role <角色名>
查看指定对象的授权列表:show acl for <客体名> [on type <客体类型>]

字符	权限
A	Allow 允许访问
C	condition 带条件，只出现在policy授权体系中
D	deny 拒绝访问
G	grant option，可对object授权

Download权限控制

为了避免tunnel下载表和实例操作存在数据泄露安全风险，需要对权限进行限制。
在创建MaxCompute后，Download 权限控制默认为关闭(代表所有人都能下载)，项目所有者，或super_administrator角色的用户可以开启或关闭这个控制
setproject odps.security.enabledownloadprivilege=true|false

Download 授权命令格式

grant Download on <客体类型> <客体名> to <主体类型> <主体名>;

Download撤销命令格式

revoke Download on <客体类型> <客体名> from <主体类型> <主体名>;

注意事项：
1. 只支持已存在的对象、被授权人进行授权
2. 只支持将具体对象的Download权限授予用户，不支持通过通配符为用户授权。（需要通配符，通过角色进行授权）
3. 如果删除了对象，MaxComput会自动撤销与改对象关联的所有Download授权信息。
4. 当一个用户被移除后，与该用户有关的授权仍然会被保留，要彻底清除用户的权限（purge download from user <用户名>）

Download 权限控制示例

//给用户授予Download权限
use test_project_a;
add user RAM$username@example.com:Allen;
grant Download on table sale_detail to USER RAM$username@example.com:Alien;
show grants for RAM$username@example.com:Alien

//为用户撤销Download授权
use test_project_a;
revoke Download on table sale_detail from USER Ram$username@example.com:Alien;
show grants for RAM$username@example.com:Alien;

//基于角色
//基于角色为多个用户授予Download权限
use test_project_a;
// 1. 创建用户
add user RAM$username@example.com:Alice;
add user RAM$username@example.com:Tom;
// 2.创建角色
create role Worker;
// 3.将用户添加到角色
grant Worker TO RAM$username@example.com:Alice;
grant Worker TO RAM$username@example.com:Tom;
// 4.角色添加权限
grant Download on table tb_* to ROLE Worker;

//为多个用户撤销基于角色授予的Download权限
use test_project_a;
revoke Worker from RAM$username@example.com:Alice;
revoke Worker from RAM$username@example.com:Tom;
show grants for RAM$username@example.com:Alice;

基于标签的安全控制

LabelSecurity是项目空间级别的一种强制访问控制策略（Mandatory Access Control(MAC)）让项目空间管理员能更加灵活地控制用户对列级别敏感数据的访问。（0-9级）
MAC
MAC就是对用户和资源设置等级（0-9），只有当用户>=资源等级，用户才能访问到对应资源。

LabelSecurity基本操作：

操作	含义
set security.LabelSecurity=true/false	打开LabelSecurity安全机制开关
set label to user <用户名>	设置用户安全许可标签
grant label on table <表名> [(字段列表)] to user <用户名> [with exp <天数>];	显式授权低级别用户访问高敏数据（可以细化到列）
revoke label on table <表名> [(字段列表)] from user <用户名>	撤销显式授权
clear expired grants	清理过期的显式授权
show label [<等级>] grants [for user <用户名>]	查看一个用户能访问哪些敏感数据集
show label [] grants on table <表名>	查看一个敏感数据表能被哪些用户访问
show label [<等级>] grants on table <表名> for user <表名>	用户对指定表上列级别的Label授权
allow project <项目名> to install package <库名> [using label ]	包安装者对包中敏感资源许可访问级别，允许用户安装包并设置标签等级

LabelSecurity注意事项：

LabelSecurity 安全机制开关必须由owner打开，admin角色没有此权限
用户的安全许可标签和文件敏感等级取值均为0-9，两者相互对应
显式设置的列的敏感等级优先级高于表的敏感等级，和顺序、等级高低无关
设置包时，若省略[usnig label <数字>],则默认为0，即只可以访问非敏感数据
跨项目空间访问敏感数据时，包安装者的项目空间中的所有用户都将使用此许可的访问级别。

//例子
set security.LabelSecurity=true; //只能由owner执行
set label 3 to user ALIYUN$lisi@aliyun.com;
grant label 3 on table t1(col1,col2) to user ALIYUN$lisi@aliyun.com with exp 1; //设置一天访问两列的权限，且等级为3

//多次设置等级：
set label 1 to lable t1;
set label 2 to table t1(id,name);
set label 3 to table t1;

//结果为：id，name的等级为2（列敏感度优先级最高）

项目prj1中的表cusmtoers有100列，其中三列含有敏感数据：name，ID,mobile_no。当前DAC的机制中已经授权了所有用户对该表的select操作。张三作为该项目的owner希望除了显式授权的几个用户之外，其余的所有用户都不允许访问这三列。请告诉张三应该如何操作才能实现上述目的。

//1. 开启Labelsecurity
use prj1
set security.LabelSecurity=true;
//2. 给这三列设置敏感等级
set label 2 to table customer(name,ID,mobile_no);

项目组成员李四被批准7天内可以访问customers表的name字段，张三需要做什么操作才可以满足他的要求？

//grant 显式授权并设置with exp 7
grant label 2 on table customers to user ALIYUN$lisi@aliyun.com with exp 7;

完成上述赋权之后，张三有了新的担心：李四会不会把数据插入到他自己新建的表custmer_name中然后把这张表的访问权限赋给其他人，比如他的好朋友小高，以及和他的闺蜜小蓝？张三想通过禁止WriteDown来防止上述不良事件的发生，但她想不起来具体该设置哪些参数了，请你告诉她应该怎么实现。

//控制对象的创建者能访问但不能授权
set security.ObjectCreatorHasAccessPermission=true;
set security.ObjectCreatorGrantPermission=false;

跨项目空间资源分享

作为非本项目团队成员小王的访问请求，最为owner的张三怎么操作？
普通授权：

将用户添加到本项目空间
对申请的对象进行用户授权

使用package授权
package授权：只需要操作package

包的创建者操作

操作	语句
创建package	create package <包名>
删除package	drop package <包名>
添加资源到package	add <项目客体> to package <包名> [with privileges privileges];
从package中去掉分享的对象	remove <项目客体> from package <包名>
允许其他项目空间使用此package	allow project <项目名> to install package <包名> [using label <数字>];
撤销项目空间对该package的使用许可	disallow project <项目名> to install package <包名>
查看已安装的package	show packages;
查看package的详细信息	describe package <包名>

包使用者的基本操作

操作	语句
安装package	install package <项目名.包名>
卸载package	uninstall package <项目名.包名>
查看已安装的package	show packages;
查看package的详细信息	describe package <包名>

被安装的package是独立的odps对象类型。若要访问package资源，需要Read权限，没有Read权限，需要向ProjectOwner或Admin申请、或Admin可以通过ACL授权或Policy授权机制来完成。

小王安装来自项目空间prj1的包pkg_wl并授权给小赵：

install package prj1.pkg_wl;
//显式授权read权限
grant read on package prj1.pkg_wl to user aliyun$xiaozhao@aliyun.com;

项目空间数据保护

张三将prj1项目空间中表customers读权限赋给李四，假设李四还有另一个项目空间prj2，那么李四就有很多种方法把这张表盗走：

SQL:create table prj2.Goult as select * from prj1.customers;
MR:通过MR将数据导出后，写入prj2去
导出：数据导出工具导出
PAI：数据间接导出
其他

解决办法：
开启数据保护机制，只让数据流入，不让数据流出
set ProjectProtection=true;
设置后，上述4中操作都将失效。

!!!
注意：
默认ProjectProtection不会被设置，需要手工开启
MaxCompute资源分享优于数据保护，即通过package分享的资源不受此限制

经过严格审查，发现李四将表导出到另一个prj2是符合规定的，但是已经设置了项目保护，怎么在不破坏目前保护机制的情况下，满足李四要求？

方法1：在设置项目保护的同时，附加一个例外策略：
set ProjectProtection=true with exception <策略文件>

文件：

{
	"Version": "1",
	"Statement":
	[{
		"Effect":"Allow",
		"Principal":"ALIYUN$lisi@aliyun.com",
		"Action":["odps:Select"],
		"Resource":"acs:odps:*:projects/prj1/tables/customers",
		"Condition":{
			"StringEquals":{
				"odps:TaskType":["DT", "SQL"]
			}
		}
	}]
}

方法2：将两个相关的项目空间设置为互信。则数据的流向不会被设为违规
add trustedproject=prj2

互信语句	含义
list trustedprojects	查看当前project中的所有TrustedProjects
add trustedproject	在当前project中添加一个TrustedProject
remove trustedproject	在当前project中移除一个TrustedProject

机密项目的Owner，很害怕数据外流，添加了ProjectProtection之后还需要？

是否添加互信
是否设置例外授权exception policy
分享有没有使用package数据分享

其他安全措施

当项目用户查询到MaxCompute项目由敏感数据的权限，但又不希望用户能看到完整的敏感数据信息时，可以对查询结果进行数据动态脱敏（加*号）。
MC提供了对SQL查询任务结果的动态脱敏,需要开通数据保护伞服务。脱敏仅仅对上层数据脱敏，不影响底层数据存储
支持的数据类型为：身份证、手机号、电子邮箱、银行卡、车牌号、IP地址、MAC地址

怎么开启数据动态脱敏？

开通数据保护伞服务

dataworks控制台–工作空间列表-数据开发-全部产品-数据治理-数据保护伞

开启动态脱敏功能

安装脱敏Package，项目Owner或管理员执行：install package aegis.aegis_package
登录数据保护伞控制台，为账号授权Project的数据脱敏权限

使用动态脱敏

Session级别：(临时)
set odps.output.field.formatter=segis:masking_v1;
set odps.isolation.session.enable=true;
Project级别（永久）
setproject odps.output.field.formatter=aegis:masking_v1;

备份与恢复

maxcompute提供了，系统会自动备份数据的历史版本，并保留默认24小时。比卖你因误操作丢失数据，默认开启，不依赖外部存储，免费的。
大于1天才收费

设置备份数据保留周期

setproject odps.timemachine.retention.days=days;
setproject;

查看备份数据

show history for tables;
show history for table <table_name>;
show history for table table_name (id'='xxxx);
show history for table table_name partition_spec;
show history for table table_name PARTITION('id'='xxxx');

恢复数据

restore table table_name (id'='xxxxx');
restore table table_name to LSN 'xxxx';
restore table table_name to LSN 'xxxx' as new_table_name;
restore table table_name PARTITION(id'='xxxx')[PARTITION(id'='xxxx')];
restore table table_name partition_spec1[partition_spec2 ]to LSN 'xxxx';
restore table table_name partition_spec1[partition_spec2 Jto LSN 'xxxx' as new_table_name;