Skip to content

athena.md

Latest commit

 

History

History
73 lines (48 loc) · 8.49 KB

athena.md

File metadata and controls

73 lines (48 loc) · 8.49 KB

Athena

Installation

pip install "sqlmesh[athena]"

Connection options

PyAthena connection options

SQLMesh leverages the PyAthena DBAPI driver to connect to Athena. Therefore, the connection options relate to the PyAthena connection options. Note that PyAthena uses boto3 under the hood so you can also use boto3 environment variables for configuration.

Option Description Type Required
type Engine type name - must be athena string Y
aws_access_key_id The access key for your AWS user string N
aws_secret_access_key The secret key for your AWS user string N
role_arn The ARN of a role to assume once authenticated string N
role_session_name The session name to use when assuming role_arn string N
region_name The AWS region to use string N
work_group The Athena workgroup to send queries to string N
s3_staging_dir The S3 location for Athena to write query results. Only required if not using work_group OR the configured work_group doesnt have a results location set string N
schema_name The default schema to place objects in if a schema isnt specified. Defaults to default string N
catalog_name The default catalog to place schemas in. Defaults to AwsDataCatalog string N

SQLMesh connection options

These options are specific to SQLMesh itself and are not passed to PyAthena

Option Description Type Required
s3_warehouse_location Set the base path in S3 where SQLMesh will instruct Athena to place table data. Only required if you arent specifying the location in the model itself. See S3 Locations below. string N

Model properties

The Athena adapter utilises the following model top-level properties:

Name Description Type Required
table_format Sets the table_type Athena uses when creating the table. Valid values are hive or iceberg. string N
storage_format Configures the file format to be used by the table_format. For Hive tables, this sets the STORED AS option. For Iceberg tables, this sets format property. string N

The Athena adapter recognises the following model physical_properties:

Name Description Type Default
s3_base_location s3:// base URI of where the snapshot tables for this model should be written. Overrides s3_warehouse_location if one is configured. string

S3 Locations

When creating tables, Athena needs to know where in S3 the table data is located. You cannot issue a CREATE TABLE statement without specifying a LOCATION for the table data.

In addition, unlike other engines such as Trino, Athena will not infer a table location if you set a schema location via CREATE SCHEMA <schema> LOCATION 's3://schema/location'.

Therefore, in order for SQLMesh to issue correct CREATE TABLE statements to Athena, you need to configure where the tables should be stored. There are two options for this:

  • Project-wide: set s3_warehouse_location in the connection config. SQLMesh will set the table LOCATION to be <s3_warehouse_location>/<schema_name>/<snapshot_table_name> when it creates a snapshot of your model.
  • Per-model: set s3_base_location in the model physical_properties. SQLMesh will set the table LOCATION to be <s3_base_location>/<snapshot_table_name> every time it creates a snapshot of your model. This takes precedence over any s3_warehouse_location set in the connection config.

Limitations

Athena was initially designed to read data stored in S3 and to do so without changing that data. This means that it does not have good support for mutating tables. In particular, it will not delete data from Hive tables.

Consequently, forward only changes that mutate the schemas of existing tables have a high chance of failure because Athena supports very limited schema modifications on Hive tables.

However, Athena does support Apache Iceberg tables which allow a full range of operations. These can be used for more complex model types such as INCREMENTAL_BY_UNIQUE_KEY and SCD_TYPE_2.

To use an Iceberg table for a model, set table_format iceberg in the model properties.

In general, Iceberg tables offer the most flexibility and you'll run into the least SQLMesh limitations when using them. However, we create Hive tables by default because Athena creates Hive tables by default, so Iceberg tables are opt-in rather than opt-out.