amazon web services - Create AWS Athena view programmatically

Question

Can you create views in Amazon Athena? outlines how to create a view using the User Interface.

I'd like to create an AWS Athena View programatically, ideally using Terraform (which calls CloudFormation).

I followed the steps outlined here: https://ujjwalbhardwaj.me/post/create-virtual-views-with-aws-glue-and-query-them-using-athena, however I run into an issue with this in that the view goes stale quickly.

...._view' is stale; it must be re-created.

The terraform code looks like this:

resource "aws_glue_catalog_table" "adobe_session_view" {

  database_name = "${var.database_name}"
  name = "session_view"

  table_type = "VIRTUAL_VIEW"
  view_original_text = "/* Presto View: ${base64encode(data.template_file.query_file.rendered)} */"
  view_expanded_text = "/* Presto View */"

  parameters = {
    presto_view = "true"
    comment = "Presto View"
  }

  storage_descriptor {
    ser_de_info {
      name = "ParquetHiveSerDe"
      serialization_library = "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe"
    }

    columns { name = "first_column" type = "string" }
    columns { name = "second_column" type = "int" }
    ...
    columns { name = "nth_column" type = "string" }
}

An alternative I'd be happy to use is the AWS CLI, however aws athena [option] provides no option for this.

I've tried:

• create-named-query which I have not been able to get working for a statement such as CREATE OR REPLACE VIEW as this doesn't seem to be the intended use case for this command.
• start-query-execution which asks for an output location, which suggests that this is meant for querying the data and outputting the results, as opposed to making stateful changes/creations. It also seems to be paired with stop-query-execution.

Answer 1

Creating views programmatically in Athena is not documented, and unsupported, but possible. What happens behind the scenes when you create a view using StartQueryExecution is that Athena lets Presto create the view and then extracts Presto's internal representation and puts it in the Glue catalog.

The staleness problem usually comes from the columns in the Presto metadata and the Glue metadata being out of sync. An Athena view really contains three descriptions of the view: the view SQL, the columns and their types in Glue format, and the columns and types in Presto format. If either of these get out of sync you will get the "… is stale; it must be re-created." error.

These are the requirements on a Glue table to work as an Athena view:

• TableType must be VIRTUAL_VIEW
• Parameters must contain presto_view: true
• TableInput.ViewOriginalText must contain an encoded Presto view (see below)
• StorageDescriptor.SerdeInfo must be an empty map
• StorageDescriptor.Columns must contain all the columns that the view defines, with their types

The tricky part is the encoded Presto view. That structure is created by this code: https://github.com/prestosql/presto/blob/27a1b0e304be841055b461e2c00490dae4e30a4e/presto-hive/src/main/java/io/prestosql/plugin/hive/HiveUtil.java#L597-L600, and this is more or less what it does:

• Adds a prefix /* Presto View: (with a space after :)
• Adds a base 64 encoded JSON string that contains the view SQL, the columns and their types, and some catalog metadata (see below)
• Adds a suffix */ (with a space before *)

The JSON that describes the view looks like this:

• A catalog property that must have the value awsdatacatalog.
• A schema property that must be the name of the database where the view is created (i.e. it must match the DatabaseName property of the surrounding Glue structure.
• A list of columns, each with a name and type
• A originalSql property with the actual view SQL (not including CREATE VIEW …, it should start with SELECT … or WITH …)

Here's an example:

{
  "catalog": "awsdatacatalog",
  "schema": "some_database",
  "columns": [
    {"name": "col1", "type": "varchar"},
    {"name": "col2", "type": "bigint"}
  ],
  "originalSql": "SELECT col1, col2 FROM some_other_table"
}

One caveat here is that the types of the columns are almost, but not quite, the same as the names in Glue. If Athena/Glue would have string the value in this JSON must be varchar. If the Athena/Glue uses array<string> the value in this JSON must be array(varchar), and struct<foo:int> becomes row(foo int).

This is pretty messy, and putting it all together requires some fiddling and testing. The easiest way to get it working is to create a few views and decoding working the instructions above backwards to see how they look, and then try doing it yourself.