How to Set Named Parameter on Bigquery Query

how to set named parameter on bigquery query

Named parameters are supported in BigQuery only through the API using standard SQL, not the web UI. You can read about them in the section on Running parameterized queries. If you are interested in web UI support for query parameters, you can star the feature request on the issue tracker.

Google Bigquery - Running parameterized queries - php

I don't have a project set up to try this out, so I apologize if there are syntax errors or other oversights, but please see if this works. I based this on the PHP API in Github. You will need to make sure to use standard SQL for your query rather than legacy SQL.

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);

$query = "SELECT COUNT(DISTINCT word) AS distinct_words
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus_name;";

$queryResults = $bigQuery->runQuery(
    $query,
    ['useLegacySql' => false],
    ['queryParameter' => new QueryParameter([
       'name' => 'corpus_name',
       'parameterType' => new QueryParameterType([
         'type' => 'STRING',
       ]),
       'parameterValue' => new QueryParameterValue([
         'value' => 'kingrichardii',
       ]),
     ],
);

Can I use a query parameter in a table name?

Yes, you can, here's a working example:

DECLARE tablename STRING;
DECLARE tableQuery STRING;

##get list of tables
CREATE TEMP TABLE tableNames as select table_name from nomo_nausea.INFORMATION_SCHEMA.TABLES where table_name not in ('_sdc_primary_keys', '_sdc_rejected', 'fba_all_order_report_data');

WHILE (select count(*) from tableNames) >= 1 DO
  SET tablename = (select table_name from tableNames LIMIT 1);
  ##build dataset + table name
  SET tableQuery = CONCAT('nomo_nausea.' , tablename);
  ##use concat to build string and execute
  EXECUTE IMMEDIATE CONCAT('SELECT * from `', tableQuery, '` where _sdc_deleted_at is not null');
  DELETE FROM tableNames where table_name = tablename;
END WHILE;

Pass parameters by name to BigQuery stored procedure or function

You were very close (I think) - Use below

CREATE TEMP FUNCTION FullName(name ANY TYPE) 
RETURNS STRING 
AS(
  name.firstName || ' ' || name.lastName
);
SELECT 
  FullName(STRUCT("Fred" AS firstName, "Flintstone" AS lastName)),
  FullName(STRUCT("Flintstone" AS lastName, "Fred" AS firstName))    

with output

How to run a fuzzy LIKE in BigQuery with a Query Parameter

It is possible to use the wildcards around string queries using parameters.
The following is based on the documentation link you sent and a public data set.

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT corpus, word
    FROM `bigquery-public-data.samples.shakespeare`
    WHERE corpus like @corpus
    ORDER BY word_count DESC;
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("corpus", "STRING", "%romeo%")
    ]
)

query_job = client.query(query, job_config=job_config)  # Make an API request

for row in query_job:
    print(f'{row.corpus}: {row.word}')

If you run the following you should get the results associated with a wildcard search of '%romeo%' as you would expect. Additionally if you change the parameter to '%romeo' you can see that it will not return rows as the data doesn't have data that matches.

I've looked over your code though and it seems like it should be working.

Error with parametrized query in Google BigQuery

You can only use parameters in place of expressions, such as column_name = @param_value in a WHERE clause. A table name is not an expression, so you cannot use parameters in place of the project or dataset names. Note also that you need to use standard SQL in order to use parameters.

---

Related Topics