Snowflake 30日トライアル申込み前に読む記事

まずはSnowflakeドキュメントを読むことをおすすめしますが、手っ取り早く概要を掴みたい場合、情報が誤っている可能性もありますが、ご自身の責任と判断のもとでお役立てください。執筆時点の個人が集めた情報になります。

Snowflakeとは

クラウド上(AWS, Azure, GCP)で動くSaaSのデータウェアハウス。ハードやソフトの導入、管理も不要なので、申込んですぐに利用が開始できる。

これまでの似たような製品と異なるのは、シェアード・エブリシング、シェアード・ナッシングのいいとこ取りをしており、コンピューティングリソースとストレージリソースを分離して管理しているので、必要なタイミングで負荷対策ができる。

2020年にIPOしている。

• 柔軟な負荷対策

  • ストレージが分離しており、ウェアハウス内のSQL処理を行うクラスタのスペック変更が可能。ウェアハウスは追加することも可能で、あわせてクラスタのスペックも変更できる。また、マルチクラスタ設定で負荷に応じてクラスタを増やして処理ができる。

• パフォーマンス

  • クラスタが独立しているので、並列に処理が可能。キャッシュも利用できるので、計算の必要ないものは、キャッシュから結果を得られる。メタデータや計算結果などがこれにあたる。ちなみにパーティションも行ってくれる。

• データ管理

  • データシェアリングを使えば、異なる組織でデータが共有できる。また、データのレプリケーションもできる。ゼロコピークローニングで開発環境から本番環境に即座に移行可能。あとはAthenaなどにもあるタイムトラベル機能もある。

• 柔軟な課金料金

  • ウェアハウスの稼働時間に応じて費用が発生する。

エディションの違い

スタンダードが基本的なエディションになり、エンタープライズでは「マルチクラスタ」「マテリアライズドビュー」「検索最適化サービス」などの機能が追加されている。

ビジネスクリティカルでは「PrivateLink」などのセキュリティ関連が強化されており、バーチャルプライベートでは「顧客専用の仮想サーバー」とあるように、より強固なセキュリティのもとでデータウェアハウスを構築できる。

トライアル申し込み

公式サイトのトライアル申し込みを行うとトライアルがすぐに可能になる。ここではAWS環境のエンタープライズエディションでトライアルを開始する。メールが届くので、アカウントのアクティベーションを行うと、ユーザー作成画面に遷移して、ログインができるようになる。

アクティベーション用のメール内にSnowflakeにアクセスするアカウント専用URLが記載されているので注意。

「30日間の無料トライアルを開始する」と書いてあったが、表示は58日になっている理由は知らない。

ユーザーインターフェース

画面上側にあるユーザーインターフェースの項目について簡単にまとめておく。

ワークシート

SQLを記述してデータを処理するためのスペース。画面左側には、データベース、スキーマ、テーブル、カラムが表示される。左下にはテーブルのレコード数やカラムの情報が表示される。

ここでは、デフォルトで用意されているTCPDS(DBのベンチマークでよく使われているデータ)のサンプルデータを利用する。

「TPCDS_SF100TCL」という数字の100は、相対的なデータのサイズを表現しており、「TPCH_SF1000.LINEITEM」テーブルは「5,999,989,709 行159.3 GB」という、なかなかのサイズのサンプルデータが格納されている。

画面右上の部分で、ロールの変更、データウェアハウスのクラスタ設定、データベース、スキーマ変更が可能。

ウェアハウス

作成したウェアハウスが確認できる。「構成」のボタンを押すと、ウェアハウスの構成を確認できる。クラスタサイズの変更やマルチクラスタで使用する最大クラスタ数、スケーリングポリシー、自動一時停止の設定が変更できる。動かしていないと自動一時停止で設定した時間で停止するので、クレジットが利用されず、お財布にも優しい。自動再開ボタンは、停止中にクエリが発行されると自動でウェアハウスの起動を再開するという設定。

クラスタサイズは課金に大きく影響するので注意。XSは一番小さいクラスタであり、5XLが一番大きいサイズ。時間あたり1クレジットと256クレジットの違いがあるが、簡単説明すると、同じ量の仕事をする場合に、高単価、最小時間をかけて処理するか、低単価、最大時間をかけて処理するか、というような違いがある。

ウェアハウスを複数作成して、異なるサイズのクラスタを割り当てておけば、利用者の目的に応じたウェアハウスを作成できる。

データベース

Snowflakeに保存しているデータベース、テーブル、ビュー、スキーマの作成、確認できる。ここかたデータベースの作成やテーブル定義なども行えるが、ワークスペースでSQLを使うほうが楽かも。

「ステージ」というのは、ファイルからテーブルにデータをロードする際に、ステージを作ることができ、それを管理するもの。「ファイル形式」はステージにあるデータの形式を確認できる。「パイプ」は、ステージのデータを検知して、ファイルをテーブルにロードする機能を管理することができる。

共有

アクセスするには「アカウント管理者に連絡してください。」とあるようにACCOUNTADMIN権限でなければ使用できない機能。この機能を使うことで、共有されているデータを使用したり、他のユーザーにデータを提供したりできる。

左上のタブが「インバウンド」になっている場合、外部の組織から共有されているデータベースが表示されている。デフォルで用意されているサンプルデータもインバウンドで共有されているデータとわかる。「アウトバウンド」では、このアカウントから外部のアカウントに共有しているデータベースを管理できる。

インバウンドは提供されていデータベース、アウトバウンドは共有しているデータベースということになる。

データマーケットプレイス

データマーケットプレイスでは、データを販売している企業のデータや、パブリックで公開されているデータをデータベースとして取り込むことができる機能。「Snowflake Data Marketplaceを探索」をクリックして進むと、共有されているデータの一覧が確認でき、画像の例は、Knoema社が公開しているCOVID-19 Data Atlasという無料のデータの詳細画面。基本的には有償のものが多いのかもしれない。

履歴

ここでは実行されたクエリの詳細(過去2週間分)を確認できる。正確な表現ではないかもしれないが、スキャンを必要としないDDL、DCL系のコマンドはスキャンの数値の表示がない。一方で、DML系のコマンドは同じコマンドであってもキャッシュから情報が取れる場合は、スキャンされない場合もあるのでスキャンサイズは異なる。クラスタ、サイズ列に情報がある場合、ウェアハウスのリソースを消費したことになる。

「クエリID」をクリックするとSQLの処理の詳細が確認できる。SQL文や視覚化された実行計画も確認できる。

アカウント

ACCOUNTADMIN権限でなければ使用できない機能。ここでは、Snowflakeの使用状況、請求情報、ユーザー管理、ロール管理、ネットワークポリシー、リソースモニター、リーダーアカウントなどの管理ができる。リーダーアカウントは読み取り専用ユーザーのことで、組織外のユーザーにアカウントを発行できる。このアカウントを通じて、データを共有できたりする。このアカウントのクレジットが消費された場合、発行した側が受け持つ。

セッション

このアカウントのSnowflake環境に対して、誰がいつ、どのようなデバイスで、どのような認証方法を使ってセッションを確立したかを確認できる。

ネットワークポリシー

ネットワークポリシーを使うことで、このSnowflakeアカウントの環境に対して、特定のIPアドレスからのアクセスしか受け付けないように設定できる。

リソースモニター

クレジットの消費量に対して、停止や通知などの管理ができる。現状の使用量を把握したい、予算以上使いたくない場合は、この機能の設定は必須である。

ロール

画面右上のアカウントボタンのところから変更ができる。

• ACCOUNTADMIN(アカウント管理者)：最上位の権限をもつロール。
• SYSADMIN(システム管理者)：ウェアハウスとデータベースを作成する権限をもつロール。
• SECURITYADMIN(セキュリティ管理者)：オブジェクトの付与をグローバルで管理して、ユーザーとロールを管理できる。
• USERADMIN(ユーザー管理者)：ユーザーとロールの管理に使用するロール。
• PUBLIC：アカウントのすべてのユーザー、ロールに自動付与される最下層のロール。
• ORGADMIN(組織管理者)：組織を管理するためのロール。

データウェアハウスの作成

ここではデータウェアハウスの作成からSQLの発行までを行う。まずは「データウェアハウス」に移動して、作成をクリック。ここではNYの黄色タクシーのトリックプデータを利用する。

下記のSQLでも同じ事ができる。

CREATE WAREHOUSE yellow_taxi_wh 
WITH WAREHOUSE_SIZE = 'XSMALL' 
WAREHOUSE_TYPE = 'STANDARD' 
AUTO_SUSPEND = 300 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY = 'STANDARD';

yellow_taxi_whウェアハウスを作成後はパブリックのユーザーが使えるように権限を与えておく。

データベース、テーブルの作成は下記のSQLを「ワークスペース」で実行することで作成する。yellow_taxiデータベースのPUBLICスキーマーに、triplogsテーブルを作成する。

CREATE DATABASE yellow_taxi;
GRANT USAGE ON DATABASE "YELLOW_TAXI" TO ROLE "PUBLIC";
CREATE TABLE "YELLOW_TAXI"."PUBLIC"."triplogs" (
  VendorID bigint, 
  tpep_pickup_datetime timestamp, 
  tpep_dropoff_datetime timestamp, 
  passnger_count integer, 
  trip_distance double, 
  RatecodeID integer, 
  store_and_fwd_flag string, 
  PULocationID integer, 
  DOLocationID integer, 
  payment_type integer, 
  fare_amount double, 
  extra double, 
  mta_tax double, 
  tip_amount double, 
  tolls_amount double, 
  improvement_surcharge double, 
  total_amount double, 
  congestion_surcharge double)

// ダブルクオーテーションで囲む
// drop table "YELLOW_TAXI"."PUBLIC"."triplogs";

csvのインサートは、「データベース」から「テーブル」を指定してインサートを行う。

データをロードするためには、「ウェアハウス」「ソースファイル」「ファイル形式」「オプション」などを設定する必要がある。データのロードが完了したら「ワークスペース」からSQLを発行する。

参考サイト

• Snowflakeドキュメント

Fighting Churn With Dataのシミュレーションデータの生成方法[PostgreSQL]

SaaSサンプルデータ生成のまとめ

"Fighting Churn With Data: Science and strategy for keeping your customers"というSaaSデータの分析をまとめたCarl S. Goldさんの書籍のサンプルデータを生成するまでの流れをまとめたもの。下記のYouTubeにある動画でも解説されているが、私の理解が足りず、いくつか躓いた部分があったので、メモしておく。

• https://www.youtube.com/watch?v=8rwRWzuGBbc

リポジトリのクローン

まずは下記のリポジトリをダウンロードしてデスクトップにでもおいておく。

% git clone https://github.com/carl24k/fight-churn.git

• fight-churn

PostgreSQLとPyCharmのインストール

• Python 3.9
  • pip3 install -r requirements.txtでパッケージをインストール(リポジトリにおいてある)
• PyCharm
  • Community EditionでOK
• PostgreSQL
  • データベース(churn)を作成する。スキーマーをやテーブル定義はPythonファイルで行うので必要なし。

スキーマの作成

次はスキーマーを作成する。まずはPyCharmを起動して、クローンしてきたfight-churn-masterをプロジェクトに指定して開く。スキーマーを作成するPythonファイルはchurndb.py、データを生成するのはchurnsim.py。

churndb.pyにはschema_name='socialnet7'がデフォルトのスキーマー名として設定されている。変更する必要がなければ、このままにしておく。コンフィグレーションファイルの設定を行い、スクリプトパスは下記を設定する。

• /Users/<name>/Desktop/fight-churn-master/fightchurn/datagen/churndb.py

コンフィグレーションファイルの設定でPostgreSQLへの接続設定をEnvironmentに記載する。

• CHURN_DB: churn
• CHURN_DB_USER: PostgreSQLに接続するユーザー名
• CHURN_DB_PASS: PostgreSQLに接続するユーザーのパスワード
• CHURN_OUT_DIR: ログを書き出すディレクトリ

これらの設定が終わったらchurndb.pyを実行する。実行完了後、PostgreSQLにchurn.socialnet7という形でテーブルが複数作成されている。

シミュレーションデータの生成

次はテーブルにシミュレーションデータをインサートする。使用するファイルはchurnsim.py。churnsim.pyのmain関数のところで、シミュレーションする期間やユーザー数が指定できるが、ここもそのままでよければ変更しない。デフォルトの6ヶ月の設定でも、12GBくらいのデータが生成される。スペックにもよるが私のPCでは1時間程度かかった。シミュレーションの結果はsubscriptionとeventに挿入される。accountやevent_typeにもデータはインサートされる。

コンフィグレーションファイルの設定を行い、スクリプトパスは下記を設定する。

• /Users/<name>/Desktop/fight-churn-master/fightchurn/datagen/churnsim.py

コンフィグレーションファイルの設定でPostgreSQLへの接続設定をEnvironmentに記載する。

• CHURN_DB: churn
• CHURN_DB_USER: PostgreSQLに接続するユーザー名
• CHURN_DB_PASS: PostgreSQLに接続するユーザーのパスワード
• CHURN_OUT_DIR: ログを書き出すディレクトリ

実行すると下記のログが出力される。

Creating 10000 initial customers for month of 2020-01-01
Simulated customer 0/10000: 4 subscriptions & 108 events
Simulated customer 100/10000: 453 subscriptions & 132,390 events
[略]

[略]
Simulated customer 1300/1331: 1,329 subscriptions & 383,867 events
Created 1331 new customers for month 2020-05-01, now 55231 subscriptions

Process finished with exit code 0

データがインサートされているか確認しておく。

% qsql -U postgres -d churn -W 

churn=# SELECT COUNT(1) FROM socialnet7.subscription;
 count 
-------
 55231
(1 row)

churn=# SELECT * FROM socialnet7.subscription LIMIT 10;
 id | account_id |   product    | start_date |  end_date  | mrr  | quantity | units | bill_period_months 
----+------------+--------------+------------+------------+------+----------+-------+--------------------
  0 |          1 | 'socialnet7' | 2020-01-13 | 2020-02-13 | 9.99 |          |       |                  1
  1 |          1 | 'socialnet7' | 2020-02-13 | 2020-03-13 | 9.99 |          |       |                  1
  2 |          1 | 'socialnet7' | 2020-03-13 | 2020-04-13 | 9.99 |          |       |                  1
  3 |          1 | 'socialnet7' | 2020-04-13 | 2020-05-13 | 9.99 |          |       |                  1
  4 |          2 | 'socialnet7' | 2020-01-04 | 2020-02-04 | 9.99 |          |       |                  1
  5 |          2 | 'socialnet7' | 2020-02-04 | 2020-03-04 | 9.99 |          |       |                  1
  6 |          2 | 'socialnet7' | 2020-03-04 | 2020-04-04 | 9.99 |          |       |                  1
  7 |          2 | 'socialnet7' | 2020-04-04 | 2020-05-04 | 9.99 |          |       |                  1
  8 |          2 | 'socialnet7' | 2020-05-04 | 2020-06-04 | 9.99 |          |       |                  1
  9 |          3 | 'socialnet7' | 2020-01-10 | 2020-02-10 | 9.99 |          |       |                  1
(10 rows)

churn=# SELECT COUNT(1) FROM socialnet7.event;
  count   
----------
 16995997
(1 row)

churn=# SELECT * FROM socialnet7.event LIMIT 10;
 account_id |     event_time      | event_type_id 
------------+---------------------+---------------
          1 | 2020-01-14 20:03:13 |             4
          1 | 2020-01-15 04:42:10 |             4
          1 | 2020-01-16 11:57:11 |             0
          1 | 2020-01-17 07:16:53 |             0
          1 | 2020-01-17 00:23:51 |             4
          1 | 2020-01-17 03:39:04 |             4
          1 | 2020-01-20 23:59:07 |             4
          1 | 2020-01-22 16:20:53 |             2
          1 | 2020-01-23 03:04:57 |             2
          1 | 2020-01-25 02:40:13 |             2
(10 rows)

churn=# SELECT * FROM socialnet7.event_type;
 event_type_id | event_type_name 
---------------+-----------------
             0 | post
             1 | newfriend
             2 | like
             3 | adview
             4 | dislike
             5 | unfriend
             6 | message
             7 | reply
(8 rows)


churn=# SELECT COUNT(1) FROM socialnet7.account;
 count 
-------
 14641
(1 row)

churn=# SELECT * FROM socialnet7.account LIMIT 10;
 id |  channel  | date_of_birth | country 
----+-----------+---------------+---------
  1 | appstore1 | 2002-12-05    | US
  2 | appstore2 | 1971-07-24    | NZ
  3 | appstore2 | 1949-02-17    | GB
  4 | appstore1 | 1987-09-23    | NZ
  5 | appstore1 | 1972-07-11    | CA
  6 | appstore2 | 1976-10-25    | IN
  7 | appstore2 | 1994-04-19    | SE
  8 | web       | 1958-05-16    | CN
  9 | web       | 1976-03-10    | AR
 10 | appstore2 | 1973-11-26    | CA
(10 rows)

下記のER図は私が勝手に作ったもの。多分こんな感じだと思われる。そして、dbdiagramすごく便利。

別のサーバに移動する必要があったので、ダンプファイルを作成して、インポート。

[PC1]
% pg_dump churn > ~/Desktop/churndump

[PC2]
$ psql -U aki -d postgres -W
>> OSのスーパユーザーのパスワードが必要

postgres=> CREATE DATABASE churn
postgres=> quit

$ psql churn < ~/Desktop/churndump
>> OSのスーパユーザーのパスワードが必要

# workerにデータベースとテーブル権限を付与
# もっと効率的かつセキュアにやれるっぽいが、ここらへんの理解が足りてない。
$ \c churn
>> OSのスーパユーザーのパスワードが必要

churn=# GRANT ALL PRIVILEGES ON DATABASE churn TO worker;
churn=# GRANT ALL ON TABLE socialnet7.account TO worker;
churn=# GRANT ALL ON TABLE socialnet7.active_period TO worker;
churn=# GRANT ALL ON TABLE socialnet7.active_week TO worker;
churn=# GRANT ALL ON TABLE socialnet7.event TO worker;
churn=# GRANT ALL ON TABLE socialnet7.event_type TO worker;
churn=# GRANT ALL ON TABLE socialnet7.metric TO worker;
churn=# GRANT ALL ON TABLE socialnet7.metric_name TO worker;
churn=# GRANT ALL ON TABLE socialnet7.observation TO worker;
churn=# GRANT ALL ON TABLE socialnet7.subscription TO worker;

以上でおわり。

MacでPostgreSQL(version14)のデータ分析環境の構築からサンプルデータを投入するまでのメモ

過去に似たような記事がたくさん作ってしまっているが、これが現状最新。

インストールから初期設定

まずは環境構築。ここではMacOSにPostgreSQL(v14)をインストールしていく。Dockerとかでもいいんだけども。

## 過去のファイルを削除
$ brew uninstall postgresql
$ rm -rf /usr/local/var/postgres/

## brewでインストール
$ brew install postgresql
$ psql --version
psql (PostgreSQL) 14.1

サーバーの起動・停止コマンドは下記の通り。

$ brew services start postgresql
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)


# サーバーの停止コマンド
# brew services stop postgresql
# Stopping `postgresql`... (might take a while)
# ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)

.bash_profileに下記のPATHを追加して、更新しておく。PATHはPostgreSQLコマンドのパスで、PGDATAはデータベースクラスタのパス。

# vimとかでもなんでもよい
$ open ~/.bash_profile

#-----------------------------------
# Setting PATH for postgres14.1.1
export PATH=/usr/local/Cellar/postgresql/14.1_1/bin/:$PATH
export PGDATA=/usr/local/var/postgres
#-----------------------------------

$ source ~/.bash_profile

データベースクラスタというのはPostgreSQLのデータの記憶領域のこと。ファイルシステム上にディレクトリが作成され管理されていく。

initdbで初期化

initdbでデータベースクラスタを作成。初期のデータベースクラスタはpostgres、template0、template1という3つのデータベースしか存在していない。-Eはエンコーディングの設定。

$ initdb /usr/local/var/postgres -E utf8
## initdb したときにエラーになる場合は/usr/local/var/postgresディレクトリを削除
## 最初にpostgresディレクトリをまるごと削除しているのになんでだろうか。

$ ls $PGDATA
PG_VERSION      pg_commit_ts        pg_ident.conf       pg_notify       pg_snapshots        pg_subtrans     pg_wal          postgresql.conf
base            pg_dynshmem     pg_logical      pg_replslot     pg_stat         pg_tblspc       pg_xact         postmaster.opts
global          pg_hba.conf     pg_multixact        pg_serial       pg_stat_tmp     pg_twophase     postgresql.auto.conf    postmaster.pid

postgresql.confの中身の確認

今回の環境構築では必要ないけれど、postgresql.confには、リッスンアドレスやポート番号、ログの設定などが記載されているのでメモ。

$ cat $PGDATA/postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
【略】

インストール直後はMACのユーザ名でスーパユーザー(aki)が作成される。下記に書いてある。

• 21.1. データベースロール

## MACのユーザ名でスーパーユーザーが作られる
## $ echo $USER
## aki
## DBにアクセスする際のオプション
## `-h`はホスト、`-p`はポート番号、`-U`はユーザー名、`-d`は接続先DB、`-W`はパスワードの指定。
## psql -h 127.0.0.1 -p 5432 -U aki -d postgres
$ psql -U aki -d postgres

psql (14.1)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 aki       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

スーパーユーザー(aki)しかいないので、管理用のロール(worker)を作成する。とりあえず、workerには、Create role, Create DBを付与しておく。

postgres=# CREATE ROLE worker WITH createdb createrole LOGIN PASSWORD '<YOUR PASSWORD>';
CREATE ROLE

postgres=# \du
                                   List of roles
Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 aki       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  |Create role, Create DB                          | {}

## ロールの削除はDROP ROLE
## postgres=# DROP ROLE worker;
## DROP ROLE

この状態だとスーパーユーザーはpsql -U aki -d postgresでデータベースにアクセスできる状態なので、スーパーユーザーにもパスワード認証を要求するようにする。そのためにpg_hba.confの中身を書き換えるが、先にスーパーユーザーにパスワードを付与しておく。

postgres=# ALTER USER aki WITH PASSWORD '<YOUR PASSWORD>';
ALTER ROLE
postgres-# \q

## 下記の部分を変更する peer → md5
$vim /usr/local/var/postgres/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     peer
↓
# "local" is for Unix domain socket connections only
local   all             all                                     md5

# データベースを再起動
$ brew services restart postgresql

# スーパーユーザーで入ろうとするとパスワードが要求される
$ psql -U aki -d postgres
Password for user aki:<YOUR PASSWORD>
psql (14.1)

DBの作成

workerでpostgresデータベースにアクセス。ニューヨークのタクシーのトリップデータ用のサンプルデータベースnytaxiを作成する。workerで作成したので、Ownerがworkerになっている。

$ psql -U worker -d postgres -W
Password:
psql (14.1)
Type "help" for help.

postgres=> \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | aki   | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | aki   | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki           +
           |       |          |             |             | aki=CTc/aki
 template1 | aki   | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki           +
           |       |          |             |             | aki=CTc/aki
(3 rows)

postgres=> CREATE DATABASE nytaxi;
CREATE DATABASE
postgres=> \l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
 nytaxi    | worker | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres  | aki    | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | aki    | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki           +
           |        |          |             |             | aki=CTc/aki
 template1 | aki    | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki           +
           |        |          |             |             | aki=CTc/aki
(4 rows)

\c [database name]でデータベースを変更できるので、postgresからnytaxiに切り替え。

postgres=> \c nytaxi;
Password:
You are now connected to database "nytaxi" as user "worker".
nytaxi=>

## ログアウト状態からは下記で`nytaxi`データベースに接続する。オプションは必要であれば。
## $psql -h 127.0.0.1 -p 5432 -U worker -d nytaxi -W

テーブルの作成

ここではpublicスキーマの中にテーブルtripdataを作成する。まずはテーブル定義を作成。

nytaxi=> CREATE TABLE public.tripdata(
  VendorID int,
  tpep_pickup_datetime timestamp,
  tpep_dropoff_datetime timestamp,
  passnger_count int,
  trip_distance real,
  RatecodeID int,
  store_and_fwd_flag varchar(10),
  PULocationID int,
  DOLocationID int,
  payment_type int,
  fare_amount real,
  extra real,
  mta_tax real,
  tip_amount real,
  tolls_amount real,
  improvement_surcharge real,
  total_amount real,
  congestion_surcharge real);
CREATE TABLE

nytaxi=> \d+
                                    List of relations
 Schema |   Name   | Type  | Owner  | Persistence | Access method |  Size   | Description
--------+----------+-------+--------+-------------+---------------+---------+-------------
 public | tripdata | table | worker | permanent   | heap          | 0 bytes |
(1 row)

テーブルへのデータ投入はCOPYコマンドで行なう。

# CSV importの場合
nytaxi=> \copy public.tripdata from '/Users/aki/Desktop/log_2021_01_01.csv' (encoding 'utf8', format csv, header true);
COPY 24827

nytaxi=> \copy public.tripdata from '/Users/aki/Desktop/log_2021_01_02.csv' (encoding 'utf8', format csv, header true);
COPY 34309

nytaxi=> select count(1) from public.tripdata;
 count
-------
 59136
(1 row)

ターミナルからCSV exportの場合

テーブルのデータをエクスポートしようとすると、pg_write_server_filesのメンバーである必要があるとのこと。

$ psql -U worker -d nytaxi -c "COPY (SELECT * FROM public.tripdata limit 10) to '/Users/aki/Desktop/data.csv' WITH CSV HEADER;"
ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

GRANTで権限を付与しておく。

nytaxi=> \du
                                          List of roles
 Role name |                         Attributes                         |        Member of
-----------+------------------------------------------------------------+-------------------------
 aki       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 worker    | Create role, Create DB                                     | {}

postgres=> GRANT pg_write_server_files TO worker;
GRANT ROLE
# readに関するエラーが出ればこれで対応
postgres=> GRANT pg_read_server_files TO worker; 
GRANT ROLE

nytaxi=> \du
                                          List of roles
 Role name |                         Attributes                         |        Member of
-----------+------------------------------------------------------------+-------------------------
 aki       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 worker    | Create role, Create DB                                     | {pg_write_server_files}

$ cat /Users/aki/Desktop/data.csv
vendorid,tpep_pickup_datetime,tpep_dropoff_datetime,passnger_count,trip_distance,ratecodeid,store_and_fwd_flag,pulocationid,dolocationid,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount,congestion_surcharge
1,2021-01-01 00:30:10,2021-01-01 00:36:12,1,2.1,1,N,142,43,2,8,3,0.5,0,0,0.3,11.8,2.5
1,2021-01-01 00:51:20,2021-01-01 00:52:19,1,0.2,1,N,238,151,2,3,0.5,0.5,0,0,0.3,4.3,0
1,2021-01-01 00:43:30,2021-01-01 01:11:06,1,14.7,1,N,132,165,1,42,0.5,0.5,8.65,0,0.3,51.95,0
1,2021-01-01 00:15:48,2021-01-01 00:31:01,0,10.6,1,N,138,132,1,29,0.5,0.5,6.05,0,0.3,36.35,0
2,2021-01-01 00:31:49,2021-01-01 00:48:21,1,4.94,1,N,68,33,1,16.5,0.5,0.5,4.06,0,0.3,24.36,2.5
1,2021-01-01 00:16:29,2021-01-01 00:24:30,1,1.6,1,N,224,68,1,8,3,0.5,2.35,0,0.3,14.15,2.5
1,2021-01-01 00:00:28,2021-01-01 00:17:28,1,4.1,1,N,95,157,2,16,0.5,0.5,0,0,0.3,17.3,0
1,2021-01-01 00:12:29,2021-01-01 00:30:34,1,5.7,1,N,90,40,2,18,3,0.5,0,0,0.3,21.8,2.5
1,2021-01-01 00:39:16,2021-01-01 01:00:13,1,9.1,1,N,97,129,4,27.5,0.5,0.5,0,0,0.3,28.8,0
1,2021-01-01 00:26:12,2021-01-01 00:39:46,2,2.7,1,N,263,142,1,12,3,0.5,3.15,0,0.3,18.95,2.5

## 権限を削除する場合
## nytaxi=> REVOKE pg_write_server_files FROM worker;
## REVOKE ROLE

おまけ

SaaSのサンプルデータを投入する方法は下記のポストの末尾参照。

mysql.hatenablog.jp