azureazure-data-lakeu-sql

U-SQL FROM parameter


I was wondering if anyone could help me understand what the below code means and what is the purpose of using it.

DECLARE @INPUT_BatchID string = "9035";
DECLARE @BatchID int? = (int?) int.Parse(@INPUT_BatchID);

@dataset1 = 
SELECT col1,col2,col3 
FROM Table AS T

@dataset2 =
SELECT col1,col2,col3
FROM Table2(@dataset1, 0, 20, @BatchID)

TVF Code below:

CREATE FUNCTION [Table2](
@baseValues TABLE(ENT_MBR_NM string, [ALS] string,
[NC] string, [DELD] string,
PRNT_MBR_NM string, HIER_LVL int,
GEO_ENT_GENERATION_ID int?, [PATH] string,
RGN_CD string, GLB_GRP string),
@CurrentIteration int = 0,
@MaxIterations int = 20,
@BatchID int? = -1)
RETURNS @result TABLE(ENT_MBR_NM string, [ALS]
string, [NC] string, [DELD]string, PRNT_MBR_NM
string, HIER_LVL int, GEO_ENT_GENERATION_ID int?,
[PATH]string, RGN_CD string, GLB_GRP string) AS

BEGIN
DECLARE @cont1 string = "EMEA";
DECLARE @cont2 string = "EUROPE";
DECLARE @cont3 string = "TOTAL CHINA";
DECLARE @cont4 string = "ASIA PACIFIC";
DECLARE @cont5 string = "UNITED STATES";
DECLARE @cont6 string = "OTHER AMERICAS";
DECLARE @cont7 string = "TOTAL AMERICAS";
DECLARE @cont8 string = "MIDDLE EAST/AFRICA";
DECLARE @cont9 string = "OTHER ASIA PACIFIC";
IF (@CurrentIteration >= @MaxIterations) THEN
@result = SELECT * FROM @baseValues;
ELSE
@nextLevel =
    SELECT A.ENT_MBR_NM,
           A.[ALS],
           A.[NC],
           A.[DELD],
           A.PRNT_MBR_NM,
           T.HIER_LVL + 1 AS HIER_LVL,
           A.GEO_ENT_GENERATION_ID,
           T.[PATH] + " | " + (A.PRNT_MBR_NM ?? "*") AS [PATH],
           A.ENT_MBR_NM.Trim().ToUpper() IN (@cont5, @cont6, @cont2, @cont8, @cont3, @cont9) ? A.ENT_MBR_NM : T.RGN_CD AS RGN_CD,
           A.ENT_MBR_NM.Trim().ToUpper() IN (@cont7, @cont1, @cont4) ? A.ENT_MBR_NM : T.GLB_GRP AS GLB_GRP
    FROM @baseValues AS T 
         INNER JOIN
            [Wz].[Fc].[DH_FCS_GEO_ENT] AS A 
         ON T.ENT_MBR_NM == A.PRNT_MBR_NM
    WHERE A.PartitionID == @BatchID.Value;
    
@baseValues =
    SELECT *
    FROM @baseValues
    UNION ALL
    SELECT *
    FROM @nextLevel;
     
@result = [Table2](@baseValues, @CurrentIteration + 1, @MaxIterations, @BatchID);
END;
END;

This code takes lot of execution time which I want to reduce but I am not sure what the code part (@dataset1, 0, 20, @BatchID) in the @dataset2 means and the use of this.

Any help will be appreciated.

Thanks in Advance.


Solution

  • Table2 is most likely a table valued function(tvf): a function that, optionally, takes some parameters (like your example) and returns a set of records.

    Somewhere the Table2 function is defined in your script or a reference.

    The same applies to Table. It might be a tvf as well.

    Note: it has been a while since I last saw a question using u-sql. You know already the service is being retired?