Athena Create Table

[rhboyd]

• I'm submitting a ...

  • 🪲 bug report
  • 🚀 feature request
  • 📚 construct library gap
  • ☎️ security issue or vulnerability => Please see policy
  • ❓ support request => Please see note at the top of this template.

• What is the current behavior?
  Athena Cfn and SDKs don't expose a friendly way to create tables

• What is the expected behavior (or behavior of feature suggested)?
  I'd propose a construct that takes

• bucket name

• path

• columns: list of tuples (name, type)

• data format (probably best as an enum)

• partitions (subset of columns)

Then uses the AWS SDK Custom Resource on the Athena SDK to execute

querystring = """
  CREATE EXTERNAL TABLE IF NOT EXISTS mydb.table_name (
    `columns[0][0]` columns[0][1],
    `columns[1][0]` columns[1][1]
    etc...
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
  WITH SERDEPROPERTIES (
    {data_format}
  ) LOCATION 's3://{bucket_name}/{path}'
  TBLPROPERTIES ('has_encrypted_data'='false');
"""
response = client.start_query_execution(
    QueryString=querystring,
    ClientRequestToken='string',
    QueryExecutionContext={
        'Database': 'string'
    },
    ResultConfiguration={
        'OutputLocation': 'string',
        'EncryptionConfiguration': {
            'EncryptionOption': 'SSE_S3'|'SSE_KMS'|'CSE_KMS',
            'KmsKey': 'string'
        }
    },
    WorkGroup='string'
)

• What is the motivation / use case for changing the behavior or adding this feature?
  Athena is the goddess of wisdom and civilization, how can we be a civilized developer tool if we don't support her?

• Please tell us about your environment:
  All of them

• Other information
  <3

[rhboyd]

Can also work for creating a database in Athena as well

[beuleal]

Any updates?

[jonny-rimek]

I'd love to see this as well, but it's a CFN limitation not CDK

[rhboyd]

This can definitely be addressed by CDK, I just don’t have the time to implement it. I’d be glad to give someone some pointers for how to get started if they wanted to pick it up.

[PikeDev]

This would definitely be an interesting implementation. @rhboyd how would the implementation deal with the user changing the table definition in code and then updating the stack? The IF NOT EXISTS part means the table will not be recreated. There is also no REPLACE mechanism that I am aware of. In a project I'm currently doing we just run DROP before the CREATE. Would that suffice here also or is it too destructive?