使用 PyODPS 统计 ODPS 空间内的表数据信息

使用 python 统计 ODPS 空间内的表和数据情况。

创建统计表

CREATE TABLE `table_statistics` (
	`table_name` string COMMENT '表名',
	`partition_name` string COMMENT '最新分区',
	`chinese_name` string COMMENT '中文表名',
	`column_count` bigint COMMENT '字段数量',
	`column_comment_null_count` bigint COMMENT '字段注释缺失数量',
	`pt_count` bigint COMMENT '分区数量',
	`data_count` bigint COMMENT '最新分区数据量'
) 
COMMENT '数据情况统计'
 PARTITIONED BY (dt string)
LIFECYCLE 180;

部署 PyODPS 任务

# -*- coding: utf-8 -*-
from odps import ODPS
import datetime

dt_str = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y%m%d')
rid_list = []
wd = []
for t in o.list_tables():
    table_name = t.name
    # 过滤非业务表
    if table_name[:3] not in ('stg', 'dim'):
        continue
    # 过滤利旧数据表
    if table_name in rid_list:
        continue
    chinese_name = t.comment.encode('utf-8')
    cs = [c for c in t.schema.columns]
    column_count = len(cs)
    column_comment_null_count = 0
    for c in cs:
        if c.comment == '' or c.comment == 'null':
            column_comment_null_count += 1
    cnt_sql = ''
    new_pt = ''
    if t.schema.partitions:
        pi = t.iterate_partitions()
        ps = [p for p in pi]
        pt_count = len(ps)
        if len(ps) == 0:
            continue
        new_pt = str(ps[-1])
        if ',' in new_pt: # 多级分区的情况
            new_pt = new_pt.replace(',', ' and ')
        cnt_sql = "select count(1) from %s where %s" % (table_name, new_pt)
        print(cnt_sql)
    else:
    	pt_count = 1
        cnt_sql = "select count(1) from %s " % (table_name)
    with o.execute_sql(cnt_sql).open_reader() as reader:
        data_count = reader[0][0]
        wd.append([table_name, str(new_pt), chinese_name, column_count, column_comment_null_count, 
                   pt_count, data_count])

sta_table = o.get_table("table_statistics")
sta_table.delete_partition('dt=%s'%dt_str, if_exists=True)
with sta_table.open_writer(partition=('dt=%s'%dt_str), create_partition=True) as writer:
    writer.write(wd)