Manage Connections to Data Sources
You can create, update, remove, and share connections to data sources.
Topics:
Create a Connection to a Data Source
You can create a connection to enable you to analyze data in that data source.
Edit a Data Source Connection
You can update a data source's connection details.
If you’re editing an SSL connection to Oracle AI Database and you need to use a new cwallet.sso file, in the Client Wallet field, click Select to browse for the cwallet.sso file. Ask your administrator for the location of the cwallet.sso file.
Delete a Data Source Connection
You can remove a data source connection from Oracle Analytics Cloud. For example, you must delete a database connection and create a new connection when the database's password has changed.
If the connection contains any datasets, then you must delete the datasets before you can delete the connection.
- Go to the Data page and select Connections.
- Hover over the connection that you want to delete. To the right of the highlighted connection, click Actions, then select Delete.
- Click Yes.
Share a Data Source Connection
You can assign access permissions to the data source connections that you create or administer.
Database Connection Options
When you specify connection details using the Create Connection dialog or Inspect dialog, some database types have extra configuration options.
General Options
Location - The catalog folder where the connection details are stored. Use the Browse option to change the default folder (that is, My Folders). If you want to other users to be able to use the connection, save the connection in the Shared Folders area.
Authentication Options
- Always use these credentials - Oracle Analytics always uses the login name and password you provide for the connection. Users aren’t prompted to log in.
- Require users to enter their own credentials - Oracle Analytics prompts users to enter their own user name and password for the data source. Users can only access the data for which they have the permissions, privileges, and role assignments.
- (Displayed if Oracle Analytics supports impersonation for this database type) Use the active user’s credentials - Oracle Analytics doesn't prompt users to sign in to access the data. The same credentials they used to sign in to Oracle Analytics are also used to access this data source.
Note: If you don't click System connection when you initially create the connection, if you later want data modelers to use the connection details, then you have to create a new connection and select System connection. In other words, you can't edit the connection later and select this option.
Database Connection Limits
Understand your database connection requirements before your create the database connection.
The maximum number of database tables displayed in Oracle Analytics is 10,000. If you need additional tables, Oracle recommends that you ask your database administrator to create a database user with access to the specific objects you want to analyze and specify that user's credentials when you create the database connection.
Connect to Data with Upper, Lower, or Mixed-case Characters
If you're connecting to an Oracle database, Oracle Autonomous AI Lakehouse, Oracle Autonomous AI Transaction Processing, Snowflake, SQL Server, or My SQL, you can change the default quoting identifier so that you can read data with upper, lower, or mixed-case characters in table or column names.
select "EfG_Field" from "AbCd"; instead of issuing select EfG_Field from AbCd;, (which would fail).
- Click Advanced, then use the Quoting Identifiers option to select the quoting identifiers used in the database.For example, you might select Double Quotes (" "). Oracle Analytics adds double quotes to the underlying SQL statement
select "EfG_Field" from "AbCd";instead of issuingselect EfG_Field from AbCd;.Note:
You won't see the Advanced option if the connection was created with the System connection option selected. Advanced options aren't supported for system connections.
Manage Connections Using REST APIs
You can use Oracle Analytics Cloud REST APIs to programmatically manage connections to a range of data sources. For example, you might want to create a script that creates (or modifies) the same set of connections in both your test and production Oracle Analytics Cloud environments.
About Connection REST APIs
You can create, update, and delete connections for a range of data sources using REST APIs. This topic lists the types of data source connections you can manage with REST APIs.
Note:
provides detailed information about each REST API. See .Supported Data Sources
- Oracle Database and Oracle AI Database
- Oracle Autonomous AI Lakehouse
- Oracle Essbase
- MySQL
- PostgreSQL
- Snowflake
- SQL Server
- Vertica
Connection Parameters
The connection parameters required for each data source are different. If you want to use the REST API to create or update a connection, you must know the JSON payload format required for your data source. See Sample JSON Payloads for Data Sources.
Typical Workflow for Managing Connections Using REST APIs
Here are the common tasks to start using REST APIs to programmatically manage connections. If you’re using the REST APIs for the first time, follow these tasks as a guide.
| Task | Description | REST API Documentation |
|---|---|---|
| Understand prerequisites |
Understand and complete several prerequisite tasks. You must have permissions to create workbooks and connect to data in to manage data connections using REST APIs (DV Content Author). |
|
| Understand supported data sources | Read the topic that describes the types of data connections you can manage with REST APIs. | Supported Data Sources |
| Determine JSON payload formats | Read the topic that describes the JSON payload format for your data source and obtain the required connection parameters. | Sample JSON Payloads for Data Sources |
| Create a connection | Create a data connection for use in workbooks, reports, and dashboards. | |
| Update a connection | Update one or more properties of an existing data connection. | |
| Delete a connection | Delete a data connection. |
How to Use REST APIs to Manage Data Source Connections
Use these samples and examples to help you manage data source connections with REST API requests using cURL.
Sample cURL Format
-
Simple JSON
curl --header "Authorization: Bearer <token>" --header "Content-Type: application/json" --request POST|PUT https://<hostname>/api/20210901/catalog/connections --data "<data source connection payload>" -
Multi-part/Form data
curl --header "Authorization: Bearer <token>" --request POST|PUT https://<hostname>/api/20210901/catalog/connections --form "cert=<security wallet file>" --form "connectionParams=<data source connection payload>"
-
<token> - bearer token required to authenticate calls to REST APIs. See .
-
<hostname> - Host where is running.
-
<data source connection payload> - Data source-specific connection information. See Sample JSON Payloads for Data Sources.
-
<security wallet file> - Stores SSL-related information such as authentication and signing credentials, private keys, certificates, and trusted certificate. Required for some connection types, such as Oracle AI Database with SSL and Oracle Autonomous AI Lakehouse (Mutual TLS).
Examples
The following examples show you how to create a connection to Oracle Autonomous AI Lakehouse (ALK).
- Example 1 - Create a wallet-less (TLS) connection to Oracle ALK
- Example 2 - Create a connection to Oracle ALK that uses a credentials wallet file
cwallet.sso(Mutual TLS)
Note:
The JSON payloads in these examples are specific to Oracle ALK. The JSON payload format is different for other data sources. See Sample JSON Payloads for Data Sources.Example 1 Create a wallet-less (TLS) connection to Oracle Autonomous AI Lakehouse
oracle_ALK_walletless. The request body includes simple JSON application/json. curl
--header "Authorization: Bearer <token>"
--header "Content-Type: application/json"
--request POST https://example.com/api/20210901/catalog/connections
--data "{
"version": "2.0.0",
"type": "connection",
"name": "oracle_ALK_walletless",
"description": "Sample Oracle ALK connection without a wallet created using Connections API",
"content": {
"connectionParams": {
"connectionType": "oracle-autonomous-data-warehouse",
"connectionString": "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abcdefg1hijkl2m_ALKwalletless_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))",
"username": "ADMIN",
"password": "<<password>>",
"systemConnection": false,
"remoteData": false,
"sslType": "ServerSideSSL"
}
}
}"Response Body
{"connectionID":"J0FkbWluJy4nb3JhY2xlX2Fkd193YWxsZXRsZXNzJw=="}
Make a note of the Base64 encoded connectionId in the
response body. Later on, you can use this value to update or delete the
connection.
Example 2 Create a connection to Oracle ALK that uses a wallet file (Mutual TLS)
oracle_ALK_with_wallet. The request body includes multipart/form-data, that is, requires both a wallet file from Oracle ALK (cwallet.sso) and Oracle ALK connection parameters.curl
--header "Authorization: Bearer <token>"
-–request POST https://<hostname>/api/20210901/catalog/connections
--form "cert=@"/Users/scott/Downloads/Wallet_ALK/cwallet.sso""
--form "connectionParams= "{
"version": "2.0.0",
"type": "connection",
"name": "oracle_ALK_with_wallet",
"description": "Sample Oracle ALK connection with a wallet created using Connections API",
"content": {
"connectionParams": {
"connectionType": "oracle-autonomous-data-warehouse",
"connectionString": "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps) (port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abcdefg1hijkl2m_walletALK_high.ALKc.oraclecloud.com/))(security=(ssl_server_dn_match=yes)))",
"username": "ADMIN",
"password": "<<password>>",
"remoteData": "false",
"systemConnection": false,
"sslType": "ClientSideSSL"
}
}
}"Response Body
{"connectionID":"J2FkbWluJy4nb3JhY2xlX2Fkd193aXRoX3dhbGxldCc="}
Make a note of the Base64 encoded connectionId in the
response body. Later on, you can use this value to update or delete the
connection.
Additional examples are available in . See , and .
Sample JSON Payloads for Data Sources
To connect to data source you provide connection parameters. When you use the Connections REST API, you specify connection parameters in a JSON payload format. Use this table to determine the JSON payload for the data source you want to connect to.
| Data Source | Request Type | Input Payload |
|---|---|---|
|
Oracle AI Database (Non-SSL) |
application/json |
Basic Connection Type Advanced Connection Type |
|
Oracle AI Database with SSL |
multi-part/form-data |
Basic Connection Type |
|
Oracle Autonomous AI Lakehouse - Wallet-less (TLS) |
application/json |
Basic Connection Type |
|
Oracle Autonomous AI Lakehouse - Wallet (Mutual TLS) |
multipart/form-data |
Basic Connection Type |
|
PostgreSQL (Non-SSL) |
application/json |
Basic Connection Type |
|
PostgreSQL with SSL |
application/json |
Basic Connection Type |
|
SQL Server (Non-SSL) |
application/json |
Basic Connection Type |
| SQL Server with SSL | application/json |
Basic Connection Type |
|
MySQL (Non-SSL) |
application/json |
Basic Connection Type |
| MySQL with SSL | application/json |
Basic Connection Type |
|
Oracle Essbase |
application/json |
Basic Connection Type |