最常用的5中流ETL模式

管道模式

1：过滤器

过滤器从流中删除不需要的记录，删除与 SQL where子句中的“规则”不匹配的记录。过滤器通常用于抑制敏感记录以确保合规性，或减少目标系统上的处理负载或存储需求。

-- Filter only records pertaining to the application

insert into application_events
select * from http_events
where hostname = 'app.decodable.co'



-- Filter only records that modify the inventory

insert into inventory_updates
select * from http_events
where hostname = 'api.mycompany.com' and
path like '/v1/inventory%' and
  method in ( 'POST', 'PUT', 'DELETE', 'PATCH' )

2：路线

Route 模式从一个或多个输入流创建多个输出流，根据一组规则将记录定向到正确的目的地。此模式实际上由多个过滤器组成，它们都可以查看每个输入记录，但每个过滤器仅传输与该特定目的地的规则匹配的那些记录。

-- Route security-related HTTP events

insert into security_events
select * from http_events
where path like '/login%' or
path like '/billing/cc%'
-- Route app-related HTTP events

insert into application_events
select * from http_eventswhere hostname = 'app.decodable.co'

-- Route requests to Customer Success if it looks like the user needs help

insert into cs_alerts
select * from http_events
where response_code between 500 and 599 or -- any server failure
( path = '/signup' and response_code != 200 ) or -- failed to sign up for any reason

3：变换

转换管道通过修改输入记录来创建输出记录。通常这将导致 1:1 传输，但在某些情况下，输出来自多个输入记录，因此可能存在 1:many 关系。在这里，我们将调用三个专门的转换：

变换：提取

解析输入记录，从输入记录中提取数据并将其用作丰富派生输出记录的基础。

-- Parse timestamp and action

insert into user_events
select
to_date(fields['ts'], 'YYYY-MM-DD''T''HH:MI:SS') as ts,
  fields['user_id']    as user_id,
  fields['path']       as path,  
  case fields['method']    when 'GET'         then 'read'
    when 'POST', 'PUT' then 'modify'
    when 'DELETE'      then 'delete'
  end as action
  from (  select
    grok(
      body,      '\[${ISO8661_DATETIME:ts} ${DATA:method} "${PATH:path}" uid:${DATA:user_id}'
    ) as fields  from http_event
)

变换：归一化

传入的数据记录通常需要针对模式进行规范化，以便目标系统处理它们。缺少的字段可能需要填充默认值，可能需要删除可选字段，并强制执行数据类型。

-- Cleanse incoming data for downstream processes
insert into sensor_readings
select
cast(ifnull(sensor_id, '0') as bigint) as sensor_id,  
lower(trim(name))                      as name,  
cast(`value` as bigint)                as reading
from raw_sensor_readings

转换：匿名化

在目标系统不需要信息来完成处理的情况下，匿名管道只是出于合规、监管或隐私原因而消除了敏感字段。

-- Anonymize SSNs and zip codes
insert into user_events_masked
select
user_id,
username,  
overlay(ssn placing '*' from 1 for 12) as ssn,  
substring(zip_code from 1 for 2)       as zip_code_1,
action
from user_events

4：聚合

聚合管道通常使用 SQL 窗口函数将传入记录分组到存储桶中（通常基于时间），在这些存储桶上执行聚合操作。Count、Min、Max、Avg、Sum 是典型的运算符，但还有很多。

-- Count the number of events by path and status every 10 seconds.
insert into site_activity
select
  window_start,
  window_end,
  path,
  status, 
  count(1) as `count`
from table(
tumble(    
  table http_events,    
  descriptor(_time),
  interval '10' seconds
  )
)group by window_start, window_end, path, status

5：触发

我们的最终模式是触发器。与几乎所有其他模式不同，触发器输出记录可能与输入记录的模式几乎没有重叠，因为它表明已在一个或多个输入记录上检测到一组条件，并作为结果输出警报。输出模式可以表示检测到的条件、要采取的行动或两者兼而有之。

-- Build hourly usage data for a Stripe integration on the output stream
insert into stripe_product_usage
select
	window_start as _time,
  customer_id,  
  'abcd1234' as price_id,
  sum(bytes_sent) / 1024 / 1024 as mb_sent
  from table(
  tumble(    
    table document_downloads,    
    descriptor(_time),
    interval '1' hour
  )
)group by window_start, customer_idhaving mb_sent > 1024

参考文献：https://mp.weixin.qq.com/s/idZUTaRwXf5Ucale-WmWyA