Looker supports connections to Amazon Athena, an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Amazon Athena is serverless, so there is no infrastructure to manage. You are charged only for the queries that are run.
This article describes how to connect Looker to an Amazon Athena instance.
Ensure that you have the following:
- A pair of Amazon AWS access keys.
The S3 bucket containing the data you wish to query in Looker with Amazon Athena. The Amazon AWS access keys must have read-write access to this bucket.
Amazon Athena must have access to this S3 bucket by either a role or a permission set, as well as by firewall rules. Do not add security rules to the S3 bucket for Looker’s IP, since this can inadvertently block Amazon Athena’s access to the S3 bucket. (For other dialects besides Amazon Athena, users may want to limit access to the data from the network layer with an IP whitelist, as described on the Enabling Secure Database Access documentation page.)
Knowledge of where your Amazon Athena instance data is located. The region name can be found in the upper right-hand portion of the Amazon Console.
In the Admin section of Looker, navigate to the Connections page and click New Connection. Looker displays this page:
Fill out the connection details:
- Name: Specify the name of the connection. This is how you will refer to the connection in LookML projects.
- Dialect: Select Amazon Athena.
- Host:Port: Specify the name of the host and port. As described in the Athena documentation on the JDBC URL format, the host should be a valid Amazon endpoint (like
athena.eu-west-1.amazonaws.com), and the port should stay at
443. An up-to-date list of endpoints that support Athena can be found here.
- Database: Specify the default database that you would like modeled. Other databases can be accessed, but Looker treats this database as the default database.
- Username: Specify the AWS access key ID.
- Password: Specify the AWS secret access key.
- Persistent Derived Tables: Check to enable PDTs.
- Temp Database: Specify the name of the output directory in your S3 bucket where you want Looker to write your PDTs. The full path to your output directory must be specified in the Additional Params field; see the Specifying Your S3 Bucket for Query Results Output and PDTs section below.
- Max PDT Builder Connections: Specify the number of possible concurrent PDT builds on this connection. Setting this value too high could negatively impact query times. For more information, see the Connecting Looker to Your Database documentation page.
- Additional Params: Specify additional parameters for the connection:
s3_staging_dirparameter is the S3 bucket that Looker should use for query results output and PDTs; see the Specifying Your S3 Bucket for Query Results Output and PDTs section below.
- Flag for streaming results. If you have the
athena:GetQueryResultsStreampolicy attached to your Athena user, you can add
;UseResultsetStreaming=1to the end of your additional params to significantly improve the performance of large result set extraction. This parameter is set to
- Optional additional parameters to add to the JDBC connection string.
- SSL: Ignore; by default, all connections to the AWS API will be encrypted.
- Max Connections: By default, this is set to 5. You can increase this up to 20 if Looker is the main query engine running against Athena. See the Athena service limits documentation for more details about the service limits. See the Connecting Looker to Your Database documentation page for more information.
- Connection Pool Timeout: Specify the connection pool timeout. By default, the timeout is set to 120 seconds. See the Connecting Looker to Your Database documentation page for more information.
- SQL Runner Precache: Unselect this option if you prefer SQL Runner to load table information only when a table is selected. See the Connecting Looker to Your Database documentation page for more information.
- Database Time Zone: Specify the time zone used in the database. Leave this field blank if you do not want time zone conversion. See the Using Time Zone Settings documentation page for more information.
This guide provides more details about custom JDBC configurations and other configuration information.
Specifying Your S3 Bucket for Query Results Output and PDTs
Use the Additional Params field of the Connections page to configure the path to the S3 bucket that Looker will use for storing query results output, and to specify the name of the output directory in the S3 bucket where you want Looker to write PDTs. Specify this information using the
s3_staging_dir JDBC parameter is an alternative way to configure the Amazon Athena
S3OutputLocation property, which is required for Athena JDBC connections. See the Athena documentation on JDBC Driver Options for more information and a list of all available JDBC driver options.
In the Additional Params field, specify the
s3_staging_dir parameter using the following format:
<s3-bucket>is the name of the S3 bucket.
<output-dir>is the directory where Looker will write query results output.
The AWS access key pair must have write permissions to the
To configure the directory where Looker will write PDTs, enter the name of the directory in the above S3 bucket in the Temp Database field. For example, if you want Looker to write PDTs into
s3://<s3-bucket>/looker_scratch, then enter this in the Temp Database field:
In the Temp Database field, you only need to enter the name of the directory. Looker gets the S3 bucket name from the
s3_staging_dir parameter that you enter in the Additional Params field.
- Amazon Athena documentation
- Amazon Web Services Console for Athena (requires an AWS login)
- Amazon Athena SQL and HiveQL reference
LogPath JDBC driver options for debugging connections. To use them, add
;LogLevel=DEBUG;LogPath=/tmp/athena_debug.log to the end of the Additional Params field and test the connection again.
If Looker is hosting the instance, then Looker Support or your analyst will need to retrieve this file to continue debugging.
Looker’s ability to provide some features depends on whether the database dialect can support them.
In the current Looker release, Amazon Athena supports the following Looker features:
After completing the database connection, configure authentication options.