阿里云爬坑日志（三）：在 Dataworks 数据建模中导入 Hologres 中的表字段

在 Dataworks 数据建模功能中，可以通过导入数据库表添加表字段。从功能上看，可以导入 MaxCompute, Hologres 等数据库中的表字段。但实际使用中发现，与 Hologres 的兼容性太差，看起来很美，但实际上是残的，几乎无法使用。阿里云连增加的产品都做不好集成，真不知道产品是为谁服务，难道又是一个面向KPI（或者流行的 OKR）的产品？虽然被恶心了一把，但还得干活吃饭，只好找一些迂回的方法挽救工作效率了。

导入 Hologres 数据库表字段的缺陷 🔗

从 Hologres 中导入字段时，最大的问题是数据类型的映射。当我咨询 Dataworks 的技术服务人员，在 Hologres 中应该使用哪种类型保存字符串时，对方的回复是使用 TEXT；还让我不考虑性能问题，如果遇到问题再优化。但是当我在 Dataworks 维度建模中将 TEXT 类型的字段导入模型时，会被识别为 Complex 类型（复杂类型）。此时是无法保存模型的，必须将复杂类型手动修改为下拉框中的类型才可以。

【疯狂吐槽】2024年初，字段类型下拉框的每一个数据类型项是“中文(大写英文)”的形式。当我在字段下拉框中尝试搜索字段类型时，发现无法进行搜索，只能用滑动鼠标选中期望的类型。我以为这个下拉框只支持前缀搜索，于是给 Dataworks 提了一个反馈。Dataworks 人员验证后告诉我：“不支持小写字母搜索”。这让人挺意外的，居然在搜索字段类型的的时候还区分大小写？无语。

【疯狂吐槽】再过了几个月，我又发现，这个数据类型下拉框里有一项“浮点数(DECIMAL)”。鉴于我有限的认知，浮点数指的是一种近似值，而DECIMAL是精确值。这中英文不匹配啊。不知道是产品粗糙，还是阿里云自创了一套知识体系，又让人迷惑。然后又给阿里云的人员提了一下；对方也没有回复这么标记的缘由。过了一段时间，这个下拉框更新了，没有了中文名称，只有英文名称，而且终于不区分大小写了。看来，这种小改动阿里云还是能接受的，虽然可能就是该一行 Javascript 或者 Typescript 吧。但总算是倾听了客户的声音。

目前发现的从 Hologres 导入的复杂类型包括：

• TIMESTAMP WITHOUT TIME ZONE
• TIMESTAMP WITH TIME ZONE
• INTEGER
• DECIMAL(x,y)
• NUMERIC

也许有人认为手动改一下类型不就好了，但在实际中，尤其是企业系统里，数据库表的字段可能很多，几十上百个都有有可能，而且不同类型的字段穿插排列。因此在 Dataworks 维度建模的页面进行修改的话，非常繁琐和容易出错，是不现实的。

有个同事的做法是，把导入的字段复制到 Excel 文件中，然后批量替换，然后再复制粘贴会 Dataworks 维度建模页面。但这还是有些繁琐。而且还要打开 Excel、Numbers，或者在 Google Sheet上打开一个文件。至少对于我来说，macOS 上的 Excel 真的难用，但没有安装 Numbers，要用 Google Sheet 的话，还得换个网络，真麻烦。或许在钉钉新建一个表格处理，也不是不行。

我在内心是极度排斥这种手动步骤的，但因为我没有参与过具体的开发，也没有特意去关注。但当我开始完成数据建模工作的时候，看到这么愚蠢的体验，我的血压又升高了。

解决方法 🔗

既然在真实的复杂场景中需要进行繁琐地手动修复，那么尝试一下用“代码模式”吧。但是，如何生成“代码模式”中的“FML快捷模式”语句呢？也许第一反应是写一个脚本去处理，但有一个巧妙的方法，就是使用 SQL 语句生成语句，然后复制粘贴到编辑框中。

比如，可使用如下 SQL 语句实现了生成 FML 语句：

WITH raw AS (
    SELECT
        table_schema,
        table_name,
        column_name,
        data_type,
        col_description((table_schema || '.' || table_name)::regclass::oid, ordinal_position) AS column_comment,
        ordinal_position
    FROM
        information_schema.columns
    WHERE
        table_schema NOT IN ('information_schema', 'pg_catalog')
        AND table_schema = 'schema name' --- 在这里修改schema名
        AND table_name = 'table name' -- 在这里修改表名
    ORDER BY
        table_schema,
        table_name,
        ordinal_position
),
transformed AS (
    SELECT
        column_name,
        CASE WHEN UPPER(data_type::varchar) = 'TIMESTAMP WITHOUT TIME ZONE' THEN
            'DATETIME'
        WHEN UPPER(data_type::varchar) = 'TIMESTAMP WITH TIME ZONE' THEN
            'DATETIME'
        WHEN UPPER(data_type::varchar) = 'INTEGER' THEN
            'INT'
        WHEN UPPER(data_type::varchar) = 'TEXT' THEN
            'STRING'
        WHEN UPPER(data_type::varchar)
        LIKE 'DECIMAL%' THEN
            'DECIMAL'
        WHEN UPPER(data_type::varchar) = 'NUMERIC' THEN
            'DECIMAL'
        ELSE
            UPPER(data_type::varchar)
        END AS data_type,
        CASE WHEN column_comment IS NULL THEN
            '''' || column_name || ''''
        ELSE
            '''' || column_comment || ''''
        END AS comments,
        ordinal_position
    FROM
        raw
    ORDER BY
        table_schema,
        table_name,
        ordinal_position
),
final AS (
    SELECT
        'etl_proc_name' AS column_name,
        'DATETIME' AS data_type,
        '''ETL处理时间''' AS comments,
        0 AS ordinal_position
    UNION
    SELECT
        *
    FROM
        transformed
)
SELECT
    column_name,
    data_type,
    REPLACE(regexp_replace(comments, E'\\n', ' '), E'\\n', ' ') AS comments
FROM
    final
ORDER BY
    ordinal_position

这段代码进行了如下转换：

1. 从 Hologres 中查询表字段，并且对数据类型进行了转换。
2. 添加了字段注释。当注释为空，那么使用字段名称作为注释。
3. 添加了一个元数据字段etl_proc_time，作为第一个字段。这样以后新增字段，追加即可。

【疯狂吐槽】Dataworks不支持修改字段的顺序（可能 MaxCompute 本身就不支持）。如果修改字段顺序且进行增量更新，Dataworks 维度建模会把字段顺序的修改，当做同行字段的重命名……真的滑稽。

实操步骤 🔗

虽然可以用 SQL 生成模型所需的 FML 语句，但实操的时候，还是要被恶心一把：在新建模型的时候，是无法使用“代码模式”的，此时代码模式按钮会灰色，即禁用的状态。因此，使用代码模式的姿势是：

1. 新建模型，填写基本信息
2. 在字段中随便填写一个字段
3. 保存模型
4. 模型保存成功，“代码模式”变成启用状态。此时点击“代码模式”，选择“FML快捷模式”
5. 在 Hologres 的 SQL 编辑器页面运行实现准备好的 SQL
6. 复制生成的结果，回到 Dataworks维度建模页面，将结果粘贴到 “FML快捷模式”编辑器中
7. 点击“确定”，再关闭“代码模式”窗口，即可看到字段已经正常导入
8. 确保字段无误后，再次保存即可

【疯狂吐槽】看，就是这么繁琐。开发效率下降 90%。

结尾 🔗

“数据工程”并不是只编写SQL、配置ETL，它还包括“工程”工作，可惜 Dataworks 的产品并没有给人“工程化数据开发”的感觉。“数据工程”本可以是一项技术活，但 Dataworks 在许许多多的细节上，将一份“略微体面”的工作，其变成了搬砖的体力活。

虽然使用 SQL生成 FML 语句依然要切换窗口，在操作流程不够丝滑，但也算是让搬砖的体力活轻松了一些，挽回了工程师的一些颜面吧。