2
create table if not exists map_table like position_map_view;

While using this it is giving me operation not allowed error

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
Saswat Ray
  • 141
  • 3
  • 14

2 Answers2

3

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
  • 80,552
  • 8
  • 87
  • 132
  • 1
    This does not preserve partitioning information – zbstof Feb 09 '22 at 14:02
  • you can specify `partitioned by` explicitly. I understand that it's not straightforward, but it's still possible to do things programmatically, for example combining `show create table` with some text manipulation – Alex Ott Feb 09 '22 at 18:09
0

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
zbstof
  • 1,052
  • 3
  • 14
  • 27