gharchive_query.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "BigQueryGithub.ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/qwiksilva/cs224w-github-rec/blob/master/gharchive_query.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h8Iwwgh0maHE",
        "colab_type": "code",
        "outputId": "0daa4f71-9cb2-43a7-e78f-2c4ee00eb84a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "print('Authenticated')"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Authenticated\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IJYZ5pDtQ8hP",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 50
        },
        "outputId": "c18becb5-5f56-492c-fbfe-4674daaa26fc"
      },
      "source": [
        "%load_ext google.colab.data_table\n",
        "# %unload_ext google.colab.data_table"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "The google.colab.data_table extension is already loaded. To reload it, use:\n",
            "  %reload_ext google.colab.data_table\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cmXado7xTScp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "%%bigquery --project cs325b-218620 issue_comment_df\n",
        "SELECT\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.id') as id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.body') as comment,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.user.id') as commenter_id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.user.login') as commenter_login,\n",
        "  #JSON_EXTRACT_SCALAR(payload, '$.issue.id') as pr_id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.issue.number') as number,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.issue.user.id') as user_id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.issue.user.login') as username,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.issue.title') as title,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.issue.body') as description,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.issue.created_at') as pr_created_at,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.created_at') as comment_created_at\n",
        "FROM `githubarchive.year.20*`\n",
        "WHERE\n",
        "  _TABLE_SUFFIX BETWEEN '15' AND '18'\n",
        "  AND type = 'IssueCommentEvent'\n",
        "  AND repo.name = \"kubernetes/kubernetes\"\n",
        "  AND JSON_EXTRACT_SCALAR(payload, '$.action')=\"created\"\n",
        "  AND JSON_EXTRACT_SCALAR(payload, '$.issue.number') IN \n",
        "    (SELECT  DISTINCT\n",
        "    JSON_EXTRACT_SCALAR(payload, '$.pull_request.number') as number\n",
        "    FROM `githubarchive.year.20*`\n",
        "    where \n",
        "    _TABLE_SUFFIX BETWEEN '15' AND '18'\n",
        "    AND type = 'PullRequestEvent'\n",
        "    AND repo.name = \"kubernetes/kubernetes\"\n",
        "    )"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p-eZpWdvR8xR",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "%%bigquery --project cs325b-218620 pr_comment_df\n",
        "\n",
        "SELECT\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.id') as id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.body') as comment,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.user.id') as commenter_id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.user.login') as commenter_login,\n",
        "  #JSON_EXTRACT_SCALAR(payload, '$.pull_request.id') as pr_id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.pull_request.number') as number,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.pull_request.user.id') as user_id,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.pull_request.user.login') as username,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.pull_request.title') as title,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.pull_request.body') as description,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.pull_request.created_at') as pr_created_at,\n",
        "  JSON_EXTRACT_SCALAR(payload, '$.comment.created_at') as comment_created_at\n",
        "FROM `githubarchive.year.20*`\n",
        "\n",
        "WHERE\n",
        "  _TABLE_SUFFIX BETWEEN '15' AND '18'\n",
        "  AND type='PullRequestReviewCommentEvent'\n",
        "  AND repo.name = \"kubernetes/kubernetes\"\n",
        "  AND JSON_EXTRACT_SCALAR(payload, '$.action')=\"created\""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "To8FochaWglF",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "comment_df = pr_comment_df.append(issue_comment_df)\n",
        "comment_df.to_csv('kubernetes.csv')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9B8w79NyWYuQ",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        },
        "outputId": "554c2a10-6e9d-482c-96f4-7948b16a0501"
      },
      "source": [
        "len(comment_df.number.unique())"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "35452"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OPi1rdtlWTkA",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 553
        },
        "outputId": "0a6a7df7-ac73-413e-8299-f91ba5139f62"
      },
      "source": [
        "comment_df"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>comment</th>\n",
              "      <th>commenter_id</th>\n",
              "      <th>commenter_login</th>\n",
              "      <th>number</th>\n",
              "      <th>user_id</th>\n",
              "      <th>username</th>\n",
              "      <th>title</th>\n",
              "      <th>description</th>\n",
              "      <th>pr_created_at</th>\n",
              "      <th>comment_created_at</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>83223424</td>\n",
              "      <td>Whitespace removed</td>\n",
              "      <td>13948445</td>\n",
              "      <td>JacobTanenbaum</td>\n",
              "      <td>27206</td>\n",
              "      <td>13948445</td>\n",
              "      <td>JacobTanenbaum</td>\n",
              "      <td>Add 'kubectl set resources'</td>\n",
              "      <td>Add \"kubectl set resources\" for easier updatin...</td>\n",
              "      <td>2016-06-10T22:17:36Z</td>\n",
              "      <td>2016-10-13T14:04:54Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>71956732</td>\n",
              "      <td>It is for sorting. See first example here http...</td>\n",
              "      <td>11994812</td>\n",
              "      <td>mwielgus</td>\n",
              "      <td>29455</td>\n",
              "      <td>11994812</td>\n",
              "      <td>mwielgus</td>\n",
              "      <td>Replica location planner for Federated Replica...</td>\n",
              "      <td>Requires #29385 to be merged.\\r\\n\\r\\ncc: @quin...</td>\n",
              "      <td>2016-07-22T15:10:49Z</td>\n",
              "      <td>2016-07-22T22:52:15Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>79633255</td>\n",
              "      <td>yeah, we could integrate dumping into the bina...</td>\n",
              "      <td>826111</td>\n",
              "      <td>jayunit100</td>\n",
              "      <td>32902</td>\n",
              "      <td>826111</td>\n",
              "      <td>jayunit100</td>\n",
              "      <td>Viper direct bindings to TestContext struct wi...</td>\n",
              "      <td>Part of #31453 to support hierarchichal parame...</td>\n",
              "      <td>2016-09-16T21:00:39Z</td>\n",
              "      <td>2016-09-20T15:04:17Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>92006105</td>\n",
              "      <td>Once both annotation conversations are in that...</td>\n",
              "      <td>87555</td>\n",
              "      <td>rrati</td>\n",
              "      <td>37299</td>\n",
              "      <td>87555</td>\n",
              "      <td>rrati</td>\n",
              "      <td>[scheduling] Moved node affinity from annotati...</td>\n",
              "      <td>Converted node affinity from annotations to ap...</td>\n",
              "      <td>2016-11-22T18:32:03Z</td>\n",
              "      <td>2016-12-12T18:17:54Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>66541531</td>\n",
              "      <td>Gone now with sets.String</td>\n",
              "      <td>13481082</td>\n",
              "      <td>therc</td>\n",
              "      <td>26900</td>\n",
              "      <td>13481082</td>\n",
              "      <td>therc</td>\n",
              "      <td>AWS: cache values from getInstancesByNodeName()</td>\n",
              "      <td>Fixes #25610 by reducing redundant calls to De...</td>\n",
              "      <td>2016-06-06T17:54:03Z</td>\n",
              "      <td>2016-06-09T23:47:07Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>705206</th>\n",
              "      <td>326383169</td>\n",
              "      <td>/retest\\nThis bot automatically retries jobs t...</td>\n",
              "      <td>30488982</td>\n",
              "      <td>fejta-bot</td>\n",
              "      <td>51519</td>\n",
              "      <td>13374016</td>\n",
              "      <td>hzxuzhonghu</td>\n",
              "      <td>update deprecated interface and fix bug not re...</td>\n",
              "      <td>&lt;!--  Thanks for sending a pull request!  Here...</td>\n",
              "      <td>2017-08-29T12:19:56Z</td>\n",
              "      <td>2017-08-31T18:30:51Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>705207</th>\n",
              "      <td>326841400</td>\n",
              "      <td>/retest\\nThis bot automatically retries jobs t...</td>\n",
              "      <td>30488982</td>\n",
              "      <td>fejta-bot</td>\n",
              "      <td>51868</td>\n",
              "      <td>730123</td>\n",
              "      <td>sttts</td>\n",
              "      <td>client-go: fix 'go build ./...'</td>\n",
              "      <td>Test-only directories seem to confuse go-build...</td>\n",
              "      <td>2017-09-03T13:16:36Z</td>\n",
              "      <td>2017-09-04T00:12:21Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>705208</th>\n",
              "      <td>331331239</td>\n",
              "      <td>/retest\\nThis bot automatically retries jobs t...</td>\n",
              "      <td>30488982</td>\n",
              "      <td>fejta-bot</td>\n",
              "      <td>52322</td>\n",
              "      <td>5459214</td>\n",
              "      <td>davidz627</td>\n",
              "      <td>Fixes issue where PVCs using `standard` Storag...</td>\n",
              "      <td>Fixes #50115\\r\\n\\r\\nChanged GetAllZones to onl...</td>\n",
              "      <td>2017-09-12T03:23:27Z</td>\n",
              "      <td>2017-09-22T02:12:24Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>705209</th>\n",
              "      <td>350207355</td>\n",
              "      <td>/retest\\nThis bot automatically retries jobs t...</td>\n",
              "      <td>30488982</td>\n",
              "      <td>fejta-bot</td>\n",
              "      <td>55832</td>\n",
              "      <td>8268008</td>\n",
              "      <td>ceridwen</td>\n",
              "      <td>Remove deprecated API definitions to make the ...</td>\n",
              "      <td>&lt;!--  Thanks for sending a pull request!  Here...</td>\n",
              "      <td>2017-11-16T01:32:16Z</td>\n",
              "      <td>2017-12-08T08:45:49Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>705210</th>\n",
              "      <td>352353576</td>\n",
              "      <td>/retest\\nThis bot automatically retries jobs t...</td>\n",
              "      <td>30488982</td>\n",
              "      <td>fejta-bot</td>\n",
              "      <td>57071</td>\n",
              "      <td>4178417</td>\n",
              "      <td>andyzhangx</td>\n",
              "      <td>Automated cherry pick of #56679</td>\n",
              "      <td>Cherry pick of #56679 on release-1.8.\\n\\n#5667...</td>\n",
              "      <td>2017-12-12T03:02:16Z</td>\n",
              "      <td>2017-12-18T08:09:38Z</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>855323 rows × 11 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "               id  ...    comment_created_at\n",
              "0        83223424  ...  2016-10-13T14:04:54Z\n",
              "1        71956732  ...  2016-07-22T22:52:15Z\n",
              "2        79633255  ...  2016-09-20T15:04:17Z\n",
              "3        92006105  ...  2016-12-12T18:17:54Z\n",
              "4        66541531  ...  2016-06-09T23:47:07Z\n",
              "...           ...  ...                   ...\n",
              "705206  326383169  ...  2017-08-31T18:30:51Z\n",
              "705207  326841400  ...  2017-09-04T00:12:21Z\n",
              "705208  331331239  ...  2017-09-22T02:12:24Z\n",
              "705209  350207355  ...  2017-12-08T08:45:49Z\n",
              "705210  352353576  ...  2017-12-18T08:09:38Z\n",
              "\n",
              "[855323 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LD3M9M71JzX_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install -U -q PyDrive\n",
        "from pydrive.auth import GoogleAuth\n",
        "from pydrive.drive import GoogleDrive\n",
        "from google.colab import auth\n",
        "from oauth2client.client import GoogleCredentials\n",
        "\n",
        "auth.authenticate_user()\n",
        "gauth = GoogleAuth()\n",
        "gauth.credentials = GoogleCredentials.get_application_default()\n",
        "drive = GoogleDrive(gauth)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wVyUnNjSVIWm",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        },
        "outputId": "ed3d9bc1-294d-4901-ebed-7d8850de9d0b"
      },
      "source": [
        "uploaded = drive.CreateFile({'title': 'kubernetes.csv'})\n",
        "uploaded.SetContentFile('kubernetes.csv')\n",
        "uploaded.Upload()\n",
        "print('Uploaded file with ID {}'.format(uploaded.get('id')))"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Uploaded file with ID 1_7UJg1gfcot7-FWoFlMm7bjvX7lutq7v\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z4pF1p-yZeB0",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}