CODEWITHSUNDEEP
azure-data-lakeu-sql
candidson
candidson

Reputation: 526

U SQL: direct output to SQL DB

Is there a way to output U-SQL results directly to a SQL DB such as Azure SQL DB? Couldn't find much about that.

Thanks!

Upvotes: 3

Views: 1169

Answers (1)

wBob
wBob

Reputation: 14399

U-SQL only currently outputs to files or internal tables (ie tables within ADLA databases), but you have a couple of options. Azure SQL Database has recently gained the ability to load files from Azure Blob Storage using either BULK INSERT or OPENROWSET, so you could try that. This article shows the syntax and gives a reminder that:

Azure Blob storage containers with public blobs or public containers access permissions are not currently supported.

wasb://<BlobContainerName>@<StorageAccountName>.blob.core.windows.net/yourFolder/yourFile.txt

BULK INSERT and OPENROWSET with Azure Blob Storage is shown here:

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/23/loading-files-from-azure-blob-storage-into-azure-sql-database/

You could also use Azure Data Factory (ADF). Its Copy Activity could load the data from Azure Data Lake Storage (ADLS) to an Azure SQL Database in two steps:

  1. execute U-SQL script which creates output files in ADLS (internal tables are not currently supported as a source in ADF)
  2. move the data from ADLS to Azure SQL Database

As a final option, if your data is likely to get into larger volumes (ie Terabytes (TB) then you could use Azure SQL Data Warehouse which supports Polybase. Polybase now supports both Azure Blob Storage and ADLS as a source.

Perhaps if you can tell us a bit more about your process we can refine which of these options is most suitable for you.

Upvotes: 7

Related Questions