The Wayback Machine - https://web.archive.org/web/20201013034447/https://github.com/cube-js/cube.js/issues/690
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Vertica Driver #690

Open
codybakken opened this issue Jun 5, 2020 · 2 comments
Open

Vertica Driver #690

codybakken opened this issue Jun 5, 2020 · 2 comments

Comments

@codybakken
Copy link

@codybakken codybakken commented Jun 5, 2020

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
Copy link

@klausb klausb commented Jul 21, 2020

@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
Copy link
Contributor

@igorlukanin igorlukanin commented Oct 12, 2020

@igorlukanin igorlukanin added the driver label Oct 12, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants
You can’t perform that action at this time.