如何准确查询Hive/Impala表的压缩状态与编码类型？

Great question—this is such a common gotcha because Hive’s DESCRIBE [FORMATTED|EXTENDED] doesn’t reliably track compression status in table metadata. As you noticed, the Compressed field almost always shows No because compression settings are often session-specific during data loading, not persisted with the table’s metadata. Let’s walk through the reliable methods to get this info, whether you’re using Hive directly or Spark.

1. Checking in Hive #

For Columnar Formats (Parquet/ORC) #

These formats store compression metadata directly with the table or file, so you can get it from extended table descriptions:

• Run DESCRIBE FORMATTED your_table_name;
• Look for sections like:
  • For ORC: Under Table Parameters, find orc.compress (values like SNAPPY, GZIP, ZLIB, or NONE).
  • For Parquet: Under Table Parameters, look for parquet.compression (same common codec values).
• You can also use dedicated command-line tools if available:
  • ORC: orc-tools meta hdfs://path/to/your/orc/file (shows compression details in the file metadata).
  • Parquet: parquet-tools meta hdfs://path/to/your/parquet/file (check the Compression section).

For Text/Row-Based Formats #

Since compression here is usually applied during data writing (session-level), you need to inspect the actual data files:

• First, get the table’s storage path from DESCRIBE FORMATTED your_table_name; (look for Location under Storage Information).
• List the files in that path:

  hadoop fs -ls /path/to/your/table/location
  

  Compressed files typically have suffixes like .snappy, .gz, .bz2, or .lz4.
• To confirm the codec (if suffix is unclear), use Hadoop’s file inspection:

  # Check if the file can be decompressed with a specific codec
  hadoop fs -text /path/to/file | head -10
  # Or use the CompressionCodecFactory to detect automatically
  hadoop jar $HADOOP_HOME/share/hadoop/common/hadoop-common.jar org.apache.hadoop.io.compress.CompressionCodecFactory /path/to/file
  

2. Checking in Spark #

Spark makes it easy to inspect both table metadata and file-level compression details.

Option 1: Inspect Table Metadata #

Run this Spark SQL command to get extended table info:

DESCRIBE EXTENDED your_table_name;

Look for the same format-specific parameters as in Hive:

• parquet.compression for Parquet tables.
• orc.compress for ORC tables.

Option 2: Detect Compression Codecs from Data Files #

If you need to verify actual files (especially useful for text formats), use this code snippet to scan the table’s storage path:

Scala Version

import org.apache.hadoop.io.compress.CompressionCodecFactory
import org.apache.hadoop.fs.Path

// Get the table's storage path (you can also get this from DESCRIBE EXTENDED)
val tablePath = new Path("/path/to/your/table/location")
val fs = org.apache.hadoop.fs.FileSystem.get(spark.sparkContext.hadoopConfiguration)
val codecFactory = new CompressionCodecFactory(spark.sparkContext.hadoopConfiguration)

// Iterate through all files in the table path
val fileIterator = fs.listFiles(tablePath, recursive = true)
while (fileIterator.hasNext) {
  val fileStatus = fileIterator.next()
  if (!fileStatus.isDirectory) {
    val filePath = fileStatus.getPath
    val codec = codecFactory.getCodec(filePath)
    if (codec != null) {
      println(s"File: ${filePath.getName} | Compression Codec: ${codec.getClass.getSimpleName}")
    } else {
      println(s"File: ${filePath.getName} | Not Compressed")
    }
  }
}

Python Version

from pyspark.sql import SparkSession
from py4j.java_gateway import java_import

spark = SparkSession.builder.appName("CheckCompression").getOrCreate()

# Import Hadoop classes via Py4J
java_import(spark._jvm, "org.apache.hadoop.io.compress.CompressionCodecFactory")
java_import(spark._jvm, "org.apache.hadoop.fs.Path")

# Get table storage path
table_path = spark._jvm.Path("/path/to/your/table/location")
fs = spark._jvm.org.apache.hadoop.fs.FileSystem.get(spark._jsc.hadoopConfiguration())
codec_factory = spark._jvm.CompressionCodecFactory(spark._jsc.hadoopConfiguration())

# Scan files
file_iterator = fs.listFiles(table_path, True)
while file_iterator.hasNext():
    file_status = file_iterator.next()
    if not file_status.isDirectory():
        file_path = file_status.getPath()
        codec = codec_factory.getCodec(file_path)
        if codec:
            print(f"File: {file_path.getName()} | Compression Codec: {codec.getClass().getSimpleName()}")
        else:
            print(f"File: {file_path.getName()} | Not Compressed")

Key Takeaways #

• Columnar formats (Parquet/ORC): Trust the table’s extended metadata or dedicated tools—they store compression settings reliably.
• Text/row formats: Always check the actual data files, since compression is often session-dependent and not tracked in table metadata.

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