9. How to do full load from On Premise SQL Server till ADLS using Azure Synapse Pipelines

This video tells how to do full load from On Premise SQL Server till ADLS in a Parquet format using Azure Synapse Pipelines.
We used Azure SQL DB to store the metadata details. We loaded data from On premise SQL server to ADLS layer.
To prepare the metadata we have used this query :
select a.TABLE_SCHEMA as Table_Schema, a.TABLE_NAME as Table_Name ,a.TABLE_CATALOG,
'select '+ string_agg(b.column_name,',') + ' from ' + a.TABLE_SCHEMA+'.'+a.TABLE_NAME
as Query, 'Y' as IncrementalFlag, 1 as Isactive
from INFORMATION_SCHEMA.tables a
inner join INFORMATION_SCHEMA.COLUMNS b
on a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME
where TABLE_TYPE='BASE TABLE'
group by a.TABLE_SCHEMA,a.TABLE_NAME,a.TABLE_CATALOG
#azuredatafactory
#azuresynapseanalytics
#datafactory
#azure

[@ravitejak3597]

Amazing work, love how you explained each step-in detail.

[@user-kakatya]

I am so happy I found your channel! Thank you very much, very well explained and only the necessary information!

[@azurecontentannu6399]

Thankyou so much 😊

[@raghum4938]

Fully impressed with the flow of explation and content on the subject.. Well done Annu!! thanks for the video!

[@azurecontentannu6399]

Thank you 😊

[@MadhurAcousticGuitarist]

Please keep creating videos like this, don't stop here only!! Request!! As it help for the beginners a lot. :)

[@vildotube]

Amazing concept and perfect video. I'm eagerly waiting for the incremental load part.

[@jackvieiraoficial]

thank you very much, excelent work!

[@sourav7413]

love this video ...really informative...

[@sudippandit7051]

super explanation Annu!! Good Job!🙂

[@agnesxavier290]

Thanks for the effort! ive learnt a good deal. Eagerly expecting incremental load. Since im a newbie in Azure, this helps a lot in understanding the mistakes I made with my pipeline.

[@azurecontentannu6399]

Thanks for watching 😊 will surely create video on incremental load soon. Stay tuned.

[@lopu8000]

@@azurecontentannu6399 please post more videos

[@user-zb9kn9pn9d]

Hi Annu.. Can you please make a video on how we can connect to on premise MS Sql server from Azure synpase notebooks?? I want to query on-premise tables from notebook.. is this possible?

[@rajbavisi3284]

@Azure Content: Annu Hello Annu, very nice explanation, thanks for your all efforts, I think you didn't show that now how you can move data from ADLS Parquet file to Dedicated SQL POOL, could you please make a video on this as well that how can we move data from ADLS to Dedicated Azure SQL

[@azurecontentannu6399]

Please check part 10 and 11 😇 Thanks

[@Kiran-eb3rm]

Hey, can't we use notebooks to read the on premise sql tables into dataframes.

[@user-kw7tq8ig9j]

Can you please give the incremental video link. Thankyou so much. your video is very informative.

[@azurecontentannu6399]

kzbin.info/www/bejne/f4HTineHZbZ4lac

[@galivenkataramana4142]

How to do a metadata-driven table? Using pipeline dynamically.

[@srinathyellasiri8422]

Hi madam Many interviewer Asking how to improve your pipeline speed while you moving data from onpremise SQL to Adls can you please

[@abdulkhan1310]

Is this the preferred method when moving 3TB Database from On Prem to Synapse Dedicated Pool? Thank you very much for this informative video.

[@azurecontentannu6399]

Yes polybase is the preferred way to copy data to synapse

[@zahidzafer4704]

It's very good video for full load. Just one query when we perform the truncate operation after first load

[@azurecontentannu6399]

Hi Zahid.. Thankyou for watching the video. In this video, we just loaded the data till ADLS. In the next video (part 10) we are loading the same till dedicated sql pool. We are having truncate table statement there before the full load. Check part 10 video after 23rd minute

[@harikrishnarevuri5480]

Hi Annu.. thanks for sharing your knowledge. instute of querying from source side.. can't we do log based like using transaction logs

[@azurecontentannu6399]

Hi hari.. Sorry i didn't get ur question. The metadata query?

[@harikrishnarevuri5480]

@@azurecontentannu6399 Log based CDC. When a new transaction comes into a database, it gets logged into a log file with no impact on the source system. And you can pick up those changes and then move those changes from the log.

[@DeepDeep-zd5jq]

Nice video .Can we load data from azure synapse to on premise SQL.

[@azurecontentannu6399]

Yes you can

[@sreenivasacharytatikonda2189]

Excellent video. Nice explanation. I have one doubt. Can you please clarify. If we move on premise sql dw database into Azure Synapse for multiple users. Assume that each user has one dw database, so we have 8 dw databases in on premises. how can we move all 8 dw databases data into Azure Synapse? can we differentiate each user data with different schema?. Please guide me. Thanks for the support.

[@azurecontentannu6399]

Hello Sreenivasa. Thanks for watching the video. Yes you can create different schema corresponding to each databases from the source. No need to create multiple databases , distinguishing based on schema will do the job. Also, another approach you can rely on is to create an additional column for all the tables that will hold the source dbname, this way you can merge tables(having same name and table definition) from different databases from source into single table in destination with a column holding source dbname. Hope it helps

[@sreenivasacharytatikonda2189]

@@azurecontentannu6399 Thanks for the reply madam. So we are separating user with different schema. I think we need to create those new schemas in Azure synapse before loading the user data..am i right?

[@azurecontentannu6399]

@@sreenivasacharytatikonda2189 If you select auto create table , schema and table both will be automatically getting created for you during the runtime

[@sreenivasacharytatikonda2189]

@@azurecontentannu6399 Thank you Madam :)

[@sriramch3128]

Good job, but can u explain this by taking small data

[@azurecontentannu6399]

I have taken small set of data . Only 3 tables with around 20 records each

[@srinathyellasiri8422]

Hi madam Please do incremental load as the same source and sink

[@azurecontentannu6399]

Hi sir Please check part 11

[@prabhatgupta6415]

is it a ideal way to load data incrementally using ADF or do we go for different approach in data bricks. I dont know coz not much content are there on utube. Tell me the preffered way@@azurecontentannu6399