19. Databricks & Pyspark: Real Time ETL Pipeline Azure SQL to ADLS

[@maheshwarkuchana190]

A one stop solution to understand basics of ETL in Databricks. Thanks Mr. Raja for such amazing tutorials on your channel. We really benefit from them. Many thanks again.

[@rajasdataengineering7585]

Thank you Maheshwar!

[@gopireddybhargavi2844]

Thank you so much sir actually I am having support kind of experience in Azure I just followed all of your videos now I got placed in 2 mnc's

[@rajasdataengineering7585]

Thanks Gopi for sharing your experience. It is really amazing to know that you got placed in MNCs. All the very best! If you find this channel helpful, just spread across data engineering communities so that people can be benefited

[@OurOrangeKitchen]

Beautiful explanation and a very good example of ETL. Thanks a lot for this video. It helped a lot in gaining a clear picture of the ETL process in Databricks.

[@rajasdataengineering7585]

Thank you

[@archichaudhari9283]

Best video series.

[@rajasdataengineering7585]

Thank you

[@mitusa1234566]

Great video with nice and clear instructions.. keep it up. Thanks.

[@rajasdataengineering7585]

Glad it was helpful! keep watching

[@tejaswinisunkavalli2086]

Great Explanation and easily understandble👏👏

[@rajasdataengineering7585]

Glad you liked it! Thanks for your comment

[@priyankapushp8271]

Wow very nicely explained. Thanks a lot for your efforts.

[@rajasdataengineering7585]

You are most welcome

[@ranajaymondal390]

Nice explanations and this series is really awesome. please create more videos on databricks while solving some real time ingest/export requirements using pyspark.

[@rajasdataengineering7585]

sure, will do more videos on this series

[@Rk-mv8sz]

It's really very helpful. Please make a video on end to end project with ADF and ADB. Thank you for giving wonderful videos.

[@rajasdataengineering7585]

Sure, will do

[@navk4960]

it would be really amazing if the links to topics - mentioned to refer are added in description. as you are an amazing tutor

[@alwalravi]

Grt thanks for sharing the video

[@rajasdataengineering7585]

Glad it helps!

[@arrooow9019]

No words to express my feelings.How great this tutorial sir.Thanks for this video👍 and also could you make any video how to clear left space and characters in string while cleansing data?

[@rajasdataengineering7585]

Thank you. Sure will post a video on your requirement

[@pawanukey2990]

Hi Raja , You have explained this in detailed . Thanks for that ,But can you please provide the data set ?? To do hands on activity ??

[@sriharichennupati5]

Thanks for sharing this info

[@rajasdataengineering7585]

My pleasure! Welcome

[@phanisrikrishna]

Hi Sir, It is a great series and well structured ones with regular topics and interview questions. Can you also share the notebooks for reference and practice. Thanks a lot in advance.

[@VanakkamSQL]

Great work!!!!!!!!!

[@rajasdataengineering7585]

Thank you! Cheers!

[@VanakkamSQL]

How can i reach you ?

[@prathapganesh7021]

Thank you for the content

[@rajasdataengineering7585]

Thanks Prathap! Glad it helps

[@zubairmushtaq7912]

read from azure sql db and write it again in azure sql db please make a video on it

[@rajasdataengineering7585]

Sure, will create a video on this requirement

[@mustafakamal5945]

This is very informative video. Do you also have a video on connecting to SQL DB via Managed identity?

[@rajasdataengineering7585]

Thank you for your comment! No I haven't a video on managed identity

[@mustafakamal5945]

@@rajasdataengineering7585 I am struggling to find a resource for implementing it, do let me know if you know of any resources for guidance and help on this topic apart from ms learn site

[@paarthiban8452]

A nice video. Can you create another video to automate this pipeline using Airflow?

[@rajasdataengineering7585]

Sure Paarthi, will create a video on this requirement

[@sravankumar1767]

Superb bro 👌 👏

[@susobhanghosh5093]

It will be very helpful if u can share the notebook also in HTML format

[@dianadai4616]

Do you have your codes posted somewhere? It is very important for us to follow along

[@naren06938]

Can u have all these commands in ur github repo, can u give repo link to practice....

[@mohsintamboli4394]

Best example

[@rajasdataengineering7585]

Thanks

[@mujeebrehman1146]

Great tutorial but i have a question. Can we connect oracle database to databricks?

[@rajasdataengineering7585]

Yes we can connect oracle database too

[@BhakthiYoutube]

Can you please provide us the some big data end to end projects involving all components

[@rajasdataengineering7585]

Hi, I have already created one video on end to end project using multiple components. Pls refer the video number 87 kzbin.info/www/bejne/mqnbiYqbacyBitE

[@chinna4549]

Nice anna

[@rajasdataengineering7585]

Hope it helps!

[@ranaumershamshad]

I read data from a huge table in Azure SQL DB and wrote it to ADLS. It created one file of 900 MB instead of partitions. Is there any parameter we can change to create the partitions?

[@anonymous-254]

Instead of ADLS... can we put that data in synapse dedicated sql pool

[@rajasdataengineering7585]

Yes we can load into azure synapse and azure SQL also. Please watch the video no 87 in this channel

[@anonymous-254]

@@rajasdataengineering7585 ok thanks

[@AdexDurojaiye]

Do you have a class where you can train me. thanks for your video.

[@what_worldgot]

Where to find dataset for this table ??

[@gunasekar_vs]

Thanks for great explanation 🙏🙏... I couldn't able to see the code clearly so if you don't mind can you pls share the code. We can also try once by following your videos 🙏🙏

[@anil6328]

Explained very well 👌 Can someone help on how to setup jdbc without showing password in the code?

[@rajasdataengineering7585]

Hi Anil, yes that is possible. I have already explained that concept in key vault integration video. Please go through once kzbin.info/www/bejne/mWOonoeJlNhosNk

[@manikiranthota2811]

In Azure SQL db itself we can do the null handling ,join and duplicates deletion.why we are using data frame is there any reason specific to that.Thanks in advance.

[@rajasdataengineering7585]

The requirement is we need to move the data from azure SQL to adls after performing these transformations. Could you please explain how you will do these transformations in azure SQL itself as part of this requirement?

[@manikiranthota2811]

@@rajasdataengineering7585 yes sir.i got it.i thought of using isnull operation on product ,delete the duplicates in fact and join the 2 tables in Azure SQL db itself but that will take too much time.Thanks.

[@shot_freeze]

@@rajasdataengineering7585 Hi Raja , Could you please share us few requirements like this for us think logically , So that we can have some clear idea how we will get in our projects.

[@rajasdataengineering7585]

Sure will do

[@shot_freeze]

@@rajasdataengineering7585 You can just share it here or either comment outside and Pin it .

[@Umerkhange]

How to apply multiple rules in a single statement like SUM("UnitPrice"), SUM("TOTALLINE), AVG(PRICE) ETC

[@PriyaDarshiniNeverSayNever]

Getting error driver not found @ send step. Please help how to solve this?

[@Learn2Share786]

@raja, nice explanation.. can you pls share notebook ?

[@nagulmeerashaik5336]

When we learn azure And also required to learn pyspark?

[@rajasdataengineering7585]

Not really. Azure has many services within data engineering like adf, synapse you analytics, databricks etc. Pyspak is mainly needed for databricks developer and spark pool inside synapse. If your project is on adf, Pyspark does not play any role

[@nagulmeerashaik5336]

Thanks.

[@alluchandrasekhar2992]

What if multiple tables(more than 10) needs to copy from azure sql db to data lake

[@rajasdataengineering7585]

We can create multiple dataframes reading multiple tables and load them into adls

[@alluchandrasekhar2992]

@@rajasdataengineering7585 yes we but can it be parametarized if yes then how?

[@rajasdataengineering7585]

Yes parameters can be setup using widgets

[@alluchandrasekhar2992]

@@rajasdataengineering7585 okay and BTW your video's are very informative .......keep doing such great videos.

[@RAHULGUPTA-wy2zb]

Hi Raja...would u plz tell that why do you take left outer join not inner join

[@pavanaditya2309]

Since this is onlya sample logic he is trying to do, I think for demonstration purpose does'nt matter which join he uses.

[@kneelakanta8137]

how did you get tables in azure sql database

[@rajasdataengineering7585]

We can create tables using create table statement. Otherwise you can use readymade adventure works database by choosing it in additional settings while creating the database

[@nivasnivi]

Can you help me on one small assignment sir please??

[@shalinikumari-qx9tn]

I want the dataset that you used..how to get

[@rajasdataengineering7585]

Hi Shalini, I have used the sample database adventure works in this exercise. It is open source dataset

[@kartikeshsaurkar4353]

While creating azure sql db you'll see the option to have sample tables/databases. After creation, you'll see few tables will be present by default

[@shot_freeze]

@@kartikeshsaurkar4353 is that table has any data in it by default have u checked it ?

[@rajasdataengineering7585]

Yes Ajay, it would have sample data as well to practice

[@shot_freeze]

@@rajasdataengineering7585 Thanks Raja !!! For your reply , Your videos are really helpful to prepare Azure Data Engineer.

[@srinivasanmadeshwaran1130]

Hi Sir, i am getting below error when i try to connect jdbc java.sql.SQLException: No suitable driver pls help on this

[@ayushbhatt9469]

.option("driver", jdbcDriver) add this code as well in the second step to resolve your issue

[@hozefakanchwala8720]

It's a nice use case for batch processing but you shouldn't call it Real Time ETL.

[@rajasdataengineering7585]

Real time does not mean streaming data here. It means one of the real time use case for ETL requirement.

[@sujitunim]

👍👍

[@sravankumar1767]

How to handle bad records in Azure databricks

[@rajasdataengineering7585]

Hi Sravan, I have already posted a video on how to handle bad records. You can refer that kzbin.info/www/bejne/rZCkiJSOo8Z8b5Y

[@sravankumar1767]

@@rajasdataengineering7585 thanks

[@MRCyberstriker]

No intermediate steps explained, a beginner will find difficulty following! Please make it beginner friendly. take it as feedback

[@rajasdataengineering7585]

The intermediate steps are already explained in videos 17 and 18. Pls watch them as a prerequisite to this video kzbin.info/www/bejne/mIvdmWqhm6eWrpY kzbin.info/www/bejne/rqmxaWt5lpmom9U

[@nagulmeerashaik5336]

Most companies are asking that's why