CnosDB FDW: A Door to PostgreSQL

This document provides brief instructions for downloading, installing, and using CnosDB FDW. Please adjust the document according to your actual needs and environment.

Overview

CnosDB FDW is a Foreign Data Wrapper for accessing CnosDB database in PostgreSQL database. It provides the functionality to query CnosDB data in PostgreSQL.

This document will guide you in downloading, installing and using CnosDB FDW.

Download

Method One

git clone git@github.com:cnosdb/wrappers.git
cd wrappers/wrappers

CnosDB FDW depends on pgrx. You need to install pgrx before installation. You can refer to the official warehouse readme [https://github.com/pgcentralfoundation/pgrx#system-requirements] . 

After pgrx is installed, run the following command:

cargo pgrx install --pg-config [path_to_pg_config] --features cnosdb_fdw

Method Two

release [ https://github.com/cnosdb/wrappers/releases ] Download the dynamic library corresponding to the system and place it in the corresponding directory of Postgres

$PATH_TO_PG/share/extension/wrappers.control

$PATH_TO_PG/lib/wrappers-0.1.18.so

$PATH_TO_PG/share/extension/wrappers–0.1.18.sql

Example

Prepare CnosDB Data

Start CnosDB server and client, enter the following SQL in the client:

CREATE TABLE air (
      visibility DOUBLE,
      temperature DOUBLE,
      pressure DOUBLE,
      TAGS(station)
);

INSERT INTO air (time, station, visibility, temperature, pressure) VALUES('2023-01-01 01:10:00', 'XiaoMaiDao', 79, 80, 63);

INSERT INTO air (time, station, visibility, temperature, pressure) VALUES('2023-01-01 01:20:00', 'XiaoMai', 80, 60, 62);

INSERT INTO air (time, station, visibility, temperature, pressure) VALUES('2023-01-01 01:30:00', 'Xiao', 81, 70, 61);

Execute Query SQL:

SELECT * FROM air;

public ❯ select * from air;
+---------------------+------------+------------+-------------+----------+
| time                | station    | visibility | temperature | pressure |
+---------------------+------------+------------+-------------+----------+
| 2023-01-01T01:10:00 | XiaoMaiDao | 79.0       | 80.0        | 63.0     |
| 2023-01-01T01:20:00 | XiaoMai    | 80.0       | 60.0        | 62.0     |
| 2023-01-01T01:30:00 | Xiao       | 81.0       | 70.0        | 61.0     |
+---------------------+------------+------------+-------------+----------+

 

Query CnosDB Data Using Postgres

Create CnosDB External Table

-- create extension
create extension wrappers;

SELECT * FROM pg_extension;

-- create foreign data wrapper and enable 'CnosdbFdw'
CREATE FOREIGN DATA WRAPPER cnosdb_wrapper
HANDLER cnosdb_fdw_handler
VALIDATOR cnosdb_fdw_validator;

SELECT * FROM pg_foreign_data_wrapper;

-- create server and specify custom options
CREATE SERVER my_cnosdb_server
FOREIGN DATA WRAPPER cnosdb_wrapper
OPTIONS (
    url 'http://localhost:8904',
    username 'root',
    password '',
    tenant 'cnosdb',
    db 'public'
);

-- create an example foreign table
CREATE FOREIGN TABLE air(
    time timestamp,
    station text,
    visibility double precision,
    temperature double precision,
    pressure double precision
)
SERVER my_cnosdb_server
OPTIONS (
    table 'air'
);

Query CnosDB Data

SELECT * FROM air;

postgres=# select * from air;
        time         |  station   | visibility | temperature | pressure
---------------------+------------+------------+-------------+----------
 2023-01-01 01:30:00 | Xiao       |         81 |          70 |       61
 2023-01-01 01:20:00 | XiaoMai    |         80 |          60 |       62
 2023-01-01 01:10:00 | XiaoMaiDao |         79 |          80 |       63
(3 rows)

Precautions

• Before using CnosDB FDW, make sure that the correct version of the CnosDB database is installed and configured.
• Currently only PostgreSQL v14, v15 and v16 are supported.
• When creating an external server, modify the URL according to the actual situation.
• When creating an external table, modify the table name and column definitions according to the actual situation.
• CnosDB FDW supports common query operations, but some advanced functions (such as spatiotemporal functions, transactions) may be limited.
• There are limitations in the code implementation of CnosDB FDW, and query performance and memory usage will be affected.
• Please refer to the official documentation of CnosDB FDW for more details and examples.

In conclusion

CnosDB FDW allows you to easily access and manipulate CnosDB databases in PostgreSQL. This document provides brief instructions for downloading, installing, and using CnosDB FDW. Please adjust the document according to your actual needs and environment.

If you need more help or have other questions, please refer to the official documentation of CnosDB FDW or contact the CnosDB support team.

Hope this sample document is helpful! If you have additional questions, please feel free to ask.