ステップ4。データを更新する¶
このチュートリアルでは、これまで次を完了しました。
サンプルの JSON イベントデータをS3バケットから
RAW_SOURCEテーブルにコピーし、簡単なクエリを調べました。また、 FLATTEN 関数を調べて、 JSON データをフラット化し、データのリレーショナル表現を取得しました。たとえば、イベントキーを抽出し、それらのキーを別の EVENTS テーブルにある個別の列に格納したとします。
最初に、チュートリアルでは、複数のソースがイベントを生成し、ウェブエンドポイントがそれをS3バケットに保存するアプリケーションシナリオについて説明します。新しいイベントがS3バケットに追加されると、スクリプトを使用して新しいデータを RAW_SOURCE テーブルに継続的にコピーできます。しかし、新しいイベントデータのみを EVENTS テーブルに挿入するにはどうすればよいでしょうか。
データの一貫性を維持するには、さまざまな方法があります。このセクションでは、2つのオプションについて説明します。
比較に主キー列を使用する¶
このセクションでは、主キーを EVENTS テーブルに追加します。主キーによって一意性が保証されます。
自然に一意であり、主キーの適切な候補となる値について、 JSON データを調べます。たとえば、
src:device_typeとvalue:rvの組み合わせが主キーになるとします。これらの2つの JSON キーは、EVENTSテーブルのDEVICE_TYPE列とRV列に対応しています。注釈
Snowflakeは主キー制約を強制しません。むしろ、制約はInformation Schema内の自然キーを識別するメタデータとして機能します。
主キー制約を
EVENTSテーブルに追加します。ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);
新しい 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 }');
主キー値の比較に基づいて、
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 テーブルを変更する必要はありません。
新しい 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 }');
すべての繰り返しキー値の比較に基づいて、
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 | +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+