Skip to content

Latest commit

 

History

History
94 lines (69 loc) · 2.79 KB

README.md

File metadata and controls

94 lines (69 loc) · 2.79 KB

PGVertica

PGVertica is a proxy server designed to be an interface to connect from BI tools to Vertica using PostgreSQL connectors.

This project utilizes the PostgreSQL wire protocol to direct data-centric queries towards a Vertica database while managing all other queries, such as metadata retrievals from pg_catalog, INSERT operations to temporary tables, and others, using a local PostgreSQL instance with recreated Vertica schemas metadata.

This project is built on top of the existing PostgreSQL-sqlite proxy, postlite. For interfacing with Vertica, we use the github.com/vertica/vertica-sql-go library, while the PostgreSQL protocol implementation is taken care of by the github.com/jackc/pgproto3/v2 library.

Supported features:

  • TLS encryption
  • automatic synchronization of selected schemas metadata, between Vertica and Postgres
  • automatic synchronization of users and roles between Vertica and Postgres
  • extensive support for prepared statements
  • handling multiple datatypes using TEXT/BINARY protocol
  • fetching data forward with explicitly declared cursors

Note, that this proxy does not support

  • queries that add data to Vertica - all changes will only be reflected in the local postgres instance
  • SELECT queries which uses PostgreSQL specific syntax, which is not supported by Vertica dialect.

Supported clients

This proxy has been tested with following BI tools:

  • DBeaver
  • Tableau
  • PowerBI
  • Excel
  • Apache Superset
  • ArcGIS

Usage

Build PGVertica

go build -o pgvertica ./cmd/pgvertica/main.go

Run tests

go test -v

Run PGVertica

./pgvertica
Usage of ./pgvertica:
  -addr string
        proxy server bind address (default ":5432")
  -log-level string
        logger level (default "INFO")
  -pgconn string
        Postgres connection string
  -require-password
        whether this proxy should ask for password
  -schemas-sync-interval-s int
        time interval between schemas synchronization (default 60)
  -vconn string
        Vertica connection string in format vertica://user:password@addr:port/db
  -x509-cert-path string
        Path to SSL x509 cert file, if empty proxy won't support SSL

To build proxy and run docker with postgres you can use. Script will fill only --pgconn parameter, you need to pass the rest of parameters, eg.:

./start-proxy.sh --vconn "vertica://vuser:vpass@vertica-host:5433/dbname" --require-password

Contribution Policy

PGVertica is open to code contributions for bug fixes & documentation fixes only. Features carry a long-term maintenance burden so they will not be accepted at this time. Please submit an issue if you have a feature you'd like to request.