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

2021-07-22 03:20:48

工欲善其数，必先利其器！

DataWorks OpenAPI现已正式发布，通过自带的100多个API 功能，可以快速进行批量操作与系统集成对接等，助您显著提升数据开发效率！

（DataWorks OpenAPI限企业版及以上版本使用）

DataWorks OpenAPI现开放7天免费试用，请使用钉钉扫码联系，提供试用账号的UID与Region即可。

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

3.1 资产总览： odps项目数量，表数量，分区表数量，表总大小

-- 3.1 资产总览： odps项目数量， 表数量，分区表数量，表总大小
SELECT  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'
;

QBI公开报表： https://bi.aliyuncs.com/token3rd/report/view.htm?id=d2825205-81d8-454b-a9a7-5cab1f706108&accessToken=7484554eecf457f4718ff4325e177c67
效果如图：

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

3.2 资产分布按工作空间划分、按所有者划分

-- 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'
GROUP BY c.project_id
        ,c.owner_yun_acct
ORDER BY c.project_id
;

QBI公开报表：https://bi.aliyuncs.com/token3rd/report/view.htm?id=1de34519-6a5b-4a22-8a31-dbfb531de322&accessToken=b3f154aae9f9c310c87bc95f1607fd2f
效果如图：

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

3.2(b) 资产分布按工作空间(名称)划分、按所有者(nick)划分

-- 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_id AND c.tenant_id = w.tenant_id
LEFT OUTER JOIN u_meta_hangzhou.raw_v_tenant_user_v1_1('${bizdate}') u
  ON c.owner_yun_acct = u.yun_account AND c.tenant_id = u.tenant_id
WHERE   c.catalog_name = 'odps'
GROUP BY w.project_name, u.nick
ORDER BY w.project_name
;