Locations

You can use the information_schema.locations view to return information about each location in your Firebolt account. The view contains one row for each location. Use a SELECT query to return information about each location as shown in the example below. For a comprehensive guide to LOCATION objects including concepts, usage patterns, and best practices, see LOCATION objects.

SELECT
  location_name,
  source,
  url,
  description,
  location_owner,
  created,
  ddl
FROM
  information_schema.locations;

Columns in information_schema.locations

Each row has the following columns with information about each location:

Column Name	Data Type	Description
location_name	TEXT	The name of the location.
source	TEXT	The type of the external data source. For locations created with `CLOUD_STORAGE` (with an S3 URL) or `AMAZON_S3`, the source column displays `CLOUD_STORAGE (S3)`. Firebolt also supports `ICEBERG` and `AMAZON_BEDROCK`.
url	TEXT	The data source URL. For Amazon S3, the format is `s3://{bucket_name}/{path}`. For Iceberg, this may be an S3 URL for file-based catalogs or an API endpoint for REST catalogs.
description	TEXT	Optional metadata describing the location’s purpose.
location_owner	TEXT	The owner of the location.
created	TIMESTAMPTZ	The timestamp when the location was created.
ddl	TEXT	The CREATE LOCATION statement used to create this location, with credentials masked for security.

Examples

View all locations

The following query returns information about all locations in your account:

SELECT 
  location_name,
  source,
  url,
  description,
  location_owner,
  created,
  ddl
FROM 
  information_schema.locations;

View locations by source type

You can filter locations by their source type:

-- View only Amazon S3 locations
SELECT location_name, url, description, created
FROM information_schema.locations
WHERE source = 'CLOUD_STORAGE (S3)';

-- View only Iceberg locations  
SELECT location_name, url, description, created
FROM information_schema.locations
WHERE source = ICEBERG;

View location DDL statements

The ddl column contains the original CREATE LOCATION statement with credentials masked for security:

SELECT 
  location_name,
  source,
  ddl
FROM 
  information_schema.locations
WHERE location_name = 'my_location';

Example output:

location_name    | source              | ddl
----------------|---------------------|----------------------------------------------------
my_s3_location  | CLOUD_STORAGE (S3)  | CREATE LOCATION "my_s3_location" WITH SOURCE=CLOUD_STORAGE URL='s3://my-bucket/data/' CREDENTIALS=(AWS_ROLE_ARN='****')
my_iceberg_loc  | ICEBERG             | CREATE LOCATION "my_iceberg_loc" WITH SOURCE=ICEBERG CATALOG=REST CATALOG_OPTIONS=(URL='https://catalog.example.com/v1' WAREHOUSE='prod' NAMESPACE='analytics' TABLE='events') CREDENTIALS=(OAUTH_CLIENT_ID='****' OAUTH_CLIENT_SECRET='****')

Find locations by owner

SELECT 
  location_name,
  source,
  url,
  created,
  ddl
FROM 
  information_schema.locations
WHERE location_owner = 'admin_user'
ORDER BY created DESC;

Audit and troubleshooting with DDL

The ddl column is particularly useful for auditing and troubleshooting location configurations:

-- Find locations that use specific authentication methods
SELECT location_name, source, created
FROM information_schema.locations
WHERE ddl ILIKE '%AWS_ROLE_ARN%';

-- Find Iceberg locations using REST catalogs
SELECT location_name, url, created
FROM information_schema.locations
WHERE source = ICEBERG AND ddl ILIKE '%CATALOG='REST'%';

-- Find locations with descriptions
SELECT location_name, description, created
FROM information_schema.locations
WHERE description IS NOT NULL
ORDER BY created DESC;

Notes

All identifiers are case-insensitive unless enclosed in double-quotes.
The ddl column contains the original CREATE LOCATION statement with all credential values replaced with **** for security purposes.
For Iceberg locations using Databricks Unity catalogs, the url column may be NULL as the URL is specified within the catalog options.
The ddl column preserves the original parameter names and structure used when the location was created.
For more information about object identifiers, see Object identifiers.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Columns in information_schema.locations

Examples

View all locations

View locations by source type

View location DDL statements

Find locations by owner

Audit and troubleshooting with DDL

Notes

​Columns in information_schema.locations

​Examples

​View all locations

​View locations by source type

​View location DDL statements

​Find locations by owner

​Audit and troubleshooting with DDL

​Notes

Columns in information_schema.locations

Examples

View all locations

View locations by source type

View location DDL statements

Find locations by owner

Audit and troubleshooting with DDL

Notes