U-SQL Tutorial
Introduction
0 Meta
1 Getting started
U-SQL vs SQL
Running U-SQL scripts
Preparing for the tutorial
Install Data Lake Tools for Visual Studio
Your first U-SQL script
Common errors
The SearchLog dataset
2 Transforming RowSets
Creating RowSets from RowSets
Creating constant RowSets
Refining RowSets
Modifying columns with SELECT
Filtering RowSets with WHERE
Numbering rows
RowSets must lead to an output
Escaping column names
3 DECLARE Parameters
Supported datatypes
Expressions
Parameter type inference
4 Data types
Nullable types
5 Expressions
Order of evaluation in expressions
Basic expressions
String expressions
Tips for SQL developers
6 Reading and writing files
Built-in Extractors
Text encoding
7 FileSets
Filtering FileSets with WHERE
FileSets with dates
8 Grouping and aggregation
Conditionally counting
Filtering aggregated rows
Aggregate functions
Basic statistics
ANY VALUE
CROSS APPLY and ARRAY_AGG
9 The U-SQL catalog
Databases
Table-valued functions (TVFs)
Tables
Assemblies
Packages
10 Using U-SQL assemblies
.NET System assemblies
User-provided .NET assemblies
Sharing U-SQL assemblies across accounts
11 Window functions
Sample data
Window functions vs. GROUP BY
Window aggregate functions
Window analytic functions
CUME_DIST
PERCENTILE_CONT & PERCENTILE_DISC
PERCENT RANK
Window ranking functions
ROW_NUMBER, RANK, and DENSE_RANK
NTILE
Ranking: Assign unique row numbers
Ranking: Get TOP N rows in group
Ranking: Get ANY N rows in group
Ranking: Get row with min/max value
12 Set operations
Sample data
UNION
INTERSECT
EXCEPT
OUTER UNION
13 Joins
Sample data
CROSS JOIN
INNER JOIN and OUTER JOIN
SEMIJOIN
ANTISEMIJOIN
14 Complex types
Arrays
Maps
15 Extending U-SQL
User-Defined Functions (UDFs)
User-Defined Aggregators (UDAggs)
Recursive aggregators
DEPLOY RESOURCE
User-Defined Types (UDTs)
Persisting UDTs
16 Tips
Powered by
GitBook
2 Transforming RowSets
Transforming RowSets
In this chapter you'll focus on the fundamental ways in which rowsets can be created and modified.
results matching "
"
No results matching "
"