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?

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

3

@Pedro_Enrique, apologies for the late reply.

I have tried to duplicate your workflow but could not reproduce any of the errors. What version of Dremio are you currently using and does it show these errors?

One approach you might try, is to be more explicit about the filesystem source you are adding the CTAS tables to. So instead of using $scratch, add a NAS or HDFS source and CTAS into that. Then you will easily be able to navigate the results of your CTAS in Dremio and see what files or directories you can been promoted to a PDS (and thus add/remove as “tables” in the system catalog).

You can enable CTAS exports on most file system sources:

ctas-option
ctas-option.png934×356 21.5 KB

For more information, see our docs on this: https://docs.dremio.com/sql-reference/sql-commands/tables.html#filesystem-sources