Create a query layer for data in a cloud data warehouse—ArcGIS AllSource

Create a query layer from the New Query Layer dialog box to access data in a cloud data warehouse. For best performance, install the ArcGIS AllSource client you use to query the data warehouse in the same cloud environment and region as the data warehouse.

Tip:

This page explains how to create a query layer for a table in a cloud data warehouse. To access data in a database, see Create a query layer for data in a database.

Prerequisites and workflow

Before you create a query layer, read Database and client configurations for query layers.

Next, read the subsections below to gain an understanding of the workflow to follow when creating a query layer for a cloud data warehouse using the New Query Layer dialog box.

Connect to the data warehouse

Before you can define a query layer, you must connect to the data warehouse.

You can create a connection in the Catalog pane (as described in Connections to cloud data warehouses in ArcGIS AllSource), add it to the project, and browse to it from the New Query Layer dialog box, or you can create the connection from the New Query Layer dialog box.

Define an SQL query

Specify a structured query language (SQL) query in the Query text box to access the data you need from data in the warehouse. When you access a table or view that contains a large number of rows, the query you define should limit the number of rows that will be included in the layer. For example, you cannot truly view millions of features on a map if the features are not aggregated into bins. For those types of tables or views, use the query to return a subset of the data.

Use the SQL syntax specific to the data warehouse when you build the query layer.

Each column in a table has a specific data type. ArcGIS can work with most common data types. However, some less common data types are not supported. If the attribute column type is unknown, this indicates that ArcGIS does not support that data type. When you specify a query, all columns that have an unknown data type must either be excluded or changed in the query to a data type that ArcGIS supports.

See Cloud data warehouse data types supported in ArcGIS for more information.

Tip:

To learn how to use variables in SQL queries, see Define parameters in a query layer.
If you don't have an SQL query prepared and you need to see a list of tables, check the List of tables box. You can double-click the tables and columns that appear in the list to construct an SQL query.
Drawing and query performance may be slow if you include external tables in the query.

Create a materialized view

When you access tables that contain large volumes of data in a data warehouse and define an SQL query that restricts the total features returned by the query to a reasonable number, store that query as a materialized view in the database to improve query performance when you access the data.

When you create a materialized view in the New Query Layer dialog box, the view definition is the SQL query you specified for the query layer. The query layer accesses the materialized view rather than executing the SQL query every time you work with the query layer.

If the materialized view is created in Google BigQuery, you can configure the materialized view to expire. Do this to avoid charges for storage of the materialized view if you don't need the view for more than a few days.

You cannot create a materialized view if you include variables in the SQL query or if the SQL query is defined for a view. You cannot create a materialized view if you include an external table in the query.

Tip:

To see the name of the materialized view associated with a query layer, open the Layer Properties dialog box for the query layer in the map. Do not delete or alter this view in the cloud data warehouse.

For information about how materialized views are supported in a specific data source, see the documentation provided by your cloud data warehouse vendor.

Validate the SQL statement

After you define an SQL query and create a materialized view, validate the SQL statement. During validation, ArcGIS uses the properties of the first row returned in the table to determine the validity of the query.

ArcGIS also uses the properties of the first row returned by the query to filter which other rows will display in the map to meet ArcGIS requirements. For example, ArcGIS supports only one spatial reference in a spatial table. If the features in your feature class use different spatial reference IDs (SRID), by default, the query layer will return only those rows that have the same SRID as the first feature returned by the query. Similarly, ArcGIS supports only one geometry type per table. By default, only features with the same geometry type as the first row returned by the query will display on the map. To use a different SRID or geometry type, define the spatial properties of the query layer.

Specify a unique identifier

The unique identifier is one or many columns used by ArcGIS to identify each row in the table.

During validation, ArcGIS attempts to identify a single unique identifier column for the table. If ArcGIS finds a qualifying column, you can use it for the query layer. If ArcGIS cannot find a unique identifier or you want to use a different unique identifier, you can define one using the New Query Layer dialog box.

Define spatial properties (optional)

For feature classes or views that contain a spatial column, you can define the following or use the defaults detected by ArcGIS when you validate:

Geometry type—This determines whether the layer stores point, multipoint, line, or polygon features.
Spatial reference—This is the coordinate system and other related spatial properties for the layer.
Note:
Defining a spatial reference for the query layer does not reproject the data; it only defines the spatial reference that should be used when mapping the results of the query in ArcGIS.
You cannot define a spatial reference when querying Google BigQuery.
SRID—This is the layer's spatial reference identifier and is used to ensure that only geometries with the same spatial reference identifier are returned by the query. The SRID value is blank if the query layer does not have a spatial field. If this value is set, all features that do not have the specified geometry SRID value are excluded from the result set.

Create a query layer that accesses data in a cloud data warehouse

Complete the following steps to define a query layer in a map in an ArcGIS AllSource project using the New Query Layer dialog box:

In ArcGIS AllSource, open the map to which you want to add the query layer.
Click the Add Data button on the Map ribbon and select the Query Layer button from the drop-down list.
The New Query Layer dialog box appears.
Do one of the following to specify a connection:
- If the connection file (.sde) already exists in the project, select it from the drop-down list.
- If the connection does not exist in the project, select the new database connection button and create a connection.
In the Name text box, specify a name for the query that will be created.
This is the name that will appear in the Contents pane of the map.
Type or paste an SQL query in the Query text box.
To see a list of tables and their columns, check the List of Tables check box. You can double-click a table in the List of Tables section to add all the columns in the table to the Query text box and edit the query in the Query text box. For example, you can delete the columns from the query that you do not want included and add clauses to the query to limit the data included in the query layer.
Choose how the layer's spatial properties will be defined.
- Let ArcGIS Pro discover spatial properties for the layer—ArcGIS AllSource uses the spatial properties of the first row returned by the query. ArcGIS AllSource determines these properties when you validate the SQL statement. This is the default.
- Define spatial properties for the layer—If you choose this option, you must specify the spatial properties to use for the layer.
Check the Create a materialized view of the output query layer check box to store the SQL query as a materialized view in the cloud data warehouse.
When you create a materialized view in BigQuery, use the up or down arrows to specify the number of days you want the materialized view to remain in the data warehouse.
After the number of days you specify, the materialized view will be deleted. If you open the map that contains the query layer after this expiration date, ArcGIS re-creates the materialized view with the same query definition and expiration as before.
Click Validate to confirm that the query syntax is correct and that ArcGIS can use the query.
This executes the query in the database and verifies whether the result set from the query meets the data modeling standards enforced by ArcGIS. A query layer is not added to the map until it is valid.
The following rules for validation apply:
- The result set must have, at most, one spatial field.
- The result set must have, at most, one spatial reference.
- The result set must have only one shape type.
- The result set cannot have field types that ArcGIS doesn't support.
If the validation fails for any reason, an error message is returned so you can modify the query.
Click Next when validation completes.
Select the unique identifier field for the query layer.
All features in the map must have a unique identifier. Therefore,the query layer must contain a unique identifier field that contains a value for each feature. See Unique identifier fields to learn more about choosing a unique identifier field for a query layer.
Define the spatial properties of the query layer.
These parameters may already be set if you chose Let ArcGIS Pro discover spatial properties for the layer in step 6.
1. If you define a query layer on Redshift, define whether to include features that include m-coordinates or z-coordinates.
2. Choose the geometry type from the drop-down menu.
3. Click Coordinate System and choose the coordinate system of the spatial reference used by the data, or import the spatial reference from an existing dataset.
  Remember, this does not reproject the data.
  You cannot choose a coordinate system when defining a query layer on data in BigQuery or Snowflake.
If you chose Let ArcGIS Pro discover spatial properties for the layer in step 6, click Finish to add the query layer to the map.
If you chose Define spatial properties for the layer in step 6, complete the additional section that appears on the dialog box.
In this section—Layer Extent Properties—specify the query layer's spatial extent by choosing one of the following:
- Input Extent—An extent you provide is used for the query later extent. Click Next, and type the extent. The extent you provide must be valid and include all the features in the table.
- Use Map Extent—The extent of the current map is used for the query layer extent. Click Next to review the map extent values.
- Use Spatial Reference Extent—The extent of the spatial reference of the data is used for the query layer extent. For some spatial references, this is the entire globe. Click Next and review the spatial reference extent values.
When you finish setting or reviewing the layer extent, click Finish to add the query layer to the map.
If you chose to create a materialized view, the query layer is defined on the materialized view rather than the source table.