On AWS Athena, how to write a passthrough query that specifies the datasource?

Question

From the AWS documentation (https://docs.aws.amazon.com/athena/latest/ug/connectors-bigquery.html#connectors-bigquery-passthrough-queries), it says that I can use something like:

SELECT * FROM TABLE(
        system.query(
            query => 'SELECT * FROM customer LIMIT 10'
        ))
... to pass the query straight to the connector. And its working fine when I use the query editor on Athena AWS console.

I have integrated Athena with Metabase and that same query doesn't work on Metabase because it doesn't specify the data source on the query. My Metabase integration is such that it's important that I'm able to query from multiple data sources. But, at the same time, I want to be able to use passthrough.

How can I specify the data source on the SQL query that uses the passthrough?

I'm hoping there is some param like data_source that I can pass to system.query to specify the data source to use on the passthrough.

AWS TAM · Answer

To specify the data source when using the passthrough query feature in Athena, you can use the data_source parameter within the system.query() function. 
Here is how you can do it.

In your SQL query, use the system.query() function to pass the query to the connector, and specify the data_source parameter

SELECT * FROM TABLE(system.query(query => 'SELECT * FROM customer LIMIT 10', data_source => 'your-data-source-name'))

Make sure that the data source you specify is configured and available in your Athena environment.

If you are using Metabase to integrate with Athena, you will need to ensure that the data source you are specifying in the passthrough query matches the data source configured in your Metabase integration.

AbdulRehman · Answer

users can add the target catalog to resolve this issue; for example;

```
SELECT * FROM TABLE(
        catalog.system.query(
            query => 'SELECT * FROM customer LIMIT 10;'
        ))
```
 should be the full resolution of the function that the customer can execute; which would allow multiple executing passthrough queries from different connector; of course if the connector supports query passthorugh.

On AWS Athena, how to write a passthrough query that specifies the datasource?

Add your answer

Relevant content