Amazon web services – AWSSDK.Athena C# You are not authorized error – How to switch Role in code

I got an Athena query working with .NET Service Client (V3). See the output here:

Here is the .NET code that works. Make sure you specify you bucket name, etc to get this program to work successfully. Notice you can specify the working group on the StartQueryExecutionRequest object. For more information, see How workgroups work.

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Amazon;
using Amazon.Athena;
using Amazon.Athena.Model;
using Amazon.Runtime;

namespace AthenaDotNet
{
    class StartQueryExample
    {
        //  Demonstrates how to query a table with a comma-separated value (CSV) table.
        //  For information, see https://docs.aws.amazon.com/athena/latest/ug/work-with-data.html
        public static int CLIENT_EXECUTION_TIMEOUT = 100000;
        public static string ATHENA_OUTPUT_BUCKET = "s3://<MYBUCKET>"; // change the Amazon S3 bucket name to match your environment
        public static string ATHENA_SAMPLE_QUERY = "SELECT * FROM <BUCKET>;"; // change the Query statement to match your environment
        public static long SLEEP_AMOUNT_IN_MS = 1000;
        public static string ATHENA_DEFAULT_DATABASE = "mydatabase"; // change the database to match your database
        static async Task Main(string[] args)
        {
            // Set up the credentials using Environment Variables
            var credentials = new EnvironmentVariablesAWSCredentials();
            var athenaClient = new AmazonAthenaClient(credentials,RegionEndpoint.USWest2);

            string queryExecutionId = await SubmitAthenaQuery(athenaClient);
            await WaitForQueryToComplete(athenaClient, queryExecutionId);
            await ProcessResultRows(athenaClient, queryExecutionId);
        }

        // Submits a sample query to Amazon Athena and returns the execution ID of the query.
        static async Task<string> SubmitAthenaQuery(AmazonAthenaClient athenaClient)
        {
            try
            {
                // The QueryExecutionContext allows us to set the database.
                var queryExecutionContext = new QueryExecutionContext
                {
                    Database = ATHENA_DEFAULT_DATABASE
                };

                // The result configuration specifies where the results of the query should go.
                var resultConfiguration = new ResultConfiguration
                {
                    OutputLocation = ATHENA_OUTPUT_BUCKET
                };

                var startQueryExecutionRequest = new StartQueryExecutionRequest
            {
                QueryString = ATHENA_SAMPLE_QUERY,
                QueryExecutionContext = queryExecutionContext,
                ResultConfiguration = resultConfiguration,
                WorkGroup = "MyGroup"
            };

                var startQueryExecutionResponse = await athenaClient.StartQueryExecutionAsync(startQueryExecutionRequest);
                return startQueryExecutionResponse.QueryExecutionId;
            }
            catch (AmazonAthenaException e)
            {
                Console.WriteLine("Error submitting query: " + e.Message);
                throw;
            }
        }

        // Wait for an Amazon Athena query to complete, fail, or be canceled.
        static async Task WaitForQueryToComplete(AmazonAthenaClient athenaClient, string queryExecutionId)
        {
            var getQueryExecutionRequest = new GetQueryExecutionRequest
            {
                QueryExecutionId = queryExecutionId
            };

            bool isQueryStillRunning = true;
            while (isQueryStillRunning)
            {
                var getQueryExecutionResponse = await athenaClient.GetQueryExecutionAsync(getQueryExecutionRequest);
                var queryState = getQueryExecutionResponse.QueryExecution.Status.State;

                if (queryState == QueryExecutionState.FAILED)
                {
                    throw new Exception("The Amazon Athena query failed to run with error message: " + getQueryExecutionResponse.QueryExecution.Status.StateChangeReason);
                }
                else if (queryState == QueryExecutionState.CANCELLED)
                {
                    throw new Exception("The Amazon Athena query was cancelled.");
                }
                else if (queryState == QueryExecutionState.SUCCEEDED)
                {
                    isQueryStillRunning = false;
                }
                else
                {
                    // Sleep an amount of time before retrying again.
                    await Task.Delay(CLIENT_EXECUTION_TIMEOUT);
                }

                Console.WriteLine("The current status is: " + queryState);
            }
        }

        // This code retrieves the results of a query.
        static async Task ProcessResultRows(AmazonAthenaClient athenaClient, string queryExecutionId)
        {
            try
            {
                // Max Results can be set but if it's not set,
                // it will choose the maximum page size.
                var getQueryResultsRequest = new GetQueryResultsRequest
                {
                    QueryExecutionId = queryExecutionId
                };

                var getQueryResultsResponse = await athenaClient.GetQueryResultsAsync(getQueryResultsRequest);
                var resultSet = getQueryResultsResponse.ResultSet;
                var columnInfoList = resultSet.ResultSetMetadata.ColumnInfo;
                var results = resultSet.Rows;

                ProcessRow(results, columnInfoList);
            }
            catch (AmazonAthenaException e)
            {
                Console.WriteLine("Error processing result rows: " + e.Message);
                throw;
            }
        }

        static void ProcessRow(List<Row> rows, List<ColumnInfo> columnInfoList)
        {
            foreach (var row in rows)
            {
                var allData = row.Data;
                foreach (var data in allData)
                {
                    Console.WriteLine("The value of the column is " + data.VarCharValue);
                }
            }
        }
    }
}