まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:sql

IBM Cloud の比較的新しい機能として、オブジェクトストレージ内の CSV ファイルに対して SQL を実行してレコードを抽出することができるようになりました。その新機能 SQL Query を紹介します。なお以下で紹介する手順は IBM Cloud のライトアカウント(無料版)の範囲内でも実施可能なので、興味ある方はぜひ本記事を参考に使ってみてください。
2021011600



この SQL Query はオブジェクトストレージ内の CSV ファイルを対象として実行するので、まずは(まだ利用していない場合は)クラウドオブジェクトストレージサービスを作成します。プランが「ライト」のものを選択して作成すれば一定条件内で無料で利用可能です:
2021011601


作成したオブジェクトストレージサービスの "Integrations" メニューを表示すると、このオブジェクトストレージサービスに統合されたサービスを確認できます。作成直後ではここには何もないはずですが、画面下部に「SQL Query を新規に作成して統合可能」と案内されています。この SQL Query のアイコンを選択することで、SQL Query (の無料版)を新規に作成して、このオブジェクトストレージと紐付けて利用することができるようになります:
2021011602


作成する SQL Query サービスの属性を設定する画面が表示されます。このプランとして「ライト」を選択すれば一定条件内で無料利用が可能です。最後に「作成」をクリック:
2021011603


SQL Query サービスが追加され、オブジェクトストレージと統合されて利用できるようになりました:
2021011604


また「バケット」メニューを参照すると、SQL Query 用のバケットが1つ追加されていることが確認できます。このバケットは必ずしも利用する必要はありませんが、SQL を実行する CSV ファイルや、SQL の実行結果をファイルとして残したい場合のファイル格納場所として利用できます。

今回はこの作成されたバケット内に CSV ファイルを置いて SQL を実行し、その実行結果ファイルもこのバケット内に作成する手順を以下で紹介します。というわけで、まずはこのバケット内に CSV ファイルを格納するため、バケット名部分を選択します:
2021011605


選択した(作成したばかりの)バケット内のファイル一覧が表示されますが、この時点ではまだ CSV ファイルは格納されていません:
2021011606


もし自分でお持ちの CSV ファイルを利用したい場合は、その CSV ファイルをこの一覧部分にドラッグ&ドロップしてアップロードしていただいても構いません。以下のサンプルでは以前に自分が全く別の目的でラズベリーパイの機器温度等を1秒おきに取得した際の CSV ファイルがあったので、このファイルを使って紹介することにします。

同 CSV ファイルは以下に公開しているので、以下ページを表示して、マウス右クリックから「名前を付けて保存」し、RPDATA.csv という名前で保存してください:
https://raw.githubusercontent.com/dotnsf/RPDATA/master/RPDATA.csv

2021011607
(1行目は列定義、2行目以降がデータで、左から ID、CPU 温度(℃)、CPU 負荷(%)、サイン値)


ダウンロードした RPDATA.csv をオブジェクトストレージ内のバケットにドラッグ&ドロップ(または「アップロード」ボタンからアップロード)して同バケット内に格納しておきます:
2021011608


これで SQL を実行する準備ができました。では SQL Query のサービスに移動します。改めてオブジェクトストレージのサービス画面から Integrations メニューを選択し、統合されている SQL Query サービス名部分を選択します:
2021011601


SQL Query サービス画面が表示されたら、画面右上の "Launch SQL Query UI" ボタンをクリックして UI 画面に移動します:
2021011602


以下のような IBM SQL Query の UI 画面が表示されます(↓この画面ではエラーメッセージが表示されていますが気にしないでください(苦笑)):
2021011603


画面上部に SQL を入力します。その際の from 直後のテーブル部を指定する箇所では、アップロードした CSV ファイルを指定します。CSV ファイルは Target location に表示されている文字列の、最後の result/ を除いて変わりに RPDATA.csv を指定し、最後に Run ボタンをクリックします:
2021011601
("select * from cos://us-south/XXXXXX/RPDATA.csv limit 10" と入力しました。一般的な SQL SELECT 文のテーブル名を指定する箇所にオブジェクトストレージ内の CSV ファイルを URI で指定する形になっています)


すると画面下部に SQL クエリーの実行結果が表示されます。特にテーブル定義をすることなく、CSV の1行目にかかれていた列情報をそのまま使って SELECT 文の実行ができることがわかります:
2021011602


またこの実行結果はもとのオブジェクトストレージのバケット内の新 CSV ファイルとしても保存されています。必要に応じてダウンロードするなどして結果を確認できます:
2021011603


オブジェクトストレージに格納された CSV ファイルに対して直接クエリーを実行できるので、IoT システムなどによって記録された CSV ファイルをバッチ処理でオブジェクトストレージ内に格納できてしまえすれば、(SQL 型のデータベースにインポートすることなく)その CSV ファイルの中から条件にあった列だけを取り出すことができる、ということがわかりました。実行結果もオブジェクトストレージ内のファイルとして生成することができるので、IoT システムなどでセンサーデータを集める場合でも「とりあえずは CSV にしてオブジェクトストレージに入れておけば、条件を指定した取り出しは後からできる」ことになって便利だと感じました。




以前に Linux 版 Db2 を導入した時の手順を紹介したブログがありました:
http://dotnsf.blog.jp/archives/2960284.html


3年以上前のこの時の仮想環境が奇跡的に残っていたので久しぶりに使ってみました。で、折角の Db2 コマンドライン環境(?)なので、このコマンドラインインターフェースから IBM Cloud 上の DBaaS である Db2 に接続して使ってみました。以下、その手順の紹介です。

まずは IBM Cloud にログインして Db2 インスタンスを作成します。無料のライトアカウントでも使えるサービスが用意されているので、派手に使わない限りは無料で試すこともできます:
2018052302


無料のライトプランを使う場合はデプロイ先ロケーションを「米国南部」に設定します。加えて自分のメールアドレスを指定します:
2018052303


デプロイ先ロケーションが米国南部の場合、無料の Lite プランが選択可能になります(もちろん他のプランでも構いません)。プラン設定後に「作成」ボタンでインスタンスが作成されます:
2018052304



インスタンス作成後、「サービス資格情報」メニューから「資格情報の表示」を選択します(「資格情報の表示」が存在しない場合は、最初に「新規資格情報」をクリックして作成し、その後に改めて「資格情報の表示」を選択します)。すると画面下部に JSON フォーマットで接続先サーバー名やユーザー、パスワードといった情報を確認できます:
2018052301


この中で、今回使うのは以下の5つの情報です:
 - hostname(接続先のサーバー名)
 - password(接続時のパスワード)
 - port(接続先のポート番号)
 - db(データベース名)
 - username(接続時のユーザー名)


以下では、これら5つの値がこのような内容になっていたと仮定して説明を続けます:
{
  "hostname": "dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net",
  "password": "xxxxxxxx",
  "port": 50000,
  "db": "BLUDB",
  "username": "lrz77612",
    :
}



ではこの DBaaS 上のデータベース(BLUDB)にコマンドラインから接続するための手順を紹介します。


db2inst1 ユーザーへの変更

ここから下のコマンドはすべて db2 インストール時に指定したユーザー(今回の例では db2inst1)に切り替えて行う必要があります。というわけでターミナル上でのユーザーを切り替えます:
$ su - db2inst1

ノードカタログの確認

接続先となる Db2 サーバーはサーバーノードとしてカタログに記録されている必要があります。というわけで、まず現在のノードカタログを確認します(青字は出力結果):
$ db2 list node directory
SQL1037W  The node directory is empty.  SQLSTATE=01606

この出力結果によるとノードカタログは空で何も登録されていないようです。


ノードのカタログ登録

というわけで今回の接続先となる IBM Cloud 上の Db2 サーバーをカタログに登録します。以下のように接続先のサーバー名とポート番号を指定し、今回は "dashdb" という名前でカタログに登録しています(太字部分は各自の環境や希望に併せて変更してください):
$ db2 catalog tcpip node dashdb remote dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net server 50000
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
成功したようです。この状態で再度ノードカタログを確認すると、今度は dashdb という名前のカタログが確認できるはずです:
$ db2 list node directory

 Node Directory

 Number of entries in the directory = 1

Node 1 entry:

 Node name                      = DASHDB
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net
 Service name                   = 50000



ノードカタログを削除

今回は作業しませんが、登録したカタログを削除する場合はカタログ名を指定して以下のように実行します:
$ db2 uncatalog node dashdb


エイリアスの確認

DB2 でデータベースに接続するにはデータベースをエイリアスとして登録する必要があります。というわけで次に接続先となるリモートデータベースのエイリアスを作成します。まずは現在登録されているエイリアスの一覧を確認します:
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =


この例では(いずれもローカルサーバー内の)JSONDB と MYDB という2つのデータベースエイリアスが登録されていました。この例ではリモートサーバーのエイリアスは登録されていません。


エイリアスの登録

では先程作成した dashdb ノード上の接続先データベース(BLUDB)を "remotedb" という名前でエイリアス登録します:
$ db2 catalog database BLUDB as remotedb at node dashdb
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

成功したようです。念の為もう1回エイリアス一覧を確認します:
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = REMOTEDB
 Database name                        = BLUDB
 Node name                            = DASHDB
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 3 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =


エイリアスが2つから3つに増え、REMOTEDB が新たに登録されました。


エイリアスを削除

この作業も今回は不要ですが、登録したエイリアスを削除する場合はエイリアス名を指定して次のように実行します:
$ db2 uncatalog db remotedb


エイリアスを指定して接続

では改めてエイリアスを作ったデータベースに接続します。接続時にはユーザー名とパスワードが必要になるので、以下のように指定します:
$ db2 connect to remotedb user lrz77612 using xxxxxxxx

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.2.2
 SQL authorization ID   = LRZ77612
 Local database alias   = REMOTEDB

IBM Cloud 上のデータベースに接続できました。後はこのリモートデータベースに対して各種 SQL を実行してテーブルやデータの作成/更新/削除/検索といったコマンドが実行できます。


接続しているエイリアスから切断

なお、接続しているエイリアスから切断する場合は以下のコマンドを実行します:
$ db2 terminate




自分が苦手なので、備忘録的に残しておきます。

まずやりたいことはこれです。RDB (今回は MySQL)のこんなテーブル(todos)があったとします。email の人の ToDo を管理するようなテーブルだと考えてください:
列名列型目的
idintプライマリキー、自動インクリメント
emailvarchar(50)メールアドレス
bodyvarchar(255)本文
createdlongレコードを作成した日時のタイムスタンプ


これだけであれば、以下の SQL でテーブルを定義するようなものだと思います:
create table todos(
  id int primary key auto_increment,
  email varchar(50),
  body varchar(255),
  created long
);

ここに運用上の条件を1つ加えます。1つの email に対しては最新の body のみを有効としたいのです。要するに同じ email に異なる body があっても(なくても)いいのですが、有効なものは最新の(created が最も大きな)body のみとして扱えるようにしたい、というものです。

例えばテーブルの中身がこのようになっているものとします(タイムスタンプの値がいいかげんですが、大小関係だけ分かればいいものとします):
idemailbodycreated
1aaa@xxx.jpToDo その110
2bbb@xxx.jpToDo その220
3aaa@xxx.jpToDo その330


全部で3件のレコードがありますが、email = aaa@xxx.jp のレコードが2件あります。この場合は created が最大である id=3 のレコードのみが有効と考えます(id=1 のレコードは削除されていても構いません)。

このような条件を付けた上で、「有効なレコードだけの一覧を取り出したい」のですが、どのようにデータベースを定義して運用すればいいでしょう?

まず email 列は実質的にユニークになるとも考えられます。が、テーブル定義時に UNIQUE 属性を付けてしまうと、INSERT 時にエラーとなってしまうので「まず同じ email のデータが存在しているかどうかを確認し、存在していなければ INSERT 、していれば UPDATE するような SQL を実行する」ことになります(こうすると実は UNIQUE 属性はあってもなくても同じと言えます)。INSERT 時の処理がちと面倒ですが、読み込み時にはシンプルに "select * from todos" だけで実現可能な方法といえます。

次に email はユニークとは考えずに、とにかくレコードは全て INSERT して、有効なレコードを取り出す時に工夫する、という方法もあります。これは SQL としては以下を実行すればよいことになります:
> select * from todos where ( email, created ) in (select email,max(created) as max_created from todos group by email);

これはこれで実現可能な方法の1つです。ただ上記の例とは逆に、INSERT は無条件に行いますが、読み込み時に比較的複雑な SQL が実行されることになります。また有効でない(使われることのない)レコードがテーブル内にずっと残ってしまうことになります。


ではこれらのいいとこ取りを考えてみます。つまり、以下のようなロジックです:
(1) INSERT は無条件に行い、
(2) INSERT 直後に有効でないレコードがあったら削除し、
(3) 検索はシンプルに "select * from todos" だけで行えるようにする


(1) の直後に (2) が出来れば、(1) も (3) もシンプルな処理になるので理想的とも言えます。で、前書きが長くなりましたが、今回はこの (2) を実現するのに苦労した話です(苦笑)。

まず、このケースでの「有効なレコード」を取り出す場合の SQL クエリーは上記のようにこのようになります:
> select * from todos where ( email, created ) in (select email,max(created) as max_created from todos group by email);

(結果)
idemailbodycreated
2bbb@xxx.jpToDo その220
3aaa@xxx.jpToDo その330

ということは、「有効でないレコード」(削除対象のレコード)はこの SQL で取り出せます:
> select * from todos where ( email, created ) not in (select email,max(created) as max_created from todos group by email);

(結果)
idemailbodycreated
1aaa@xxx.jpToDo その110

ここまでできれば後は単純に削除対象のレコードを削除すればよいので、サブクエリーを使ってこんな SQL を実行するだけ・・・
> delete from todos where id in ( select id from todos where ( email, created ) not in (select email,max(created) as max_created from todos group by email) );

と思ったのですが、これを実行すると以下のようなエラーが発生します:
ERROR 1093 (HY000): You can't specify target table 'todos' for update in FROM clause

これがサブクエリーのややこしい(というか、自分がよく分かっていなかった)所です。サブクエリーを使った時に意識する構文がいくつか存在しています:
https://dev.mysql.com/doc/refman/5.6/ja/subquery-errors.html

↑これの「サブクエリー内の誤って使用されているテーブル」に書かれた項目が今回のエラーの原因でした。サブクエリーの FROM で指定されるテーブルと、更新系のテーブルに同じものを使用することができない、というルールです。

これを解決するにはサブクエリー内の FROM 部分("todos" と書かれた部分)を更にサブクエリーにしてテンポラリテーブルにします。上記例だとこんな感じ:
> delete from todos where id in ( select id from (select * from todos) where ( email, created ) not in ( select email,max(created)
 as max_created from (select * from todos) group by email ) );

ただこれだと該当部分が2箇所あり、今度は複数のテンポラリテーブルは異なるエイリアスを持たなければならない、という制約に引っかかってエラーになってしまいます:
ERROR 1248 (42000): Every derived table must have its own alias

というわけで、最終的な答はこちら。テンポラリテーブルに異なるエイリアスを指定して完成です:
> delete from todos where id in ( select id from (select * from todos) as temp1 where ( email, created ) not in ( select email,max(created)
 as max_created from (select * from todos) as temp2 group by email ) );

こんな SQL 、何も見ずに一発でさらっと書ける人いるんかな・・・


WordPress のデータベースから SQL でデータを取り出すシリーズ(?)の応用編。今回のテーマは
 ポストした本文とタイトルと、その文書に付与されたカテゴリの一覧だけを取り出す
というものです。

最初に結論を書いておきます。この SQL で目的を達成できるはず(公開データと未公開データの両方を取り出しています):
select wp_posts.post_content as content, wp_posts.post_title, wp_terms.name as category
 from wp_posts, wp_terms, wp_term_relationships
 where wp_posts.post_type = 'post' 
 and ( wp_posts.post_status = 'publish' or wp_posts.post_status = 'draft' )
 and wp_posts.ID = wp_term_relationships.object_id
 and wp_term_relationships.term_taxonomy_id = wp_terms.term_id;

説明する上で、WordPress のテーブルの相関関係を理解しておく必要があります。関連図についてはこちらを参照ください:
データベース構造 - WordPress Codex 日本語版


まず WordPress にポストしたレコードデータは wp_posts テーブル内に格納されています。特に(固定ページなどではなく)ポストデータは
 post_type = 'post'
となっているものが該当します。

そして今回は公開済みレコードと未公開(ドラフト)レコードの両方を取り出すことにしました。公開状況は同テーブルの post_status に格納されており、
 post_status = 'publish' or post_status = 'draft'
のいずれかの条件を満たしているものを取り出し、そこから本文(post_content)とタイトル(post_title)を取りだせばよい、ということになります。もしもドラフトが不要な場合はこの行の or 以降は不要です。

取り出すレコードの選別はこれだけです。次に各レコードに紐付けられたカテゴリ名称を取り出す必要があります。

カテゴリーのデータは wp_terms テーブルに(カテゴリ名は name 列に)含まれています。そしてどの文書がどのカテゴリに属しているのか、というリレーションは wp_term_relationships テーブルに格納されており、同テーブル内の object_id が wp_posts.ID 、term_taxonomy_id が wp_terms.term_id に該当しています。

例えば、wp_term_relationships テーブルに以下のようなレコードが存在していた場合、
object_idterm_taxonomy_id
41
61
82

wp_posts テーブルの ID が 4 の文書と 6 の文書は、wp_terms テーブルの term_id = 1 のカテゴリに属していて、ID が 8 の文書は term_id = 2 のカテゴリに属している、ということになります。

これらの関係を1つの SQL に書き直すと上記の SQL になります。
2016070701


WordPress に使っているデータベース(一般的には MySQL のデータベースだと思います)から、SQL を使って、ブログの名称や説明文を取り出す方法です:

2016070501
↑この図の赤枠部分がブログの名称、青枠部分が説明文です。


前提として、データベース作成時のテーブルのプレフィックスはデフォルトの 'wp_' をそのまま使っているものとします。つまりデータベース内には wp_posts とか wp_terms といったテーブルが存在している状態で利用中のものとします:
2016070502


で、ブログの名称は wp_options テーブル内に option_name の値が 'blogname' であるレコードの option_value 値として格納されています。したがってブログ名称を取り出す際の SQL は以下になります:
select wp_options.option_value as name from wp_options where wp_options.option_name = 'blogname';

2016070503
↑取り出せました


同様に、説明文は option_name の値が 'blogdescription' であるレコードの option_value 値です。したがって SQL だと以下になります:
select wp_options.option_value as name from wp_options where wp_options.option_name = 'blogdescription';
2016070504


この辺りがわかっていると、SQL でブログタイトルや説明を無理やり書き換える、ということも可能になります。


このページのトップヘ