Is there a way to run a SELECT with external data through Apla?

[hodgesrm]

Hi,

I'm trying to encode a query that uses external data. Here's an example. 'values' are external data and must be supplied as form data.

SELECT number FROM numbers(20) WHERE number IN values

It's pretty easy to do this with node-fetch or similar request libraries. You just put the query and values_structure parameters in the URL and put the file in as form data. Here's an example using node-fetch.

// Nab required modules. 
const fetch = require('node-fetch');
const fs = require('fs');
const FormData = require('form-data');

// Pull in a file with the external values. 
const formData = new FormData();
formData.append('values', fs.createReadStream('values.tsv'));

// Build the query URL. 
query = 'SELECT number FROM numbers(20) WHERE number IN values'
values_struct = 'value Int32'
url = "http://localhost:8123?query=" + query + "&values_structure=" + values_struct;

fetch(url, {
    method: 'POST',
    body: formData
})
  .then(response => response.text())
  .then(text => console.log(text))
  .catch(err => console.log(err))

Is there a way to do this in Apla? It seems Apla wants to post the query via the request body, which conflicts with using form data for external data files. Thanks!

[bifot]

I'm using custom class for this:

const Client = require("@apla/clickhouse");

import _ from "lodash";
import SqlString from "sqlstring";

export type QueryOptions = {
  [key: string]: any;
  format?: "JSON" | "TabSeparated";
  replacements?: any[];
}

export class ClickHouse {
  public client: any;

  constructor(settings: ClickHouseSettings) {
    this.client = new Client(settings);
  }

  ...

  async query(sql: string, options?: QueryOptions | any[]) {
    let replacements = null;

    if (options && Array.isArray(options)) {
      replacements = options;
      options = undefined;
    }

    if (options && (<QueryOptions>options).replacements && Array.isArray((<QueryOptions>options).replacements)) {
      replacements = (<QueryOptions>options).replacements;
      options = _.omit(options, ["replacements"]);
    }

    if (replacements && replacements.length) {
      sql = SqlString.format(sql, replacements);
    }

    return (await this.client.querying(sql, options)).data;
  }

  ...
}

So I can write queries like this:

// simple
await ch.query(`SELECT * FROM "Users" WHERE "id" = ?`, [1]);

// advanced
await ch.query(`SELECT * FROM "Users" WHERE "id" = ?`, {
  replacements: [1],
  format: "JSON"
});