Use string text from file in u-sql query where clause ( U-SQL )

Question

I need load some text from one file and choose specific records from another where one of second file columns is equalt to text loaded from first file.

I'm trying with something like that but actually it doesn't work.

@countryName =
    EXTRACT City string
    FROM "/TestCatalog/test.txt"
    USING Extractors.Text();

@result =
    SELECT CityName,
           Temperature,
           MeasurmentDate
    FROM @readEmployee
    WHERE CityName IN(@countryName);

What is the best way to pass some parameters to where expression ( readed from another file in azure data lake ) ?

Answer 1

Variables in U-SQL which are assigned to with EXTRACT or SEELCT are rowsets, rather than scalar variables. Therefore use SEMIJOIN to do this, for example:

@output =
    SELECT re.CityName,
           re.Temperature,
           re.MeasurmentDate
    FROM @readEmployee AS re
            SEMIJOIN @countryName AS c ON re.CityName == c.City;

Answer 2

EXTRACT this other file into another rowset, and JOIN both rowsets together.