CODEWITHSUNDEEP
sqlapache-sparkhiveazure-databricks
xavier
xavier

Reputation: 177

Change data capture with databricks delta using "INSERT OVERWRITE"

Instead of deleting and selecting the data that I want to load into my Delta table, I would like to use INSERT OVERWRITE for performance purposes. I have a couple of queries e.g.

query1 = "DELETE FROM asl_process.otr WHERE substring(Requested_delivery_d,1,6) in ('{}' , '{}' , '{}' , '{}')".format(month_M1, month, month_P1, month_P2) ;

query2 = "DELETE FROM asl_process.otr WHERE substring(Requested_delivery_d,1,6) in (select distinct substring(Requested_delivery_d,1,6) from df_otrcurr)

that I want to summarise.

I tried to rewrite the 2 queries in a sql statement like:

%sql

INSERT OVERWRITE TABLE asl_process.otr
    PARTITION(Ord_Planned_GI_date)
    SELECT * FROM asl_process.otr 
    WHERE substring(`Requested_delivery_d`,1,6) < > ('month_M1', 'month', 'month_P1', 'month_P2')
          AND substring(`Requested_delivery_d`,1,6) < > (select distinct substring(`Requested_delivery_d`,1,6) from df_otrcurr)

The problem is that I get the following error: "SyntaxError: invalid syntax" at INSERT OVERWRITE.

What am I doing wrong? It doesn't like that I have the schema before the name of the table?..

Upvotes: 1

Views: 664

Answers (1)

KarthiKeyan Siva Baskaran
KarthiKeyan Siva Baskaran

Reputation: 173

You have mentioned 2 FROM clause in your Insert Overwrite query

Upvotes: 0

Related Questions