Application Integration - BigQuery - Dynamic Query

[padmanabhamv]

https://cloud.google.com/integration-connectors/docs/connectors/bigquery/configure#execute-custom-qu...

1. Is there a option to pass dynamic query to the input for the Bigquery Connector customer sql query ?

2. and also is there a possibility to set the input parameters as a optional

Usecase:

I want to generate a dynamic query depends on the input parameters and generate a query and pass it to BQ connector to get the data. .

select * from svc-edp-exchange-nonprod.sap.vw_orders_by_contacts where  OpportunityId = ? and EndUserAccountId = ? and ResellerAccountName= ? order by ? desc limit ? offset ?

select * from svc-edp-exchange-nonprod.sap.vw_orders_by_contacts where  OpportunityId = ? order by ? desc limit ? offset ?

[tejeshraut]

1. There is no option to pass a dynamic query to the input for the custom SQL query. However there is an option to pass a parameterized query where parameter values are provided by integration variables (`?` will be replaced by integration variables). This is to prevent injection of malicious user input from any input variable into the SQL.

2. You can set a default value for input variables. Let's say, if the input variable is a string, you can specify it's default value as "" (empty)

To run different SQL queries based on the user input, you can use the edge conditions to run separate BigQuery SQL tasks.

Edge condition 1: `EndUserAccountId != ""` can point to task running the query

select * from svc-edp-exchange-nonprod.sap.vw_orders_by_contacts where OpportunityId = ? and EndUserAccountId = ? and ResellerAccountName= ? order by ? desc limit ? offset ?

Edge condition 2: `EndUserAccountId == ""` can point to task running the query

select * from svc-edp-exchange-nonprod.sap.vw_orders_by_contacts where  OpportunityId = ? order by ? desc limit ? offset ?

[padmanabhamv]

thank @tejeshraut for your response.

For a static query we can able to pass param, my question is dynamic query and dynamic inputs.

[tejeshraut]

@padmanabhamv as mentioned in an earlier reply, dynamic queries are not supported. Dynamic inputs can only be passed as parameters to query (to replace `?`) and not as part of query string itself.

[melfeqy03]

@tejeshraut 
I achieved this by downloading the integration and making updates to the JSON integration file to enable dynamic queries by creating connectorInputpayload variable, as shown in the image below.

My question is: Is this restriction only enforced through the UI but allowed with manuel edits? Or is this a bug?

Is the dynamic query not supported(as you mentioned)for a specific reasons ?

[tejeshraut]

We allowed dynamic queries via backend for specific usecases.

As of now, this restriction is only enforced through the UI.

[melfeqy03]

@tejeshraut 
Is this restriction for specific reason ? and please correct me as it is allowed from the backend there is no risk to depend on it on production environments ?

[tejeshraut]

If the dynamic query is generated from some input variables, which in turn are populated by malicious input sources, they can inject into sql and affect your data visibility.

Basically, if you are bypassing the UI and using the backend's dynamic query directly, it's your responsibility to ensure the variables being used in the dynamic query are not coming from malicious sources intending to reveal your secret information.

[padmanabhamv]

Yah got it, will find a alternate integration tool for this use case.