Comment passer un paramètre dans une requête bigquery sur colab

Question

J'ai une requête Bigquery sur colab :

from google.colab import auth
auth.authenticate_user()
print('Authenticated')
project_id = '[your project ID]'

sample_count = 2000
df = pd.io.gbq.read_gbq('''
  SELECT name, SUM(number) as count
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE state = 'TX'
  AND year BETWEEN 1910 AND 1920
  GROUP BY name
  ORDER BY count DESC
  LIMIT 100
''', project_id=project_id, dialect='standard')

df.head()

Cela fonctionne, mais j'essaie maintenant de passer un paramètre dans la requête et de remplacer '1920' dans la clause WHERE de la requête. Ce paramètre dépend d'un autre fichier.

end_year = max(record.year) # set end_year

df = pd.io.gbq.read_gbq('''
  SELECT name, SUM(number) as count
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE state = 'TX'
  AND year BETWEEN 1910 AND end_year
  GROUP BY name
  ORDER BY count DESC
  LIMIT 100
''', project_id=project_id, dialect='standard')

df.head()

Mais je reçois une erreur :

BadRequest: 400 Syntax error: Unexpected identifier "end_year"

Je suppose que le paramètre ne passe pas dans la requête avec succès, mais je ne sais pas comment le réparer.

Answer 1

Pour python, la requête que vous transmettez à bigquery est une chaîne de caractères, et il ne peut donc pas appeler votre variable.

Comme ça :

end_year = max(record.year) # set end_year

df = pd.io.gbq.read_gbq('''
  SELECT name, SUM(number) as count
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE state = 'TX'
  AND year BETWEEN 1910 AND %s
  GROUP BY name
  ORDER BY count DESC
  LIMIT 100
''' % (end_year), project_id=project_id, dialect='standard')

df.head()

Remarque très importante : je suppose qu'il s'agit d'un script que vous exécutez personnellement pour une analyse de données ponctuelle et non d'un code utilisé dans une application de production où l'injection SQL pourrait poser problème.

Answer 2

Comme @Mike Karp l'a mentionné, la requête dans votre code est un String, c'est pourquoi vous rencontrez une erreur lorsque vous passez votre variable directement à la requête.

Vous pouvez également utiliser la chaîne f de python pour formater votre chaîne et pouvoir passer la variable dans votre requête.

from google.colab import auth
import pandas as pd
auth.authenticate_user()
print('Authenticated')
project_id = 'PROJECT_ID'

end_year = max(record.year) # set end_year

query = (f" SELECT name, SUM(number) as count \
  FROM `bigquery-public-data.usa_names.usa_1910_2013` \
  WHERE state = 'TX' \
  AND year BETWEEN 1910 AND {end_year} \
   GROUP BY name \
  ORDER BY count DESC \
  LIMIT 100")

df = pd.io.gbq.read_gbq(query=query, project_id=project_id, dialect='standard')

df.head()