Why you need it

The extension provides with an ability to access the Cassandra data using PostgreSQL.

How it works

The extension allows a user to manage the data of other database (Cassandra): read, write, delete, update.


cassandra-fdw

PostgreSQL Foreign Data Wrapper for Cassandra


Requirements

  • PostgreSQL 9.3+
  • PostgreSQL development packages (postgresql-server-dev-9.x)
  • Cassandra 2.1+, 2.2+, 3+


Features

  • Foreign schema import
  • Full CQL types support
  • CQL query optimizations


How to install


install Multicorn

pgxn install multicorn


install Cassandra driver and modules

pip install cassandra-driver
pip install pytz


clone repository

git clone https://github.com/rankactive/cassandra-fdw.git


install FDW

cd cassandra-fdw
python setup.py install


Usage

-- Create test database
CREATE DATABASE fdw_test;

Switch to database fdw_test

-- Create extension for database
CREATE EXTENSION multicorn;
-- Create server
CREATE SERVER fdw_server FOREIGN DATA WRAPPER multicorn
OPTIONS (
wrapper 'cassandra-fdw.CassandraFDW',
hosts '10.10.10.1,10.10.10.2',
port '9042',
username 'cassandra user', -- optional
password 'cassandra password' -- optional
);
-- Create foreign table
CREATE FOREIGN TABLE fdw_table
(
col1 text,
col2 int,
col3 bigint
) SERVER fdw_server OPTIONS (keyspace 'cassandra keyspace', columnfamily 'cassandra columnfamily');

Use it!

SELECT * FROM fdw_table WHERE col1 = 'some text';
INSERT INTO fdw_table VALUES ('text', 123, 1234);

By default, the concurrency level of modifications is 4. It means that batch modifications will be sent in 4 threads to Cassandra. To change it use:

ALTER SERVER fdw_srv OPTIONS (modify_concurency 'your integer value');

Or if it has been set before, use:

ALTER SERVER fdw_srv OPTIONS (SET modify_concurency 'your integer value');

If you want to use updates and deletes, you must create column named "__rowid__" with type "TEXT"

Import foreign schema example:

CREATE SCHEMA fdw_test;
IMPORT FOREIGN SCHEMA cassandra_keyspace FROM SERVER fdw_server INTO fdw_test;


Types mapping

CQL typePostgreSQL type
bigintbigint
blobbytea
booleanboolean
counterbigint
datedate
decimaldecimal
doublefloat8
floatfloat4
inetinet
intint
list<type>type[]
map<type, type>json
set<type>type[]
smallintsmallint
texttext
timetimetz
timestamptimestamptz
timeuuiduuid
tinyintsmallint
tuple<type,type,...>json
uuiduuid
varintint
mautic is open source marketing automation