Amazon Athena Azure Synapse connector

The Amazon Athena connector for Azure Synapse analytics enables Amazon Athena to run SQL queries on your Azure Synapse databases using JDBC.

Prerequisites

Limitations

Terms

Parameters

Use the Lambda environment variables in this section to configure the Synapse connector.

Connection string

Use a JDBC connection string in the following format to connect to a database instance.

synapse://${jdbc_connection_string}

Using a multiplexing handler

You can use a multiplexer to connect to multiple database instances with a single Lambda function. Requests are routed by catalog name. Use the following classes in Lambda.

Multiplexing handler parameters

The following example properties are for a Synapse MUX Lambda function that supports two database instances: synapse1 (the default), and synapse2.

Providing credentials

To provide a user name and password for your database in your JDBC connection string, you can use connection string properties or AWS Secrets Manager.

Example connection string with secret name

The following string has the secret name ${secret_name}.

synapse://jdbc:synapse://hostname:port;databaseName=<database_name>;${secret_name}

The connector uses the secret name to retrieve secrets and provide the user name and password, as in the following example.

synapse://jdbc:synapse://hostname:port;databaseName=<database_name>;user=<user>;password=<password>

Using a single connection handler

You can use the following single connection metadata and record handlers to connect to a single Synapse instance.

Single connection handler parameters

The single connection handlers support one database instance and must provide a default connection string parameter. All other connection strings are ignored.

The following example property is for a single Synapse instance supported by a Lambda function.

Configuring Active Directory authentication

The Amazon Athena Azure Synapse connector supports Microsoft Active Directory Authentication. Before you begin, you must configure an administrative user in the Microsoft Azure portal and then use AWS Secrets Manager to create a secret.

Modifying the connection string

To enable Active Directory Authentication for the connector, modify the connection string using the following syntax:

synapse://jdbc:synapse://hostname:port;databaseName=database_name;authentication=ActiveDirectoryPassword;{secret_name}

Using ActiveDirectoryServicePrincipal

The Amazon Athena Azure Synapse connector also supports ActiveDirectoryServicePrincipal. To enable this, modify the connection string as follows.

synapse://jdbc:synapse://hostname:port;databaseName=database_name;authentication=ActiveDirectoryServicePrincipal;{secret_name}

For secret_name, specify the application or client ID as the username and the secret of a service principal identity in the password.

Spill parameters

The Lambda SDK can spill data to Amazon S3. All database instances accessed by the same Lambda function spill to the same location.

Data type support

The following table shows the corresponding data types for Synapse and Apache Arrow.

Partitions and splits

A partition is represented by a single partition column of type varchar. Synapse supports range partitioning, so partitioning is implemented by extracting the partition column and partition range from Synapse metadata tables. These range values are used to create the splits.

Performance

Selecting a subset of columns significantly slows down query runtime. The connector shows significant throttling due to concurrency.

The Athena Synapse connector performs predicate pushdown to decrease the data scanned by the query. Simple predicates and complex expressions are pushed down to the connector to reduce the amount of data scanned and decrease query execution run time.

Predicates

A predicate is an expression in the WHERE clause of a SQL query that evaluates to a Boolean value and filters rows based on multiple conditions. The Athena Synapse connector can combine these expressions and push them directly to Synapse for enhanced functionality and to reduce the amount of data scanned.

The following Athena Synapse connector operators support predicate pushdown:

Combined pushdown example

For enhanced querying capabilities, combine the pushdown types, as in the following example:

SELECT * 
FROM my_table 
WHERE col_a > 10 
    AND ((col_a + col_b) > (col_c % col_d)) 
    AND (col_e IN ('val1', 'val2', 'val3') OR col_f LIKE '%pattern%');

Passthrough queries

The Synapse connector supports passthrough queries. Passthrough queries use a table function to push your full query down to the data source for execution.

To use passthrough queries with Synapse, you can use the following syntax:

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

The following example query pushes down a query to a data source in Synapse. 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'
        ))

License information

By using this connector, you acknowledge the inclusion of third party components, a list of which can be found in the pom.xml file for this connector, and agree to the terms in the respective third party licenses provided in the LICENSE.txt file on GitHub.com.

Additional resources