Google Cloudのデータベースの作成

Google Cloudのデータベースを試してみよう

Google Cloudのデータベースは、クラウドでデプロイ、提供、アクセスされるデータベースです。クラウド データベースでは、従来のオンプレミス データベースと同様に、構造化データ、非構造化データ、半構造化データを整理して保存できます。そのうえ、スピード、スケーラビリティ、アジリティ、費用の削減など、クラウド コンピューティングと同じメリットも数多く提供されます。

Google Cloudのデータベースの種類

Google Cloudのデータベースは、リレーショナル データベースと非リレーショナル データベースに分類できます。

• リレーショナル データベース:
  列と行からなる 1 つ以上のテーブルで構成され、事前定義された関係に基づいてデータを整理し、管理します。

• 非リレーショナル データベース:
  メールやモバイルのメッセージ テキスト、ドキュメント、アンケート、リッチメディア ファイル、センサーデータなどの非構造化データを保存、管理します。

データベースの作成

Cloud SQL インスタンスを作成する

1. GCPからGoogle Cloud コンソールで Cloud SQL の [インスタンス] ページに移動し、[インスタンスを作成] をクリックします。

2. 以下のようにデータベースエンジンを選択。
   MySQL: 　　　 高頻度の読み取りに優れる
   PostgreSQL: 　高頻度の書き込みや複雑なクエリに優れる
   SQL Server: 　 商用DBMSとして全般的に高いパフォーマンス

選択画面

3.インスタンスの情報でインスタンス IDとrootユーザーのパスワード
（空白可）を設定。ほかは自由に設定してかまいません。
4.作成をクリック

インスタンスへの接続

1. Google Cloud Console で、右上隅の Cloud Shell アイコンをクリック。
   Cloud Shell の初期化が完了すると、次のようなメッセージが表示されます。

Welcome to Cloud Shell! Type "help" to get started.
Your Cloud Platform project in this session is set to プロジェクト名.
Use “gcloud config set project [PROJECT_ID]” to change to a different project.
ユーザー@cloudshell:~ (プロジェクト名)$ 

2.Cloud Shell プロンプトで、Cloud SQL インスタンスに接続します。次のように gcloud sql connect コマンドを使用します。

gcloud sql connect [インスタンス名] --user=root --quiet

3.root に先ほど登録したパスワードを入力します（空白の場合はEnterキーで）。
mysqlを例に以下のように表示されます。

Allowlisting your IP for incoming connection for 5 minutes...done.                                      
Connecting to database with SQL user [root].Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 79455
Server version: 8.0.31-google (Google)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

データベースの作成とデータのアップロード

1.Cloud SQL インスタンスに SQL データベースを作成します。

CREATE DATABASE book;

2.book データベースにサンプルデータを挿入。

USE book;
CREATE TABLE BOOK (bookName VARCHAR(255), price INT,
    entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));
    INSERT INTO BOOK (bookName, price) values ("book1", 1000);
    INSERT INTO BOOK (bookName, price) values ("book2", 2500);

3.データを取得3。

SELECT * FROM BOOK;

結果は以下のようになる。

mysql> SELECT * FROM BOOK;
+----------+-------+---------+
| bookName | price | entryID |
+----------+-------+---------+
| book1    |  1000 |       1 |
| book2    |  2500 |       2 |
+----------+-------+---------+
2 rows in set (0.16 sec)

SQLデータベースの削除

以下のコマンドを入力すれば削除できます。

gcloud sql databases delete [データベース名] --instance=[インスタンス名]

余談：CSVファイルをMySQLにインポートする方法

MySQLは高速で信頼性の高いデータベース管理システムですが、データのインポートにはいくつかの手順が必要です。ここでは、CSVファイルをMySQLにスムーズにインポートする方法を解説します。

MySQLサーバーのインストールと起動

まず最初に、MySQLサーバーがインストールされていることを確認します。インストールされていない場合は以下のコマンドでインストールします。

sudo apt-get update 
sudo apt-get install mysql-server

インストール後、rootユーザーに切り替えてMySQLサーバーを起動します。

sudo su -
sudo service mysql start

MySQLクライアントの起動とlocal_infileの有効化
CSVファイルをインポートするには、local_infileを有効にしてMySQLクライアントを起動する必要があります。

sudo mysql --local-infile=1 -u root -p

MySQLクライアント内で以下のコマンドを実行し、local_infileをグローバルに有効化します。

SET GLOBAL local_infile = 1;

データベースとテーブルの準備
CSVデータをインポートする先のデータベースとテーブルを用意します。新規のデータベースを作成する場合は以下のコマンドを実行します。

CREATE DATABASE example;

既存のデータベースを使う場合はUSE文で選択します。

USE example;

データセット準備
今回使うデータ例は下のリンクからの入手できます。kaggleさん本当にありがたい。

USA House Prices

データベースを選択した後、CSVデータの構造に合わせてテーブルを作成します。例として以下のようなスキーマを定義します。

CREATE TABLE house_sales (
    sale_date DATE,
    price INT,
    bedrooms INT,
    bathrooms DECIMAL(3,2),
    sqft_living INT,
    sqft_lot INT,
    floors INT,
    waterfront INT,
    view INT,
    house_condition INT,
    sqft_above INT, 
    sqft_basement INT,
    yr_built INT,
    yr_renovated INT,
    street VARCHAR(255),
    city VARCHAR(255),
    statezip VARCHAR(255),
    country VARCHAR(255)
);

CSVファイルのアップロードとインポート
いよいよCSVファイルをMySQLにインポートします。まずCSVファイルをサーバーにアップロードします。例えばGoogle Cloud Shellの場合、右上のメニューからファイルをアップロードできます。

ファイルを選択し、保存場所を指定

アップロード後、LOAD DATAコマンドでCSVファイルの内容をテーブルにロードします。

LOAD DATA LOCAL INFILE 'home/[ユーザー名]/USA_Housing_Dataset.csv'
INTO TABLE house_sales
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

これでCSVファイルのMySQLへのインポートは完了です。
内容を確認するときは以下のコードを入力してください。

SELECT * FROM house_sales;

余談：CSVファイルからMySQLテーブルを自動生成する方法

CSVファイルをMySQLにインポートする際、テーブルスキーマを事前に用意するのが面倒な場合もあります。そんなときは、AIを活用してスキーマを自動生成したり、MySQLの機能を使ってCSVファイルからテーブル構造を推測するのも一つの手です。

CREATE TABLE new_table 
AS SELECT * FROM 
    (SELECT * FROM 
        (SELECT * FROM 
            (SELECT 'col1,col2,col3') AS tmp) 
        AS s1 
    WHERE s1.col1 NOT LIKE '%col1%') AS s2
UNION ALL SELECT * FROM 
    (SELECT * FROM 
        mytable.`/path/to/file.csv`) AS s3;

このクエリでは、CSVファイルのヘッダー行からカラム名を抽出し、そのカラム構造を持つ新しいテーブルを作成します。カラムのデータ型は自動的に推測してくれる。ただし、この方法にもいくつか注意点があります。

• 推測されるデータ型が期待通りでない場合がある

• 文字コードの不一致でエラーになりやすい

使い方を誤るとデータ型のミスマッチなどのトラブルに繋がるので、慣れないうちは通常の方法で事前にスキーマを定義するのがおすすめ。でも選択肢の一つとして覚えておくと便利なのかもしれない。

参考

クイックスタート: Cloud Shell から Cloud SQL for MySQL に接続する  |  Google Cloud