在Snowflake创建对象链映射：procedure_id等对象ID何处查找？

替代对象ID的唯一标识方案 #

因为INFORMATION_SCHEMA无法返回procedure、table、view的对象ID，直接用复合唯一标识替代对象ID：

• 存储过程：PROCEDURE_CATALOG || '.' || PROCEDURE_SCHEMA || '.' || PROCEDURE_NAME || COALESCE(ARGUMENT_SIGNATURE, '')（不同数据库拼接语法有差异，比如MySQL用CONCAT）
• 表/视图：TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME
  这些字段在INFORMATION_SCHEMA的对应视图中都能获取，且能唯一标识每个对象。

解决权限不足的索引方案 #

INFORMATION_SCHEMA是系统视图，普通用户无权修改或创建索引，换以下方式：

1. 创建自定义元数据表：

CREATE TABLE object_metadata (
    object_type VARCHAR(20) NOT NULL, -- PROCEDURE/TABLE/VIEW
    object_identifier VARCHAR(255) NOT NULL PRIMARY KEY,
    catalog_name VARCHAR(64),
    schema_name VARCHAR(64),
    object_name VARCHAR(64),
    argument_signature VARCHAR(255),
    definition TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 从INFORMATION_SCHEMA同步数据到自定义表：

-- 同步存储过程
INSERT INTO object_metadata (object_type, object_identifier, catalog_name, schema_name, object_name, argument_signature, definition)
SELECT 'PROCEDURE',
       CONCAT(PROCEDURE_CATALOG, '.', PROCEDURE_SCHEMA, '.', PROCEDURE_NAME, COALESCE(ARGUMENT_SIGNATURE, '')),
       PROCEDURE_CATALOG,
       PROCEDURE_SCHEMA,
       PROCEDURE_NAME,
       ARGUMENT_SIGNATURE,
       ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.PROCEDURES;

-- 同步表
INSERT INTO object_metadata (object_type, object_identifier, catalog_name, schema_name, object_name, definition)
SELECT 'TABLE',
       CONCAT(TABLE_CATALOG, '.', TABLE_SCHEMA, '.', TABLE_NAME),
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       NULL
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

-- 同步视图
INSERT INTO object_metadata (object_type, object_identifier, catalog_name, schema_name, object_name, definition)
SELECT 'VIEW',
       CONCAT(TABLE_CATALOG, '.', TABLE_SCHEMA, '.', TABLE_NAME),
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS;

3. 在自定义表上创建所需索引：

CREATE INDEX idx_object_identifier ON object_metadata (object_identifier);
CREATE INDEX idx_object_type_schema ON object_metadata (object_type, schema_name);

构建对象链映射的具体步骤 #

1. 采集对象依赖关系
   • 优先用数据库自带的依赖视图（避免自己解析SQL）：
     • MySQL：查询sys.schema_dependencies直接获取对象间依赖
     • PostgreSQL：查询pg_depend关联pg_class、pg_proc获取依赖
   • 若数据库无自带依赖视图，解析自定义表中definition字段的SQL内容，用正则匹配提取被调用的对象（比如匹配FROM/JOIN后的表名、CALL后的存储过程名）
2. 存储对象链
   创建依赖关系表：

CREATE TABLE object_dependencies (
    parent_identifier VARCHAR(255) NOT NULL,
    child_identifier VARCHAR(255) NOT NULL,
    dependency_type VARCHAR(20) NOT NULL, -- CALL/REFERENCE
    PRIMARY KEY (parent_identifier, child_identifier),
    FOREIGN KEY (parent_identifier) REFERENCES object_metadata(object_identifier),
    FOREIGN KEY (child_identifier) REFERENCES object_metadata(object_identifier)
);

将采集到的依赖关系插入该表，形成父子链。
3. 分析对象状态

• 闲置对象：结合数据库审计日志/查询日志，统计object_metadata中无访问记录的对象
• 高影响对象：统计object_dependencies中每个child_identifier对应的parent_identifier数量，数量多的即为被多个对象调用的高影响对象

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