We're excited to announce the integration of Foreign Data Wrappers (FDWs) in CrateDB 5.7, marking a significant enhancement in its data integration features.
Foreign Data Wrappers allow for direct access and manipulation of data residing in various databases and other data sources. Rooted in the SQL/MED (SQL Management of External Data) standard, it offers a uniform approach for databases to connect with external data sources, spanning SQL and NoSQL databases, file systems, and web services. The introduction of Foreign Data Wrapper in CrateDB serves to eliminate the existence of data silos and promotes a seamless integration within a heterogenous data environment.
In this blog post, we explore the practical application of CrateDB 5.7’s Foreign Data Wrapper support, particularly highlighting how it enables querying data stored in a PostgreSQL instance. Neon.tech is one of the leaders of serverless PostgreSQL solutions and offers an innovative, scalable, and cost-efficient service for managing databases. FDW streamlines data workflows, allowing for real-time data access and integration from external databases into CrateDB's environment.
Before diving into the integration of CrateDB with serverless PostgreSQL, let's set up our environment. The first step involves ensuring you have a CrateDB instance up and running. You can easily set up a CrateDB cluster on CrateDB Cloud by following the instructions outlined in this tutorial.
The second step involves setting up a PostgreSQL Instance on Neon.tech:
To illustrate the process of executing queries on a PostgreSQL instance from CrateDB utilizing Foreign Data Wrappers, we first create an example table in the PostgreSQL database with some basic information:
Now, if you navigate to the Tables tab in the neon.tech console, you can find the customers table with the initial data.
To access the customers
table from CrateDB, you'll need to set up a Foreign Data Wrapper in CrateDB for your PostgreSQL instance. The process involves the following steps:
This example illustrates the connection between CrateDB and PostgreSQL. To connect CrateDB with other databases via Foreign Data Wrappers, it is important to add the corresponding JDBC driver inside the CrateDB lib/
folder (all JAR files in this location will be added to the class path) and to adapt the JDBC connection string accordingly.
The possibility to query foreign tables from CrateDB through foreign data wrapper support opens a world of opportunities to further enhance data interaction and analysis, such as:
The addition of Foreign Data Wrapper support to CrateDB is a significant step forward, streamlining the integration of data from diverse data sources. This feature simplifies the process of accessing and combining data, enabling users to make the most of CrateDB's powerful analytics alongside the convenience of other databases.