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

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

　

--------------------------------------------------------

# -*- 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)