以编程方式创建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 View，最好使用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 OR REPLACE VIEW 这样的语句工作似乎不是该命令的预期用例。


• start-query-execution ，它要求一个输出位置，这表明这是用于查询数据和输出结果，而不是使状态变为有状态变化/创造。它似乎还与 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.

推荐答案

正如您所建议的，绝对有可能创建一个Athena使用 start-query-execution 通过AWS CLI以编程方式查看。正如您所指出的那样，这确实需要您为结果提供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"
}

您可以通过

You can avoid having the client specify a bucket by creating a workgroup and setting the location there.

您可以使用<$ c $来检查视图创建是否成功c> get-query-execution 命令。

$ 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屋！