DataBricks Issue - "The specified schema does not match the existing schema"

-


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

Comments

Post a Comment

Popular posts from this blog

SQL QUERY NIGHTMARE

-
Image
Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one! One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same. I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:- I must hand it to our dba to provide such a detailed response.  So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Be...
Read more

Handling decimal and non numeric types using Case statement

-
Image
Hi Guys Just encountered a scenario where I needed to handle numeric and non numeric types using Case statement. Here is the synopsis of challenge and solution:- Challenge :- You have a varchar column in your input table having values such as 1.1111, 34, John. CREATE TABLE #TEST ([UpdatedValue] VARCHAR(100)) INSERT INTO  #TEST VALUES('1.1111'),('34'),('John') select * from #TEST Requirement :- You need to round off the decimal values to 2 places  Solution :- I got to know while implementing that for a case statement to work the output data i.e. the resultant data from each case should be of same type. It won't be able to treat numeric and non numerics together. That is why I personally found it a bit tricky but on brain storming solution looked to be very simple. The basic idea is to use different select queries and then union together. Here it is :- select CASE WHEN CHARINDEX('.',[UpdatedValue],0) >=1 THEN CAST( C...
Read more

Be Aware of CARTESIAN PRODUCT When Using Join Keys in SQL

-
Image
I got a massive satisfaction last week when I was able to bring down the execution time of a sql stored procedure from > 3 hours to mere 2 minutes. It all boiled down to a missing joining key that led to a cartesian product within the tables. Here is how the basic structure of the query (there were plenty of joining columns, however for our convenience lets consider 2 columns only) looked like Select * from                      table1 t1 left join table2 t2 on t1.k1 =t2,k1 and t1.k2 = t2.k2                      left join table3 t3 on t1.k1 = t3.k1 and t1.k2 = t2.k2                      left join table4 t4 on t1.k1 = t4.k1 And this query ran forever when given a particular set of parameters. I started with the approach of joining the parent table with each subsequent table until I met the road block. For examp...
Read more