make_table_definition.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "無題のノートブック",
      "cell_execution_strategy": "setup",
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# BigQuery のテーブル定義を抽出"
      ],
      "metadata": {
        "id": "ltt0vb-LaUkP"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## ライブラリのインポートと対象データセットの設定"
      ],
      "metadata": {
        "id": "Hi8xvemf61Lz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from google.cloud import bigquery"
      ],
      "metadata": {
        "id": "WpyrSOjn6wwr",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616670571,
          "user_tz": -540,
          "elapsed": 402,
          "user": {
            "displayName": "",
            "userId": ""
          }
        }
      },
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# 対象データセットの設定\n",
        "PROJECT_ID = \"bigquery-public-data\"\n",
        "DATASET_ID = \"chicago_taxi_trips\""
      ],
      "metadata": {
        "id": "8Sgdu30X6xIW",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616671682,
          "user_tz": -540,
          "elapsed": 1,
          "user": {
            "displayName": "",
            "userId": ""
          }
        }
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# BigQueryクライアントのインスタンスを生成\n",
        "client = bigquery.Client()"
      ],
      "metadata": {
        "id": "GZvxSy-4amsf",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616672035,
          "user_tz": -540,
          "elapsed": 1,
          "user": {
            "displayName": "",
            "userId": ""
          }
        }
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## メタデータの取得\n",
        "\n",
        "次に、テーブル定義書に必要なメタデータを、INFORMATION_SCHEMA から取得していきます。\n",
        "\n",
        "まずは、INFORMATION_SCHEMA.COLUMNS ビューからメタデータを取得します。"
      ],
      "metadata": {
        "id": "nO1yhbPE7AB0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = f\"\"\"\n",
        "    SELECT\n",
        "      *\n",
        "    FROM\n",
        "      `{PROJECT_ID}.{DATASET_ID}.INFORMATION_SCHEMA.COLUMNS`;\n",
        "\"\"\"\n",
        "\n",
        "# クエリを実行してDataFrameとして取得\n",
        "df = client.query(query).to_dataframe()\n",
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 313
        },
        "id": "blg97y-cavQX",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616675272,
          "user_tz": -540,
          "elapsed": 2807,
          "user": {
            "displayName": "",
            "userId": ""
          }
        },
        "outputId": "ef7f135c-d9f6-45dc-9363-3967cb01e896"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          table_catalog        table_schema  table_name           column_name  \\\n",
              "0  bigquery-public-data  chicago_taxi_trips  taxi_trips            unique_key   \n",
              "1  bigquery-public-data  chicago_taxi_trips  taxi_trips               taxi_id   \n",
              "2  bigquery-public-data  chicago_taxi_trips  taxi_trips  trip_start_timestamp   \n",
              "3  bigquery-public-data  chicago_taxi_trips  taxi_trips    trip_end_timestamp   \n",
              "4  bigquery-public-data  chicago_taxi_trips  taxi_trips          trip_seconds   \n",
              "\n",
              "   ordinal_position is_nullable  data_type is_generated generation_expression  \\\n",
              "0                 1          NO     STRING        NEVER                  None   \n",
              "1                 2          NO     STRING        NEVER                  None   \n",
              "2                 3         YES  TIMESTAMP        NEVER                  None   \n",
              "3                 4         YES  TIMESTAMP        NEVER                  None   \n",
              "4                 5         YES      INT64        NEVER                  None   \n",
              "\n",
              "  is_stored is_hidden is_updatable is_system_defined is_partitioning_column  \\\n",
              "0      None        NO         None                NO                     NO   \n",
              "1      None        NO         None                NO                     NO   \n",
              "2      None        NO         None                NO                     NO   \n",
              "3      None        NO         None                NO                     NO   \n",
              "4      None        NO         None                NO                     NO   \n",
              "\n",
              "   clustering_ordinal_position collation_name column_default rounding_mode  \n",
              "0                         <NA>           NULL           NULL          None  \n",
              "1                         <NA>           NULL           NULL          None  \n",
              "2                         <NA>           NULL           NULL          None  \n",
              "3                         <NA>           NULL           NULL          None  \n",
              "4                         <NA>           NULL           NULL          None  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b721e06a-6f49-4cac-a98e-695289a00639\" class=\"colab-df-container\">\n",
              "    <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>table_catalog</th>\n",
              "      <th>table_schema</th>\n",
              "      <th>table_name</th>\n",
              "      <th>column_name</th>\n",
              "      <th>ordinal_position</th>\n",
              "      <th>is_nullable</th>\n",
              "      <th>data_type</th>\n",
              "      <th>is_generated</th>\n",
              "      <th>generation_expression</th>\n",
              "      <th>is_stored</th>\n",
              "      <th>is_hidden</th>\n",
              "      <th>is_updatable</th>\n",
              "      <th>is_system_defined</th>\n",
              "      <th>is_partitioning_column</th>\n",
              "      <th>clustering_ordinal_position</th>\n",
              "      <th>collation_name</th>\n",
              "      <th>column_default</th>\n",
              "      <th>rounding_mode</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>unique_key</td>\n",
              "      <td>1</td>\n",
              "      <td>NO</td>\n",
              "      <td>STRING</td>\n",
              "      <td>NEVER</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>NULL</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>taxi_id</td>\n",
              "      <td>2</td>\n",
              "      <td>NO</td>\n",
              "      <td>STRING</td>\n",
              "      <td>NEVER</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>NULL</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_start_timestamp</td>\n",
              "      <td>3</td>\n",
              "      <td>YES</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>NEVER</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>NULL</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_end_timestamp</td>\n",
              "      <td>4</td>\n",
              "      <td>YES</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>NEVER</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>NULL</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_seconds</td>\n",
              "      <td>5</td>\n",
              "      <td>YES</td>\n",
              "      <td>INT64</td>\n",
              "      <td>NEVER</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>None</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>NULL</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b721e06a-6f49-4cac-a98e-695289a00639')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-b721e06a-6f49-4cac-a98e-695289a00639 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-b721e06a-6f49-4cac-a98e-695289a00639');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-7ee00bea-d64d-4dc3-ad0e-5624071c6390\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-7ee00bea-d64d-4dc3-ad0e-5624071c6390')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-7ee00bea-d64d-4dc3-ad0e-5624071c6390 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "テーブル定義書に必要な列を抽出します。"
      ],
      "metadata": {
        "id": "3aUP15wab469"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_columns = df[\n",
        "    [\n",
        "        \"table_catalog\",\n",
        "        \"table_schema\",\n",
        "        \"table_name\",\n",
        "        \"column_name\",\n",
        "        \"ordinal_position\",\n",
        "        \"data_type\",\n",
        "        \"is_nullable\",\n",
        "        \"is_partitioning_column\",\n",
        "        \"clustering_ordinal_position\",\n",
        "    ]\n",
        "]\n",
        "\n",
        "# Excel書き込み時のNA対策でstr型に変換\n",
        "df_columns = df_columns.astype({\"clustering_ordinal_position\": str})\n",
        "df_columns.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 313
        },
        "id": "45ckYAL-avNv",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616675272,
          "user_tz": -540,
          "elapsed": 3,
          "user": {
            "displayName": "",
            "userId": ""
          }
        },
        "outputId": "0af3815c-ca88-4f5e-968b-e997cae5dc0c"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          table_catalog        table_schema  table_name           column_name  \\\n",
              "0  bigquery-public-data  chicago_taxi_trips  taxi_trips            unique_key   \n",
              "1  bigquery-public-data  chicago_taxi_trips  taxi_trips               taxi_id   \n",
              "2  bigquery-public-data  chicago_taxi_trips  taxi_trips  trip_start_timestamp   \n",
              "3  bigquery-public-data  chicago_taxi_trips  taxi_trips    trip_end_timestamp   \n",
              "4  bigquery-public-data  chicago_taxi_trips  taxi_trips          trip_seconds   \n",
              "\n",
              "   ordinal_position  data_type is_nullable is_partitioning_column  \\\n",
              "0                 1     STRING          NO                     NO   \n",
              "1                 2     STRING          NO                     NO   \n",
              "2                 3  TIMESTAMP         YES                     NO   \n",
              "3                 4  TIMESTAMP         YES                     NO   \n",
              "4                 5      INT64         YES                     NO   \n",
              "\n",
              "  clustering_ordinal_position  \n",
              "0                        <NA>  \n",
              "1                        <NA>  \n",
              "2                        <NA>  \n",
              "3                        <NA>  \n",
              "4                        <NA>  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-46c1d09b-9cfb-4664-bac7-01e64d3da04d\" class=\"colab-df-container\">\n",
              "    <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>table_catalog</th>\n",
              "      <th>table_schema</th>\n",
              "      <th>table_name</th>\n",
              "      <th>column_name</th>\n",
              "      <th>ordinal_position</th>\n",
              "      <th>data_type</th>\n",
              "      <th>is_nullable</th>\n",
              "      <th>is_partitioning_column</th>\n",
              "      <th>clustering_ordinal_position</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>unique_key</td>\n",
              "      <td>1</td>\n",
              "      <td>STRING</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>taxi_id</td>\n",
              "      <td>2</td>\n",
              "      <td>STRING</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_start_timestamp</td>\n",
              "      <td>3</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>YES</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_end_timestamp</td>\n",
              "      <td>4</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>YES</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_seconds</td>\n",
              "      <td>5</td>\n",
              "      <td>INT64</td>\n",
              "      <td>YES</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-46c1d09b-9cfb-4664-bac7-01e64d3da04d')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-46c1d09b-9cfb-4664-bac7-01e64d3da04d button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-46c1d09b-9cfb-4664-bac7-01e64d3da04d');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-f9187864-82e8-4acc-b8bb-77dd10d89de2\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-f9187864-82e8-4acc-b8bb-77dd10d89de2')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-f9187864-82e8-4acc-b8bb-77dd10d89de2 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "次に、INFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューからメタデータを取得します。"
      ],
      "metadata": {
        "id": "-KOX_fn-b8FI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "query = f\"\"\"\n",
        "    SELECT\n",
        "      *\n",
        "    FROM\n",
        "      `{PROJECT_ID}.{DATASET_ID}.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`;\n",
        "\"\"\"\n",
        "\n",
        "# クエリを実行してDataFrameとして取得\n",
        "df = client.query(query).to_dataframe()\n",
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 556
        },
        "id": "7BZ07mM8avLR",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616678266,
          "user_tz": -540,
          "elapsed": 2996,
          "user": {
            "displayName": "",
            "userId": ""
          }
        },
        "outputId": "de18d8bc-e981-481b-9c5d-d935d2604a2c"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          table_catalog        table_schema  table_name           column_name  \\\n",
              "0  bigquery-public-data  chicago_taxi_trips  taxi_trips            unique_key   \n",
              "1  bigquery-public-data  chicago_taxi_trips  taxi_trips               taxi_id   \n",
              "2  bigquery-public-data  chicago_taxi_trips  taxi_trips  trip_start_timestamp   \n",
              "3  bigquery-public-data  chicago_taxi_trips  taxi_trips    trip_end_timestamp   \n",
              "4  bigquery-public-data  chicago_taxi_trips  taxi_trips          trip_seconds   \n",
              "\n",
              "             field_path  data_type  \\\n",
              "0            unique_key     STRING   \n",
              "1               taxi_id     STRING   \n",
              "2  trip_start_timestamp  TIMESTAMP   \n",
              "3    trip_end_timestamp  TIMESTAMP   \n",
              "4          trip_seconds      INT64   \n",
              "\n",
              "                                         description collation_name  \\\n",
              "0                    Unique identifier for the trip.           NULL   \n",
              "1                  A unique identifier for the taxi.           NULL   \n",
              "2  When the trip started, rounded to the nearest ...           NULL   \n",
              "3  When the trip ended, rounded to the nearest 15...           NULL   \n",
              "4                       Time of the trip in seconds.           NULL   \n",
              "\n",
              "  rounding_mode  \n",
              "0          None  \n",
              "1          None  \n",
              "2          None  \n",
              "3          None  \n",
              "4          None  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-99ca963c-2ec3-4e61-a870-9219ed19ed6b\" class=\"colab-df-container\">\n",
              "    <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>table_catalog</th>\n",
              "      <th>table_schema</th>\n",
              "      <th>table_name</th>\n",
              "      <th>column_name</th>\n",
              "      <th>field_path</th>\n",
              "      <th>data_type</th>\n",
              "      <th>description</th>\n",
              "      <th>collation_name</th>\n",
              "      <th>rounding_mode</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>unique_key</td>\n",
              "      <td>unique_key</td>\n",
              "      <td>STRING</td>\n",
              "      <td>Unique identifier for the trip.</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>taxi_id</td>\n",
              "      <td>taxi_id</td>\n",
              "      <td>STRING</td>\n",
              "      <td>A unique identifier for the taxi.</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_start_timestamp</td>\n",
              "      <td>trip_start_timestamp</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>When the trip started, rounded to the nearest ...</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_end_timestamp</td>\n",
              "      <td>trip_end_timestamp</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>When the trip ended, rounded to the nearest 15...</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_seconds</td>\n",
              "      <td>trip_seconds</td>\n",
              "      <td>INT64</td>\n",
              "      <td>Time of the trip in seconds.</td>\n",
              "      <td>NULL</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-99ca963c-2ec3-4e61-a870-9219ed19ed6b')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-99ca963c-2ec3-4e61-a870-9219ed19ed6b button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-99ca963c-2ec3-4e61-a870-9219ed19ed6b');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-62861d5a-1e40-406a-b547-3fd29e50a791\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-62861d5a-1e40-406a-b547-3fd29e50a791')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-62861d5a-1e40-406a-b547-3fd29e50a791 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "テーブル定義書に必要な列を抽出します。"
      ],
      "metadata": {
        "id": "HeJxjzsGb-SG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_description = df[[\"table_name\", \"column_name\", \"description\"]]\n",
        "df_description.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 241
        },
        "id": "VnKUdQfSa4F4",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616678267,
          "user_tz": -540,
          "elapsed": 3,
          "user": {
            "displayName": "",
            "userId": ""
          }
        },
        "outputId": "bfbd6c3e-0f60-4ca8-f1a3-fb8c67b52727"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   table_name           column_name  \\\n",
              "0  taxi_trips            unique_key   \n",
              "1  taxi_trips               taxi_id   \n",
              "2  taxi_trips  trip_start_timestamp   \n",
              "3  taxi_trips    trip_end_timestamp   \n",
              "4  taxi_trips          trip_seconds   \n",
              "\n",
              "                                         description  \n",
              "0                    Unique identifier for the trip.  \n",
              "1                  A unique identifier for the taxi.  \n",
              "2  When the trip started, rounded to the nearest ...  \n",
              "3  When the trip ended, rounded to the nearest 15...  \n",
              "4                       Time of the trip in seconds.  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b077b4d2-7f5e-4f38-b692-8582b71b3565\" class=\"colab-df-container\">\n",
              "    <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>table_name</th>\n",
              "      <th>column_name</th>\n",
              "      <th>description</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>unique_key</td>\n",
              "      <td>Unique identifier for the trip.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>taxi_id</td>\n",
              "      <td>A unique identifier for the taxi.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_start_timestamp</td>\n",
              "      <td>When the trip started, rounded to the nearest ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_end_timestamp</td>\n",
              "      <td>When the trip ended, rounded to the nearest 15...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_seconds</td>\n",
              "      <td>Time of the trip in seconds.</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b077b4d2-7f5e-4f38-b692-8582b71b3565')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-b077b4d2-7f5e-4f38-b692-8582b71b3565 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-b077b4d2-7f5e-4f38-b692-8582b71b3565');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-8a5168da-e3dd-4fbf-8203-2bec73504bf3\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-8a5168da-e3dd-4fbf-8203-2bec73504bf3')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-8a5168da-e3dd-4fbf-8203-2bec73504bf3 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "最後に、取得した df_columns と df_description をマージします。\n",
        "\n",
        "ここで、レコードを一意に特定するため、table_name と column_name の2つのキーを使用しています。"
      ],
      "metadata": {
        "id": "yHsLp_Xub___"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_table_def = df_columns.merge(df_description, on=[\"table_name\", \"column_name\"])\n",
        "df_table_def.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 556
        },
        "id": "pwuR-Qira4D_",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616678267,
          "user_tz": -540,
          "elapsed": 2,
          "user": {
            "displayName": "",
            "userId": ""
          }
        },
        "outputId": "68a69e71-f013-4d7e-fb9f-bd16a60d2504"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          table_catalog        table_schema  table_name           column_name  \\\n",
              "0  bigquery-public-data  chicago_taxi_trips  taxi_trips            unique_key   \n",
              "1  bigquery-public-data  chicago_taxi_trips  taxi_trips               taxi_id   \n",
              "2  bigquery-public-data  chicago_taxi_trips  taxi_trips  trip_start_timestamp   \n",
              "3  bigquery-public-data  chicago_taxi_trips  taxi_trips    trip_end_timestamp   \n",
              "4  bigquery-public-data  chicago_taxi_trips  taxi_trips          trip_seconds   \n",
              "\n",
              "   ordinal_position  data_type is_nullable is_partitioning_column  \\\n",
              "0                 1     STRING          NO                     NO   \n",
              "1                 2     STRING          NO                     NO   \n",
              "2                 3  TIMESTAMP         YES                     NO   \n",
              "3                 4  TIMESTAMP         YES                     NO   \n",
              "4                 5      INT64         YES                     NO   \n",
              "\n",
              "  clustering_ordinal_position  \\\n",
              "0                        <NA>   \n",
              "1                        <NA>   \n",
              "2                        <NA>   \n",
              "3                        <NA>   \n",
              "4                        <NA>   \n",
              "\n",
              "                                         description  \n",
              "0                    Unique identifier for the trip.  \n",
              "1                  A unique identifier for the taxi.  \n",
              "2  When the trip started, rounded to the nearest ...  \n",
              "3  When the trip ended, rounded to the nearest 15...  \n",
              "4                       Time of the trip in seconds.  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-199fbae6-05c5-49cb-bd9a-5b2cf5eca83e\" class=\"colab-df-container\">\n",
              "    <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>table_catalog</th>\n",
              "      <th>table_schema</th>\n",
              "      <th>table_name</th>\n",
              "      <th>column_name</th>\n",
              "      <th>ordinal_position</th>\n",
              "      <th>data_type</th>\n",
              "      <th>is_nullable</th>\n",
              "      <th>is_partitioning_column</th>\n",
              "      <th>clustering_ordinal_position</th>\n",
              "      <th>description</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>unique_key</td>\n",
              "      <td>1</td>\n",
              "      <td>STRING</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>Unique identifier for the trip.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>taxi_id</td>\n",
              "      <td>2</td>\n",
              "      <td>STRING</td>\n",
              "      <td>NO</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>A unique identifier for the taxi.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_start_timestamp</td>\n",
              "      <td>3</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>YES</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>When the trip started, rounded to the nearest ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_end_timestamp</td>\n",
              "      <td>4</td>\n",
              "      <td>TIMESTAMP</td>\n",
              "      <td>YES</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>When the trip ended, rounded to the nearest 15...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>bigquery-public-data</td>\n",
              "      <td>chicago_taxi_trips</td>\n",
              "      <td>taxi_trips</td>\n",
              "      <td>trip_seconds</td>\n",
              "      <td>5</td>\n",
              "      <td>INT64</td>\n",
              "      <td>YES</td>\n",
              "      <td>NO</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>Time of the trip in seconds.</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-199fbae6-05c5-49cb-bd9a-5b2cf5eca83e')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-199fbae6-05c5-49cb-bd9a-5b2cf5eca83e button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-199fbae6-05c5-49cb-bd9a-5b2cf5eca83e');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-2ce513d1-5f28-4ffd-8fce-27cd127378c2\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-2ce513d1-5f28-4ffd-8fce-27cd127378c2')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-2ce513d1-5f28-4ffd-8fce-27cd127378c2 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "テーブル定義書に必要なメタデータをデータフレームとして抽出することができました。"
      ],
      "metadata": {
        "id": "fBdix2MfcDTH"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# エクセルに出力\n",
        "\n",
        "前準備として、テンプレートファイルを作業ディレクトリに保存します。\n",
        "\n",
        "ノートブックでは、次のコマンドを実行して、GitHubからテンプレートファイルをダウンロードします。"
      ],
      "metadata": {
        "id": "WQmyGTLYa_57"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!wget https://github.com/cloud-ace/zenn-bq-table-definition/raw/main/table_template.xlsx"
      ],
      "metadata": {
        "id": "8LL4W2J8I7x3"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Python で Excel ファイルを操作するために、 openpyxl をインポートします。\n",
        "\n",
        "また、メタデータを転記するセルの開始位置を指定します。"
      ],
      "metadata": {
        "id": "HcodOKM9cLW_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from openpyxl import load_workbook\n",
        "\n",
        "# テンプレートファイルの設定\n",
        "STRT_TBL = (2, 3) # テーブル情報の開始位置 (行, 列)\n",
        "STRT_COL = (8, 1) # カラム情報の開始位置 (行, 列)"
      ],
      "metadata": {
        "id": "n6NokVRDa3-2",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616685550,
          "user_tz": -540,
          "elapsed": 414,
          "user": {
            "displayName": "",
            "userId": ""
          }
        }
      },
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# テンプレートファイルを開く\n",
        "wb = load_workbook(\"./table_template.xlsx\")\n",
        "\n",
        "# templateシートを取得\n",
        "ws_template = wb[\"template\"]\n",
        "\n",
        "# tableごとにテーブル定義書を作成\n",
        "for table_name, df in df_table_def.groupby(\"table_name\"):\n",
        "\n",
        "    # templeteシートをコピペ\n",
        "    ws = wb.copy_worksheet(ws_template)\n",
        "    print(f\"Table Name: {table_name}\")\n",
        "\n",
        "    # シート名をテーブル名に変更\n",
        "    ws.title = table_name\n",
        "\n",
        "    # テーブル情報の書き込み\n",
        "    ws.cell(\n",
        "        row=STRT_TBL[0],\n",
        "        column=STRT_TBL[1],\n",
        "        value=df[\"table_catalog\"].iloc[0],\n",
        "    )\n",
        "\n",
        "    ws.cell(\n",
        "        row=STRT_TBL[0] + 1,\n",
        "        column=STRT_TBL[1],\n",
        "        value=df[\"table_schema\"].iloc[0],\n",
        "    )\n",
        "\n",
        "    ws.cell(\n",
        "        row=STRT_TBL[0] + 2,\n",
        "        column=STRT_TBL[1],\n",
        "        value=df[\"table_name\"].iloc[0],\n",
        "    )\n",
        "\n",
        "    # カラム情報の書き込み\n",
        "    for i, (_, sr) in enumerate(df.iterrows()):\n",
        "        row = STRT_COL[0] + i\n",
        "\n",
        "        ws.cell(row=row, column=STRT_COL[1], value=sr[\"ordinal_position\"])\n",
        "        ws.cell(row=row, column=STRT_COL[1] + 1, value=sr[\"column_name\"])\n",
        "        ws.cell(row=row, column=STRT_COL[1] + 2, value=sr[\"data_type\"])\n",
        "        ws.cell(row=row, column=STRT_COL[1] + 3, value=sr[\"is_nullable\"])\n",
        "        ws.cell(row=row, column=STRT_COL[1] + 4, value=sr[\"is_partitioning_column\"])\n",
        "        ws.cell(row=row, column=STRT_COL[1] + 5, value=sr[\"clustering_ordinal_position\"])\n",
        "        ws.cell(row=row, column=STRT_COL[1] + 6, value=sr[\"description\"])\n",
        "\n",
        "# templeteシートを削除\n",
        "wb.remove(ws_template)\n",
        "\n",
        "# テーブル定義書（エクセルファイル）の保存\n",
        "save_path = f\"./table_definition_{PROJECT_ID}.{DATASET_ID}.xlsx\"\n",
        "wb.save(save_path)\n",
        "print(f\"Saved file at {save_path}\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Bxm76wJ4bIpe",
        "executionInfo": {
          "status": "ok",
          "timestamp": 1714616685963,
          "user_tz": -540,
          "elapsed": 1,
          "user": {
            "displayName": "",
            "userId": ""
          }
        },
        "outputId": "7352b6bf-522e-4b4d-a3ca-25c5212e5b2a"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Table Name: taxi_trips\n",
            "Saved file at ./table_definition_bigquery-public-data.chicago_taxi_trips.xlsx\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "テーブル定義書のエクセルファイルが出力されるので、ダウンロードします。\n",
        "\n",
        "以上です。"
      ],
      "metadata": {
        "id": "i_PFOCxZcrbt"
      }
    }
  ]
}