CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-athena
Hayat Bellafkih
Hayat Bellafkih

Reputation: 599

aws - How create request when partitions exist

I create a table whith many partitions :

PARTITIONED BY (`year` string, `day` string, `month` string, `version_` string, `af_` string, `type_` string, `msm` string)

after that, I run :

MSCK REPAIR TABLE mytable;

When I launch the preview of mytable, I had 0 rows. I try:

select * from mytable

Also no results.

One solution is to use alter table to add partitions with values, but should I create alter table befory every request ?!

Upvotes: 0

Views: 56

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270184

The cause is that your PARTITIONED BY statement has fields in a different order that your directory hierarchy:

PARTITIONED BY (`year` string, `day` string, `month` string, `version_` string, `af_` string, `type_` string, `msm` string)

af_=4/type_=anchor/msm=1026355/year=2017/month=05/day=14/version_=1

You can fix it by listing the fields in PARTITIONED BY in the same order as the directory hierarchy.

I did a small test where I had a partition working, but then recreated the table with a different order and it returned zero rows. (It also created new directories in the expected hierarchy -- weird!)

Upvotes: 1

Related Questions