Data Factory lookups

Task in alpha phase

This Data Factory task is in phase. You can contact the Product-Live team at contact@product-live.com if you want more details and get an early access.

Overview

The data-* tasks address elements by their technical id. An id is not something you know when you write a job: it differs from one account to the next. What you do know is the business key - a job key, a table key, a partition key.

A lookup resolves one into the other. You declare it in the request body, and the task turns the key into an id before making its call:

{
    "lookups": [
        {
            "taskName": "data-job-find",
            "assign": "element.jobId",
            "entityKey": "export-items-by-ean"
        }
    ]
}

Read out loud: find the job whose key is export-items-by-ean, and put its id in element.jobId.

No extra task, no id hardcoded in the job. And it is purely additive: a request without lookups behaves exactly as before.

Where to declare a lookup

A lookup goes in the request body, and assign is a path inside the object that holds the lookups array. So the same assign means different things depending on where you declare it.

On a find task

{
    "request": {
        "json": {
            "lookups": [
                {
                    "taskName": "data-table-find",
                    "assign": "criteria.value",
                    "entityKey": "PRODUCTS"
                }
            ],
            "criteria": {
                "type": "eq",
                "field": "id",
                "value": "will be replaced"
            }
        }
    }
}

On a task that writes

{
    "request": {
        "json": {
            "operations": [
                {
                    "key": "a",
                    "lookups": [
                        {
                            "taskName": "data-job-find",
                            "assign": "element.jobId",
                            "entityKey": "export-items-by-ean"
                        }
                    ],
                    "element": {
                        "info": { "title": "Manual run" }
                    }
                }
            ]
        }
    }
}

In the first case lookups sits next to criteria, so assign: "criteria.value" fills request.json.criteria.value. In the second it sits inside an operation, so assign: "element.jobId" fills that operation's element.jobId - not the request's.

You can also declare lookups next to operations rather than inside one. It then behaves like the first case: paths start at request.json.

The path is dot-notation. Missing objects along the way are created, an existing value at the target is overwritten, and array indexes such as [0] are not supported.

WARNING

Inside an operation, assign must start with element., options. or context.. Anything else is rejected: the request body is rebuilt from element before the call, so the value would be silently discarded.

The three ways to resolve a value

By key

entityKey takes one business key and resolves it to one id. This is the common case.

{
    "taskName": "data-job-find",
    "assign": "element.jobId",
    "entityKey": "export-items-by-ean"
}

By several keys

entityKeys takes a list and resolves it to a list of ids, in the same order. Useful to build an in criteria.

{
    "taskName": "data-table-find",
    "assign": "criteria.value",
    "entityKeys": ["PRODUCTS", "SUPPLIERS", "MEDIA"]
}

By criteria

criteria resolves on something other than a key, using the same criteria shape as the target task. extract names the property to read from the element found; it defaults to id.

{
    "taskName": "data-job-find",
    "assign": "element.jobId",
    "criteria": {
        "type": "eq",
        "field": "title",
        "value": "Export items by EAN"
    },
    "extract": "id"
}

Exactly one of entityKey, entityKeys or criteria must be set.

Supported tasks

taskName is the find task used to resolve the value.

taskName	Resolves	Context
data-job-find	a job key -> its id	-
data-partition-find	a partition key -> its id	tableId
data-table-find	a table key -> its id	-

The Context column is what the lookup itself must carry in its context property, because the target task cannot search without it. Omitting it is not a degraded search: the lookup fails and the job stops. For a task not in this table, the required context is listed in the Context requirements section of its own task page.

Properties

Property	Type	Required	Description
			The find task of the entity to resolve. See Supported tasks.
			Where the resolved value is written, in dot-notation.
			A single business key.
			Several business keys. Maximum 1000.
			Free criteria, in the shape the target task accepts.
			Default: id. The property read from the element found. Only with criteria, and must be a top-level property.
			The context the target task needs to search.

Examples

Trigger a job from its key

The job id is never written in the definition. This replaces the data-job-find task you would otherwise add and wire by hand.

{
    "name": "data-job-execution-create",
    "taskReferenceName": "run_export_job",
    "description": "Run the export job, referenced by its job key",
    "type": "SUB_WORKFLOW",
    "optional": false,
    "inputParameters": {
        "request": {
            "method": "INLINE",
            "contentType": "application/json",
            "model": "default",
            "json": {
                "operations": [
                    {
                        "key": "a",
                        "lookups": [
                            {
                                "taskName": "data-job-find",
                                "assign": "element.jobId",
                                "entityKey": "export-items-by-ean"
                            }
                        ],
                        "element": {
                            "info": {
                                "title": "Export triggered by the parent job"
                            },
                            "input": {}
                        }
                    }
                ]
            }
        },
        "wait": true
    }
}

See Create job executions for the rest of the task, and the Trigger a job from another job use case for the same scenario wired with an explicit find task.

List the fields of a table from its key

data-field-find cannot search without a context.tableId. A lookup on the table key fills it, so listing the fields of a table takes a single task and no hardcoded id. In findMode: "ALL" no criteria is needed: the task returns every field of the table the lookup resolved.

{
    "name": "data-field-find",
    "taskReferenceName": "find_fields",
    "description": "List the fields of the PRODUCTS table, referenced by its key",
    "type": "SUB_WORKFLOW",
    "optional": false,
    "inputParameters": {
        "findMode": "ALL",
        "request": {
            "method": "INLINE",
            "contentType": "application/json",
            "model": "default",
            "json": {
                "lookups": [
                    {
                        "taskName": "data-table-find",
                        "assign": "context.tableId",
                        "entityKey": "PRODUCTS"
                    }
                ],
                "context": {
                    "tableId": "will be replaced"
                }
            }
        }
    }
}

List the screens of a table from its key

The same lookup with a different target task: data-screen-find also requires a context.tableId.

{
    "name": "data-screen-find",
    "taskReferenceName": "find_screens",
    "description": "List the screens of the PRODUCTS table, referenced by its key",
    "type": "SUB_WORKFLOW",
    "optional": false,
    "inputParameters": {
        "findMode": "ALL",
        "request": {
            "method": "INLINE",
            "contentType": "application/json",
            "model": "default",
            "json": {
                "lookups": [
                    {
                        "taskName": "data-table-find",
                        "assign": "context.tableId",
                        "entityKey": "PRODUCTS"
                    }
                ],
                "context": {
                    "tableId": "will be replaced"
                }
            }
        }
    }
}

Find the items of a table from its keys

Items are the one case where you do not write the lookups yourself: data-item-find takes the business keys directly and builds these lookups for you - a data-table-find on the table key, then a data-partition-find scoped by the resolved table id.

One partition

{
    "name": "data-item-find",
    "taskReferenceName": "find_items",
    "description": "List the items of the ACTIVES partition of the PRODUCTS table",
    "type": "SUB_WORKFLOW",
    "optional": false,
    "inputParameters": {
        "findMode": "ALL",
        "request": {
            "method": "INLINE",
            "contentType": "application/json",
            "model": "default",
            "json": {
                "context": {
                    "tableKey": "PRODUCTS",
                    "partitionKey": "ACTIVES"
                }
            }
        }
    }
}

Several partitions

{
    "name": "data-item-find",
    "taskReferenceName": "find_items",
    "description": "List the items of the ACTIVES and ARCHIVED partitions of the PRODUCTS table",
    "type": "SUB_WORKFLOW",
    "optional": false,
    "inputParameters": {
        "findMode": "ALL",
        "findOptions": {
            "partitionSelection": "LIST",
            "tableKey": "PRODUCTS",
            "partitionKeys": ["ACTIVES", "ARCHIVED"]
        },
        "request": {
            "method": "INLINE",
            "contentType": "application/json",
            "model": "default",
            "json": {}
        }
    }
}

Every partition of a table

{
    "name": "data-item-find",
    "taskReferenceName": "find_items",
    "description": "List the items of every partition of the PRODUCTS table",
    "type": "SUB_WORKFLOW",
    "optional": false,
    "inputParameters": {
        "findMode": "ALL",
        "findOptions": {
            "partitionSelection": "TABLE",
            "tableKey": "PRODUCTS"
        },
        "request": {
            "method": "INLINE",
            "contentType": "application/json",
            "model": "default",
            "json": {}
        }
    }
}

For one partition, context.tableKey + context.partitionKey replace context.partitionId. For several partitions - or a whole table - the keys move to findOptions: partitionSelection: "LIST" with tableKey and partitionKeys, or partitionSelection: "TABLE" with tableKey alone. In both cases request.json.context must designate neither a partition nor a table: supplying partitionId, partitionKey, tableKey or tableId there fails the task. See Find mode and partition selection for the remaining scope: ALL, every partition of every table of the account.

Target a partition by its key

data-partition-find cannot search without a tableId, so the lookup carries it:

{
    "lookups": [
        {
            "taskName": "data-partition-find",
            "assign": "context.partitionId",
            "entityKey": "ACTIVES",
            "context": {
                "tableId": "633ec1f0829f993dedc288eb"
            }
        }
    ]
}

TIP

For items you rarely write this lookup by hand: the example above shows the ready-made shortcut data-item-find offers.

Filter on several tables at once

entityKeys keeps the order of the input keys, which makes it a drop-in value for an in criteria:

{
    "lookups": [
        {
            "taskName": "data-table-find",
            "assign": "criteria.value",
            "entityKeys": ["PRODUCTS", "SUPPLIERS"]
        }
    ],
    "criteria": {
        "type": "in",
        "field": "tableId",
        "value": ["will be replaced"]
    }
}

When a lookup fails

A lookup never lets the job continue with an unresolved value - it stops the job with a message naming what it could not resolve:

• entityKey and criteria must match exactly one element. Zero matches and two or more matches both fail. A key that is not unique in the scope the task can see is therefore an error, not an arbitrary pick.
• entityKeys must resolve every key. The message lists the ones that did not.
• A missing required context fails the lookup.
• With criteria, if the property named by extract is absent from the element found, the lookup fails.

Limits

• entityKeys accepts at most 1000 keys per lookup, and at most 1000 distinct keys per taskName across the whole request.
• Lookups on the same taskName that carry no context are grouped into a single search. Lookups with a context, and lookups using criteria, always get their own.
• On a find task, a lookup that writes into the criteria only changes the actual search when findMode is REQUEST. In the other modes the resolved values appear in the logs but the search is unchanged - unless assign targets context.*.