SAP HANA数据库表/视图列批量分析实现方案咨询

需求说明 #

需要SAP HANA数据库自主完成表/列的多维度统计，输出固定格式的结果表，支持自定义统计项，示例输出如下：

核心要求：

• 无需本地PC参与计算，完全由HANA处理
• 支持扩展统计项，例如Top X高频值
• 可按表名、列名过滤统计对象

现有思路 #

用户尝试的逻辑：

1. 通过系统视图获取目标Schema下的表/视图与列列表：

   SELECT VIEW_NAME TableViewName, COLUMN_NAME ColumnName FROM VIEW_COLUMNS WHERE SCHEMA_NAME='SCHEMA'
   

2. 遍历每个表/视图→遍历每个列→遍历每个统计项，通过子查询获取结果并整理格式

最优方案：使用HANA存储过程（灵活可扩展） #

单SQL无法实现动态遍历所有表列及自定义统计项，存储过程是最简洁高效的实现方式，以下是具体方案：

1. 创建存储过程 #

该存储过程支持传入Schema、表/列过滤条件、Top N高频值数量等参数，自动生成所有统计结果：

CREATE PROCEDURE GET_COLUMN_PROFILES(
    IN SCHEMA_NAME VARCHAR(256),
    IN TABLE_FILTER VARCHAR(256) DEFAULT '%',
    IN COLUMN_FILTER VARCHAR(256) DEFAULT '%',
    IN TOP_N_VALUES INT DEFAULT 3
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    -- 创建临时表存储结果
    CREATE LOCAL TEMPORARY COLUMN TABLE #PROFILE_RESULTS (
        TABLE_NAME VARCHAR(256),
        COLUMN_NAME VARCHAR(256),
        PROFILE_TYPE VARCHAR(256),
        PROFILE_COUNT BIGINT
    );

    -- 游标获取符合条件的表与列
    DECLARE CURSOR C_TABLE_COLS FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM TABLE_COLUMNS -- 统计表用TABLE_COLUMNS，统计视图替换为VIEW_COLUMNS
        WHERE SCHEMA_NAME = :SCHEMA_NAME
          AND TABLE_NAME LIKE :TABLE_FILTER
          AND COLUMN_NAME LIKE :COLUMN_FILTER;

    -- 遍历每个表列，计算所有统计项
    FOR REC IN C_TABLE_COLS DO
        -- 计算NULLCOUNT：总行数-非空行数
        EXECUTE IMMEDIATE '
            INSERT INTO #PROFILE_RESULTS
            SELECT ''' || REC.TABLE_NAME || ''', ''' || REC.COLUMN_NAME || ''', ''NULLCOUNT'', COUNT(*) - COUNT(' || REC.COLUMN_NAME || ')
            FROM "' || SCHEMA_NAME || '"."' || REC.TABLE_NAME || '"
        ';

        -- 计算UNIQUEVALS：去重后的值数量
        EXECUTE IMMEDIATE '
            INSERT INTO #PROFILE_RESULTS
            SELECT ''' || REC.TABLE_NAME || ''', ''' || REC.COLUMN_NAME || ''', ''UNIQUEVALS'', COUNT(DISTINCT ' || REC.COLUMN_NAME || ')
            FROM "' || SCHEMA_NAME || '"."' || REC.TABLE_NAME || '"
        ';

        -- 计算Top N高频值（若指定N>0）
        IF TOP_N_VALUES > 0 THEN
            EXECUTE IMMEDIATE '
                INSERT INTO #PROFILE_RESULTS
                SELECT ''' || REC.TABLE_NAME || ''', ''' || REC.COLUMN_NAME || ''', ''TOP_VALUE:'' || CAST(' || REC.COLUMN_NAME || ' AS VARCHAR(256)), COUNT(*)
                FROM "' || SCHEMA_NAME || '"."' || REC.TABLE_NAME || '"
                WHERE ' || REC.COLUMN_NAME || ' IS NOT NULL
                GROUP BY ' || REC.COLUMN_NAME || '
                ORDER BY COUNT(*) DESC
                LIMIT ' || TOP_N_VALUES || '
            ';
        END IF;
    END FOR;

    -- 返回最终统计结果
    SELECT * FROM #PROFILE_RESULTS;
END;

2. 调用存储过程 #

示例：统计YOUR_SCHEMA下名称含TEST的表、名称含COL的列，同时输出Top2高频值：

CALL GET_COLUMN_PROFILES('YOUR_SCHEMA', '%TEST%', '%COL%', 2);

3. 扩展统计项 #

如需添加新的统计类型（例如最大值、最小值），只需在循环中添加对应的动态SQL即可，示例：

-- 添加最大值统计
EXECUTE IMMEDIATE '
    INSERT INTO #PROFILE_RESULTS
    SELECT ''' || REC.TABLE_NAME || ''', ''' || REC.COLUMN_NAME || ''', ''MAX_VALUE'', MAX(' || REC.COLUMN_NAME || ')
    FROM "' || SCHEMA_NAME || '"."' || REC.TABLE_NAME || '"
';

注意事项 #

• 确保执行用户拥有查询系统视图、读取目标表、创建临时表的权限
• 大表统计会占用数据库资源，建议在非业务高峰时段执行
• 若统计视图，需将存储过程中的TABLE_COLUMNS替换为VIEW_COLUMNS

内容的提问来源于stack exchange，提问作者jdawgx