iLearn

  So got this error today and at first glance, I thought I inadvertently cancelled the job. Tried running it twice and the job got cancelled again.  Full marks to the DevOps developers for diagnosing and proposing the solution.  Changing the agent job machine to windows-latest worked for me

  Super quick solution if you ever encountered/stuck with this scenario in Azure Data Factory  Let's say there is an if condition activity and you need to evaluate the output value from a previous activity (could be metadata or could be inside for each loop ).  The values to be evaluated against are more than two in number.  The programming equivalent of it would           when variable1 not in (Value 1, Value 2, Value 3, Value 4) If you have some experience working in the azure data factory, you would like to use the following expression  @and(not(contains(item().name, 'Value1')), not(contains(item().name, 'Value2')), not(contains(item().name, 'Value3')), not(contains(item().name, 'Value4')), not(contains(item().name, 'Value5')), not(contains(item().name, 'Value6')) ) However, this will not work out and you will get an error message.  Instead, try using a variable. Assign the value to the variable before evaluating and then us...

  Man! The past couple of weeks has been really tough. Hardcore development on Azure Data Factory, and Azure Databricks as we are up against a tight deadline (again  :-) ). Loads of different scenarios and loads of new learnings. Sharing one below, keep reading. We are receiving a source file (let's call it Test.csv) which has a blank row before the header row  1 2 "colname1", "colname2" 3 "value1","value2" we are using  spark.read.format to load this into a data frame.  Looking at the file contents, one would assume that you need to somehow skip the first blank row.  So I began researching it. Found that spark.read.format does nt provide any such property.  After spending couple of hours with no major break through, I thought of testing the code as it is  val rawdataframe=  spark.read.format("csv").option("header","true").option("inferSchema","true").option("delimiter", s",...

  Hey guys, time to share something new that I have learned in the past few days.  I am going to share my thought process on how I viewed the problem statement and arrived at the solution.  So, let's get rolling, as part of the requirements, we were given an API endpoint and were told that we need to ingest data and show it on the power bi report.  I began brainstorming on how do we pull data from API either to ADLS storage in the form of CSV or txt files or to Azure SQL DB in the form of a managed table.  Disclaimer -  I am new to extracting information from API endpoints using Azure Data Factory, so had to read quite a few blogs. I discovered that we could either use Rest API connectors or HTTP connectors to talk to the API endpoint and pull out the information needed.  So I tried using both of the above connectors and found that it is becoming challenging to extract the information in the CSV file. Although I did get some success in fetching only th...

Continuing our series on SQL Query Questions Asked During Interview. This is part 2.  You can find part 1  here You might not encounter the following scenario in real-time projects, however, it would help you exercise your SQL skills and might help if you are preparing for an Interview.  Please refer to the picture below. On the left side, you have a table with a single column called teamname which consists of various acronyms of countries situated in Asia that play cricket. And on the right side, is the expected output. Essentially, we need to come up with the fixtures of teams playing each other and no two teams playing twice .  Common give it a try For answer and explanation, refer to the video below 

 If you are someone who likes to brian storm on SQL query solutions, you would like this post.  Some time back, one of my friend reached out to me to get some help on a SQL query question that was asked to him during an interview with Microsoft (if you know what I mean :-) ). Keep reading. There are 2 tables, one Accounts and the other Transactions with entries as shown below.  Here D is for Deposit and W for Withdrawal Expected Output The accounts table reflect the balance before these transactions happened. So, essentially we have to generate an account statement that would look something like the above. At first glance, it looks pretty straightforward, especially the Credit and Debit part. However, the real challenge is that you need to calculate account balance as a running calculation.  Pseudo-Code  You first have to calculate the balance from the first transaction. Then just add or subtract the respective transaction amounts based on if it's a withdrawal o...

I was trying to sync our team's database project and was getting the below error git: 'credential-manager-core' is not a git command could not read Username for 'https://github.com': terminal prompts disabled One thing to call out here is that I had not synced the changes in the database project for over a month and there were no issues before.  My guess is that somehow the OWA authentication got wiped out from my system and this might be the reason for this issue.  In terms of the Solution I tried searching online on  terminal prompts disabled and got a plethora of solutions mostly suggesting some git commands. I tried a few of the suggestions and didn't get any luck. This morning I started searching for  git: 'credential-manager-core' is not a git command   and came across the following issue reported on Microsoft forums  Warning/error message when running git fetch within Visual Studio 2019 It basically suggests downloading/updating the latest versi...

Hi everyone, welcome to another blog of iLearn.  First of all, it always gives a different "high" when you tackle a performance-related issue. This particular scenario was that the query in our quality assurance environment was running quicker than the query in production.  Here is how we started the investigation and arrived at the solution. First of all, started looking at the statistics.  1. The First check was to see if the auto stats property is set to active or not and it was ON both in Production and QA databases SELECT name,is_auto_create_stats_on FROM sys.databases WHERE name = 'OurDatabase';   2. The Second check was to update the stats on the table (in Production) being queried (since we found that the stats were not up to date) update STATISTICS OurTable; Didn't do any good, the query was still running slow 3. Finally decided to compare the configuration for Production and QA Database instances and found that the QA is set to 8 vCores and Production ...

Hey guys, wanted to talk about delta lake in data bricks and the ADLS ecosystem. I have found an issue while changing the schema of delta tables.  Problem Statement - Let's say you have a table Table1 with columns column1 and column2 as a delta table and the requirement is to get rid of column2 from Table1, something like below Using T SQL we could easily achieve this by using ALTER Table drop column ...command However, in the spark SQL and Databricks realm, I couldn't find any such feature. There are options to re-order, add columns but not drop as per  https://docs.databricks.com/delta/delta-batch.html Solution - Seems like the only possible solution is to drop the folder/delta table in the ADLS You would have the table/folder recreated after running the above spark SQL query (screenshot) Does anyone have a proper solution to this? This looks like a legit limitation as changing schemas is very common in real-time projects

  Prerequisites  Azure SQL Server with a source and target database instance GitHub repo Visual code for SQL Server Database Objects Familiarity about dacpac Azure DevOps Account Things I didn't check  Since I was an admin on azure, DevOps, I am not sure on the permissions required. Ideally contrubutor access should be enough to implement this solution.  Problem Statement and Solution We are using dacpac for deploying our SQL artifacts from source to target server/database. The SQL server code, as well as the dacpac, is checked inside the GitHub repo. We need to develop an azure DevOps pipeline that uses the dacpac and does the deployment.  I have found if you change the dacpac location to a custom folder and sync it to the Github repo, we could achieve what we had set out to.  Logical Steps 1. Create a SSDT project  2. Link SSDT to github repo with a local repo 3. Create a folder for dacpac in local repo 4. Add settings in visual studio to point dacpa...

  Problem Statement - We had a power shell script as part of post-deployment step in one of the release pipelines and it was erroring out with the following message "Unable to initialize device PRN" Solution - There was a print statement inside the script, replaced it with echo. Example print "Thanks for reading this post"  to  echo "You are awesome"

Although I have been aware of GitHub for quite a while but got a chance to use it as a repository in the project for storing artifacts only over the past few months.  We use GitHub as the sole cloud repo for storing SQL, Databricks, and Azure Data Factory artifacts.  Got an opportunity to set up GitHub with ADF right from scratch and all the excitement to learn more about GitHub took a back seat when I stumbled upon the following issue Problem Statement - You are an admin on a repo in GitHub and even then get a permission error while linking the ADF to GitHub Solution - After scratching my hairs and going on a searching spree on google for some days, I was able to figure out what needed to be done, however was not aware of how to implement it. Finally thanks to Microsoft for getting on a call and helping me resolve this issue. What needed to be done - Need to authorize AzureDataFactory service so that Github can communicate with it.  How it should be done -  Basicall...