CUBE SUGAR CONTAINER

技術系のこと書きます。

Google Compute Engine で SSH Port Forwarding する

今回は Google Compute Engine のインスタンスで SSH Port Forwarding する方法について。 SSH Port Forwarding を使うと、インスタンスのポートをインターネットに晒すことなく利用できる。

使った環境は次の通り。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ gcloud version          
Google Cloud SDK 239.0.0
bq 2.0.42
core 2019.03.17
gsutil 4.37

下準備

まずは下準備から。

Google Cloud SDK をインストールする

今回は Google Cloud SDK の CLI を使って Compute Engine を操作する。 なので、最初に Homebrew Cask を使って Google Cloud SDK をインストールしておく。

$ brew cask install google-cloud-sdk

認証する

インストールできたら gcloud auth login コマンドで Google Cloud Platform の認証をしておく。

$ gcloud auth login

プロジェクトを作る

今回の動作確認をするプロジェクトを用意する。

$ gcloud projects create gce-example-$(whoami)-$(date "+%Y%m%d")
$ gcloud config set project gce-example-$(whoami)-$(date "+%Y%m%d")

API と課金を有効にする

プロジェクトで API と課金を有効にする。 この操作だけは CLI からできないのでブラウザから行う。

$ open https://console.cloud.google.com/apis/dashboard

API が使えないと実行できないコマンドが上手くいけばおっけー。

$ gcloud compute instances list
Listed 0 items.

インスタンスを起動する。

適当にインスタンスを立ち上げる。

$ gcloud compute instances create gce-example \
  --preemptible \
  --zone asia-northeast1-a \
  --machine-type f1-micro \
  --image-project ubuntu-os-cloud \
  --image-family ubuntu-1804-lts

立ち上げたインスタンスにログインする。

$ gcloud compute ssh --zone asia-northeast1-a gce-example

試しに Jupyter Notebook をインストールして起動する。 これでリモートの TCP:8888 ポートで Jupyter Notebook のサービスが動く。

gce-example $ sudo apt-get update
gce-example $ sudo apt-get -y install jupyter-notebook
gce-example $ jupyter notebook

準備ができたら、続いて SSH Port Forwarding する。

gcloud compute ssh では -- 以降に OpenSSH のオプションをそのまま渡せる。 これを利用してリモートの TCP:8888 をローカルホストの TCP:8888 にマッピングする。 -N オプションを指定すると、リモートでコマンドを実行しないことを表す。

$ gcloud compute ssh --zone asia-northeast1-a gce-example \
  -- -N -L 8888:localhost:8888

あとはブラウザでローカルホストの TCP:8888 ポートにアクセスすれば、いつもの画面が見える。

$ open http://localhost:8888

f:id:momijiame:20190323151508p:plain

めでたしめでたし。

Google Compute Engine のカスタムイメージを作る

今回は Google Compute Engine でカスタムイメージを作る方法について。 カスタムイメージというのは、既存のベースイメージに何らかのカスタマイズを施したイメージを指す。 あらかじめカスタムイメージを作っておくことで環境構築の手間をはぶくことができる場合がある。

使った環境は次の通り。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ gcloud version
Google Cloud SDK 238.0.0
bq 2.0.42
core 2019.03.08
gsutil 4.37

下準備

あらかじめ、いくつか下準備をしておく。

Google Cloud SDK をインストールする

今回の操作は基本的に Google Cloud SDK の CLI を使う。

なので、まずは Homebrew で Google Cloud SDK をインストールしておく。

$ brew cask install google-cloud-sdk

Google Cloud Platform にログインする

Google Cloud SDK をインストールできたら、gcloud auth login コマンドでログインしておく。

$ gcloud auth login

プロジェクトを作る

今回の動作確認用にプロジェクトを用意する。

$ gcloud projects create gce-example-$(whoami)-$(date "+%Y%m%d")

プロジェクトを作ったら、デフォルトのプロジェクトに指定する。

$ gcloud config set project gce-example-$(whoami)-$(date "+%Y%m%d")

課金と API を有効にする

続いては課金と API を有効にする。

ここだけは Google Cloud SDK からはできないのでブラウザから。 Compute Engine API を選んで有効にする。

$ open https://console.cloud.google.com/apis/dashboard

動作を確認する

API が有効になっていないと呼べないコマンドを実行してみて、上手くいけば準備完了。

$ gcloud compute instances list
Listed 0 items.

カスタムイメージを作る

下準備が終わったので、ここからはカスタムイメージを作っていく。 あらかじめ、基本的な手順について解説する。 カスタムイメージを作るときは、まず既存の任意のイメージを使って VM インスタンスを立ち上げる。 このとき、インスタンスが使うディスクには永続ディスク (Persistent Disk) を指定する。 その上で、インスタンスにソフトウェアをインストールするなどのカスタマイズを施す。 カスタマイズが終わったら、インスタンスを停止して永続ディスクを切り離す。 この切り離した永続ディスクから、カスタムイメージを作れる。 あとはカスタムイメージから VM インスタンスを立ち上げられれば作業完了。

永続化ディスクは pd- というプレフィックスから始まる。 リージョンごとに使えるディスクの種類が異なるので、確認しておく。

$ gcloud compute disk-types list | grep asia-northeast1
local-ssd    asia-northeast1-b          375GB-375GB
pd-ssd       asia-northeast1-b          10GB-65536GB
pd-standard  asia-northeast1-b          10GB-65536GB
local-ssd    asia-northeast1-c          375GB-375GB
pd-ssd       asia-northeast1-c          10GB-65536GB
pd-standard  asia-northeast1-c          10GB-65536GB
local-ssd    asia-northeast1-a          375GB-375GB
pd-ssd       asia-northeast1-a          10GB-65536GB
pd-standard  asia-northeast1-a          10GB-65536GB

各ディスクの特性については以下のページに記載がある。

cloud.google.com

各ディスクを利用するときの料金は以下。

cloud.google.com

また、これから作るイメージも保管には料金がかかる。 詳細については、以下に記載がある。

cloud.google.com

さて、早速永続ディスクを使ってインスタンスを立ち上げてみよう。 以下では 10GBpd-standard を使って Ubuntu 18.04 LTS のインスタンスを起動している。 料金を抑えるために Preemptible なインスタンスにした。

$ gcloud compute instances create gce-example \
  --preemptible \
  --zone asia-northeast1-a \
  --machine-type f1-micro \
  --image-project ubuntu-os-cloud \
  --image-family ubuntu-1804-lts \
  --boot-disk-type pd-standard \
  --boot-disk-size 10GB

上手く立ち上がれば gcloud compute instances list に表示される。

$ gcloud compute instances list
NAME         ZONE               MACHINE_TYPE  PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP   STATUS
gce-example  asia-northeast1-a  f1-micro      true         10.146.0.3   34.85.54.239  RUNNING

立ち上げたインスタンスを操作するために SSH でログインする。

$ gcloud compute ssh gce-example

ログインして lsblk コマンドを実行すると sda という名前で永続ディスクが認識されているようだ。

$ lsblk
NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
loop0     7:0    0 91.1M  1 loop /snap/core/6531
loop1     7:1    0 56.7M  1 loop /snap/google-cloud-sdk/75
sda       8:0    0   10G  0 disk
├─sda1    8:1    0  9.9G  0 part /
├─sda14   8:14   0    4M  0 part
└─sda15   8:15   0  106M  0 part /boot/efi

さて、ここからはイメージをカスタマイズするフェーズに入る。 試しにインスタンスに sl コマンドをインストールしておこう。

$ sudo apt-get update
$ sudo apt-get -y install sl
$ which sl
/usr/games/sl

カスタマイズが終わったらログアウトする。

$ exit

インスタンスを削除する。 このとき、永続ディスクについては削除されないように --keep-disks オプションを指定する。

$ gcloud compute instances delete gce-example \
  --keep-disks boot

これでインスタンスは削除された。

$ gcloud compute instances list
Listed 0 items.

ただし永続ディスクについては残っている。

$ gcloud compute disks list
NAME         ZONE               SIZE_GB  TYPE         STATUS
gce-example  asia-northeast1-a  10       pd-standard  READY

上記の永続ディスクを元にカスタムイメージを作る。 以下では ubuntu1804lts-sl という名前でカスタムイメージを作っている。

$ gcloud compute images create ubuntu1804lts-sl \
  --source-disk gce-example \
  --source-disk-zone asia-northeast1-a \
  --family ubuntu-1804-lts

次のように、カスタムイメージができた。

$ gcloud compute images list | grep gce-example-$(whoami)-$(date "+%Y%m%d")
ubuntu1804lts-sl                                      gce-example-20190321  ubuntu-1804-lts                               READY

カスタムイメージができたら、元になった永続ディスクは削除してしまっても構わない。

$ gcloud compute disks delete gce-example \
  --zone asia-northeast1-a

カスタムイメージを使ってインスタンスを立ち上げる

続いてはカスタムイメージを使ってインスタンスを立ち上げてみよう。

$ gcloud compute instances create gce-example \
  --preemptible \
  --zone asia-northeast1-a \
  --machine-type f1-micro \
  --image ubuntu1804lts-sl

起動したらインスタンスにログインする。

$ gcloud compute ssh gce-example

確認すると、たしかに sl コマンドがインストールされている。 ちゃんとカスタマイズされた状態になっているようだ。

$ which sl
/usr/games/sl

確認が終わったら、インスタンスからログアウトする。

$ exit

カスタムイメージを削除する

不要になったカスタムイメージは gcloud compute images delete で削除できる。

$ gcloud compute images delete ubuntu1804lts-sl

前述した通りカスタムイメージの保管にもお金がかかるので注意する。

後片付け

あとは後片付けもお忘れなく。

$ gcloud compute instances delete gce-example
$ gcloud projects delete gce-example-$(whoami)-$(date "+%Y%m%d")

いじょう。

Google Cloud SDK の CLI で Compute Engine を操作する

今回は Google Cloud SDK を使って CLI から Compute Engine を操作してみる。

使った環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ gcloud version                             
Google Cloud SDK 238.0.0
bq 2.0.42
core 2019.03.08
gsutil 4.37

Google Cloud SDK をインストールする

最初に Homebrew Cask を使って Google Cloud SDK をインストールしておく。

$ brew cask install google-cloud-sdk

認証する

インストールできたら gcloud auth login コマンドで Google Cloud Platform の認証をしておく。

$ gcloud auth login

プロジェクトを作る

Google Cloud Platform は何をするにしても、まずはプロジェクトが必要になる。

そこで、続いてはプロジェクトを作る。 このとき名前は GCP 上でユニークになっている必要がある。

$ gcloud projects create gce-example-$(date "+%Y%m%d")

上手く作ることができれば gcloud projects list コマンドで確認できる。

$ gcloud projects list
PROJECT_ID            NAME                  PROJECT_NUMBER
gce-example-20190318  gce-example-20190318  171069556366

操作対象とするデフォルトのプロジェクトを作成したものに設定しておくと楽ができる。

$ gcloud config set project gce-example-$(date "+%Y%m%d")

Compute Engine API を有効にする

Google Cloud SDK から Compute Engine を操作するには、はじめに API を有効にする必要がある。

そこで。以下の Web ページを開いて Compute Engine API を有効にしよう。

$ open https://console.cloud.google.com/apis/dashboard

Compute Engine API が有効にできると、関連する操作が CLI からできるようになる。

$ gcloud compute instances list
Listed 0 items.

インスタンスを起動する

準備ができたので、試しにインスタンスを起動してみよう。

インスタンスの起動には gcloud compute instances create コマンドを使う。 --preemptible オプションを指定しておくと、利用料金がだいぶ安くなる。 その代わり、突然マシンをシャットダウンされることがある点に注意が必要。

$ gcloud compute instances create gce-example \
  --preemptible \
  --zone asia-northeast1-a

インスタンスを起動するゾーンについては、以下のページを参照して選ぶ。 日本はネットワークのレイテンシが低くなる代わりに若干利用料金がお高い傾向にある。 あと、新しい機能が実装されるのも北米なんかに比べると結構遅い。

Regions and Zones  |  Compute Engine Documentation  |  Google Cloud

うまく起動できれば gcloud compute instances list で起動したインスタンスが表示される。

$ gcloud compute instances list
NAME         ZONE               MACHINE_TYPE   PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP     STATUS
gce-example  asia-northeast1-a  n1-standard-1  true         10.146.0.3   35.243.118.246  RUNNING

インスタンスに SSH でログインする

起動したインスタンスに SSH でログインするには gcloud compute ssh コマンドを使う。 最初に使うときは既存の公開鍵ペアがないか調べて、見つからないときは新たに作られる。

$ gcloud compute ssh gce-example

デフォルトでは、以下の場所に公開鍵ペアが用意される。

$ ls ~/.ssh | grep google_compute
google_compute_engine
google_compute_engine.pub
google_compute_known_hosts

上手くいけば、次のようにインスタンスにログインできる。 デフォルトでは Debian の OS イメージが使われるようだ。

$ uname -a
Linux preemptible-example 4.9.0-8-amd64 #1 SMP Debian 4.9.144-3.1 (2019-02-19) x86_64 GNU/Linux
$ cat /etc/debian_version 
9.8

ひとまずインスタンスからログアウトしておく。

$ exit

インスタンスを停止・削除する

使い終わったインスタンスは gcloud compute instances stop コマンドでシャットダウンできる。 これで Compute Engine に対する課金は停止する。 ただし、インスタンスに紐付いた永続化ストレージや IP アドレスに対する課金は継続する点に注意が必要となる。

$ gcloud compute instances stop gce-example

停止した状態のインスタンスは状態が TERMINATED と表示される。

$ gcloud compute instances list
NAME         ZONE               MACHINE_TYPE   PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP     STATUS
gce-example  asia-northeast1-a  n1-standard-1  true         10.146.0.3   35.243.118.246  TERMINATED

もし、完全に課金を停止したいときは紐付いた永続化ディスクや IP アドレスと共にインスタンスを削除する必要がある。

$ gcloud compute instances delete gce-example

マシンタイプを選ぶ

Compute Engine では必要に応じてマシンのスペックを選択できる。 先ほどは n1-standard-1 というマシンがデフォルトで選ばれていた。

選択できるマシンのスペックは、例えば以下の Web ページで確認できる。

Machine Types  |  Compute Engine Documentation  |  Google Cloud

それ以外にも gcloud compute machine-types list で確認できる。

$ gcloud compute machine-types list
NAME             ZONE                       CPUS  MEMORY_GB  DEPRECATED
f1-micro         us-central1-f              1     0.60
g1-small         us-central1-f              1     1.70
n1-highcpu-16    us-central1-f              16    14.40
n1-highcpu-2     us-central1-f              2     1.80
n1-highcpu-32    us-central1-f              32    28.80
...
n1-standard-8    northamerica-northeast1-b  8     30.00
n1-standard-96   northamerica-northeast1-b  96    360.00
n1-ultramem-160  northamerica-northeast1-b  160   3844.00
n1-ultramem-40   northamerica-northeast1-b  40    961.00
n1-ultramem-80   northamerica-northeast1-b  80    1922.00

例として、一番スペックの低い f1-micro インスタンスを使ってみよう。 このマシンタイプは CPU 1 vCore と RAM 0.6GB というスペックになっている。

$ gcloud compute machine-types list | grep asia-northeast1-a | grep f1-micro
f1-micro         asia-northeast1-a          1     0.60

起動したいマシンタイプは --machine-type オプションで選べる。

$ gcloud compute instances create gce-example \
  --preemptible \
  --zone asia-northeast1-a \
  --machine-type f1-micro

起動したインスタンスにログインする。

$ gcloud compute ssh gce-example

マシンのスペックを確認すると、たしかに CPU は 1 コアでメモリは 600MB しかない。

$ cat /proc/cpuinfo | grep processor | wc -l
1
$ cat /proc/meminfo | head -n 1
MemTotal:         606720 kB

動作確認が終わったらインスタンスを削除する。

$ exit
$ gcloud compute instances delete gce-example

OS イメージを選ぶ

続いては Debian 以外の OS イメージのインスタンスを使う方法について。

まず、公開されている OS イメージは gcloud compute images list で確認できる。

$ gcloud compute images list  
NAME                                                  PROJECT            FAMILY                            DEPRECATED  STATUS
centos-6-v20190312                                    centos-cloud       centos-6                                      READY
centos-7-v20190312                                    centos-cloud       centos-7                                      READY
coreos-alpha-2079-0-0-v20190312                       coreos-cloud       coreos-alpha                                  READY
coreos-beta-2051-2-0-v20190312                        coreos-cloud       coreos-beta                                   READY
coreos-stable-2023-5-0-v20190312                      coreos-cloud       coreos-stable                                 READY
...
sql-2017-express-windows-2019-dc-v20190225            windows-sql-cloud  sql-exp-2017-win-2019                         READY
sql-2017-standard-windows-2016-dc-v20190108           windows-sql-cloud  sql-std-2017-win-2016                         READY
sql-2017-standard-windows-2019-dc-v20190225           windows-sql-cloud  sql-std-2017-win-2019                         READY
sql-2017-web-windows-2016-dc-v20190108                windows-sql-cloud  sql-web-2017-win-2016                         READY
sql-2017-web-windows-2019-dc-v20190225                windows-sql-cloud  sql-web-2017-win-2019                         READY

今回は、その中でも Ubuntu 18.04 LTS を起動してみる。

$ gcloud compute images list | grep -i ubuntu  
ubuntu-1404-trusty-v20190315                          ubuntu-os-cloud    ubuntu-1404-lts                               READY
ubuntu-1604-xenial-v20190306                          ubuntu-os-cloud    ubuntu-1604-lts                               READY
ubuntu-1804-bionic-v20190307                          ubuntu-os-cloud    ubuntu-1804-lts                               READY
ubuntu-1810-cosmic-v20190307                          ubuntu-os-cloud    ubuntu-1810                                   READY
ubuntu-minimal-1604-xenial-v20190307                  ubuntu-os-cloud    ubuntu-minimal-1604-lts                       READY
ubuntu-minimal-1804-bionic-v20190307a                 ubuntu-os-cloud    ubuntu-minimal-1804-lts                       READY
ubuntu-minimal-1810-cosmic-v20190307                  ubuntu-os-cloud    ubuntu-minimal-1810                           READY

イメージ自体は頻繁に更新されるので、以下では --image-project--image-family を指定してインスタンスを起動している。

$ gcloud compute instances create gce-example \
  --preemptible \
  --zone asia-northeast1-a \
  --machine-type f1-micro \
  --image-project ubuntu-os-cloud \
  --image-family ubuntu-1804-lts

起動したインスタンスにログインする。

$ gcloud compute ssh gce-example

確認すると、ちゃんと Ubuntu 18.04 LTS が起動していることがわかる。

$ cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS"
$ uname -a
Linux gce-example 4.15.0-1028-gcp #29-Ubuntu SMP Thu Feb 7 18:20:08 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

確認が終わったら、忘れずにインスタンスを削除しておく。

$ exit
$ gcloud compute instances delete gce-example

とりあえず、いじょう。

Python: pandas の Series#apply() で複数カラムの特徴量を一度に作る

今回は、複数カラムの特徴量を一度に作りたいなーっていう、たまに思うやつを書く。 結論から先に書いてしまうと、返り値を Series にしてやれば良い。

使った環境は次の通り。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ python -V
Python 3.7.2
$ pip list | grep pandas
pandas          0.24.2

下準備

まずは下準備として pandas をインストールしておく。

$ pip installp pandas

サンプルデータとしてくだものの名前が入った DataFrame を用意する。

>>> import pandas as pd
>>> data = [
...   ('Apple'),
...   ('Banana'),
...   ('Cherry'),
... ]
>>> df = pd.DataFrame(data, columns=['name'])

複数カラムの特徴量を作る

例えば n-gram の特徴量をくだものの名前から抽出することを考える。

そのために、次のような n-gram を計算して配列として返す関数を定義する。

>>> def n_gram(x, n=1):
...     return [x[i : i + n] for i in range(len(x) - n + 1)]
...

これを先ほどの DataFramename カラムに適用すると、次のようになる。

>>> df.name.apply(n_gram)
0       [A, p, p, l, e]
1    [B, a, n, a, n, a]
2    [C, h, e, r, r, y]
Name: name, dtype: object

ちゃんと unigram が計算できている。

bigram を計算したいときは、次のように引数に 2 を指定すれば良い。

>>> df.name.apply(n_gram, args=(2,))
0        [Ap, pp, pl, le]
1    [Ba, an, na, an, na]
2    [Ch, he, er, rr, ry]
Name: name, dtype: object

でもこれ、めんどくさいから一度に計算したくない?ってなる。 なったときは、次のように各 n-gram を計算して Series として返すラッパーを書いてやる。

>>> def multiple_n_gram(x, n_max=3):
...     return pd.Series([n_gram(x, n=i)
...                       for i in range(1, n_max + 1)])
... 

上記のデフォルトでは trigram まで計算する。

これを、先ほどと同じように適用してみる。 すると、三次元の特徴量が一度に抽出できた。

>>> df.name.apply(multiple_n_gram)
                    0                     1                     2
0     [A, p, p, l, e]      [Ap, pp, pl, le]       [App, ppl, ple]
1  [B, a, n, a, n, a]  [Ba, an, na, an, na]  [Ban, ana, nan, ana]
2  [C, h, e, r, r, y]  [Ch, he, er, rr, ry]  [Che, her, err, rry]

次のようにカラム名のリストを渡すと、既存の DataFrame に名前をつけながら一度に挿入できる。

>>> df[['unigram', 'bigram', 'trigram']] = df.name.apply(multiple_n_gram)
>>> df
     name             unigram                bigram               trigram
0   Apple     [A, p, p, l, e]      [Ap, pp, pl, le]       [App, ppl, ple]
1  Banana  [B, a, n, a, n, a]  [Ba, an, na, an, na]  [Ban, ana, nan, ana]
2  Cherry  [C, h, e, r, r, y]  [Ch, he, er, rr, ry]  [Che, her, err, rry]

もちろん、ふつうに pandas.concat() しても良い。

n-gram の返り値を Series にしてみる

先ほどは各 n-gram を異なるカラムにしただけで、その中の処理が返す値は単なるリストだった。

続いては、試しに n-gram の処理自体が返す値を Series にしてみよう。

>>> def n_gram_series(x, n=1):
...     return pd.Series(x[i : i + n] for i in range(len(x) - n + 1))
... 

くだものの名前は固定長ではないので、返す Series の長さもまちまちになる。 どうなるだろうか。

先ほどと同じように name カラムに適用してみる。

>>> df.name.apply(n_gram_series)
   0  1  2  3  4    5
0  A  p  p  l  e  NaN
1  B  a  n  a  n    a
2  C  h  e  r  r    y

上記から、返すカラムの長さがまちまちなときは、足りない部分に NaN が補完されることがわかる。

いじょう。

Python: pandas でグループごとにデータをサンプリングする

取り扱うデータをサンプリングする機会は意外と多い。 ユースケースとしては、例えばデータが多すぎて扱いにくい場合や、グループごとに件数の偏りのある場合が挙げられる。 今回は pandas を使ってグループごとに特定の件数をサンプリングする方法について書いてみる。

使った環境は次の通り。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ python -V                 
Python 3.7.2
$ pip list | grep pandas    
pandas          0.24.2 

下準備

まずは pandas をインストールしておく。

$ pip install pandas

続いて Python のインタプリタを起動する。

$ python

そして、サンプルとなる DataFrame を用意する。 今回は野菜とくだものが入ったデータをサンプルとして使うことにした。

>>> import pandas as pd
>>> data = [
...   ('Apple', 'Fruit'),
...   ('Beetroot', 'Vegetable'),
...   ('Carrot', 'Vegetable'),
...   ('Date', 'Fruit'),
...   ('Eggplant', 'Vegetable'),
...   ('Fig', 'Fruit'),
... ]
>>> df = pd.DataFrame(data, columns=['name', 'category'])

グループを加味しないでサンプリングする

今回用意したデータは野菜とくだものが 3 つずつ入っている。

>>> df
       name   category
0     Apple      Fruit
1  Beetroot  Vegetable
2    Carrot  Vegetable
3      Date      Fruit
4  Eggplant  Vegetable
5       Fig      Fruit

これをそのままランダムサンプリングすると、野菜とくだものが均等に取り出される確率が最も高い。 とはいえ、それはあくまで期待値としての話なので実際にはそうならない場合も多い。 次のように、グループを加味しない状態で DataFrame からサンプリングすると偏ることもある。

>>> df.sample(n=4)
       name   category
0     Apple      Fruit
3      Date      Fruit
5       Fig      Fruit
1  Beetroot  Vegetable

こうなると都合が悪い。

グループを加味してサンプリングする

続いてはグループについて加味した上で特定の件数をサンプリングしてみる。

まずは DataFrame#groupby()category ごとにグルーピングする。

>>> gdf = df.groupby('category')

これで得られるのは DataFrameGroupBy というクラスのインスタンス。

>>> gdf
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x117e6e1d0>
>>> gdf.size()
category
Fruit        3
Vegetable    3
dtype: int64

DataFrameGroupByapply() メソッドを使うと、グループ単位の DataFrame に対して処理を実行できる。 結果は、全ての DataFrame が一枚に結合された状態で得られる。 つまり、DataFrameGroupBy#apply() の中で DataFrame に対して sample() メソッドを呼び出せば良い。 例えば野菜とくだものを 2 件ずつ取り出したいときは次のようにする。

>>> gdf.apply(lambda x: x.sample(n=2))
                 name   category
category                        
Fruit     0     Apple      Fruit
          5       Fig      Fruit
Vegetable 1  Beetroot  Vegetable
          4  Eggplant  Vegetable

これで、野菜とくだものが必ず毎回 2 件ずつ取り出せるようになった。

なお、ランダムサンプリングの結果を安定させたいときは random_state オプションを指定すれば良い。

>>> gdf.apply(lambda x: x.sample(n=2, random_state=42))
                 name   category
category                        
Fruit     0     Apple      Fruit
          3      Date      Fruit
Vegetable 1  Beetroot  Vegetable
          2    Carrot  Vegetable

ちなみに、元のグループの比率を維持してサンプリングしたいときは、次のように DataFrame の長さを用いると良い。

>>> gdf.apply(lambda x: x.sample(n=round(len(x) * 0.5)))
                 name   category
category                        
Fruit     0     Apple      Fruit
          3      Date      Fruit
Vegetable 4  Eggplant  Vegetable
          2    Carrot  Vegetable

MultiIndex を解除する

ちなみに、このやり方で作った DataFrame はインデックスが MultiIndex になっている。

>>> sampled_df = gdf.apply(lambda x: x.sample(n=2))
>>> sampled_df.index
MultiIndex(levels=[['Fruit', 'Vegetable'], [0, 1, 4, 5]],
           codes=[[0, 0, 1, 1], [3, 0, 1, 2]],
           names=['category', None])

これはこれでグループごとに要素を取り出しやすくて良い。

>>> sampled_df.loc['Fruit']
    name category
5    Fig    Fruit
0  Apple    Fruit

もし、元の DataFrame のフォーマットと合わせたいときには DataFrame#reset_index() を使う。

>>> sampled_df.reset_index(level='category', drop=True)
       name   category
5       Fig      Fruit
0     Apple      Fruit
1  Beetroot  Vegetable
4  Eggplant  Vegetable

サンプリングしたい件数が実在する件数よりも多いとき

先ほどのデータはグループごとに件数が均衡だった。 しかし、現実世界のデータではこのようにキレイな分布をするものはあまりない。 次は分布に偏りがあるときに生じる悩みについて考えてみる。

次のデータは、先ほどのデータからくだものを 2 つほど取り除いたものを扱う。 これで、データに含まれるくだものは Fig (いちじく) だけになった。

>>> lack_df = df[~df.name.isin(['Apple', 'Date'])]
>>> lack_df
       name   category
1  Beetroot  Vegetable
2    Carrot  Vegetable
4  Eggplant  Vegetable
5       Fig      Fruit

先ほどと同じように category の列でグルーピングする。

>>> lack_gdf = lack_df.groupby('category')
>>> lack_gdf.size()
category
Fruit        1
Vegetable    3
dtype: int64

この状態で、野菜とくだものを 2 件ずつ取り出したいという場合について考えてみる。 しかし、データの中に存在するくだものは 1 件しかないので 2 件というサンプル数に満たない。 実際に実行してみると、次のようなエラーになってしまう。

>>> lack_gdf.apply(lambda x: x.sample(n=2))
Traceback (most recent call last):
...
ValueError: Cannot take a larger sample than population when 'replace=False'

こうしたときに、データがサンプル数に満たないものは全てそのまま取り出したいのであれば、次のようにする。 具体的には、組み込み関数 min() を使って、DataFrame の長さとサンプル数の少ない方に合わせてしまう。

>>> lack_gdf.apply(lambda x: x.sample(n=min(2, len(x))))
                 name
category             
Fruit     5       Fig
Vegetable 1  Beetroot
          2    Carrot

もし、重複 (繰り返し) を許してでもサンプル数にあわせてほしいときは、次のように replace オプションに True を指定する。 こういった、本来ある件数よりも多くサンプリングする処理はオーバーサンプリングと呼ばれる。

>>> lack_gdf.apply(lambda x: x.sample(n=2, replace=True))
                 name
category             
Fruit     5       Fig
          5       Fig
Vegetable 1  Beetroot
          4  Eggplant

いじょう。

SQLite3 のテーブルに CSV でデータを読み込む

メモリに乗り切らない程度のちょっとした集計をするのに SQLite3 のデータベースを使うのが意外と便利だなーと思っている今日このごろ。 サポートされている SQL の構文や関数が少ないするのはネックだけど、まあ手軽さには変えられないという感じ。 今回は SQLite3 のテーブルに CSV のデータをインポートする方法について書いておく。

使った環境は以下の通り。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ sqlite3 --version                                        
3.24.0 2018-06-04 14:10:15 95fbac39baaab1c3a84fdfc82ccb7f42398b2e92f18a2a57bce1d4a713cbaapl

まずは読み込む CSV ファイルを用意する。

$ cat << 'EOF' > users.csv 
1,Alice,20
2,Bob,30
3,Carol,40
EOF

SQLite3 のデータベースファイルを作る。

$ sqlite3 users.db

CSV ファイルにあわせてテーブルの定義を用意する。

sqlite> CREATE TABLE IF NOT EXISTS users (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT NOT NULL,
   ...>   age INTEGER NOT NULL
   ...> );

データの区切り文字をカンマ (,) にしたら .import 命令を使って CSV ファイルをテーブルに読み込む。

sqlite> .separator ,
sqlite> .import users.csv users

ちゃんと読み込まれているか確認しよう。

sqlite> .headers on
sqlite> SELECT * FROM users;
id,name,age
1,Alice,20
2,Bob,30
3,Carol,40

ばっちり。

Python: テストで SQLite3 のインメモリデータベースを使うときの問題点と解決策

今回は SQLite3 のインメモリデータベースをテストで使うときに生じる問題点と、その解決策について。

SQLite3 のインメモリデータベースを使うと、追加でソフトウェアをインストールしたり、データベースファイルを作ることなくリレーショナルデータベースを扱うことができる。 この点はリレーショナルデータベースを扱うソフトウェアを作るときに自動テストを組む上で望ましい特性といえる。 ただ、SQLite3 のインメモリデータベースには、問題点が複数ある。 まず一つ目はコネクションを閉じると永続化した内容が消えてしまうところ。 そして、二つ目は異なるスレッドから内容を参照できないところ。

これらの問題の解決策として、無理に制約のあるインメモリデータベースを使わずテンポラリファイルを使ってディスクに永続化することを提案する。

使った環境は次の通り。

$ sw_vers          
ProductName:    Mac OS X
ProductVersion: 10.14.3
BuildVersion:   18D109
$ python -V
Python 3.7.2

インメモリデータベースの問題点について

まずはインメモリデータベースを使うときの問題点について書いていく。

Python のインタプリタを起動しておこう。

$ python

コネクションを閉じるとデータが消えてしまう問題

最初に、SQLite3 のインメモリデータベースへのコネクションを作成する。

>>> import sqlite3
>>> connection = sqlite3.connect(':memory:')

コネクションからカーソルオブジェクトを生成する。

>>> cursor = connection.cursor()

カーソルオブジェクトを使うことで SQL 文を実行できる。 まずはテーブルの作成から。

>>> create_query = """
... CREATE TABLE users (
...   id INTEGER,
...   age INTEGER NOT NULL,
...   name TEXT NOT NULL,
...   PRIMARY KEY (id)
... );
... """
>>> cursor.execute(create_query)
<sqlite3.Cursor object at 0x1037fa260>

続いて、作成したテーブルにレコードを追加する。

>>> insert_query = """
... INSERT INTO users
... VALUES
...   (1, 20, 'Alice'),
...   (2, 30, 'Bob'),
...   (3, 40, 'Carol');
... """
>>> cursor.execute(insert_query)
<sqlite3.Cursor object at 0x1037fa260>

この状態で、テーブルを SELECT する SQL 文を発行してみよう。 すると、ちゃんと永続化された内容が取得できる。

>>> select_query = """
... SELECT
...   *
... FROM users
... """
>>> cursor.execute(select_query)
<sqlite3.Cursor object at 0x1037fa260>
>>> cursor.fetchall()
[(1, 20, 'Alice'), (2, 30, 'Bob'), (3, 40, 'Carol')]

しかし、ここで一旦コネクションを閉じるとどうなるだろうか?

>>> connection.commit()
>>> connection.close()

改めてインメモリデータベースへのコネクションを開いてみよう。

>>> connection = sqlite3.connect(':memory:')
>>> cursor = connection.cursor()

そして SELECT 文を発行する。 すると、テーブルがないというエラーになってしまった。

>>> cursor.execute(select_query)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: users

このように、インメモリデータベースでは同じコネクションの中でしか永続化した内容が参照できない。

ちなみに、この問題は何もコネクションを閉じなければ問題ないかというと、そういう話でもない。 別のコネクションとして開いた場合にも問題になる。

まずはあるコネクションでテーブルを作ってレコードを追加しておく。

>>> conn1 = sqlite3.connect(':memory:')
>>> cur1 = conn1.cursor()
>>> create_query = """
... CREATE TABLE users (
...   id INTEGER,
...   age INTEGER NOT NULL,
...   name TEXT NOT NULL,
...   PRIMARY KEY (id)
... );
... """
>>> cur1.execute(create_query)
<sqlite3.Cursor object at 0x105c8b260>
>>> insert_query = """
... INSERT INTO users
... VALUES
...   (1, 20, 'Alice'),
...   (2, 30, 'Bob'),
...   (3, 40, 'Carol');
... """
>>> cur1.execute(insert_query)
<sqlite3.Cursor object at 0x105c8b260>

そして、別のコネクションを開いて SELECT 文を発行してみる。

>>> conn2 = sqlite3.connect(':memory:')
>>> cur2 = conn2.cursor()
>>> select_query = """
... SELECT
...   *
... FROM users
... """
>>> cur2.execute(select_query)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: users

すると、やはりテーブルがないとエラーになってしまう。

異なるスレッド間でデータベースを参照できない問題

続いては異なるスレッド間でデータベースを参照できない問題について。

まずは、先ほどと同じように動作確認用のデータベースを作成しておく。

>>> connection = sqlite3.connect(':memory:')
>>> cursor = connection.cursor()
>>> create_query = """
... CREATE TABLE users (
...   id INTEGER,
...   age INTEGER NOT NULL,
...   name TEXT NOT NULL,
...   PRIMARY KEY (id)
... );
... """
>>> cursor.execute(create_query)
<sqlite3.Cursor object at 0x1037fa260>
>>> insert_query = """
... INSERT INTO users
... VALUES
...   (1, 20, 'Alice'),
...   (2, 30, 'Bob'),
...   (3, 40, 'Carol');
... """
>>> cursor.execute(insert_query)
<sqlite3.Cursor object at 0x1037fa260>

続いて、次のようにデータベースのコネクションからレコードを SELECT する関数を定義する。

>>> def select(connection):
...     cursor = connection.cursor()
...     select_query = """
...     SELECT
...       *
...     FROM users
...     """
...     cursor.execute(select_query)
...     print(cursor.fetchall())
... 

上記の関数を別のスレッドから実行する。

>>> import threading
>>> t = threading.Thread(target=select, args=(connection,))

すると、次のようにエラーになる。 SQLite のオブジェクトは作成したスレッドでしか扱ってはいけない、という旨のメッセージが表示されている。 しかし、異なるコネクションを開いても内容が参照できないのは先ほど見た通り。

>>> t.start()
>>> Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/local/Cellar/python/3.7.2_2/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 917, in _bootstrap_inner
    self.run()
  File "/usr/local/Cellar/python/3.7.2_2/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 865, in run
    self._target(*self._args, **self._kwargs)
  File "<stdin>", line 2, in select
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 4478109120 and this is thread id 123145389289472.

テンポラリファイルを使った解決策について

続いては、ここまで見てきた問題の解決策について提案する。 具体的には、無理に成約あるインメモリデータベースを使うのではなく、テンポラリファイルを使ってディスクに永続化するというもの。 Python には、使い終わった際に自動で削除されるファイルを扱うためのモジュールとして tempfile が用意されている。

まずは、このモジュールをインポートする。

>>> import tempfile

名前付きテンポラリファイルのオブジェクトをインスタンス化しよう。

>>> tfile = tempfile.NamedTemporaryFile()

このオブジェクトは、作成された時点でファイルシステムのどこかに対応するファイルが作成される。

>>> tfile.name
'/var/folders/1f/2k50hyvd2xq2p4yg668_ywsh0000gn/T/tmpc08x657m'

別のターミナルから確認すると、ちゃんとファイルができていることが分かる。

$ ls -alF /var/folders/1f/2k50hyvd2xq2p4yg668_ywsh0000gn/T/tmpc08x657m
-rw-------  1 amedama  staff  0  3  3 18:36 /var/folders/1f/2k50hyvd2xq2p4yg668_ywsh0000gn/T/tmpc08x657m

このファイルは、オブジェクトが GC されると自動的に削除される。

>>> del tfile

次のように、ファイルがなくなった。

$ ls -alF /var/folders/1f/2k50hyvd2xq2p4yg668_ywsh0000gn/T/tmpc08x657m
ls: /var/folders/1f/2k50hyvd2xq2p4yg668_ywsh0000gn/T/tmpc08x657m: No such file or directory

続いては、このテンポラリファイルを使って SQLite3 の動作を確認してみよう。 テンポラリファイルのパスを使って SQLite3 のオブジェクトを作成する。

>>> tfile = tempfile.NamedTemporaryFile()
>>> connection = sqlite3.connect(tfile.name)
>>> cursor = connection.cursor()

データベースにテーブルを作って、レコードを追加する。

>>> cursor.execute(create_query)
<sqlite3.Cursor object at 0x105d1f0a0>
>>> cursor.execute(insert_query)
<sqlite3.Cursor object at 0x105d1f0a0>

終わったらコネクションを閉じてしまおう。

>>> connection.commit()
>>> connection.close()

その上で、テンポラリファイルのパスを使って再度コネクションを開く。

>>> connection = sqlite3.connect(tfile.name)
>>> cursor = connection.cursor()

そして SELECT 文を発行すると、ちゃんと先ほど永続化した内容が取れることが分かる。

>>> cursor.execute(select_query)
<sqlite3.Cursor object at 0x105edd6c0>
>>> cursor.fetchall()
[(1, 20, 'Alice'), (2, 30, 'Bob'), (3, 40, 'Carol')]

テンポラリファイルを使い終わって削除すれば、永続化に使っていた SQLite3 のデータベースファイルは自動的に削除される。

>>> del tfile

実際にテストを書くときのイメージ

もう少し具体的にテストを書くときのイメージをつけるために pytest を使った例を示す。

まずは pytest をインストールしておく。

$ pip install pytest

以下は SQLite3 を使ってリレーショナルデータベースの動作を確認するテストのサンプルコード。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import tempfile
import sqlite3

import pytest


@pytest.fixture(scope='module', autouse=True)
def temp_dbfile():
    # テンポラリファイルを用意する
    dbfile = tempfile.NamedTemporaryFile()

    # SQLite3 のデータベースファイルとして使う
    conn = sqlite3.connect(dbfile.name)
    c = conn.cursor()

    # テーブルを作る
    create_query = """
      CREATE TABLE users (
        id INTEGER,
        age INTEGER NOT NULL,
        name TEXT NOT NULL,
        PRIMARY KEY (id)
      );
    """
    c.execute(create_query)

    # データを追加する
    insert_query = """
      INSERT INTO users VALUES
        (1, 20, 'Alice'),
        (2, 30, 'Bob'),
        (3, 40, 'Carol');
    """
    c.execute(insert_query)

    # 永続化する
    conn.commit()
    conn.close()

    return dbfile


def test_example(temp_dbfile):
    # テンポラリファイルの名前を使ってデータベースを開き直す
    conn = sqlite3.connect(temp_dbfile.name)
    c = conn.cursor()

    # データベースに永続化されているユーザを取り出す
    select_query = """
    SELECT
      *
    FROM users
    WHERE
      age > 35
    """
    c.execute(select_query)
    fetched_users = c.fetchall()

    # 取り出した内容を検証する
    assert [(3, 40, 'Carol')] == fetched_users


if __name__ == '__main__':
    pytest.main(['-v', __file__])

上記の実行結果は次の通り。

$ py.test -v test_example.py 
============================= test session starts ==============================
platform darwin -- Python 3.7.2, pytest-4.3.0, py-1.8.0, pluggy-0.9.0 -- /Users/amedama/.virtualenvs/py37/bin/python3.7
cachedir: .pytest_cache
rootdir: /Users/amedama/Documents/temporary, inifile:
collected 1 item                                                               

test_example.py::test_example PASSED                                     [100%]

=========================== 1 passed in 0.04 seconds ===========================

うまくテストがパスした。

注意事項

ここまで見てきた通り SQLite3 を使うとリレーショナルデータベースの自動テストが書きやすいのは、確かにそう。 ただ、リレーショナルデータベースは使うソフトウェアによってクセが強いので、結果をあまり信用しすぎない方が良い。 例えば、SQLite3 の自動テストで動作確認しながら開発していたけど、本番で使うデータベースと結合したときに動かないみたいなことが起こりうる。 これは、SQLAlchemy のような O/R マッパーを間に挟んでいても同じ。 そのため、本番で MySQL や PostgreSQL を使うのであれば、SQLite3 の自動テストはあくまで軽い動作確認程度にとどめた方が良い。 早めに Docker などを用いて本番で使うデータベースを使った E2E テストを書いておくと、だいぶ心理的には安心できる。

いじょう。