开发者学堂课程【SaaS 模式云数据仓库系列课程 —— 2021数仓必修课：MaxCamputeInfomationSchema 详解】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/55/detail/1049
MaxCamputeInfomationSchema 详解
内容简介
一、InfomationSchema 简介
二、InfomationSchema 安装使用
三、InformationSchema 使用限制
四、InfomationSchema 常用场景
五、InfomationSchema 使用建议
一、InfomationSchema 简介
背景
MaxCompute 是一种快速、完全托管的 PB/EB 级数据仓库解决方案。
用户结构化的数据在 MaxCompute 中以行-列表的的形式存储，每天
有干万级别的作业对这些表进行操作，同时有丰富完整的权限控制体
系对这些资源访问进行约束控制。
为了满足这些海量对象以及对象之间关系的管理、分析需求，
MaxCompute 提供了开放的元数据服务 InformationSchema 。通过
informationSchema 用户可以对 MaxCompute 中管理的对象使用 SQL
进行全局分析，可以分析用户数据访问行为等，也可以利用这些数据
构建自己的内部的元数据管理系统。
1.MaxCompute的InformationSchema 提供了项目元数据及使用历史
数据等信息。
2.在ANSISQL-92的InformationSchema 基础上，，添加了面向
MaxCompute 服务特有的字段及视图。
3.包含了22张视图，其中20张是基础对象及对象关系视图，2张是
历史记录数据。
（1）产出方式周期性离线+实时在线结合的方式进行数据产出。
（2）产出流程涉及多个系统，数据是存储在 ODPS 系统上确保数据可
靠性。
（3）通过 ODPSpackage+view 的机制提供给用户，视图是 project
级别的
（4）information-schema 数据安全依托 ODPS 已有的成熟安全机制
（5）数据覆盖面广，对象关系进行展开，使用方便简单。
数据产出
·使用周期性任务从在线元数据定期 dump，进行清洗以及复杂的关
系展开
·在线实时流作业L侦听服务 worker 发出日志事件流进行ETL，定
期 dump 到 oDPS 数据表
InfomationSchema 简介
Package+View 机制
●什么是 view
基于 SQL 语句的结果集的可视化的表，包含行和列就像一个真实
的表。通过视图我们可以向用户选择性、稳定地提供数据，隐藏复杂
的实现细节。
●什么是 PACKAGE
Package 是一种跨项目空间共享数据及资源的机制，我们可以把资
源放到 package 中打包一起共享给其他 project 使用，相当于功能包。
●为什么要用 PACKAGE
●共享简单，管理方便。不需要逐张 view 进行授权，后续对
informationSchema 内容不需要额外授权操作。
●安全，不需要管理庞大的用户，只需要对 project 进行授权。只需
要授权 project 安装后，由 projectowner 管理 project 内部谁能访
问。
视图信息大概可以分为如图四类，其中只有历史数据表是带有分
区字段 ds ，其他都为非分区表
二、InfomationSchema 安装使用
使用 odpscmd 或者 dataworks，注意需要 PROJECT
1.OWNER/SUPERADMIN 权限的执行
installpackageinformation_schema.systables;
2.执行下面命令如果能看到右图信息就说明安装成功了
descpackageinformation_schema.systables;
如果需要 project 里面其他非 projectowner、superadmin 能使用
information_schema，可以通过 ACL 或者 policy 授权
强烈建议∶使用 information_schema 时候关闭 SQL1.0回退set
odps.sql.planner.mode=lot;
第一次使用时候由于字段不对，类型不对或者没有目标表权限等等会
出现类似如下错误
FAlLED:No'Read'permissionforentity
"projects/information_schema/registration/functions/udf
unixtime2str':AuthorizationFailed[4002],Youdon'texistin
projectinformation_schema.Context
ID:353dbeb8-9798-4a3f-8c9f-c5aa0faed66f.--->Tips:
CurrentProject:lightning;
Pricipal:ALIYUN$shuijia_demo@aliyun-inner.com;No
permission'odps:Read'onresouce
acs:odps:*:projects/information_schema/registration/functio
ns/udf_unixtime2str
从query可能看不出来什么，有时候用户就是 insertoverwrite
tablexxxselect*frominformation_schema.tasks_history
Q∶为什么只是访问 information_schema 所有表出错
A∶因为部分视图使用了 udf，也有用户部分表的 schema 是对的，比
如只有 history 表带有分区 ds 字段，其他表是没有的
三、InformationSchema 使用限制
1.InformationSchema 提供的是当前项目的元数据视图，不支持跨
项目的元数据访问。如果需要对多个项目的元数据进行统一查询、分
析，需要分别获取各个项目中的元数据并整合在一起进行跨项目元数
据分析。
2.元数据系统表目前提供视图有一定时延，对元数据时效性要求较高
的应用，建议使用 SDK/CLI 直接获取指定对象的元数据。
3.历史数据视图目前只提供14天数据，其他数据为最近产出副本，
如果需要长期分析需要定期导出到用户空间
4.视图数据不支持 tunnel 等拷贝，需要执行 odpssql
四、InfomationSchema 常用场景
TABLES、PARTITIONS
Q∶最近存储量上涨，查看具体哪些表占了大头
SELECTtable_name,data_lengthFROM
(SELECTtable_name,SUM(data_length)asdata_lengthFROM
information_schema.partitionsGROUPBYtable_nameUNIONALL
SELECTtable_name,data_lengthFROM
information_schema.tables)t
ORDERBYdata_lengthDESCLIMIT5;
TABLES、PARTITIONS
类似场景还有
●谁占的存储量最大
●分区增长最快
●如果是做了定期 snapshot ，还可以用来分析表存储变化趋势
TASKS_HISTORY
Q∶查看 dataworks 任务
selectds,inst_id,cost_cpufrom
information_schema.tasks_historywhere
get_json_object(settings,'SKYNET_NODENAME')='parse_raw_Log'
;
Q∶提交作业量
selectowner_id,count(*)ascntfrom
information_schema.tasks_historywhereds='20200301'groupby
owner_idorderbycntdesclimit10;如果是标准模式，可以通过
get_json_object（settings，
TASKS_HISTORY
Q∶每个人跑了多少钱，谁花费最多
SELECTowner_name,sum(input_bytes/1000/1000/1000*complexity
*0.3)ascostFROM
information_schema.tasks_historyWHEREds='20200206'GROUPBY
owner_nameORDERBYcostDESCLIMIT5;
Q∶谁跑了大作业
SELECT*FROMinformation_schema.tasks_historyWHERE
ds='20200206'ORDERBYcost_cpuDESC,cost_memDESCLIMIT5；”
TASKS_HISTORY
类似场景还有
·预付费用户最近报整体变慢了，可以查下作业量，作业消耗最近
的变化趋势
·dataworks 调度作业的整体情况分析，比如按任务 id 做分组按运
行时间排序查看是否有作业执行时间逐渐增长
·通过 SQLquery 来查看哪些任务访问了某张表
·通过 input/outputtable 信息构建表的血缘图
TUNNELS_HISTORY
·查看用户下载量，估计费用消耗
·谁下载了敏感数据，下载了多少，下载到哪里去了 etc
·每天 project 有多少上传下载量
selectsum(data_size)asbytes,sum(length)asrecord_count
from
information_schema.tunnels_historywhereds='20200301'and
object_name='shop_orders'andoperate_type='DOWNLOADLOG'and
client_ip!='202.10.1.1'orderbybyteslimit5:
schema_privileges
Q∶谁能在我的项目中创建表，提交作业 etc
SELECTgrantee,user_id,privilege_typeFROM
information_schema.schema_privilegesWHEREprivilege_type
");
IN('all','createinstance'
table_privileges
Q∶谁有权限读取某张表数据
select*frominformation_schema.table_privilegeswhere
table_name='m_task'andprivilege_typein('all',
'select','download');
column_labels
Q∶根据一些特征可以确定一些列是敏感列，比如 phone ， addressetc
确保这些别是否被正确设置访问门槛
selecttable_name,column_namewherecolumn_namelike
'%address%'
andlabel_level<"2";
column_label_grants
Q∶对敏感表字段的授权是否有不合预期的账户
select*frominformation_schema.column_Label_grantswhere
table_name='shop_orders'andcolumn_namein
('price','card_no')andlabel_level>;
五、InfomationSchema 使用建议
●禁止掉 SQL1.0回退 setodps.sql.planner.mode=lot;
●adhoc查询历史表使用时候加上分区限制避免扫描所有的分区，如
果是查询昨天以及之前的数据会更，快，因为历史表是历史数据和当
天实时数据的聚合，历史数据会定期进行 rangecluster，进行过滤
时候扫描量更小
●定期 dump 时候不要将时间刚刚好设置为0点，因为产出时间是有
一定的延迟，可以考虑设置晚几十分钟。
●多个 project 数据时候可以通过设置二级分区（stringds，string
project），每个项目 insertoverwrite 到自己分区，查询时候可以
通过指定ds查询主账号下 project 数据
●尽量不要用 select*， 类似 insertintotablexxxxselect*from
information_schema.tables ，因为视图可能会在尾部进行字段扩展。
●使用权限表时候查询用户权限时候注意不要遗漏"all"，对应权限
*，比如查询用户 drop 权限时候，过滤列表除了 drop 还要含有 all
