Search code examples
pysparkapache-spark-sqlazure-databricks

temp view and merge into statement

Is it supported to pass the temp view as the source into merge statement?

Im creating the temp view

df.createOrReplaceTempView("dim_user")

and then I would like to use it as the source in merge statement like this

query_1 = f"""
    MERGE INTO gold.d_hsseq_user_v2 AS tgt
    USING dim_user AS src
    ON tgt.userid = src.userid
    AND tgt.hashed_key_SCD1 <> src.sha2(concat_ws(',', {concatenated_scd1}))
    WHEN MATCHED THEN
    UPDATE SET {set_scd1},
               tgt.HSH_SCD1 = src.sha2(concat_ws(',', {concatenated_scd1}));
"""

But Im getting the error that Database 'src' not found.

it is running under one spark session, but I also tried to create global temp view and i got same error.

When I do run show views i see it is created enter image description here

Solution

  • enter image description here

    The above error occured due to the wrong usage of the sha2(). The sha2() is a not a table inbuilt function. In the above you have used it as <table_name>.sha2() and that's the reason for the error.

    You need to use sha2() function like below sample.

    query1 = "select sha2(concat_ws(',',array('user_id','name','gender')),256)"
spark.sql(query1).show()

    enter image description here