Part 3- Data Ingestion (1) | End to End Azure Data Engineering Project

Рет қаралды 45,150

Жыл бұрын

#azuredataengineer #endtoendproject #azuredataengineeringproject #azureintamil #azuredatafactory #azuredatabricks #azuresynapseanalytics #azuredatalake #datalake #powerbi #keyvault
This is a long awaited video of mine- Lets build a complete End to End Azure Data Engineering Project. In this project we are going to create an end to end data platform right from Data Ingestion, Data Transformation, Data Loading and Reporting.
The tools that are covered in this project are,
1. Azure Data Factory
2. Azure Data Lake Storage Gen2
3. Azure Databricks
4. Azure Synapse Analytics
5. Azure Key vault
6. Azure Active Directory (AAD) and
7. Microsoft Power BI
The use case for this project is building an end to end solution by ingesting the tables from on-premise SQL Server database using Azure Data Factory and then store the data in Azure Data Lake. Then Azure databricks is used to transform the RAW data to the most cleanest form of data and then we are using Azure Synapse Analytics to load the clean data and finally using Microsoft Power BI to integrate with Azure synapse analytics to build an interactive dashboard. Also, we are using Azure Active Directory (AAD) and Azure Key Vault for the monitoring and governance purpose.
Part 4 will be uploaded soon. Stay tuned.
- - - Book a Private One on One Meeting with me (1 Hour) - - -
www.buymeacoffee.com/mrktalkstech/e/166354
- - - Express your encouragement by brewing up a cup of support for me - - -
www.buymeacoffee.com/mrktalkstech
- - - Other useful playlist: - - -
Azure Data Factory Playlist: kzfaq.info/sun/PLrG_BXEk3kXwTClTt3_28CMz2dZoaFhKD
Azure General Topics Playlist: kzfaq.info/sun/PLrG_BXEk3kXxv0IEASoJRTHuRq_DUqrjR
Microsoft Fabric Playlist: kzfaq.info/sun/PLrG_BXEk3kXybedCIBBI4lmaIbtbn7MdM
Azure Databricks Playlist: kzfaq.info/sun/PLrG_BXEk3kXznRvTJXwmazGCvTSxdCMsN
Azure End to End Project Playlist: kzfaq.info/sun/PLrG_BXEk3kXx6KE4nBmhf6QwSHMbznP2W
Databricks CICD Playlist: kzfaq.info/sun/PLrG_BXEk3kXzMLDAgRbDsbIKvhoWsu-Gq
End to End Azure Data Engineering Project: kzfaq.info/get/bejne/n7dkZLqiza7LnJ8.html
- - - Let’s Connect: - - -
Email: mrktalkstech@gmail.com
Instagram: mrk_talkstech
- - - Tools & Equipment (Gears I use): - - -
Disclaimer: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting me so I can continue to provide you with free content each week!
DJI Mic: amzn.to/3sNpDv8
Dell XPS 13 Plus 13.4" 3.5K : amzn.to/45KqH1c
Rode VideoMicro Vlogger Kit: amzn.to/3sVFW8Y
DJI Osmos Action 3: amzn.to/44KYV3x
DJI Mini 3 PRO: amzn.to/3PwRwAr
- - - About me: - - -
Mr. K is a passionate teacher created this channel for only one goal "TO HELP PEOPLE LEARN ABOUT THE MODERN DATA PLATFORM SOLUTIONS USING CLOUD TECHNOLOGIES"
I will be creating playlist which covers the below topics (with DEMO)
1. Azure Beginner Tutorials
2. Azure Data Factory
3. Azure Synapse Analytics
4. Azure Databricks
5. Microsoft Power BI
6. Azure Data Lake Gen2
7. Azure DevOps
8. GitHub (and several other topics)
After creating some basic foundational videos, I will be creating some of the videos with the real time scenarios / use case specific to the three common Data Fields,
1. Data Engineer
2. Data Analyst
3. Data Scientist
Can't wait to help people with my videos.
- - - Support me: - - -
Please Subscribe: www.youtube.com/@mr.ktalkstech

Пікірлер: 113

@mohamedihras Жыл бұрын

I have get many clairification about azure data engineering in this channel. Thax sir 😀

@claudiocampolo4660 5 ай бұрын

fantastic, thank you we're testing Azure DF potential for risk management. Thank you very much

@ChanceMinus 9 ай бұрын

Excellent! Thank you.

@jeffh566 Жыл бұрын

Best ADF demo, thanks for sharing.

@mr.ktalkstech Жыл бұрын

Thank you :)

@DeepaliLamkane 2 ай бұрын

excellent content, clarifying everything about Azure DE

@mr.ktalkstech 25 күн бұрын

Thank you so much :)

@VinodKumar-lg3bu Жыл бұрын

wow loved the explanation , to the point .Thanks for sharing the knowledge .

@mr.ktalkstech Жыл бұрын

Thank you so much :)

@Esports36 5 ай бұрын

where is datasets@@mr.ktalkstech

@manjunathbn9513 Жыл бұрын

Hi Sir, Excellent Video. Clearly explained. Please upload all other videos. Waiting. Thanks

@mr.ktalkstech Жыл бұрын

Thank you, the next part will be uploaded this week.

@mr.ktalkstech Жыл бұрын

I have just uploaded the next part- cheers :)

@manjunathbn9513 Жыл бұрын

@@mr.ktalkstech thank you so much

@nehashahpatel1741 3 ай бұрын

Million Thanks

@happyheart9431 3 ай бұрын

million thanks

@rajeshwarreddyracha4655 11 ай бұрын

Excellent work... please do more videos on real time projects... waiting for another Azure E2E pipeline

@mr.ktalkstech 11 ай бұрын

Thank you so much :)

@mohamedihras Жыл бұрын

Useful Channel 😍

@mr.ktalkstech Жыл бұрын

Thank you :)

@peakythomas21 9 ай бұрын

Where we can get data base script to use that in our laptops? I'm doing this project along side you.

@selairendri9997 23 күн бұрын

Hello THANKS for your pretty videos. I have a question , if i have 3 environments ( DEV, TEST,PROD) , I need to create new pipeline for each environment ?

@MultiKalleman 4 ай бұрын

Having problems with the copy activity. The column 'geospatial' in the address table cannot be copied, due to its datatype 'geography'. I don't know how to handle this can anyone help?

@sahildeshmukh2148 10 ай бұрын

Hi Mate, I am getting the below error while installing the SHIR, Could you please help Error:- Detail Error: {{Error [1]. }}Service '[2]' ([3]) failed to start. Verify that you have sufficient privileges to start system services. 1920 Integration Runtime Service DIAHostService Suggestion: Please use the manual setup to install the Integration Runtime (Self-hosted).

@LuckyAzher 17 күн бұрын

does we need to have sql server configure in azure before we connect in azure data factory please help

@TrinhDang-zt1yz 7 ай бұрын

Ironically, if i have never use MS SQL server before or any type of SQL database. i wouldn't really know much about how you can fix this. He should really cover on how to install and add adventure database since it can be challenging if it is your first time touching on these subject.

@SaiNathVinnamudi Ай бұрын

I am getting error like " "Schema import failed: Please select a table"" after connecting ssms and selecting table. Could you help me in resolving this.

@sganpisetty 5 ай бұрын

Excellent videos, will that possible to share the Database file for practice?

@odyelouej6433 5 ай бұрын

problems with linking : Open MSSMS Select Security. Then, navigate to Logins and search for your user. Right-click on the user and select Properties. Reset the password Click on the Server Roles tab. Choose Public and Sysadmin roles. Click OK to apply the changes. This process should resolve the linking issues.

@zuhailiaminuddin520 Ай бұрын

Your comment save my time. Tq very much

@LuckyAzher 17 күн бұрын

hi im unable to connect on prem sql server error make sure sql db firewall alows integration to acess please help wasted whole day no solution

@karanswamygowda 9 ай бұрын

not able to access access policies please help

@dasundhananjaya299 9 ай бұрын

Hi I have an issue in access policies in 12:47 . There are no any access policies avilable there. Can you please help to fix it. I almost try everything

@ultra----- 6 ай бұрын

grant the 'proprietary' access in the key-vault-demo for the current user what u using

@mithrahkalaimannan 5 ай бұрын

Where can I find the database?

@justvenkyy...3423 6 ай бұрын

please post on steaming data and challenges

@abbielmi9163 2 ай бұрын

nice video, i m having trouble when i create newlinked service and tested but failed. i m not sure why is failing I trying windown authentication and sql authentication as well and both failed.

@ankitchourase 21 күн бұрын

I'm facing the same issue

@priyabratbishwal5149 Жыл бұрын

I am using mac , is there any way to use Self hosted Integration Runtime ?as it shows .exe file if i download

@mr.ktalkstech Жыл бұрын

It's not supported in mac os I believe, you may need to create a Azure VM and install the SHIR in the VM

@B22.Dota2 11 ай бұрын

I am planing to go for Data Engineer jobs, In this sample project I dont see any coding. Is it requred to be in this field ? can you please clarify this thanks in advance.

@mr.ktalkstech 11 ай бұрын

Coding is used in the Data Transformation part :)

@balli4.44 Жыл бұрын

Hello! I really appreciate this excellent tutorial. However, when I tried to test the connection for the on-prem SQL server as per at 15:15 of the video, I encountered an error with the code "failed to connect." Could you kindly assist me with this issue? Thank you in advance for your help!

@mr.ktalkstech Жыл бұрын

Please use the same credentials in the SSMS and see if you can connect to the DB successfully or not- If you can do it, please check the secret values in the Key Vault, there might be something wrong there.

@myahya968 10 ай бұрын

i faced the same probelm, my linked service is not connecting

@myahya968 10 ай бұрын

did you solve the issue?

@abhishekr5447 6 ай бұрын

hey did you solve the issue? @@myahya968

@venkatramana4475 Жыл бұрын

Hello bro, its a really good explanation. I'm stuck at 15:13 it says "cannot connect to SQL server Database. Please conatact SQL server team further support. Make sure SQL database firewall allows the integration runtime to access". Where can I see if the SQL database can give access to integration runtime. Please help me in resolving this. TIA

@raulgatto6326 11 ай бұрын

Were you able to solve this? I can't find a solution..

@josel2719 11 ай бұрын

I get the same error, how do you resolve it?

@myahya968 10 ай бұрын

are you using sql authentication or windows authentication? @@seusbeats534

@TambeAjinkya 4 ай бұрын

@@josel2719 did you guys got the solution. If yes please share! Thanks in advance

@TambeAjinkya 4 ай бұрын

did you guys got the solution. If yes please share! Thanks in advance

@venkatashasidharreddychali7485 Жыл бұрын

Hello bro, Thank u for replying to all my issues, I got one more error, "Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user '.," Please help me on this, Thanks in Advance

@mr.ktalkstech Жыл бұрын

Hello, Sure. 1. Please first check if the username and the password that you have in the Key vault is correct or not. Also check the server name and database name. 2. If correct, please use the same credentials to connect to the server/db using the SSMS. 3. If connected successfully, right click on the sever name in SSMS and click the properties option, under the properties menu go to the connection and see if the "Allow remote connections to the sever" checkbox is enabled or not. If not, please enable it and then give it a try. I also assuming that, you have given the db_owner access to the user login as per the Part 2 video (environment setup)

@raulgatto6326 11 ай бұрын

Did you solve this? I have the same issue

@rohanthekanath5901 9 ай бұрын

@@mr.ktalkstech in Part 2 video you gave db_datareader access and not db_owner access. I still selected both the steps and I am not able to solve this error. Below is my error: Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'localhost', Database: 'AdventureWorksLT2017', User: . Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user ''., SqlErrorNumber=18456,Class=14,State=1,

@rohanthekanath5901 9 ай бұрын

The solution to this issue is to restart the SQL server once we give access to the user created. To restart the server, right-click the server name and select restart.

@arihantjain2432 Ай бұрын

@@rohanthekanath5901 Restarting SQL Server did not help.

@DeepakKumar-fx3hr 3 ай бұрын

My pipe line is getting failed due to JRE missing . I tried installing JRE and when i use sink as parquet ,it is repeating the same error. Could you please help me

@falgunkhimasiya2531 Ай бұрын

could you find a solution?

@ankitchourase 18 күн бұрын

Add path in environment variables

@mosesakwagiobe4438 6 ай бұрын

my test connection @ 15:19 to my on prem sql server is failing, i have created the key vault correctly. any idea why? please help

@Akash_Lonkar 5 ай бұрын

Same for me. Did u get any solution?

@mosesakwagiobe4438 5 ай бұрын

@@Akash_Lonkar yes after many trial and error I bumped into solution that should have been mentioned in the video. It was just the authentication type. Use sql authentication instead of default windows authentication to login on ssm. Hope that helps

@sudeepbehera5921 4 ай бұрын

Yes it works for me. After changing to sql authentication, i had to setup the user name and password in SSMS also. Thanks@@mosesakwagiobe4438

@TheMapleSight Жыл бұрын

Hi, I'm unable to connect to database. I wrote my login, password and so on. Only difference was that I didn't login with key vault. What might be also the issue?

@mr.ktalkstech Жыл бұрын

What's the error you are getting? please use the same credentials in SSMS to connect to the database, if its working in SSMS then it should work in Azure as well. Otherwise, create a new user and try again by following the steps mentioned in the video. Hope it works for you :)

@TheMapleSight Жыл бұрын

@@mr.ktalkstech thank you sir. I coped with this problem. The issue was to change windows authentication to SQL server authentication. I'm currently following your project further. Thank for your feed back

@TheMapleSight Жыл бұрын

Sorry to bother you again. After I run a pipeline I get error: ErrorCode=JreNotFound,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Java Runtime Environment cannot be found on the Self-hosted Integration Runtime machine. It is required for parsing or writing to Parquet/ORC files. Make sure Java Runtime Environment has been installed on the Self-hosted Integration Runtime what should I do in this situation?

@mr.ktalkstech Жыл бұрын

This happens when the Java runtime is not installed in the machine. (happens only when you copy the data in parquet/orc format, if you try to copy in CSV or others it would work) If you want to use the parquet format, then try to install the java runtime using the below link to your local machine and then give it a try after restarting your system. www.java.com/en/download/manual.jsp

@TheMapleSight Жыл бұрын

@@mr.ktalkstech thank you very much, it worked. I can't wait to get to another episodes!

@anshulbhardwaj909 10 ай бұрын

I am stuck on 15:22 minute where the test connection fails, with code saying “Lofing failed for user ‘user2’. SqlErrorNumber=18456, Class=14, State=1 I tried to use chat got to look for some solutions but I can hardly understand anything (I am using SQL Server 2017) Please help

@anshulbhardwaj909 10 ай бұрын

I have checked and the the details provided are all correct but can’t understand the error solution

@logsofzany 4 ай бұрын

Did you fix it?

@prabhatgupta6415 Жыл бұрын

HI SIR CAN U BRING MORE VIDEOS ON PROJECT

@mr.ktalkstech Жыл бұрын

Sure, ll do :)

@drizzle1866 Жыл бұрын

Hello, how do I make the connection as I am using windows authentication. I have been stuck here. Thanks

@mr.ktalkstech Жыл бұрын

Hope you sorted this out as you are into other parts now?

@drizzle1866 Жыл бұрын

@@mr.ktalkstech Yes, thanks. I used Windows authentication option alonside my windows password

@logsofzany 4 ай бұрын

@@drizzle1866 How did you do it? what do you mean? did you change the sql script password to you windows password?

@ravitejapuli Жыл бұрын

I use Mac OS, this works there ?

@entertainmentandcricket4294 Жыл бұрын

i'm sorry to say this, but mac is not integrated with azure services

@ravitejapuli Жыл бұрын

@@entertainmentandcricket4294 thanks for the reply and clarification

@MultiKalleman 4 ай бұрын

Not able to connect to linked service SQL on prem. Tried everything both SQL and Microsoft authentication. The user was created as you did in part 2 - mrk with db_reader access and created key vault secrets for username and password. I also gave ADF the right to use these secrets. I passed in the Databasename and Servername. Not able to connect throught SSMS also with the user mrk and the password created.

@MultiKalleman 4 ай бұрын

i solved this issue, by using microsoft authentication. I opened my commandprompt and wrote 'whoami' this gave me the user name for my current local user. I also wrote the password i use to login to my pc and that worked

@TambeAjinkya 4 ай бұрын

@@MultiKalleman Can you tell the steps you performed precisely?

@MultiKalleman 4 ай бұрын

@@TambeAjinkya When creating the linked service in data factory I used Microsoft authentication. To find the username I opened my cmd (search for this in windows search bar) and wrote whoami. For the password I used my login to my pc user

@arihantjain2432 Ай бұрын

Check Server Authentication mode on your SQL Server instance and change it to SQL Server and Windows Authentication mode.

@venkatashasidharreddychali7485 Жыл бұрын

Hello bro, Thank u for the amazing content. When i try to create access policy. I have this information displayed on the screen,"Access policies not available. The access configuration for this key vault is set to role-based access control. To add or manage your access policies, go to the Access control (IAM) page." Please help me on this bro. Thanks in advance

@mr.ktalkstech Жыл бұрын

Did you create a Key Vault from scratch or using an existing one? I guess the key vault is configured as azure role based control. Please go the Key Vault -- Access configuration (under settings) -- Permission model, and see if the vault access policy is enabled or not, I guess for you "azure role based" should be selected. If that's the case, then please try to switch it to vault access policy- then it should work. Otherwise, I think you can still use the role based control, instead of giving the access policy (secret access) to the ADF- just give the same "Key Vault Admin" access to the ADF managed identity using IAM.

@venkatashasidharreddychali7485 Жыл бұрын

@@mr.ktalkstech Thank u bro, It worked. Than u so much.🙏

@venkatashasidharreddychali7485 Жыл бұрын

Hello bro, after changing from Azure RBAC to Vault based, I have lost the secrets. I am clueless now. Please help me out bro.Thank u

@mr.ktalkstech Жыл бұрын

Seems weird, can you create the secret again? or use the role based approach as I mentioned above, switch back from vault based to role based and after that using IAM assign the "Key Vault Admin" access to the ADF managed identity

@venkatashasidharreddychali7485 Жыл бұрын

@@mr.ktalkstech I did bro, It worked.

@sachindubey4315 9 ай бұрын

I M GETTING THIS ERROR DURING CREATION OF LINK SERVICE DO ANYBODY HAVE IDEA OF THIS "Input Payload is invalid, validation result - '["Connector or activity name: SqlServer_LINK_S, connector or activity type: SqlServer, error: Calling partner RP EvaluatePolicyAsync returned an invalid status code 'Conflict', ReasonPhrase Conflict"]' "

@mevijay1610 7 ай бұрын

Hi I got below error for parquet. It was successful for csv. Please give solution. ErrorCode=JreNotFound,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Java Runtime Environment cannot be found on the Self-hosted Integration Runtime machine. It is required for parsing or writing to Parquet/ORC files. Make sure Java Runtime Environment has been installed on the Self-hosted Integration Runtime machine.,Source=Microsoft.DataTransfer.Common,''Type=System.DllNotFoundException,Message=Unable to load DLL 'jvm.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E),Source=Microsoft.DataTransfer.Richfile.HiveOrcBridge,'

@arihantjain2432 Ай бұрын

Did you find any solution?

@ankitchourase 18 күн бұрын

Add jre path to environment variable

@saikalyangonuguntla594 9 ай бұрын

SqlErrorNumber=229,Class=14,State=5 :i got this error when loading LT.address under pipline-source ,resolve this by executing query in local sql server USE [master] EXEC master..sp_addsrvrolemember @loginame = N'sai', @rolename = N'sysadmin' GO :in login name (sai) give your usename , it is for updating user role to system admin