Cloud data warehouses are used to store tremendous volumes of both structured and semi-structured data and are designed for analytics. With ArcGIS Pro and ArcGIS Enterprise, users can integrate data from three popular cloud data warehouses including Amazon Redshift, Google BigQuery, and Snowflake to support visualization and analysis workflows, leading to greater insights.
Most often, organizations want to power their web maps and web apps using information stored in their cloud data warehouse. In ArcGIS Pro, publishers can share feature layers and map image layers to ArcGIS Enterprise and configure the web layers to access data directly, without the need to copy any data to ArcGIS.
In this blog, we share a few best practices as they relate to integrating data from cloud data warehouses with ArcGIS Pro and ArcGIS Enterprise.
Quick links
Use the links below to navigate to a section of interest:
- Getting Started
- #1: Co-locate ArcGIS with your cloud data warehouse
- #2: Ensure your query layers are analytical in nature
- #3: Verify field data type compatibility with ArcGIS
- #4: Take advantage of feature binning
- #5: When sharing layers to ArcGIS Enterprise, use a materialized view or a snapshot
- Summary and Resources
Getting Started
To connect to data from Snowflake, BigQuery, or Redshift, you will need ArcGIS Pro 2.9 and ArcGIS Enterprise 10.9.1 or later. To publish web layers to ArcGIS Enterprise, ensure you’re signed in to ArcGIS Pro as a member of ArcGIS Enterprise who has publishing privileges within the organization.
The client files or driver for the cloud data warehouse you are using will need to be installed on the ArcGIS Pro machine you are using to establish the database connection from, as well as the machines where ArcGIS Server is installed. Typically, this work is handled by your IT department or ArcGIS administrators.
For more information on the requirements and steps to connect to a cloud data warehouse from ArcGIS Pro and ArcGIS Enterprise, please see this documentation:
- Connect to Amazon Redshift from ArcGIS Pro
- Connect to Google BigQuery from ArcGIS Pro
- Connect to Snowflake from ArcGIS Pro
- Configure a database connection for ArcGIS Server
Note: If your organization is using ArcGIS Enterprise on Kubernetes, then you will not need to install any drivers, as these are already configured for you.
Let’s now discuss a few good practices, starting with where ArcGIS is running.
Best Practice #1: Co-locate ArcGIS with your cloud data warehouse
Over the past few years, we have had more conversations than we can count about challenges with performance when integrating data from a cloud data warehouse with ArcGIS Pro and ArcGIS Enterprise. Why? Because ArcGIS Pro and ArcGIS Enterprise are on-premises and the data being accessed is in the cloud.
Why is this an issue? Latency. This is stated in many places across the ArcGIS documentation:
Connections from ArcGIS software to databases or data warehouses in the cloud must originate from machines in the same cloud. For best performance, connections should originate from machines in the same cloud region. In the majority of cases, connection and query performance is poor when you connect from ArcGIS clients installed on-premises to databases or data warehouses in the cloud, connect from ArcGIS clients in the cloud to databases or data warehouses on-premises, or make connections between clients and databases or data warehouses in different cloud regions. In these scenarios, connections to the database may time out.
To summarize, you will see the best experience if you co-locate your ArcGIS Pro and ArcGIS Enterprise deployments in the same cloud region as your cloud data warehouse.
Best Practice #2: Ensure your query layers are analytical in nature
Cloud data warehouses store tremendous amounts of data, and it’s not uncommon to have a table that contains hundreds of millions of records. To effectively visualize or analyze this data in a GIS, like ArcGIS Pro or ArcGIS Enterprise, you should use SQL to define a query layer that aggregates that information into something meaningful.
For example, a traffic dataset could be brought into ArcGIS Pro using an analytical query layer that averages all speed readings taken between the hours of 7 and 9 am for each street segment.
The resulting layer could then be used to visualize and analyze morning rush hour traffic patterns. The original table contains more than 27 million speed readings captured at specific intersections and street segments in Raleigh, North Carolina. But because we grouped the data by street segment in the query layer definition, ArcGIS Pro only needs to display 8,701 features.
As you see in the above example, spatial functions supported by the respective cloud data warehouse can be used as part of your query layer definition in ArcGIS Pro, including spatial functions.
Refer to the following resources for examples:
- Amazon Redshift: Spatial functions
- Google BigQuery: Geography functions
- Snowflake: Geospatial functions
While cloud data warehouses can be queried row-by-row, they are not optimized for this kind of interaction and can lead to frustrating experiences if a user attempts to display a table with a million rows on a map in ArcGIS. For this reason, make sure you’re defining query layers that are either summarizing data or are analytical in nature.
To learn more about query layers in ArcGIS, please refer to this ArcGIS Blog: An introduction to query layers and the technical documentation on Create a query layer for data in a cloud data warehouse.
Best Practice #3: Verify field data type compatibility with ArcGIS
ArcGIS supports specific data types. When data is integrated from a user-managed database, like a cloud data warehouse, every field (or column) you bring in as part of your query layer definition must have a data type that maps to an ArcGIS field data type. If your table includes a data type that is not supported in ArcGIS, you will need to either eliminate the field from your query layer or you can convert it to a compatible data type using a cast function in the query layer definition.
For example, an insurance company stores property insurance statuses in a Snowflake table using the BOOLEAN data type, with values of True or False to indicate whether a property is currently insured. Since ArcGIS does not support Snowflake’s BOOLEAN data type, this field can be easily cast to the VARCHAR data type, which maps to the ArcGIS Text field data type.
The list of data types from the supported cloud data warehouses and their associated ArcGIS data type that they will be mapped to when integrated with ArcGIS Pro and ArcGIS Enterprise are detailed here: Cloud data warehouse data types supported in ArcGIS.
Best Practice #4: Take advantage of feature binning
Did you know that ArcGIS Pro includes an Enable Feature Binning tool that can be used to aggregate large amounts of point features into dynamic polygons called bins? Well, it’s true! While users can set the display of a feature layer to draw using bins client-side by modifying the display properties of a layer in ArcGIS Pro or Map Viewer, database-driven feature binning is much more performant when working with hundreds of thousands or millions of features in a database or cloud data warehouse. This is because the feature bins are calculated in advance and stored within the respective database, instead of being generated on-the-fly as the data is being displayed.
To learn more about binned feature layers, see this documentation: Aggregate features into bins.
Best Practice #5: When sharing layers to ArcGIS Enterprise, use a materialized view or a snapshot
When a web layer is shared from a cloud data warehouse to ArcGIS Enterprise, the publisher has options for how the data should be accessed. The options are to access the data directly, create a materialized view, or to create a snapshot.
What’s the difference? Well, we are glad you asked.
When creating a query layer in ArcGIS Pro from a cloud data warehouse, you will be presented with the option to create a materialized view.
A materialized view will store a copy of the query results set within the cloud data warehouse, and ArcGIS Pro and ArcGIS Enterprise will reference this instead of querying the tables directly. This provides an advantage from a performance perspective, especially for complex queries. If data is changing, the materialized views will be updated behind the scenes, with little to no delay. This also provides an advantage from a cost perspective, as reduced compute usage for each query generally results in fewer compute units or dollars spent on compute in the cloud data warehouse software.
If you do not create a materialized view, the query layer in ArcGIS Pro will result in the full query used to define the layer being used each time the layer is accessed. For complex queries, this can result in poor performance when compared to accessing the query results set that could have been persisted using the option to create a materialized view. That said, the benefit is that the results return real-time information without any delay.
When publishing to ArcGIS Enterprise, the Configuration tab of the Share as Web Layer pane will present the users with the options for how the web layer should (or should not) reach out to and communicate with the cloud data warehouse. If a materialized view was created when defining the query layer, that will be the default option. Otherwise, it will be configured to access the data directly. Regardless of which is chosen, there is another option presented, unique to ArcGIS Enterprise, which is to create a snapshot.
If you choose to create a snapshot, you are telling the software to take the copy of the query results set and store it in ArcGIS Enterprise to support the web layer. With this option selected, interaction with the web layer will not result in any queries being pushed to the cloud data warehouse.
Sharing the data as a snapshot is a great option if the data is static, or if you are unable to co-locate ArcGIS Enterprise in the same cloud region as your cloud data warehouse. This is because the query results set supporting the layer is now co-located with ArcGIS Enterprise, stored in the relational data store. If the data needs to be updated, snapshots can be refreshed from the item page, settings tab. And, in ArcGIS Enterprise 11.3 we also added the option to schedule refreshes to snapshots, making this even easier.
To summarize, when sharing layers to ArcGIS Enterprise from cloud data warehouses, it is generally a best practice to configure the layers to access the data using either a materialized view or a snapshot. Otherwise, the full SQL query is going to be run against the tables in the cloud data warehouse anytime the web layer is accessed.
Summary and Resources
We hope this information has been helpful. What did you learn? Did we miss any tips you’d like to share? To share your thoughts and feedback, or to explore additional resources, please visit this related ArcGIS Community Blog.
Commenting is not enabled for this article.