Tutorial: Introdução às funções de métricas de dados¶

Criação de uma tabela¶

USE ROLE dq_tutorial_role;
CREATE DATABASE IF NOT EXISTS dq_tutorial_db;
CREATE SCHEMA IF NOT EXISTS sch;

CREATE TABLE customers (
  account_number NUMBER(38,0),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  email VARCHAR(16777216),
  phone VARCHAR(16777216),
  created_at TIMESTAMP_NTZ(9),
  street VARCHAR(16777216),
  city VARCHAR(16777216),
  state VARCHAR(16777216),
  country VARCHAR(16777216),
  zip_code NUMBER(38,0)
);

Insira os valores em uma tabela¶

Adicione dados à tabela:

USE WAREHOUSE dq_tutorial_wh;

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
  VALUES (1589420, 'san francisco', 'usa', 'john.doe@', 'john', 'doe', 1234567890, null, null, null);

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
  VALUES (8028387, 'san francisco', 'usa', 'bart.simpson@example.com', 'bart', 'simpson', 1012023030, null, 'market st', 94102);

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
  VALUES
    (1589420, 'san francisco', 'usa', 'john.doe@example.com', 'john', 'doe', 1234567890, 'ca', 'concar dr', 94402),
    (2834123, 'san mateo', 'usa', 'jane.doe@example.com', 'jane', 'doe', 3641252911, 'ca', 'concar dr', 94402),
    (4829381, 'san mateo', 'usa', 'jim.doe@example.com', 'jim', 'doe', 3641252912, 'ca', 'concar dr', 94402),
    (9821802, 'san francisco', 'usa', 'susan.smith@example.com', 'susan', 'smith', 1234567891, 'ca', 'geary st', 94121),
    (8028387, 'san francisco', 'usa', 'bart.simpson@example.com', 'bart', 'simpson', 1012023030, 'ca', 'market st', 94102);

Copy

Criar uma DMF¶

Crie uma função de métricas de dados (DMF) para retornar o número de endereços de e-mail em uma coluna que não corresponda à expressão regular especificada:

CREATE DATA METRIC FUNCTION IF NOT EXISTS
  invalid_email_count (ARG_T table(ARG_C1 STRING))
  RETURNS NUMBER AS
  'SELECT COUNT_IF(FALSE = (
    ARG_C1 REGEXP ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$''))
    FROM ARG_T';

Copy

Defina o cronograma na tabela¶

O cronograma de DMF define quando todas as DMFs na tabela são executadas. Atualmente, 5 minutos é o menor intervalo possível:

ALTER TABLE customers SET DATA_METRIC_SCHEDULE = '5 MINUTE';

Copy

Defina as DMFs na tabela e confira as referências¶

Associe a DMF à tabela:

ALTER TABLE customers ADD DATA METRIC FUNCTION
  invalid_email_count ON (email);

Copy

Como o cronograma é definido para 5 minutos, precisamos esperar 5 minutos para que o Snowflake chame a DMF e processe os resultados. Por enquanto, podemos verificar se a DMF está associada à tabela chamando a função de tabela Information Schema DATA_METRIC_FUNCTION_REFERENCES:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
  REF_ENTITY_NAME => 'dq_tutorial_db.sch.customers',
  REF_ENTITY_DOMAIN => 'TABLE'));

Copy

Visualize os resultados da DMF:¶

Os resultados da chamada da DMF agendada são armazenados na exibição DATA_QUALITY_MONITORING_RESULTS. Para determinar o número de endereços de e-mail inválidos, consulte a exibição DATA_QUALITY_MONITORING_RESULTS para ver os resultados da chamada da DMF agendada:

SELECT scheduled_time, measurement_time, table_name, metric_name, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE TRUE
AND METRIC_NAME = 'INVALID_EMAIL_COUNT'
AND METRIC_DATABASE = 'DQ_TUTORIAL_DB'
LIMIT 100;

Copy

Os resultados mostram que a value coluna contém 1. Este número corresponde a um endereço de e-mail com formato incorreto, que corresponde à primeira instrução INSERT na seção Insira os valores em uma tabela.

Remova a definição das DMFs da tabela¶

Você estabeleceu que a DMF está funcionando conforme o esperado com base na definição da DMF, o cronograma e os resultados esperados.

Para evitar o uso desnecessário de crédito sem servidor, remova a definição da DMF da tabela:

ALTER TABLE customers DROP DATA METRIC FUNCTION
  invalid_email_count ON (email);

Copy

Criação de uma tabela¶

Cole e execute a seguinte instrução para criar uma tabela.

CREATE or REPLACE table dq_tutorial_db.sch.employeesTable (
  id NUMBER,
  name VARCHAR,
  last_name VARCHAR,
  email VARCHAR,
  zip_code NUMBER
 );

Copy

Insira os valores em uma tabela¶

Adicione dados com alguns registros incorretos, como valores em branco, à tabela:

INSERT INTO dq_tutorial_db.sch.employeesTable (id, name, last_name, email, zip_code)
VALUES
  (8, 'John', 'Doe', 'johndoe@example.com', 12345),
  (23, '', 'Smith', 'smithj@example.com', 23456),
  (1, NULL, 'Taylor', 'taylorj@example.com', 34567),
  (99, 'Jane', 'Adams', 'jadams@example.com', 45678),
  (50, 'Alice', 'Brown', '', 56789),
  (51, NULL, 'Lee', 'lee@example.com', 67890),
  (234, 'Michael', '', 'michael@example.com', 78901),
  (56, 'Sara', 'Jones', 'sjones@example.com', 89012),
  (11, '', NULL, 'blanklast@example.com', 90123),
  (12, 'Tom', 'Harris', NULL, 10234);

Copy

Retornar o número de valores em branco executando a função de métrica de dados BLANK_COUNT¶

Execute a função de métrica de dados BLANK_COUNT para retornar o número de valores em branco:

SELECT snowflake.core.blank_count (SELECT name FROM dq_tutorial_db.sch.employeesTable)

Retornar as linhas executando a função SYSTEM$DATA_METRIC_SCAN¶

Para retornar as linhas da tabela que contêm valores em branco na coluna name, execute a função SYSTEM$DATA_METRIC_SCAN na coluna name.

SELECT *
  FROM TABLE(SYSTEM$DATA_METRIC_SCAN(
    REF_ENTITY_NAME  => 'dq_tutorial_db.sch.employeesTable',
    METRIC_NAME  => 'snowflake.core.blank_count',
    ARGUMENT_NAME => 'name'
   ));

Copy

Exibir os resultados da verificação métrica do sistema¶

Os resultados mostram as linhas da tabela employeeTable que contêm valores em branco.

+-----+-------+--------------+-----------------------+-----------+------- --+
| ID  | NAME  | LAST_NAME    | EMAIL                 | CREATEDAT | ZIP_CODE |
|-----+-------+--------------+-----------------------+----------------------|
| 23  |       |   Smith      | smith@example.com     | null      | 23456    |
| 11  |       |   null       | blanklast@example.com | null      | 90123    |
+-----+-------+--------------+-----------------------+-----------+----------+

Atualizar registros com um novo valor¶

Para substituir os valores em branco na coluna name, execute uma consulta na tabela de destino que inclua a função SYSTEM$DATA_METRIC_SCAN. Isso define os valores em branco na coluna name para NULL executando o comando UPDATE em cada uma das linhas retornadas pela função do sistema:

UPDATE dq_tutorial_db.sch.employeesTable
  SET name = null
  WHERE dq_tutorial_db.sch.employeesTable.ID IN (
    select ID from table(system$data_metric_scan(
  REF_ENTITY_NAME => 'dq_tutorial_db.sch.employeesTable',
  METRIC_NAME => 'snowflake.core.blank_count',
  ARGUMENT_NAME => 'name'
  )));

Depois que você atualiza os valores, a seguinte execução retorna 0:

SELECT snowflake.core.blank_count (SELECT name FROM dq_tutorial_db.sch.employeesTable)

Nesta seção, você extraiu registros com dados que não passaram na verificação de qualidade. Na próxima seção, você aprenderá a visualizar o consumo de crédito sem servidor.

Resumo e pontos principais¶

Em resumo, você aprendeu a fazer o seguinte:

• Criar uma DMF personalizada para avaliar a qualidade dos dados e gerenciar a DMF para otimizar o uso de crédito sem servidor.

• Monitorar o uso de crédito sem servidor associado à chamada da DMF agendada.

Descarte os objetos do tutorial¶

Se você planeja repetir o tutorial, pode manter os objetos que criou.

Caso contrário, descarte os objetos do tutorial da seguinte forma:

USE ROLE ACCOUNTADMIN;
DROP DATABASE dq_tutorial_db;
DROP WAREHOUSE dq_tutorial_wh;
DROP ROLE dq_tutorial_role;

Qual é o próximo passo?¶

Continue aprendendo sobre o Snowflake com os seguintes recursos:

• Saiba mais sobre as DMFs começando por Introdução à qualidade de dados e funções de métricas de dados.

• Conclua os outros tutoriais fornecidos pela Snowflake no tópico Tutoriais Snowflake.