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?