以编程方式创建 AWS Athena 视图 [英] Create AWS Athena view programmatically

问题描述

您可以在 Amazon Athena 中创建视图吗? 概述了如何使用用户界面创建视图.

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

我想以编程方式创建一个 AWS Athena 视图，最好使用 Terraform(调用 CloudFormation).

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

我按照此处列出的步骤操作:https://ujjwalbhardwaj.me/post/create-virtual-views-with-aws-glue-and-query-them-using-athena，但是我遇到了一个问题，因为视图很快就会过时.

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' 是陈旧的；它必须重新创建.

terraform 代码如下所示:

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" }
}

我很乐意使用的另一种选择是 AWS CLI，但是 aws athena [option] 没有为此提供任何选项.

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

我试过了:

• create-named-query 我无法为诸如 CREATE OR REPLACE VIEW 之类的语句工作，因为这似乎不是此命令的预期用例.
• 开始查询执行 要求输出位置，这表明这是用于查询数据和输出结果，而不是进行有状态的更改/创建.它似乎也与 stop-query- 配对执行.

• 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.

推荐答案

正如您所建议的，绝对可以使用 start-query-execution 通过 AWS CLI 以编程方式创建 Athena 视图.正如您所指出的，这确实需要您为结果提供 S3 位置，即使您不需要检查文件(Athena 会出于某种原因在该位置放置一个空的 txt 文件).

As you suggested, it is definitely possible to create an Athena view programmatically via the AWS CLI using the start-query-execution. As you pointed out, this does require you to provide an S3 location for the results even though you won't need to check the file (Athena will put an empty txt file in the location for some reason).

这是一个例子:

$ aws athena start-query-execution --query-string "create view my_view as select * from my_table" --result-configuration "OutputLocation=s3://my-bucket/tmp" --query-execution-context "Database=my_database"

{
    "QueryExecutionId": "1744ed2b-e111-4a91-80ea-bcb1eb1c9c25"
}

您可以通过创建一个工作组并在那里设置位置.

您可以使用 get-query-execution 命令检查视图创建是否成功.

You can check whether your view creation was successful by using the get-query-execution command.

$ aws --region athena get-query-execution --query-execution-id bedf3eba-55b0-42de-9a7f-7c0ba71c6d9b
{
    "QueryExecution": {
        "QueryExecutionId": "1744ed2b-e111-4a91-80ea-bcb1eb1c9c25",
        "Query": "create view my_view as select * from my_table",
        "StatementType": "DDL",
        "ResultConfiguration": {
            "OutputLocation": "s3://my-bucket/tmp/1744ed2b-e111-4a91-80ea-bcb1eb1c9c25.txt"
        },
        "Status": {
            "State": "SUCCEEDED",
            "SubmissionDateTime": 1558744806.679,
            "CompletionDateTime": 1558744807.312
        },
        "Statistics": {
            "EngineExecutionTimeInMillis": 548,
            "DataScannedInBytes": 0
        },
        "WorkGroup": "primary"
    }
}

这篇关于以编程方式创建 AWS Athena 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！