PROJECT='cloud-training-demos'  # CHANGE THIS
from googleapiclient.discovery import build
service = build('bigquery', 'v2')

"""
Explanation: Example of using the Google API Client to access BigQuery
Note that this is <b>not</b> the recommended approach. You should use the BigQuery client library because that is idiomatic Python. 
See the bigquery_client notebook for examples.
Authenticate and build stubs
End of explanation
"""


# information about the ch04 dataset
dsinfo = service.datasets().get(datasetId="ch04", projectId=PROJECT).execute()
for info in dsinfo.items():
  print(info)

"""
Explanation: Get info about a dataset
End of explanation
"""


# list tables in dataset
tables = service.tables().list(datasetId="ch04", projectId=PROJECT).execute()
for t in tables['tables']:
  print(t['tableReference']['tableId'] + ' was created at ' + t['creationTime'])

"""
Explanation: List tables and creation times
End of explanation
"""


# send a query request
request={
  "useLegacySql": False, 
  "query": "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" 
}
print(request)
response = service.jobs().query(projectId=PROJECT, body=request).execute()
print('----' * 10)
for r in response['rows']:
  print(r['f'][0]['v'])

"""
Explanation: Query and get result
End of explanation
"""


# send a query request that will not terminate within the timeout specified and will require paging
request={
  "useLegacySql": False,
  "timeoutMs": 0,
  "useQueryCache": False,
  "query": "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" 
}
response = service.jobs().query(projectId=PROJECT, body=request).execute()
print(response)

jobId = response['jobReference']['jobId']
print(jobId)

# get query results
while (not response['jobComplete']):
  response = service.jobs().getQueryResults(projectId=PROJECT, 
                                            jobId=jobId, 
                                            maxResults=2, 
                                            timeoutMs=5).execute()

while (True):
  # print responses
  for row in response['rows']:
    print(row['f'][0]['v']) # station name
  print('--' * 5)
  # page through responses
  if 'pageToken' in response:
    pageToken = response['pageToken']
    # get next page
    response = service.jobs().getQueryResults(projectId=PROJECT, 
                                              jobId=jobId, 
                                              maxResults=2,
                                              pageToken=pageToken,
                                              timeoutMs=5).execute()
  else:
    break
  

"""
Explanation: Asynchronous query and paging through results
End of explanation
"""


