Datalab to BigQuery - Inserting variable values into SQL

Question

I am trying to use the Google Datalab - BigQuery magic command to insert data into a BigQuery table via the datalab notebook. When I run this code it works fine with the data appearing in the table correctly;

INSERT mydataset.sampletable (word, count)
VALUES('testing', 7)

However, I would like to have the string 'testing' and number 7 in their own variables, which I would then insert into BQ. I have set:

test = 'testing'
size = 7

and then I try to run

%%bq query
INSERT mydataset.sampletable (word, count)
VALUES (test, size)

This results in an error message: "invalidQuery: Unrecognized name: test at [2:9]" How can I go about utilizing my variables within an SQL function?

Thanks,

Graham Polley Graham Polley · Accepted Answer · 2017-12-18T05:22:44

You need to use the correction syntax/notation. The way you are doing it is just issuing a straight SQL command to BigQuery i.e. it knows nothing about those parameters you are trying to set/use. I tested and the following works as expected (you need to split it into 2 Datalab commands):

%%bq query -n params_test
INSERT `grey-sort-challenge.dataflow_on_a_tram.melbourne_titles` (year,month,day,wikimedia_project,language,title,views)
VALUES(2017,1,1,'wp','en',@title,100)

%%bq execute -q params_test
parameters:
- name: title
  type: STRING
  value: the_dude_abides_in_melbourne

Results (I ran it 4 times in Datalab):

See here and here.

Datalab to BigQuery - Inserting variable values into SQL

1 Answers