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 NameData TypeDescription
location_nameTEXTThe name of the location.
sourceTEXTThe type of the external data source. Firebolt currently supports AMAZON_S3 and ICEBERG.
urlTEXTThe 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.
descriptionTEXTOptional metadata describing the location’s purpose.
location_ownerTEXTThe owner of the location.
createdTIMESTAMPTZThe timestamp when the location was created.
ddlTEXTThe 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 = AMAZON_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  | AMAZON_S3 | CREATE LOCATION "my_s3_location" WITH SOURCE=AMAZON_S3 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.