Using the GridDB Foreign Data Wrapper for PostgreSQL

Introduction

There are times when data needs the flexibility to be accessed from several different interfaces. For many legacy and business intelligence applications it is still needed to access your data with traditional SQL queries. Luckily a FOREIGN DATA WRAPPER for PostgreSQL and GridDB has been released and is available on Github.
In this post, we will be going over how to properly build and install the foreign data wrapper as well as how to create the EXTENSION in PostgreSQL for a GridDB server. We will also demonstrate ways to translate GridDB containers into PostgreSQL table schemas quickly through the wrapper as well as ways we can manipulate GridDB data using standard SQL queries.

Building a PostgreSQL Database from Source

The easiest and simplest way to build and compile the GridDB Foreign Data Wrapper for PostgreSQL source code is to use it in a PostgreSQL installation that is built from source code. As of this post, the GridDB Foreign Data Wrapper for PostgreSQL is compatible with PostgreSQL versions 9.6 and 10.0. This post is covered for a PostgreSQL 9.6 database.
You can obtain the source code from PostgreSQL’s ftp download site.

$ cd /usr/
$ wget https://ftp.postgresql.org/pub/source/v9.6.9/postgresql-9.6.9.tar.gz
$ tar xvfz postgresql-9.6.9.tar.gz

From there just follow this guide to build, install, and test PostgreSQL.

Obtaining and installing the GridDB Foreign Data Wrapper for PostgreSQL

Once a source installation of PostgreSQL has been completed on your system, obtain the GridDB Foreign Data Wrapper for PostgreSQL’s source code repository from Githu (the name of the repository is griddb_fdw) and placed it in the installation’s contrib directory.

$ cd /path/to/postgresql/source/installation/contrib
$ git clone https://github.com/pgspider/griddb_fdw

The GridDB Foreign Data Wrapper for PostgreSQL requires a fully built GridDB C client along with it. You will need to obtain the C client from GitHub and follow its README.md instructions to build and install.

• NOTE: The C client directory must be named griddb instead of c_client in order for the GridDB Foreign Data Wrapper for PostgreSQL to fully build.

$ cd griddb_fdw
$ git clone https://github.com/griddb/c_client
$ mv c_client griddb
$ cd griddb
## Follow the repository's README instructions for building the C client.

General details on setting and installing GridDB’s C client can be found on either in the README.md file from the C client’s Github repository or from this post about using GridDB’s different APIs.
Once the C client is built and installed properly the Foreign Data wrapper can be built on the griddb_fdw directory.

$ cd /path/to/griddb_fdw
$ make
$ make install

• Note: the Makefile used for building the GridDB Foreign Data Wrapper for PostgreSQL makes references to the src/ and contrib/ directories of PostgreSQL, some of which cannot be found without installing PostgreSQL from source.

Possible Issues

In order for the GridDB Foreign Data Wrapper for PostgreSQL to work with SQL Queries, all the .o and executable files should be placed in the /usr/local/pgsql/share/extension/ directory on your system. Usually the make install command does this automatically, but in the case that you receive errors like:

ERROR: could not open extension control file:

Simply copy all the compiled .o and executable files into the share/extension directory.

$ cd /path/to/postgresql/source/installation/contrib/griddb_fdw
$ make clean
$ make
$ cp * /usr/local/pgsql/share/extension

Accessing a GridDB Cluster

Now that the GridDB Foreign Data Wrapper for PostgreSQL is installed, we can access GridDB data using SQL queries through a PostgreSQL database.
To begin, an extension must be added to PostgreSQL for GridDB using the CREATE EXTENSION command. The name of the GridDB’s extension is 'griddb_fdw'. To access foreign data, PostgreSQL needs a foreign SERVER and a USER MAPPING to connect and authenticate into GridDB. The configurations needed to connect to a GridDB cluster must be specified in the OPTIONS flags for these commands.
You can automatically create foreign tables for existing containers in your GridDB server and import them into the PostgreSQL of your choice. This can be done with the IMPORT FOREIGN SCHEMA command. The name of the remote schema for GridDB is 'griddb_schema'. In the example
below, we will be accessing our GridDB database from the 'public' database in PostgreSQL.

-- Name of the built extension for GridDB is 'griddb_fdw'
CREATE EXTENSION griddb_fdw;
-- The host, port, cluster name, username, and password are needed for
-- PostgreSQL to access your GridDB server. These can be specified in the 'OPTIONS' field
CREATE SERVER griddb_svr FOREIGN DATA WRAPPER griddb_fdw OPTIONS(host '239.0.0.1', port '31999', clustername 'defaultCluster');
CREATE USER MAPPING FOR public SERVER griddb_svr OPTIONS(username 'admin', password 'admin');
-- The name of the remote schema needed to create and load foreign tables from GridDB container
-- is named 'griddb_schema'
IMPORT FOREIGN SCHEMA griddb_schema FROM SERVER griddb_svr INTO public;

Once this is complete the foreign SERVER will encapsulate the connection information to GridDB that the GridDB Foreign Data Wrapper for PostgreSQL (the griddb_fdw extension) will use as its data source.

Accessing GridDB Containers from PostgreSQL

Once the GridDB foreign data wrapper for PostgreSQL is setup and your PostgreSQL database is connected to your GridDB server, GridDB containers and their data can now be accessed with SQL queries.

Viewing Available Containers

All the available foreign tables imported from GridDB can be viewed by querying the information_schema._pg_foreign_tables table.
Example Query:

SELECT foreign_table_name FROM information_schema._pg_foreign_tables;

Example Output

foreign_table_name
-------------------------
employee
department
jp_prefecture
jp_prefecture_readings
water_quality_ts_3

Creating Foreign Tables

There may be times when the tables imported from griddb_schema may not be truly reflective of all the containers in the GridDB database and we need to manually recreate the mapping for that container in PostgreSQL. This can be done by issuing a CREATE FOREIGN TABLE command to PostgreSQL.
For example let us say we have a TimeSeries container named jp_prefecture_readings that records timestamped temperature readings from many sensors in an area and we wish to model this as a FOREIGN TABLE in PostgreSQL.
It could have the below row-schema in the GridDB Java API.

public class InstrumentLog {
             @RowKey
             public Date timestamp;  //Timestamp Column (Primary Key for Timeseries containers)
             public String weatherstationid; //String column for the sensor of recording
             public double temperature; //Temperature reading (Double Column)
             public Blob liveimage; //Image recording (Binary Stream Data or Blob Column)
}

The above GridDB container schema would then be converted to the below FOREIGN TABLE in PostgreSQL that we can then manipulate with SQL. Note that when creating foreign table you must specify the foreign server that PostgreSQL will use to find the foreign container.

CREATE FOREIGN TABLE jp_prefecture_readings (
                  timestamp timestamp,
                  weatherstationid text,
                  temperature float,
                  liveimage bytea
) SERVER griddb_svr;

The general format for creating the FOREIGN TABLE is:

CREATE FOREIGN TABLE name_of_container_in_foreign_database (
                 [ column_name column_type ]
) SERVER foreign_server_name;

Ensure that your column names for both the PostgreSQL foreign table and its corresponding GridDB container are equivalent to prevent any column not found errors. Also ensure that the column types are equivalent to prevent any type mismatch errors in your SQL queries.

Limitations

An important thing to note is that while GridDB rows can be created and destroyed through PostgreSQL, the actual containers that store them cannot. This means that you cannot use PostgreSQL queries to create or drop a container in GridDB.
A CREATE FOREIGN TABLE statements creates a table schema mapping for PostgreSQL to use based on an existing container in GridDB. The DROP FOREIGN TABLE statement will remove the GridDB container from the information_schema._pg_foreign_tables table in PostgreSQL, but leave the container and its data intact in the GridDB database. This means you can no longer access that table from PostgreSQL anymore until create the FOREIGN TABLE again but still access the container through GridDB’s APIs or with TQL queries.

Column Type Reference

For the most part, most column types in GridDB have an equivalent type in PostgreSQL that can be used to create a corresponding foreign table. Below are some of the corresponding column types in PostgreSQL that may not be so obvious.

GridDB Column Type	PostgreSQL Column Type
bool, boolean	boolean
byte	char
double, float	float, real
blob	bytea
string	text

• NOTE: There may be issues storing LONG column values to their full capacity using the GridDB Foreign Data Wrapper for PostgreSQL. This may be due to long column types in GridDB containers being truncated to integer types when being converted into foreign PostgreSQL tables. This could lead to issues with inserting numerical data over 2147483647.

Issuing SQL Queries

Now that we can wrap any GridDB container into a PostgreSQL table, we can incorporate many SQL functionalities with our GridDB data that were not available through TQL.
To begin we can now SELECT individual columns from our containers or SELECT DISTINCT column values as well.

Selecting Data

-- Exclude the 'liveimage' (type BLOB / BYTEA) column from our SELECT query
SELECT weatherstationid FROM jp_prefecture_readings ORDER BY weatherstationid ASC;
-- Find all the unique temperature values from our timestamp measurements
SELECT DISTINCT temperature FROM jp_prefecture_readings ORDER BY temperature ASC;

Sample Output:

        name         | latitude | longitude
---------------------+----------+-----------
 Hokkaido-Sapporo    | 43.06417 | 141.34694
 Aomori-Aomori       |    48.83 |    140.75
 Iwate-Morioka       | 39.70361 |  141.1525
-- (snip)
 temperature
-------------
          50
        55.6
          70
          80
(4 rows)

Modifying Data

We can also modify data our with SQL queries which includes INSERT, UPDATE, and DELETE statements.

-- Insert Data into GridDB Containers
INSERT INTO jp_prefecture (id,name,latitude,longitude,hascamera)
  VALUES ('weather_station_48','Tsukishima',35.662,139.776,false);
INSERT INTO jp_prefecture_readings (timestamp, weatherstationid, temperature, liveimage)
  VALUES('2018-05-20 12:35:55.789','weather_station_0',55.6,bytea('\x533435363741'));
-- (snip)
-- Update Queries
UPDATE water_quality_ts_3 SET water_temperature = 18.5 WHERE beach_name LIKE '%met Beach';
UPDATE jp_prefecture SET latitude = 48.83, longitude = 140.75 WHERE id = 'weather_station_2' AND name='Aomori-Aomori';
-- (snip)
-- Delete Data from GridDB Containers
DELETE FROM jp_prefecture WHERE name='Tsukishima';
DELETE FROM jp_prefecture_readings
  WHERE weatherstationid LIKE '%0' AND temperature > 50.0 AND temperature <= 60.0;
-- Delete all rows from a GridDB Container
DELETE FROM jp_prefecture;

Aggregating and Grouping Data

We can also aggregate and categorize our GridDB to a deeper level with GROUP BY clauses.

-- Aggregation Operations coupled with Group By Operations
-- Determine and display the average temperature every unique weather station id
-- The 'weatherstationid' could represent an individual weather or climate sensor
SELECT weatherstationid, AVG(temperature) AS average_temp FROM jp_prefecture_readings
  GROUP BY weatherstationid ORDER BY average_temp ASC;

Sample Output:

  weatherstationid  |   average_temp
--------------------+------------------
 weather_station_0  |             55.6
 weather_station_44 |               60
 weather_station_12 | 63.3333333333333
 weather_station_32 |               64
 weather_station_26 |               64
 weather_station_38 |               64
 weather_station_20 |               64
 weather_station_22 | 66.6666666666667
 weather_station_36 | 66.6666666666667
 weather_station_6  | 66.6666666666667
 weather_station_24 | 67.1428571428571
 weather_station_4  | 67.1428571428571
 weather_station_28 | 67.1428571428571
 weather_station_16 | 67.1428571428571
 weather_station_43 |             67.5
 weather_station_23 |             67.5
 weather_station_15 |             67.5
 weather_station_27 |             67.5
 weather_station_17 |             67.5

Merging Tables

We can also merge tables from multiple containers by selecting from multiple foreign tables or using UNION queries. It is also possible to merge data from containers that share related column values with JOIN operations.

-- Selecting from multiple tables
SELECT jp.id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp, jp_prefecture_readings jpr WHERE jp.id = 'weather_station_18'
  ORDER BY jpr.timestamp DESC;
-- Join Operations
SELECT jp.id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp INNER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;
SELECT jp.id AS left_id, jpr.weatherstationid AS right_id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp LEFT OUTER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;
SELECT jp.id AS left_id, jpr.weatherstationid AS right_id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp RIGHT OUTER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;
SELECT jp.id AS left_id, jpr.weatherstationid AS right_id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp FULL OUTER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;
-- Union operations
SELECT id FROM jp_prefecture
  UNION
SELECT weatherstationid FROM jp_prefecture_readings;
SELECT id FROM jp_prefecture
  UNION ALL
SELECT weatherstationid FROM jp_prefecture_readings;

Conclusion

GridDB's Foreign Data Wrapper for PostgreSQL can give applications a simple and quick way to access all your GridDB data from PostgreSQL. Rather than having to go through the lengthy process of migrating a whole GridDB database to PostgreSQL, simply use the GridDB's foreign data wrapper to convert the GridDB containers from the GridDB cluster into PostgreSQL tables in a way that is seamless and automatic.
This new component will make incorporating GridDB data into legacy applications that still use SQL interfaces much easier. With GridDB's foreign data wrapper for PostgreSQL, you can now have both the fast and scalable performance of GridDB along with the additional data management tools that SQL systems and queries provide.

Reference

• GridDB Community Edition 3.0.1 was used as the GridDB database and server for this post.

• The GridDB C Client used for this post was the open-source version for Community Edition, whose code is available for download at this here and documentation can be found in this section of the GridDB API reference.

• Both the GridDB C client the GridDB foreign data wrapper were built using gcc version 4.8.5

• All the queries and sample code on this post were performed on a CentOS 7.3 operating system

• PostgreSQL version 9.6.9 was used for this post and was built by installing it directly from source code.

• More details on PostgreSQL foreign data wrappers can be found on this PostgreSQL wiki page

• If you wish to learn more about migrating relational or SQL databases to GridDB, feel free to check out our posts on MySQL to GridDB or PostgreSQL to GridDB database migrations or the documentation on using import and export functions with GridDB Standard Edition.

• GridDB also provides JDBC and ODBC connectors which can also be used for interfacing GridDB with SQL queries and databases.

• More details on understanding how to interact with foreign servers in PostgreSQL can be found from this thoughtbot blog post.

Source Code

All the source code used in this post can be downloaded from the link below.

• [download id="25142"]

If you have any questions about the blog, please create a Stack Overflow post here https://stackoverflow.com/questions/ask?tags=griddb .
Make sure that you use the “griddb” tag so our engineers can quickly reply to your questions.