none
Can you load data directly from SQL Server into the Data Lake

    Question

  • I am new to the data lake.  I have been going online to find some samples of loading a sql server table into the data lake.  I have not found anything.

    Can you create tables in the data lake using management studio?

    Can you directly connect to sql server and just take all data from one table in sql server and load it into a table in the data lake?


    Thank you

    Thursday, October 13, 2016 6:34 PM

All replies

  • Hi Is your SQL Server an Azure SQL Server, or on-premise server? If it is an Azure SQL Database you can connect directly. You can find a guide here: http://sqlblog.com/blogs/jorg_klein/archive/2016/06/17/setup-azure-data-lake-analytics-federated-u-sql-queries-to-azure-sql-database.aspx You cannot create tables in data lake using Management Studio. Tables are created by U-SQL jobs in Data Lake Analytics. If your data is in a on-premise SQL Server you should use Data Factory. With an on-premise Data Gateway you can use the copy wizard of Data Factory to copy the data to Data Lake Store. I would not recommend to copy data from a SQL Server to a Data Lake Analytics table directly, unless you are sure that the source table will not change (and this is rarely the case). What we are doing at my current client is this: we copy the data with data factory to data lake store. This is done with SELECT * FROM table, which will work for changes in the table. Data is stored as JSON-files, and then read in a way that makes schema-on-read possible, so that the data load is more robust than working with a schema-on-write pattern where data structures are more strict, and the solution less robust.
    Wednesday, October 19, 2016 3:49 PM