The concept of federated queries isn’t new. Facebook PrestoDB popularized the idea of distributed structured query language (SQL) query engines in 2013.
Over the years, AWS, Google, Microsoft, and many others in the industry have accelerated the adoption of a distributed query engine model within their products. For example, AWS developed Amazon Athena on top of the Presto code base, while Google’s BigQuery is based on Cloud SQL.
Organizations store their data in several database and storage systems, such as relational databases (MySQL, SQL Server, Postgres) and object storage systems (S3, HDFS) for storing large amounts of data at a cheaper rate. Organizations often faced the challenge of being able to quickly access the right data at the right time.
Presto simplified this by enabling the querying of relational and non-relational databases and object stores - or disparate sources as you can call it collectively - via SQL, allowing for easier access to data from BI tools and even organizations’ own code.
Facilitating access to data from multiple sources in a single query, and that too fast is what is so revolutionary about Query Federation. It’s because consolidating data from different sources used to be a long, tedious process. You need Extract, Transform, Load (ETL) processes to bring data together into a shared format.
But ETL tools typically aren’t considered suitable by the community experts if you are looking for near-real-time or on-demand data access. They were designed for a batch mode of working where commands are read and acted upon as a batch without user intervention. ETL was more suited for established, slow-changing data. Also, ETL is not accessible to data analytics and business users directly.
The benefits of federated queries are immense compared to the traditional querying approaches of other database solutions. Here are a few:
There is no need for users to remember credentials or log into individual databases as everything is centralized within the federated query service. This enables unified access to data across all source types and IT environments.
Federated queries make it easier for data scientists and analysts to analyze data as the traditional ETL tools were geared more toward developers and coders who understood database language.
Federated queries are usually optimized before execution, enabling hundreds of user queries to be load-balanced and de-duplicated in real-time. This leads to higher throughput and lowers costs when using advanced analytics or business intelligence tools, promoting data-driven decision-making.
The biggest advantage is that the users don’t need to know the specific query or data language for each database. Automated Data Definition Language (DDL) conversion in federated queries allows anyone to perform queries on all data sources.
Our extensive study on digital transformations finds that over 90% of IT and data leaders are planning to use either multi-cloud or hybrid cloud architectures. Applications and data are therefore highly unlikely to be consolidated on a single cloud platform – be it AWS, Azure, GCP, IBM or your private virtualized environment.
This means that data will increasingly get distributed across data sources and become difficult to manage. While data lakes are one desirable solution, consolidating all enterprise data into a lake and continuously updating it can be expensive.
Looking for the fastest way to analyze data stored in Amazon S3?
Users simply point Athena at data stored in their S3 bucket, identify their fields, run the queries, and get results back in seconds.
Amazon’s new Athena platform uses federated queries that enable quick and easy SQL queries across data stored in several relational, non-relational, object, and custom data sources. Using data source connectors that run on AWS Lambda, data scientists, engineers, and analysts can analyze data from multiple sources running on-prem or the cloud with a single SQL query.
Trianz has built Athena Federated Query (AFQ) extensions on top of the Athena platform to simplify BI and facilitate cross-data-source analytics. These extensions typically scan data from S3 and execute the Lambda-based connectors to read data from on-prem Teradata, Amazon Redshift, Google BigQuery, and SAP HANA.
With a complete ecosystem of Trianz and AWS AFQ connectors, you can produce hybrid/multi-cloud analytics and visualizations without migrating or consolidating your data. The combined library of AFQ connectors can pull data from literally any source in your enterprise or other cloud platforms such as Azure or GCP.
Our AFQ connectors have been tested and tried by Amazon and large, complex data organizations in Fortune 1000 companies. The power of Athena is to allow technical and non-technical users to generate rich, powerful visualizations with simple queries, and saves time and resources in the long run.
What Is an SQL Query Engine? SQL query engine architecture was designed to allow users to query a variety of data sources within a single query. While early SQL-based query engines such as Apache Hive allowed analysts to cut through the clutter of analytical data, they found running SQL analytics on multi-petabyte data warehouses to be a time-intensive process that was difficult to visualize and hard to scale.Explore
Application Modernization at Speed and Scale Enterprises are pursuing greater application scalability, cost efficiency, and standardization with containerization and virtualization platforms. So, what’s the difference? Containers are a type of virtualization technology that allows users to run multiple operating systems inside a single instance of an OS. They are lightweight and portable, making them ideal for running applications across different platforms.Explore
Container Orchestration or Compute Service? Amazon Web Services (AWS) offers a range of cloud computing services to meet enterprise needs. Included in its service offering is the elastic compute service (ECS) and elastic compute cloud (EC2). Choosing between these two services can be difficult, as one focuses on virtualization while the other manages containerization. In the following article, we will explore the differences between Amazon ECS and EC2 to help you better understand which service is right for your use case.Explore
What is Application Modernization? Application modernization is the process of converting, rewriting, or porting legacy software packages to operate more efficiently with a modern infrastructure. This can involve migrating to the cloud, creating apps with a serverless architecture, containerizing services, or overhauling data pipelines using a modern DevOps model.Explore
What are the Differences? Though often used interchangeably, data pipelines and ETL are two different methodologies for managing and structuring data. ETL tools are used for data extraction, transformation, and loading. Whereas data pipelines encompass the entire set of processes applied to data as it moves from one system to another. Sometimes data pipelines involve transformation, and sometimes they do not.Explore