Drop and re-create table Using notebook, While loading objects doesn't exist while insert & fail

[Dhanapal]

We have a notebook, which has script as below (refer the screen shot)

Drop table from Lakehouse and Create table same in takehouse. When we try to load data, its getting some weird error .. saying "Table is not found" or 

'Error name - AnalysisException, Error value - [TABLE_OR_VIEW_NOT_FOUND] The table or view `lh_sales`.`dim_cots` cannot be found.

To Resolve notebook issue, We have added some pyspark script to refresh meta_data, but still failing.. If we re-run the failure step & its getting executed successfully. Some of my friends says, its intermittent failure.. Any alternative or perment fix to resolve this type of issues ?

[Anonymous · Accepted solution]

Hi @Dhanapal ,

I understand your need now, here's my suggestion.

Like mentioned in the case below, you can explicitly refresh the table metadata using the spark.catalog.refreshTable("table_name") command.

apache spark - How to refresh a table and do it concurrently? - Stack Overflow

I ran some tests and deleted a table named salesorders.

The error occurs when I create the form with the same name again.

After using the following command, the table with the same name is created successfully.

# Define the table name
table_name = "salesorders"

# Drop and create table with actual schema
spark.sql(f"""
    DROP TABLE IF EXISTS {table_name}
""")

spark.sql(f"""
    CREATE TABLE {table_name} (
        SalesOrderID INT,
        OrderDate DATE,
        CustomerID INT,
        Item STRING,
        Quantity INT,
        UnitPrice DOUBLE,
        Tax DOUBLE,
        TotalAmount DOUBLE
    )
""")

Please note that when you use it, change the table names, column names, and data types to your own.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!