cube.js dremio driver基于补偿机制提升查询速度

默认cube.js 的dremio driver 在设计的时候，为了进行状态处理的请求使用了循环处理，同时添加了一个1s的延迟处理

原始参考代码

async query(query, values) {

    const queryString = applyParams(

      query,

      (values || []).map(s => (typeof s === 'string' ? {

        toSqlString: () => SqlString.escape(s).replace(/\\\\\\([_%])/g, '\\$1').replace(/\\'/g, '\'\'')

      } : s))

    );

 

    await this.getToken();

    const jobId = await this.executeQuery(queryString);

 

    for (;;) {

      const { data } = await this.getJobStatus(jobId);

 

      console.log(data.jobState, jobId);

 

      if (data.jobState === 'FAILED') {

        throw new Error(data.errorMessage);

      } else if (data.jobState === 'CANCELED') {

        throw new Error(`Job ${jobId} has been canceled`);

      } else if (data.jobState === 'COMPLETED') {

        let rows = [];

        const querys = [];

 

        for (let i = 0; i < data.rowCount; i += dremioJobLimit) {

          querys.push(this.getJobResults(jobId, dremioJobLimit, i));

        }

 

        const parts = await Promise.all(querys);

        parts.forEach((e) => {

          rows = rows.concat(e.data.rows);

        });

 

        return rows;

      }

     //   此处延迟1s执行，真是因为这个，所以会发现dremio 的查询都比较慢

      await this.sleep(1000);

    }

  }

解决方法

基于线性补偿机制，参考了bigquery driver

async query(query, values) {

    const queryString = applyParams(

      query,

      (values || []).map(s => (typeof s === 'string' ? {

        toSqlString: () => SqlString.escape(s).replace(/\\\\([_%])/g, '\\$1').replace(/\\'/g, '\'\'')

      } : s))

    );

 

    await this.getToken();

    const jobId = await this.executeQuery(queryString);

 

    // do some query like bigquery links  https://github.com/cube-js/cube.js/blob/master/packages/cubejs-bigquery-driver/driver/BigQueryDriver.js#L224

 

    const startedTime = Date.now();

 

    for (let i = 0; Date.now() - startedTime <= this.config.pollTimeout; i++) {

      const { data } = await this.getJobStatus(jobId);

      console.log(data.jobState, jobId);

      if (data.jobState === 'FAILED') {

        throw new Error(data.errorMessage);

      } else if (data.jobState === 'CANCELED') {

        throw new Error(`Job ${jobId} has been canceled`);

      } else if (data.jobState === 'COMPLETED') {

        let rows = [];

        const querys = [];

 

        for (let j = 0; j < data.rowCount; j += dremioJobLimit) {

          querys.push(this.getJobResults(jobId, dremioJobLimit, j));

        }

 

        const parts = await Promise.all(querys);

        parts.forEach((e) => {

          rows = rows.concat(e.data.rows);

        });

 

        return rows;

      }

 

      await this.sleep(

        Math.min(this.config.pollMaxInterval, 200 * i),

      );

    }

  }

说明

经过以上的调整cube.js 与dremio 的查询结合dremio 的数据反射能力，基本都可以在1s内响应了

参考资料

https://github.com/cube-js/cube.js/blob/master/CONTRIBUTING.md
https://github.com/cube-js/cube.js/pull/2475
https://github.com/cube-js/cube.js/blob/master/packages/cubejs-bigquery-driver/driver/BigQueryDriver.js#L60