ステップ4。データを更新する

これまで、このチュートリアルでは、 JSON イベントデータのバッチを RAW_SOURCE テーブルにロードしてクエリし、イベントキー値で構成される EVENTS テーブルを作成しました。このチュートリアルで前述したシナリオでは、 JSON データは複数のソースからウェブエンドポイントを介してS3バケットに流れます。 RAW_SOURCE テーブルに新しいデータファイルをプルするスクリプトを作成できます。次に、新しいイベントデータのみを EVENTS テーブルに挿入するにはどうすればよいでしょうか。

RAW_SOURCE テーブルの JSON データと EVENTS テーブルのフラット化されたデータを区別する方法は多数あります。このセクションでは、2つのオプションを提案します。

主キーデータの比較

  1. 自然に一意であり、主キーの適切な候補となる値について、 JSON データを調べます。現在の例では、 src:device_typevalue:rv の組み合わせが適切な複合主キーになると仮定します。これらの2つの JSON キーは、 EVENTS テーブルの DEVICE_TYPE 列と RV 列に対応しています。

    注釈

    Snowflakeは主キー制約を強制しません。むしろ、制約は情報スキーマ内の自然キーを識別するメタデータとして機能します。

  2. 主キー制約を EVENTS テーブルに追加します。

    ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);
    
  3. 新しい JSON イベントレコードを RAW_SOURCE テーブルに挿入します。

    insert into raw_source
      select
      PARSE_JSON ('{
        "device_type": "cell_phone",
        "events": [
          {
            "f": 79,
            "rv": "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22",
            "t": 5769784730576,
            "v": {
              "ACHZ": 75846,
              "ACV": 098355,
              "DCA": 789,
              "DCV": 62287,
              "ENJR": 2234,
              "ERRS": 578,
              "MXEC": 999,
              "TMPI": 9
            },
            "vd": 54,
            "z": 1437644222811
          }
        ],
        "version": 3.2
      }');
    
  4. 主キー値の比較に基づいて、 RAW_SOURCE テーブルの新しいレコードを EVENTS テーブルに挿入します。

    insert into events
    select
          src:device_type::string
        , src:version::string
        , value:f::number
        , value:rv::variant
        , value:t::number
        , value:v.ACHZ::number
        , value:v.ACV::number
        , value:v.DCA::number
        , value:v.DCV::number
        , value:v.ENJR::number
        , value:v.ERRS::number
        , value:v.MXEC::number
        , value:v.TMPI::number
        , value:vd::number
        , value:z::number
        from
          raw_source
        , lateral flatten( input => src:events )
        where not exists
        (select 'x'
         from events
         where events.device_type = src:device_type
         and events.rv = value:rv);
    

    EVENTS テーブルをクエリすると、追加された行が表示されます。

    select * from EVENTS;
    
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
    | DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |  ACHZ |    ACV | DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
    |-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------|
    | server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 | 42869 | 709489 | 232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
    | server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |  6953 | 346795 | 250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
    | cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 | 75846 |  98355 | 789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
    

すべてのキー/列の比較

JSON データに自然な主キー候補であるキー値が含まれていない場合、 RAW_SOURCE テーブルのすべての繰り返し JSON キーを EVENTS テーブルの対応する列値と比較できます。

既存の EVENTS テーブルを変更する必要はありません。

  1. 新しい JSON イベントレコードを RAW_SOURCE テーブルに挿入します。

    insert into raw_source
      select
      parse_json ('{
        "device_type": "web_browser",
        "events": [
          {
            "f": 79,
            "rv": "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43",
            "t": 5769784730576,
            "v": {
              "ACHZ": 768436,
              "ACV": 9475,
              "DCA": 94835,
              "DCV": 88845,
              "ENJR": 8754,
              "ERRS": 567,
              "MXEC": 823,
              "TMPI": 0
            },
            "vd": 55,
            "z": 8745598047355
          }
        ],
        "version": 8.7
      }');
    
  2. すべての繰り返しキー値の比較に基づいて、 RAW_SOURCE テーブルの新しいレコードを EVENTS テーブルに挿入します。

    insert into events
    select
          src:device_type::string
        , src:version::string
        , value:f::number
        , value:rv::variant
        , value:t::number
        , value:v.ACHZ::number
        , value:v.ACV::number
        , value:v.DCA::number
        , value:v.DCV::number
        , value:v.ENJR::number
        , value:v.ERRS::number
        , value:v.MXEC::number
        , value:v.TMPI::number
        , value:vd::number
        , value:z::number
        from
          raw_source
        , lateral flatten( input => src:events )
        where not exists
        (select 'x'
         from events
         where events.device_type = src:device_type
         and events.version = src:version
         and events.f = value:f
         and events.rv = value:rv
         and events.t = value:t
         and events.achz = value:v.ACHZ
         and events.acv = value:v.ACV
         and events.dca = value:v.DCA
         and events.dcv = value:v.DCV
         and events.enjr = value:v.ENJR
         and events.errs = value:v.ERRS
         and events.mxec = value:v.MXEC
         and events.tmpi = value:v.TMPI
         and events.vd = value:vd
         and events.z = value:z);
    

    EVENTS テーブルをクエリすると、追加された行が表示されます。

    select * from EVENTS;
    
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
    | DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |   ACHZ |    ACV |   DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
    |-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------|
    | server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 |  42869 | 709489 |   232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
    | server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |   6953 | 346795 |   250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
    | cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 |  75846 |  98355 |   789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
    | web_browser | 8.7     |      79 | "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43"              | 5769784730576 | 768436 |   9475 | 94835 | 88845 | 8754 |  567 |  823 |    0 |  55 | 8745598047355 |
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
    

次: ステップ5。おめでとうございます。