CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerydynamicquerygenerative-programming
VIJU
VIJU

Reputation: 176

Is there a way to generate a bigquery query on the fly or Dynamically, based on an list of criteria for the where condition?

I'm Trying to generate a query in big query dynamically based on different conditions in the "Where" condition of the query.

so for instance if I receive a list of columns and their corresponding values (a=1,b=2,c=3,d=null,e=null), then my query needs to be,

SELECT col1, col2 from TABLE_NAME WHERE a=1 and b=2 and c=3

I'm receiving these columns and their values from a UI/application, so I need to be generating these queries dynamically.

Is there a way to do this?

TIA

Upvotes: 0

Views: 322

Answers (1)

Kolban
Kolban

Reputation: 15276

BigQuery provides a statement called EXECUTE IMMEDIATE that takes a string as input that represents a SQL statement to execute. The SQL is parsed and then executed. This means you can build your SQL statement for execution dynamically (on the fly) given your input parameters. Be very careful here to consider SQL injection vulnerabilities and code defensively, especially if the SQL to run comes from an end user UI input. See also this Blog article:

How to use Dynamic SQL in BigQuery

Upvotes: 1

Related Questions