Link to Original Document

 ℹ️将当阳的数据从宜昌市级各单位业务系统中分离到我们自己手上是当阳数据治理的基础性工作。本次数据分区工作从到宜昌学习开始，截止到3月底，历时5个月。目前已分得数据库表220余张，总计220G数据量。

一、MaxCompute平台简介

 宜昌城市大脑运营管理公司（以下简称公司）使用阿里的云原生大数据计算服务解决方案来对全市政务数据进行管理应用。本次任务中主要使用到其数据库开发能力，任务调度能力与资源目录能力。

1. 数据治理项目架构

 在公司的MaxCompute里，数据治理任务有5个项目，分别是shujuzhili_zhengwu_stg，shujuzhili_zhengwu_ods，shujuzhili_zhengwu_dwd，shujuzhili_zhengwu_dws，shujuzhili_zhengwu_adm。

 每一个项目都是一个命名空间，该项目下的数据表与其他项目相互独立。为了适应项目开发流程，5个项目被人为赋予从上到下的层级意义：stg项目存储未经加工的原始数据副本，ods项目存储经由处理过的stg数据库的数据表，dwd及dws项目存储专题与场景化的治理数据，adm存储可视化项目数据。在各项目中，底层项目为上层项目提供服务。

 各县市区数据中心在MaxCompute中也是以项目存在，比如当阳分中心为dys_420582。

 在本次任务中，只涉及到stg与ods项目，根据公司的设计，各县市区数据的处理过程集中设置在ods项目中，即本次任务是：将stg项目中的数据表按地区划分导入到ods项目中，然后同步到各县市区的数据中心项目中。

2. 功能简介

• 数据库开发能力

 MaxCompute原生数据库为ODPS数据库，实现了存储、调度、元数据管理上的一体化架构融合，支撑上述各类数据的高效处理。本次使用到了该数据库的表创建，修改，更新等基础能力。

• 任务调度

 任务调度包含有2个重要应用，为脚本执行和数据同步。其主要特点是自动化地按设定的频率执行任务。显然，后期运维工作重点就在于确保调度的平稳准确地执行。

 脚本执行。定制化的功能实现需要脚本的编写执行。本任务中分表利用sql脚本的定时执行实现。

 数据同步。为了将不同源的数据进行自动化地同步，该平台提供了2种同步模式：实时同步与离线同步。公司使用离线同步模式将外部数据库表按设定的时间频率同步到stg中。由于大多数业务对时间不敏感，因此采用每日全量更新的方式。

• 资源目录制作

 对于项目中的数据资产，平台提供了资源目录生成的可视化操作界面，最终将获得的数据表以web页面展示出来。

二、数据流向分析及任务明确

 公司已向云平台接入各市直单位业务系统若干，其副本被定期拉取、存储在云平台的stg项目中。归属各县市区的数据混合在这些数据表副本中，经过在项目ods中进行分区操作后，数据被定期更新到各县市区数据中心中。

1. 表数据的各阶段的示例

 在分表过程中，数据表在不同项目中有不同的形态。一种适宜的展示如下。该过程采用离线同步，每日全量拉取数据表数据。

2. 命名规范

 大量数据有组织地进行治理，数据标识的规范尤为重要。数据表名称根据所属单位，业务系统，所在项目与更新频率进行编制。具体规则为：项目简称_单位_业务系统_更新频率。项目简称取为stg，ods，dws，dwd，adm；单位与业务系统使用拼音首字母表示；更新频率中df表示每日更新，di表示每分钟更新。具体示例可参考上图。

三、我的工作

 主要工作量在于分区脚本的编写与调试，涉及到大量机械化操作与团队协作工作。亮点在于预先抓取数据表的建表语句，编写python程序解析了1400余条该类语句，生成了所有的分表模版sql脚本，于是可以将主要精力聚焦在地区字段的确定上。其次，共享文档的使用极大提升了多人协作的效率。

1. 预分表

 预则立，不预则废。首先对一些数据表进行了试分区，碰到了一系列问题。

• 有大量的人工机械操作。分表sql脚本格式基本固定，好处是脚本编写不费脑子，但是需要大量的复制粘贴工作。以下是一个典型的分表脚本。

--odps sql 
--********************************************************************--
--author:尹思璐
--create time:2023-02-14 20:32:45
--********************************************************************--
--创建ods分区表
CREATE TABLE IF NOT EXISTS shujuzhili_zhengwu_ods.ods_sjyj_xjxx_ycxj_jtcy_dq_df 
(
...
...
) COMMENT "表名"
PARTITIONED BY 
(
    dq STRING
  , dt STRING
)
;

--将stg表中数据导入到ods分区表中
INSERT OVERWRITE TABLE shujuzhili_zhengwu_ods.ods_sjyj_xjxx_ycxj_jtcy_dq_df 
PARTITION(dt, dq)
SELECT  ...
        ,CASE    WHEN 地区标识 IN ('420502','420503','420504','420505','420506',
                                  '420507','420525','420526','420527','420528',
                                  '420529','420581','420582','420583') 
                 THEN 地区标识 
                 ELSE '420500' 
         END dq
FROM   shujuzhili_zhengwu_stg.stg_sjyj_xjxx_ycxj_jtcy
;

 分区脚本分为2部分，先是创建ods项目下的分区表，然后是将相应stg表中的数据导入到刚创建的数据表中。

 其中除了23到29行文本，所有信息可以直接从平台的数据地图页面复制得到。对于几百张数据表，频繁切换页面获取数据的操作工作量极大，因此有必要考虑其他解决方案。

 考虑到平台导出的数据表创建语句格式固定，包含所有分表脚本语句要素，因此可以自行编程序批量解析生成脚本模版。使用模版后，能够将精力聚焦于地区字段的分析处理上。模版示例如下：

-- 市教育局
-- 学籍信息
-- stg_sjyj_xjxx_ycxj_jtcy
-- ods_sjyj_xjxx_ycxj_jtcy_dq_df
-- 宜昌学籍家庭成员信息 20230130文本导入
-- made by 尹思璐@当阳
CREATE TABLE IF NOT EXISTS shujuzhili_zhengwu_ods.ods_sjyj_xjxx_ycxj_jtcy_dq_df (
  xs_jbxx_id STRING COMMENT '学生id',
  sfjhrm STRING COMMENT '是否监护人',
  cyxm STRING COMMENT '成员姓名',
  xzz STRING COMMENT '现住址',
  gzdw STRING COMMENT '工作单位',
  csny STRING COMMENT '出生年月',
  mzm STRING COMMENT '民族编码',
  lxdh STRING COMMENT '联系电话',
  sfzjh STRING COMMENT '身份证件号',
  hkszd STRING COMMENT '户口所在地',
  sfzjlxm STRING COMMENT '身份证件类型码',
  gxm STRING COMMENT '与学生关系码'
) COMMENT "宜昌学籍家庭成员信息 20230130文本导入"
PARTITIONED BY 
(
    dt STRING
    ,dq STRING
)
;

INSERT OVERWRITE TABLE shujuzhili_zhengwu_ods.ods_sjyj_xjxx_ycxj_jtcy_dq_df PARTITION(dt='${bdp.system.bizdate}', dq)
SELECT
a.xs_jbxx_id,a.sfjhrm,a.cyxm,a.xzz,a.gzdw,a.csny,a.mzm,a.lxdh,a.sfzjh,a.hkszd,a.sfzjlxm,a.gxm
        ,CASE    WHEN SUBSTR(待确认,1,6) IN ('420502','420503','420504','420505','420506',
                                           '420507','420525','420526','420527','420528',
                                          '420529','420581','420582','420583') 
                     THEN SUBSTR(待确认,1,6) 
                 ELSE '420500' 
         END dq
FROM    (SELECT * from shujuzhili_zhengwu_stg.stg_sjyj_xjxx_ycxj_jtcy
        WHERE   dt = max_pt("shujuzhili_zhengwu_stg.stg_sjyj_xjxx_ycxj_jtcy")
        ) AS a
;

 经过解析处理获得全部分表脚本模版文件，其目录（部分）结构如下：

• 多人协作问题。分表任务涉及到2种角色：开发与审核，也涉及到2处工作地点：当阳与宜昌，因此对于任务推进的过程把控需要有一个同步机制。钉钉在线文档很好的解决了这个问题，将待分数据表列表共享到每个账号中，将各方的进度信息集中到文档中，实现了任务分配协调的共享机制。
• 流程规范问题。多人协作时也发现了一个问题，个人的权责不清将导致任务执行混乱，质量管理难以推行。经过与李俊的沟通，制定了操作流程。

2. 写方案，准备物料，培训

• 根据前期经验，编写任务方案，交由李俊审核。

 数据分区方案

• 物料的准备。

 一是待分表详单，由李俊提供。

 宜昌数据中体数据目录梳理2023.xlsx

 二是所有分表模版制作，由我提供。

 Archive.zip

 其中，编写的解析文件如下。

 main.py

 三是培训，通过钉钉会议在线给各位同事进行了任务分配，操作指导。

 四是排期。

3. 难点--确定地区字段

 我这边的工作，难点在于地区字段的确定，需要打开每一个表进行判断。根据李俊的要求，数据产出机构是数据的归属地判断标准，意味着不能简单使用身份证号进行分表操作。以下有几个典型案例。

• 数据表中含有机构归属地信息

 对于字段中含有数据归属地的，直接取表中该字段进行处理，对应地生成6位国标地区码用作分区字段dq。

• 地区字段需要其他关联字段确定

 多数数据表并不含属地字段，需要关联其他表取得该条记录的归属地。

 上面示例中，为了得到油枪数据的归属地，关联了其他2张表，最终利用油站表中的地址信息分得了数据。

四、经验总结

 本次分表任务是一个工程上的问题，更是一个项目管理上的问题。即使经过了比较周密的安排，整个实施过程也显得磕磕碰碰。外部的干扰（生病，人员更换）与内部需求的变更（宜昌要求全市一盘棋）对整个实施过程的推进造成了不利影响。

 技术上经验的缺乏也增加了任务完成的时间。对于平台的使用，还需进一步研读操作指南，加深对各组件的理解。

 最后，人员未能有效利用，需要进一步长期的培养此项能力。