9.8 Create and Use a Materialized View in a Data Flow

A materialized view is a database object that contains the results of a query. To use materialized views in Data Transforms you need to first build the materialized view data entity using the Data Flow editor, and then use the data entity in a data flow as source.

To use a materialized view data entity in the Data Flow editor,

  1. Drag the data entity that you want to build the materialized view on onto the Design Canvas.
  2. Select the component and click the Add Data Entity icon Add Data Entity icon present on the top right corner of the component.
  3. The Add Data Entity page appears allowing you to configure the following details of the target component:

    General tab

    • In the Name text box, enter the name of the newly created Data Entity.
    • From the Entity Type drop-down, select Materialized View as the data entity type.

      When you select this entity type the Connection Type drop-down only lists Oracle as the option, and the Connection drop-down is populated with the same connection as the source data entity. Both options are grayed out.

    • In the Schema drop-down, all schema corresponding to the selected connection are listed in two groups.
      • New Database Schema (ones that you've not imported from before) and
      • Existing Database Schema (ones that you've imported from before and are potentially replacing data entities).
      From the Schema drop-down, select the required schema.
    • In the Tags text box, enter a tag of your choice. You can use tags to filter the Data Entities displayed in the Data Entity Page.
    • If you want to mark this data entity as a feature group, expand Advanced Options and click the Treat as Feature Group check box.
    • Click Next.

    Columns tab

    • Click the Add Columns icon Add Columns icon, to add new columns to the newly created Data Entity.

      A new column is added to the displayed table.

    • The table displays the following columns:
      • Name
      • Data Type - Click the cell to configure the required Data Type.
      • Scale
      • Length
      • Actions - Click the cross icon to delete the created column.
    • To delete the columns in bulk, select the columns and click the Delete icon Delete icon.
    • To search for the required column details, in the Search text box enter the required column name and click enter. The details of the required column are displayed.
    • Click Next.

    Preview Data Entity tab

    This tab displays a preview of all the created columns and their configured details.

  4. Click Save to save the configuration and exit the wizard. The materialized view data entity is added as a component on the Design Canvas.
  5. Click the materialized view data entity and in the Properties pane to the right, click Options (Options).

    Set the following options to define the refresh settings:

    • Recreate materialized view: This option allows you to drop the existing materialized view and create a new one if the definition changes. Set this to true to recreate the materialized view to match the query from the updated data flow. This only needs to be done once. Default value is false.
    • Refresh with: This option allows you to specify whether the incremental refresh should be done using PRIMARY KEY or the internal ROWID. Default value is ROWID.
  6. Save and execute the data flow. The left panel of the Data flow Details page lists the materialized view data entities that you can use as a source component in a data flow.
  7. Drag and drop the materialized view data entity onto the Design Canvas. Drag the required Data Entities that you want to use in the data flow and drop them on the design canvas.
  8. Save and execute the data flow. On data flow execution, the materialized view will refresh according to the configured settings.

Parent topic: Data Flows