如何通过Spark或HQL获取SQL列注释及元数据检索方案

嘿，针对你需要批量提取Hive/Spark中大量表的列注释，并且要求结构化输出（一等表优先）的需求，我给你整理了两个专业方案——完全避开SHOW/DESCRIBE这类低效命令，直接从Derby底层元数据存储入手，效率和灵活性拉满！

核心思路 #

Hive的元数据（包括表、列、注释）都存在Derby的系统表里，我们直接通过Spark或Hive SQL关联这些系统表，就能一次性拿到所有结构化的元数据，输出成一等表、JSON或CSV都不在话下。

方案1：Spark Scala脚本（输出一等表，推荐） #

这个方案用Spark直接连接Derby元数据库，生成的DataFrame就是一等表，支持后续的检索、过滤、导出等所有Spark原生操作。

第一步：配置Spark连接Derby #

首先确保Spark的classpath里有Derby驱动（一般Hive安装包的lib目录下自带derby*.jar），然后初始化SparkSession并建立JDBC连接：

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder()
  .appName("ColumnCommentExtractor")
  .config("spark.sql.catalogImplementation", "hive") // 绑定Hive元数据
  .getOrCreate()

// 替换成你实际的Derby元数据存储路径，默认是Hive安装目录下的metastore_db
val derbyJdbcUrl = "jdbc:derby:/your/hive/installation/path/metastore_db;create=false"
val derbyUser = "" // Derby默认不需要用户名密码
val derbyPassword = ""

// 读取Derby的核心元数据表
val dbsDF = spark.read.jdbc(derbyJdbcUrl, "DBS", derbyUser, derbyPassword) // 数据库信息表
val tblsDF = spark.read.jdbc(derbyJdbcUrl, "TBLS", derbyUser, derbyPassword) // 表信息表
val colsDF = spark.read.jdbc(derbyJdbcUrl, "COLUMNS_V2", derbyUser, derbyPassword) // 列信息表
val sdDF = spark.read.jdbc(derbyJdbcUrl, "SDS", derbyUser, derbyPassword) // 存储描述表

第二步：关联表生成结构化元数据 #

通过关联这几张系统表，把数据库名、表名、列名、注释、类型整合到一起：

import spark.implicits._

val fullMetadataDF = dbsDF
  .join(tblsDF, dbsDF("DB_ID") === tblsDF("DB_ID"), "inner")
  .join(sdDF, tblsDF("SD_ID") === sdDF("SD_ID"), "inner")
  .join(colsDF, sdDF("CD_ID") === colsDF("CD_ID"), "inner")
  .select(
    dbsDF("NAME").alias("database_name"),
    tblsDF("TBL_NAME").alias("table_name"),
    colsDF("COL_NAME").alias("column_name"),
    colsDF("COMMENT").alias("column_comment"),
    colsDF("TYPE_NAME").alias("column_type")
  )
  .filter($"column_comment".isNotNull) // 可选：过滤掉没有注释的列

// 先看看结果
fullMetadataDF.show(false)

// 注册成临时表（一等表），方便后续SQL检索
fullMetadataDF.createOrReplaceTempView("column_metadata")

// 示例：查询指定数据库下所有表的列注释
spark.sql("SELECT * FROM column_metadata WHERE database_name = 'your_target_db'").show(false)

第三步：导出为JSON/CSV #

如果需要导出成文件格式，直接用Spark的写入API：

// 导出为JSON，覆盖已有文件
fullMetadataDF.write.mode("overwrite").json("/your/output/path/column_comments.json")

// 导出为CSV（带表头）
fullMetadataDF.write.mode("overwrite")
  .option("header", "true")
  .option("sep", ",")
  .csv("/your/output/path/column_comments.csv")

方案2：HQL直接查询Derby元数据（Hive环境适用） #

如果你更习惯用Hive SQL，也可以通过Hive的JDBC存储引擎直接映射Derby的系统表，然后关联查询。

第一步：创建Derby系统表的外部映射 #

先在Hive中创建指向Derby系统表的外部表（只需执行一次）：

-- 映射数据库信息表DBS
CREATE EXTERNAL TABLE dbs (
  DB_ID INT,
  NAME STRING,
  DESCRIPTION STRING,
  LOCATION_URI STRING,
  OWNER_NAME STRING,
  OWNER_TYPE STRING
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
  "hive.sql.database.type" = "DERBY",
  "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
  "hive.sql.jdbc.url" = "jdbc:derby:/your/hive/installation/path/metastore_db;create=false",
  "hive.sql.table" = "DBS"
);

-- 映射表信息表TBLS
CREATE EXTERNAL TABLE tbls (
  TBL_ID INT,
  CREATE_TIME BIGINT,
  DB_ID INT,
  LAST_ACCESS_TIME BIGINT,
  OWNER STRING,
  RETENTION INT,
  SD_ID INT,
  TBL_NAME STRING,
  TBL_TYPE STRING,
  VIEW_EXPANDED_TEXT STRING,
  VIEW_ORIGINAL_TEXT STRING
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
  "hive.sql.database.type" = "DERBY",
  "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
  "hive.sql.jdbc.url" = "jdbc:derby:/your/hive/installation/path/metastore_db;create=false",
  "hive.sql.table" = "TBLS"
);

-- 映射列信息表COLUMNS_V2
CREATE EXTERNAL TABLE columns_v2 (
  CD_ID INT,
  COMMENT STRING,
  COL_NAME STRING,
  INTEGER_IDX INT,
  TYPE_NAME STRING
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
  "hive.sql.database.type" = "DERBY",
  "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
  "hive.sql.jdbc.url" = "jdbc:derby:/your/hive/installation/path/metastore_db;create=false",
  "hive.sql.table" = "COLUMNS_V2"
);

-- 映射存储描述表SDS
CREATE EXTERNAL TABLE sds (
  SD_ID INT,
  CD_ID INT,
  INPUT_FORMAT STRING,
  IS_COMPRESSED BOOLEAN,
  LOCATION STRING,
  NUM_BUCKETS INT,
  OUTPUT_FORMAT STRING,
  SERDE_ID INT
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
  "hive.sql.database.type" = "DERBY",
  "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
  "hive.sql.jdbc.url" = "jdbc:derby:/your/hive/installation/path/metastore_db;create=false",
  "hive.sql.table" = "SDS"
);

第二步：关联查询生成结构化结果 #

通过HQL关联这些表，生成一等表，也可以直接导出为JSON/CSV：

-- 创建持久化的一等表，存储为Parquet（高效格式）
CREATE TABLE column_metadata STORED AS PARQUET AS
SELECT
  d.NAME AS database_name,
  t.TBL_NAME AS table_name,
  c.COL_NAME AS column_name,
  c.COMMENT AS column_comment,
  c.TYPE_NAME AS column_type
FROM dbs d
JOIN tbls t ON d.DB_ID = t.DB_ID
JOIN sds s ON t.SD_ID = s.SD_ID
JOIN columns_v2 c ON s.CD_ID = c.CD_ID
WHERE c.COMMENT IS NOT NULL;

-- 导出为CSV到本地目录
INSERT OVERWRITE LOCAL DIRECTORY '/your/output/path/csv'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
SELECT * FROM column_metadata;

-- 导出为JSON到本地目录
INSERT OVERWRITE LOCAL DIRECTORY '/your/output/path/json'
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
SELECT * FROM column_metadata;

几个关键注意点 #

1. Derby路径替换：所有示例中的/your/hive/installation/path/metastore_db一定要替换成你实际的Hive元数据存储路径，找不到的话可以看Hive配置文件hive-site.xml里的javax.jdo.option.ConnectionURL参数。
2. 性能优化：如果元数据量特别大，建议在关联时先过滤指定的数据库（比如WHERE d.NAME = 'your_db'），减少数据处理量。
3. 驱动依赖：如果Spark或Hive找不到Derby驱动，手动把Hive lib下的derby.jar和derbyclient.jar加到classpath里就行。

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