DataWorks OpenAPI实现数据资产分布和排行分析

-- 3.1 资产总览： odps项目数量， 表数量，分区表数量，表总大小SELECTCOUNT(DISTINCT c.database_name)AS project_count
,COUNT(1)AS table_count
,SUM(CASE WHEN c.is_partition_table THEN 1 ELSE 0 END)AS partitioned_table_count
,SUM(COALESCE(c.size,0))AS table_size_bytes
FROM    u_meta_hangzhou.rpt_v_meta_ind_table_core_v1_1('${bizdate}') c
WHERE   c.catalog_name='odps';

-- 3.2 资产分布 按工作空间划分、按所有者划分SELECT  c.project_id,c.owner_yun_acct,COUNT(DISTINCT c.database_name)AS project_count
,COUNT(1)AS table_count
,SUM(CASE WHEN c.is_partition_table THEN 1 ELSE 0 END)AS partitioned_table_count
,SUM(COALESCE(c.size,0))AS table_size_bytes
FROM    u_meta_hangzhou.rpt_v_meta_ind_table_core_v1_1('${bizdate}') c
WHERE   c.catalog_name='odps'GROUPBY c.project_id,c.owner_yun_acctORDERBY c.project_id;

-- 3.2(b) 资产分布 按工作空间(名称)划分、按所有者(nick name)划分SELECT  w.project_name,u.nick,COUNT(DISTINCT c.database_name)AS project_count
,COUNT(1)AS table_count
,SUM(CASE WHEN c.is_partition_table THEN 1 ELSE 0 END)AS partitioned_table_count
,SUM(COALESCE(c.size,0))AS table_size_bytes
FROM    u_meta_hangzhou.rpt_v_meta_ind_table_core_v1_1('${bizdate}') c
LEFT OUTER JOIN u_meta_hangzhou.raw_v_tenant_workspace_v1_1('${bizdate}') w 
ON c.project_id= w.project_idAND c.tenant_id= w.tenant_idLEFT OUTER JOIN u_meta_hangzhou.raw_v_tenant_user_v1_1('${bizdate}') u
ON c.owner_yun_acct= u.yun_accountAND c.tenant_id= u.tenant_idWHERE   c.catalog_name='odps'GROUPBY w.project_name, u.nickORDERBY w.project_name;