Forum Discussion
Advocate IV"ALTER TABLE RENAME TO" does not work with tables in upper casee
Hi KimTutein ,
I can reproduce your error, running the command will, despite successfully renaming the table name to another uppercase table name, also create an unidentified folder, and when querying the data it will report an error saying that the current table doesn't exist.
This issue may be related to Delta Lake's behavior when dealing with uppercase table names. Specifically, the way Delta Lake handles table names can lead to inconsistent metadata in some cases, especially when the spark.sql.caseSensitive property is set to true.
To rename an uppercase table name to another uppercase table name, you can manually copy the data from the old table to the new table, make sure the data is written correctly in the new table, and then delete the old table. This method avoids the problems that can result from using the ALTER TABLE RENAME TO command.
The steps are as follows:
First, create a table named in uppercase letters and insert data.
Next, use the following command to rename and delete the original table.
# read original table df = spark.sql(“SELECT * FROM A_TEST”) # create new table df.write.format(“delta”).mode(“overwrite”).saveAsTable(“A_TEST_RENAME_1”) # drop table spark.sql(“DROP TABLE IF EXISTS A_TEST”)As you can see, it runs fine. Finally, perform a query on the data.
%%sql SELECT * FROM A_TEST_RENAME_1If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf 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!
Hi KimTutein ,
I can reproduce your error, running the command will, despite successfully renaming the table name to another uppercase table name, also create an unidentified folder, and when querying the data it will report an error saying that the current table doesn't exist.
This issue may be related to Delta Lake's behavior when dealing with uppercase table names. Specifically, the way Delta Lake handles table names can lead to inconsistent metadata in some cases, especially when the spark.sql.caseSensitive property is set to true.
To rename an uppercase table name to another uppercase table name, you can manually copy the data from the old table to the new table, make sure the data is written correctly in the new table, and then delete the old table. This method avoids the problems that can result from using the ALTER TABLE RENAME TO command.
The steps are as follows:
First, create a table named in uppercase letters and insert data.
Next, use the following command to rename and delete the original table.
# read original table
df = spark.sql(“SELECT * FROM A_TEST”)
# create new table
df.write.format(“delta”).mode(“overwrite”).saveAsTable(“A_TEST_RENAME_1”)
# drop table
spark.sql(“DROP TABLE IF EXISTS A_TEST”)
As you can see, it runs fine. Finally, perform a query on the data.
%%sql
SELECT * FROM A_TEST_RENAME_1
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!
KimTuteinHi Anonymous
Thank you for you reply - it is much appreciated.
The reason I wanted to make the renaming is that I need to be able to change metadata on field in table (I thought I would rename original table as a backup and then make new table with old name). We have tables defined using spark.sql which for instance varchar(100) the core system some times add length to the filds and we then needed to add the lenght to the field. As pyspark only define using for instance string type we decided to make new table with new definition. Read that table into spark dataframe and overwrite existing table with schema overwrite option = true. I then get a new table with new metadata (for instance varchar 150) and preserve time-travel on the table. My understanding is that having the correct metadata limitation on field will help the system use optimal execution plan when queriyng data via. the sql end-point.
Friendly regards
Kim