【深入 MaxCompute】人力家：借助 Information Schema 合理治理费用

set odps.namespace.schema=true;set odps.sql.decimal.odps2=true;
create table if not exists ads_project_cost_pay_di(    env_type  string comment '环境类型'    ,cost_type string comment '消费类型'    ,inst_id string comment  '唯一id，作业id'    ,owner_name string comment  '作业所属人'    ,task_type string  comment  '作业类型  SQL：SQL作业 CUPID：Spark或Mars作业 SQLCost：SQL预估作业 SQLRT：查询加速SQL作业 LOT：MapReduce作业 PS：PAI的Parameter Server AlgoTask：机器学习作业'    ,input_records string comment  '作业输入的records数目'    ,output_records string comment '作业输出的records数目'    ,input_bytes string comment '实际扫描的数据量，与Logview相同。'    ,output_bytes string comment '输出字节数。'    ,status string comment '数据采集瞬间的运行状态（非实时状态）。包含以下状态：Terminated：作业已执行结束。Failed：作业失败。 Cancelled：作业被取消。'    ,cost_pay DECIMAL(18,5) comment '费用 单位元'    ,complexity string  comment  '任务复杂度'    ,settings string comment '上层调度或用户传入的信息，以JSON格式存储。包含字段：USERAGENT、BIZID、SKYNET_ID和SKYNET_NODENAME。'    ,sql_script string comment 'sql 代码'    ,start_time string comment '开始时间'    ,end_time string comment '结束时间'        ,data_collection string comment  'quickbi数据集')comment 'odps 费用 明细'partitioned by (ds string comment '分区');
insert overwrite table ads_project_cost_pay_di partition(ds=${bizdate})select  case when task_catalog = 'renlijia_ng' then '生产'              when task_catalog = 'renlijia_ng_dev' then '测试'             else  task_catalog         end as   env_type        ,if(regexp_count(settings,'quickbi')>0,'quickbi',task_catalog)cost_type        ,inst_id        ,owner_name        ,task_type        ,input_records        ,output_records        ,input_bytes        ,output_bytes        ,status        ,nvl(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,0) cost_pay        ,complexity         ,settings        ,operation_text sql_script        ,start_time        ,end_time        ,regexp_extract(operation_text,'(?<=quickbi=).*?(?==quickbi)',0)data_collectionfrom  SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY where ds=${bizdate};

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;

‘quickbi=xxx数据集=quickbi’ as 数据集自定义字段

regexp_extract(operation_text,'(?<=quickbi=).*?(?==quickbi)',0)