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 available for all supported Insights versions.
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 2023.1 | Insights 2022.3 | Insights 2022.2 | Insights 2022.1 |
|---|---|---|---|
Standard/Enterprise editions:
| |||
Cloud database as a service:
| |||
Note:
It is best practice to keep your service packs up-to-date. If you experience issues using tables from SQL Server, verify that your database instance has the latest service pack installed and update the service packs, if necessary.
Oracle
The following table lists the supported versions of Oracle:
| Insights 2023.1 | Insights 2022.3 | Insights 2022.2 | Insights 2022.1 |
|---|---|---|---|
Standard/Standard One/Enterprise editions:
| |||
Note:
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)
If an entry in the TNS file is updated, you must restart ArcGIS Server for the changes to take effect in Insights.
PostgreSQL
The following table lists the supported versions of PostgreSQL:
| Insights 2023.1 | Insights 2022.3 | Insights 2022.2 | Insights 2022.1 |
|---|---|---|---|
Postgres PostGIS support:
| Postgres PostGIS support:
| ||
PostgreSQL geodatabase support:
For PostgreSQL databases without spatial extensions, only versions 9.6, 10, and 11 are supported. | |||
Limitation
Enabling location by coordinates has reduced performance on tables from a PostgreSQL database owned by a user account other than the one used to create the database connection. For best results, database connections and database tables should have the same owner.
SAP HANA
The following table lists the supported versions of SAP HANA:
| Insights 2023.1 | Insights 2022.3 | Insights 2022.2 | Insights 2022.1 |
|---|---|---|---|
| |||
Note:
SAP HANA modeling views are not supported.
Snowflake
Snowflake is a cloud-based database that updates automatically. Snowflake is available for all supported Insights versions.
Limitations
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 allows 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.
Geodatabase administrators have access to system tables that cannot be used in Insights. Therefore, it is best practice to create connections to enterprise geodatabases using an account with a role other than administrator (data creator, publisher, editor, analyst, or viewer). The analyst role is recommended.
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 all supported Insights versions.
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.