Oracle Cloud Infrastructure Documentation

Connect to Data Sources

In this tutorial, you create data assets for the data sources by using Object Storage as the source and Autonomous Data Warehouse as the target.

Before You Begin

You must have the following:

  • Access to a Data Integration workspace. See Connect to Data Integration.
  • Import sample data in an Object Storage bucket.
  • The OCID of the workspace that you created. See Getting the Workspace OCID.

  • Policies in the required compartment as follows:

    • To use the Object Storage service:
      allow group <group_name> to use object-family in compartment <compartment-name>
allow any-user to read buckets in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace_ocid>', request.operation = 'GetBucket'}
allow any-user to manage objects in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace_ocid>'}
    • To use Oracle Autonomous Data Warehouse as a target:
      allow any-user to manage buckets in compartment <compartment-name> where ALL {request.principal.type = 'disworkspace', request.principal.id = '<workspace_ocid>', request.permission = 'PAR_MANAGE'}

    For reference, see Creating a Policy Using the Console and Policy Examples to Enable Access to OCI Object Storage .

  • Access to an Autonomous Data Warehouse database, including credentials and the wallet file. See Download Database Connection Information.
  • The user name and password to establish a connection when creating a new data asset. You create the user name and password later in this tutorial.

1. Creating the Source Data Asset

For the purposes of this tutorial, Oracle Cloud Infrastructure Object Storage serves as the source data asset.

To create a data asset for Oracle Cloud Infrastructure Object Storage:

  1. On the workspace Home page, select Create data asset on the Design tile.
    If you need help finding the Data Integration workspace to work in, see Creating and Accessing a Workspace.
  2. On the Select Data asset type panel, for Connector Type select All. Then select the OCI Object Storage icon.

    If you don't see OCI Object Storage, enter obj in the search field.

  3. On the Create data asset page, complete the fields in the General information section:
    1. For Name, enter Data_Lake without any spaces. You can use alphanumeric characters, hyphens, periods, and underscores only.
    2. For Description, enter a description about the data asset.
    3. The Type menu already shows OCI Object Storage selected.
    4. The Tenancy OCID and Namespace fields are automatically filled for you.
      Note

      This procedure assumes that the workspace and Object Storage resources are within the same tenancy.

      The namespace value is based on the tenancy OCID value. If the Namespace field isn't automatically filled, you can enter the tenancy name.

    5. For OCI region, select the region for the Object Storage resource, or start typing a name to filter the list and then select a region from the filtered list.
      Important

      The Object Storage data source that you set up as a Source or Target data asset in Data Integration is identified using the region ID, namespace, and bucket name. You don't encounter 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 are the same, the Data Integration tasks would fail.
    6. Ignore the Enable policies to use data asset information box, if you have already added the required policies. Otherwise, select Show more information to view the details of the policy name and policy statements required to use the Object Storage data asset. Add or copy the policies, specifying the group name and compartment in the statements.
  4. In the Default connection information section, enter a name and description (optional).
  5. (Optional) Select Test connection.
    A successful test isn't required to create the data asset.
  6. Select Create.

2. Preparing the Target Database

To complete all the tutorials in this series, configure the target autonomous database to add a schema and a table.

To prepare the target autonomous database with the BETA schema:

  1. In the Console navigation menu, select Oracle Database, and then select Autnomouse Database.
  2. Select the compartment that has the autonomous database you're going to use.
  3. From the list of autonomous databases, select the database.
  4. Under Database Actions, select SQL.
    If prompted, log in with the administrator credentials for the autonomous database.
  5. To create the BETA user, enter and then run the following script in the SQL worksheet:
    create user BETA identified by "<example-password>";
grant DWROLE to BETA;
alter user BETA quota 200M on data;
    Note

    Ensure that you enter a password in place of <example-password>.
  6. Run the following SQL statements to create the CUSTOMERS_TARGET table. 
    CREATE TABLE "BETA"."CUSTOMERS_TARGET"
   ("CUST_ID" NUMBER,
    "LAST_NAME" VARCHAR2(200 BYTE),
    "FIRST_NAME" VARCHAR2(200 BYTE),
    "FULL_NAME" VARCHAR2(200 BYTE),
    "STREET_ADDRESS" VARCHAR2(400 BYTE),
    "POSTAL_CODE" VARCHAR2(10 BYTE),
    "CITY_ID" NUMBER,
    "CITY" VARCHAR2(100 BYTE),
    "STATE_PROVINCE_ID" NUMBER,
    "STATE_PROVINCE" VARCHAR2(100 BYTE),
    "COUNTRY_ID" NUMBER,
    "COUNTRY" VARCHAR2(400 BYTE),
    "CONTINENT_ID" NUMBER,
    "CONTINENT" VARCHAR2(400 BYTE),
    "AGE" NUMBER,
    "COMMUTE_DISTANCE" NUMBER,
    "CREDIT_BALANCE" NUMBER,
    "EDUCATION" VARCHAR2(40 BYTE),
    "EMAIL" VARCHAR2(416 BYTE),
    "FULL_TIME" VARCHAR2(40 BYTE),
    "GENDER" VARCHAR2(6 BYTE),
    "HOUSEHOLD_SIZE" NUMBER,
    "INCOME" NUMBER,
    "INCOME_LEVEL" VARCHAR2(20 BYTE),
    "INSUFF_FUNDS_INCIDENTS" NUMBER,
    "JOB_TYPE" VARCHAR2(200 BYTE),
    "LATE_MORT_RENT_PMTS" NUMBER,
    "MARITAL_STATUS" VARCHAR2(8 BYTE),
    "MORTGAGE_AMT" NUMBER,
    "NUM_CARS" NUMBER,
    "NUM_MORTGAGES" NUMBER,
    "PET" VARCHAR2(40 BYTE),
    "PROMOTION_RESPONSE" NUMBER,
    "RENT_OWN" VARCHAR2(40 BYTE),
    "SEG" NUMBER,
    "WORK_EXPERIENCE" NUMBER,
    "YRS_CURRENT_EMPLOYER" NUMBER,
    "YRS_CUSTOMER" NUMBER,
    "YRS_RESIDENCE" NUMBER,
    "COUNTRY_CODE" VARCHAR2(2 BYTE),
    "ORDER_NUMBER" NUMBER,
    "REVENUE" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
 
--------------------------------------------------------
--  Constraints for Table CUSTOMERS_TARGET
--------------------------------------------------------
  ALTER TABLE "BETA"."CUSTOMERS_TARGET" MODIFY ("CUST_ID" NOT NULL ENABLE);
  ALTER TABLE "BETA"."CUSTOMERS_TARGET" MODIFY ("SEG" NOT NULL ENABLE);
  7. In the Navigator pane on the left side, switch to the BETA schema to verify that the table was created successfully.
    You might have to log out and then log in again.
  8. Log out of the SQL page.

3. Creating the Target Data Asset

Navigate back to Data Integration to create the target data asset.

To create a data asset for the target:

  1. On the workspace Home page, select Create data asset on the Design tile.
    If you need help finding the Data Integration workspace to work in, see Creating and Accessing a Workspace.
  2. On the Select Data asset type panel, for Connector Type select RDBMS. Then select the Oracle ADW icon.
  3. On the Create data asset page, complete the fields in the General information section:
    • Name: Data_Warehouse (You can use alphanumeric characters, hyphens, periods, and underscores only).
    • Identifier: A generated identifier based on the value you enter for Name. You can change the generated value, but after you save the data asset, you aren't allowed to update the identifier.
    • Description: Optional
    • Type: The Type menu already shows Oracle Autonomous Data Warehouse selected.
    • Upload wallet and Wallet file: Drop or browse to select the wallet file for accessing the database.
  4. In the Default connection information section, enter or select the following:
    • Name: Default connection (Optionally, you can rename the connection)
    • Description: Optional (For example, Connect with BETA user)
    • Username: BETA
    • Use password: The password you created for BETA.

      We recommend that you create a vault secret, and then select Use vault secret OCID for the BETA user password. See Vault, Keys, and Secrets.

    • TNS alias: The database service to use
  5. In the Default staging location section, enter or select the following:
    • Object Storage data asset: The Object Storage data asset that has the bucket you want to use as the default staging location
    • Connection: The connection
    • Compartment: The compartment
    • Bucket: The bucket name
  6. (Optional) Select Test connection.
    A successful test isn't required to create the data asset.
  7. Select Create.