虽然在DataWorks工作台和数据分析台有估算费用功能,但是如果从其他地方查询(比如公司的可视化平台)是没办法使用估算功能看每一条数据费用的。基于此进行MaxCompute的SQL计费预估整理。
P.S.:费用预估是按照MaxCompute的SQL作业按量付费来进行计算和统计的。这里只看标准SQL作业和SpotSQL作业:
- 标准SQL作业:指使用按量付费标准版计算配额的SQL作业
- SpotSQL作业:指使用按量付费闲时版计算配额的SQL作业
执行失败的SQL作业不计费。
与存储类似,SQL作业的输入量是以压缩后的量计费。
账单和下载的数据记录中,数据量的单位是Byte,需要除以1024³换算为GB。
不支持预估外部表参与计算的SQL作业费用。
获取Information Schema服务
-
自2020年12月1日起,对于新创建的MaxCompute项目,MaxCompute默认提供Information
Schema相关的元数据视图,无需手工安装Information Schema权限包。 -
对于存量MaxCompute项目,在您开始使用Information Schema服务前,需要以项目所有者(Project Owner)或具备Super_Administrato管理角色的RAM用户身份安装Information
Schema权限包,获得访问项目元数据的权限。- 在DataWorks在对应项目下新建临时查询,然后输入
install package Information_Schema.systables;
,运行。 - 在MaxCompute客户端内输入
use 项目名
进入对应项目,然后输入install package Information_Schema.systables;
,运行。
- 在DataWorks在对应项目下新建临时查询,然后输入
如果统计多个MaxCompute项目的元数据,您需要分别对各个MaxCompute项目安装Information Schema权限包。然后把各个MaxCompute项目的元数据的备份数据插入到同一个表中做集中统计分析。
下载并备份元数据
- 在MaxCompute项目上创建元数据备份表,并定时将元数据写入备份表中。
直接使用元数据表也可以得到数据,但是如果要在别的地方(比如Superset)是无法使用的,所以跟着官方文档做元数据备份表是有必要的。
--project_name为MaxCompute项目名称。
CREATE TABLE IF NOT EXISTS qc_user_behavior.information_history(
task_catalog STRING COMMENT '任务所在项目名称',
task_schema STRING,
task_name STRING COMMENT '作业名称',
task_type STRING COMMENT '作业类型',
inst_id STRING COMMENT '作业实例ID',
`status` STRING COMMENT '数据采集瞬间的运行状态作业状态',
owner_id STRING COMMENT '作业提交人阿里云账号ID',
owner_name STRING COMMENT '作业提交人阿里云账号名称',
result STRING COMMENT '作业执行错误信息',
start_time DATETIME COMMENT '作业启动时间',
end_time DATETIME COMMENT '作业结束时间',
input_records BIGINT COMMENT '输入记录数',
output_records BIGINT COMMENT '输出记录数',
input_bytes BIGINT COMMENT '输入数据量',
output_bytes BIGINT COMMENT '输出数据量',
input_tables STRING COMMENT '输入表列表',
output_tables STRING COMMENT '输出表列表',
operation_text STRING COMMENT '作业语句',
signature STRING COMMENT '作业签名',
complexity DOUBLE COMMENT 'SQL作业复杂度',
cost_cpu DOUBLE COMMENT '作业CPU消耗',
cost_mem DOUBLE COMMENT '作业内存消耗',
settings STRING COMMENT '上层调度或用户传入的信息',
ds STRING COMMENT '按作业的end_time的UTC+8时区划分归档。'
)
PARTITIONED BY (dt STRING COMMENT '时间分区')
STORED AS ALIORC
TBLPROPERTIES ('comment'='元数据备份');
如果是追加插入数据的话分区可用可不用,ds一样可以过滤出需要的数据ds的格式为
'YYYYMMDD'
(ds=20231215)。
字段 | 类型 | 值 |
---|---|---|
task_catalog | STRING | 任务所在项目名称。 |
task_schema | STRING | |
task_name | STRING | 作业名称。 |
task_type | STRING | 作业类型。取值: SQL:SQL作业。 CUPID:Spark或Mars作业。。 SQLCost:SQL预估作业。。 SQLRT:查询加速SQL作业。。 LOT:MapReduce作业。。 PS:PAI的Parameter Server。。 AlgoTask:机器学习作业(无资源消耗和数据扫描相关信息)。 |
inst_id | STRING | 作业实例ID。 |
status | STRING | 数据采集瞬间的运行状态(非实时状态)作业状态。取值包括:Terminated:作业已执行结束。Failed:作业失败。Cancelled:作业被取消。 |
owner_id | STRING | 作业提交人阿里云账号ID。 |
owner_name | STRING | 作业提交人阿里云账号名称。 |
result | STRING | 作业执行错误信息。 |
start_time | DATATIME | 作业启动时间。 |
end_time | DATATIME | 作业结束时间。 |
input_records | BIGINT | 输入记录数。 |
output_records | BIGINT | 输出记录数。 |
input_bytes | BIGINT | 输入数据量,单位:字节(Byte)。 |
output_bytes | BIGINT | 输出数据量,单位:字节(Byte)。 |
input_tables | STRING | 输入表列表,仅SQL类型作业。 |
output_tables | STRING | 输出表列表,仅SQL类型作业。 |
operation_text | STRING | 作业语句,最大限制256 KB。 |
signature | STRING | 作业签名。 |
complexity | DOUBLE | SQL作业复杂度。 |
cost_cpu | DOUBLE | 作业CPU消耗(100表示1 core× 1 s 。例如:10 core运行5s,cost_cpu为10×100×5=5000)。 |
cost_mem | DOUBLE | 作业内存消耗,值为:MB×seconds。 |
settings | STRING | 上层调度或用户传入的信息,以JSON格式存储。包含字段:USERAGENT、BIZID、SKYNET_ID和SKYNET_NODENAME等。 |
ds | STRING | 数据归档日期。按作业的end_time 的UTC+8时区划分归档。 |
- 导入数据
insert into table <project_name>.information_history select * from information_schema.tasks_history where ds ='${yyyymmdd}';
information_schema.tasks_history
是项目租户级别的元数据表。
- 表内数据为各个项目内已完成的作业历史。
- 分区表(查询的时候要加
ds
,分区格式为'yyyymmdd'
),保留近14天数据。- 与在线数据存在一定延迟,延迟时间为5分钟左右。
查看费用及耗时作业
information_schema.tasks_history
表中有一个settings字段,里面会记录上层调度或用户传入的信息,以JSON格式存储。包含的具体信息有:USERAGENT、BIZID、SKYNET_ID和SKYNET_NODENAME等。如果需要计算TOPN费用账号及耗时作业的话需要setting字段,只查看单条SQL费用不需要。
1. 基础查看单条SQL费用
单条SQL费用计算公式(摘抄自官方文档):
task_type = 'SQL' -> input_bytes/1024/1024/1024 * complexity * 0.3
task_type = 'SQLRT' -> input_bytes/1024/1024/1024 * complexity * 0.3
task_type = 'CUPID' and status='Terminated' -> cost_cpu/100/3600 * 0.66
根据上面列comment可知input_bytes为输入数据量,单位:字节(Byte);cost_cpu为作业CPU消耗(100表示1 core× 1 s);complexity为复杂度。
代码:
--开启2.0数据类型开关。
set odps.sql.decimal.odps2=true;
SELECT
inst_id,
owner_name,
task_type,
input_bytes,
cost_cpu,
status,
CASE
WHEN task_type = 'SQL' THEN cast(
input_bytes / 1024 / 1024 / 1024 * complexity * 0.3 AS DECIMAL(18, 5)
)
WHEN task_type = 'SQLRT' THEN cast(
input_bytes / 1024 / 1024 / 1024 * complexity * 0.3 AS DECIMAL(18, 5)
)
WHEN task_type = 'CUPID'
AND status = 'Terminated' THEN cast(cost_cpu / 100 / 3600 * 0.66 AS DECIMAL(18, 5))
ELSE 0
END cost_sum,
settings,
FROM
information_history
WHERE
ds = '${datetime1}'
[1] 不开启2.0数据类型开关的话不要用DECIMAL(18, 5)
[2] '${datetime1}'
格式为yyyymmdd
2.统计账号费用的明细
settings中的SKYNET_ONDUTY内容是MaxCompute用户的user_id,可以根据user_id来得到该条SQL是谁发出。user_id对应的用户名可以在权限管理里获取到。
1.创建一张RAM用户明细表user_ram,记录需要统计的账号及账号ID。
create table if not exists <project_name>.user_ram
(
user_id STRING
,user_name STRING
);
2.创建一张统计账号费用的明细表cost_topn,记录TOPN费用账号明细
create table if not exists <project_name>.cost_topn
(
cost_sum DECIMAL(38,5)
,task_owner STRING
)
partitioned by
(
ds STRING
);
3.将数据写入cost_topn
--开启2.0数据类型开关。
SET odps.sql.decimal.odps2 = TRUE;
--将元数据写入cost_topn表。user_id为账号ID。您可以在个人信息页面查看账号ID。
INSERT INTO TABLE cost_topn PARTITION (ds = '${datetime1}')
SELECT
nvl(cost_sum, 0) cost_sum,
CASE WHEN a.task_owner = '设置好的user_id1'
OR a.task_owner = '设置好的user_id2'
OR a.task_owner = '设置好的user_id3' THEN b.user_name
ELSE a.task_owner
END task_owner
FROM
(
SELECT
inst_id,
owner_name,
task_type,
a.input_bytes,
a.cost_cpu,
a.status,
CASE WHEN a.task_type = 'SQL' THEN cast(
a.input_bytes / 1024 / 1024 / 1024 * a.complexity * 0.3 AS DECIMAL(18, 5)
)
WHEN a.task_type = 'SQLRT' THEN cast(
a.input_bytes / 1024 / 1024 / 1024 * a.complexity * 0.3 AS DECIMAL(18, 5)
)
WHEN a.task_type = 'CUPID'
AND a.status = 'Terminated' THEN cast(a.cost_cpu / 100 / 3600 * 0.66 AS DECIMAL(18, 5))
ELSE 0
END cost_sum,
a.settings,
get_json_object(settings, "$.SKYNET_ONDUTY") owner,
CASE WHEN get_json_object(a.settings, "$.SKYNET_ONDUTY") IS NULL THEN owner_name
ELSE get_json_object(a.settings, "$.SKYNET_ONDUTY")
END task_owner
FROM
information_history
WHERE
ds = '${datetime1}'
) a
LEFT JOIN user_ram b ON a.task_owner = b.user_id;
3.统计耗时作业的明细
1.建一张统计耗时作业的明细表time_topn,记录TOPN耗时作业明细
create table if not exists time_topn
(
inst_id STRING
,cost_time BIGINT
,task_owner STRING
)
partitioned by
(
ds STRING
);
2.将数据写入time_topn
INSERT INTO TABLE time_topn PARTITION(ds = '${datetime1}')
SELECT
inst_id,
cost_time,
CASE WHEN a.task_owner = '设置好的user_id1'
OR a.task_owner = '设置好的user_id2'
OR a.task_owner = '设置好的user_id3' THEN b.user_name
ELSE a.task_owner
END task_owner
FROM
(
SELECT
inst_id,
task_type,
status,
datediff(a.end_time, a.start_time, 'ss') AS cost_time,
CASE WHEN get_json_object(a.settings, "$.SKYNET_ONDUTY") IS NULL THEN owner_name
ELSE get_json_object(a.settings, "$.SKYNET_ONDUTY")
END task_owner
FROM
information_history a
WHERE
ds = '${datetime1}'
) a
LEFT JOIN user_ram b ON a.task_owner = b.user_id;
后续可能会更新整理(画饼中)
MaxCompute文档参考地址
[1] 统计MaxCompute TOPN费用账号及耗时作业
[2] 租户级别Information Schema
[3]MaxCompute技术人背后的故事:从ApacheORC到AliORC
[4]计算费用(按量付费)