Oracle Big Data SQLを使用した全データの分析

概要

    目的

    このチュートリアルでは、データの保管場所（Oracle Database 12c、Hadoop、Oracle NoSQL Database、またはその組み合わせ）に関係なく、ビッグ・データ・プラットフォーム全体のデータを安全に分析する方法について説明します。重要なのは、オラクルの既存のスキル・セットとアプリケーションを利用して、これらの洞察を得られることです。Oracle Big Data SQLを使用すれば、データ・プラットフォーム全体にオラクルの機能豊富なSQLダイアレクトとセキュリティ・ポリシーを適用できます。このため、あらゆるデータから非常に簡単に洞察を得ることができます。

    Big Data SQLには、2つの部分があります。1）Exadata上の機能拡張されたOracle Database 12c外部表と、2）Oracle Big Data Appliance上のOracle Big Data SQLサーバーです。Oracle BDAでは、Big Data SQLサーバーによって、Hadoopに保存されているデータに対してSmartScanが適用されるため、パフォーマンスが向上します（詳しくは、こちらのブログ投稿を参照してください）。このラボで使用しているOracle Big Data Lite Virtual Machineには、Big Data SQLサーバーはインストールされていません。

    所要時間

    約90分

    前提条件

    このチュートリアルには、Oracle Big Data Lite Virtual Machine（VM）が必要です。VMは、Oracle Technology Network（OTN）のBig Data Liteのページからダウンロードできます。
    このレッスンを開始する前に、次の手順を実行します。

    1. Big Data Liteを起動したら、LinuxデスクトップにあるStart/Stop Servicesアプリケーションを使用して、次のサービスが開始されていることを確認します:
      • ORCL
      • Zookeeper
      • HDFS
      • Hive
      • NoSQL
      • YARN

      開始されているサービスの名前の横には、*が付いています:
      services

    2. マウスの右クリックで表示されるメニューを使用して、SQL Developerがインストールされているマシン上のディレクトリに、bigdatasql_hol_otn_setup.sqlとbigdatasql_hol.sqlの2つのファイルを保存します。この場所を覚えておいてください。この後すぐに、SQL Developerでこれらのファイルを開きます。注：メニューの'Save Link As'オプションを使用します。
    3. 次のように、デスクトップ・ツールバーのメニューからSQL Developerを起動します:

      4. SQL Dev

    4. SQL Developerで、両方のファイルを開きます。
    5. 次のように、「bigdatasql_hol_otn_setup.sql」スクリプトを選択してから、SQL Developerの「Run Script」ツールをクリックします。接続を求められたら、「moviedemo」の接続を選択して「OK」をクリックします。これで、このチュートリアルの設定は完了です。

      6. SQL Dev

    6. bigdatasql_hol_otn_setup.sqlスクリプトを閉じます。
    7. 続いて、次のようにbigdatasql_hol.sqlスクリプトでスクリプト上部のdrop文を複数選択して、「Run Statement」ツールをクリックします:

      8. SQL Dev

      注：これらの文で生成されるエラーは無視します。

    8. SQL Developerでbigdatasql_hol.sqlスクリプトを開いたままにしておきます。このチュートリアルで参照するすべてのコード例が含まれているためです。

    はじめに

    このチュートリアルは次の項に分かれています:

    1. シナリオの確認
    2. Oracle Big Data SQLの構成
    3. HDFSソースのアプリケーション・ログに関するOracle表の作成
    4. Hiveメタストアを利用したHadoopおよびOracle NoSQL Database内のデータへのアクセス
    5. ビッグ・データ・プラットフォーム全体へのOracle Databaseセキュリティ・ポリシーの適用
    6. すべてのデータに対してOracle Analytic SQLを使用
    7. Webログ・データに対するSQLパターン・マッチングの使用

    シナリオ

    Oracle MoviePlexはオンラインの映画ストリーミング会社です。サイトにアクセスするすべてのユーザーに対して、過去の視聴履歴に基づいてお勧めのムービーが表示されます。このお勧めのムービーのリストは頻繁に更新され、ユーザーのプロファイルの一部となります。Oracle NoSQL Databaseにはこのようなプロファイルが保存されており、大規模なユーザー・コミュニティのアプリケーション問合せリクエストが非常に短い待機時間で送られます。またWebサイトでは、すべての顧客のやり取りがJSON形式の大量のログ・ファイルで収集されます。そのアクティビティ・データに含まれる情報のロックを解除して、そのデータウェアハウス内のお勧めデータおよびエンタープライズ・データと組み合わせることで、企業は顧客行動、製品の効果、Webサイト・コンテンツの構成などについて、より深く理解することができます。

    この会社は、オラクルのBig Data Management Systemを使用してデータ・プラットフォームを統合し、分析をしやすくしています。

    Oracle Big Data Management System

    Oracle Big Data Management Systemでは、Hadoop、NoSQL、Oracle Databaseの間で問合せ言語、管理プラットフォーム、セキュリティ・フレームワークを共通化することで、データ・プラットフォームを統合しています。Oracle Big Data SQLは、このプラットフォームの主要コンポーネントです。Oracle ExadataではOracle Big Data SQLによって、オラクルの機能豊富なSQLダイアレクトを使用してOracle Big Data Appliance上のデータをシームレスに問い合わせることができます。HadoopやOracle NoSQL Databaseに保存されているデータについては、Oracle Database内のその他のデータとまったく同じ方法で問合せが行われます。つまりユーザーは、既存のスキル・セットとアプリケーションを使用して、これらの新しいソースから新たに洞察を取得できるということです。

    Oracle MoviePlexの場合、WebサイトでクリックするたびにOracle Big Data Appliance（Oracle BDA）上のHDFSにデータが流れます。データがOracle BDAに到達するとすぐに、Oracle ExadataユーザーがOracle Big Data SQL経由でそのデータにアクセスできるようになります。また、Oracle Big Data SQL経由でOracle NoSQL Database内のお勧めデータにもアクセスできるようになります。このハンズオンでは、HDFSに保存されている"クリック・データ"と、NoSQL Database内のお勧めデータおよびOracle Database内の収益データを組み合わせて、サイトを訪問する顧客の買い物と購入のパターンをより的確に把握する方法について学習します。

    チュートリアルの開始にあたり、まずOracle BDAへのアクセスがOracle Exadata内でどのように構成されているかを確認しましょう。

Part 1 - Oracle Big Data SQLの構成

    ここでは、Oracle Big Data SQLの構成方法について学習します。この構成プロセスによって、Oracle Exadataから、Oracle Big Data Appliance上のHadoopまたはOracle NoSQL Database内のデータの問合せが実行できるようになります。

    タスクの構成

      概要で説明したとおり、このVMではOracle Database 12c内の拡張された外部表を使用して、HDFSおよびOracle NoSQL Database内のデータにアクセスします。Big Data SQLサーバーはインストールされていません。実際のOracle Big Data環境では、2つのインストール作業があります:

      1. Mammothを使ったOracle BDAへのOracle Big Data SQLのインストール - Oracle BDAのインストールおよび構成ユーティリティ。この手順では、Oracle BDAの各ノードでBig Data SQLサーバーを設定し、ローカル・データでのSmartScanを有効にします。
      2. Oracle Exadataの各データベース・ノードでのBig Data SQL-Exadataインストール・スクリプトの実行。この手順では、ExadataからOracle BDA上のBig Data SQLサーバーへの接続を設定します。この手順には、Hadoopクライアント、構成ディレクトリと構成ファイル、Big Data SQLエージェント、Oracleディレクトリ・オブジェクトなどのインストールが含まれます。

      2番目の構成タスクで生成されるいくつかの重要な要素を確認しましょう。

    共通ディレクトリとクラスタ・ディレクトリの確認

    Exadataサーバーには、2つのファイル・システム・ディレクトリ（共通ディレクトリとクラスタ・ディレクトリ）が必要です。これらのディレクトリには、ExadataサーバーからOracle BDAへの接続に必要な構成ファイルが保存されています。各ディレクトリの概要は次のとおりです。

    共通ディレクトリ

    • 共通ディレクトリには、いくつかのサブディレクトリと、bigdata.propertiesという名前の重要なファイルが含まれています。このファイルには、すべてのOracle BDAクラスタに共通の構成情報が保存されています。特に、JVMの構成とデフォルト・クラスタの識別に使用するプロパティと値のペアが含まれます。
    • Oracle Databaseが実行されるオペレーティング・システムのユーザーは、bigdata.propertiesファイルにアクセスできる必要があります。
    • Exadataの場合、共通ディレクトリがクラスタ全体のファイル・システムに存在する必要があります。すべてのExadata Databaseノードが、完全に同じ構成情報にアクセスすることが重要です。

    クラスタ・ディレクトリ

    • クラスタ・ディレクトリには、特定のOracle BDAクラスタへの接続に必要な構成ファイルが含まれます。
    • また、クラスタ・ディレクトリは共通ディレクトリのサブディレクトリである必要があり、ディレクトリの名前が重要です。これは、クラスタの識別用の名前です。この名前については、後で詳しく説明します。

    まず、共通ディレクトリのbigdata.propertiesファイルを確認してみましょう:

      デスクトップ・ツールバーを使用して、ターミナル・ウィンドウを起動します （同様にSQL Developerも開きます）。

      launch SQL Developer and Terminal

      ターミナル・ウィンドウで共通ディレクトリの場所に移動し、bigdata.propertiesファイルの内容を確認します。プロンプトで次のコマンドを入力します:

      cd /u01/bigdatasql_config/
      cat bigdata.properties

      結果：コマンドの出力は次のようになります:

      bigdatasql.properties

      注:

      • Hadoopクラスタに固有ではないプロパティには、Java VMの場所、クラスパス、LD_LIBRARY_PATHなどの項目が含まれます。
      • また、ファイルの最終行ではデフォルト・クラスタ・プロパティ（この場合はbigdatalite）を指定します。 
      • 後で説明するとおり、デフォルト・クラスタによって、Hadoop内のデータにアクセスしているOracle表の定義が簡素化されます。
      • 当社のハンズオン・ラボにはbigdataliteという単一クラスタがあります。bigdataliteのサブディレクトリには、bigdataliteクラスタの構成ファイルが含まれます。
      • クラスタ名は、サブディレクトリ名と（大文字/小文字も含めて）一致する必要があります。

      次に、クラスタ・ディレクトリの内容を確認しましょう。

      ターミナル・ウィンドウを使用してクラスタ・ディレクトリに変更し、プロンプトで次のコマンドを実行して内容を確認します:

      cd /u01/bigdatasql_config/bigdatalite
      ls

      結果：上記のコマンドの出力は次のようになります:

      bigdatasql cluster

      注:

      • これらのファイルは、Oracle DatabaseをHDFSおよびHiveに接続するために必要です。
      • 各クラスタには、そのクラスタ固有の構成ファイルが含まれる独自のディレクトリがあります。

      これらのファイル・システム・ディレクトリに対応するOracleディレクトリ・オブジェクトは、インストール・プロセスで作成されます。

      Oracleディレクトリ・オブジェクトの確認

      前に説明したとおり、構成ファイルはファイル・システムに保存されています。これらのフォルダをポイントする、対応するOracleディレクトリ・オブジェクトは、インストール・プロセスで作成されます。

      Oracleディレクトリ・オブジェクトには、次のような固有のネーミング規則があります:

      • ORACLE_BIGDATA_CONFIG：共通ディレクトリを参照するOracleディレクトリ・オブジェクト
      • ORACLE_BIGDATA_CL_bigdatalite：クラスタ・ディレクトリを参照するOracleディレクトリ・オブジェクトこのディレクトリのネーミング規則は次のとおりです:
        • クラスタ・ディレクトリの名前の先頭にはORACLE_BIGDATA_CL_が付く。
        • 後にクラスタ名が付く（"bigdatalite"など）。この名前では大/小文字が区別され（小文字の名前には必ず引用符を付けてください）、文字数が15字以内に制限されます。
        • この名前は、ファイル・システムの物理ディレクトリ名と一致します（同様に、大/小文字が区別されます）。

      これらのOracleディレクトリ・オブジェクトを確認します:

        SQL Developerで、bigdatasql_holスクリプト・ファイルを使用して次の文を実行します:

        connect as moviedemo

        注:

        • SQL Developerで、（上記の）Run Statementツールを使用して、選択した1つ以上の文を実行します。
        • ディレクトリ・オブジェクトでは大/小文字が区別されます。この例ではbigdataliteクラスタは小文字であり、次のコマンドを使用してインストール・スクリプトで作成したものです:
        create or replace directory "ORA_BIGDATA_CL_bigdatalite" as '';

        クラスタ・ディレクトリの場所は指定されていません。ディレクトリは次のようになります:

        • ORACLE_BIGDATA_CONFIGのサブディレクトリになる
        • Oracleディレクトリ・オブジェクトによって識別されるクラスタ名を使用する

        Oracle Big Data SQLエージェントの確認

        インストール・スクリプトを実行すると、Oracleディレクトリ・オブジェクトの他に、Big Data SQLエージェントも作成されます:

        • このマルチスレッド・エージェントによって、Oracle DatabaseとHadoopの間でメタデータがブリッジされます。JVMは、プロセスごとではなく1つだけ起動されます。このため、速度が大幅に低下することはありません。
        • マルチスレッド・エージェントが未設定であった場合は、次のコマンドを実行して作成します:

          create public database link BDSQL$_bigdatalite using 'extproc_connection_data';
          create public database link BDSQL$_DEFAULT_CLUSTER using 'extproc_connection_data';


      構成を確認したので、HDFSとOracle NoSQL DatabaseのデータにアクセスするOracle表を作成しましょう！

       

Part 2 - アプリケーション・ログに関するOracle表の作成

    ここでは、HDFSに保存されているデータに関するOracle表を作成してから、そのデータの問合せを実行します。この例では、ORACLE_HDFSドライバを使用します。Hiveカタログに保存されているメタデータは利用しません。

    HDFSに保存されているアプリケーション・ログの確認

      ムービー・アプリケーションからHDFS（特にディレクトリ/user/oracle/moviework/applog_json）にデータがストリームされました。そのログ・データを確認しましょう。:

      ターミナル・ウィンドウを開きます。

      次のコマンドを実行して、HDFSに保存されているログ・ファイルを確認します:

      hadoop fs -ls /user/oracle/moviework/applog_json

      結果：出力は次のとおりです:

      ls movielog

      ファイルの内容を確認して次のコマンドを実行します:

      hadoop fs -tail /user/oracle/moviework/applog_json/movieapp_log_json.log

      結果：出力は次のとおりです:

      tail log

      ファイルには、Webサイトでのすべてのクリックが含まれます。JSONログには、各インタラクションに関する次の情報が取り込まれます:
      • custid: サイトにアクセスする顧客
      • movieid: ユーザーがクリックしたムービー
      • genreid: ムービーが属するジャンル
      • time: アクティビティの発生時刻
      • recommended: 顧客がお勧めのムービーをクリックしたかどうか
      • activity: 実行される可能性があるさまざまなアクティビティのコード、たとえばログイン/ログアウト、ムービーの視聴、ムービーの購入、ムービー一覧の表示など
      • price: 購入されたムービーの価格

    アプリケーション・ログに関するOracle表の作成

      ソース・データを確認したので、ファイルに関するOracle表を作成します。この表は、非常にシンプルなもので、各レコードにJSONドキュメントが含まれる単一の列です。次に、Oracle SQLを使用して、各ドキュメント内のJSONフィールドを簡単に解析します:

      SQL DeveloperのSQLワークシートに移動して、次のSQL文を実行します（注：これらの文は、すべてbigdatasql_hol.sqlスクリプトに含まれます）:

      CREATE TABLE movielog
        (click VARCHAR2(4000))
        ORGANIZATION EXTERNAL
        (TYPE ORACLE_HDFS
         DEFAULT DIRECTORY DEFAULT_DIR
         LOCATION ('/user/oracle/moviework/applog_json/')
        )
        REJECT LIMIT UNLIMITED;

      上記のコードでは、Oracle外部表が拡張されて、Oracle BDAに保存されているデータがネイティブで理解されています。特に、次の属性が利用されています:

      • アクセス・ドライバORACLE_HDFSは、データがHDFSに保存されていることを示します
      • LOCATIONによって、表のソース・データが含まれるHDFSディレクトリ（またはファイルや複数のディレクトリ）を識別します。
      • DEFAULT DIRECTORYには、外部表によって生成されるログ・ファイルが含まれます（ロギングが有効な場合）。
      • REJECT LIMITは、問合せを実行している各パラレル問合せスレーブに適用されます

      次のコマンドを実行して、表movielogに含まれるデータを確認します。

      SELECT * FROM movielog WHERE rownum < 20;

      結果：出力は、前のテール文と似ています。レコードはJSONドキュメントごとに返されます。

      simple json query

      外部表に適用できるオプションは多数あり、データの問合せと処理の方法に影響します。これらのオプションをいくつか見てみましょう。次のDDLコマンドを使用して、表movielog_plusを作成します:

      CREATE TABLE movielog_plus
        (click VARCHAR2(40))
        ORGANIZATION EXTERNAL
        (TYPE ORACLE_HDFS
         DEFAULT DIRECTORY DEFAULT_DIR
         ACCESS PARAMETERS (
                            com.oracle.bigdata.cluster=bigdatalite
                            com.oracle.bigdata.overflow={"action":"truncate"}         
                           )
         LOCATION ('/user/oracle/moviework/applog_json/')
        )
        REJECT LIMIT UNLIMITED;

      上記コードから次のことがわかります:

      • まず、click列がVARCHAR2(40)に変更されています。これは明らかに問題になります。JSONドキュメントの長さがそのサイズを超えています。この状況に対処するには、次のような多くの方法があります:
        • エラーを生成してレコードを拒否し、その値をnullに設定するか代替値に置き換える。
        • 単にデータを切り捨てる。ここでは、データを切り捨てています。また、この切捨てアクションを表内のすべての列に適用しています。切り捨てる個々の列を指定することもできます。
      • 次に、クラスタbigdataliteが指定されています。このクラスタは、デフォルトの代わりに使用されます（この場合はたまたま同じになっています）。現在は、所定のセッションが単一クラスタにのみ接続できます。

      次のコマンドを実行して、表movielog_plus内のデータを確認します:

      SELECT * FROM movielog_plus WHERE rownum < 20;

      query truncating json

      注：各JSONドキュメントは、Oracle表の列のサイズ（40文字）に基づいて切り捨てられます。実際には、JSONドキュメントの切捨てはそれほど便利なものではありませんが、ここでは分かりやすく説明するために使用しています。

      Oracle Database 12c（12.1.0.2）では、JSONがネイティブでサポートされています。このため、問合せでJSONドキュメントから属性データを簡単に抽出できます。SQL Developerで次の問合せを実行します:

      SELECT m.click.custid, m.click.movieid, m.click.genreid, m.click.time
      FROM movielog m
      WHERE rownum < 20;

      結果：問合せの出力は次のようになります:

      json query

      注:

      • 選択リスト中の列の仕様は、JSON属性のフルパスです。
      • この仕様は、先頭に表の別名（"m" - 注：これは必須です）が付き、その後にカラム名（"click"）、大/小文字が区別されるJSONパス（"genreId"など）が続きます。

      Oracle Big Data SQLの主要な強みの1つは、Oracle DatabaseとHadoopからのデータを組み合わせた質問に答えられることです。次のコマンドを実行して、"click"データとmovieディメンション表からのデータを組み合わせます:

      SELECT f.click.custid, m.title, m.year, m.gross, f.click.rating
      FROM movielog f, movie m
      WHERE f.click.movieid = m.movie_id
      AND f.click.rating > 4;

      結果：問合せ結果の構造は、次の内容と似ています（レコード出力の順序は異なる可能性があります）:

      query movie and clicks

      注：上記の出力を見ると、特定の顧客のWebサイトに対する評価とムービーの総収入との相関関係が分かります。

      次のコマンドを実行してビューを作成し、JSONデータに対する問合せを簡素化します。このビューは、この後の演習でセキュリティ・ポリシーが表に適用される場合にも便利です:

      CREATE OR REPLACE VIEW movielog_v AS
      SELECT
        CAST(m.click.custid   AS NUMBER) custid,
        CAST(m.click.movieid AS NUMBER) movieid,
        CAST(m.click.activity AS NUMBER) activity,
        CAST(m.click.genreid AS NUMBER) genreid,
        CAST(m.click.recommended AS VARCHAR2(1)) recommended,
        CAST(m.click.time AS VARCHAR2(20)) time,
        CAST(m.click.rating AS NUMBER) rating,
        CAST(m.click.price AS NUMBER) price
      FROM movielog m;

      次のコマンドを実行して、Oracle MoviePlexの平均評価と収益がトップ10のムービーの相関関係を把握します:

      SELECT m.title, m.year, m.gross, round(avg(f.rating), 1)
      FROM movielog_v f, movie m
      WHERE f.movieid = m.movie_id
      GROUP BY m.title, m.year, m.gross
      ORDER BY m.gross desc
      FETCH FIRST 10 ROWS ONLY;

      結果：出力は次のようになります:

      compare to top 10

      注：このデータは、MoviePlexユーザーが必ずしも超大作映画に満足しているわけではないことを示しています。

    まとめ:

    たった数分で、HDFSからのデータについてOracle Database表の作成および問合せを実行し、そのデータを他のOracle Database表と結合することができました。 

    次に、Hiveメタストアですでに使用可能なメタデータを利用して、Hadoop内の複雑なデータの問合せを簡単に実行できるようにします。

Part 3 - Hiveメタストアを利用したHadoopおよびOracle NoSQL Database内のデータへのアクセス

    Hiveによって、HadoopストアおよびNoSQLストアに保存されているデータにSQLからアクセスできます。Hiveには、Hive実行エンジンとHiveメタストアという2つの部分があります。 

    Hive実行エンジンでは、発行したSQLに基づいてMapReduceジョブを起動します。MapReduceはバッチ処理フレームワークであり、 インタラクティブな問合せおよび分析用ではありませんが、使い慣れたSQL言語を使った大量のデータセットの問合せには非常に便利です。重要なのは、コーディング（Java、Pigなど）が不要である点です。HiveでサポートされるSQLはまだ限定的（SQL92）ですが、時間をかけて改良されています。

    Hiveメタストアは、Hadoopに保存されるデータの標準的なメタデータ・リポジトリとなっています。Hiveメタストアには、表の定義（表の名前、列、データ型）、データファイルの場所（HDFS内のディレクトリなど）、およびそのデータの解析に必要なルーチン（StorageHandlers、InputFormats、SerDesなど）が含まれます。Hive経由でアクセスしたデータを、Hadoopに保存する必要はありません。たとえば、Oracle NoSQL DatabaseにはStorageHandlerという機能があり、Hive経由でそのデータにアクセスできます。この機能は、Oracle Big Data SQLによって利用されます。

    Hive実行エンジンをバイパスしながらHiveメタストアを使用する問合せ実行エンジンは多数あります。Oracle Big Data SQLはこのようなエンジンの1つです。つまり、複数の製品（Hive、Oracle Big Data SQL、Impala、Pig、Spark SQLなど）で同じメタデータを共有できるということです。次の演習では、この実際の例を見ることができます。

    まず、Hiveで定義された表を確認しましょう。これらのHiveの定義を確認した後、HDFSとOracle NoSQL Databaseに保存されている、基盤となるHiveデータを問い合わせる表をOracle Databaseで作成します:

    Hiveに保存されている表の確認

      Hive内の表は、データベースに整理されます。この例では、デフォルト・データベースに複数の表が作成されています。Hiveに接続して、これらの表を調べます。

      ターミナル・ウィンドウを開いて、コマンド・プロンプトで次のコマンドを実行します:

      bee

      結果：このコマンドは、beeline（Hive JDBCクライアント）を実行するためのショートカットです（/opt/bin/beeを参照）。beelineは、Hiveの非常に基本的なコマンドライン・インタフェース（CLI）です。

      hive cli

      プロンプトで次のコマンドを実行して、デフォルト・データベースの表のリストを表示します:

      show tables;

      結果：出力のとおり、データベース内でいくつかの表が定義されています。Avroデータ、JSONデータ、およびタブ・デリミタ付きテキスト・ファイルに関する表が定義されています。

      show tables

      JSONデータについて定義されている2つの表を確認しましょう。

      最初の表は非常にシンプルで、前の演習でOracle Databaseで定義した外部表と同等です。プロンプトで次のコマンドを実行して、表の定義を確認します。:

      show create table movielog;

      結果：表のDDLが表示されます。

      show movielog

      注:

      • clickという単一の文字列の列があります。また、表では/user/oracle/moviework/applog_jsonフォルダ内に保存されているデータを参照しています。
      • JSONデータの特別な処理はありません。つまり、属性を列に変換しているルーチンはありません。表では、JSONが単なる1行のテキストとして表示されています。

      続いて、次のコマンドを実行してmovielog表のデータの問合せを実行します:

      select * from movielog limit 10;

      結果：次の出力が生成されます:

      query movielog

      注:

      • 選択リストには列がなく、フィルタが適用されていないため、問合せを実行すると単にファイルがスキャンされて結果が返されます
      • MapReduceジョブは実行されません。

      JSONデータの問合せには、もっと便利な方法があります。次の手順では、Hiveでシリアライザ/デシリアライザ（SerDe）を使用して、どのようにJSONデータを解析できるかについて説明します。

      2番目の表では同じファイルの問合せを実行していますが、今回は属性を列に変換するSerDeを使用しています。次のコマンドを実行して、表の定義を確認します:

      show create table movieapp_log_json;

      結果：2番目の表のDDLが表示されます。

      show movieapp_log_json

      注:

      • JSONドキュメントのフィールドごとに列が定義されているため、データの理解と問合せがずっと簡単になっています。
      • Javaクラスorg.apache.hive.hcatalog.data.JsonSerDeを使用して、JSONファイルをデシリアライズします。

      これは、読取りパラダイムでのHadoopのスキーマも示しています。ファイルはHDFSに保存されますが、ファイルが読み取られないと、HDFSにスキーマが関連付けられません。この例では、同じデータの読取りに2種類のスキーマを使用しています。これらのスキーマは、Hive表movielogおよびmovieapp_log_jsonによってカプセル化されています。

      movieapp_log_json表に対して次の問合せを実行して、評価の高いムービーを検索します:

      SELECT movieid, AVG(rating) AS avg_rating
      FROM movieapp_log_json
      WHERE rating IS NOT NULL
      GROUP BY movieid
      ORDER BY avg_rating DESC, movieid ASC
      LIMIT 25;

      結果：次の出力が生成されます（問合せでこれらの結果が返されるまで、少し時間がかかる場合があります）。

      query movieapp_log_json

      注：これはデータの問合せと表示の方法として、前の表よりずっと優れています。

      • Hive問合せ実行エンジンによって、この問合せがMapReduceジョブに変換されました。  
      • 問合せの作成者は、基盤となる実装を気にする必要はありません。Hiveによって自動的に処理されるためです。

      recommendationという名前の3番目の表を確認します。この表はムービー作品のデータベースに含まれており、ユーザーごとのお勧めムービーが含まれるOracle NoSQL Database表について定義されています。

      show create table moviework.recommendation;

      結果：3番目の表のDDLが表示されます。

      show movieapp_log_json

      注:

      • TBLPROPERTIESは、Oracle NoSQL Databaseインスタンスの接続の詳細を示しています。
      • Oracle NoSQL Databaseストレージ・ハンドラoracle.kv.hadoop.hive.table.TableStorageHandlerによって、基盤となるデータ・ストアにアクセスできます。
    1. recommendation表に対して次の問合せを実行して、ユーザーにお勧めのジャンルとムービーを表示します:

      2. SELECT * FROM moviework.recommendation LIMIT 20;

      query movieapp_log_json

      プロンプトで!exit;コマンドを実行してbeelineを閉じます。

    Oracle表を作る場合のHiveメタデータの利用

      Oracle Big Data SQLでは、表の作成と問合せにHiveメタデータを利用できます。 

      ここでは、movieapp_log_json、movieapp_log_avro、recommendationという3つのHive表に関するOracle表を作成します。Oracle Big Data SQLでは、このデータの処理に必要な既存のStorageHandlersとSerDesを利用します。

      Oracle SQL Developerに移動します。次のDDLを使用して、movieapp_log_jsonというHive表に関する表を作成します。:

      CREATE TABLE movieapp_log_json (
          custid      INTEGER ,
          movieid     INTEGER ,
          genreid     INTEGER ,
          time        VARCHAR2 (20) ,
          recommended VARCHAR2 (4) ,
          activity    NUMBER,
          rating      INTEGER,
          price       NUMBER
        )
        ORGANIZATION EXTERNAL
        (
          TYPE ORACLE_HIVE
          DEFAULT DIRECTORY DEFAULT_DIR
        )
        REJECT LIMIT UNLIMITED; 

      ここで、ORACLE_HIVEという新しいアクセス・ドライバ・タイプがあります。このアクセス・ドライバによって、問合せのコンパイル時にOracle Big Data SQLが起動され、Hiveメタストアからメタデータの詳細が取得されます。デフォルトでは、外部表の名前movieapp_log_jsonと一致する表名のメタストアが問合せの対象となります。後で示すとおり、このデフォルトはACCESS PARAMETERSによって上書きできます。

      次のSELECT文を使用して、表の問合せを実行します:

      SELECT * FROM movieapp_log_json WHERE rating > 4

      結果：問合せの出力は次のとおりです:

      query movieapp_log_json

      注:

      • 前に説明したとおり、問合せのコンパイル時に、Oracle Big Data SQLからHiveメタストアに対して、データの選択に必要なすべての情報の問合せが行われます。このメタデータには、データの処理に必要なデータとクラスの場所が含まれます（StorageHandlers、InputFormats、SerDesなど）
      • この例では、Oracle Big Data SQLで、/user/oracle/movie/moviework/applog_jsonディレクトリで検出されたファイルをスキャンしてから、Hive SerDeを使用して各JSONドキュメントを解析しました。 
      • 実際のOracle Big Data Appliance環境では、Big Data SQLサーバーによって、クラスタのノード全体にわたって入力分割が並行して処理されます。その後、Smart Scanによってデータがローカルでフィルタリングされ、フィルタリング結果（行と列）がOracle Databaseに返されます。 

      次のSELECT文を使用して、表の問合せを実行します:

      SELECT movieid, AVG(rating)
      FROM movieapp_log_json
      WHERE rating IS NOT NULL
      GROUP BY movieid
      ORDER BY AVG(rating) DESC, movieid ASC
      FETCH FIRST 25 ROWS ONLY; 

      結果：問合せの出力は次のとおりです:

      query movieapp_log_json

      注:

      • この問合せでは、Big Data SQLでHiveメタデータが利用されていても、Hive実行エンジンは使用されていないことが分かります。前にはbeelineから同様の問合せを実行し、問合せの実行のためにMapReduceジョブが起動されました。MapReduceはここでは使用されませんでした。

      Hiveを使って、使用可能なデータをOracle Database表に保存されているデータと組み合わせることは簡単になりました。では、顧客が購入している高評価のムービーは何でしょうか。

      SELECT f.custid, m.title, m.year, m.gross, f.rating
      FROM movieapp_log_json f, movie m
      WHERE f.movieId = m.movie_id
      AND f.rating > 4

      結果：問合せの出力は次のとおりです:

      query movieapp_log_json

      注:

      • Oracle Databaseには、クリック・データのコンテキストが含まれるムービー検索表があります
      JSONテキスト形式ではなくAvro形式である点が異なります。次のコマンドを使用して、AvroベースのHive表に関するOracle表を作成します:

      CREATE TABLE mylogdata (
          custid      INTEGER ,
          movieid     INTEGER ,
          genreid     INTEGER ,
          time        VARCHAR2 (20) ,
          recommended VARCHAR2 (4) ,
          activity    NUMBER,
          rating      INTEGER,
          price       NUMBER
        )
        ORGANIZATION EXTERNAL
        (
          TYPE ORACLE_HIVE
          DEFAULT DIRECTORY DEFAULT_DIR
          ACCESS PARAMETERS ( com.oracle.bigdata.tablename=default.movieapp_log_avro )
        )
        REJECT LIMIT UNLIMITED;

      注：この例では、Oracle表とHive表の名前が一致していません。このため、Hive表（default.movieapp_log_avro）を参照するアクセス・パラメータを指定しました。

      次のコマンドを使用して、mylogdata表の問合せを実行します:

      SELECT custid, movieid, time FROM mylogdata;

      結果：問合せの出力は次のようになります:

      query mylogdata

      注：Oracle Big Data SQLでは、Avro InputFormatを利用してデータの問合せを実行しました。

      ここでは、問合せのコンパイル時にOracle Big Data SQLがどのようにHiveメタストアを使用して問合せ実行パラメータを決定しているかを示すため、Hive表movieapp_log_dataの定義を変更します。Hiveで表のLOCATIONフィールドを変更し、2個のレコードのみが含まれるファイルをポイントするようにします。

      ターミナル・ウィンドウに戻り、Hiveのbeeline CLIを起動してから、次の3つのコマンドを実行して、場所フィールドの変更と表の問合せを実行します:

      bee

      ALTER TABLE movieapp_log_json SET LOCATION "hdfs://bigdatalite.localdomain:8020/user/oracle/moviework/two_recs";

      SELECT * FROM movieapp_log_json;

      結果：Hive表からは次のような2つのレコードのみが返されます（2行に表示される実際のデータは異なる可能性があります）:

      hive two recs

      SQL Developerに戻り、 Oracle表を変更せずにmovieapp_log_jsonの問合せを実行します:

      SELECT * FROM movieapp_log_json;

      結果：Oracle Big Data SQLからHiveメタストアに問合せが実行され、LOCATIONの変更が検出されました。Oracle表から同じ2つの行が返されます（この2行はHiveで返される行と同じです）。

      oracle 2 rows

      最後に、Hive表をリセットして3行以上があることを確認します。beelineプロンプトで次のコマンドを実行します。

      ALTER TABLE movieapp_log_json SET LOCATION "hdfs://bigdatalite.localdomain:8020/user/oracle/moviework/applog_json";

      select * from movieapp_log_json limit 10;

      注：問合せによって、10個の行が返されます。

      Oracle NoSQL Databaseのお勧めデータへのアクセスには、同じ方法が使用されます。SQL Developerに戻って、recommendation表を作成します。次に、表から最初の20行を選択します。

      CREATE TABLE RECOMMENDATION
      (
        CUSTID NUMBER
      , SNO NUMBER
      , GENREID NUMBER
      , MOVIEID NUMBER
      )
      ORGANIZATION EXTERNAL
      (
        TYPE ORACLE_HIVE
        DEFAULT DIRECTORY DEFAULT_DIR
        ACCESS PARAMETERS
        (
          com.oracle.bigdata.tablename: moviework.recommendation
        )
      )
      REJECT LIMIT UNLIMITED;

      SELECT * FROM recommendation WHERE rownum <=20;

      結果：Oracle Big Data SQLからHiveメタストアに問合せを実行して、Oracle NoSQL Database表へのアクセス方法を決定しました。次にその情報を使用して、キーと値のストアから最初の20行を取得しました:

      query recommendation data

Part 4 - ビッグ・データ・プラットフォーム全体へのOracle Databaseセキュリティ・ポリシーの適用

    ほとんどのデプロイメントでは、Oracle Databaseに保護が必要な重要な機密データが含まれます。Oracle Databaseでは、豊富なセキュリティ機能（厳密認証、行レベルのアクセス、 データ・リダクション、データ・マスキング、監査など）によってデータの安全性を確保します。これらのセキュリティ・ポリシーは、Oracle Big Data SQLでも利用できます。つまり、1つのセキュリティ・ポリシー・セットを利用してすべてのデータを保護できるということです。

    この例では、個人を識別できる情報（顧客の姓やIDなど）を保護する必要があります。このため、customer表には、これら2つのフィールドを抽象化するOracle Data Redactionポリシーがすでに設定されています。この設定は次のように、DBMS_REDACT PL/SQLパッケージを使用して行いました:


    DBMS_REDACT.ADD_POLICY(
      object_schema => 'MOVIEDEMO',
      object_name => 'CUSTOMER',
      column_name => 'CUST_ID',
      policy_name => 'customer_redaction',
      function_type => DBMS_REDACT.PARTIAL,
      function_parameters => '9,1,7',
      expression => '1=1'
      );

    DBMS_REDACT.ALTER_POLICY(
      object_schema => 'MOVIEDEMO',
      object_name => 'CUSTOMER',
      action => DBMS_REDACT.ADD_COLUMN,
      column_name => 'LAST_NAME',
      policy_name => 'customer_redaction',
      function_type => DBMS_REDACT.PARTIAL,
      function_parameters => 'VVVVVVVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVVVVVVV,*,3,25',
      expression => '1=1'
      );

    最初のPL/SQLコールによって、customer_redactionというポリシーが作成されます:

    • このポリシーは、moviedemo.customer表のcust_id列に適用されます  
    • これで、部分的なリダクションが実行されます。つまり、必ずしもフィールド内のすべての文字に適用されるわけではありません
    • 最初の7文字が数字の"9"に置き換えられます
    • リダクション・ポリシーは常に適用されます。いつ適用されるかを示す式が、"1=1"と指定されているためです

    2番目のAPIコールによってcustomer_redactionポリシーが更新され、同じ表の2番目の列がリダクションされます。これで、LAST_NAME列の3～25の文字が'*'に置き換えられます。注：リダクション・ポリシーを適用しても、基盤となるデータは変更されません。Oracle Databaseでは実行時、すなわちデータがアプリケーション・ユーザーに表示される直前にリダクションが実行されます。

    顧客表のこれら2つの列を問い合わせた結果は、次のようになります:

    SELECT cust_id, last_name FROM customer;

    redact customer

    重要なのは、リダクションしたデータに対して実行されたSQLは、変更されないままであることです。たとえば、問合せではcust_id列とlast_name列を結合条件で使用して、これらの列にフィルタを適用できます。データがリダクションされているという事実は、アプリケーション・コードに対して透過的です。

    次の項では、Hadoopからのデータが含まれる表にリダクション・ポリシーを適用します。

    HadoopとOracle NoSQL Databaseに保存されているデータに対するリダクション・ポリシーの適用

      ここでは、Oracle Big Data SQLの2つの表に対して、次の処理を実行する同等のリダクション・ポリシーを適用します:

      • 最初のプロシージャで、HDFS内のJSONからのデータがリダクションされる
      • 2番目のプロシージャで、HiveからのAvroデータがリダクションされる
      • 3番目のプロシージャで、Oracle NoSQL Databaseからのデータがリダクションされる
      • 両方のポリシーによって、custid属性がリダクションされる

      SQL Developerワークシートに移動して、次の2つのPL/SQL DBMS_REDACT.ADD_POLICYプロシージャを実行します:

      BEGIN
        -- JSON file in HDFS
        DBMS_REDACT.ADD_POLICY(
          object_schema => 'MOVIEDEMO',
          object_name => 'MOVIELOG_V',
          column_name => 'CUSTID',
          policy_name => 'movielog_v_redaction',
          function_type => DBMS_REDACT.PARTIAL,
          function_parameters => '9,1,7',
          expression => '1=1'
        );

        -- Avro data from Hive
        DBMS_REDACT.ADD_POLICY(
          object_schema => 'MOVIEDEMO',
          object_name => 'MYLOGDATA',
          column_name => 'CUSTID',
          policy_name => 'mylogdata_redaction',
          function_type => DBMS_REDACT.PARTIAL,
          function_parameters => '9,1,7',
          expression => '1=1'
        );

        -- Recommendations data from Oracle NoSQL Database
        DBMS_REDACT.ADD_POLICY(
          object_schema => 'MOVIEDEMO',
          object_name => 'RECOMMENDATION',
          column_name => 'CUSTID',
          policy_name => 'recommendation_redaction',
          function_type => DBMS_REDACT.PARTIAL,
          function_parameters => '9,1,7',
          expression => '1=1'
        );

      END;
      /

      結果：前述のように、これら3つのオブジェクトのcustid列がリダクションされています。

      Avroソースからのリダクションされたデータを確認します:

      SELECT * FROM mylogdata WHERE rownum < 20;

      結果：出力は次のようになります:

      query redacted avro

      custid列に、元の値の代わりに9が連続表示されていることが分かります。

      次のSELECT文を実行して、リダクションされたHDFSデータとcustomer表を結合します:

      SELECT f.custid, c.last_name, f.movieid, f.time
      FROM customer c, movielog_v f
      WHERE c.cust_id = f.custid;

      結果：問合せの出力は次のようになります:

      join redacted data

      注:

      • 上記の例で強調表示されているとおり、TIME列でSortツールを使用して、TIMEの昇順で出力を並べ替えました
      • このように、Hadoopからのリダクションされたデータが、Oracle Databaseの残りのデータとシームレスに連携します

      同様に、次のSELECT文を実行して、リダクションされたNoSQLデータとcustomerおよびmovieというOracle Database表を結合します:

      SELECT f.custid, c.last_name, c.income_level, f.genreid, m.title
      FROM customer c, recommendation f, movie m
      WHERE c.cust_id = f.custid
      AND f.movieid = m.movie_id
      AND c.income_level like 'F%'
      ORDER BY f.custid, f.genreid;

      結果：問合せ結果には、より裕福な顧客向けのお勧めが表示されます:

      join redacted data

      注:

      • これで、機密性の高い顧客IDを保護しながら、顧客向けのお勧めのムービーを簡単に確認できるようになりました。

Part 5 - 全データに対するOracle Analytic SQLの使用

    Oracle Big Data SQLを使用すると、データがどこにあっても、オラクルの機能豊富なSQL言語を利用してすべてのデータに問合せができます。ここでは、3つのデータソースから独自の視点で情報を取得する分析問合せをいくつか紹介します。

    全データからの情報の取得

      次に示すのは、Oracle MoviePlexが把握している顧客情報を、RFM分析を利用して強化する例です。この問合せで次の項目が明確になります:

      • Recency : 顧客が最後にサイトにアクセスした日時
      • Frequency :サイト上での顧客のアクティビティ・レベル
      • Monetary : 顧客が消費した金額

      これらの項目を明確にするために、Hadoop上のアプリケーション・ログとOracle Database表にある売上データの両方に存在するデータにSQL分析関数を適用します。顧客は重要度に応じて5つのバケットに分類されます。たとえば、RFMを組み合わせた551というスコアは、最近のアクセス数（R=5）とサイト上でのアクティビティ（F=5）に関しては最高レベルの顧客であるが、消費額（M=1）の面では最低レベルの顧客であることを示します。これに該当するのは、このサイトで調査をしておきながら他所で映画を購入することにした顧客でしょう。

      ターゲットにすべきなのは、競合相手に奪われそうな顧客です。そこで、次の問合せを実行して、最近サイトにアクセスしていない重要な顧客（Recencyのスコアが低くMonetaryのスコアが高い顧客）を検索します:

      SQL Developerのワークシートを開き、次の問合せを実行します:

        WITH customer_sales AS (
        -- Sales and customer attributes
        SELECT m.cust_id,
               c.last_name,
               c.first_name,
               c.country,
               c.gender,
               c.age,
               c.income_level,
               NTILE (5) over (order by sum(sales)) AS rfm_monetary
        FROM movie_sales m, customer c
        WHERE c.cust_id = m.cust_id
        GROUP BY m.cust_id,        
               c.last_name,
               c.first_name,
               c.country,
               c.gender,
               c.age,
               c.income_level
      ),
      click_data AS (
        -- clicks from application log
        SELECT custid,
             NTILE (5) over (order by max(time)) AS rfm_recency,
             NTILE (5) over (order by count(1))    AS rfm_frequency
        FROM movielog_v
        GROUP BY custid
      )
      SELECT c.cust_id,
          c.last_name,
          c.first_name,
          cd.rfm_recency,
          cd.rfm_frequency,
          c.rfm_monetary,
          cd.rfm_recency*100 + cd.rfm_frequency*10 + c.rfm_monetary AS rfm_combined,
          c.country,
          c.gender,
          c.age,
          c.income_level
        FROM customer_sales c, click_data cd
        WHERE c.cust_id = cd.custid
        AND c.rfm_monetary >= 4
        AND cd.rfm_recency <= 2
        ORDER BY c.rfm_monetary desc, cd.rfm_recency desc;

      注:

      • customer_sales副問合せでは、売上に基づいて顧客を分類するためのデータをOracle Databaseのファクト表movie_salesから選択しています
      • click_data副問合せでは、アプリケーション・ログに保存されているWebサイト・アクティビティについて、同様のタスク（アクティビティと最近のアクセスに基づく顧客の分類）を実行しています
      • 続いて、これら2つの副問合せを結合して、完全なRFMスコアを生成しています。結果には、消費額が多い（>= 4）のに最近サイトにアクセスしていない（<= 2）顧客のみが表示されます

      結果：問合せの出力は次のようになります:

      rfm query

      これらは、Oracle MoviePlexが失いかけている顧客です。これらの顧客は、かつては頻繁にサイトにアクセスして多額の消費をしていました。では、これらの顧客を取り戻すためにできることを検討してみましょう。

      リコメンデーション・エンジンはどのように動作するのでしょうか。この疑問を解決するには、次のことを理解する必要があります:

      • 映画がリコメンドされた回数のランク（Oracle NoSQL Databaseから）
      • 映画の販売収益のランク（Oracle Database表から）
      • 映画のインタレスト・レベル（予告編の再生回数、視聴回数、詳細情報の表示回数など）のランク（HDFSのクリック・データから）

      WITH rank_recs AS (
        -- recommendation rank from NoSQL Database
        SELECT movieid,
               RANK () OVER (ORDER BY COUNT(movieid) DESC) AS rec_rank
        FROM recommendation
        GROUP BY movieid),
      rank_sales AS (
        -- sales rank from Oracle Database
        SELECT m.movie_id,
               m.title,
               RANK () OVER (ORDER BY SUM(ms.sales) DESC) as sales_rank
        FROM movie m, movie_sales ms
        WHERE  ms.movie_id = m.movie_id
        GROUP BY m.title, m.movie_id
      ),
      rank_interest AS (
        -- "interest" rank from hdfs logs
        SELECT movieid,
        RANK () OVER (ORDER BY COUNT(movieid) DESC) AS click_rank
        FROM movielog_v
        WHERE activity IN (1,4,5) -- rated, started or browsed the movie
        GROUP BY movieid
      )
      -- combine the results
      SELECT rs.title,
             sales_rank,
             rec_rank,
             click_rank
      FROM rank_recs rr, rank_sales rs, rank_interest ri
      WHERE rr.movieid = rs.movie_id
        AND ri.movieid = rs.movie_id
      ORDER BY rec_rank asc;

      結果：問合せの出力は次のようになります:

      recommendation effectiveness

      注:

      • 3つのデータソースすべての結果を結合することで、顧客アクティビティの全容を把握できます。

Part 6 - SQLパターン・マッチングの概要

    この項では、Oracle Database 12cに含まれる新機能のSQLパターン・マッチングと分析SQLについて説明します。ネイティブSQLの行パターン・マッチングを使用すると、アプリケーションの開発がしやすくなって開発者の生産性が向上し、行シーケンス分析問合せの効率も向上します。この新機能がSQLツールボックスに追加されたことには大きな意味があります

    概要

    行シーケンスに含まれるパターンを認識する機能を求める声が多数上がっていましたが、これまでSQLでは実現できませんでした。解決方法は多数ありましたが、記述するのが困難で、理解しにくく、効率的に実行できませんでした。Oracle Database 12cの場合は、MATCH_RECOGNIZE句を使用してSQLでパターン・マッチングを実行し、次のことを実行できます:

    1. PARTITION BY句およびORDER BY句を使用して、MATCH_RECOGNIZE句で使用されているデータを論理的にグループ化して順序付けする
    2. PATTERN句を使用して、ビジネス・ルール/パターンを定義する。パターンには、強力な表現機能である正規表現構文を使用し、パターン変数を適用します
    3. 行を行パターン変数にマップするのに必要な論理条件をDEFINE句を使用して指定する。
    4. 出力メジャー（MEASURES句に含まれる式）を定義する
    5. パターン・マッチング・プロセスからの出力（サマリー対ディテール）を制御する

    moviedemoスキーマには、Webアプリケーション・ログ・ファイルから取得したJSONのクリック・データ・ストリームの書式化済みバージョンを返すMOVIEAPP_LOG_JSON_Vという名前のビューが含まれます。このビューは次の列を返します:
    CUST_ID
    MOVIE_ID
    GENRE_ID
    TIME_ID
    RECOMMENDED
    RATED
    COMPLETED
    PAUSE
    START
    BROWSE
    LIST
    SEARCH
    LOGIN
    LOGOUT
    INCOMPLETE
    PURCHASE
    PRICE
    RATING

    このクリック・データを使用して、各セッション、セッションの継続時間、クリック数/イベント数を追跡するセッション化データセットを作成します。

    パターン・マッチングのタスクおよびキーワード

      パターン・マッチングで使用するタスクとキーワードの一部を紹介します。パターン・マッチング文を作成するプロセスは、4つの単純な手順に分解できます:

      タスク キーワード 説明
      1.データを体系化する PARTITION BY

      ORDER BY      		 行を論理的にグループに分割/パーティション化する

      パーティション内で行を論理的に順序付ける
      2. ビジネス・ルールを定義する PATTERN



      DEFINE

      AFTER MATCH      		 照合する必要があるパターン変数、照合する必要があるシーケンス、および照合する必要がある行数を定義する

      パターン変数を定義する条件を指定する

      一致が見つかった後にマッチング・プロセスを再開する場所を決定する
      3. 出力メジャーの定義 MEASURES

      MATCH_NUMBER

      CLASSIFIER      		 行パターン・メジャー列を定義する

      パターン変数を適用する行を見つける

      特定の行に適用するパターンの要素を特定する
      4. 出力の制御 ONE ROW PER MATCH

      ALL ROWS PER MATCH      		 各一致の出力のサマリー行を返す

      各一致の行ごとにディテール行を1つ返す

    パターン・マッチングの例：Webログのセッション化の分析

      パターン/ビジネス・ルールの定義

      この使用例では、イベント（クリック）とイベント（クリック）の間の時差が2時間未満であれば、Webログ・ファイルに含まれる一連のイベントまたはクリックを同じセッションのものであると想定します（通常、映画を観ているときはクリック・アクティビティが記録されないため、このしきい値が低過ぎると単一セッションが複数セッションに分割されてしまう恐れがあります）。セッションの正確な定義はビジネス・ユーザーが決める必要があります。当然ながら、ビジネス固有の要件に合わせて、セッションのしきい値をたとえば2分など比較的容易に変更できるSQLパターン・マッチングを使用する必要があります。

      この情報を使用してPATTERN句およびDEFINE句を作成します。

        PATTERN (bgn sess+)
        DEFINE
                sess as time_id <= PREV(sess.time_id) + interval '2' hour

      ＋記号（+）は、パターンのインスタンスを1つ以上探すという意味です。つまり、各イベントは、要素sess.time_idで取得されるPREVIOUSイベントの2時間の境界内に収まっている必要があります。この情報を取得するために、多数ある組込み関数の1つである、データセットの処理時にデータセット内の特定の値をポイントできる関数を使用します（この関数については後ほど詳しく説明します）。

      使用できる正規表現は他にも多数ありますが、それらについてはデータウェアハウス・ガイドを参照してください。

      組込み関数の使用

      MATCH_RECOGNIZE機能はいくつかの非常に便利な組込み関数に付属しており、自作のコードに埋め込むことができます:

      MATCH_NUMBER:所定の行パーティション内にパターンの一致が大量にある場合があります。どうすれば、すべての一致を識別できるでしょうか。これには、MATCH_NUMBER関数を使用します。行パターン・パーティション内の一致には、見つかった順に、1から始まる番号がふられます。なお、行パターン・パーティション同士にはもともと順序がないため、一致番号は行パターン・パーティションごとに1からふり直されます。

      CLASSIFIER:調べているMATCH_NUMBERを知るだけでなく、パターンのどのコンポーネントが特定の行に適用されているかを知ることが必要な場合もあります。これには、CLASSIFIER関数を使用します。行の分類子は、行パターン・マッチングで行がマップされるパターン変数です。この関数から返されるのは、行の分類子を値として持つ文字列です。行パターン・マッチングでマップされない行の分類子はnullです。

      一意のセッションに属するレコードのグループを識別したら、結果セットに含まれる一意のセッションを1つ1つ識別する方法が必要です。これを実行するには、組込みメジャーであるMATCH_NUMBER()を使用して、一意のセッションそれぞれに順序番号を適用します。同時にCLASSIFIER()関数を使用して、各行に一致したパターン変数を表示します。この情報を使用して、MEASURE句を作成できます:

        MEASURES MATCH_NUMBER() session_id,
                 CLASSIFIER() AS pattern_id,

      ディテール・レポートにするかサマリー・レポートにするかここまで来たら、前述の情報をすべてまとめてパターン・マッチング文を作成できます。この単純なSELECT文は、ソース・ビューに含まれるすべての列と、MATCH_RECOGNIZE句で取得したセッションidを返します。この例の一環として、特定の顧客（1000693）の行のみが出力されるように、最後にもう1つWHERE句を使用しています

      セッション化データセットを作成するこの1つ目の手順では、ALL ROWS PER MATCH構文を使用してディテール・レポートを返します。

      単純なセッション化の結果セットを返す

      ここまで来たら、前述の情報をすべてまとめてパターン・マッチング文を作成できます。この単純なSELECT文は、ソース・ビューに含まれるすべての列と、MATCH_RECOGNIZE句で取得したセッションidを返します。この例の一環として、特定の顧客（1000693）の行のみが出力されるように、最後にもう1つWHERE句を使用しています:

       SELECT *
       FROM movieapp_log_json_v
       MATCH_RECOGNIZE
        (PARTITION BY cust_id ORDER BY time_id
         MEASURES MATCH_NUMBER() AS session_id
         ALL ROWS PER MATCH
         PATTERN (bgn sess+)
         DEFINE
               sess as time_id <= PREV(sess.time_id) + interval '2' hour
       )
       WHERE cust_id ='1000693';

      このMATCH_RECOGNIZE文の出力は次のようになります:

      自動計算で求めたセッションidは列3に表示されています。これで、元のWebログ・ファイルが基本的なセッション化データセットに変換されました。なお、このデータセットはビジネス・ユーザーと共有することもできます。ただし、共有する前に、このパターン・マッチング・プロセスが正しく行われていることを確認するための作業をもう少し行うことにします。

Part 7 - パターン・マッチング・プロセスの確認

    この項では、CLASSIFIER()メジャーを使用して、各行に割り当てられるパターン変数を表示します。こうすると、パターン・マッチング・プロセスをデバッグして動作が正しいかどうか確認できます。

    CLASSIFIERメジャーの追加

    MEASURE句を拡張して組込み関数CLASSIFIER()を含める必要があります。

       MEASURES MATCH_NUMBER() AS session_id,
                CLASSIFIER() AS pattern_id

    特定の列の選択

    ここでは、SELECT句を修正して、顧客id、セッションid（MATCH_NUMBER()関数から）、日付、時間、およびパターンid（CLASSIFIER()関数から）のみが返されるようにします。新しいコードは次のようになります:

     SELECT
      cust_id,
      session_id,
      time_id,
      TO_CHAR(time_id, 'hh24:mi:ss') AS session_time,
      pattern_id
     FROM movieapp_log_json_v
     MATCH_RECOGNIZE
      (PARTITION BY cust_id ORDER BY time_id
       MEASURES MATCH_NUMBER() AS session_id,
                CLASSIFIER() AS pattern_id
       ALL ROWS PER MATCH
       PATTERN (bgn sess+)
       DEFINE
             sess as time_id <= PREV(sess.time_id) + interval '2' hour
     )
     WHERE cust_id ='1000693';

    このMATCH_RECOGNIZE文の出力は次のようになります:

    新しいセッションはそれぞれBGNパターンで始まり、それに続く他のすべてのクリックは前のSESS.timeイベントから2時間以内に発生していることが分かります。また、パターンとしてSESSがマークされていることを確認できます。これで、ビジネス・ユーザー向けのデータセットはかなり改善されましたが、データセットにはまだ改良の余地があります。

Part 8 - さらに有用なデータセットの作成

    パターン・マッチング・プロセスが正しく動作していることを、CLASSIFIER()関数を使用して確認しました。次にすべきことは、データセットを要約して1セッション1行にすることです。これには、データの出力方法を変更することで対処できます。データセットの一部として、いくつかのビジネス・メトリックを追加で含めても良いでしょう。次の項では、その実行方法を説明します。

    サマリー・レポートの作成

      ONE ROW PER MATCHの使用

      出力句をALL ROWS PER MATCHからONE ROW PER MATCHに変更する必要があります。

      メジャー句の更新

      これからサマリー・レポートを作成するため、MEASURE句からCLASSIFIER()関数を削除する必要があります。

      特定の列の選択

      次に、SELECT句を修正し、顧客idとセッションidのみが（MATCH_NUMBER()関数から）このサマリー・レポートに返されるようにします。新しいコードは次のようになります:

       SELECT
        cust_id,
        session_id
       FROM movieapp_log_json_v
       MATCH_RECOGNIZE
        (PARTITION BY cust_id ORDER BY time_id
         MEASURES MATCH_NUMBER() AS session_id
         ONE ROW PER MATCH
         PATTERN (bgn sess+)
         DEFINE
               sess as time_id <= PREV(sess.time_id) + interval '2' hour
       )
       WHERE cust_id ='1000693';

      このMATCH_RECOGNIZE文の出力は次のようになります:

      レポートには31行が出力されています。これで、セッション1つにつき1つの行がレポートに表示されるようになりました。ただし、この情報はビジネス・ユーザーにとっては特に有用とは言えません。メジャー句を拡張することで、さらに有用な情報をこのレポートに追加できます。

    ビジネス価値の付加

      セッション中のクリック数の算出

      セッション中に実行されたクリックの回数を返すには、MEASURE句の中でCOUNT()関数を使用します。

         MEASURES MATCH_NUMBER() AS session_id,
                  COUNT(*) AS no_of_events

      セッションの開始時刻と終了時刻の検索

      セッションの開始時刻と終了時刻を検索するには、MATCH_REOGNIZEの独自機能の1つである、関連するパターン式を参照して列内の特定の値をポイントする機能を使用します。MATCH_RECOGNIZEには、特定の列からデータ・ポイントを抽出するのに役立つ関数がいくつか追加で含まれています。追加された新しい関数は次のとおりです:

      • FIRST
      • LAST
      • NEXT
      • PREVIOUS

      メジャー句の更新

      これらの新しい関数を使用してメジャー句を拡張し、各セッションの開始時刻と終了時刻を返します。

         MEASURES MATCH_NUMBER() AS session_id,
                  COUNT(*) AS no_of_events,
                  TO_CHAR(FIRST(bgn.time_id),'hh24:mi:ss') AS start_time,
                  TO_CHAR(LAST(sess.time_id),'hh24:mi:ss') AS end_time

      セッションの継続時間の算出

      セッションの継続時間を算出するには、各セッションの終了時刻から各セッションの開始時刻を引き去ります。この計算の実行には、データベースに組み込まれている通常の日時機能を使用できます。

         MEASURES MATCH_NUMBER() AS session_id,
                  COUNT(*) AS no_of_events,
                  TO_CHAR(FIRST(bgn.time_id),'hh24:mi:ss') AS start_time,
                  TO_CHAR(LAST(sess.time_id),'hh24:mi:ss') AS end_time,
                  TO_CHAR(to_date('00:00:00','HH24:MI:SS') + (LAST(sess.time_id)-FIRST(bgn.time_id)),'hh24:mi:ss') AS mins_duration

      サマリー・レポートを生成するSQL

      リダクション後のSQL文は次のようになります:

       SELECT
        cust_id,
        session_id,
        no_of_events,
        start_time,
        end_time,
        mins_duration
       FROM movieapp_log_json_v
       MATCH_RECOGNIZE
        (PARTITION BY cust_id ORDER BY time_id
         MEASURES MATCH_NUMBER() AS session_id,
                  COUNT(*) AS no_of_events,
                  TO_CHAR(FIRST(bgn.time_id),'hh24:mi:ss') AS start_time,
                  TO_CHAR(LAST(sess.time_id),'hh24:mi:ss') AS end_time,
                  TO_CHAR(to_date('00:00:00','HH24:MI:SS') + (LAST(sess.time_id)-FIRST(bgn.time_id)),'hh24:mi:ss') AS mins_duration
         ONE ROW PER MATCH
         PATTERN (bgn sess+)
         DEFINE
               sess as time_id <= PREV(sess.time_id) + interval '2' hour
       )
       WHERE cust_id ='1000693';

      このMATCH_RECOGNIZE文の出力は次のようになります:

      これで、新しいサマリー・レポートにはセッションごとに1行が表示されるようになりました。また、メジャー句を拡張して各セッションの開始時刻、終了時刻および継続時間を表示するようにしたことで、ビジネス・ユーザーに有用な情報が増加しました。ビジネス・ユーザーは、この豊富な情報を基に分析を開始できるようになりました。

Part 9 - 12cのAnalytical SQLのその他の便利な機能

    以降のコードの解読を容易にするために、先ほど作成したMATCH_RECOGNIZE句はmovieapp_analytics_vという名前のビューにラップしてあります。もちろん、前述したMATCH_RECOGNIZE文に以降の追加機能を組み込むこともできます。

    個別顧客数の算出

      クリック・データをメトリックとして活用する方法には、サイトを使用している個別顧客の月別人数を数える方法があります。通常は、COUNT(DISTINCT 式)関数を使用します。ところが、この関数で大規模なデータセット全体を検索して個別値の正確な数を返すには、大量のリソースが必要です。

      そこでオラクルは、Oracle Database 12cのリリースに伴い、この種の分析をはるかに高速に実行できる方法を用意しました。APPROX_COUNT_DISTINCTを使用すると、列に含まれる個別値の推定数をかなり正確に求めることができます。この新しい関数は、COUNT(DISTINCT 式)関数よりはるかに高速に大量のデータを処理でき、得られる結果は正確な数値とほとんど変わりません。この新機能について詳しくは、SQL言語リファレンスを参照してください。

      では、この2つの関数を使用して、月ごとの一意のセッション数を算出しましょう:

      Using APPROX_COUNT_DISTINCT

      新しい関数をSELECT文に追加し、比較のためにCOUNT(DISTINCT...)関数も含めます。:

       SELECT
        time_id,
        SUM(no_of_events) AS tot_events,
        COUNT(DISTINCT cust_id) AS unique_customers,
        APPROX_COUNT_DISTINCT(cust_id) AS est_unique_customers
       FROM movieapp_analytics_v
       GROUP BY time_id
       ORDER BY 1;

      この文の出力は次のようになります:

      レポートには、月ごとの個別顧客数と月ごとの個別顧客の概算数が表示されています。通常はこの種のサマリー・レポートを基準にしてさらに分析を実行します。つまり、算出された数値が著しく高かったり低かったりすると、さらなる分析が必要になる場合があります。したがって、新しいAPPROX_COUNT_DISTINCT関数を使用すれば、ビジネス・ユーザーは正確さをあまり犠牲にせずに、はるかに短時間で結果を取得できることになります。

    上位1%の顧客の検索

      クリック・データをメトリックとして活用するもう1つの方法は、最優良顧客と最劣悪顧客を見つけ出すことです。新しいTop-N構文を使用すると、合計セッション数と記録されたクリック数に基づいて上位1%の顧客を非常に短時間で検索できます。

      TOP-Nの新しい構文は次のとおりですが、この構文は12cからサポートされるようになりました:

            OFFSET [ROW | ROWS]
            FETCH [FIRST | NEXT]
            [ | PERCENT] [ROW | ROWS]
            [ONLY | WITH TIES]

      この新しい関数は、COUNT(DISTINCT 式)関数よりはるかに高速に大量のデータを処理でき、得られる結果は正確な数値とほとんど変わりません。この新しい機能について詳しくは、SQL言語リファレンスを参照してください。

      上位N%を求めるSQLコード

      新しいFETCH構文を使用すると、上位1%の顧客を簡単に検索できます:

       SELECT
        cust_id,
        MAX(session_id) AS no_of_sessions,
        SUM(no_of_events) AS tot_clicks_session,
        TRUNC(AVG(no_of_events)) AS avg_clicks_session,
        MIN(no_of_events) AS min_clicks_session
        MAX(no_of_events) AS max_clicks_session
       FROM movieapp_analytics_v
       GROUP BY cust_id
       ORDER BY 2 DESC, 3 DESC
       FETCH FIRST 1 PERCENT ROWS ONLY;

      この新機能について詳しくは、SQL言語リファレンスを参照してください。

      この文の出力は次のようになります:

      このレポートには、セッション中に記録されたセッション数とクリック数に基づく上位1%の顧客が表示されており、23行が含まれています。

    下位1%の顧客の検索

      下位N%を求めるSQLコード

      下位1%の顧客は、ソート順を逆にするだけで検索できます。

       SELECT
        cust_id,
        MAX(session_id) AS no_of_sessions,
        SUM(no_of_events) AS tot_clicks_session,
        TRUNC(AVG(no_of_events)) AS avg_clicks_session,
        MIN(no_of_events) AS min_clicks_session,
        MAX(no_of_events) AS max_clicks_session
       FROM movieapp_analytics_v
       GROUP BY cust_id
       ORDER BY 2 ASC, 3 ASC
       FETCH FIRST 1 PERCENT ROWS ONLY;

まとめ

    このチュートリアルで学習した内容は、次のとおりです:

    • Oracle ExadataでのOracle Big Data SQLの構成 
    • HDFS、Oracle NoSQL DatabaseおよびHiveに存在するデータにアクセスするOracle外部表の作成
    • Oracle DatabaseとHadoopの両方に存在するデータ全体へのOracleセキュリティ・ポリシーの適用
    • Oracleの機能豊富なSQL言語によるデータ・プラットフォーム全体のデータ分析
    • Oracleの新しい機能豊富なSQLパターン・マッチング機能によるWebログ・データ分析
    • SQLでのMATCH_RECOGNIZE句を使用したパターン・マッチングの実行
    • パターン・マッチングに使用されるおもなキーワードの使用
    • Webログ・ファイルのデータからのセッション化分析の作成
    • 既存の問合せのビジネス・ルールのすばやい変更
    • 算出済みメジャーの追加によるビジネス価値の向上
    • 新しいAPPROX_COUNT_DISTINCT関数を使用して処理を高速化することによる個別値の数の算出時間の短縮
    • 新しいFETCH機能による上位値/下位値の高速検索

    参考資料

    Oracle Big Data SQL、Oracle Data WarehousingおよびOracle Analytical SQLについて詳しくは、Oracle Technology Networkを参照してください。

    パターン・マッチングについて詳しくは、『Oracle Database 12cデータ・ウェアハウス・ガイド』のパターン一致に関する章を参照してください。他にも、この章では次のような詳しい例が紹介されています。:

    • 株式市場の例: 株価やパターンが関係する一般的なタスクに基づく例
    • セキュリティ・ログ分析の例: エラー・メッセージの発行と認証チェックを行い、イベントをシステム・ファイルに格納するコンピュータ・システムを対象にした例
    • セッション化の例: 単一セッション中に複数のイベントが含まれることが一般的な、ユーザー・アクティビティの分析。パターン・マッチングを使用すると、セッション化を行う問合せを容易に表現できます

    クレジット

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.