Data Assets

Data assets represent data sources that you use as sources and targets for your data integration solutions. Data assets can be databases, flat files, and so on.

For information about supported data asset types, see Supported Data Sources for Data Assets.

You create and access data assets from the Data Assets page. From the workspace's Home page, you can access the Data Assets page by clicking Data Assets or clicking the Open tab (plus icon) in the tab bar and then selecting Data Assets.

The Actions icon (three dots) for a data asset has these options:

  • View Details: Displays the data asset details, including connections and schemas.
  • Copy Data Asset Key: Copies to the clipboard the key of the data asset, which you can use with APIs.
  • Edit: Lets you edit the data asset, including adding and editing connections.
  • Delete: Lets you delete the data asset.

When you create a data asset, you specify one default connection. A connection is a user/password combination used to connect to your data source. You can create additional connections for a data asset as needed. Each connection should give you access to additional schemas and data entities  to integrate. For more information on connections, see Creating a Connection.

After you create your data asset, you're brought to its details page where you can review or update the data asset's name, description, and tags. You can also see its data entities, and add or modify connections. For more information, see About Data Asset Details.

Supported Data Sources for Data Assets

The following table lists the data sources that you can use with Data Integration.

Data Source TypeVersionSourceTarget
Oracle Database12.1YesYes
12.2YesYes
18YesYes
19YesYes
Oracle Database on Oracle Cloud Infrastructure12.1YesYes
12.2YesYes
18YesYes
19YesYes
Exadata DB Systems12.1YesYes
12.2YesYes
18YesYes
19YesYes
Oracle Object StorageLatestYesYes
Autonomous Data Warehouse18c/19cYesYes
Autonomous Transaction Processing18c/19cYesYes
MySQL5.7.x and 8.0.xYesYes
PostgreSQL10.1YesNo
9.6, 9.5, 9.4, 9.3, 9.2, 9.1, and 9.0YesNo
8.4, 8.3, and 8.2YesNo
HiveCDH 5.4 and higherYesNo
Apache 1.0, 2.0, 3.0 and higherYesNo
Microsoft SQL Server2019YesYes
2017YesYes
2016 Service Pack 2YesYes
2014 Service Pack 3YesYes
2012 Service Pack 4YesYes

Supported Object Types

For Oracle Object Storage, Data Integration supports the following object types:

  • JSON
  • Parquet
  • CSV
  • Avro

Note that only read and write of primitive data types are supported.

Data Types Not Supported

The following Oracle Database data types are not supported:

  • ROWID
  • UROWID
  • BFILE
  • TIMESTAMP WITH LOCAL TIMEZONE
  • INTERVAL DAY TO SECOND
  • INTERVAL YEAR TO MONTH
  • XMLTYPE
  • SDO_GEOMETRY

Creating a Data Asset

When you create a data asset, you select the data source type and provide the corresponding connection details. Each type of data asset has a different set of connection fields. Use the appropriate instructions for your data asset type. You can use the Console or the APIs to create a data asset.

Creating an Oracle Database Data Asset
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select Oracle Database.
  4. Select a connection type (Basic or Wallet), depending on whether you will provide an Oracle Wallet file.
  5. Enter the Host name and Port of the data asset.
  6. Select a service type (Service Name or SID), and then enter the details in the corresponding field.
  7. (For Wallet connection type only) Under Wallet File, click Select File or drag a wallet file to the Drop File space.
  8. Under Default Connection Settings, enter a name and description (optional), and then enter the User Name and Password for the Default Connection to the data asset.
  9. (Optional) After you complete all the required fields, you can click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  10. Click Create.
Creating an Oracle Object Storage Data Asset
Important

Before you can create an Service-to-Service (S2S) Principal connection for your Oracle Object Storage data asset, you must create a policy to allow Data Integration to access the Oracle Object Storage resource. See creating a policy and Policy Examples to Enable Access to Oracle Object Storage under Policy Examples.
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select Oracle Object Storage.
  4. For URL, enter the URL for your Oracle Object Storage resource in the following format:

    https://objectstorage.<region-identifier>.oraclecloud.com

    For example: https://objectstorage.us-phoenix-1.oraclecloud.com

    Important

    The Object Storage data source that you set up as a Source or Target data asset in Data Integration is identified using its region ID, namespace, and bucket name. You won't encounter any issues if the Source and Target data assets have different region IDs, different namespaces, and different bucket names. If the Source and Target have the same bucket name but different region IDs and namespaces, or if they have the same namespace but different region IDs and different bucket names, then also you won't encounter any issues. However, if only the Source and Target data asset region ID is different and the namespace and bucket name as same, your Data Integration tasks will fail.
  5. For Tenant OCID, enter the Oracle Cloud ID of the tenancy.
    Note

    To view your tenancy OCID string in the Console, from the Profile menu click Tenancy:<your_tenancy_name>. You can find the tenancy OCID under Tenancy Information.

  6. For Namespace, enter the namespace for the Object Storage bucket, if the value is not auto-populated after completing the preceding step.
  7. Under Default Connection, enter a name and description (optional).
  8. (Optional) After you complete all the required fields, you can click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  9. Click Create.
Creating an Autonomous Data Warehouse or Autonomous Transaction Processing Data Asset
To use Autonomous Data Warehouse or Autonomous Transaction Processing as your source or target data asset, your database administrator must grant the following privileges to the Autonomous Data Warehouse or Autonomous Transaction Processing user in Data Integration. For example:
GRANT EXECUTE ON DBMS_CLOUD TO <USER_NAME>
GRANT DWROLE TO <USER_NAME>
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing.
  4. For Service Name select the Autonomous Data Warehouse or Autonomous Transaction Processing database.
  5. For Credential File, you can click Select File or drag a wallet file to the Drop File space.
    Note

    For the Credential File, see Required Keys and OCIDs.

  6. (Optional) After you complete all the required fields, click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  7. Click Create.
Note

When you use Autonomous Data Warehouse or Autonomous Transaction Processing as the target in a data flow design, you select an Object Storage bucket as the staging location. Ensure that you enable the PAR_MANAGE permission on the staging bucket. In addition, you must create all the required Object Storage policies.

Learn more about pre-authenticated requests.

When you use Autonomous Data Warehouse as a target data asset, you need the following policy:
allow any-user to {PAR_MANAGE} in compartment where ALL {request.principal.type='disworkspace'}
Creating a MySQL Data Asset
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select MySQL.
  4. Enter the Host name and Port, and then enter the Database name.
  5. Under Default Connection Settings, enter a name and description (optional), and then enter the User Name and Password for the Default Connection to the data asset.
  6. (Optional) After you complete all the required fields, you can click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  7. Click Create.
Creating an Apache Hive Data Asset
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select Apache Hive.
  4. Enter the Host name and Port, and then enter additional database parameters in the Data Asset Properties field, if needed.
  5. Under Default Connection Settings, enter a name and description (optional). In Connection Properties, enter additional connection parameters, if needed. Then enter the User Name and Password for the Default Connection to the data asset.
  6. (Optional) After you complete all the required fields, you can click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  7. Click Create.
Creating a PostgreSQL Data Asset
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select PostgreSQL.
  4. Enter the Host name and Port, and then enter additional database parameters in the Data Asset Properties field, if needed.
  5. Under Default Connection Settings, enter a name and description (optional). In Connection Properties, enter additional connection parameters, if needed. Then enter the User Name and Password for the Default Connection to the data asset.
  6. (Optional) After you complete all the required fields, you can click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  7. Click Create.
Creating a Microsoft SQL Server Data Asset
  1. On the workspace's Home page, select Create Data Asset from the Quick Actions tile.

    You can also click Create Data Asset on the Data Assets page.

  2. On the Create Data Asset page, enter a name and description (optional).
  3. From the Type drop-down, select Microsoft SQL Server.
  4. Enter the Host name and Port, and then enter additional database parameters in the Data Asset Properties field, if needed.
  5. Under Default Connection Settings, enter a name and description (optional). In Connection Properties, enter additional connection parameters, if needed. Then enter the User Name and Password for the Default Connection to the data asset.
  6. (Optional) After you complete all the required fields, you can click Test Connection to ensure you've entered the data asset details correctly.

    A success or failure message displays, indicating whether the test was successful or not. If the test fails, review your connection settings and try again.

  7. Click Create.

Creating a Connection

When you create a data asset, you must provide the default connection details. You can then create additional connections as needed.

To create a connection:

  1. In the tab bar, click Open tab (plus icon) and then select Data Assets.
  2. On the Data Assets page, select the data asset that you want to add a connection for.
  3. On the Data Asset Details page, click Add Connection, and then complete the fields in the Add Connection panel as needed.

    Only one connection is needed for an Oracle Object Storage data asset; you might not be able to create more than one.

  4. After you complete all required fields successfully, click Test Connection to ensure that a connection can be established.
  5. Click Save Connection.

Editing a Connection

You can edit connections from a data asset's Details page.

To edit a connection:

  1. In the tab bar, click Open tab (plus icon) and then select Data Assets.
  2. On the Data Assets page, select the data asset that has a connection you want to edit.
  3. On the Data Asset Details page, in the Connections list, select Edit from the connection's Actions (three dots) menu.
  4. In the Edit Connection panel, edit the fields you want to change.
  5. Click Test Connection to ensure that the connection can be established.
  6. Click Save Connection.

About Data Asset Details

The Data Assets page shows you a list of all the data assets in your workspace. Select a data asset to view its details. On the Data Asset Details page, you can view the data asset's summary information, and access connections, schemas, and data entities.

Summary

In the summary view, you can review the data asset's details such as type and description.

Connections

In the list of connections for a data asset, you can designate a different connection as the default, create a connection, and edit or delete a connection. You can also copy the key of a connection. These options are available from the Actions icon (three dots) for a connection.

Note

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

Schemas

On the data asset's detail page, select Schemas to view the list of schemas available to you through the data asset's connections. Select a schema to view its data entities.

Data Entities

The Schema Details page shows a list of data entities available to you from the selected schema. You can select a data entity to view attributes.

When you view a CSV data entity from an Oracle Object Storage data asset, you also see a set of formatting parameter options. Here, you can specify how to parse the attributes retrieved. You can specify whether to include a header, the encoding, the escape character, delimiter, and quote character. Click Get Attributes to fetch the data entity's attributes.

Editing a Data Asset

In the Console, you can only edit a data asset from the Data Assets page. You can also update a data asset using the API. When you edit a data asset, you can modify the data asset's details, select or add new connections, and change the default connection.

To edit a data asset:

  1. In the tab bar, click Open tab (plus icon), and then select Data Assets.
  2. On the Data Assets page, select a data asset from the list and then click Edit.

    You can also open the Data Asset's Actions (three dots) menu and select Edit.

  3. On the Edit Data Asset page, you can modify the data asset details, select or add new connections, or select a new default connection.
  4. Click Save.
After you save your changes, you're brought back to the Data Assets page.

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.

To delete a data asset:

  1. In the tab bar, click Open tab (plus icon), and then select Data Assets.
  2. On the Data Assets page, select the data asset you want to delete and then click Delete.

    You can also select Delete from the data asset's Actions (three dots) menu.

  3. Confirm that you want to delete the data asset and its connections, and click Delete.

Unicode Support

Data Integration supports the Unicode standard, which is a universal character encoding standard for written characters and text in any language. The Unicode standard provides a unique number for every character regardless of the platform, device, or application. For example, 0041 is the Unicode character for the English letter "A".

Data Integration supports Unicode characters (including multibyte characters) in data and metadata.

Unicode support in data means that the attributes and attribute values in your source and target data assets can include Unicode and multibyte characters. You can also enter Unicode and multibyte characters in expressions. For JSON and CSV Object Storage data assets, the default encoding is UTF-8, and you cannot change it.

Unicode support in metadata means that the data entity and schema names of your data assets can include Unicode and multibyte characters. You can also enter Unicode and multibyte characters for names and descriptions when working with objects in Data Integration.

In the Unicode standard, a unique number assigned to a Unicode character is a code point. Currently, Data Integration supports the following Unicode code points and range of code points:

Code Point or RangeCharacterNumber of Characters Supported
Basic Latin characters
0024$ (dollar sign)1
0041 - 005AA to Z26
005F_ (underscore)1
0061 - 007Aa to z26
Latin-1 Supplement characters
00C0 - 00D6Latin-1 Supplement characters with accents23
00D8 - 00F631
00F8 - 00FF8
Characters in 46 ranges from Latin Extended-A to Greek Extended
0100 - 1FFFCharacters in the following named ranges: Latin Extended-A, Latin Extended-B, IPA Extensions, Spacing Modifier Letters, Combining Diacritical Marks, Greek and Coptic, Cyrillic, Cyrillic Supplementary, Armenian, Hebrew, Arabic, Syriac, Thaana, Devanagari, Bengali, Gurmukhi, Gujarati, Oriya, Tamil, Telugu, Kannada, Malayalam, Sinhala, Thai, Lao, Tibetan, Myanmar, Georgian, Hangul Jamo, Ethiopic, Cherokee, Unified Canadian Aboriginal Syllabics, Ogham, Runic, Tagalog, Hanunoo, Buhid, Tagbanwa, Khmer, Mongolian, Limbu, Tai Le, Khmer Symbols, Phonetic Extensions, Latin Extended Additional, Greek Extended7936
Characters in 4 ranges from Hiragana to Hangul Compatibility Jamo
3040 - 318FCharacters in the following named ranges: Hiragana, Katakana, Bopomofo, Hangul Compatibility Jamo336
Characters in 4 CJK (Chinese, Japanese, and Korean) ranges
3300 - 337FCJK Compatibility characters128
3400 - 3D2DCJK Unified Ideographs Extension A characters2350
4E00 - 9FFFCJK Unified Ideographs characters20992
F900 - FAFFCJK Compatibility Ideographs characters512

Using the API

For information about using the API and signing requests, see REST APIs and Security Credentials. For information about SDKs, see Software Development Kits and Command Line Interface.

Schema operations
Data Entity operations