Atsushi2022の日記

データエンジニアリングに関連する記事を投稿してます

PostgreSQL text型配列のパターンマッチングのやり方

テキスト型配列ではそのままパターンマッチングができない。

テキスト型配列でパターンマッチングをするには、UNNEST関数でテキスト型配列のネストを解除し、ネスト解除したものに対してパターンマッチングを行う。

実際にやってみる。

まずテキスト型配列をもつテーブルを作成し、適当な行を挿入する。

テキスト型配列を挿入する際は、'{}'で囲む。

CREATE TABLE table1 (
    "id" integer,
    nest_data text[]
);

INSERT INTO table1 VALUES (1, '{abc, 123, XYZ}');
INSERT INTO table1 VALUES (2, '{ttt, 543, BCD}');

作成されたテーブルを表示すると、ちゃんとネストされている。

SELECT id, nest_data FROM table1;

id  unnested_data
1   {abc,123,XYZ}
2   {ttt,543,BCD}

nest_data列をUNNEST関数でネスト解除するとこんな感じになる。

SELECT id, unnested_data FROM 
(SELECT id, UNNEST(nest_data) "unnested_data" FROM table1 ) as t;

id  nest_data       unnested_data
1   {abc,123,XYZ}   abc
1   {abc,123,XYZ}   123
1   {abc,123,XYZ}   XYZ
2   {ttt,543,BCD}   ttt
2   {ttt,543,BCD}   543
2   {ttt,543,BCD}   BCD

あとはネスト解除した列でパターンマッチングしつつ、ネストされた列のみを取り出せばうまいこと抽出できる。

SELECT id, nest_data FROM 
(SELECT id, nest_data, UNNEST(nest_data) "unnested_data" FROM table1) as t
WHERE unnested_data LIKE '1%';

id  nest_data
1   {abc,123,XYZ}

バージョンアップで参考になりそうな記事

PostgreSQLのバージョンアップで参考になりそうな記事があったのでメモしておく。

techblog.gmo-ap.jp

データだけでなく、権限やオーナーも確認しなくっちゃな。

検証時に確認ポイントとしていたのは以下の点です。

データベースのタイムゾーンがUTC+9になっているか
データ移行前後で、各ロールの権限に差異がないか
データ移行前後で、データベースオーナー/エンコーディング/アクセス権限/データベースサイズに差異がないか
データ移行前後でスキーマ数/スキーマのオーナー/各スキーマへのアクセス権限に差異がないか
データ移行前後でインデックス数/インデックスのオーナー/各インデックスへのアクセス権限に差異がないか
データ移行前後でテーブル数/テーブルのオーナー/各テーブルへのアクセス権限に差異がないか

PostgreSQLを始めてみた

概要

PostgreSQLを使用する機会があったので勉強を始めてみました。

大事そうなところ、かつ忘れてしまいそうなところに絞ってメモしようと思います。

参照文献

定数

文字列定数

シングルクォーテーションで囲んで記述する。

'文字列'

文字列中にシングルクォーテーションがある場合には、シングルクォーテーションを2個連続して記述する。\マークはそのまま文字列として扱われる。

'I''ll be back.'

PostgreSQL 14ではバックスラッシュでエスケープすることもできるが、今後は使用できなくなる予定らしいので要注意。

単一引用符をバックスラッシュでエスケープすること(\')ができます。 しかし、今後のPostgreSQLのバージョンではこれはできなくなる予定です。 ですので、バックスラッシュを使用するアプリケーションを上述の標準に準拠するように変更しなければなりません。

引用元

エスケープ文字構文

エスケープ文字構文を使用することで特殊な文字も入力できる。 エスケープ文字構文では、文字列をシングルクォーテーションで囲み、先頭にEまたはeを記述する。

E'\041'
あるいは
e'\041'

ビット文字列、16進文字列

  • ビット文字列

    B'01'b'011110'

  • 16進文字列

    X'3F'x'A37E'

データ型

文字列型

varchar型、char型、text型がある。

varchar型は可変長文字列型とchar型は固定長文字列型である。

text型は、文字数に上限のない可変長の文字列型。便利だが、ANSI SQL標準外なので要注意。

浮動小数点型

numericrealdouble precisionがある(real型って由来はなんなんだろう、、、)

numeric(8, 2)で少数点以下2桁、整数部が6桁の値を格納できる。整数部の桁数を超えるとエラーとなる。

real型は4バイトの小数点以下6桁の精度、double precision型は8バイトの小数点以下15桁の精度である。

serial型

テーブル作成時にserial型のカラムを作り、インサート文でそのカラムに値を指定せずにレコードを挿入すると、自動的にシリアルな整数を挿入できる。

裏側ではシーケンスが動いているらしい。

serial型のカラムに任意の整数値を挿入できるが、serial型のカラムはそれを検知しないので、重複した整数値が払い出される。

serial型が4バイトで 1 から 2147483647 まで扱えるのに対し、bigserial型は8バイトで 1 から 9223372036854775807まで扱うことができる。

日付・時刻データ型

date型、timestamp型、time型がある。

date型は日付のみ格納できる。時刻データ部分は切り捨てられる。Oracleのdate型とは異なるので要注意。

例:2018-01-23

timestamp型は日付と時刻を格納できる。秒数は小数点以下6桁まで扱うことができる。

例:2018-01-23 12:34:56.526066

timeがたは時刻のみ格納する。日付データは持たない。

例:12:34:56.526066

キャスト

CAST関数を使用する方法と::を使用する方法がある。

以下のどちらのケースでもinteger型の123になる。

SELECT CAST('123' AS integer);
SELECT '123'::interger;

データベースクラスタの作成、初期化

データベースクラスタディレクトリ(テーブルファイルとかインデックスファイルとか設定ファイルとか管理ファイルが格納される)

initdbコマンドでデータベースクラスタを作成できる。

initdb --no-locale --encoding=UTF8 --pgdata=/var/lib/pgsql/14/data

エンコーディングはUTF8を指定しとけばオーケー。

localeというのは地域の言語や文化に応じた処理をしてくれるOSの仕組みのこと。例えば、日付や通貨の表示処理、文字列のソート順序処理など。PostgreSQLではロケール使用は非推奨です。ロケール機能には統一した仕様が存在しないため。

--pgdataにはデータベースクラスタを作成するディレクトリを指定する。環境変数 $PGDATAに指定することもできる。

$PGDATA/postgresql.confに設定をしておく。

データベースクラスタはpg_ctlコマンドで起動できる。どのデータベースクラスタを利用するかは--pgdataフラグ、または環境変数 $PGDATAに設定しておく。pg_ctl statusでデータベースクラスタの起動状態を確認できる。

pg_ctl start --pgdata=/var/lib/pgsql/14/data
pg_ctl status

データベースクラスタを停止する際も同様にデータベースクラスタを指定する。

pg_ctl stop --pgdata=/var/lib/pgsql/14/data

デフォルトのデータベース

Postgreのデータベースにはデフォルトで、postgrestemplate0template1がある。

template0template1はデータベースのテンプレートである。

create database文でデータベースを作成する際、デフォルトでtemplate1をコピーしてデータベースを作成する。template1は書込み可能なので、あらかじめtemplate1に設定を登録しておくと便利。

pgbench

PostgreSQLに同梱されているベンチマークツール。

TBC-Bという銀行口座、支店、窓口担当者の業務をモデル化しているベンチマークシナリオを用いている。

pgbenchコマンドでデータベースにベンチマークテーブルを作成できる。

pgbench --initialize --scale=30 <データベース名>

--initializeベンチマークテーブルの初期化を意味する。

--scaleベンチマークテーブルの規模を表す。--scale=1でデータベースサイズが15MBほどになる。

Oracle Master Silver SQLに合格しました

2022年10月にOracle Master Silver SQLに合格しました。

受験してみた感想

OracleSQLを使用する上での最低限の知識を問う資格なのかなと思う。with句やPL/SQLが範囲外なのでホントに基本という感じ。

もっとアドバンスな内容の方が良かった気もするが、おそらく自主学習しているだけでは得られなかった細かいルールというか細かい知識が身についた。試験勉強することで忘れがちなSQLの構文が脳に定着した(気がする)のでまあ良かったと思う。

正直Oracle自体はあまり使わないので、Oracleの知識を比較対象として、PostgreSQLとかOSS DBの学習に生かしていきたい。

今になって思うと、Oracle Master Silver SQLではなく、OSS-DB Silver(LPIC-Japan主催のPostgreSQL資格)を受けた方がよかったかもしれない。Oracle Master Silver SQLの勉強し始めた当初はOSS-DB Silver知らなかったのでまあしょうがない。

学習方法

スタンダードな学習方法だと思うが、黒本を2周して、Udemyの模擬試験を1.5周した。黒本では関数のあたりは読み飛ばしてしまったので模擬試験でキャッチアップするようにした。Udemyの模擬試験で6個中3個は2回目トライしたが、いずれも90%以上とれたのでまあいけるかなと思って受験したところ、試験結果は75%で思ったほど点数がとれてなかった。

【Tableau】増分更新によるデータ反映漏れについて

Tableauでは、データソースの更新を抽出する方法は2種類ある。完全更新と増分更新である。デフォルトでは完全更新が行われる。

完全更新ではデータソースの全行が抽出されるためTableau側に完全に同じデータを反映される。但し、トランザクションデータなどの大規模になりがちなデータの場合は、完全更新だと参照元データベースに大きな負荷がかる。BigQueryを利用している場合は、クエリによるデータ処理量が大きいと費用が大変なことになる。そういった場合は増分更新を検討する。

増分更新では、前回データを抽出したものを除いて、新しい行のみを追加するように抽出を行う。例えば、日付のフィールドをキーとして、日付が前回の更新以降である行のみを抽出する。但し、この設定の場合だと、後から過去のデータを取り込んだ場合にTableauには反映されない。

増分更新を利用する場合は、Tableauへ反映漏れが起きないか、反映漏れが起きたとしても問題ないかを事前によく考慮する必要がある。

ローカルにおけるGitコマンド

アーキテクチャ

全体的なアーキテクチャを理解しよう。

まずローカルPCとクラウドに分類される。ローカルPCのドキュメントをクラウドに配置する(push)、あるいはクラウドから取ってくる(pull)。

ローカルPC内では、データがワークツリー、ステージ、リポジトリのいずれか、あるいはすべてに保管される。

ワークツリーで編集した内容をgit addでステージに配置する。さらにgit commitでステージからリポジトリに配置する。

ひとまずcommitしてみる

ステージにtest.txtを入れる。

git add test.txt

git add .とすることで、フォルダ(ワークツリー)にあるすべてのファイルをaddすることもできる。

ステージに入れたので、さらにcommitしてリポジトリに移したい。だが、次のようにエラーとなる。

> git commit -m "1st commit"
Author identity unknown

*** Please tell me who you are.

Run

  git config --global user.email "you@example.com"
  git config --global user.name "Your Name"

to set your account's default identity.
Omit --global to set the identity only in this repository.

Eメールとユーザー名を事前に登録しておかなければならない。

git config --global user.email "you@example.com"
git config --global user.name "Your Name"

再度Commitすると今度はうまくいった。

> git commit -m "1st commit"
[master (root-commit) 54ed5fb] 1st commit
 1 file changed, 1 insertion(+)
 create mode 100644 test.txt

branchを作成してみる

ここでbranchを確認してみる。masterブランチのみ存在するのが確認できる。 初Commit前に、git branchコマンドでbranchを確認しても何も表示されないことに注意。Commitして初めてbranchが確認できるようになる。

> git branch
* master

試しにdevブランチを作成してみる。アスタリスクが現在のブランチを指している。devブランチを作成しただけでは、devブランチに移らないので注意。

> git branch dev
> git branch
  dev
* master

branchをスイッチするには、git checkoutコマンドを使う。devブランチに移っていることがわかる。

> git checkout dev
Switched to branch 'dev'
> git branch
* dev
  master

statusを確認してみる

現在、test.txtにはnumber1という文字列が入っている。

> cat .\test.txt
number1

test.txtにはnumber2という文字列を追加する。

> cat .\test.txt
number1
number2

git statusコマンドにより、リポジトリとステージ間で差異があるファイルをリストアップできる。modified: test.txtと表示されているが、これは「ファイルが変更されているけれど、まだステージに追加されていませんよ」という意味。

> git status
On branch master
Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git restore <file>..." to discard changes in working directory)
        modified:   test.txt

no changes added to commit (use "git add" and/or "git commit -a")

次に、db.txtをaddしてみる。

> git add db.txt

再度、git statusコマンドを実行すると、new file: db.txtというのが追加で表示されていることがわかる。これは「新しくファイルがステージに追加されたけど、まだコミットされてませんよ」という意味。

> git status
On branch master
Changes to be committed:
  (use "git restore --staged <file>..." to unstage)
        new file:   db.txt

Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git restore <file>..." to discard changes in working directory)
        modified:   test.txt

これでコミットしてstatusを確認すると、test.txtはステージに追加してなかったので、modified: test.txtという表示が消えてない。

> git commit -m "New file: db.text"
[master 1428b7d] New file: db.text
 1 file changed, 1 insertion(+)
 create mode 100644 db.txt
> git status
On branch master
Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git restore <file>..." to discard changes in working directory)
        modified:   test.txt

no changes added to commit (use "git add" and/or "git commit -a")

git addでtest.txtをステージに追加してからコミットすると、nothing to commit, working tree cleanとなり、すべてコミットされていることが確認できる。

> git add test.txt
> git status
On branch master
Changes to be committed:
  (use "git restore --staged <file>..." to unstage)
        modified:   test.txt

> git commit -m "Add line in test.txt"
[master 338041d] Add line in test.txt
 1 file changed, 2 insertions(+), 1 deletion(-)
> git status
On branch master
nothing to commit, working tree clean

diffを確認してみる

git diffではステージとワークツリー間の差分を確認する。一方、git diff --stagedではステージとリポジトリ間の差分を確認する。

test2.txtというファイルがあり、中身には「1」が入っている。ワークツリーとステージ間、ステージとリポジトリ間で差分がない状態になっている。

> cat test2.txt                                                              
1                                                                                                                                                                                                                                               > git diff                                                                   
> git diff --staged
>

test2.txtに「2」を追加し、差分を確認する。git diff によりワークツリーとステージ間には差分があり、一方git diff --stagedによりステージとリポジトリ間で差分がない、ということがわかる。

> vim test2.txt                                                              
> cat test2.txt
1
2

> git diff                                                                   
diff --git a/test2.txt b/test2.txt
index d474e1b..c47213d 100644
--- a/test2.txt
+++ b/test2.txt
@@ -1,2 +1,3 @@
 1
+2

> git diff --staged
>

git addでtest2.txt をステージに追加する。すると、ワークツリーとステージ間では差分がなくなり、ステージとリポジトリ間で差分がある状態になる。git diffとgit diff --stagedにより、それが確認できる。

> git add test2.txt                                                          
> git diff                                                                   
> git diff --staged
diff --git a/test2.txt b/test2.txt
index d474e1b..c47213d 100644
--- a/test2.txt
+++ b/test2.txt
@@ -1,2 +1,3 @@
 1
+2

resoreしてみる

git restore --stagedではリポジトリを元にステージを復元する。git restoreではステージを元にワークツリーを復元する。

実際にやってみる。

まず、ワークツリーとステージとリポジトリ間で差分がない状態になっている。

> cat test2.txt                                                              
1 
> git diff                                                               
> git diff --staged
>

test2.txt に「2」を追加する。ワークツリーとステージ間で差分が発生する。

> vim test2.txt
> cat test2.txt                                                              
1
2
> git diff                                                                   
diff --git a/test2.txt b/test2.txt
index d474e1b..c47213d 100644
--- a/test2.txt
+++ b/test2.txt
@@ -1,2 +1,3 @@
 1
+2
> git diff --staged
>

git restoreにより、ステージを元にワークツリーを復元する。中身が「1」に戻っている。

> git restore test2.txt
> cat test2.txt                                                              
1

再度test2.txt に「2」を追加し、さらにステージに追加する。ステージとリポジトリ間で差分が発生する。

> vim test2.txt
> cat test2.txt                                                              
1
2
> git add test2.txt
> git diff
> git diff --staged
diff --git a/test2.txt b/test2.txt
index d474e1b..c47213d 100644
--- a/test2.txt
+++ b/test2.txt
@@ -1,2 +1,3 @@
 1
+2

git restore --stagedにより、リポジトリを元にステージを復元したため、ステージとリポジトリ間で差分が解消する。リポジトリを元にステージが復元されたことにより、ワークツリーとステージ間で差分が発生している。

> git restore --staged test2.txt
> git diff --staged
> git diff
diff --git a/test2.txt b/test2.txt
index d474e1b..c47213d 100644
--- a/test2.txt
+++ b/test2.txt
@@ -1,2 +1,3 @@
 1
+2