{"action":"created","comment":{"url":"https://api.github.com/repos/airbnb/airflow/pulls/comments/46234402","id":46234402,"diff_hunk":"@@ -0,0 +1,77 @@\n+import httplib2\n+import logging\n+import pandas\n+\n+from airflow.hooks.dbapi_hook import DbApiHook\n+from apiclient.discovery import build\n+from oauth2client.client import SignedJwtAssertionCredentials\n+\n+logging.getLogger(\"bigquery\").setLevel(logging.INFO)\n+\n+BQ_SCOPE = 'https://www.googleapis.com/auth/bigquery'\n+\n+class BigQueryHook(DbApiHook):\n+    \"\"\"\n+    Interact with BigQuery. Connections must be defined with an extras JSON field containing:\n+\n+    {\n+        \"project\": \"<google project ID>\",\n+        \"service_account\": \"<google service account email>\",\n+        \"key_path\": \"<p12 key path>\"\n+    }\n+    \"\"\"\n+\n+    conn_name_attr = 'bigquery_conn_id'\n+    default_conn_name = 'bigquery_default'\n+\n+    def get_conn(self):\n+        \"\"\"\n+        Returns a BigQuery service object.\n+        \"\"\"\n+        connection_info = self.get_connection(self.bigquery_conn_id)\n+        connection_extras = connection_info.extra_dejson\n+        service_account = connection_extras['service_account']\n+        key_path = connection_extras['key_path']\n+\n+        with file(key_path, 'rb') as key_file:\n+            key = key_file.read()\n+\n+        credentials = SignedJwtAssertionCredentials(\n+            service_account,\n+            key,\n+            scope=BQ_SCOPE)\n+            # TODO Support domain delegation, which will allow us to set a sub-account to execute as. We can then\n+            # pass DAG owner emails into the connection_info, and use it here.\n+            # sub='some@email.com')\n+\n+        http = httplib2.Http()\n+        http_authorized = credentials.authorize(http)\n+        service = build('bigquery', 'v2', http=http_authorized)\n+\n+        return service\n+\n+    def get_pandas_df(self, bql, parameters=None):\n+        \"\"\"\n+        Returns a Pandas DataFrame for the results produced by a BigQuery query.\n+        \"\"\"\n+        service = self.get_conn()\n+        connection_info = self.get_connection(self.bigquery_conn_id)\n+        connection_extras = connection_info.extra_dejson\n+        project = connection_extras['project']\n+        response = service.jobs().query(projectId=project, body={\n+            \"query\": bql,\n+        }).execute()\n+\n+        if len(response['rows']) > 0:\n+            # Extract column names from response.\n+            columns = [c['name'] for c in response['schema']['fields']]\n+\n+            # Extract data into a two-dimensional list of values.\n+            data = []\n+            for row in response['rows']:\n+                row = map(lambda kv: kv['v'], row['f'])\n+                data.append(row)","path":"airflow/hooks/bigquery_hook.py","position":73,"original_position":73,"commit_id":"539923874ab01352bec6dfa3ac823a38f8435ce4","original_commit_id":"539923874ab01352bec6dfa3ac823a38f8435ce4","user":{"login":"criccomini","id":167569,"avatar_url":"https://avatars.githubusercontent.com/u/167569?v=3","gravatar_id":"","url":"https://api.github.com/users/criccomini","html_url":"https://github.com/criccomini","followers_url":"https://api.github.com/users/criccomini/followers","following_url":"https://api.github.com/users/criccomini/following{/other_user}","gists_url":"https://api.github.com/users/criccomini/gists{/gist_id}","starred_url":"https://api.github.com/users/criccomini/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/criccomini/subscriptions","organizations_url":"https://api.github.com/users/criccomini/orgs","repos_url":"https://api.github.com/users/criccomini/repos","events_url":"https://api.github.com/users/criccomini/events{/privacy}","received_events_url":"https://api.github.com/users/criccomini/received_events","type":"User","site_admin":false},"body":"Poked at this a bit. The Pandas GBQ implementation appears to use 3 legged oauth, and thus requires a user at the terminal to actually be present. Running gbq.read_gbq actually opens a browser window for the human to complete OAuth. This doesn't work with two legged OAuth, which requires a service account to run. My entire implementation is predicated off of two legged OAuth. Some discussion on Google cloud is documented [here](https://developers.google.com/identity/protocols/OAuth2ServiceAccount).\r\n","created_at":"2015-12-01T02:39:32Z","updated_at":"2015-12-01T02:39:32Z","html_url":"https://github.com/airbnb/airflow/pull/702#discussion_r46234402","pull_request_url":"https://api.github.com/repos/airbnb/airflow/pulls/702","_links":{"self":{"href":"https://api.github.com/repos/airbnb/airflow/pulls/comments/46234402"},"html":{"href":"https://github.com/airbnb/airflow/pull/702#discussion_r46234402"},"pull_request":{"href":"https://api.github.com/repos/airbnb/airflow/pulls/702"}}},"pull_request":{"url":"https://api.github.com/repos/airbnb/airflow/pulls/702","id":52193227,"html_url":"https://github.com/airbnb/airflow/pull/702","diff_url":"https://github.com/airbnb/airflow/pull/702.diff","patch_url":"https://github.com/airbnb/airflow/pull/702.patch","issue_url":"https://api.github.com/repos/airbnb/airflow/issues/702","number":702,"state":"open","locked":false,"title":"Add BigQuery hook.","user":{"login":"criccomini","id":167569,"avatar_url":"https://avatars.githubusercontent.com/u/167569?v=3","gravatar_id":"","url":"https://api.github.com/users/criccomini","html_url":"https://github.com/criccomini","followers_url":"https://api.github.com/users/criccomini/followers","following_url":"https://api.github.com/users/criccomini/following{/other_user}","gists_url":"https://api.github.com/users/criccomini/gists{/gist_id}","starred_url":"https://api.github.com/users/criccomini/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/criccomini/subscriptions","organizations_url":"https://api.github.com/users/criccomini/orgs","repos_url":"https://api.github.com/users/criccomini/repos","events_url":"https://api.github.com/users/criccomini/events{/privacy}","received_events_url":"https://api.github.com/users/criccomini/received_events","type":"User","site_admin":false},"body":"I've added support for [Google BigQuery](https://cloud.google.com/bigquery/) hooks. This allows us to connect to BQ via a hook, as well as query BigQuery from the `ad hoc` page. It also enables charts to generate based off of a BigQuery query.","created_at":"2015-11-30T23:10:04Z","updated_at":"2015-12-01T02:39:32Z","closed_at":null,"merged_at":null,"merge_commit_sha":"dac8cd50afad7958bc1574992ab0acb7e180eb87","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/airbnb/airflow/pulls/702/commits","review_comments_url":"https://api.github.com/repos/airbnb/airflow/pulls/702/comments","review_comment_url":"https://api.github.com/repos/airbnb/airflow/pulls/comments{/number}","comments_url":"https://api.github.com/repos/airbnb/airflow/issues/702/comments","statuses_url":"https://api.github.com/repos/airbnb/airflow/statuses/539923874ab01352bec6dfa3ac823a38f8435ce4","head":{"label":"criccomini:bigquery-hook","ref":"bigquery-hook","sha":"539923874ab01352bec6dfa3ac823a38f8435ce4","user":{"login":"criccomini","id":167569,"avatar_url":"https://avatars.githubusercontent.com/u/167569?v=3","gravatar_id":"","url":"https://api.github.com/users/criccomini","html_url":"https://github.com/criccomini","followers_url":"https://api.github.com/users/criccomini/followers","following_url":"https://api.github.com/users/criccomini/following{/other_user}","gists_url":"https://api.github.com/users/criccomini/gists{/gist_id}","starred_url":"https://api.github.com/users/criccomini/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/criccomini/subscriptions","organizations_url":"https://api.github.com/users/criccomini/orgs","repos_url":"https://api.github.com/users/criccomini/repos","events_url":"https://api.github.com/users/criccomini/events{/privacy}","received_events_url":"https://api.github.com/users/criccomini/received_events","type":"User","site_admin":false},"repo":{"id":46378946,"name":"airflow","full_name":"criccomini/airflow","owner":{"login":"criccomini","id":167569,"avatar_url":"https://avatars.githubusercontent.com/u/167569?v=3","gravatar_id":"","url":"https://api.github.com/users/criccomini","html_url":"https://github.com/criccomini","followers_url":"https://api.github.com/users/criccomini/followers","following_url":"https://api.github.com/users/criccomini/following{/other_user}","gists_url":"https://api.github.com/users/criccomini/gists{/gist_id}","starred_url":"https://api.github.com/users/criccomini/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/criccomini/subscriptions","organizations_url":"https://api.github.com/users/criccomini/orgs","repos_url":"https://api.github.com/users/criccomini/repos","events_url":"https://api.github.com/users/criccomini/events{/privacy}","received_events_url":"https://api.github.com/users/criccomini/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/criccomini/airflow","description":"Airflow is a system to programmatically author, schedule and monitor data pipelines.","fork":true,"url":"https://api.github.com/repos/criccomini/airflow","forks_url":"https://api.github.com/repos/criccomini/airflow/forks","keys_url":"https://api.github.com/repos/criccomini/airflow/keys{/key_id}","collaborators_url":"https://api.github.com/repos/criccomini/airflow/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/criccomini/airflow/teams","hooks_url":"https://api.github.com/repos/criccomini/airflow/hooks","issue_events_url":"https://api.github.com/repos/criccomini/airflow/issues/events{/number}","events_url":"https://api.github.com/repos/criccomini/airflow/events","assignees_url":"https://api.github.com/repos/criccomini/airflow/assignees{/user}","branches_url":"https://api.github.com/repos/criccomini/airflow/branches{/branch}","tags_url":"https://api.github.com/repos/criccomini/airflow/tags","blobs_url":"https://api.github.com/repos/criccomini/airflow/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/criccomini/airflow/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/criccomini/airflow/git/refs{/sha}","trees_url":"https://api.github.com/repos/criccomini/airflow/git/trees{/sha}","statuses_url":"https://api.github.com/repos/criccomini/airflow/statuses/{sha}","languages_url":"https://api.github.com/repos/criccomini/airflow/languages","stargazers_url":"https://api.github.com/repos/criccomini/airflow/stargazers","contributors_url":"https://api.github.com/repos/criccomini/airflow/contributors","subscribers_url":"https://api.github.com/repos/criccomini/airflow/subscribers","subscription_url":"https://api.github.com/repos/criccomini/airflow/subscription","commits_url":"https://api.github.com/repos/criccomini/airflow/commits{/sha}","git_commits_url":"https://api.github.com/repos/criccomini/airflow/git/commits{/sha}","comments_url":"https://api.github.com/repos/criccomini/airflow/comments{/number}","issue_comment_url":"https://api.github.com/repos/criccomini/airflow/issues/comments{/number}","contents_url":"https://api.github.com/repos/criccomini/airflow/contents/{+path}","compare_url":"https://api.github.com/repos/criccomini/airflow/compare/{base}...{head}","merges_url":"https://api.github.com/repos/criccomini/airflow/merges","archive_url":"https://api.github.com/repos/criccomini/airflow/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/criccomini/airflow/downloads","issues_url":"https://api.github.com/repos/criccomini/airflow/issues{/number}","pulls_url":"https://api.github.com/repos/criccomini/airflow/pulls{/number}","milestones_url":"https://api.github.com/repos/criccomini/airflow/milestones{/number}","notifications_url":"https://api.github.com/repos/criccomini/airflow/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/criccomini/airflow/labels{/name}","releases_url":"https://api.github.com/repos/criccomini/airflow/releases{/id}","created_at":"2015-11-17T22:17:26Z","updated_at":"2015-11-17T22:17:27Z","pushed_at":"2015-11-30T23:13:11Z","git_url":"git://github.com/criccomini/airflow.git","ssh_url":"git@github.com:criccomini/airflow.git","clone_url":"https://github.com/criccomini/airflow.git","svn_url":"https://github.com/criccomini/airflow","homepage":"","size":6781,"stargazers_count":0,"watchers_count":0,"language":"JavaScript","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"airbnb:master","ref":"master","sha":"6f51b5a5f999d712451b3465d25b118cc145462b","user":{"login":"airbnb","id":698437,"avatar_url":"https://avatars.githubusercontent.com/u/698437?v=3","gravatar_id":"","url":"https://api.github.com/users/airbnb","html_url":"https://github.com/airbnb","followers_url":"https://api.github.com/users/airbnb/followers","following_url":"https://api.github.com/users/airbnb/following{/other_user}","gists_url":"https://api.github.com/users/airbnb/gists{/gist_id}","starred_url":"https://api.github.com/users/airbnb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/airbnb/subscriptions","organizations_url":"https://api.github.com/users/airbnb/orgs","repos_url":"https://api.github.com/users/airbnb/repos","events_url":"https://api.github.com/users/airbnb/events{/privacy}","received_events_url":"https://api.github.com/users/airbnb/received_events","type":"Organization","site_admin":false},"repo":{"id":33884891,"name":"airflow","full_name":"airbnb/airflow","owner":{"login":"airbnb","id":698437,"avatar_url":"https://avatars.githubusercontent.com/u/698437?v=3","gravatar_id":"","url":"https://api.github.com/users/airbnb","html_url":"https://github.com/airbnb","followers_url":"https://api.github.com/users/airbnb/followers","following_url":"https://api.github.com/users/airbnb/following{/other_user}","gists_url":"https://api.github.com/users/airbnb/gists{/gist_id}","starred_url":"https://api.github.com/users/airbnb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/airbnb/subscriptions","organizations_url":"https://api.github.com/users/airbnb/orgs","repos_url":"https://api.github.com/users/airbnb/repos","events_url":"https://api.github.com/users/airbnb/events{/privacy}","received_events_url":"https://api.github.com/users/airbnb/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/airbnb/airflow","description":"Airflow is a system to programmatically author, schedule and monitor data pipelines.","fork":false,"url":"https://api.github.com/repos/airbnb/airflow","forks_url":"https://api.github.com/repos/airbnb/airflow/forks","keys_url":"https://api.github.com/repos/airbnb/airflow/keys{/key_id}","collaborators_url":"https://api.github.com/repos/airbnb/airflow/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/airbnb/airflow/teams","hooks_url":"https://api.github.com/repos/airbnb/airflow/hooks","issue_events_url":"https://api.github.com/repos/airbnb/airflow/issues/events{/number}","events_url":"https://api.github.com/repos/airbnb/airflow/events","assignees_url":"https://api.github.com/repos/airbnb/airflow/assignees{/user}","branches_url":"https://api.github.com/repos/airbnb/airflow/branches{/branch}","tags_url":"https://api.github.com/repos/airbnb/airflow/tags","blobs_url":"https://api.github.com/repos/airbnb/airflow/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/airbnb/airflow/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/airbnb/airflow/git/refs{/sha}","trees_url":"https://api.github.com/repos/airbnb/airflow/git/trees{/sha}","statuses_url":"https://api.github.com/repos/airbnb/airflow/statuses/{sha}","languages_url":"https://api.github.com/repos/airbnb/airflow/languages","stargazers_url":"https://api.github.com/repos/airbnb/airflow/stargazers","contributors_url":"https://api.github.com/repos/airbnb/airflow/contributors","subscribers_url":"https://api.github.com/repos/airbnb/airflow/subscribers","subscription_url":"https://api.github.com/repos/airbnb/airflow/subscription","commits_url":"https://api.github.com/repos/airbnb/airflow/commits{/sha}","git_commits_url":"https://api.github.com/repos/airbnb/airflow/git/commits{/sha}","comments_url":"https://api.github.com/repos/airbnb/airflow/comments{/number}","issue_comment_url":"https://api.github.com/repos/airbnb/airflow/issues/comments{/number}","contents_url":"https://api.github.com/repos/airbnb/airflow/contents/{+path}","compare_url":"https://api.github.com/repos/airbnb/airflow/compare/{base}...{head}","merges_url":"https://api.github.com/repos/airbnb/airflow/merges","archive_url":"https://api.github.com/repos/airbnb/airflow/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/airbnb/airflow/downloads","issues_url":"https://api.github.com/repos/airbnb/airflow/issues{/number}","pulls_url":"https://api.github.com/repos/airbnb/airflow/pulls{/number}","milestones_url":"https://api.github.com/repos/airbnb/airflow/milestones{/number}","notifications_url":"https://api.github.com/repos/airbnb/airflow/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/airbnb/airflow/labels{/name}","releases_url":"https://api.github.com/repos/airbnb/airflow/releases{/id}","created_at":"2015-04-13T18:04:58Z","updated_at":"2015-11-30T22:02:13Z","pushed_at":"2015-12-01T01:50:22Z","git_url":"git://github.com/airbnb/airflow.git","ssh_url":"git@github.com:airbnb/airflow.git","clone_url":"https://github.com/airbnb/airflow.git","svn_url":"https://github.com/airbnb/airflow","homepage":"","size":7858,"stargazers_count":1441,"watchers_count":1441,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":259,"mirror_url":null,"open_issues_count":103,"forks":259,"open_issues":103,"watchers":1441,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/airbnb/airflow/pulls/702"},"html":{"href":"https://github.com/airbnb/airflow/pull/702"},"issue":{"href":"https://api.github.com/repos/airbnb/airflow/issues/702"},"comments":{"href":"https://api.github.com/repos/airbnb/airflow/issues/702/comments"},"review_comments":{"href":"https://api.github.com/repos/airbnb/airflow/pulls/702/comments"},"review_comment":{"href":"https://api.github.com/repos/airbnb/airflow/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/airbnb/airflow/pulls/702/commits"},"statuses":{"href":"https://api.github.com/repos/airbnb/airflow/statuses/539923874ab01352bec6dfa3ac823a38f8435ce4"}}}}