Creating a CloudWatch Metrics from the Athena Query results

It's somewhat involved, but you can use a Lambda for this. In a nutshell:

1. Setup your query in Athena and make sure it works using the Athena console.
2. Create a Lambda that:
   • Runs your Athena query
   • Pulls the query results from S3
   • Parses the query results
   • Sends the query results to CloudWatch as a metric
3. Use EventBridge to run your Lambda on a recurring basis

Here's an example Lambda function in Python that does step #2. Note that the Lamda function will need IAM permissions to run queries in Athena, read the results from S3, and then put a metric into Cloudwatch.

import time
import boto3


query = 'select count(*) from mytable'
DATABASE = 'default'
bucket='BUCKET_NAME'
path='yourpath'


def lambda_handler(event, context):
    
    #Run query in Athena
    client = boto3.client('athena')
    output =  "s3://{}/{}".format(bucket,path)
    # Execution
    response = client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': DATABASE
        },
        ResultConfiguration={
            'OutputLocation': output,
        }
    )

    #S3 file name uses the QueryExecutionId so 
    #grab it here so we can pull the S3 file.
    qeid = response["QueryExecutionId"]
    
    
    #occasionally the Athena hasn't written the file
    #before the lambda tries to pull it out of S3, so pause a few seconds
    #Note:  You are charged for time the lambda is running.
    #A more elegant but more complicated solution would try to get the 
    #file first then sleep.
    time.sleep(3)
    
    ###### Get query result from S3.
    s3 = boto3.client('s3');
    objectkey = path + "/" + qeid + ".csv"
    #load object as file
    file_content = s3.get_object(
        Bucket=bucket,
        Key=objectkey)["Body"].read()
    #split file on carriage returns
    lines = file_content.decode().splitlines()
    #get the second line in file
    count = lines[1]
    #remove double quotes
    count = count.replace("\"", "")
    #convert string to int since cloudwatch wants numeric for value
    count = int(count)
    
    
    #post query results as a CloudWatch metric
    cloudwatch = boto3.client('cloudwatch')
    response = cloudwatch.put_metric_data(
        MetricData = [
            {
                'MetricName': 'MyMetric',
                'Dimensions': [
                    {
                        'Name': 'DIM1',
                        'Value': 'dim1'
                    },
                ],
                'Unit': 'None',
                'Value': count
            },
        ],
        Namespace = 'MyMetricNS'
    )
    
    return response
    return

You can use CloudWatch custom widgets, see "Run Amazon Athena queries" in Samples.