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.

[padmanabhamv]

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