Does a tool exist that can simplify data virtualization yet is inexpensive to maintain? After all, data virtualization is key to simplifying data analytics and digital transformation. But up until recently, not only has there been a shortage of inexpensive data virtualization platforms, but the existing platforms have also been incredibly complex.
That all seems to be changing. AWS Athena with its Presto engine has gotten very wide acceptability as a mature data lake solution on top of Amazon Simple Storage Service. With Athena, organizations can also manage multiple on-premises and on-cloud data sources. Now, the AWS Athena Query Federation can simplify connecting various data sources and allow Athena to be used for data virtualization.
When Athena SQL is combined with AFQ Connectors, an organization can mix and match data from any source. Not only are SQL joins or aggregates possible, but with CTAS (Create Table as Select) tactical data migration from remote data sources to Athena and S3 can also be achieved. This is very advantageous considering that SQL can be used for not only data mining but for data migration too.
As SQL is involved, it is simpler. And because the data is on S3, it is faster.
For the purpose of this blog and demonstrating Athena AFQ Federation, we will use Trianz Snowflake AFQ Connector. Your Snowflake instance will be running on the cloud infrastructure.
Imagine a situation where Snowflake is your data warehouse, and Athena is your data lake. When you need to unify data from the Snowflake warehouse and the Athena data lake, you would deploy the Trianz Snowflake AFQ Connector available in the AWS Serverless Application Repository (SAR). There are other connectors available for Teradata, Sap Hana, Oracle, Big Query, Cloudera, and others as well.
Therefore, depending upon your requirements, multiple connectors can be configured. The deployment details are more or less like the Snowflake Connector described below. You can always reach out to [email protected] for any queries and feedback.
Let’s start with highlighting the solution and then detailing the steps involved.
Solution overview
Data federation is the capability to integrate data in another data store using a single interface (Athena). The following diagram depicts how Athena federation works by using Lambda to integrate with a federated data source.
AWS Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage, and you pay only for the queries that you run. Simply point to your data in Amazon S3, define the schema, and start querying using standard SQL.
AWS Lambda lets you run code without provisioning or managing servers. You can run code for virtually any type of application with zero administration and only pay for when the code is running.
Snowflake is a widely recognized cloud based data warehouse. Snowflake can be connected using code via its Java Database Connectivity (JDBC) drivers.
In AFQ Federation, Athena will route Snowflake specific queries via Lambda to the target Snowflake instance. Inside the Lambda, JDBC code will make connection and fetch data and route it back to Athena. If the data does not fit into Lambda RAM runtime memory, it will spill the data to S3 ensuring reliable transmission. For snowflake, we have implemented pagination-based approach for parallel processing of data. There is a custom environment variable “pagecount” which defines the number of records per partition.
Number of partitions = Total number of records / pagecount
Based on the number of partitions in a Snowflake Table, Athena will try to load the data by running multiple Lambda in parallel.
The Trianz Snowflake AFQ Connector (and other connectors from Trianz) ensure simple, reliable, and fast transmission for data – two to three times faster than other data virtualization platforms – for joining or aggregating within Athena.
Prerequisites
Before you get started, create a secret for the Snowflake instance with username and password using AWS Secrets Manager.
On the Secrets Manager console, choose Secrets.
Choose Store a new secret.
Choose the Other types of secrets (e.g., API key) radio button
Set the credentials as key/value pairs (username, password) for your Snowflake instance.
For Secret name, enter a name for your secret. Use the prefix using string snowflake so it’s easy to find.
Leave the remaining fields at their defaults and choose Next.
Complete your secret creation.
Setting up your S3 bucket
On the Amazon S3 console, create a new S3 bucket and subfolder for Lambda to use. For this post, use the name athena-accelerator/snowflake.
Configuring Athena federation with Snowflake instance
To configure Athena federation with Snowflake instance, complete the following steps:
On the AWS Serverless Application Repository, choose Available applications.
In the search field, enter TrianzSnowflakeAthenaJDBC..
In the Application settings section, provide the following details:
Application name - TrianzSnowflakeAthenaJDBC
SecretNamePrefix – trianz-snowflake-athena
SpillBucket – Athena-accelerator/snowflake
JDBCConnectorConfig –
Format : snowflake://jdbc:snowflake://{snowflake_instance_url}/?warehouse={warehousename}&db={databasename}&schema={schemaname}&${secretname}
Example : snowflake://jdbc:snowflake://trianz.snowflakecomputing.com/?warehouse=ATHENA_WH&db=ATHENA_DEV&schema=ATHENA&${trianz-snowflake-athena}DisableSpillEncyption – False
LambdaFunctionName – trsnowflake
SecurityGroupID – Security group ID where Snowflake instance is deployed. Please ensure valid Inbound and Outbound rules are applied based on your connection.
SpillPrefix – Create a folder under the S3 bucket which is created in previous steps and specify the name. Example: athena-spill
Subnetids – Use the subnets where Snowflake instance is running with comma separation. Ensure the subnet is in VPC and has NAT Gateway and Internet Gateway attached.
Select the I acknowledge check box.
Choose Deploy.
Note: By default, connectors have a 7-day license key. To extend the license key or for help on deploying connectors please reach out to [email protected]
Note: Make sure the Your IAM Role and Lambda Role have permissions to access Serverless Application Repository, CloudFormation, S3, CloudWatch, CloudTrail, Secrets Manager, Lambda, Athena
Running federated queries with Athena
To start running federated queries, complete the following steps:
On the Athena console, choose Workgroup with Athena engine version 2. Athena Federated Query is supported only on Athena engine version.
The current engine version for any workgroup can be found in Workgroups page.
Run your queries, using lambda:trsnowflake to run against tables in Snowflake Database.
Below is a union all query example of data from table in s3 and table in snowflake.
Athena query performance comparison
Several customers have asked us for performance insights and prescriptive guidance on how queries in Athena compare against federated queries, and how to use them.
In this section, we used the COVID19 standard dataset to run results from Athena with Snowflake connection. This should give you an idea of what to expect when running federated queries against Snowflake Database from the Athena.
The following table summarizes the dataset sizes:
sno | Table | Table Size (Records) | Partition table | Application | Total Time | Throughput |
| T1 | COVID19_DATA_20M | 20546048 | partitioned table with 300000 records per partition | Snowflake | 181.61 seconds | 113132 records/second |
| T2 | COVID19_DATA_10M | 10113024 | partitioned table with 200000 records per partition | Snowflake | 18.24 seconds | 87,695 records/second |
| T3 | COVID19_DATA_10M in S3 and COVID19_DATA_10M in Snowfake | 10113024 | partitioned table with 200000 records | S3/ Snowflake | 26.06 seconds | 388,066 records/second |
We ran the following three tests:
T1 – Queries ran from AFQ and retrieved records from the Snowflake instance
T2 – Queries ran from AFQ and retrieved records from the Snowflake instance
T3 – Queries ran from AFQ and retrieved records from the Snowflake instance and S3 with join query
The following graph represents the performance of some of the queries when run from AWS Athena.
Key performance best practice considerations
When considering Athena federation with Snowflake Database, you could take into account the following best practices:
Athena federation works great for queries with predicate filtering because the predicates are pushed down to Snowflake Database. Use filter and limited-range scans in your queries to avoid full table scans.
If your SQL query requires returning a large volume of data from Snowflake Database to Athena (which could lead to query timeouts or slow performance), unload the large tables in your query from Snowflake to your Amazon S3 data lake.
Snowflake schema which is an extension of Star schema is a used data model in Snowflake Database. In the Snowflake schema model, unload your large fact tables into your data lake and leave the dimension tables in Snowflake Database. If large dimension tables are contributing to slow performance or query timeouts, unload those tables to your data lake.
When you run federated queries, Athena spins up multiple Lambda functions, which causes a spike in database connections. It’s important to monitor the Snowflake Database WLM queue slots to ensure there is no queuing. Additionally, you can use concurrency scaling on your Snowflake Database cluster to benefit from concurrent connections to queue up.
Conclusion
In this post, you learned how to configure and use Athena federation with Snowflake Database using Lambda. Now you don’t need to wait for all the data in your Snowflake Database data warehouse to be unloaded to Amazon S3 and maintained on a day-to-day basis to run your queries.
You can use the best-practice considerations outlined in the post to minimize the data transferred from Snowflake Database for better performance. When queries are well-written for federation, the performance penalties are negligible, as observed in the TPC-DS benchmark queries in this post.