[U-SQL]][FlexExtractor] I Can't join. The expressions must both be columns

1
votes

I am working with Cortana Analytics Suite. Currently analyzing data of a Data Lake store with a visual studio data lake analytics project with files containing hundred of columns. After reading the u-sql docs I found a flex extractor to help with this problem (hundred columns). The flex extractor was working perfectly with one table. The problem starts with joining tables, I am getting this error when compile:

"Error E_CSC_USER_JOINCOLUMNSEXPECTEDONEACHSIDEOFCONDITION: The expressions map[0] and map_2[0] on each side of the comparison must both be columns."

I have two extractors like this:

@data = EXTRACT map SqlMap<Int32,string>
    FROM @input
    USING new USQLFlexExtractor.FlexExtractor();

@data_2 = EXTRACT map_2 SqlMap<Int32,string>
    FROM @input_2
    USING new USQLFlexExtractor.FlexExtractor();

@output =  SELECT map[0], map[2], map_2[1]
FROM @data AS data
LEFT JOIN @data_2 AS data_2 ON map[0] == map_2[0]

I am following this example (usql script and c# code behind): https://github.com/Azure/usql/tree/master/Examples/FlexibleSchemaExtractor/FlexibleSchemaExtractor

I have even tried a different approach with the Combine expression but I got the same error.

Any ideas why?

Regards

1
azureazure-data-lakeu-sql

1 Answers

3
votes

U-Sql does not support derived columns in join conditions. You can try to use an intermediate result set like this:

@dataExtract = EXTRACT map SqlMap<Int32,string>
    FROM @input
    USING new USQLFlexExtractor.FlexExtractor();

@dataExtract_2 = EXTRACT map_2 SqlMap<Int32,string>
    FROM @input_2
    USING new USQLFlexExtractor.FlexExtractor();

@data =
    SELECT map[0] AS map0,
           map[2] AS map2
FROM @dataExtract;

@data_2 =
    SELECT map[0] AS map2_0,
           map[1] AS map2_1
FROM @dataExtract_2;

@output =  SELECT map0, map2, map21
FROM @data AS data
LEFT JOIN @data_2 AS data_2 ON map0 == map2_0;