Bigquery EXTERNAL_QUERY filters: support unsafeValue for FILTER_PARAMS

Problem

First of all, thank you for making Cube.js, it is a powerful and useful tool!

We are using Google BigQuery, PostgreSQL on Google Cloud SQL and Cube.js. We have a pre-aggregated BigQuery table that Cube will query on. This table is however missing some information that we are getting from a PostgreSQL table using a federated query.

To be more precise, in our case we have a website name in the BigQuery table, and the country in which this website is hosted in the PostgreSQL table, so we join to get both information in the same cube. All sites are associated with an organization (organization_id column). We are always filtering by organization.

To avoid querying the entire PostgreSQL table and to take advantage of indexes, we would like to apply the cube filters to the external query as well, but there are some problems with this approach:

• Using FILTER_PARAMS, Cube generates a positional parameter, but the EXTERNAL_QUERY requires either a positional parameter that represents the entire external query or a string literal.
• There is no way (as far as I know) to get the real filter value from FILTER_PARAMS so the generated SQL already contains the filter value instead of being parameterized.
• Using SECURITY_CONTEXT and unsafeValue() does not look like a solution in our case because we would have to generate a new JWT for each Cube request, as the organization_id can vary, or there can even be many organization_ids.

Related Cube.js schema

cube(`BigQueryTable`, {
  sql: `
    SELECT *, _PARTITIONTIME FROM database.bigquery_table
    LEFT OUTER JOIN
      EXTERNAL_QUERY('connection_id',
        """SELECT name, countryIso AS country FROM sites WHERE ${FILTER_PARAMS.BigQueryTable.organizationId.filter('organization_id')}""") AS rq
      ON
        rq.name = bigquery_table.site
    `,

  measures: {
    //...
  },

  dimensions: {
    organizationId: {
      sql: `organization_id`,
      type: `string`
    },

    site: {
      sql: `site`,
      type: `string`
    },

    country: {
      sql: `country`,
      type: `string`
    },

    //...

    partitionTime: {
      sql: `_PARTITIONTIME`,
      type: `time`
    }
  },

  dataSource: `default`
});

Related Cube.js generated SQL

SELECT
  /* ... */
FROM (
  SELECT
    *,
    _PARTITIONTIME
  FROM
    database.bigquery_table
  LEFT OUTER JOIN
    EXTERNAL_QUERY('connection_id',
      """SELECT name, countryIso AS country from sites WHERE organization_id = ?""") AS rq
  ON
    rq.name = bigquery_table.site ) AS `bigquery_table`
WHERE
  /* ... */
  AND (`bigquery_table`._PARTITIONTIME >= TIMESTAMP(?)
    AND `bigquery_table`._PARTITIONTIME <= TIMESTAMP(?))
  AND (`bigquery_table`.organization_id = ?)
GROUP BY
  1,
  2,
  3
ORDER BY
  2 ASC
LIMIT
  50000

What I need:

EXTERNAL_QUERY('connection_id', ?) AS rq

With a param value as : """SELECT name, countryIso AS country from sites WHERE organization_id = 1234"""

Or the same query that is already generated, but without parameterizing the organization_id in the external query.

Do you think it is possible?