Connecting to Data Sources

Oracle Cloud Infrastructure Data Integration supports a range of data source types, from databases to flat files, that you can use as sources and targets for your integration tasks.

Use the following sections to learn about supported data sources and other support information before you get started with connecting to the data sources that you need.

In Data Integration data assets represent the data sources that you want to use in your data flows, tasks, and pipelines.

You create data assets from the Data assets page in a workspace. When you create a data asset, you specify one default connection to the data source. After creating a data asset, you can create additional connections as needed, edit a connection, view the data asset details, edit the data asset, and delete the data asset.

Creating a Data Asset

When you create a data asset, you select the data source type and then provide the data source details and the corresponding default connection details to that source. You can use the Console or the APIs to create data assets.

Each type of data asset has a different set of source and connection fields. See the appropriate section under Data Sources for the instructions to create the data asset that you want to use.

For most data assets, instead of providing the actual password for a connection, you can choose to use a secret in Oracle Cloud Infrastructure Vault that has the password for the connection user. Take note of the following before using OCI Vault secrets:

Ensure that you have set up the required policies for using OCI vault and secrets.
Create a secret for the database password.
If you use Oracle wallets, you must modify the wallet zip before you create a secret for the wallet contents. You also need a secret for the wallet password, and a secret for the database password.

See OCI Vault Secrets and Oracle Wallets.

If you intend to publish a task to an application in OCI Data Flow, note also the following when creating your data assets:

Use OCI Vault secrets that contain the passwords to connect to the data sources. This is required for passing credentials securely across OCI services.
Specify the Fully Qualified Domain Name (FQDN) for the database hosts. OCI Data Flow does not allow connections through host IP addresses.

See Considerations and Support Information for more information about what's supported and what you should consider when connecting to data sources.

To create a data asset, use the procedure of your choice.

The procedure described in this topic is a general one that is used across all types of data sources. The specific fields and properties for a data source type (such as Object Storage or Oracle Database) are available in Data Sources.
1. On the workspace's home page, select Create data asset on the Quick actions tile.
  
  You can also click Data asset on the tab bar, and then click Create data asset on the Data assets page that displays.
2. On the Create data asset page, enter a Name and Description (optional).
  
  The Identifier is a system-generated value based on the name. You can change the value, but after you create and save the data asset, you cannot update the identifier.
3. From the Type menu, select the type of data source you need.
4. For the data source type that you've selected, complete the properties and the default connection fields.
  See the appropriate section under Data Sources for the properties and connection field instructions to create the data asset.
5. Click Create.
Use the oci data-integration data-asset create command and required parameters to create a data asset:
oci data-integration data-asset create [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the CreateDataAsset operation to create a data asset.

Viewing Data Asset Details

The Data assets page shows you a list of all the data assets in your Oracle Cloud Infrastructure Data Integration workspace.

Select a data asset to navigate to the details page of the data asset, where you can view summary information such as the data asset type and description, access connections, and view data entities in a schema or bucket. You can also edit or delete the data asset from the details page.

Use the following sections to understand the actions you can perform, depending on the type of data source associated with the data asset.

Connections

On the data asset details page, select Connections to view the list of connections available to you. On the list, the Actions menu for a connection has these options:

Edit: Edits the connection details.
Make default: Designates this connection as the default. The action is only applicable to a connection that is not designated as the default.
Test connection: Tests whether a connection can be established. If the test fails because of missing policies, you get a list of policies that must be added.
Note
- For an OCI Object Storage data asset, when you use a cross-tenancy OCID, you require policies to connect to the Object Storage data asset. For information about the required cross-tenancy policies and the process to create them, click the View Documentation button in the information box that appears when you enter a cross-tenant OCID.
- For an autonomous database data asset, you get the list of policies only if you have selected Use Vault Secret OCID as the option to provide the login credentials for the autonomous database
Copy connection key: Copies to the clipboard the key of the connection, which you can use with APIs.
Delete: Deletes this connection.
Note

You cannot delete the connection that is designated as the default connection.

Schemas

On the details page of a data asset that is not OCI Object Storage or Generic REST, select Schemas to view the list of schemas available to you through a connection in the data asset. For more than one connection in the data asset, select a Connection (under Filters), then click a schema to view the data entities in that schema.

Buckets (OCI Object Storage)

On the details page of an OCI Object Storage data asset, select Buckets to view the list of buckets available to you. Uunder List of scope, select the compartment and then click a bucket to view the data entities in that bucket.

Data Entities (in Schema)

Available data entities are listed on the schema details page. Select a data entity to view the entity details. On the data entity details page, click Attributes to list the attribute names and data types. Click Data to list the data rows in that data entity. In the Data table, click an attribute header to view the data and attribute profile.

For BIP Oracle Fusion Applications data assets, the available reports are listed on the schema details page. Select a report to view the report attributes and data.

Data Entities (in bucket of OCI Object Storage)

Available data entities are listed on the schema (bucket) details page. Select a data entity to view the entity details. Under Format options, select a File type. Then specify format options and click Get attributes to fetch the data entity's attributes. The Attributes and Data tables are empty if you do not fetch the attributes.

File format options:

For CSV and JSON files, select the Compression type of the file. If you do not know the compression algorithm that is used to compress the file, use Auto (Default). Specify also the Encoding to use to parse the attributes.
For Parquet, Avro, and Excel files, the Auto (Default) Compression type cannot be changed.
For CSV files, other format options you can select are:
- If the first row in the file is a header row, select Yes for Has header.
- If the values in the data rows span multiple lines, select Yes for Multi-line.
- Specify the Escape character that escapes other characters found in data values. For example: \
- Select the Delimiter character that separates data fields. For example: COLON (:), COMMA (,), PIPE (|), SEMICOLON (;), or TAB (\t)
- If a column delimiter is included at the end of a data row in the file, select Yes for Trailing delimiter.
- Specify the Quote chracter that treats other characters as literal characters. For example: "
For Excel files:
- By default, Data Integration treats the first row in a file as a header row. If the first row in your file is not a header row, select No for Has header.
- For Select entity by, choose the criteria as Sheet name, Sheet index, or Table name. Then enter a Value for the worksheet name, worksheet index, or table name. Sheet index is zero-based.
- For Sheet name or Sheet index, enter the area of the file to use as the Data range for selection. If you don't enter a data range value, the default is the data range A1, which corresponds to the entire sheet. If the file has a header row, enter a value that starts from the header row, for example, A1:K56.

After the data entity's attributes have been successfully retrieved, click Data to list the data rows. In the Data table, click an attribute header to view the data and attribute profile.

Note

A data and attribute profile is not supported on these attribute data types:

BLOB
RAW
BINARY
BINARY_DOUBLE
BINARY_FLOAT
CLOB
NCLOB
SDO_GEOMETRY
XMLTYPE
XMLFORMAT
COMPLEX
VARBINARY

Resources and Operations (for REST data asset)

On the details page of a REST data asset, select Resources to view the endpoints for the selected data asset. Click the endpoint to view the available Operations.

When you click an operation, you can view the following details:

REST details format: In this section, you can view the parameters that are defined for the operation, and retrieve data after adding or modifying a parameter value.

To add or edit a parameter value, do the following:
1. From the list of parameters, click Add value or Edit value for the parameter for which you want to use to retrieve data.
2. In the Add value or Edit value panel that appears, select the required value from the list and click Add or Save.
3. Click Get data, then click Data under Data entity details to see the results.
Data entity details: In this section, you can view the data entity's Attributes and the Data that's based on the defined parameter values.

You can use the Console, CLI, or API to retrieve the details of a data asset.

1. On the workspace home page, click Data assets.
2. From the list on the Data assets page, click the name of the data asset you want to view.
  
  Alternatively, you can select View details from the Actions menu for the data asset you want to view.
Use the oci data-integration data-asset get command and required parameters to retrieve a data asset:
oci data-integration data-asset get [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the GetDataAsset operation to retrieve a data asset.

Editing a Data Asset

When you edit a data asset, you can modify the data asset's details. If applicable, you can also edit or add connections, or change the default connection.

1. On the workspace home page, click Data assets.
2. From the list on the Data assets page, click the name of a data asset and then click Edit on the data asset details page.
  
  Alternatively, you can select Edit from the Actions menu for the data asset you want to edit.
3. On the edit page, you can modify the data asset details, except the identifier.
  
  If applicable, you can select a connection to edit, add new connections, or select a new default connection. Note that only one connection is needed for an OCI Object Storage data asset, so you cannot create more than one connection.
4. Click Save changes.
Use the oci data-integration data-asset update command and required parameters to update a data asset:
oci data-integration data-asset update [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the UpdateDataAsset operation to update a data asset.

Creating a Connection

When you created a data asset, you provided the default connection details. For existing data assets (except Object Storage), you can add additional connections as needed.

1. On the workspace home page, click Data assets.
2. From the list on the Data assets page, click the name of the data asset for which you want to add a connection.
3. On the data asset details page, click Add connection.
  
  Only one connection is needed for an OCI Object Storage data asset; you cannot create more than one.
4. In the Add connection panel, complete the fields as needed to create a connection.
  
  Select the check box Make this the default connection if this connection is the default for this data asset.
5. (Optional) After you complete all the required fields, you can click Test connection to ensure that a connection can be established.
  
  A success or failure message displays, indicating whether the test is successful or not. A successful test is not required to add the connection.
6. Click Save connection.
Use the oci data-integration connection create command and required parameters to create a connection:
oci data-integration connection create [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the CreateConnection operation to create a connection.

Editing a Connection

You edit connections from a data asset's details page.

1. On the workspace home page, click Data assets.
2. From the list on the Data assets page, click the name of the data asset that has the connection you want to edit.
3. On the data asset details page, under Connections, select Edit from the connection's Actions menu.
4. In the Edit connection panel, edit the fields as needed.
  
  If the data asset has only one connection, you cannot clear the check box Make this the default connection.
5. (Optional) Click Test connection to ensure that a connection can be established.
  
  A success or failure message displays, indicating whether the test is successful or not. A successful test is not required to edit the connection.
  
  Note
  
  If the test fails because of missing policies, you get a list of policies that must be added.
  
  For an OCI Object Storage data asset, when you use a cross-tenancy OCID, you require policies to connect to the Object Storage data asset. For information about the required cross-tenancy policies and the process to create them, click the View Documentation button in the information box that appears when you enter a cross-tenant OCID.
  
  For an autonomous database data asset, you get the list of policies only if you have selected Use Vault Secret OCID as the option to provide the login credentials for the autonomous database
6. Click Save connection.
Use the oci data-integration connection update command and required parameters to update a connection:
oci data-integration connection update [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the UpdateConnection operation to update a connection.

Deleting a Data Asset

When you delete a data asset, you also permanently delete its connections. Once deleted, the data asset and its connections cannot be restored. You can delete a data asset using the Console or the APIs.

1. On the workspace home page, click Data assets.
2. From the list on the Data assets page, select Delete from the Actions menu for the data asset you want to delete.
  
  Alternatively, you can click the name of a data asset and then click Delete on the data asset details page.
3. To confirm that you want to delete the data asset and its connections, click Delete.
Use the oci data-integration data-asset delete command and required parameters to delete a data asset:
oci data-integration data-asset delete [OPTIONS]
For a complete list of flags and variable options for CLI commands, see the Command Line Reference.
Run the DeleteDataAsset operation to delete a data asset.

Oracle Cloud Infrastructure Documentation