U-SQL FROM parameter-CodePudding

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)

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.

CodePudding user response：

Table2 is most likely a table valued function: 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.

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

CodePudding user response：

CREATE FUNCTION [C1].[stg].[TVF](
@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 = [C1].[stg].[TVF](@baseValues, @CurrentIteration   1, @MaxIterations, @BatchID);
END;
END;