How to use query parameters with BigQuery?

Lukas
Lukas Member
in Archive

I've been trying to create BigQuery datasets that use the "Query Parameters" field to limit my queries based on lastrundate or lastvalue of one of columns in the query.

 

The hover text for the "Query Parameters" field states:

 

Enter the query parameter value, it is the initial value for query parameter. The last run date is optional by default it is '02/01/1700' if is not provided. For example: !{lastvalue:_id}!=1,!{lastrundate:start_date}!=02/01/1944

It's not cear to me how to use parameters here in conjunction with the "fully qualified Google BigQuery query" required by the previous field. Does anyone have any examples of how this might work? The documentation for teh BigQuery connector only mentions the "Query" field and does not describe the "Query Parameters" field.

 

Thanks, 

Lukas

Comments

  • BCorless
    BCorless Domo Employee

    @Lukas

     

    Thanks for your question! The query parameters would be based on the type of SQL you are using from BigQuery. There is either a Legacy SQL (https://cloud.google.com/bigquery/docs/reference/legacy-sql) or Standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql) which can be used. Based off of which SQL you are using, that will dictate how those parameters are included within the query.

     

    More information on the connector, which includes links to the Google BigQuery SQL documentation can be found here: http://knowledge.domo.com?cid=googlebigquery.

    If I have answered your question, please click "Yes" on my comment's option.

  • Lukas
    Lukas Member

    Thanks the reply.

     

    The queries all use standard SQL. Though that does not exlain how to use the query parameters (or what would be different than legacy).

     

    As mentioned in my initial question, the documentation has zero mention of the Query Parameters field and no examples of how to use them.

     

     

  • Lukas
    Lukas Member

    I've figured out how to get it work, sort of. I'm able to use last rundate in the query and that works:

     

    WHERE updated_at > !{lastrundate:updated_at}!

    In the Query Paramter field I was able ot set the initial value using:

    !{lastrundate:updated_at}! = '2010-01-01 00:00:00'

    Setting the initial value is optional. The first time it runs it uses the intial value and the next time the job runs it uses the batch last run timestamp in the query. 

     

    What I can't seem to get to work is the lastvalue option. Using a query like:

    WHERE updated_at > !{lastvalue:updated_at}!

    and a query paramter initial value:

    !{lastvalue:updated_at}!='2010-01-01 00:00:00'

    This way does generate a runnable query. However, the where clause always uses the initial value set in the query parameter.

Categories