AlloyDB Vol2: 読み取りプール検証

vol2-readpool.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "## 事前にやっておくこと\n",
    "\n",
    "- ローカルマシンからアクセスできるようにAlloyDB Auth Proxyを貼っておく\n",
    "    - ref: <https://cloud.google.com/alloydb/docs/auth-proxy/connect>\n",
    "- このとき、 **踏み台用インスタンスの設定で「API と ID の管理」の「Cloud API アクセス スコープ」が「すべての Cloud API に完全アクセス権を許可」** である必要がある\n",
    "\n",
    "```bash\n",
    "docker run -d --rm --name proxy -p 15432:15432 -p 15433:15433 gcr.io/alloydb-connectors/alloydb-auth-proxy:latest \\\n",
    "    --address=0.0.0.0 \\\n",
    "    <プライマリインスタンスのURI>?port=15432 \\\n",
    "    <読み取りプールのURL>?port=15433\n",
    "```"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## gcloudコマンドで踏み台用インスタンスIPを取得"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "ips = !gcloud compute instances describe bastion --zone=asia-northeast1-b --format json | jq -r \".networkInterfaces[0].accessConfigs[0].natIP\"\n",
    "host = ips[0]\n",
    "host"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "is_executing": true
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "注: 以下の変数のSuffixで1/2は次の意味\n",
    "\n",
    "1: プライマリインスタンス\n",
    "2: 読み取りプール"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "port1 = \"15432\"\n",
    "port2 = \"15433\""
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 接続用URI組み立て\n",
    "\n",
    "環境変数は基本的に[PostgreSQL公式と同じ値](https://www.postgresql.org/docs/current/libpq-envars.html)をセット済み\n",
    "\n",
    "- `PGUSER`: ユーザ名(`postgres`)\n",
    "- `PGPASSWORD`: パスワード(起動時に設定した値)\n",
    "- `PGDATABASE`: データベース名(今回は`etude`)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "user = os.environ[\"PGUSER\"]\n",
    "password = os.environ[\"PGPASSWORD\"]\n",
    "database = os.environ[\"PGDATABASE\"]\n",
    "\n",
    "uri1 = f\"postgresql+psycopg2://{user}:{password}@{host}:{port1}/{database}\"\n",
    "uri2 = f\"postgresql+psycopg2://{user}:{password}@{host}:{port2}/{database}\"\n",
    "uri1, uri2"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "is_executing": true
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, text"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [],
   "source": [
    "engine1 = create_engine(uri1)\n",
    "conn1 = engine1.connect()\n",
    "\n",
    "engine2 = create_engine(uri2)\n",
    "conn2 = engine2.connect()"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## サンプルSQL実行\n",
    "\n",
    "Vol1の英国不動産取引データから、少し重そうなクエリを(多少雑ですが)書いてみました"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "'SELECT\\n\\tmain.property_type,\\n\\tmain.newly_built,\\n\\tmain.duration,\\n\\tmain.city,\\n\\t_avg.average,\\n\\t_min.minimum,\\n\\t_max.maximum\\nFROM\\n\\tland_registry_price_paid_uk AS main\\n\\tLEFT JOIN (\\n\\t\\tSELECT\\n\\t\\t\\tproperty_type,\\n\\t\\t\\tnewly_built,\\n\\t\\t\\tduration,\\n\\t\\t\\tcity,\\n\\t\\t\\tAVG(price) AS average\\n\\t\\tFROM\\n\\t\\t\\tland_registry_price_paid_uk\\n\\t\\tGROUP BY\\n\\t\\t\\tproperty_type,\\n\\t\\t\\tnewly_built,\\n\\t\\t\\tduration,\\n\\t\\t\\tcity) AS _avg ON main.property_type = _avg.property_type\\n\\tAND main.newly_built = _avg.newly_built\\n\\tAND main.duration = _avg.duration\\n\\tAND main.city = _avg.city\\n\\tLEFT JOIN (\\n\\t\\tSELECT\\n\\t\\t\\tproperty_type,\\n\\t\\t\\tnewly_built,\\n\\t\\t\\tduration,\\n\\t\\t\\tcity,\\n\\t\\t\\tMIN(price) AS minimum\\n\\t\\tFROM\\n\\t\\t\\tland_registry_price_paid_uk\\n\\t\\tGROUP BY\\n\\t\\t\\tproperty_type,\\n\\t\\t\\tnewly_built,\\n\\t\\t\\tduration,\\n\\t\\t\\tcity) AS _min ON main.property_type = _min.property_type\\n\\tAND main.newly_built = _min.newly_built\\n\\tAND main.duration = _min.duration\\n\\tAND main.city = _min.city\\n\\tLEFT JOIN (\\n\\t\\tSELECT\\n\\t\\t\\tproperty_type,\\n\\t\\t\\tnewly_built,\\n\\t\\t\\tduration,\\n\\t\\t\\tcity,\\n\\t\\t\\tMAX(price) AS maximum\\n\\t\\tFROM\\n\\t\\t\\tland_registry_price_paid_uk\\n\\t\\tGROUP BY\\n\\t\\t\\tproperty_type,\\n\\t\\t\\tnewly_built,\\n\\t\\t\\tduration,\\n\\t\\t\\tcity) AS _max ON main.property_type = _max.property_type\\n\\tAND main.newly_built = _max.newly_built\\n\\tAND main.duration = _max.duration\\n\\tAND main.city = _max.city;\\n'"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qs = \"\"\"SELECT\n",
    "\tmain.property_type,\n",
    "\tmain.newly_built,\n",
    "\tmain.duration,\n",
    "\tmain.city,\n",
    "\t_avg.average,\n",
    "\t_min.minimum,\n",
    "\t_max.maximum\n",
    "FROM\n",
    "\tland_registry_price_paid_uk AS main\n",
    "\tLEFT JOIN (\n",
    "\t\tSELECT\n",
    "\t\t\tproperty_type,\n",
    "\t\t\tnewly_built,\n",
    "\t\t\tduration,\n",
    "\t\t\tcity,\n",
    "\t\t\tAVG(price) AS average\n",
    "\t\tFROM\n",
    "\t\t\tland_registry_price_paid_uk\n",
    "\t\tGROUP BY\n",
    "\t\t\tproperty_type,\n",
    "\t\t\tnewly_built,\n",
    "\t\t\tduration,\n",
    "\t\t\tcity) AS _avg ON main.property_type = _avg.property_type\n",
    "\tAND main.newly_built = _avg.newly_built\n",
    "\tAND main.duration = _avg.duration\n",
    "\tAND main.city = _avg.city\n",
    "\tLEFT JOIN (\n",
    "\t\tSELECT\n",
    "\t\t\tproperty_type,\n",
    "\t\t\tnewly_built,\n",
    "\t\t\tduration,\n",
    "\t\t\tcity,\n",
    "\t\t\tMIN(price) AS minimum\n",
    "\t\tFROM\n",
    "\t\t\tland_registry_price_paid_uk\n",
    "\t\tGROUP BY\n",
    "\t\t\tproperty_type,\n",
    "\t\t\tnewly_built,\n",
    "\t\t\tduration,\n",
    "\t\t\tcity) AS _min ON main.property_type = _min.property_type\n",
    "\tAND main.newly_built = _min.newly_built\n",
    "\tAND main.duration = _min.duration\n",
    "\tAND main.city = _min.city\n",
    "\tLEFT JOIN (\n",
    "\t\tSELECT\n",
    "\t\t\tproperty_type,\n",
    "\t\t\tnewly_built,\n",
    "\t\t\tduration,\n",
    "\t\t\tcity,\n",
    "\t\t\tMAX(price) AS maximum\n",
    "\t\tFROM\n",
    "\t\t\tland_registry_price_paid_uk\n",
    "\t\tGROUP BY\n",
    "\t\t\tproperty_type,\n",
    "\t\t\tnewly_built,\n",
    "\t\t\tduration,\n",
    "\t\t\tcity) AS _max ON main.property_type = _max.property_type\n",
    "\tAND main.newly_built = _max.newly_built\n",
    "\tAND main.duration = _max.duration\n",
    "\tAND main.city = _max.city;\n",
    "\"\"\"\n",
    "\n",
    "query = text(qs)\n",
    "query.text"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 実際に取れたデータ確認\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "pd.read_sql(query, conn1)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "is_executing": true
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 実際に計測\n",
    "\n",
    "前述の通り `conn1`がプライマリインスタンス、 `conn2`が読み取りプール\n",
    "だいぶ時間かかるので注意。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "%%timeit -n 5 -r 5\n",
    "\n",
    "_ = pd.read_sql(query, conn1)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "is_executing": true
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "%%timeit -n 5 -r 5\n",
    "\n",
    "_ = pd.read_sql(query, conn2)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "is_executing": true
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## お片付け"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "conn1.close()\n",
    "conn2.close()"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}

jupyterの他に実行に必要なパッケージ:

• SQLAlchemy>=2.0.0
• psycopg2-binary
• pandas