Connect to Data Sources

In this tutorial, you create data assets for your source and target data sources 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.
Imported sample data in an Object Storage bucket.

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 or Oracle Autonomous Transaction Processing 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 and policy examples to enable access to Oracle Object Storage.

Access to an Autonomous Data Warehouse database, including credentials and the wallet file. See Download a Wallet.
The user name and password to establish connection, while creating a new data asset. You'll create the user name and password 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:

On your workspace Home page, click Create data asset from the Quick actions tile.
On the Create data asset page, complete the General information fields:
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 your data asset.
3. From the Type dropdown, select OCI Object Storage.
4. The Tenancy OCID and Namespace fields are auto-filled for you.
  
  Note
  
  This procedure assumes that your workspace and Object Storage resources are within the same tenancy.
  
  The namespace value is based on the tenancy OCID value. If the Namespace field is not auto-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 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 are the same, your Data Integration tasks will fail.
6. Ignore the Enable policies to use data asset information box, if you have already added the required policies. Otherwise, click 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 your group name and compartment in the statements.
For Default connection information, enter a name and description (optional).
(Optional) Click Test connection.
A successful test is not required to create the data asset.
Click Create.

2. Preparing the Target Database

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

To prepare the target autonomous database with the BETA schema:

In the Oracle Cloud Infrastructure Console navigation menu, go to Oracle Database, and then click Autonomous Data Warehouse.
Select the compartment that has the autonomous database you are going to use.
From the list of autonomous databases, select your database.
On your database details page, click Database Actions.
When prompted, log in with the administrator credentials for your autonomous database.
Under Development, click SQL.
To create the BETA user, enter and then run the following script in the SQL worksheet:
```
create user BETA identified by "<password>";
grant DWROLE to BETA;
alter user BETA quota 200M on data;
```
Note

Ensure that you enter a password in place of <password>.

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);

In the Navigator tab on the left, switch to the BETA schema to verify that your table was created successfully.
You might have to log out and then log in again.
Log out of the SQL page.

3. Creating the Target Data Asset

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

To create a data asset for your target:

In the Oracle Cloud Infrastructure Console navigation menu, go to Analytics & AI, then click Data Integration.
Click Workspaces.
Navigate to the compartment where you created the workspace, and select your workspace.
On your workspace Home page, click Create data asset from the Quick actions tile.

You can also click Open tab (plus icon) in the tab bar and select Data assets. Then on the Data assets page, click Create data asset.
On the Create data asset page, for General information, set the following:
- Name: Data_Warehouse (You can use alphanumeric characters, hyphens, periods, and underscores only).
- Identifier: Auto-generated based on the value you enter for Name. You can change the auto-generated value, but after you save the data asset, you cannot update the identifier again.
- Description: Optional
- Type: Oracle Autonomous Data Warehouse
- Upload wallet and Wallet file: Drag and drop or browse to select the wallet file. See Download a Wallet.
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
- Password: The password you created for BETA
- TNS alias: The database service to use
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
(Optional) Click Test connection.
A successful test is not required to create the data asset.
Click Create.

Additional Resources

Here are some resources if you want to learn more:

What's Next

Now that you have source and target data assets created, you can create a data flow to ingest and transform your data.