Serverless query with AWS Athena

Sure, let's produce a Python Pulumi program that enables running serverless queries on AWS Athena. We'll be using aws.athena.Database and aws.athena.NamedQuery resources from the Pulumi's AWS package. The Database resource allows us to create a database in Athena while the NamedQuery resource is used to store a SQL query.

Here's a simple Pulumi Program.

import pulumi
from pulumi_aws import athena

# Create an AWS Athena database
database = athena.Database('my_database',
    name='example_database',
    # Let's assume that the bucket 'query_results_bucket' exists with the correct setup
    bucket='query_results_bucket',
    force_destroy=True  # Force destroy allows us to delete the database even when it contains tables
)

# Define a named query
query = athena.NamedQuery('my_query',
    database=database.name,
    query='''
        CREATE TABLE IF NOT EXISTS elb_logs (
            request_timestamp STRING,
            elb_name STRING,
            request_ip STRING,
            request_port INT,
            backend_ip STRING,
            backend_port INT,
            request_processing_time DOUBLE,
            backend_processing_time DOUBLE,
            client_response_time DOUBLE,
            elb_response_code STRING,
            backend_response_code STRING,
            received_bytes BIGINT,
            sent_bytes BIGINT,
            request_verb STRING,
            url String,
            protocol STRING,
            user_agent STRING,
            ssl_cipher STRING,
            ssl_protocol STRING
        )
        ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
        WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|\\-|\\d{3}) (|\\-|\\d{3}) (|\\-|\\d*) (|\\-|\\d*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$'
        )
        LOCATION 's3://my-log-bucket/prefix/'
    '''
)

# Export the ID of the named query
pulumi.export('named_query_id', query.id)

This program creates an Athena database, and a named query within it. The named query is an SQL statement that creates a table for Elastic Load Balancer logs.

For further reading, here are the relevant documentation sections for the Database and NamedQuery resources. Remember to replace 's3://my-log-bucket/prefix/' with the location of your own logs, and query_results_bucket with your own bucket for query results.