Etapa 4. Atualizar dados¶

Até agora, neste tutorial, você fez o seguinte:

Copiou dados de evento JSON de amostra de um bucket S3 para a tabela RAW_SOURCE e explorou consultas simples.
Você também explorou a função FLATTEN para nivelar os dados JSON e obter uma representação relacional dos dados. Por exemplo, você extraiu as chaves de eventos e armazenou as chaves em colunas separadas em outra tabela EVENTS.

No início, o tutorial explica o cenário de aplicativo onde múltiplas fontes geram eventos e um ponto de extremidade da web o salva em seu bucket S3. Conforme novos eventos são adicionados ao bucket S3, você pode usar um script para copiar continuamente novos dados na tabela RAW_SOURCE. Mas como inserir somente novos dados de eventos na tabela EVENTS ?

Há inúmeras maneiras de manter a consistência dos dados. Esta seção explica duas opções.

Uso de colunas de chave primária para comparação¶

Nesta seção você adiciona uma chave primária à tabela EVENTS. A chave primária garante a exclusividade.

Examine seus dados JSON em busca de quaisquer valores que sejam naturalmente únicos e que seriam bons candidatos a uma chave primária. Por exemplo, suponha que a combinação de src:device_type e value:rv pode ser uma chave primária. Estas duas chaves JSON correspondem às colunas DEVICE_TYPE e RV da tabela EVENTS.

Nota

O Snowflake não impõe a restrição de chave primária. Em vez disso, a restrição serve como metadado que identifica a chave natural no Information Schema.

Adicione a restrição de chave primária à tabela EVENTS:

ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);

Copy

Insira um novo registro de evento JSON na tabela 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
  }');

Copy

Insira o novo registro que você adicionou à tabela RAW_SOURCE na tabela EVENTS com base em uma comparação dos valores de chave primária:

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);

Copy

A consulta da tabela EVENTS mostra a linha adicionada:

select * from EVENTS;

Copy

A consulta retorna o seguinte resultado:

+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
| 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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+

Copy

Uso de todas as colunas para comparação¶

Se os dados JSON não tiverem campos que possam ser candidatos à chave primária, você poderia comparar todas as chaves JSON repetidas na tabela RAW_SOURCE com os valores das colunas correspondentes na tabela EVENTS.

Não são necessárias alterações em sua tabela EVENTS existente.

Insira um novo registro de evento JSON na tabela 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
  }');

Copy

Insira o novo registro da tabela RAW_SOURCE na tabela EVENTS com base na comparação de todos os valores chave que se repetem:

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);

Copy

A consulta da tabela EVENTS mostra a linha adicionada:

select * from EVENTS;

Copy

A consulta retorna o seguinte resultado:

+-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
| 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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+

Copy

Próximo: Etapa 5. Limpeza