Live Stream: Turbocharge your aggregations, search & AI models & get real-time insights

Register now
Skip to content
Start free
Start free
Blog

Integrate PostgreSQL with CrateDB Using Foreign Data Wrappers

2024-04-30
by Marija Selakovic,
4 minute read
Feature Focus

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.

Prerequisites

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:

1. Sign Up/Login to Neon.tech: Visit the Neon.tech website and sign up for an account or log in if you already have one.
2. Create a New PostgreSQL Database: Follow the Neon.tech dashboard instructions to create a new PostgreSQL database. During the creation process, you will specify the database name and possibly select a region for your database as illustrated below:

Get started with Neon for free
Figure 1: Get started with Neon
3. Note Connection Details: Once your database is created, note down the connection details provided, including the database name, role, and connection string. You'll need these details to connect to your PostgreSQL database from CrateDB.

Connection details for main
Figure 2: Connection details

 

Creating an Example Table

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:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

INSERT INTO customers (name, email) VALUES
(1, 'John Doe', 'john.doe@example.com'),
(2, 'Jane Smith', 'jane.smith@example.com'),
(3, 'Alex Johnson', 'alex.johnson@example.com');

Now, if you navigate to the Tables tab in the neon.tech console, you can find the customers table with the initial data.

Accessing PostgreSQL Table from CrateDB

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:

1. Create a Server Connection: as a first step, you need to define a foreign server in CrateDB that represents your PostgreSQL instance:
 
CREATE SERVER neon_server
FOREIGN DATA WRAPPER jdbc
OPTIONS (url 'jdbc:postgresql://ep-steep-scene-a29pbt8b.eu-central-1.
    aws.neon.tech/crate?sslmode=require');
2. User Mapping: next, you map a CrateDB user to a PostgreSQL user, including authentication details. Make sure that the username in CrateDB matches the username in PostgreSQL:
 
CREATE USER MAPPING
FOR USER
SERVER neon_server
OPTIONS ("user" 'admin', password '*******');
3. Create a Foreign Table: a foreign table in CrateDB must mirror the structure of the customers table in PostgreSQL.
 
CREATE FOREIGN TABLE public.customers (
    customer_id INT PRIMARY KEY,
    name TEXT,
    email TEXT
) 
SERVER neon_server OPTIONS (schema_name 'public');
4. Query the Table: with the foreign table setup complete, you can now query the customers table directly from CrateDB. 
 
SELECT * FROM public.customers;
+--------------------------+-------------+--------------+
| email                    | customer_id | name         |
+--------------------------+-------------+--------------+
| john.doe@example.com     |           1 | John Doe     |
| jane.smith@example.com   |           2 | Jane Smith   |
| alex.johnson@example.com |           3 | Alex Johnson |
+--------------------------+-------------+--------------+

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.

Possibilities with Foreign Data Wrappers

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:

  • Data Consolidation for Analytics: aggregate data from various databases into CrateDB for advanced analytics and reporting.
  • Cross-Database Joins: use cross-database joins to combine data that resides in separate databases (like PostgreSQL and CrateDB) for complex queries. This can be particularly useful for businesses that store different parts of their data in different systems but need to analyze them as a whole.
  • Data Access without ETL: access external data sources without the need for ETL processes.
  • Migrate Data Between Systems: smoother data migrations between databases by allowing for direct access and manipulation of external data.

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.

Related Posts