Note:
Database connections are supported in Insights in ArcGIS Enterprise and Insights desktop.
Database connections allow you to connect directly with your database tables in Insights. The tables below list the supported databases and versions you can connect to when using Insights.
Google BigQuery
BigQuery is a cloud-based database that updates automatically. BigQuery is supported in the following versions of Insights:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
Supported* | Not supported | |||
*Spatial capabilities are not supported at Insights 2021.1. Connections are read-only at Insights 2021.2.
Note:
Insights uses service account authentication when connecting to BigQuery. A service account with an Editor, Owner, or Viewer role is required to create a connection from Insights to BigQuery. For more information, see Create a service account and private key in the Insights connector guide.
Limitations
String fields in tables from BigQuery connections cannot be converted to date/time fields from the data pane or using a DATEVALUE calculation that does not specify a date format in Insights 2021.3 and lower. Date and time data should be stored in the database using a valid date or time data type or calculated using an expression that includes a valid date format.
BigQuery uses approximate calculations for median and percentile. Therefore, median and percentile statistics calculated on datasets from BigQuery may not match the results seen from other data sources. Median and percentile statistics are available on summary tables and some charts.
Note:
The median value on a box plot represents the true median, rather than the approximate median calculated by BigQuery.
BigQuery does not support mode calculations for Spatial Aggregation or Spatial Mean.
BigQuery only supports spatial reference 4326 (World Geodetic System 1984). Therefore, only spatial reference 4326 is supported when enabling location with coordinates. To enable location using a different spatial reference, you must first copy the data to your workbook.
Partitioned tables from BigQuery are not supported. A filtered view must be created to add data from a partitioned table.
The following spatial capabilities are not currently supported for read-only connections:
- Use Spatial Aggregation with lines or areas in the Choose a layer to summarize parameter.
- Use Enable location.
- Use Calculate Density.
- Use Calculate Density Ratio.
- Use K-Means Clusters.
- Use Spatial Mean.
- Use Enrich Data.
Microsoft SQL Server
The following table lists the supported versions of Microsoft SQL Server:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
Standard/Enterprise editions:
| Standard/Enterprise editions:
| |||
Cloud database as a service:
| ||||
Oracle
The following table lists the supported versions of Oracle:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
Standard/Standard One/Enterprise editions:
| Standard/Standard One/Enterprise editions:
| Standard/Standard One/Enterprise editions:
| ||
Note:
Oracle 11g supports read-only functionality. Other Oracle database versions support read/write functionality.
When Oracle connections using TNS are used in a multiple ArcGIS Server deployment, all ArcGIS Server machines must have the same Oracle configuration, including the following:
- The same TNS file path
- The same content in the TNS file (tnsnames.ora)
Advanced string matching
Advanced string matching logic uses case-insensitive string comparisons and matches null and empty strings. The logic improves outputs for joins on string fields, filters on string fields, and field calculations using equal to (=) or not equal to (<>) on string fields, but negatively affects database performance.
Advanced string matching functionality in Oracle databases is used in Insights 2020.3 and earlier but has been deprecated for Insights 2021.1 and higher. Connections created in Insights 2020.3 and earlier will continue to use advanced string matching unless the property is changed manually.
An ArcGIS administrator can manually override the advancedStringMatching property for the RelationalCatalogServer in the ArcGIS Server Administrator Directory.
PostgreSQL
The following table lists the supported versions of PostgreSQL:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
Postgres PostGIS support:
| Postgres PostGIS support:
| Postgres PostGIS support:
| ||
PostgreSQL geodatabase support:
For PostgreSQL databases without spatial extensions, only versions 9.6, 10, and 11 are supported. | ||||
SAP HANA
The following table lists the supported versions of SAP HANA:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
|
|
| ||
Note:
SAP HANA modeling views are not supported.
Snowflake
Snowflake is a cloud-based database that updates automatically. Snowflake is supported in the following versions of Insights:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
Supported* | Not supported | |||
*Spatial capabilities are not supported at Insights 2021.1. Connections are read-only at Insights 2021.2.
Limitations
String fields in tables from Snowflake connections cannot be converted to date/time fields from the data pane or using a DATEVALUE calculation that does not specify a date format in Insights 2021.3 and lower. Date and time data should be stored in the database using a valid date or time data type or calculated using an expression that includes a valid date format.
Snowflake only supports spatial reference 4326 (World Geodetic System 1984). Therefore, only spatial reference 4326 is supported when enabling location with coordinates. To enable location using a different spatial reference, you must first copy the data to your workbook.
Joins are not currently supported for tables from Snowflake. You can create a view with the required join within Snowflake and use the view in Insights.
The following spatial capabilities are not currently supported for read-only connections:
- Use Spatial Aggregation with lines or areas in the Choose a layer to summarize parameter.
- Use Enable location.
- Use Buffer/Drive Times.
- Use Calculate Density.
- Use Calculate Density Ratio.
- Use K-Means Clusters.
- Use Spatial Mean.
- Use Enrich Data.
Enterprise geodatabases
Insights in ArcGIS Enterprise and Insights desktop allow you to create a connection to a supported Microsoft SQL Server, Oracle, SAP HANA, or PostgreSQL database that has a non-versioned enterprise geodatabase defined. If the geodatabase is versioned, the data must be unregistered as versioned to work in Insights. Currently, only geodatabase user tables not created under the sde user schema can be viewed and accessed from Insights. Insights does not work directly with file and personal geodatabases.
Additional database platforms
In addition to the supported databases, connectors can also be added for other relational databases that use a Java Database Connectivity (JDBC) driver. See the Insights connector guide for more information about the required configuration files and connectors that have been certified in Insights.
Additional database connectors are available in the following Insights versions:
| Insights 2022.1 | Insights 2021.3 | Insights 2021.2 | Insights 2021.1 | Insights 2020.3 |
|---|---|---|---|---|
Supported | Preview* | Not supported | ||
*Spatial capabilities are not supported at Insights 2021.1. Connections are read-only at Insights 2021.2.
Limitations
Insights only supports client-server database systems that can be accessed over a network. Other types of database systems, such as embedded databases, are not supported.
String fields cannot be converted to date/time fields from the data pane or using a DATEVALUE calculation that does not specify a date format for these connectors. Date and time data should be stored in the database using a valid date or time data type (configured in the dialect.yml file) or calculated using an expression that includes a valid date format. See the Insights connector guide for more information on configuring data types.
The following spatial capabilities are not currently supported for read-only connections:
- Use Spatial Aggregation with lines or areas in the Choose a layer to summarize parameter.
- Use Enable location.
- Use Buffer/Drive Times.
- Use Calculate Density.
- Use Calculate Density Ratio.
- Use K-Means Clusters.
- Use Spatial Mean.
- Use Enrich Data.
- Calculate geometry is only supported for read-only connections if the database itself supports calculating geometry (length, area, and perimeter) and the geometricProperties capability is set to true in the SQL dialect configuration file.
Resources
For more information about databases, see the following:
Next steps
Now that you know which databases are supported, the next step is to download the required vendor files for your database type. For more information, see Required vendor files.