cube-js / cube-js/cube

Bigquery EXTERNAL_QUERY filters: support unsafeValue for FILTER_PARAMS

Open
#3,583 13 comments 5 reactions 0 assignees View on GitHub
backend:server help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**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](https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries).

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**
```javascript
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**

```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**:
```sql
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?

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.