Vertica Driver

[codybakken]

Hi, can you add Vertica as a connection? I tried to use the blog post instructions to implementing a driver for Vertica but can't seem to make it work.

[klausb]

@codybakken, I played around w/ Cube a while ago and wrote my own Vertica driver for cube. It uses node-vertica. I think I copied parts from the built-in Postgres driver. Below you need to fill in your schema name:

const vertica = require('vertica');
const SqlString = require('sqlstring');
const BaseDriver = require('@cubejs-backend/query-orchestrator/driver/BaseDriver');

const { zipObj, prop } = require('ramda');

const applyParams = (query, params) => {
    return SqlString.format(query, params);
};

class VerticaDriver extends BaseDriver {

  constructor(config) {
    super();
    this.config = config || {};
    this.verticaConnection = vertica.connect({
      host: process.env.CUBEJS_DB_HOST,
      database: process.env.CUBEJS_DB_NAME,
      port: process.env.CUBEJS_DB_PORT,
      user: process.env.CUBEJS_DB_USER,
      password: process.env.CUBEJS_DB_PASS,
      ssl: false
    }, err => {
      if (err) {
        console.log(`Cannot connect to vertica: ${err}`);
      }
    });
    // this.verticaConnection.debug = true;
  }

  async query(query, values) {
    let qstr = applyParams(query, values);
    // qstr = qstr.replace(/'{/g, '');
    // qstr = qstr.replace(/}'/g, '');
    // qstr = qstr.replace(/["]/g, () => '\'');
    console.log(qstr);
    return await this.queryResponse(qstr);
  }

  queryResponse(query) {
      return new Promise((resolve, reject) => {
        const res = this.verticaConnection.query(query, (err, rs) => {
            if (err) {
              reject(err);
            } else {
              resolve(rs.rows.map(row => zipObj(rs.fields.map(prop('name')), row)));
            }
        });
    });
  }


  async testConnection() {
    try {
      return await this.query('SELECT ?::int AS number', ['1']);
    } catch (e) {
      throw e;
    }
  }

    informationSchemaQuery() {
      return `select column_name,
             table_name, 
             table_schema,
             data_type
       from columns
       where table_schema = '<your-schema>'
       order by table_schema, table_name`;
    }
   
    downloadQueryResults() {
      throw new Error('Not implemented');
    }
  
    readOnly() {
      return true;
    }

    /*
    tablesSchema() {
      const query = this.informationSchemaQuery();
  
      const reduceCb = (result, i) => {
        let schema = (result[i.table_schema] || {});
        let tables = (schema[i.table_name] || []);
  
        tables.push({ name: i.column_name, type: i.data_type, attributes: i.key_type ? ['primaryKey'] : [] });
  
        tables.sort();
        schema[i.table_name] = tables;
        schema = sortByKeys(schema);
        result[i.table_schema] = schema;
  
        return sortByKeys(result);
      };
  
      return this.query(query).then(data => reduce(reduceCb, {}, data));
    }
    */
  
    createSchemaIfNotExists(schemaName) {
        console.log('createSchemaIfNotExists not supported');
        return {};
    }
  
    getTablesQuery(schemaName) {
      return this.query(
        `SELECT table_name FROM tables WHERE table_schema = ${this.param(0)}`,
        [schemaName]
      );
    }
  
    loadPreAggregationIntoTable(preAggregationTableName, loadSql, params, tx) {
      return this.query(loadSql, params, tx);
    }
  
    dropTable(tableName, tx) {
      console.log('dropTable not supported');
      return {};
    }
  
    param(/* paramIndex */) {
      return '?';
    }
  
    testConnectionTimeout() {
      return 10000;
    }
  
    async downloadTable(table) {
      return { rows: await this.query(`SELECT * FROM ${table}`) };
    }
  
    async uploadTable(table, columns, tableData) {
        console.log('uploadTable not supported');
    }
  
    toColumnValue(value, genericType) {
      return value;
    }
  
    async tableColumnTypes(table) {
      const [schema, name] = table.split('.');
      const columns = await this.query(
        `select column_name,
            table_name, 
            table_schema,
            data_type
        from columns
        where table_schema = '<your-schema>' AND table_name = ${this.param(0)} AND table_schema = ${this.param(1)}`,
        [name, schema]
      );
      return columns.map(c => ({ name: c.column_name, type: this.toGenericType(c.data_type) }));
    }
  
    createTable(quotedTableName, columns) {
      return this.query(this.createTableSql(quotedTableName, columns), []);
    }
  
    createTableSql(quotedTableName, columns) {
      columns = columns.map(c => `${this.quoteIdentifier(c.name)} ${this.fromGenericType(c.type)}`);
      return `CREATE TABLE ${quotedTableName} (${columns.join(', ')})`;
    }
  
    toGenericType(columnType) {
      return DbTypeToGenericType[columnType.toLowerCase()] || columnType;
    }
  
    fromGenericType(columnType) {
      return columnType;
    }
  
    quoteIdentifier(identifier) {
      return `"${identifier}"`;
    }  
}

module.exports = VerticaDriver;

In the server you refer to the driver like so:

const server = new CubejsServer({
  driverFactory: ({ dataSource }) => new VerticaDriver({ database: dataSource })
});

[igorlukanin]

How to contribute a driver: https://github.com/cube-js/cube.js/blob/master/CONTRIBUTING.md#implementing-driver

[Korg95]

Hi @klausb thanks for posting about this. I'm curious to implement my own Vertica driver as well and the fact you have attempted to do so gives me some confidence about giving it a go.

Are there any particular gotchas that you experienced that you would care to share?