SparkSQL/Hive是否有MySQL中information_schema.tables统计信息的等价获取方式？

Great question! I’ve run into this exact scenario before—unlike MySQL’s handy information_schema.tables, SparkSQL and Hive don’t have a single drop-in replacement, but there are reliable ways to get table row counts and size metrics depending on your needs. Let’s break it down:

Using SparkSQL to Retrieve Table Stats #

SparkSQL can query Hive’s metastore tables directly, or compute stats on the fly (though the latter is slow for large tables). Here are the most common approaches:

1. Query Hive Metastore Tables (Precomputed Stats) #

Hive stores table/partition stats in its internal metastore tables (like TBLS, TABLE_PARAMS, PARTITIONS). These stats are not real-time—you need to run ANALYZE TABLE your_table COMPUTE STATISTICS; (or FOR ALL PARTITIONS for partitioned tables) first to populate them.

For Non-Partitioned Tables:

SELECT
  d.NAME AS database_name,
  t.TBL_NAME AS table_name,
  tp.PARAM_VALUE AS total_size_bytes,
  tp2.PARAM_VALUE AS row_count
FROM
  hive_metastore.TBLS t
JOIN
  hive_metastore.DBS d ON t.DB_ID = d.DB_ID
LEFT JOIN
  hive_metastore.TABLE_PARAMS tp ON t.TBL_ID = tp.TBL_ID AND tp.PARAM_KEY = 'totalSize'
LEFT JOIN
  hive_metastore.TABLE_PARAMS tp2 ON t.TBL_ID = tp2.TBL_ID AND tp2.PARAM_KEY = 'numRows'
WHERE
  d.NAME = 'your_database'
  AND t.TBL_NAME = 'your_table';

For Partitioned Tables:

You’ll need to aggregate stats across all partitions:

SELECT
  d.NAME AS database_name,
  t.TBL_NAME AS table_name,
  SUM(CAST(p.PARAM_VALUE AS BIGINT)) AS total_size_bytes,
  SUM(CAST(p2.PARAM_VALUE AS BIGINT)) AS row_count
FROM
  hive_metastore.TBLS t
JOIN
  hive_metastore.DBS d ON t.DB_ID = d.DB_ID
JOIN
  hive_metastore.PARTITIONS pt ON t.TBL_ID = pt.TBL_ID
LEFT JOIN
  hive_metastore.PARTITION_PARAMS p ON pt.PART_ID = p.PART_ID AND p.PARAM_KEY = 'totalSize'
LEFT JOIN
  hive_metastore.PARTITION_PARAMS p2 ON pt.PART_ID = p2.PART_ID AND p2.PARAM_KEY = 'numRows'
WHERE
  d.NAME = 'your_database'
  AND t.TBL_NAME = 'your_table'
GROUP BY
  d.NAME, t.TBL_NAME;

2. Compute Stats On-the-Fly (Real-Time but Slow) #

If you can’t rely on precomputed metastore stats, you can calculate directly—just be aware this will scan the entire table, which is not feasible for large datasets:

-- Get row count
SELECT COUNT(*) AS row_count FROM your_database.your_table;

-- Get table size (SparkSQL 3.0+; support varies by distribution)
SELECT SUM(data_size) AS total_size_bytes
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'your_database'
  AND table_name = 'your_table';

Using HiveMetaStoreClient (Java API) #

You’re right that there’s no direct method for row count/size in HiveMetaStoreClient, but you can extract these values from the table/partition parameters—again, only if you’ve run ANALYZE TABLE first. Here’s how to do it:

Example Code #

import org.apache.hadoop.hive.conf.HiveConf;
import org.apache.hadoop.hive.metastore.HiveMetaStoreClient;
import org.apache.hadoop.hive.metastore.api.Table;
import org.apache.hadoop.hive.metastore.api.Partition;
import org.apache.thrift.TException;

public class HiveTableStatsFetcher {
    public static void main(String[] args) {
        HiveConf hiveConf = new HiveConf();
        String dbName = "your_database";
        String tableName = "your_table";

        try (HiveMetaStoreClient client = new HiveMetaStoreClient(hiveConf)) {
            Table table = client.getTable(dbName, tableName);
            
            // Fetch table-level stats (for non-partitioned tables)
            String totalSize = table.getParameters().get("totalSize");
            String numRows = table.getParameters().get("numRows");
            
            System.out.printf("Table: %s.%s%n", dbName, tableName);
            System.out.println("Total Size (bytes): " + (totalSize != null ? totalSize : "Not available (run ANALYZE TABLE)"));
            System.out.println("Row Count: " + (numRows != null ? numRows : "Not available (run ANALYZE TABLE)"));
            
            // Handle partitioned tables
            if (!table.getPartitionKeys().isEmpty()) {
                long totalPartitionSize = 0;
                long totalPartitionRows = 0;
                
                for (String partitionName : client.listPartitions(dbName, tableName, (short) -1)) {
                    // Split partition name into key-value pairs (format: col1=val1/col2=val2)
                    String[] partitionVals = partitionName.split("/");
                    Partition partition = client.getPartition(dbName, tableName, partitionVals);
                    
                    String partSize = partition.getParameters().get("totalSize");
                    String partRows = partition.getParameters().get("numRows");
                    
                    if (partSize != null) totalPartitionSize += Long.parseLong(partSize);
                    if (partRows != null) totalPartitionRows += Long.parseLong(partRows);
                }
                
                System.out.println("\nPartitioned Table Aggregates:");
                System.out.println("Total Size (bytes): " + totalPartitionSize);
                System.out.println("Total Row Count: " + totalPartitionRows);
            }
        } catch (TException e) {
            e.printStackTrace();
        }
    }
}

Key Notes #

• Stats are static: The metastore only updates these values when you run ANALYZE TABLE—they won’t reflect new data added after the last ANALYZE run.
• Partitioned tables require iteration: You have to loop through each partition to sum up stats, since there’s no aggregated value stored at the table level.

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