如何提取表的分桶数、分区及分桶字段信息（排除冗余）

你之前尝试的select PARTITIONED BY FROM (SHOW CREATE TABLE schema-name.table-name)方法无效，因为SHOW CREATE TABLE返回的是完整建表语句的字符串结果，并非结构化的列数据。以下是两种可行的实现方案，可直接集成到Jenkins自动化流水线中：

方法一：正则解析SHOW CREATE TABLE输出 #

通过shell命令（grep/sed/awk）提取目标信息，适合快速实现：

1. 获取分区字段 #

hive -e "SHOW CREATE TABLE schema-name.table-name;" | grep -i "PARTITIONED BY" | sed 's/PARTITIONED BY (//g' | sed 's/)//g' | sed 's/ //g'

2. 获取分桶字段 #

hive -e "SHOW CREATE TABLE schema-name.table-name;" | grep -i "CLUSTERED BY" | sed 's/CLUSTERED BY (//g' | sed 's/) INTO.*BUCKETS//g' | sed 's/ //g'

3. 获取分桶数 #

hive -e "SHOW CREATE TABLE schema-name.table-name;" | grep -i "CLUSTERED BY" | sed 's/.*INTO //g' | sed 's/ BUCKETS//g'

Jenkins流水线集成示例 #

pipeline {
    agent any
    stages {
        stage('Fetch Table Metadata') {
            steps {
                sh '''
                    # 定义表名变量
                    TABLE="schema-name.table-name"
                    
                    # 提取分区字段
                    PARTITION_COLS=$(hive -e "SHOW CREATE TABLE $TABLE;" | grep -i "PARTITIONED BY" | sed 's/PARTITIONED BY (//g' | sed 's/)//g' | sed 's/ //g')
                    echo "Partitioned by: $PARTITION_COLS"
                    
                    # 提取分桶字段
                    CLUSTER_COLS=$(hive -e "SHOW CREATE TABLE $TABLE;" | grep -i "CLUSTERED BY" | sed 's/CLUSTERED BY (//g' | sed 's/) INTO.*BUCKETS//g' | sed 's/ //g')
                    echo "Clustered by: $CLUSTER_COLS"
                    
                    # 提取分桶数
                    BUCKET_NUM=$(hive -e "SHOW CREATE TABLE $TABLE;" | grep -i "CLUSTERED BY" | sed 's/.*INTO //g' | sed 's/ BUCKETS//g')
                    echo "Number of buckets: $BUCKET_NUM"
                '''
            }
        }
    }
}

方法二：直接查询Hive元数据表 #

通过访问Hive内置元数据表获取结构化数据，稳定性更高（需确保有元数据表访问权限）：

1. 查询分区字段 #

SELECT GROUP_CONCAT(column_name ORDER BY integer_idx SEPARATOR ', ') AS partition_columns
FROM partition_keys
WHERE tbl_id = (
    SELECT tbl_id 
    FROM tbls 
    JOIN dbs ON tbls.db_id = dbs.db_id 
    WHERE dbs.name = 'schema-name' 
      AND tbls.tbl_name = 'table-name'
);

2. 查询分桶字段 #

SELECT GROUP_CONCAT(col_name ORDER BY integer_idx SEPARATOR ', ') AS clustered_columns
FROM sd_params sp
JOIN tbls t ON sp.sd_id = t.sd_id
JOIN dbs d ON t.db_id = d.db_id
WHERE d.name = 'schema-name' 
  AND t.tbl_name = 'table-name' 
  AND sp.param_key = 'clusterByCols';

3. 查询分桶数 #

SELECT sp.param_value AS bucket_count
FROM sd_params sp
JOIN tbls t ON sp.sd_id = t.sd_id
JOIN dbs d ON t.db_id = d.db_id
WHERE d.name = 'schema-name' 
  AND t.tbl_name = 'table-name' 
  AND sp.param_key = 'numBuckets';

Jenkins流水线集成示例 #

pipeline {
    agent any
    stages {
        stage('Query Hive Metastore') {
            steps {
                sh '''
                    # 查询分区字段
                    PARTITION_COLS=$(hive -e "
                        SELECT GROUP_CONCAT(column_name ORDER BY integer_idx SEPARATOR ', ')
                        FROM partition_keys
                        WHERE tbl_id = (
                            SELECT tbl_id 
                            FROM tbls 
                            JOIN dbs ON tbls.db_id = dbs.db_id 
                            WHERE dbs.name = 'schema-name' 
                              AND tbls.tbl_name = 'table-name'
                        );
                    ")
                    echo "Partitioned by: $PARTITION_COLS"
                    
                    # 查询分桶字段
                    CLUSTER_COLS=$(hive -e "
                        SELECT GROUP_CONCAT(col_name ORDER BY integer_idx SEPARATOR ', ')
                        FROM sd_params sp
                        JOIN tbls t ON sp.sd_id = t.sd_id
                        JOIN dbs d ON t.db_id = d.db_id
                        WHERE d.name = 'schema-name' 
                          AND t.tbl_name = 'table-name' 
                          AND sp.param_key = 'clusterByCols';
                    ")
                    echo "Clustered by: $CLUSTER_COLS"
                    
                    # 查询分桶数
                    BUCKET_NUM=$(hive -e "
                        SELECT sp.param_value
                        FROM sd_params sp
                        JOIN tbls t ON sp.sd_id = t.sd_id
                        JOIN dbs d ON t.db_id = d.db_id
                        WHERE d.name = 'schema-name' 
                          AND t.tbl_name = 'table-name' 
                          AND sp.param_key = 'numBuckets';
                    ")
                    echo "Number of buckets: $BUCKET_NUM"
                '''
            }
        }
    }
}

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