AWS Athena: use "folder" name as partition

Question

I have thousands of individual json files (corresponding to one Table row) stored in s3 with the following path: s3://my-bucket/<date>/dataXX.json

When I create my table in DDL, is it possible to have the data partitioned by the present in the S3 path ? (or at least add the value in a new column)

Thanks

Answer 1

It is possible to do this now using storage.location.template. This will partition by some part of your path. Be sure to NOT include the new column in the column list, as it will automatically be added. There are a lot of options you can search to tweak this for your date example. I used "id" to show the simplest version i could think of.

CREATE EXTERNAL TABLE `some_table`(
  `col1` bigint, 
PARTITIONED BY (
  `id` string
  )
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://path/bucket/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'projection.enabled'='true', 
  'projection.id.type' = 'injected',
  'storage.location.template'='s3://path/bucket/${id}/'
  )

official docs: https://docs.amazonaws.cn/en_us/athena/latest/ug/partition-projection-dynamic-id-partitioning.html

Answer 2

Its not necessary to do this manually. Setup a glue crawler and it will pick-up the folder( in the prefix) as a partition, if all the folders in the path has the same structure and all the data has the same schema design.

Put it will name the partition as partition0. You can go into edit-schema and change the name of this partition to date or whatever you like.

But make sure you go into your glue crawler and under "configuration options" select the option - "Add new columns only". Otherwise on the next glue-crawler run it will reset the partition name back to partition0.