Colaboratory＋BigQuery最初の一歩

colaboratory-bigquery.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Colaboratory＋BigQuery最初の一歩",
      "version": "0.3.2",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "[View in Colaboratory](https://colab.research.google.com/gist/actindi/18bd80f552e97d395bf2fec2062797ab/colaboratory-bigquery.ipynb)"
      ]
    },
    {
      "metadata": {
        "id": "MASP_bRviaHu",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Google アカウントの認証を通します。\n",
        "# このセルを実行するとURLが表示されます。そのURLにアクセスすると認証コードが発行されます。\n",
        "# 表示されている入力フィールドに認証コードを入力すると、認証完了です。\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "XoU9YFRmirlC",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "020e0667-26c7-4620-f651-27c304eca64b"
      },
      "cell_type": "code",
      "source": [
        "# マジックコマンド`%%bigquery`を利用してBigQueryのクエリを実行します。\n",
        "# 引数にはプロジェクトIDと結果を格納する変数名を指定します。\n",
        "\n",
        "%%bigquery --project your_project_id df\n",
        "SELECT \n",
        "  COUNT(*) as total_rows\n",
        "FROM `bigquery-public-data.samples.gsod`"
      ],
      "execution_count": 0,
      "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>total_rows</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>114420316</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   total_rows\n",
              "0   114420316"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "metadata": {
        "id": "T4iG2xZ8qeL6",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "5d06c7f6-b169-4a1a-fbb6-a1835db1d0af"
      },
      "cell_type": "code",
      "source": [
        "# 結果を格納している変数 ｄｆ の中身と型を見てみます。ちゃんと格納されていますね。\n",
        "df"
      ],
      "execution_count": 0,
      "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>total_rows</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>114420316</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   total_rows\n",
              "0   114420316"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "metadata": {
        "id": "Oe890I6Aqf_L",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "6013082a-1d93-455f-b206-be7e4f5e1641"
      },
      "cell_type": "code",
      "source": [
        "# 変数の型を調べてみると pandasのDataFrameです。結果を後で加工しやすいですね。\n",
        "print(type(df))"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}