見出し画像

Google ColaboratoryでSQL演習環境を作る

根元@ルートアシスト

はじめに

SQLの演習を行おうとすると、データベースサーバーのインストールなどの環境整備が少し面倒です。
Google Colaboratoryの環境を活用することで、ブラウザのみでSQLを実行を試すことができるようになります。
データベースの環境設定を行う必要もなく、またタブレットなどでも実行可能です。Google Colaboratoryを利用するためにはGoogleアカウントが必要です。

Google Colaboratoryを利用するためにはGoogleアカウントが必要です。

そのサンプルであるNoteBookをGitHubで公開しています。

この記事では、その具体的なサンプルの使い方を説明します。

同様の環境で「データサイエンス100本ノック（構造化データ加工編）」をGoogle Colaboratoryで実行する環境も提供しています。併せてご活用ください。
https://note.com/nmt_rootassist/n/nf70b6e73f673
https://github.com/rootassist/100knocks-preprocess-inSQLandPython-withColab

提供しているNoteBookについて

提供しているNoteBookは、

  • データベースサーバーの種類の違い（PostgreSQL、MySQL）

  • データベースを構築するために必要なファイルを得る方法の違い

により、以下の３種類を提供しています。適宜カスタマイズしてご利用下さい

Google Colaboratoryは起動するたびに環境が初期化されるため、そのたびにデータベースサーバーを起動し、SQL実行のターゲットとなるデータベースを構築する必要があります。「構築に必要なファイル」とは、DDLを記述したファイルや、テーブルにインポートするデータのCSVファイルなどです。

  1. PostgreSQL: データベースを構築に必要なファイルを「GitHubのリポジトリ」から得る

  2. PostgreSQL: データベースを構築に必要なファイルを「Google Drive」から得る

  3. MySQL: データベースを構築に必要なファイルを「サイト」から得る

PosgreSQL：構築用のファイルをGitHubのリポジトリから得る

  • データベースサーバー：PostgreSQL13

  • ファイルを得る方法：GitHubのリポジトリ（Pagila）

「構築に必要なファイル」をGitHubのリポジトリに置くためにはGitHubアカウントが必要です。
ただしこのサンプルではPagilaのリポジトリから得るためにアカウントを用意する必要はありません。

PostgreSQL：構築用のファイルをGoogle Driveから得る

  • データベースサーバー：PostgreSQL13

  • ファイルを得る方法：Google Drive

「構築に必要なファイル」として、「world.sql」というファイルをGoogle Driveの「マイドライブ」の下にある「DBSample」フォルダーからコピーするように設定しています。事前にそのフォルダーへファイルを置いてください。
「world.sql」の入手方法は、「技術的な補足事項」を参照してください。

また、ファイルをGoogle Driveに置いて利用する場合には、Googleアカウントに対してドライブへの接続を許可する必要があります。許可しなければ利用することができません。（許可する手順は「使い方」に書いています）

MySQL：構築用のファイルをサイトからダウンロードする

  • データベースサーバー：MySQL

  • ファイルを得る方法：サイト（MySQLの公式サイトにあるサンプルデータベースSakila）

「構築に必要なファイル」として、データベースSakilaの構築ファイルを以下のURLから/tmp/dataにダウンロードし、展開して利用しています。
http://downloads.mysql.com/docs/sakila-db.zip

使い方

1. 初回実行時

1-1. 以下のリンクをクリックする

「PosgreSQL：構築用のファイルをGitHubのリポジトリから得る」
https://github.com/rootassist/SQL_ExecEnv_ForColab/blob/main/Pagila_Env_FromGitHub_ForColab.ipynb

「PostgreSQL：構築用のファイルをGoogle Driveから得る」
https://github.com/rootassist/SQL_ExecEnv_ForColab/blob/main/World_Env_FromGoogleDrive_ForColab.ipynb

「MySQL：構築用のファイルをサイトからダウンロードする」
https://github.com/rootassist/SQL_ExecEnv_ForColab/blob/main/Sakila_Env_FromGitHub_ForColab.ipynb

1-2. ファイル上部の「Open in Colab」をクリックする

1-3. NoteBookを自身のGoogleアカウントのGoogleドライブに保存する

メニューの「ファイル」→「ドライブにコピーを保存」を選択すると、「マイドライブ」フォルダーの下の「Colab Notebooks」フォルダーにコピーされる。

コピーしなくても実行できますが、解答したNoteBookを保存できません。

1-4. 新しいタブが開き、Googleドライブに保存されたNoteBookが表示される。

NoteBookの名前をクリックすると変更できます。

1-5.「#環境構築用セル」と書かれているセルを実行する

最初にセルを事項するときに以下の警告が表示されますが、「このまま実行」をクリックします。
また、環境構築のために1分程度の時間が必要です。

「Google Driveから得る」場合、Google Driveへのアクセスの許可を求めるダイアログボックスが表示されます。
以下の画面では「Googleドライブに接続」をクリックしてください。

次に表示される画面で、接続するGoogle Driveのアカウントを選択してください。

次に表示される画面で、必要となるアクセス権を確認し、問題なければ「許可」をクリックしてください。
（許可しない限りGoogle Driveと接続することはできません）

1-6. 演習を始める

1-7. 結果を保存する

メニューの「ファイル」→「保存」をクリックする。

1-8. 演習を終了する

ブラウザのタブを閉じればよい。(NoteBookは事前に保存しておくこと)

2.再開時

2-1. Googleドライブに保存したNoteBookを開く

NoteBookは「マイドライブ」フォルダーの下の「Colab Notebooks」フォルダーに保存されている。
ダブルクリックするとGoogle Colaboratoryで開く。

2-2. 「#環境構築用セル」と書かれているセルを実行する

再開時には環境が消えているために、再度実行する必要があります。

2-3. 演習を再開する

2-4. 演習を終了する

ブラウザのタブを閉じればよい。(NoteBookは事前に保存しておくこと)

出力結果の表示方法（インタラクティブ・テーブル）

インタラクティブ・テーブルの機能を使うことで、出力結果の並び替え、フィルタリング（条件で表示行を絞り込み）、ページネーション（ページに分割して表示）、ファイル出力（CSV）などができるようになります。
例えば、行数、列数が多い出力結果の確認などで利用できます。

出力の表示に以下のアイコンが表示されますので、それをクリックするとインタラクティブ・テーブルの表示に切り替わります。

インタラクティブ・テーブルはGoogle Colaboratoryの機能で、pandasのデータフレームに対する機能です。このNoteBookでは結果をpandasのデータフレームに出力しているために、利用できるようになっています。

インタラクティブ・テーブルの使い方については以下のページを参考にしてください。
https://atmarkit.itmedia.co.jp/ait/articles/2205/16/news029.html

技術的な補足事項

Colaboratoryでの環境構築に関する技術的な補足です。
演習には必要ありませんので、不要ならば読み飛ばして構いません。

PostgreSQLのバージョンは13

NoteBookで起動しているPostgreSQLサーバーのバージョンは13です。Google Colaboratoryでインストールできるのは10ですが、grantでpg_read_server_files、pg_write_server_filesを指定出来ないため、リポジトリに追加した上でインストールしています。

また、サーバーは以下のポート、ユーザーで起動しています。
- Port:5432
- ユーザ名:padwan
- パスワード:padwan12345

MySQLのバージョンは5.7

Google Colaboratoryでインストールできるのは5.7ですが、PostgreSQLのように新たに8.0を追加しインストールする方法を見つけられていません。

サーバーは以下のユーザーで起動しています。
- Port:3306
- ユーザ名:root
- パスワード:root

データベース「Pagila」について

「GitHubから得る」NoteBookで使用しているデータベース「Pagila」は、MySQLのサンプルデータベース「Sakila」をPostgreSQLに移植したものです。
以下のGitHubリポジトリで公開されており、このNoteBookでも構築用SQLファイルである「pagila-schema.sql」「pagila-data.sql」をこのリポジトリから得ています。
https://github.com/devrimgunduz/pagila

データベース「world」について

「Google Driveから得る」NoteBookで使用しているデータベース「World」は、PosgreSQLのサンプルデータベースとして以下のURLで公開さているものを利用しています。
https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/world/world-1.0/

構築用SQLファイル「world.sql」は、上記のURLにアクセスして「world.tar.gz」をダウンロードし、これを展開すると得られますので、これをGoogle Driveの「マイドライブ」の下にある「DBSample」フォルダーに置いてください。

.tar.gzファイルの展開には、Windowsでは「7-zip」などを利用してください。

なお、「world.sql」は筆者が公開しているリポジトリにも同じものを置いています。
以下のURLをクリックし、「Download」のボタンをクリックすると「world.sql.zip」がダウンロードされますので、これを展開して得られた「world.sql」をGoogle Driveの「DBSample」フォルダーに置いてください。

https://github.com/rootassist/SQL_ExecEnv_ForColab/blob/main/world-1.0/dbsamples-0.1/world/world.sql.zip

作業用フォルダは/tmp下が使用できる

ファイル出力が必要な場合には、ディレクトリ「/tmp」の下が利用できます。
また、「Google Driveから得る」パターンでは、ファイルを一旦「/tmp/data」の下にコピーしてから使用しています。
（この環境ではGoogle Driveにアクセスできるのはルートユーザーだけのため）

いいなと思ったら応援しよう！