Just get column names from a Hive table

sql

hive-command

column-names

table-metadata

byAlex Kataev·Jan 1, 2025

To quickly fetch the column names of a Hive table:

hive -e 'SHOW COLUMNS IN table_name;'

This command provides a "clean list" of only the column names.

To get a detailed description of the table:

hive -e 'DESCRIBE table_name;'

You'll receive column names, data types, and comments in a descriptive format. Extra info at the cost of typing!

Column Extraction and Beyond

Get Column Names in Specific Database

If you have multiple databases in Hive, it's wise to specify the database:

hive -e 'USE database_name; SHOW COLUMNS IN table_name;'

hive -e 'SHOW COLUMNS IN database_name.table_name;'

Save Column Names in File

Need to analyze the column names later or go through them offline?

Save them to a file:

hive -e 'SHOW COLUMNS IN table_name;' > /path/to/yourfile.txt

Note: /path/to/yourfile.txt needs to be replaced with the actual path where you want the file to be saved. If you can find Nemo, you can find the path!

An Overview of Table Metadata

For a comprehensive overview of a table's metadata (not just column names), use:

hive -e 'DESC formatted table_name;'

Get ready for a meta(metadata)data data fall!

Steering Clear of Unnecessary Adjustments

Setting Headers isn't Necessary

You don't need to adjust settings like hive.cli.print.header=true;. While this is a popular approach when fetching query results, for our purpose of getting column names using SHOW COLUMNS, it's not required. They said simpler is better!

Beeline or CLI? It's a Draw

Still worried about getting column names? No sweat. Use Hive CLI or Beeline to execute DESC or SHOW COLUMNS. It will get you there, one column name at a time.

Pro Tips and Troubleshooting Guide

Dealing with Complex Structures

If you are dealing with tables with complex structures like maps or arrays, gear up:

hive -e 'DESCRIBE EXTENDED table_name;'

It returns an extended description, packed with metadata and column data types. More data than an owl's wisdom!

Column Name Overlaps? Backticks to the Rescue

Column names overlapping with Hive keywords or functions can be mess up the syntax. Use backticks (`) around column names to avoid such conflicts. A formula: Backticks + Overlapping Names = Problem Solved.