CREATE LOCATION

Reference and syntax for the CREATE LOCATION statement.

Creates a new location object in your Firebolt account, which is a secure, reusable object that stores the connection details and credentials for external data sources. Instead of entering these details each time you run a query or create a table, you can use a location object.

The location object stores the following:

• The source type specification
• Authentication credentials
• The data source URL
• Optional descriptive metadata

This makes it easier to manage data access, keeps your credentials safe, and saves you from having to repeat the same information across multiple queries.

You can use LOCATION to centralize credential storage with managed access with Role-Based Access Control (RBAC) for CREATE, MODIFY, and USAGE permissions for different users, use a single location definition for multiple tables and queries, and control access to sensitive data. LOCATION is version control-friendly because no sensitive credentials are stored in source code.

After you create a LOCATION, you can use the information_schema.locations view to see details about all the locations in your account including source type, URL, description, owner and creation time.

For a comprehensive guide to understanding and using LOCATION objects, see LOCATION objects.

Related:

This document covers LOCATION objects in general. For more information about specific types of LOCATION objects, including more in-depth examples, please see:

• CREATE LOCATION (Amazon S3)
• CREATE LOCATION (Amazon Bedrock)
• CREATE LOCATION (Iceberg)
• CREATE LOCATION (Postgres)
• CREATE LOCATION (MongoDB)

Syntax

CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = { CLOUD_STORAGE | AMAZON_S3 | AMAZON_BEDROCK | ICEBERG | KAFKA | POSTGRES | MONGODB }
  [ , ... ]

-- Cloud Storage Location (auto-infers backend from URL)
CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = CLOUD_STORAGE
  CREDENTIALS = {
    ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
      AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
      [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
    |
    ( AWS_ROLE_ARN = '<aws_role_arn>'
      [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
  }
  URL = '<url>'
  [ DESCRIPTION = '<description>' ]

-- Amazon S3 Location (alias for CLOUD_STORAGE with S3 URL)
CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = AMAZON_S3
  CREDENTIALS = {
    ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
      AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
      [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
    |
    ( AWS_ROLE_ARN = '<aws_role_arn>'
      [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
  }
  URL = '<url>'
  [ DESCRIPTION = '<description>' ]

-- Amazon Bedrock Location
CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = AMAZON_BEDROCK
  CREDENTIALS = {
    ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
      AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
      [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
    |
    ( AWS_ROLE_ARN = '<aws_role_arn>'
      [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
  }
  [ DESCRIPTION = '<description>' ]

-- Iceberg Location
CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = ICEBERG
  CATALOG = { FILE_BASED | REST | DATABRICKS_UNITY | SNOWFLAKE_OPEN_CATALOG | AWS_GLUE | S3_TABLES }
  CATALOG_OPTIONS = {
    -- FILE_BASED
    ( URL = '<file_based_catalog_url>' )
    |
    -- REST or SNOWFLAKE_OPEN_CATALOG
    ( URL = '<rest_catalog_url>'
      WAREHOUSE = '<warehouse>'
      NAMESPACE = '<namespace>'
      TABLE = '<table_name>' )
    |
    -- DATABRICKS_UNITY
    ( WORKSPACE_INSTANCE = '<workspace_instance>'
      CATALOG = '<uc_catalog_name>'
      SCHEMA = '<uc_schema_name>'
      TABLE = '<uc_table_name>' )
    |
    -- AWS_GLUE
    ( URL = '<aws_glue_endpoint_url>'
      CATALOG_ID = '<catalog_id>'
      [ DATABASE = '<database_name>' ]
      [ TABLE = '<table_name>' ] )
    |
    -- S3_TABLES
    ( WAREHOUSE = '<table_bucket_arn>'
      [ URL = '<s3_tables_endpoint_url>' ]
      [ NAMESPACE = '<namespace>' ]
      [ TABLE = '<table_name>' ] )
  }
  CREDENTIALS = {
    -- AWS Authentication
    ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
      AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
      [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
    |
    ( AWS_ROLE_ARN = '<aws_role_arn>'
      [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
    |
    -- OAuth Authentication
    ( OAUTH_CLIENT_ID = '<oauth_client_id>'
      OAUTH_CLIENT_SECRET = '<oauth_client_secret>'
      [ OAUTH_SCOPE = '<oauth_scope>' ]
      [ OAUTH_SERVER_URL = '<oauth_server_url>' ] )
    |
    ( BEARER_TOKEN = '<bearer_token>' )
  }
  -- STORAGE_CREDENTIALS: REST, DATABRICKS_UNITY, SNOWFLAKE_OPEN_CATALOG only
  [ STORAGE_CREDENTIALS = {
      ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
        AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
        [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
      |
      ( AWS_ROLE_ARN = '<aws_role_arn>'
        [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
    } ]
  [ DESCRIPTION = '<description>' ]

When you authenticate with AWS_ROLE_ARN, set the optional AWS_ROLE_EXTERNAL_ID to add a customer-controlled condition to your role’s trust policy.

For role-based AWS access you can additionally set an external ID. An external ID is a value you choose and control that AWS checks when Firebolt assumes your role, adding a second condition on top of your account’s unique IAM principal. Configuring one is a recommended best practice. See IAM roles.

Parameters

Common Parameters

Parameter	Description
<location_name>	A unique identifier for the location within your account.
SOURCE	The external data source type. Currently, CLOUD_STORAGE (see CREATE LOCATION (Amazon S3)), AMAZON_S3 (alias for CLOUD_STORAGE with S3 URL, see CREATE LOCATION (Amazon S3)), AMAZON_BEDROCK (see CREATE LOCATION (Amazon Bedrock)), ICEBERG (see CREATE LOCATION (Iceberg)), KAFKA, POSTGRES (see CREATE LOCATION (Postgres)), and MONGODB (see CREATE LOCATION (MongoDB)) are supported.
DESCRIPTION	Optional metadata describing the location’s purpose.

Cloud Storage Parameters

Parameter	Description
CREDENTIALS	Authentication credentials for cloud storage access. On a gs:// URL, omit CREDENTIALS to let the engine use its own Google identity, where the deployment permits that.
URL	The data source URL. CLOUD_STORAGE infers the backend from the scheme: s3://{bucket_name}/{path} (also s3a:// and s3n://) for Amazon S3, and gs://{bucket_name}/{path} for Google Cloud Storage.

A URL with no CREDENTIALS that points to object storage Firebolt manages for your account is rejected, because such a request reaches that storage with the engine’s own cloud identity. Query the managed tables it holds directly instead. On a self-managed deployment the bucket is in your own cloud account, so you can also supply CREDENTIALS to reach it as that principal. On the Firebolt managed service the bucket is Firebolt’s, so that route is not open to you.

Whether you can omit CREDENTIALS on a gs:// URL depends on the deployment. Where it sets storage.gcp.intermediary_service_account_id, that service account is the principal, so you can omit CREDENTIALS whatever storage.gcp.allow_engine_identity says. Where it sets none, the engine uses its own Google identity, which storage.gcp.allow_engine_identity = true allows. The Firebolt Operator and the Helm chart enable it by default for the engines they deploy; on a standalone engine you enable it yourself. On the Firebolt managed service the engine’s Google identity belongs to Firebolt rather than to your account, so the mode is not available there: supply a Google Cloud Storage HMAC key in CREDENTIALS instead.

Amazon S3 Parameters

Parameter	Description
CREDENTIALS	Authentication credentials for Amazon S3 access.
URL	The data source URL in the format: s3://{bucket_name}/{path}. This must be a valid S3 URL.

Amazon Bedrock Parameters

Parameter	Description
CREDENTIALS	Authentication credentials for Amazon Bedrock access.

Iceberg Parameters

Parameter	Description
CATALOG	The type of Iceberg catalog. Supported values: FILE_BASED, REST, DATABRICKS_UNITY, SNOWFLAKE_OPEN_CATALOG, AWS_GLUE, or S3_TABLES.
CATALOG_OPTIONS	Configuration options specific to the chosen catalog type.
CREDENTIALS	Authentication credentials for accessing the Iceberg catalog. Depending on the type of catalog, either AWS or OAuth credentials are used.
STORAGE_CREDENTIALS	Optional AWS object-storage credentials for REST, DATABRICKS_UNITY, and SNOWFLAKE_OPEN_CATALOG, used as a fallback if the catalog does not have credential vending enabled. See CREATE LOCATION (Iceberg).

AWS Authentication Parameters

Parameter	Description
AWS_ACCESS_KEY_ID	Your AWS access key ID for key-based authentication.
AWS_SECRET_ACCESS_KEY	Your AWS secret access key for key-based authentication.
AWS_SESSION_TOKEN	Optional temporary session token for temporary credentials.
AWS_ROLE_ARN	The ARN of the IAM role to assume for role-based authentication.
AWS_ROLE_EXTERNAL_ID	Optional external ID for role assumption.

OAuth Authentication Parameters

Parameter	Description
OAUTH_CLIENT_ID	The OAuth client ID for authentication.
OAUTH_CLIENT_SECRET	The OAuth client secret for authentication.
OAUTH_SCOPE	Optional OAuth scope for authentication.
OAUTH_SERVER_URL	Optional OAuth server URL for authentication.
BEARER_TOKEN	OAuth bearer token for authentication. Only supported for DATABRICKS_UNITY catalog.	TEXT

Iceberg Catalog-Specific Parameters

FILE_BASED Catalog

Parameter	Description
URL	The URL for the file-based catalog.

REST Catalog

Parameter	Description
URL	The REST catalog URL.
WAREHOUSE	The warehouse identifier.
NAMESPACE	The namespace identifier.
TABLE	The table name.

DATABRICKS_UNITY Catalog

Parameter	Description
WORKSPACE_INSTANCE	The Databricks workspace instance.
CATALOG	The Unity Catalog name.
SCHEMA	The Unity Catalog schema name.
TABLE	The Unity Catalog table name.

SNOWFLAKE_OPEN_CATALOG Catalog

Snowflake Open Catalog is a REST-shaped catalog and takes the same parameters as REST. See CREATE LOCATION (Iceberg) for details.

AWS_GLUE Catalog

Parameter	Description
URL	The AWS Glue endpoint URL. Format: https://glue.<region>.amazonaws.com/iceberg
CATALOG_ID	The AWS Glue catalog ID.
DATABASE	The AWS Glue database name.
TABLE	The AWS Glue table name.

S3_TABLES Catalog

Nightly Feature

Direct access to Amazon S3 Tables through the S3 Tables Iceberg REST endpoint, authenticated with AWS SigV4. See CREATE LOCATION (Iceberg) for details.

Parameter	Description
WAREHOUSE	The Amazon S3 Tables table-bucket ARN. Format: arn:aws:s3tables:<region>:<account_id>:bucket/<bucket_name>. Firebolt derives the signing region from the ARN.
URL	Optional. The S3 Tables Iceberg REST endpoint. Derived from the ARN for standard AWS Regions when omitted.
NAMESPACE	Optional. Restricts the location to a single namespace.
TABLE	Optional. Pins the location to a single table. Requires NAMESPACE.

Examples

• Create a location
• Create a location with a description
• Create a location only if it doesn’t exist
• Use a location to load data into an external table
• Use a location to load data using COPY statements
• Use a location to load data with a TVF
• Alter a location
• Delete a location

Create a location

The following code example creates a location that uses keys to authenticate to AWS:

CREATE LOCATION my_location WITH
  SOURCE = AMAZON_S3
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '1231' AWS_SECRET_ACCESS_KEY = '567' )
  URL = 's3://my-bucket/path/to/data'

Create a location with a description

The following code example creates a location object named my_location, for an Amazon S3 data source with the specified URL and description:

CREATE LOCATION my_location WITH
  SOURCE = AMAZON_S3
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '1231' AWS_SECRET_ACCESS_KEY = '567' )
  URL = 's3://my-bucket/path/to/data'
  DESCRIPTION = 'Main data storage location'

Create a location only if it doesn’t exist

The following code example uses an access key to authenticate to AWS using a location only if it doesn’t already exist:

CREATE LOCATION IF NOT EXISTS my_location WITH
  SOURCE = AMAZON_S3
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '1231' AWS_SECRET_ACCESS_KEY = '567' )
  URL = 's3://my-bucket/path/to/data'

Use a location to load data into an external table

The following code example creates an external table my_ext_table that uses a previously created location my_location to load Parquet data files matching the *.parquet pattern from Amazon S3:

CREATE EXTERNAL TABLE my_ext_table (
  c_id    INT,
  c_name  TEXT
)
LOCATION = my_location
OBJECT_PATTERN = '*.parquet'
TYPE = PARQUET

For more information about using locations in external tables, see CREATE EXTERNAL TABLE.

Use a location to load data using COPY statements

The following code example uses COPY FROM to load Parquet data files matching the *.parquet pattern from my_location into my_table:

COPY INTO my_table
FROM my_location
WITH
  OBJECT_PATTERN = '*.parquet'
  TYPE = PARQUET

For more information, see COPY TO and COPY FROM.

Use a LOCATION to load data with a TVF

You can use LOCATION to load data using table-valued functions (TVFs) such as READ_CSV, READ_PARQUET, and LIST_OBJECTS.

The following code example uses READ_CSV to query data from a CSV file stored in my_location, using the first row as headers for column names:

SELECT * FROM READ_CSV(
  LOCATION => 'my_location',
  header => TRUE
)

Alter a location

Firebolt does not yet support altering a location that has been created. This feature will be available in a future release.

Delete a location

You can use DROP LOCATION to remove a location from your Firebolt account.

The following code example deletes a LOCATION from your Firbolt account:

DROP LOCATION [IF EXISTS] <location_name> [WITH FORCE]

Deleting a location will affect all objects that depend on the LOCATION that you are dropping.

Notes

• All identifiers are case-insensitive unless enclosed in double-quotes
• For more information about object identifiers, see Object identifiers

Error Handling

The system uses secure error handling for location objects:

• Generic error messages ensure security by not exposing sensitive information.
• Detailed error information is only available to users with the necessary permissions.
• Users without the required permissions are provided with instructions to contact an administrator.

Best Practices

1. Use location objects instead of embedding credentials directly in queries or table definitions.
2. Regularly rotate credentials stored in location objects.
3. Follow the principle of least privilege when assigning permissions.
4. Use clear and descriptive names and descriptions for location objects.
5. Keep a record of dependencies before removing any location objects.

Limitations

• The DROP CASCADE functionality is not supported.
• The source type cannot be modified for existing location objects.
• Location objects cannot be used alongside inline credentials in the same statement.
• Historical versions of credentials are not maintained.