Select query in azure adf pipeline

Question

How to pass the select query in azure adf pipeline by fetching data from a file stored in azure blob.

For Ex : my file(test) in blob is having a single value

in my adf pipeline in select query , we shd fetch the value from the table.

Sqlquery:"Select * from emp where id = (need to fetch the value from test)"

is it possible?

Answer 1

NB This answer refers to v1 of Azure Data Factory (ADF)

v2 supports setting variables via the Set Variable activity.

Re v1, I do not think this is possible directly within the pipeline as Azure Data Factory does not really support variables in that way. One other way of doing this would be to load the file from the blob into the same database where your other query is run, create a stored proc which joins the two, then call the stored proc using the Stored Proc Activity, eg:

SELECT * 
FROM emp 
WHERE id = ( SELECT id FROM yourBlobStagingTable );

Hope that makes sense.

Answer 2

The original answer addresses the v1 of ADF, where this functionality was not possible at a time.

As of v2 (GA June 2018) of ADF, it is now possible to pass parameters in the way demonstrated below. So the following pipeline definition would nowadays makes this possible.

What you want is Lookup getting a value from blob storage. You can then use it as a parameter in query using @concat string function. How to get the right value out of blob is up to your design. You can either use Lookup or Metadata for that.

Here is an example pipeline.

{
"name": "pipeline1",
"properties": {
    "activities": [
        {
            "name": "get_variable_from_file",
            "type": "Lookup",
            "dependsOn": [],
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 30,
                "secureOutput": false,
                "secureInput": false
            },
            "userProperties": [],
            "typeProperties": {
                "source": {
                    "type": "DelimitedTextSource",
                    "storeSettings": {
                        "type": "AzureBlobFSReadSettings",
                        "recursive": true,
                        "enablePartitionDiscovery": false
                    },
                    "formatSettings": {
                        "type": "DelimitedTextReadSettings",
                        "skipLineCount": 1
                    }
                },
                "dataset": {
                    "referenceName": "blob_storage",
                    "type": "DatasetReference"
                },
                "firstRowOnly": true
            }
        },
        {
            "name": "use_variable_in_query",
            "type": "Copy",
            "dependsOn": [
                {
                    "activity": "get_variable_from_file",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 30,
                "secureOutput": false,
                "secureInput": false
            },
            "userProperties": [],
            "typeProperties": {
                "source": {
                    "type": "AzurePostgreSqlSource",
                    "query": {
                        "value": "@concat('select * from schema.emp where id=',activity('get_variable_from_file').output)",
                        "type": "Expression"
                    }
                },
                "enableStaging": false
            },
            "inputs": [
                {
                    "referenceName": "postgres_database",
                    "type": "DatasetReference",
                    "parameters": {
                        "table_name": "emp"
                    }
                }
            ]
        }
    ],
    "annotations": []
}

}