How to get Full Path of file in U-SQL query

Question

Is it possible to get file "full path" from following u-sql code, just like file name column?

DECLARE @in = "D:/Sample/Data/{FileName}";

@drivers =
    EXTRACT id string,
            first_name string,
            last_name string,
            address string,
            FileName string
    FROM @in
    USING USING Extractors.Csv();

OUTPUT @drivers
TO "/ReferenceGuide/DML/QSE/Extract/Drivers.txt"
USING Outputters.Csv();

From the above u-sql query I am able to get file name but I want file full path also. Like "D:/Sample/Data"

score 3 · Accepted Answer · answered May 30 '17 at 14:29

Well you already have the input path in a variable at the top of your script so just add that to the output dataset. Like this:

DECLARE @in = "D:/Sample/Data/{FileName}";

@drivers =
    EXTRACT id string,
            first_name string,
            last_name string,
            address string,
            FileName string
    FROM @in
    USING USING Extractors.Csv();


@driverswithpath =
    SELECT
        *,
        @in AS 'InputPath'
    FROM
        @drivers;

OUTPUT @driverswithpath
TO "/ReferenceGuide/DML/QSE/Extract/Drivers.txt"
USING Outputters.Csv();

score 1 · Answer 2 · answered May 30 '17 at 21:25

As you can use .net methods on strings in U-SQL, you can use the Replace method to reconstruct the whole filepath, something like this:

DECLARE @in string = "input/{FileName}";

@drivers =
    EXTRACT id string,
            first_name string,
            last_name string,
            address string,
            FileName string
    FROM @in
    USING Extractors.Csv();


@driverswithpath =
    SELECT
        *,
        @in.Replace("{FileName}", FileName) AS InputPath
    FROM
        @drivers;

OUTPUT @driverswithpath TO "/output/output.csv"
USING Outputters.Csv();

How to get Full Path of file in U-SQL query

2 Answers2