Load Data from Azure Data Lake Storage to Azure database

Loading a Data from Azure Data Lake Storage to Azure Data Lake Database requires an azure data lake storage.

So you must have a file in azure data Lake storage.  If you do not know how to upload file in Azure Data Lake storage, you can refer post  Load files to Azure Data Lake Storage.

Here we have a file named as StudentInfo.txt in Azure Data Lake Storage within StudentData folder as shown below.

Now we will load that file into Azure data Lake database.

Go to Home page, select ResourceGroup under that click on Data Lake Analytics Account, here we have an account named as azuredatalakeacc.

Next, click on New Job button.

Once you click on New job, a New Job Page opens. Provide a Name for Jobs.

Next in code editor, we will write a U-SQL Scripts, which extracts the data from Azure Data lake storage, that means data is extracted from the file StudentInfo.txt that is within the StudentData folder. The Extracted data is stored into variable @data.

After, that data is taken from the variable and inserted into table studentInfo.

USE DATABASE Db_live;

@data = EXTRACT StudID int,

        StudName string,

        StudAddress String,

        RegDate DateTime

FROM "StudentData/StudentInfo.txt"

USING Extractors.Text(delimiter: '\t', skipFirstNRows: 1);

INSERT INTO Stud.StudentInfo

SELECT * FROM @data ;

Once you click on Submit button, you will get a job status as succeeded when job is completed successfully, also you can see a Job graph is generated for all the steps taken during the job execution.

Lets see check the data in table, Go back to catalog, click on database and then click on table to preview the data.

Once you click on table, A file preview page opens, there you can see the table data.

Querying Data

You can also see the table data by querying data, but for this you need to write another job to query the data.

On clicking a table, a file preview page opens that you have already seen above.

Now click on Query table button on file preview page.

Once you click on Query table, you will see a U-SQL scripts is generated by Data lake automatically, so you do not need to write any U-SQL scripts for querying the table data, just submit the Job.

@table = SELECT * FROM [Db_live].[Stud].[StudentInfo];
OUTPUT @table
    TO "/Outputs/Db_live.Stud.StudentInfo.tsv"
   USING Outputters.Tsv();