In this article I'll present you how to get a list of databases from your Amazon Athena environment.

Use SHOW DATABASES command

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

SHOW DATABASES

Example response

default
mynewdatabase
redis
sampledb
testdb

Querying AWS Glue Data Catalog

Querying AWS Glue Data Catalog can provide you with a list of databases in a tabular format.

SELECT 
    catalog_name,
    schema_name AS database_name
FROM information_schema.schemata
WHERE schema_name <> 'information_schema'

Example result

Using AWS CLI

You can also use the official AWS CLI to get a list of Athena databases in a JSON, text, or tabular format.

aws athena list-databases \
    --catalog-name <CATALOG_NAME>

Example

aws athena list-databases \
    --catalog-name AwsDataCatalog

Example output:

{
    "DatabaseList": [
        {
            "Name": "default"
        },
        {
            "Name": "mydatabase"
        },
        {
            "Name": "newdb"
        },
        {
            "Name": "sampledb",
            "Description": "Sample database",
            "Parameters": {
                "CreatedBy": "Athena",
                "EXTERNAL": "TRUE"
            }
        },
        {
            "Name": "webdata"
        }
    ]
}

You can read more on this method in the official docs