Update in USQL Azure Data lake

Question

Is there any way by which I can update a value in rowset

Data:

1,apple,0
2,check,1
3,chec,1

USQL script:

@result = EXTRACT
ID int,
value string,
types int
FROM @"TLD_BT/sacmple.txt"
USING Extractors.Csv();

Now I would like to update the above result set @result set type =1 where value is apple

I tried below script.

UPDATE @result SET types=1 WHERE value="apple"

But I get below error:

UPDATE  ### @result SET types=1 WHERE value="apple"

Error
   E_CSC_USER_SYNTAXERROR
Message
    syntax error. Expected one of: STATISTICS

Is there anyway by which I can update the value of a rowset or should I find out any other work around.

wBob wBob · Accepted Answer · 2016-10-25T16:30:02

There is no UPDATE command in U-SQL at this time but you could use the conditional statement to create a new column and output that. You could also use the CTAS syntax to create a new internal table. Examples below:

@result =
    EXTRACT [ID] int,
            value string,
            types int
    FROM @"input/input.txt"
    USING Extractors.Csv();

//UPDATE @result SET types=1 WHERE value="apple"
@output =
    SELECT [ID] AS id,
           value,
           value == "apple"? 1 : types AS types
    FROM @result;

// CTAS
CREATE TABLE IF NOT EXISTS dbo.interimResult
(
    INDEX cdx_Result
    CLUSTERED(id)
    DISTRIBUTED BY
    ROUND ROBIN
)
AS
SELECT [ID] AS id,
       value,
       value == "apple"? 1 : types AS types
FROM @result;


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

Hope that makes sense. Also see here: U-SQL DML Statements

Update in USQL Azure Data lake

2 Answers