Create a database connection

Note:

Database connections are supported in Insights in ArcGIS Enterprise and Insights desktop.

A database connection allows you to work with database tables directly in Insights. Database connections can be created to supported databases and additional relational databases that use a Java Database Connectivity (JDBC) driver.

Before you create a database connection, the following prerequisites must be met:

• The appropriate connector must be added before you can create a database connection. For more information, see Required vendor files and Manage connector types.
• You must have the appropriate privileges to the database you want to connect to. If you do not have database privileges, contact the database administrator.
• You must be able to authenticate the connection. For most databases, a username and password are used for authentication. SQL Server and BigQuery use the following alternate authentication methods:
  • SQL Server can use either SQL Server authentication (username and password) or OS authentication. For more information, see Enable OS authentication.
  • BigQuery uses service account authentication. For more information, see Create a service account and private key.

Create database connections

Database connections are saved as relational database connection items. You can view relational database connection items on the Connections tab on the home page or, if you are using Insights in ArcGIS Enterprise, in your ArcGIS Enterprise portal. Connections can be created from the home page or before you add data to your workbook.

Create a connection on the home page

Complete the following steps to create a database connection on the home page:

1. Click the Connections tab .
2. Click New connection and choose Database from the menu.

   The New connection window appears.

3. Choose the database type you want to connect to.

   Only database types available to your organization are listed. For more information, see Manage connector types.

4. Provide the connection properties for the database.

   Required fields are marked with an asterisk (*).

5. Click Add.

   Insights uses the connection properties to try to connect to the database. Once successful, the connection appears on the Connections page.

   If Insights can't connect to the database, an error message appears. See Troubleshoot a database connection for more information about the reasons a connection cannot be established.

A relational database connection is created. The connection can be updated on the Connections tab on the home page. For more information, see Update a database connection.

As the owner of the database connection in Insights in ArcGIS Enterprise, you can share the item with team members so that they can use the data from the database connection in their analysis.

Create a connection in the Add to page window

Complete the following steps to create a database connection in the Add to page window before you add data to your workbook:

1. Open the Add to page window by doing one of the following:
   • Create a workbook.

     The Add to page window appears when the workbook is created.

   • Click the Add to page button above the data pane in an existing workbook.
   • Create a page in an existing workbook.

     The Add to page window appears when the page is created.

2. In the Add to page window, choose Database.
3. Click New connection.
4. Provide the connection properties for the database.

   Required fields are marked with an asterisk (*).

5. Click OK.

   Insights uses the connection properties to try to connect to the database. If connecting to the database is possible, the connection is created, and datasets from the database are listed in the middle pane. You can choose datasets from the new connection to add to your workbook page.

A relational database connection is created. The connection can be updated on the Connections tab on the home page. For more information, see Update a database connection.

As the owner of the database connection in Insights in ArcGIS Enterprise, you can share the item with team members so that they can use the data from the database connection in their analysis.

Connection properties

Database connections include both required and optional properties. Required properties are marked in the New connection window with an asterisk (*).

The table below describes the properties that are used for at least one supported database type.

Note:

The properties for connections to additional databases that are not on the list of supported databases are configured in the YAML files (properties.yml) that are used to add the connector type. The properties in the New connection window for additional databases may not correspond to the properties in this table.

Property	Description	Databases
Name	The alias that will be assigned to the database connection. The Name property does not need to correspond to the database name.	Required for all databases.
Type	The database type, for example, Oracle or SQL Server. Only connectors that are available for your organization are listed. For more information, see Manage connector types.	Required for all databases.
Default spatial type	The spatial type used when a location field is added to a database dataset using enable location.	Required for Oracle and PostgreSQL.
Connection method	The method for connecting to an Oracle database. The Connection method value can be Basic or TNS. The basic method creates a connection using a server name, instance name, and port number. The TNS method creates a connection using a TNS file path and alias.	Required for Oracle.
Server name	The database host name.	Required for Oracle databases using the basic connection method, PostgreSQL, SAP HANA, and SQL Server databases using SQL Server authentication.
Server name (Fully qualified domain name)	The fully qualified domain name from the ArcGIS Server machine for your organization.	Required for SQL Server databases using OS (Windows) authentication.
Instance name	The instance of the database you are connecting to.	Required for Oracle databases using the basic connection method. This property is optional for SQL Server.
TNS file path	The path where the TNS file is saved on your computer (for example, C:\oracle\network\admin). The path does not include the TNS file name (tnsnames.ora).	Required for Oracle databases using the TNS connection method.
TNS alias	The alias in the TNS file that defines the database you are connecting to.	Required for Oracle databases using the TNS connection method.
Port number	The port number for the database you are connecting to.	Required for SAP HANA. This property is optional for Oracle databases using the basic connection method, PostgreSQL, and SQL Server.
Authentication method	The method of verifying the database connection. The Authentication method can be either SQL Server or Windows.	Required for SQL Server.
Project	The project where your BigQuery dataset is stored.	Required for BigQuery.
Domain or username	The domain name or username you are using to authenticate the database connection. For SQL Server connections using Windows authentication, use a domain name. For all other databases and authentication methods, use a username.	Required for Oracle, PostgreSQL, SAP HANA, and SQL Server.
Password	The password that corresponds to the username you are using.	Required for Oracle, PostgreSQL, SAP HANA, and SQL Server databases using SQL Server authentication.
Service account email	The email address connected to your Google service account.	Required for BigQuery.
Private key	The private key for your Google service account.	Required for BigQuery.
Connect using SSL	Enable connecting to a SAP HANA database using Secure Sockets Layer (SSL).	Optional for SAP HANA.
Database name	The name of the database you are connecting to.	Required for SQL Server and PostgreSQL. Optional for SAP HANA.
Dataset name	The default dataset for the database connection. This schema or dataset is used to store temporary tables created during analysis workflows for read-write connections. If no schema or dataset is provided, or if the user does not have read-write permissions to the schema or dataset, the database connection will be read-only.	Optional for BigQuery.

Index columns

Insights will create an index on database tables for certain functions using Oracle, SQL Server, or PostgreSQL databases to improve performance. For Oracle and SQL Server databases, indexing can only take place if the user who creates the database connection has the required database privileges. Once the connection is created, indexes will be created on the database tables regardless of who is using them in Insights.

If the user who creates the database connection does not have the necessary privileges, indexing will not take place on the database tables.

The following table describes how indexing is performed for each database type and the privileges required to create a connection where indexing takes place:

Database	Description	Index type	Required privileges
SAP HANA	Indexing is not completed in Insights for SAP HANA databases.	Not applicable	Not applicable
Oracle	Indexing occurs in the following situations: A field is calculated on the dataset. A relationship is created with the dataset. Enrich Data is used on the dataset. Location is enabled on the dataset, including through results from spatial analysis. The data is projected to a different geographic coordinate system. Data is projected if the coordinate system of the dataset does not match the coordinate system of the basemap.	Nonunique	Connect to and browse contents with data caching. One of the following: The user is the owner of the table. The owner of the table granted the CREATE INDEX ON <table name> privilege to the user.
SQL Server	Indexing occurs in the following situations: A filter is applied to the dataset. A relationship is created with the dataset. Enrich Data is used on the dataset. Location is enabled on the dataset, including through results from spatial analysis.	Nonunique	Connect to and browse contents with data caching.
PostgreSQL	Indexing occurs in the following situations: A filter is applied to the dataset. A relationship is created with the dataset. A time series graph is created.	Nonunique	Connect to and browse contents with data caching.
BigQuery	Indexing is not completed in Insights for BigQuery databases.	Not applicable	Not applicable

Spatial types

The default spatial type is used to determine the spatial type that will be used when a location field is added to a database dataset using enable location. The following table describes the default spatial types that are available for each database type:

Database	Supported default spatial types
SAP HANA	ST_Geometry
Oracle	Oracle Spatial Esri Geodatabase
SQL Server	Geometry
PostgreSQL	PostGIS Geometry Esri Geodatabase None
BigQuery	Geography

Note:

SDELOB spatial types are not supported.

Resources

For creating and troubleshooting enterprise geodatabases in Oracle, see Geodatabases in Oracle.

For creating and troubleshooting enterprise geodatabases in Microsoft SQL Server, see Geodatabases in Microsoft SQL Server.

For creating and troubleshooting enterprise geodatabases in PostgreSQL, see Geodatabases in PostgreSQL.

Next steps

Now that you've created a database connection, you're ready to share the connection with your organization or add data to a workbook.