Overwriting schema in Delta Live Tables

[BWong]

Hi all

I have a table created by DLT. Initially I specified cloudFiles.inferColumnTypes to false and all columns are stored as strings. However, I now want to use cloudFiles.inferColumnTypes=true. I dropped the table and re-ran the pipeline, which failed with the error that some columns have incompatible types

How can I overwrite the inferred schema of this table? Or remove it all together?

Regards,

Billy

[Anonymous]

@Billy Wong​ :

You can overwrite the inferred schema of the table by specifying the schema explicitly when you create the table. You can do this by setting the cloudFiles.schema parameter to the desired schema in the Databricks Delta Lake table properties. To do this, you can follow these steps:

1. Drop the existing table using the DROP TABLE command in Databricks.
2. Rerun the pipeline with cloudFiles.inferColumnTypes set to true to infer the schema.
3. Use the DESCRIBE command to retrieve the schema of the table. You can use this schema to create a new table with the same name and overwrite the schema with the desired schema.
4. Create a new table with the same name using the CREATE TABLE command and specifying the cloudFiles.schema parameter with the desired schema. Here's an example:

CREATE TABLE my_table
USING DELTA
LOCATION 'path/to/table'
OPTIONS ('cloudFiles.schema' 'id INT, name STRING, age INT')

In this example, the cloudFiles.schema parameter is set to a string representation of the desired schema, which includes column names and data types.

Note that this will overwrite the existing schema and any data in the table will be read according to the new schema. If you want to remove the table altogether, you can use the DROP TABLE command again.

[Anonymous]

Hi @Billy Wong​ 

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!