azure-data-lakeu-sql

How do you pass a List_File_Path to an Azure Data Factory U-SQL activity?


Input Files (U-SQL) states that input paths can take any of the following forms:

Input_File_Path :=                                                                                       
     Single_File_Path 
|    List_File_Path 
|    File_Set_Path.

As an example of List_File_Path they provide:

@searchLog =  
    EXTRACT UserId          int  
          , Start           DateTime  
          , Region          string  
          , Query           string  
          , Duration        int  
          , Urls            string  
          , ClickedUrls     string
    FROM "/Samples/Outputs/ExampleA.csv",
         "/Samples/Outputs/ExampleB.csv"
    USING Extractors.Csv();

I have the following code:

DECLARE EXTERNAL @FileList string = "ListOfFiles.txt";
@searchLog =  
    EXTRACT UserId          int  
          , Start           DateTime  
          , Region          string  
          , Query           string  
          , Duration        int  
          , Urls            string  
          , ClickedUrls     string
    FROM @FileList
    USING Extractors.Csv();

If I pass in the parameter FileList as @variables('FileList') where FileList is an array, I get the following error:

Error Id: E_STORE_USER_FAILURE_83090017, Error Message: Invalid pathname Cosmos Path:

It is treating FileList as if it were: "System.Collections.Generic.List1[System.Object]"`

If I pass in the parameter FileList as @join(variables('FileList'),',') If treats FileList as if it were: "Dir/File1.txt,Dir/File2.txt" That is, as if it is a single file name.

I also tried using @join(variables('FileList'),',') with this:

DECLARE EXTERNAL @FileList string = "ListOfFiles.txt";
@searchLog =  
    EXTRACT UserId          int  
          , Start           DateTime  
          , Region          string  
          , Query           string  
          , Duration        int  
          , Urls            string  
          , ClickedUrls     string
    FROM @FileList.Split(',')
    USING Extractors.Csv();

But that generates the following error: C# error CS0029: Cannot implicitly convert type 'string[]' to 'string'

Is there a way to work around this?


Solution

  • Please try this:

    U-SQL:

    DECLARE EXTERNAL @FileList1 string = "ListOfFiles.txt";
    DECLARE EXTERNAL @FileList2 string = "ListOfFiles.txt";
    
    @searchLog =  
        EXTRACT UserId          int  
              , Start           DateTime  
              , Region          string  
              , Query           string  
              , Duration        int  
              , Urls            string  
              , ClickedUrls     string
        FROM @FileList1,@FileList2
        USING Extractors.Csv();
    
    OUTPUT @searchLog
    TO "/output/exampleD.csv" 
    USING Outputters.Csv();
    

    parameters: enter image description here