Running federated passthrough queries - Amazon Athena
Supported connectorsConsiderations and limitationsSyntaxExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Running federated passthrough queries

In Athena, you can run queries on federated data sources using the query language of the data source itself and push the full query down to the data source for execution. These queries are called passthrough queries. To run passthrough queries, you use a table function in your Athena query. You include the passthrough query to run on the data source in one of the arguments to the table function. Pass through queries return a table that you can analyze using Athena SQL.

Supported connectors

The following Athena data source connectors support passthrough queries.

Considerations and limitations

When using passthrough queries in Athena, consider the following points:

  • Query passthrough is supported only for Athena SELECT statements or read operations.

  • Passthrough queries must be run within the context of the catalog of the outer query (that is, the query that calls the table function).

  • Query performance can vary depending on the configuration of the data source.

  • Passthrough queries are not supported for views.

Syntax

The general Athena query passthrough syntax is as follows.

SELECT * FROM TABLE(system.function_name(arg1 => 'arg1Value'[, arg2 => 'arg2Value', ...]))

For most data sources, the first and only argument is query followed by the arrow operator => and the query string.

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

For simplicity, you can omit the optional named argument query and the arrow operator =>.

SELECT * FROM TABLE(system.query('query string'))

If the data source requires more than the query string, use named arguments in the order expected by the data source. For example, the expression arg1 => 'arg1Value' contains the first argument and its value. The name arg1 is specific to the data source and can differ from connector to connector.

SELECT * FROM TABLE(
        system.query(
            arg1 => 'arg1Value',
            arg2 => 'arg2Value',
            arg3 => 'arg3Value'
        ));

For information about the exact syntax to use with a particular connector, see the individual connector page.

Quotation mark usage

Argument values, including the query string that you pass, must be enclosed in single quotes, as in the following example.

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

When the query string is surrounded by double quotes, the query fails. The following query fails with the error message COLUMN_NOT_FOUND: line 1:43: Column 'select * from testdb.persons limit 10' cannot be resolved.

SELECT * FROM TABLE(system.query(query => "SELECT * FROM testdb.persons LIMIT 10"))

To escape a single quote, add a single quote to the original (for example, terry's_group to terry''s_group).

Examples

The following example query pushes down a query to a data source. The query selects all columns in the customer table, limiting the results to 10.

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

The following statement runs the same query, but eliminates the optional named argument query and the arrow operator =>.

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