Additional Scope (string) SQL Query

Hi!

I am trying to create a dashboard where users can make export themselves, to reduce some export work in our data department. Maybe someone can link me to an example application for this use case, would be great.

While I am trying to make this application myself, I have come across an issue.

I am using a multiselect dropdown where users can select some values so that a query is run based on the user input.

So I have tried this:

const select_query = "SELECT " + multiselect1.value + " FROM mytable";


query.trigger({
  additionalScope: {
    'select_query': select_query,
  }
});

## Other Query
{{ select_query }}

And link this to the big query resource connector. But it does not seem to work. Error is that "?" was not expected.

I tried this as well but also does not seem to work:

const select_query = multiselect1.value;


query.trigger({
  additionalScope: {
    'select_query': select_query,
  }
});

## other query
SELECT {{ select_query }} FROM mytable


Also, no luck.

Hi @Sion, this approach won't work as the data type you are passing will constantly be string literals and BQ will treat it as strings. Parameterized queries are only at filter level and BQ does not allow it on any other parts of the query. Check that limitation here.

You might want to go down the route of establishing an API connection with BQ but I wouldn't recommend that. Doing this kind of dynamic query and putting the controls to the user wouldn't be wise. Your example query does not have any filters (probs for simplicity on the example) but that would essentially pull all time data of that table. You might be able to do that in BQ console but I don't think retool can support 100k+ rows of data.

What I would do is identify what common queries/data requests they have, what their needs are, and create different apps per department. This way, you can limit of access to data they actually don't need access to (ex. Project A members not gaining access to Project B data).

Thanks @jocen! I will think of another way to set this up.

If anyone has some other forum topics or additional suggestions for this specific use case, please let me know.