In this article, I'll share with you a couple of ways to list all database columns and columns for a specific table in Amazon Athena.

SHOW COLUMNS command

This approach is excellent for quick investigation but is very limited. It returns only column names and outputs string instead of a table.

You can get a list of all columns within a table using this statement.

SHOW COLUMNS IN <TABLE_NAME>

Example

SHOW COLUMNS IN cities_csv

latd
latm
lats
ns
lond
lonm
lons
ew
city
state

Querying AWS Glue Data Catalog

Querying AWS Glue Data Catalog can provide you with full columns' metadata in a tabular format.

All table columns

To select all columns within Athena enviroment you can use this SQL query.

SELECT 
    table_catalog,
    table_schema AS database_name,
    table_name,
    column_name,
    ordinal_position,
    column_default,
    CASE
        WHEN is_nullable = 'YES' THEN TRUE
        ELSE FALSE
    END AS is_nullable,
    data_type
FROM information_schema.columns

Columns in a specific database

Below query helps you list columns in a specific database.

SELECT 
    table_catalog,
    table_schema AS database_name,
    table_name,
    column_name,
    ordinal_position,
    column_default,
    CASE
        WHEN is_nullable = 'YES' THEN TRUE
        ELSE FALSE
    END AS is_nullable,
    data_type
FROM information_schema.columns
WHERE table_schema = '<DATABASE_NAME>' 

Columns in a specific table

Below query lets you list columns in a specific table.

SELECT 
    table_catalog,
    table_schema AS database_name,
    table_name,
    column_name,
    ordinal_position,
    column_default,
    CASE
        WHEN is_nullable = 'YES' THEN TRUE
        ELSE FALSE
    END AS is_nullable,
    data_type
FROM information_schema.columns
WHERE table_schema = '<DATABASE_NAME>' 
    AND table_name = '<TABLE_NAME>'

Example

SELECT 
    table_catalog,
    table_schema AS database_name,
    table_name,
    column_name,
    ordinal_position,
    column_default,
    CASE
        WHEN is_nullable = 'YES' THEN TRUE
        ELSE FALSE
    END AS is_nullable,
    data_type
FROM information_schema.columns
WHERE table_schema = 'default' 
    AND table_name = 'elb_logs'

Response

Using AWS CLI

To get list of all tables and columns inside them you can also use official AWS CLI.

aws athena list-table-metadata \
    --catalog-name <CATALOG_NAME> \
    --database-name <DATABASE_NAME \
    --max-items 2

Example

aws athena list-table-metadata \
    --catalog-name AwsDataCatalog \
    --database-name geography \
    --max-items 2

Response

{
    "TableMetadataList": [
        {
            "Name": "country_codes",
            "CreateTime": 1586553454.0,
            "TableType": "EXTERNAL_TABLE",
            "Columns": [
                {
                    "Name": "country",
                    "Type": "string",
                    "Comment": "geo id"
                },
                {
                    "Name": "alpha-2 code",
                    "Type": "string",
                    "Comment": "geo id2"
                },
                {
                    "Name": "alpha-3 code",
                    "Type": "string",
                    "Comment": "state name"
                },
                {
                    "Name": "numeric code",
                    "Type": "bigint",
                    "Comment": ""
                },
                {
                    "Name": "latitude",
                    "Type": "bigint",
                    "Comment": "location (latitude)"
                },
                {
                    "Name": "longitude",
                    "Type": "bigint",
                    "Comment": "location (longitude)"
                }
            ],

You can read more on this method in the official docs.

Dataedo

You can also import AWS Athena columns using Dataedo. You can download Dataedo here and proceed with these instructions to import metadata from Athena.

Final result