CTAs - prefix in partition names

1

Hi there,

While I was trying CTAs with partitioning, I found that there were some prefixes added in partition name:
e.g.:

CREATE TABLE s3.mybucket.folder1.folder2 PARTITION BY (an_integer_field) AS 
SELECT * FROM my_table

would give partitions such as:

/mybucket/folder1/folder2/900_417/
/mybucket/folder1/folder2/901_354/
/mybucket/folder1/folder2/904_551/
/mybucket/folder1/folder2/917_259/
/mybucket/folder1/folder2/917_383/
/mybucket/folder1/folder2/91_734/
/mybucket/folder1/folder2/924_424/
/mybucket/folder1/folder2/925_427/
/mybucket/folder1/folder2/926_326/
/mybucket/folder1/folder2/926_577/
/mybucket/folder1/folder2/930_485/
/mybucket/folder1/folder2/938_328/
/mybucket/folder1/folder2/946_439/
/mybucket/folder1/folder2/955_329/
/mybucket/folder1/folder2/955_386/
/mybucket/folder1/folder2/956_486/
/mybucket/folder1/folder2/963_415/
/mybucket/folder1/folder2/969_303/
/mybucket/folder1/folder2/969_330/
/mybucket/folder1/folder2/971_551/
/mybucket/folder1/folder2/973_358/
/mybucket/folder1/folder2/975_328/
/mybucket/folder1/folder2/976_750/
/mybucket/folder1/folder2/978_435/
/mybucket/folder1/folder2/989_308/
/mybucket/folder1/folder2/992_436/
/mybucket/folder1/folder2/992_751/
/mybucket/folder1/folder2/997_399/
/mybucket/folder1/folder2/997_754/
/mybucket/folder1/folder2/9_47/
/mybucket/folder1/folder2/9_82/

I would expect only the integer field value but I always have a prefix such as 992_.

Is it intended ? Is there a way to remove it ?

And does PARTITION BY and HASH PARTITION BY do the same ?

Thanks,

Luc

2

@lucbaro default should be hash, I do see the numbers getting generated, will check to see if they can be removed

3

Hi @balaji.ramaswamy, is there any update on that topic ?

Thanks,

Luc

4

@lucbaro apologies, have not got a chance to check on this yet. Is it super important to have them removed?

5

It is not absolutely necessary as we can still filter on partition with a like in the query, but it is quite important for readability, it’s going to be a mess when we are dealing with several partitions.
I even wonder if it would be more efficient for Dremio to avoid the like usage and use the equal sign for the exact partition name match.