# Some pre-flight work:
# - Libraries.
# - Patch to handle authentication automatically on Google Compute Enginefrom oauth2client.gce import AppAssertionCredentials
from bigquery_client import BigqueryClient
from pandas.io import gbq
import pandas as pd

def GetMetadata(path):
  import urllib2
  BASE_PATH = 'http://metadata/computeMetadata/v1/'
  request = urllib2.Request(BASE_PATH + path, headers={'Metadata-Flavor': 'Google'})
  return urllib2.urlopen(request).read()

credentials = AppAssertionCredentials(scope='https://www.googleapis.com/auth/bigquery')

client = BigqueryClient(credentials=credentials,
                        api='https://www.googleapis.com',
                        api_version='v2',
                        project_id=GetMetadata('project/project-id'))

gbq._authenticate = lambda: client

query = """
SELECT country, SUM(c) AS count
FROM [gdelt-bq:sample_views.country_date_matconf_numarts]
GROUP BY country
ORDER BY count DESC
LIMIT 10
"""
gbq.read_gbq(query)

query = """
SELECT date, c AS count
FROM [gdelt-bq:sample_views.country_date_matconf_numarts]
WHERE country='Egypt'
ORDER BY date
"""
data=gbq.read_gbq(query)
data.index=pd.to_datetime(data['date']*1000)
x = data['count'].plot()

query = """
  SELECT country, date, c AS count
  FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a 
  CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 30) b
  WHERE country='Egypt'
  AND date+i*86400000000 = PARSE_UTC_USEC('2011-01-27')
"""
data_egypt=gbq.read_gbq(query)
data_egypt.index=pd.to_datetime(data_egypt['date']*1000)
x = data_egypt['count'].plot()

query = """
SELECT
  STRFTIME_UTC_USEC(a.ending_at, "%Y-%m-%d") ending_at1,
  STRFTIME_UTC_USEC(b.ending_at-30*86400000000, "%Y-%m-%d") starting_at2,
  STRFTIME_UTC_USEC(b.ending_at, "%Y-%m-%d") ending_at2,
  a.country, b.country, CORR(a.c, b.c) corr, COUNT(*) c
FROM (
  SELECT country, date+i*86400000000 ending_at, c, i
  FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a 
  CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 30) b
) b
JOIN (
  SELECT country, date+i*86400000000 ending_at, c, i
  FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a 
  CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 30) b
  WHERE country='Egypt'
  AND date+i*86400000000 = PARSE_UTC_USEC('2011-01-27')
) a
ON a.i=b.i
WHERE a.ending_at != b.ending_at
GROUP EACH BY ending_at1, ending_at2, starting_at2, a.country, b.country
HAVING (c = 30 AND ABS(corr) > 0.254)
ORDER BY corr DESC

LIMIT 10
"""
data=gbq.read_gbq(query)
data


query = """
  SELECT country, date, c AS count
  FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a 
  CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 30) b
  WHERE country='Chad'
  AND date+i*86400000000 = PARSE_UTC_USEC('2013-11-18')
"""
data_chad=gbq.read_gbq(query)
data_chad.index=pd.to_datetime(data_chad['date']*1000)
data_chad['count'].plot()

data_egypt['count'].plot()