Best way to extract data from Azure Data Lake to SQL Server

0
votes

I am looking for a best programmatic way to extract data from Azure Data Lake to MSSQL database, which is installed on a VM within Azure.

Currently I am considering following options:

Any other good ways I am missing?

1
azureazure-data-lakedata-extraction
Unfortunately at the moment extraction from ADLA directly to SQL Server is still not available - feedback.azure.com/forums/327234-data-lake/suggestions/… – Victor F
Hi @VictorF, I'm guessing they mean Azure Data Lake Storage (ADLS) rather than Azure Data Lake Analytics (ADLA)? – wBob
What version of SQL? What format are the files in? – GregGalloway
@GregGalloway It's SQL Server 2016 and it's inside a ADLA database table (but it can be a simple .csv stored on ADLS if it will be more convenient) – Victor F
@VictorF I was thinking that since you had SQL 2016 you could use Polybase. But I don't believe Polybase in SQL 2016 supports Azure Data Lake Store as a source. So nevermind that. docs.microsoft.com/en-us/sql/t-sql/statements/… For what it's worth, I think your list of options is good. – GregGalloway

1 Answers

1
votes

Data factory v2 (currently in public preview), also supports hosting SSIS to give you a data factory AND ssis option.

And not necessarily a good idea for many scenarios, but Azure Logic Apps has both a data lake store connector and SQL Server connector, which could be useful in scenarios such as writing lots of small files on a schedule or trigger.

You also may not need to go full on c# and instead use PowerShell, there are powershell modules for both data lake store and sql server.