Oracle Cloud Infrastructure Documentation
Try Free Tier

Ingest and Transform Data Using a Data Flow

A data flow is a logical diagram representing the flow of data from source data assets, such as a database or flat file, to target data assets, such as a data lake or data warehouse.

The flow of data from source to target can undergo a series of transformations to aggregate, cleanse, and shape the data. Data engineers and ETL developers can then analyze or gather insights and use that data to make impactful business decisions.

In this tutorial, you:

  1. Create a project where you can save the data flow.
  2. Add source operators and select the data entities to use in the data flow.
  3. Use shaping operators and apply transformations.
  4. Identify the target data asset for loading the data.

Before You Begin

To ingest and transform data using a data flow, you must have the following:

  • Access to a Data Integration workspace. See Connect to Data Integration.
  • Source and target data assets created.

  • The PAR_MANAGE permission enabled on the staging bucket. 

    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'}

    Autonomous databases use Object Storage for staging data and need pre-authenticated requests.

1. Creating a Project and a Data Flow

In Oracle Cloud Infrastructure Data Integration, data flows and tasks can only be created in a project or folder.

To create a project and a data flow:

  1. On the workspace Home page, click Projects.

    You can also click Open tab (plus icon) in the tab bar, and select Projects.

  2. On the Projects page, click Create project.
  3. On the Create project page, enter DI_Lab for Name, and then click Create.

    After you create a project, you can create a data flow to ingest data from two source files, containing customers (CUSTOMERS.json) and orders (REVENUE.csv) information.

  4. On the DI_Lab project details page, click Data flows in the submenu on the left side.
    If you're on the Projects page, click DI_Lab to open the project details page.
  5. Click Create data flow.

    The data flow designer opens in a new tab.

  6. In the Properties panel, for Name, enter Load Customers and Revenue Data.

    The Identifier value is automatically generated based on the value you enter for the data flow name. You can change the generated value, but after you save the data flow, you aren't allowed to update the identifier.

  7. Click Create.

    The designer remains open for you to continue editing.

2. Adding Source Operators

You add source operators to identify the data entities to use for the data flow. A data entity represents a database table in this tutorial.

Learn more about operators.

  1. From the Operators panel, drop a Source operator onto the canvas.
  2. On the canvas, select SOURCE_1, if it isn't already selected.

    The Properties panel displays the details for the operator in focus.

  3. In the Details tab of the Properties panel, click Select next to each of the following options to make the selections:
    • For Data asset, select Data_Lake.
    • For Connection, select Default connection.
    • For Schema, select the compartment and then the bucket. For the purposes of this tutorial, Object Storage serves as the source data asset, this is why you select the bucket here.
    • For Data entity, click Browse by name and then select CUSTOMERS.json. For File type, select JSON.
  4. When you complete the selections for SOURCE_1, the operator name becomes CUSTOMERS_JSON, reflecting the data entity selection. In the Identifier field, rename the source operator to CUSTOMERS.
  5. Repeat steps 1 through 3 to add a second Source operator with the following values:
    • For Data asset, select Data_Lake.
    • For Connection, select Default Connection.
    • For Schema, select the compartment and then the bucket. For the purposes of this tutorial, Object Storage serves as the source data asset, this is why you select the bucket here.
    • For Data entity, select REVENUE.csv, and then select CSV for the file type. Accept the default values for the remaining items.
  6. When you complete the selections for the second source operator, the operator name becomes REVENUE_CSV, reflecting the data entity selection. In the Identifier field, rename the source operator to REVENUE.
  7. (Optional) Click Assign parameter to keep the source details from being bound to the compiled code, when you publish the data flow.

    Learn more about Parameters.

  8. (Optional) In the Attributes tab, you can view the data entity's attributes and apply exclude or rename rules to the attributes from their respective Actions menu (Actions menu).
    You can also use the filter icon on the Name or Type column to apply one or more filters on the attributes to be excluded.
  9. (Optional) In the Data tab, you can view a sampling of data from the source data entity and apply transformations to the data or select attributes to view a data profile.

    Learn more about Data Transformations.

  10. (Optional) In the Validation tab, you can check for warnings or errors with the configuration of the source operators.
  11. To save the data flow and continue editing, click Save.

3. Filtering and Transforming Data

4. Joining Data

After you apply filters and transformations, you can join the source data entities using a unique customer identifier, and then load the data into a target data entity.

  1. To join the data from EXPRESSION_1 with the data from FILTER_1, drop a Join operator from the Operators panel onto the canvas, placing it after to EXPRESSION_1 and FILTER_1.
  2. Connect EXPRESSION_1 to JOIN_1. Then connect FILTER_1 to JOIN_1.
  3. With JOIN_1 selected, in the Details tab of the Properties panel, click Create next to Join condition.
  4. In the Create join condition panel, enter CUST in the search by name field.
    You want to join the entities using CUST_ID and CUST_KEY.
  5. In the editor, enter JOIN_1_1.CUSTOMERS_JSON.CUST_ID=JOIN_1_2.REVENUE_CSV.CUST_KEY.
  6. Click Create.
  7. To save the data flow and continue editing, click Save.

5. Adding a Target Operator

  1. From the Operators panel, drop a Target operator onto the canvas.
  2. Connect JOIN_1 to TARGET_1.
  3. Select TARGET_1 on the canvas, if it isn't already selected.
  4. In the Details tab of the Properties panel, don't select the Create new data entity checkbox.
  5. Next, click Select for each of the following options to make the selections for a data entity:
    • For Data asset, select Data_Warehouse.
    • For Connection, select Default Connection.
    • For Schema, select BETA.
    • For Data entity, select CUSTOMERS_TARGET.
  6. For Staging location, select the Data Lake data asset, its default connection, and the compartment. Then for Schema, select the Object Storage bucket that you used to import the sample data.
  7. Leave the value for Integration strategy as Insert.
  8. To review the Attributes mapping, click the Map tab.

    By default, all attributes are mapped by name.

    For example, CUST_ID from JOIN_1 maps to CUST_ID in the target data entity.

  9. To manually map attributes that aren't yet mapped, select the All menu in the Target attributes table, and then select Attributes not mapped.

    You can do the same in the Source attributes table (for the incoming fields).

  10. Now drop FULLNAME from Source attributes to FULL_NAME in Target attributes.
  11. In the Target attributes table, select Attributes not mapped, and then select All.

    All attributes are now mapped.

  12. (Optional) Click Actions to review the various mapping options that are available.
  13. (Optional) Click View rules to view the applied Rules.
  14. To save the data flow and exit the designer, click Save and close.

Was this article helpful?