How to check if there is records in a Data Lake table

Question

In SQL we can do IF Exists(select * from table) or IF (select count(*) from table) = 3, is there equivalent function in Data Lake / U-SQL?

thanks.

Answer 1

U-SQL is a declarative query language only that currently does not allow you to change control flow based on values with IF statements (IF statements at this point are executed at compilation time).

What would you want to do in the different IF branches?

Now depending on what you want to do in the IF clause, you have different options:

1. Move your IF condition into the query predicate (e.g.,

    @res = 
      SELECT a, b, c FROM @x WHERE condition
      UNION ALL 
      SELECT a, b, c FROM @y WHERE !condition;
   

   instead of

    IF (condition) THEN
      @res = SELECT a, b, c FROM @x;
    ELSE
      @res = SELECT a, b, c FROM @y;
    END;
   

   If your condition depends on a different table, you may need to do a (cross) join to get the condition correlated.

2. Split your script into smaller script and use ADF or Powershell for control flow. This may mean that you create intermediate results that you download though, so I normally prefer 1.

3. Last but not least: Often there may be a declarative way to express your logic that can avoid the conditional in the first place.