(記事執筆時点 2018/08/24)
はじめに
こんにちわ！石田です。IBM Cloudの新サービス「SQL Query」を触ってみたので、ご紹介します。クラウド上のオブジェクト・ストレージに置いてある様々なデータ・ファイルを簡単にSQLで検索できる仕組み、って意味ではAmazonさんのAthenaやGoogleさんのBigQueryに似ていますけど、データベースへのロードやスキーマの準備は不要で「置いてあるファイルを、そのまま/即、ANSI準拠の標準SQLで検索できる」簡単さがウリです。
SQL Queryって何? ~1分で
- IBM Cloud Object Storage上のCSVなどのオープン・フォーマットのファイルを簡単にSQLで検索(SELECT)できるサービスです
- ICOS上にファイルが置いてあれば、すぐに検索できます。データベースへのロードやスキーマの定義などの準備は不要です
- 環境構築や準備も特にありません。カタログからサービスを選ぶだけ=1分で使い始められます
- DBのアーカイブやIoTのランディング・データ等へのアドホックな検索に向いています
- Webコンソールがありますが、REST APIやサーバーレスの仕組み1からもアクセスできます
- 内部的にはSparkSQLを使っていますので並列処理できます
- 課金の単位は(他社様の類似サービス同様に) x円/TBスキャン・月です2
1分終了です。
何に使えるの? ~ユースケース
オブジェクト・ストレージは「コールド・データのコストの安い保管場所」的な意味合いが強いですが、アドホックな検索ができると色々な利用場面が考えられます。例えば
- DBの過去時系列等のアーカイブを置いておき、過去調査の必要が生じた都度、いちいち再度テーブルにロードせずに直接検索する
- IoTやストリーミングのランディング・データを置いておき、検索/集計/一次加工する
- 簡単なSQLを書ける現場部門の方なら、IT部門の支援なしで上記のことはできるでしょう
私見ですが、SQL QueryはETLやBIなどの本格的でカチッと定例運用する分析システムの代わりになるものではなく、「基本、コールドデータだけど、たまに検索する必要が出るんだよね」というアドホックな検索に向いているものと思われます。あと「今はアーカイブはテープに保管して倉庫いれてるんで、いざ検索するときは出庫～リストア～ロードと、何かと手間がかかるんだよね」なんてシーンもあるでしょう。
何ができるの? ~機能
動き
【入力】ICOS上のバケットのファイルをテーブルと見立てます
【処理】標準SQLでSELECTします。内部的にはSparkSQLが動きます
【出力】SELECTした結果はICOS上で指定した任意のバケット上に書き出されます。(Webコンソールの場合は結果はパネルにも表示されます）
サポートされている入力ファイルの形式
- CSV / JSON / Parquet / ORC
-
CSVが一番お手軽でしょうが、現状は以下の制限があります
- 先頭行に見出しヘダー必須
- 区切り文字は「カンマ」のみ
-
CSVの見出しが日本語の場合はSELECTのカラム名を「 ‘ 」（バッククオート）で囲んでください。
機能へのアクセス方法
一番簡単なのはWebコンソールですが、他にも以下のように色々な方法でサービスにアクセスできます。
AmazonさんのAthenaはJDBCでのアクセスもあるようですが、SQL Queryは現時点は未対応です。
追加機能
- GeoSpatial Analyticsが含まれており、位置情報を使った検索ができます
- 標準のANSI SQLのSELECT以外に SparkSQLのFunctionsが利用できます
やってみた
今回はお手軽にWebコンソールでやってみます。
1. サービス・インスタンスの作成
カタログから「SQL Query」を選んでお好きなサービス名を指定して「作成」ボタン。
インスタンスができたら「Open UI」でWebコンソールを開きます。
2. Webコンソールの起動
以下がWebコンソールです。これで検索の準備ができました。
| # | 説明 |
|---|---|
| 1 | サンプルQuery.データも用意されています |
| 2 | APIリファレンスとドキュメントへのリンク |
| 3 | ここにSELECT文を書きます |
| 4 | 実行結果ファイルはここで指定したICOSバケットに保管されます |
| 5 | 実行ボタン |
| 6 | SELECT実行結果の表示領域 |
3. 検索前のICOSの状態
検索前のICOSのバケットの状態を確認しておきます。
【入力(検索対象)】
ロケーション us-geoのバケット名 sql-in-e051466-usにCustomer.csvというCSVファイルを置きました。中身は以下のようなテスト用の日本語CSVデータです。(UTF-8）今回はこのファイルを検索対象にします。
SQLQueryではICOSのインスタンス名( ICOS-e051466)は意識不要です。ロケーションとバケット名でユニークになるので。
【出力】
ロケーション us-geoのバケット名 sql-out-e051466-usを用意しておきます。中身は空です。
5. SELECTする
やってみる
WebUIで以下のSELECT文を入力し「Run」ボタンを押すと、数秒で結果が表示されます。きちんとカラムの選択、条件の適用、並べ替え等が効いているのがわかります。
SELECT CUSTOMERID,NAME_KANJI,AREA,TELNO,MARRIAGE,GENDER,BIRTHDAY
FROM cos://us-geo/sql-in-e051466-us/Customer.csv
WHERE AREA ="北海道"
ORDER BY CUSTOMERID DESC;
FROM 句の書き方
基本、標準のSELECTなので迷うことは無いのですが、一点だけ。FROM句で検索対象のファイルを指定するところは独自なのでご説明します。
ファイルを表す書き方はドキュメントにありますが、以下のURIの形式です。
-
endpointは"s3-api.us-geo.objectstorage.softlayer.net"などの正式の長い名前でもいいですが、**”us-geo”**などのaliasが使えます
-
バケット名とオブジェクト名はそのまま書けばいいです（楽！）
-
CSV以外の形式ではSTORED AS でファイルの形式を指定する必要があります。(STORED AS JSON, STORED AS ORC, or STORED AS PARQUET) (CSVはデフォルトなので割愛可能)
SELECT *FROM cos://us-geo/sql-in-e051466-us/Customer.json STORED AS JSON -
SQLの表現についてはパネル右上のサンプルボタンを押すと様々なSELECTの例があるのでご参照ください。JOIN,UNION,HAVINGなど標準のSQL(SELECT)でできることは、できます。
-
検索対象のファイルは正確に言うとRDBでいうテーブルのパーティションのイメージであり、よって複数を連結できます。つまり
SELECT *FROM cos://us-geo/sql-in-e051466-us/Customer*.csvとファイル名に*(ワイルドカード)を使え、その場合は名前が条件に一致する複数のファイルを一つのテーブルとして扱え、一度に検索できます。これは便利ですね！
結果のファイル
以下のようにSELECT実行毎に出力で指定したバケット上にjobidの名前がついた３つのファイルができます。＃１、＃２のファイルは空で＃３のファイルにはSELECTの結果が入っています。
以下のように、この出力をFROMで指定して再利用することもできます。
SELECT *
FROM cos://us-geo/sql-out-e051466-us/jobid=b7580d94-c9f9-46c2-a7a8-49e9cb52a9d5
ORDER BY CUSTOMERID;
当該出力ファイル群は自動的に消されません=増え続けます。定期的に運用するなら消しこみを考える必要があります
前述の#1,#2のファイルは空ですが、「検索結果を再利用するなら」消してはいけないそうです。（使わないならもちろん消して結構です）
性能面の考慮点 ～ビッグデータをSELECTするなら
ブログ記事How to Layout Big Data in IBM Cloud Object Storage for Spark SQLに考慮点が記載されています。いくつか抜粋すると
- ファイルは128MBくらいで分割しましょう
- Parquet形式など格納効率のいい(カラムナ）形式を使いましょう
- CSV or JSONをgzipで圧縮しない
- (時系列で分割するなど)Hive style のpartitioningを行いましょう
などなど。
REST API
詳しくはREST APIのリファレンスに記載がありますが、SQL Queryはジョブを起動する非同期型のサービスです。ゆえに
- ジョブを起動する(POST)→jobidが戻る
- 一定間隔でジョブの状況(Status)を確認する(GET)
- StatusがCompleteになったら、出力のバケットから結果をダウンロードする
の段階を踏みます。（=SELECTの結果を同期的に直にプログラムに戻すことはできません）
最後に
以上です。あれこれ触ってて、「要はICOS-SparkSQL連携のラッパーだよな、これ..」とも思いましたが、実際に自分で環境を用意してICOS上のファイルをSparkSQLで並列に検索できるようにするのはインフラやプログラミングの知識が必要です。できないとは申しませんが、何もないところから１分でCSVやJSONファイルをSELECT検索できるのは便利でいいんじゃない、と思いました。GAしたら皆様もぜひ使ってみてくださいませ。
参考文献
ドキュメント
IBM Cloud SQL Query - 量が少ないので全部読めます。
ネット/ブログ記事
IBMソリューション・ブログ
-
IBM Cloud SQL Query の発表 - お披露目記事 - Query and Analyze Call Logs with IBM Cloud SQL Query - Aspera & ややこしいSQLもかける、って例
- Geospatial Without Projections in IBM Cloud SQL Query - 地理情報機能の紹介シリーズ#1
Medium - Analyzing Data with IBM Cloud SQL Query
動画やデモ
YouTube: IBM Cloud SQL Query - Learning Center
- 動画が5本あります