Can you add more than one partition in one "ALTER TABLE" command?

Question

I'm using Amazon Athena to query through some log files stored in an S3 bucket, and am using partitions to section off days of the year for the files I need to query. I was wondering -- since I have a large batch of days to add to my table, could I do it all in one ALTER TABLE command, or do I need to have as many ALTER TABLE commands as the number of partitions I would like to create?

This is an example of the command I am using at the moment:

ALTER TABLE
  logfiles
ADD PARTITION
  (day='20170525')
location 's3://log-bucket/20170525/';

If I do have to use one ALTER TABLE command per partition, is there a way to create a range of days, and then have Athena loop through it to create the partitions, instead of me manually copy/pasting out this command 100+ times?

score 9 · Accepted Answer · edited Dec 26 '18 at 22:03

It appears that you can add many partitions to one ALTER TABLE command, per the Athena documentation found at https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.html or go do the athena root and search for add partition.

ALTER TABLE orders ADD
  PARTITION (dt = '2016-05-14', country = 'IN') LOCATION 's3://mystorage/path/to/INDIA_14_May_2016'
  PARTITION (dt = '2016-05-15', country = 'IN') LOCATION 's3://mystorage/path/to/INDIA_15_May_2016';

Can you add more than one partition in one "ALTER TABLE" command?

1 Answers1