Search code examples
sqlamazon-web-servicesamazon-s3amazon-athena

Create table in Athena with auto load partitions

I've created the table like this:

CREATE EXTERNAL TABLE IF NOT EXISTS my_table (
  `id` string COMMENT '', 
  `source` string COMMENT '', 
  `os` string COMMENT '')
PARTITIONED BY ( 
  `date` string COMMENT '')
STORED AS PARQUET
LOCATION 's3://.....';

But I have to tap LOAD PARTITION every time working this table. Is it possible to auto load partition for this table?

Solution

  • You can use Partition Projection in Athena table which automatically handles partition.

    You have to use tblproperties while creating the table.

    Suppose , you have partitions from 2020-01-01 then extend this query in your create table statement.

    TBLPROPERTIES
 (
     "projection.enabled" = "true",
     "projection.date.type" = "date",
     "projection.date.format"="yyyy-DD-mm",
     "projection.date.range"="2010-01-01,NOW"
    
 )

    You can follow this link to know more--

    Partition Projection in Athena