必要なのはブラウザだけ！　最新SQLが無料で試せる「Oracle Live SQL」のススメ

Live SQLの小技を紹介

井上克己氏（以下、井上）：みなさん、こんにちは。本日は貴重な時間をいただきまして、品川までお越しいただきまして、ありがとうございます。日本オラクルでデータベース関連のエンジニアをしております、井上と申します。

オラクルの公式ブログで過去にSQLの小技などを書かせていただいてるんですが、本日はその小技なども交えながら、実際のデモをお見せします。

具体的な内容としては、オラクル社が提供している「Live SQL」というサービスを中心に説明していきます。最後に時間が余りましたら、「SQLcl」というJava系の「SQL*Plus」に代わるツールをご紹介していきたいと思います。

まずはLive SQL基本編ということで、Live SQLというのは無償のサービスです。ブラウザだけで最新のSQLを試すことが出来ます。

1点だけみなさまに挙手お願いしたいんですけれども、この中で、Oracleのユーザーアカウントを作っている方、挙手いただけますでしょうか？

（会場挙手）

ありがとうございます。こちらはOTNアカウントがあれば無償で使えるサービスです。

Live SQL活用法

実はつい1週間ぐらい前にバージョンアップしまして、もともと12.2だったんですけど、マイナーバージョンアップをしている状態になっています。

ここには、オラクル社のデータベース製品担当者、開発責任者が15人ぐらいいるんですが、彼らがSQLのサンプルを投稿したり、OTNアカウントを持っている方がSQLのサンプルをパブリックに公開しているサイトでもあります。

だいたいの数を見ていただけると思うんですが、少し合計を取らなかったんですけど、PL/SQLだけで280程度と、相当な数がすでに公開されている状態です。

では見つけたいサンプルをどうやって見つけるのか。SQLって、例えばJavaやPythonと比べてキーワードが多かったり、文法が難しくて覚えられなかったりすると思うのでGoogle先生に頼りたいですよね。Googleでもいいんですが、Live SQL自体にも検索機能が備わっております。

あと、もう言ってしまったんですが、Googleでもクローリングしていますので、かなりひっかかってきます。みなさまご存じかと思うんですけど、「site:livesql.oracle.com」と検索すれば「livesql.oracle.com」以下のものだけを探してきますので、SQLの断片を知っている場合であれば、こちらのほうが確実かと思います。

これはSQLとなにも関係ないんですけれども、この「site:」はかなり使えるGoogleの技だと思いますので、覚えて帰っていただければと思います。

これはサンプルですね。Composite Range-List Partitionの例ですが、コメントをつけられたり、結果のサンプルが出ていたりします。そのため、見るだけでもけっこう勉強になると思います。

ただ1つ残念なのは、サンプルコードも、後ほどご紹介するチュートリアルもそうなんですが、今のところ英語のものしかないところです。

自分でテーブルを作ったり、インデックスを作ったり、ストアド・プロシージャなども作れます。作ったオブジェクトは、ハンバーガーメニューのSchemaから確認ができます。

注意点ですが、タイムアウトがけっこう早いです。だいたい1時間ぐらいでたぶん消えてしまいますので、なにかしら作業していないと消えてしまいます。この点にご注意ください。

スクリプトの例

今、お見せしているのはチュートリアルというタイプのサンプルです。このあたりを少しデモしてみたいと思います。これはまだログインしていない状態ですね。

ここで今、「range-list partition」と検索してみます。これはインクリメンタルサーチですので、ひっかかりました。Range-List Partition、この文法を覚えるのは難しいと思うんですが、サンプルが出てきます。

見つかったので少しログインしてみます。「Login and Run Script」ボタンを押します。実際にはRunせず、表示するだけです。

これをどうやって見るかといいますと、ここにコメント・説明があります。

この例は2階層のパーティションの表です。1階層目がdate、日付ですね。日付でまずパーティション化します。2階層目はアメリカの州の名前ですね。カリフォルニアだとCA、ニューヨークだとNYみたいな、州のコードで2階層目をパーティションしてある例です。

このページの見方ですが、SQL文は裏が真っ白ですね。そして少しグレーがかってるところは、実際に流したらこういう結果を表示しているところになります。

これがスクリプトの例でした。今度は別の例を探してみます。先ほどチュートリアルと言いましたので、今度はチュートリアルの例を探してみます。

今、キーワードとして「match_recognize」と打ったところ、けっこう出てきました。

これは12cからの関数で、かなり使い方が難しいんですが、けっこう登録されていますね。

その中で1つ、JSONを使った例がありましたので開いてみました。

Tutorialとありますね。チュートリアルの場合は、作者ががんばって説明をつけられている場合が多いです。英語になるんですが、「このSQLはどういう処理をするものなのか」詳しく書かれていることが多いです。

もう1つは、スクリプトの場合と違いまして、左側がエディタになっていて、SQLの一文だけを取り込むことができます。

ここまでが基本的な機能のデモです。まだ実際に動かしていないですけど、これからやっていきます。

JSONを使ってデモをしてみる

これは実際のデモですね。実際にテーブルを作ったりしたいんですが、テーマはJSONです。お客様にもすでに日頃から使っている方も多いと思います。

Oracle DatabaseのJSON機能は、3年前に12cがリリースされてかなり実装されました。例えばIS JSON制約や、JSON_VALUE operatorが実装されています。

ここに「SQL:2016」とあります。去年の12月にANSI（注：米国国会規格協会）から出された標準があるんですが、それにかなり近いものになっています。本日はそうした例をいくつかお見せしたいと思います。

JSONデータのサンプルは今日のイベントのセッションデータを使ってみます。

このセッションは世界20都市を行脚するセッションなんですが、すでに半分以上終わっています。なので合計するとすでに280セッションぐらいがすでに終わっています。そのセッションデータをJSONデータとして取ってくることができます。そのデータを使ってみます。

すでにINSERT文に変えてあります。ここはあとからキーになってきますので、少しデータを見ていきたいと思うんですが、このJSONは非常に単純な構造ですね。titleとcompanyという2つキーを持つだけ。ネストをしていないJSONになっています。

この今見せているところは、先ほどの東京ガスiネットさんのセッションのデータです。非常に単純なデータなんですが、これが約300個あります。これを使っていきます。

あともう1つ、ここで小技が出てるんですが、Oracleの文字列は、普通はシングルクォートでくくりますよね。ですが、とくにJSONなどのデータを扱う場合には、この文字列の開始をカスタマイズできます。

おそらくPerlなどでもできると思いますけど、文字列のクォート文字列を宣言しているので、実際にJSONの中にシングルクォートが入ってきても大丈夫という小技を使っています。

作業を快適にする小技

このデータを取り込むのですが、まずはテーブルを作らなくてはいけないですね。ハンバーガーメニューに「My Scripts」とありますが、ここに自分のアップロードしたファイルを置くことができます。

今、DDL文が見えていますが、コメントも書くことができます。「--」はコメントですね。

なので、これを実際に実行すると、「create table」は1個のコメントがないほうしか実行されませんでした。

次に、このテーブルの中に、先ほどのJSONデータをインサートしていきます。言い忘れましたが、Live SQLはSQL*LoaderもData Pumpも使えないので、基本的にはINSERT文でデータを入れます。

これはINSERT文ですね。大量にありますので、実行します。

これを実行すると、INSERT文が289個あったので、289のSQL文が実行されました。

実際にどういうテーブルができたかを見ていきます。ハンバーガーメニューのSchemaですね。ここに1個だけテーブルあります。

はい、テーブルができました。

これは右にもハンバーガーメニューがあります。「Query」というボタンがありますので、ここをクリックするとselect文が編集画面に出てきます。

ここで実際にどういったデータが入っているのかを見ていきます。全行ではなく、最初の100行ぐらいを見てみましょうか。ANSI SQLで定義されている書き方ですね。SQL Serverだと「TOP 10」のように書けるんですが、Oracleでは「fetch first ◯行」と書きます。

あとは、このLive SQLのWorksheetの次の小技なんですが、SQL*Plusなどにはスラッシュかセミコロンが必要ですよね。ですが、SQL Worksheetはそれが必要ありません。

あとはキーボードショートカットがありまして、Ctrl+Enterを押すだけで実行されます。今、Ctrl+Enterを押したので、実際に実行されました。すると、100行ぐらい下にきている状態ですね。

データがJSONの場合は、小技をLive SQL自身が持っていまして、データを折りたたむことができます。

日本語のデータもちゃんと入っていますし、ロシア語などいろいろな言語のデータが入っています。

JSONデータを集計する

今、未加工のデータを見てしまったのですが、このJSONデータのセッションタイトル、講演のタイトルだけを見たい場合の書き方をやってみます。

まず、テーブルにエイリアスをつけます。ここには「t」というエイリアスをつけました。テーブルのエイリアス「t」ですね。選択リストで「t.”カラム名”.JSONのキー」です。なので、ここは「title」と書きます。

試しに200にしてみたところ、200行分のセッションタイトルだけを持ってきました。

次は、このJSONデータはtitleとcompanyですよね。プレゼンターが所属する組織・会社の部分もありますが、そこを見てみます。ただ、今回は同じ会社の方が何回か講演されている場合もあるので、出現回数でソートをしてみます。

あと、オラクル社員がセッションをけっこうやっていますね。そこは除外してみます。なので「not like」のオラクル社員を除外しますので、大文字化したカラム名の値に、オラクルじゃないということですね。「order by」のカウントでソートをしてみます。

すると、これでよかったと思うんですが……「group by」がないですね。失礼しました。

これで、講演者の所属する組織で集計した結果が取れました。

東京ガスiネットさんは1回ですね。コンサル会社の方やベンダーさんなど、オラクル以外の会社の方もけっこう出てます。

中には9回もセッションを持たれている会社さんもあります。Red HatさんやIBMさんなどもやっています。こんなふうにJSONにデータを集計できます。