Editorials

U-SQL

So you like the simplicity of the SQL Language. What if you could use SQL syntax against data that is not structured? Well, that is U-SQL.

To get started using U-SQL you can find an introduction with links to more tutorials at https://docs.microsoft.com/en-us/azure/data-lake-analytics/data-lake-analytics-u-sql-get-started.

We already have the ability to work with data that may not be contained in an SQL Server database using the Open Rowset command. However to use Open Rowset, the data must already be in some structured form, such as a comma separated value file. U-SQL also allows you to work with that same kind of file, allowing you to define the structure of the data as it is being read. There is a default csv file reading function already built in. However, you can write your own functions for reading external data stores, that may not be so nicely structured in table format.

With U-SQL you combine the power of SQL with C#, allowing you to use two familiar languages when dealing with data external from SQL Server. Your queries have three parts.

First, define the data you are reading. You provide connection information to the data store, and a data transformation definition allowing you to read in the data. This is not a lot different than using BCP, except you can write your own data transformation code in C#, and you don’t have to import the data into an SQL Server table. Using your connection and transformation function you can now define the scalar values (table definition) to be read in from the data.

Second, write your SQL like queries against this newly defined virtual table. I say that the query is SQL like because it combines some constructs of C# with SQL, providing you with a lot of power.

Third, output the results to the desired destination.

There is much more that can be done. This just touches on some of the basics in order to catch your interest. Go to the link above for more details.

Cheers,

Ben

Facebooktwitterredditpinterestlinkedinmail