Create a View with the identifier as a variable on u-sql

Question

I would like to create a view using U-SQL. The name of the view can only be defined at compile time using the DECLARE function.

Visual Studio throws a syntax error when i try to use the variable in the create view statement.

Is there a workaround for this?

score 0 · Answer 1 · answered Aug 21 '17 at 11:52

U-SQL does not support dynamic U-SQL at this point although if you feel this is an important missing feature you could post a feature request here:

https://feedback.azure.com/forums/327234-data-lake

It looks like there is already a similar request you can vote for here.

As a workaround, you could generate the U-SQL dynamically then run the script separately either manually or using one of the SDKs eg using Powershell, .net. A simple example:

//Dynamic U-SQL
DECLARE @viewName string = "dbo.vw_yourViewName";

// Create dynamic U-SQL
@usql = 
        SELECT * 
        FROM ( VALUES
            ( "USE DATABASE yourDatabase;"),
            ( String.Format("DROP VIEW IF EXISTS {0};", @viewName)),
            ( String.Format("CREATE VIEW IF NOT EXISTS {0} AS EXTRACT col1 int, col2 string, col3 string, col4 string, col5 string FROM \"/input/input44.txt\" USING Extractors.Csv();", @viewName))
             ) AS  x (usql);


// Output the statements in the correct sort order
OUTPUT @usql
TO "/output/dynamic.usql" 
USING Outputters.Text(delimiter:' ', quoting:false);

Other examples of dynamic U-SQL (or more precisely, U-SQL generated dynamically) are here and here.

Create a View with the identifier as a variable on u-sql

1 Answers1