Deepgreen DB

Xdrive JDBC Plugin

The JDBC Plugin allows Deepgreen DB to read/write tables of another database.

Mountpoint

[[xdrive.mount]]
name = "myotherdb"
argv = ["/usr/bin/java", 
         "-classpath", "YOUR-JDBC-JAR:jars/vitessedata-db-plugin.jar", 
         "com.vitessedata.xdrive.jdbc.Main"]
env = ["CONNECTION_STRING=YOUR-JDBC-CONNECTION-STRING"]

For PostgreSQL database:

  • replace YOUR-JDBC-JAR above with jars/postgresql-42.2.1.jar

  • replace YOUR-JDBC-CONNECTION-STRING with a string like this: jdbc:postgresql://dbhost:port/myotherdb?user=scott&password=tiger&ssl=true. For details on postgres connection string, please refer to this document.

DDL

When constructing an external table DDL that refers to a JDBC Plugin, specify the LOCATION clause like this:

LOCATION('xdrive://XDRIVE-HOST-PORT/MOUNTPOINT/TABLENAME')
FORMAT 'SPQ'

For example, to read/write the nation table on another database through the JDBC Plugin, we may write the DDL like this:

DROP EXTERNAL TABLE IF EXISTS nation;
CREATE EXTERNAL TABLE nation (
    n_nationkey int,
    n_name text,
    n_regionkey int,
    n_comment text) LOCATION('xdrive://XDRIVE-HOST-PORT/myotherdb/nation') 
FORMAT 'SPQ'; 


DROP EXTERNAL TABLE IF EXISTS w_nation;
CREATE WRITABLE EXTERNAL TABLE w_nation (
    n_nationkey int,
    n_name text,
    n_regionkey int,
    n_comment text) LOCATION('xdrive://XDRIVE-HOST-PORT/myotherdb/nation') 
FORMAT 'SPQ';

Note that:

  • Our mountpoint name is myotherdb.

  • Our table name on the source or destination database is nation.

  • FORMAT 'SPQ' must be specified as the JDBC Plugin expects this format.