Live Connections to Remote Databases with Federated Tables
Federated tables allow CARTO users to combine data hosted in CARTO with data that lives in a remote database such as Amazon Aurora and Microsoft Azure
Getting your data connected is usually the first step in working with the CARTO platform. That data might be in files databases URLs services etc. The options are numerous so we offer a wide range of connectivity capabilities to different sources—each coming with its own benefits and tradeoffs.
At the end of the day every dataset connected to CARTO gets represented in the form of a table but these come in distinct flavors:
- Native Tables: When data is pushed to CARTO for management native PostgreSQL data tables are created. This is the case when you upload a file like a Shapefile CSV GeoJSON or Geopackage. Data from any of these sources can be transformed like in any regular database.
- Sync Tables: When connecting a remote file via a public URL Dropbox GDrive or ArcGIS Server data gets imported and cached in a table in CARTO that is usually updated automatically with the contents from the original dataset every X defined minutes. Connections to external databases where we use ODBC to connect to a remote database dump the data and cache it in CARTO also fall down under this category. We have already connectors for the major databases like PostgreSQL MySQL SQL Server and pretty much any database that supports ODBC. In this type of tables you can not modify the data in CARTO because at the next sync cycle we will replace the data from whatever comes from the source.
This process makes CARTO very performant with our internal database. But what happens when you can’t or you don't want to cache the data in CARTO?
Introducing Federated Tables
Technically a Federated Table is a PostgreSQL Foreign Data Wrapper to a remote server allowing us to perform live queries to a remote database. Think of it as a virtual table that looks like a regular sync table in CARTO — but when used makes the queries you perform in CARTO travel to the remote database and get executed there.
Use cases for federated tables include:
- Having frequently changing data that you join with other tables in CARTO. Because queries are dynamically executed on your remote server there is no need for continuous refreshes.
- Having a large amount of data that is dificult to sync with CARTO but that you only need to query distinct parts of like in the case of time-series databases.
- You want to leverage the scalability or low latency of your cloud database from within the CARTO ecosystem.
So technically this means that when you do a query to make a choropleth map:
CARTO will push the filters to the federated database executing for example:
And then join locally the data inside CARTO and execute any spatial operations that could not be sent to the federated database.
But wait there's more! We have extended the capabilities of Foreign Data Wrapper to push spatial aggregations and filters down to the remote server. That of course requires the usage of a remote server that has PostGIS capabilities (which Aurora Citus AWS RDS and Google Cloud SQL does!). In that case when you run a query like the following to retrieve a vector tile:
The remote server will run the following SQL:
Which means that we have pushed the spatial filter down to the remote server and the query took ~15 seconds to run which is too slow for a tile request.
We can break down the time the query took into 3 steps:
In total 3200 KB were transferred from the remote to the local server.
With our implementation running the same query the remote server will run ALSO the spatial aggregation defined by ST_AsMVT:
And that query now runs in 840 miliseconds. That's 17x faster!
In this scenario 285 KB were transferred between both servers.
As you can see by pushing down the filter and the aggregation to the remote server we only send the minimum data between servers speeding up the federated queries tremendously. Of course there will always be latency for running a query between different cloud providers (Google Cloud and AWS in this case) and locations but if servers are close by and ideally on the same cloud provider latency shouldn't be an issue.
We have worked hard to ensure that the maximum possible set of filters and aggregations get passed through to the original database to reduce the payload between servers and take advantage of the capabilities of the remote database. Still there are some cases where tuning will be necessary and we will be there to help you with non performant queries and keep pushing the limits of federated tables. Talk to us!
Designed for cloud databases and on-premise
Right now we have implemented and heavily optimized this functionality to connect to the rich PostgreSQL ecosystem like:
- AWS RDS GCP Cloud SQL Azure Database for PostgreSQL.
- Auto-scaling databases compatible with PostgreSQL like Amazon Aurora and Hyperscale (Citus) on Azure Database.
Depending on the destination database the performance of spatial operations might be different because of their version of PostGIS (the spatial extension of PostgreSQL). We will follow with more specific measurements in follow-ups blog posts.
We have plans to add support to other databases including SAP Hana Oracle SQL Server and Cloud Spanner so if you are interested in any of those please let us know!
Do you want to start using Federated Tables?
We are glad you are interested! We would love to talk to you about activating this functionality on your CARTO account or discuss your connectivity needs to other databases and warehouses.
Get in touch! Let us know what your connectivity needs are