BI and visualization with AWS Athena

This program creates three AWS resources using the Python language: an Athena Database, a Named Query, and a Repository to store our query results.

First, we create an AthenaDatabase resource. This resource instance represents the AWS Athena database, which will store the tables and their metadata in this Athena application. Second, a named query is created using AthenaNamedQuery resource which represents the SQL query to execute on this database later. Finally, an S3Bucket resource is created to store the results of the Athena query. Athena will write the results of each query execution to this S3 bucket.

Find the complete program below:

using Pulumi;
using Pulumi.Aws.Athena;
using Pulumi.Aws.S3;
using Pulumi.Aws.Glue;

class AthenaStack : Stack
{
    public AthenaStack()
    {
        // Create an AWS S3 bucket to store query results
        var bucket = new Bucket("athenaBucket");

        // Create an AWS Glue Catalog Database
        var glueCatalogDb = new CatalogDatabase("athenaDatabase", new CatalogDatabaseArgs
        {
            Name = "my_database",
            CatalogId = Pulumi.Aws.Glue.GetCatalogId.Invoke().Apply(catalogId => catalogId.CatalogId),
        });

        // Create a Simple Athena Query
        var namedQuery = new NamedQuery("athenaNamedQuery", new NamedQueryArgs
        {
            Database = glueCatalogDb.Name,
            QueryString = $"SELECT * FROM my_table",
            Description = "This is a query that selects all from my_table",
        });

        // Export the bucket name and the query ID
        this.BucketName = bucket.Id;
        this.QueryId = namedQuery.Id;
    }

    [Output]
    public Output<string> BucketName { get; set; }

    [Output]
    public Output<string> QueryId { get; set; }
}

This program gives you an Athena Database that you can run queries on, by using AthenaNamedQuery and an S3 Bucket that received the query results.

Remember to replace "my_table" with the name of your table.

For visualizing Athena query results, you might want to look into AWS services like QuickSight, or third party tools like Tableau, Looker, etc. However, setting up these services is beyond the scope of this Pulumi program.