AWS Managed Grafana - Athena Datasource - 400 Bad Request


Trying to set up AMG to query athena. Tagged the workgroup, configured the datasource and it tests ok. I don't seem to be able to return any data though, its possible I'm just struggling with syntax - able to test just fine in athena itself.

says syntax is like this

SELECT {column_1}, {column_2} FROM {table};

For now I'm just trying to do

SELECT * FROM $__table() LIMIT 10; or SELECT * FROM {$__table} limit 10; or putting actual table name in

as I'm not 100% the parenthesis, either way, neither work

Query error Query error: 400 Bad Request

Is my syntax bad otherwise how do I find what the actual problem is?


asked 18 days ago55 views
1 Answer
Accepted Answer

When you're querying AWS Athena from AWS Managed Grafana, it's essential to ensure that your syntax aligns with both Grafana's macros and Athena's SQL requirements. Grafana's macros (like $__table) are placeholders that Grafana replaces with the actual value when the query is run.

If you're getting a 400 Bad Request error, it can indicate a few different issues:

  1. Syntax Error: This could be an issue with how you've written your query. Since the basic SELECT * FROM {table} LIMIT 10; works in Athena directly, the issue is likely with how you're using Grafana's macros.

  2. Permissions: Even though the datasource tests OK, it's possible that the permissions associated with the workgroup or the execution role used by Grafana don't include the necessary permissions to read the specific data.

  3. Resource Naming: Ensure that the table names, database names, and any other resources are correctly referenced and that they exist within the context that Grafana is running.

  4. Grafana Version: Ensure that you're using a version of Grafana that supports the macros or syntax you're using.

Here's how you might troubleshoot the issue:

  • Check Grafana's Query Inspector: Grafana has a query inspector tool that allows you to see the actual query that's being sent to Athena. This can help you understand what's going wrong with the query. You can access it by clicking on the query and then selecting the "Query Inspector" option.

  • Review Grafana's Athena Data Source Documentation: Make sure that you're using the macros correctly. Some macros might need to be used in a particular context or require additional parameters.

  • Examine Athena's Query Execution in AWS Console: Go to the Athena console in AWS and check the query history. Sometimes the queries that fail in Grafana will show up there with more detailed error messages.

  • Check Grafana's Logs: If you have access to the server where Grafana is running, you can also check the logs for more detailed error messages.

  • Review IAM Permissions: Make sure that the IAM role that Grafana is using has the necessary permissions to perform queries on Athena and read the relevant S3 buckets.

  • Macro Usage: The macros should be used as per Grafana's documentation. For example, $__table should be used if you are trying to insert a table variable. If you want to use a static table name, you don't need a macro, just use SELECT * FROM your_table_name LIMIT 10.

  • Grafana Support: If you're still stuck, consider reaching out to AWS support directly for more help. Sometimes, issues can be specific to your setup, and having someone look at your exact configuration can be very helpful.

As for the correct syntax, if you're not sure about the macros, start with a simple hardcoded query to ensure that the connection and permissions are all working as expected. Once that's working, you can start introducing variables and macros one at a time to identify where the problem might be.

Drew D
answered 18 days ago
  • Thanks for the guidance, I didn't realise that the query inspector would return the full error message - which it did and was very helpful. Turns out it was lack of output s3 location specified for the Athena workgroup / Athena datasource - both are optional in isolation but it needs to be set in one or the other and there was a slight change needed to S3 IAM policy attached to managed grafana role

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions