Is it possible to use U-SQL managed tables as output datasets in Azure Data Factory?

Question

I have a small ADF pipeline that copies a series of files from an Azure Storage Account to an Azure Data Lake account. As a final activity in the pipeline I want to run a U-SQL script that uses the copied files as inputs and outputs the result to a U-SQL managed table.

The U-SQL script basically extracts the data from the copied files, applies some transformation and then INSERT´s it into an existing U-SQL managed table.

How (if possible) can I add the U-SQL table as a output dataset in Azure Data Factory?

Answer 1

You cannot currently add a U-SQL internal table as an output dataset in Azure Data Factory (ADF). A similar question came up recently here and the answer from Michael Rys (the "father" of U-SQL) was "I know that the ADF team has a work item to do this for you."

You could use howerver Azure Data Factory to run a parameterised U-SQL script, where the input parameter is the filepath. This would have a similar result.

Example pipeline from a recent question:

{
    "name": "ComputeEventsByRegionPipeline",
    "properties": {
        "description": "This is a pipeline to compute events for en-gb locale and date less than 2012/02/19.",
        "activities": [
            {
                "type": "DataLakeAnalyticsU-SQL",
                "typeProperties": {
                    "scriptPath": "adlascripts\\SearchLogProcessing.txt",
                    "scriptLinkedService": "StorageLinkedService",
                    "degreeOfParallelism": 3,
                    "priority": 100,
                    "parameters": {
                        "in": "/input/SearchLog.tsv",
                        "out": "/output/Result.tsv"
                    }
                },
...

Basically the U-SQL script goes from:

@searchlog =
    EXTRACT ...
    FROM @in
    USING Extractors.Tsv();

to:

@searchlog =
    EXTRACT ...
    FROM "/input/SearchLog.tsv"
    USING Extractors.Tsv();

which I think achieves the same thing you want.