DynamoDBのデータをGlue, Athena, Lambda, QuickSight を使って分析の定期実行

CREATE EXTERNAL TABLE `useractivitylog`(
  `datetime` string,
  `role` string,
  `activity` string,
  `value` bigint,
  `userid` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://glue-etl-output/useractivitylog'
TBLPROPERTIES (
  'has_encrypted_data'='false')

WITH useractivity_daygroup AS (
 SELECT
  DISTINCT userid,
  activity,
  value,
  date_trunc('day', date_parse(datetime, '%Y-%m-%dT%H:%i:%s')) AS daygroup
  FROM "glue_output"."useractivitylog"
  WHERE activity = 'login'
)
SELECT
  count(userid) AS UU,
  daygroup
FROM
  useractivity_daygroup
GROUP BY
  daygroup
;

{
    "fileLocations": [
        {
            "URIPrefixes": [
                "s3://aws-athena-query-results-<id>-<region>/userActivityLogin/"
            ]
        }
    ],
    "globalUploadSettings": {
        "format": "CSV",
        "delimiter": ",",
        "textqualifier": "'",
        "containsHeader": "true"
    }
}

$ export AWS_DEFAULT_PROFILE=my_aws_profile

$ aws athena list-named-queries

aws athena list-named-queries
{
    "NamedQueryIds": [
        "123456786-414d-44a9-82cc-8e4f9781c8a0"
    ]
}

$ aws athena get-named-query --named-query-id "123456786-414d-44a9-82cc-8e4f9781c8a0"
{
    "NamedQuery": {
      (略)
    }
}

$ aws athena start-query-execution  \
  --query-string "(略)" \
  --result-configuration OutputLocation=s3://aws-athena-query-results-<id>-<region>/login_uu_day-prod/
 {
     "QueryExecutionId": "67b1abba-3acc-4148-b541-420dfa46dd58"
 }

$ aws athena get-query-results  --query-execution-id "67b1abba-3acc-4148-b541-420dfa46dd58"

import os
import time
import boto3

RETRY_COUNT = 300
DATABASE = 'glue-output' 
TABLE = 'useractivity'
PATH = 'login_uu_day/'
S3_BUCKET = 'aws-athena-query-results-<id>-<region>'
S3_OUTPUT = 's3://' + S3_BUCKET + '/' + PATH

QUERY = ("""
略(上記のクエリ)
""")

def lambda_handler(event, context):
    query = QUERY
    client = boto3.client('athena')
    response = client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': DATABASE
        },
        ResultConfiguration={
            'OutputLocation': S3_OUTPUT,
        }
    )
    query_execution_id = response['QueryExecutionId']
    print(query_execution_id)
    for i in range(1, 1 + RETRY_COUNT):
        query_status = client.get_query_execution(QueryExecutionId=query_execution_id)
        query_execution_status = query_status['QueryExecution']['Status']['State']
        if query_execution_status == 'SUCCEEDED':
            print("STATUS:" + query_execution_status)
            break
        if query_execution_status == 'FAILED':
            raise Exception("STATUS:" + query_execution_status)
        else:
            print("STATUS:" + query_execution_status)
            time.sleep(i)
    else:
        client.stop_query_execution(QueryExecutionId=query_execution_id)
        raise Exception('TIME OVER')
    QUERY_FILE_FROM = PATH + query_execution_id + ".csv"
    QUERY_FILE_TO = PATH + "out.csv"

    # rename csv file
    s3 = boto3.client('s3')
    s3.copy_object(Bucket=S3_BUCKET, Key=QUERY_FILE_TO, CopySource={'Bucket': S3_BUCKET, 'Key': QUERY_FILE_FROM})

    # created s3 object
    s3_objects_key = []
    s3_object_key_csv = QUERY_FILE_FROM
    s3_objects_key.append({'Key': s3_object_key_csv})
    s3_object_key_metadata = QUERY_FILE_FROM + '.metadata'
    s3_objects_key.append({'Key': s3_object_key_metadata})
    # delete s3 object
    for i in range(1, 1 + RETRY_COUNT):
        response = s3.delete_objects(
            Bucket=S3_BUCKET,
            Delete={
                'Objects': s3_objects_key
            }
        )
        if response['ResponseMetadata']['HTTPStatusCode'] == 200:
            print("delete %s complete" % s3_objects_key)
            break
        else:
            print(response['ResponseMetadata']['HTTPStatusCode'])
            time.sleep(i)
    else:
        raise Exception('object %s delete failed' % s3_objects_key)

$ aws cloudformation package --template-file template.yaml --s3-bucket bucketname --output-template-file output-template.yaml

Successfully packaged artifacts and wrote output template to file output-template.yaml.
Execute the following command to deploy the packaged template
aws cloudformation deploy --template-file /<path-to-file>/output-template.yaml --stack-name <YOUR STACK NAME>