Applying subsetting conditions

Edit online

Use subsetting conditions to filter rows from source tables within an IBM® Optim Archive flow. Subsetting helps by refining the data scope for extract operations.

Who can complete this task?

Administrators or users can create subsets. Standard users can only see and work with the subsets that they created. Administrators can see and work with all subsets in the system, regardless of who created them.

Applying subset conditions by using the flow builder

You can define subsetting conditions to a flow by adding a Subset node onto a flow builder canvas. Subset nodes must follow an Access definition node in a flow.

Figure 1. Subsetting is the optional third step in the flow of data in IBM Optim
Data flows from the source, through the access definition, then undergoes subsetting and masking before going to the target

To add a subset to a flow:

  1. From the navigation menu, click Workflow icon Workflow > Flow builder, then open a flow builder canvas:
    • Click New flow.
    • Load an existing flow from the My flows panel.
      or
    • Click Import to load a previously exported flow from a valid JSON flow definition. For more information, see Importing and exporting a flow.
      or
    • Click Node templates to view templates saved from existing nodes such as access definitions, subsets, masking policies, or targets.
  2. Add an access definition node to your flow builder canvas, if one does not already exist. For more information, see Creating an access definition.
  3. Connect a subset node to the previous access definition node in your flow. You can either create a new subset or add a predefined subset from your node templates.
    • To create a new subset, click on the circle on the side of the previous node in the flow, then drag the connector line to an open position on your canvas. Select New subset.
    • To add a subset based on an existing node template, drag a subset template from the Node templates panel and drop it onto the canvas. For more information, see Creating and working with template nodes.
  4. Define your subset node details:
    1. Provide a name and description for the subset, then click Next.
    2. Define your subset selection criteria. Select a table and enter a condition, such as customer_id < 8, to filter rows based on business logic.
      • For the condition operator, choose one of the following: =, <, >, IN, or BETWEEN.
      • For string values, enclose the value in single quotes (for example, city = 'Boston').
      • For the IN operator, enclose the values in parentheses (for example, IN ('a', 'b', 'c')).
      Important: Filter conditions on columns with REAL data types might not match exact values because REAL and FLOAT are approximate numeric data types. Exact values might not be stored precisely in the database. To ensure accurate filtering, use range comparisons or change the column to an exact numeric data type such as DECIMAL or NUMERIC.

      Click Next.

    3. Review the subsetting conditions you have defined, then click Create.
  5. Click Save to save the flow.
  6. Finish building the flow by adding more nodes as necessary. For more information about creating flows, see Building an IBM Optim Archive flow.

Applying subset conditions by using the API

To apply subset conditions to a flow by using the IBM Optim Archive API:

  1. Send a POST or PUT request to https://VM_HOSTNAME:PORT/optim/v1/flow using the required headers and a JSON payload that includes subsetting logic.
    Headers
    
Authorization: Bearer <accessToken>
Content-Type: application/json
    Sample request body

    The following configuration extracts customer records that match the conditions customer_id = 1000 and first_name <> 'EMMA'.

    {
    "id": "302e581d-56dd-47dc-9bc2-465c78691a76",
    "version": "3.0",
    "app_data": {
        "optim": {
            "version": "2.0.0"
        }
    },
    "doc_type": "pipeline",
    "pipelines": [
        {
            "id": "369d625b-ca3e-45a5-bbd0-08d41c1bc8b8",
            "name": "F2",
            "nodes": [
                {
                    "id": "a3761753-58cd-40fe-a449-668b0669aa89",
                    "op": "connection_profile",
                    "type": "binding",
                    "inputs": [],
                    "outputs": [],
                    "connection": {
                        "ref": "f4dc15d1-25ee-4a20-800c-caa0d439e451",
                        "name": "PgSQL"
                    },
                    "parameters": {
                        "connection_type": "source"
                    }
                },
                {
                    "id": "e4d89905-6952-4e17-b929-3e0917a14091",
                    "op": "access_definition",
                    "type": "execution_node",
                    "app_data": {
                        "source_tables": [
                            {
                                "id": "a79a9b95-6e11-4a15-a32e-919ca935228c",
                                "schema": "public",
                                "database": "optim_test",
                                "table_name": "customers",
                                "connection_profile": "f4dc15d1-25ee-4a20-800c-caa0d439e451"
                            }
                        ]
                    }
                },
                {
                    "id": "e0e35e2b-af57-4b66-b967-6ef40791ba57",
                    "op": "subset_definition",
                    "type": "execution_node",
                    "app_data": {
                        "subset_tables": [
                            {
                                "schema": "public",
                                "database": "optim_test",
                                "table_name": "customers",
                                "source_table_id": "a79a9b95-6e11-4a15-a32e-919ca935228c",
                                "subset_parameters": [
                                    {
                                        "type": "where_clause",
                                        "structured_conditions": {
                                            "condition": "AND",
                                            "clauses": [
                                              {
                                                "value": 1000,
                                                "operator": "=",
                                                "table_name": "customers",
                                                "column_name": "customer_id",
                                                "schema_name": "public",
                                                "table_alias": ""
                                              },
                                              {
                                                "value": "'EMMA'",
                                                "operator": "<>",
                                                "table_name": "customers",
                                                "column_name": "first_name",
                                                "schema_name": "public",
                                                "table_alias": ""
                                              }
                                            ]
                                        }
                                    }
                                ]
                            }
                        ]
                    }
                },
                {
                    "id": "ab5b6b4f-7a91-47a4-b6a1-4cdad3c13396",
                    "op": "connection_profile",
                    "type": "binding",
                    "inputs": [],
                    "outputs": [],
                    "connection": {
                        "ref": "6a7b49eb-4de8-4390-9431-91bfe08fd0b6",
                        "name": "DB2"
                    },
                    "parameters": {
                        "connection_type": "target"
                    }
                },
                {
                    "id": "cf7e971c-a3c3-4643-a4ba-5231dd4c7000",
                    "op": "insert_definition",
                    "type": "execution_node",
                    "app_data": {
                        "table_map": [
                            {
                                "id": "64febd6b-8952-45a2-af49-05c0caec24eb",
                                "source": {
                                    "schema": "public",
                                    "database": "optim_test",
                                    "table_name": "customers",
                                    "source_table_id": "a79a9b95-6e11-4a15-a32e-919ca935228c",
                                    "connection_profile": "f4dc15d1-25ee-4a20-800c-caa0d439e451"
                                },
                                "target": {
                                    "schema": "demo_target",
                                    "database": "testdb",
                                    "table_name": "customers",
                                    "connection_profile": "6a7b49eb-4de8-4390-9431-91bfe08fd0b6"
                                }
                            }
                        ],
                        "insert_parameters": {
                            "relational_integrity": {
                                "conflict_resolution": "merge"
                            }
                        }
                    }
                }
            ],
            "app_data": {
                "ui_data": {
                    "comments": [],
                    "worksheet_type": "subset"
                }
            },
            "runtime_ref": "optim-runtime:2.0.0"
        }
    ],
    "parameters": {},
    "json_schema": "https://api.dataplatform.ibm.com/schemas/common-pipeline/pipeline-flow/pipeline-flow-v3-schema.json",
    "primary_pipeline": "369d625b-ca3e-45a5-bbd0-08d41c1bc8b8"
}
    Subset definition node

    The following shows the structure of a subset_definition node. Include this node in the nodes array of your flow definition to apply subsetting conditions to a source table.

    {
    "app_data": {
        "name": "<name for subset definition>",
        "subset_tables": [
            {
                "schema": "default",
                "table_name": "<source table name>",
                "source_table_id": "<UUID of source table from access definition>",
                "subset_parameters": [
                    {
                        "type": "where_clause",
                        "structured_conditions": {
                            "condition": "AND",
                            "clauses": [
                                {
                                    "schema_name": "default",
                                    "table_name": "<TABLE-NAME>",
                                    "column_name": "<TABLE-FIELD-NAME>",
                                    "operator": "=",
                                    "value": "<value>"
                                }
                            ]
                        }
                    }
                ]
            }
        ]
    },
    "id": "<UUID for subset definition node>",
    "op": "subset_definition",
    "type": "execution_node"
}
  2. To update an existing subsetting configuration, send a PUT request to https://VM_HOSTNAME:PORT/optim/v1/flow with the modified conditions in the request body.
  3. To define subset conditions for additional tables, extend the subset_conditions array with the relevant schema, table name, and filtering logic.

Results

You have applied subset conditions to a workflow using either manual entry or a saved template, and optionally created a new version to preserve prior configurations.