%%bq query -n logs_query
SELECT * FROM `cloud-datalab-samples.httplogs.logs_20140615`

%bq sample -q logs_query --count 10

%%bq query
SELECT endpoint FROM `cloud-datalab-samples.httplogs.logs_20140615` GROUP BY endpoint

"""
Explanation: BigQuery Parameterization
Google BigQuery Standard SQL supports parameterization. It is interesting to be able to use Python variables defined in the notebook as parameter values for SQL.
This notebook is an example how to use parameterized queries.
Data Preview
End of explanation
"""


%%bq query -n endpoint_stats
SELECT *
FROM `cloud-datalab-samples.httplogs.logs_20140615`
WHERE endpoint = @endpoint
LIMIT 10

%%bq execute -q endpoint_stats
parameters:
- name: endpoint
  type: STRING
  value: Interact2

"""
Explanation: Parameterization within SQL queries
Parameters are declared in SQL queries using a @name syntax within the SQL, and then defining name's value when executing the query. Notice you will have to define the query and execute it in two different cells. The shorthand way of running queries (using %%bq query without --name) gives you little control over the execution of the query.
End of explanation
"""


endpoint_val = 'Interact3'

"""
Explanation: This defined a SQL query with a string parameter named endpoint, which can be filled when executing the query. Let's give it some value in a separate cell:
End of explanation
"""


%%bq execute -q endpoint_stats
parameters:
- name: endpoint
  type: STRING
  value: $endpoint_val

"""
Explanation: In order to reference the variable defined above, Google Cloud Datalab offers the $var syntax, which can be invoked in the magic command:
End of explanation
"""


import google.datalab.bigquery as bq
endpoint_stats2 = bq.Query(sql='''
SELECT *
FROM `cloud-datalab-samples.httplogs.logs_20140615`
WHERE endpoint = @endpoint
LIMIT 10
''')

endpoint_value = 'Interact3'

query_parameters = [
  {
    'name': 'endpoint',
    'parameterType': {'type': 'STRING'},
    'parameterValue': {'value': endpoint_value}
  }
]

job = endpoint_stats2.execute(query_params=query_parameters)

job.result()

"""
Explanation: This can also be achieved using the Python API instead of the magic commands (%%bq). This is how we will create and execute a parameterized query using the API:
End of explanation
"""


