concept U - SQL in category azure

appears as: U-SQL, U-SQL
Azure Storage, Streaming, and Batch Analytics: A guide for data engineers

This is an excerpt from Manning's book Azure Storage, Streaming, and Batch Analytics: A guide for data engineers.

Azure Data Lake Analytics uses a new coding language called U-SQL. U-SQL is not ANSI SQL. For starters, WHERE clauses use C# syntax. Declarative statements can be extended with C# functions. Query data comes from tables or files. We’ll discuss this new language more in chapter 8.

  • You’ll compare methods of structuring U-SQL for reuse, including creating indexed data stores in the U-SQL Catalog.
  • Listing 8.15. Declaring an anonymous function in U-SQL
    DECLARE @Trend Func<decimal, decimal?, string> = #1
    ((nodeValue, lastValue) => #2
    {
        if (nodeValue > lastValue)
        {
            return "Greater"; #3
        }
        else if (nodeValue < lastValue)
        {
            return "Lesser";
        }
        else
        {
            return "Same";
        };
    });
    
    @counts =
        SELECT
            s.Player,
            s.Node,
            s.NodeValue,
            s.EventTime,
            LAG(s.NodeValue, 1, 0) OVER(ORDER BY s.Player, s.Node, s.EventTime) AS lastNodeValue, #4
            @Trend(s.NodeValue, LAG(s.NodeValue, 1, 0) OVER(ORDER BY s.Player, s.Node, s.EventTime)) AS NodeTrend #5
            FROM [Players].[dbo].[f_Sensors]("v1") AS s;
    
    OUTPUT @counts
        TO "/Sandbox/User1/SensorTable/SensorData5.tsv"
        ORDER BY Player, Node, EventTime
        USING Outputters.Tsv(outputHeader: true);

    With these inline functions, you can convert, validate, and construct many variations of the raw data using U-SQL and C#. In the next chapter, you’ll learn how to create your own classes to add even more complex C# logic. The U-SQL catalog forms the container for database items, including assemblies of custom code. Now that you’ve seen some of U-SQL’s processing capabilities, you’re ready to expand on it with integrations for your own custom code and external endpoints.