AnalysisException: Operation not allowed: `CREATE TABLE LIKE` is not supported for Delta tables;

user_9858033 0
Viewed 825
create table if not exists map_table like position_map_view;

While using this it is giving me operation not allowed error

databricks-sql azure-databricks databricks
2 Answers

As pointed in documentation, you need to use CREATE TABLE AS, just use LIMIT 0 in SELECT:

create table map_table as select * from position_map_view limit 0;
Alex_Ott_186270

I didn't find an easy way of getting CREATE TABLE LIKE to work, but I've got a workaround. On DBR in Databricks you should be able to use SHALLOW CLONE to do something similar:

%sql
CREATE OR REPLACE TABLE $new_database.$new_table 
SHALLOW CLONE $original_database.$original_table`;

You'll need to replace $templates manually. Notes:

  1. This has an added side-effect of preserving the table content in case you need it.
  2. Ironically, creating empty table is much harder and involves manipulating show create table statement with custom code
user_3627210
Related