#!pip install google-cloud-bigquery
%load_ext google.cloud.bigquery

PROJECT='cloud-training-demos'  # CHANGE THIS

"""
Explanation: Example of using the Jupyter magics for BigQuery
This is the recommended way to programmatically access BigQuery when you are in a notebook environment.
If you need to create a Python code that works outside of a notebook environment (e.g. in a production environment), you should use code as shown in the Google Cloud Client Library example notebook in this directory.
Install library and extensions if needed
End of explanation
"""


%%bigquery --project $PROJECT
SELECT 
  start_station_name 
  , AVG(duration) as duration
  , COUNT(duration) as num_trips
FROM `bigquery-public-data`.london_bicycles.cycle_hire 
GROUP BY start_station_name
ORDER BY num_trips DESC
LIMIT 5

"""
Explanation: Run a query
End of explanation
"""


PARAMS = {"num_stations": 3}

%%bigquery --project $PROJECT --params $PARAMS
SELECT 
  start_station_name 
  , AVG(duration) as duration
  , COUNT(duration) as num_trips
FROM `bigquery-public-data`.london_bicycles.cycle_hire 
GROUP BY start_station_name
ORDER BY num_trips DESC
LIMIT @num_stations

"""
Explanation: Run a parameterized query
End of explanation
"""


%%bigquery df --project $PROJECT
SELECT 
  start_station_name 
  , AVG(duration) as duration
  , COUNT(duration) as num_trips
FROM `bigquery-public-data`.london_bicycles.cycle_hire 
GROUP BY start_station_name
ORDER BY num_trips DESC

df.describe()

df.plot.scatter('duration', 'num_trips');

"""
Explanation: Into a dataframe
End of explanation
"""


