Select your cookie preferences

We use essential cookies and similar tools that are necessary to provide our site and services. We use performance cookies to collect anonymous statistics, so we can understand how customers use our site and make improvements. Essential cookies cannot be deactivated, but you can choose “Customize” or “Decline” to decline performance cookies.

If you agree, AWS and approved third parties will also use cookies to provide useful site features, remember your preferences, and display relevant content, including relevant advertising. To accept or decline all non-essential cookies, choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”

Omit the catalog name in external Hive metastore queries

Focus mode
Omit the catalog name in external Hive metastore queries - Amazon Athena

When you run DML and DDL queries on external Hive metastores, you can simplify your query syntax by omitting the catalog name if that name is selected in the query editor. Certain restrictions apply to this functionality.

DML statements

To run queries with registered catalogs
  1. You can put the data source name before the database using the syntax [[data_source_name].database_name].table_name, as in the following example.

    select * from "hms-catalog-1".hms_tpch.customer limit 10;
  2. When the data source that you want to use is already selected in the query editor, you can omit the name from the query, as in the following example.

    select * from hms_tpch.customer limit 10:
    A DML query using a default data source.
  3. When you use multiple data sources in a query, you can omit only the default data source name, and must specify the full name for any non-default data sources.

    For example, suppose AwsDataCatalog is selected as the default data source in the query editor. The FROM statement in the following query excerpt fully qualifies the first two data source names but omits the name for the third data source because it is in the AWS Glue data catalog.

    ... FROM ehms01.hms_tpch.customer, "hms-catalog-1".hms_tpch.orders, hms_tpch.lineitem ...

DDL statements

The following Athena DDL statements support catalog name prefixes. Catalog name prefixes in other DDL statements cause syntax errors.

SHOW TABLES [IN [catalog_name.]database_name] ['regular_expression'] SHOW TBLPROPERTIES [[catalog_name.]database_name.]table_name [('property_name')] SHOW COLUMNS IN [[catalog_name.]database_name.]table_name SHOW PARTITIONS [[catalog_name.]database_name.]table_name SHOW CREATE TABLE [[catalog_name.][database_name.]table_name DESCRIBE [EXTENDED | FORMATTED] [[catalog_name.][database_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )]

As with DML statements, you can omit the datasource and database prefixes from the query when the data source and database are selected in the query editor.

In the following image, the hms-catalog-1 data source and the hms_tpch database are selected in the query editor. The show create table customer statement succeeds even though the hms-catalog-1 prefix and the hms_tpch database name are omitted from the query itself.

A DDL statement using the default catalog.

Specifying a default data source in a JDBC connection string

When you use the Athena JDBC Driver to connect Athena to an external Hive metastore, you can use the Catalog parameter to specify the default data source name in your connection string in a SQL editor like SQL workbench.

Note

To download the latest Athena JDBC drivers, see Using Athena with the JDBC driver.

The following connection string specifies the default data source hms-catalog-name.

jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://amzn-s3-demo-bucket/lambda/results/;Workgroup=AmazonAthenaPreviewFunctionality;Catalog=hms-catalog-name;

The following image shows a sample JDBC connection URL as configured in SQL Workbench.

Configuring a JDBC connection URL in SQL Workbench.
PrivacySite termsCookie preferences
© 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved.