DROP TABLE with HASH PARTITION?

#1

I’m using the CTA a lot, and I like the idea of hash partitions, but I need to be able to recreate existing tables as well, and I’m unable to do that. Calling DROP TABLE nukes the entire table, I only want to nuke a specific partition. Say I have multiple partitions, in my case a partition is a customer_id. I want to recreate that table for a specific customer without touching the data for other customers.

CREATE TABLE $scratch.mysource.config HASH PARTITION BY (customer_id) AS SELECT * from mysource.config

This works, but I cannot recreate that table, nor “drop” it
I’m currently doing this instead, which behind the scenes seems to be the same

CREATE TABLE $scratch.mysource.config."1234567890" AS SELECT * from mysource.config WHERE customer_id = '1234567890'

But there are some side effects to doing it this way, and weird errors associate with it, I’ll open a different ticket for that.
How do I recreate scratch table with hash partitions the right way?

0 Likes

#2

Check this out

create table $scratch.pedro HASH PARTITION BY (customer_id) as  select * from mysource.config
(no error)
create table $scratch.pedro HASH PARTITION BY (customer_id) as  select * from mysource.config
ERROR: A table or view with given name ["$scratch".pedro] already exists.
select * from $scratch.pedro 
(26 results)
drop table $scratch.pedro."1234567890"
(no error)
create table $scratch.pedro HASH PARTITION BY (customer_id) as  select * from mysource.config
ERROR: A table or view with given name ["$scratch".pedro] already exists.
select * from from  $scratch.pedro
ERROR: DATA_READ ERROR: File does not exist ....
bunch of java stack trace, I wont paste it here since it's a lot
drop table $scratch.pedro;
ERROR: NullPointerException

I can’t create that table ever again

0 Likes