[SOLVED] How can I use a fully-qualified table function in AWS Athena Federated Query Pass-Through?

How can I use a fully-qualified table function in AWS Athena Federated Query Pass-Through?

A few days ago AWS launched pass-through querying for AWS Athena Federated Query which allows for queries to be passed straight through to the underlying data store.

https://aws.amazon.com/about-aws/whats-new/2024/04/amazon-athena-federated-query-pass-through/

All the docs I have seen uses non-fully qualified table names. ie:

SELECT * FROM TABLE(
    system.query(
        query => 'SELECT * FROM customer LIMIT 10'
    ))

https://docs.aws.amazon.com/athena/latest/ug/federated-query-passthrough.html

This is fine for querying via the console where you can directly control the context in which a query is run. But third-party tools often just use fully qualified table names to enable cross database / schema queries (eg dbt)

Trino does support fully qualified names for polymorphic table functions, but every permutation I try doesn't work. https://trino.io/docs/current/develop/table-functions.html#access-control

Is there some magic syntax or work around I am missing?

Solution

Federated query has since been updated to support this.

SELECT * FROM TABLE(catalog.system.query(query => 'query string'))