이상적인 데이터 웨어하우스는 어떤 원칙에 따라 구축되나요?

상용구 코드가 없는 경우 비즈니스 가치와 분석에 집중하세요. DWH를 코드베이스로 관리: 버전 관리, 검토, 자동화된 테스트 및 CI. 모듈식, 확장 가능한 오픈 소스 및 커뮤니티입니다. 사용자 친화적인 문서화 및 종속성 시각화(Data Lineage).

이에 대한 자세한 내용과 빅 데이터 및 분석 생태계에서 DBT의 역할에 대해 자세히 알아보세요. cat에 오신 것을 환영합니다.

안녕하세요.

Artemy Kozyr님이 연락드립니다. 저는 5년 넘게 데이터 웨어하우스 작업, ETL/ELT 구축, 데이터 분석 및 시각화 작업을 해왔습니다. 나는 현재 일하고 있습니다 Wheely, 저는 OTUS에서 강좌를 가르치고 있습니다 데이터 엔지니어, 그리고 오늘은 시작을 기대하며 제가 쓴 글을 여러분과 공유하고 싶습니다. 과정에 대한 신규 등록.

개요

DBT 프레임워크는 ELT(Extract - Transform - Load) 약어의 T에 관한 것입니다.

BigQuery, Redshift, Snowflake와 같은 생산적이고 확장 가능한 분석 데이터베이스의 출현으로 인해 데이터 웨어하우스 외부에서 변환을 수행할 필요가 없게 되었습니다. 

DBT는 소스에서 데이터를 다운로드하지 않지만 저장소(내부 또는 외부 저장소)에 이미 로드된 데이터로 작업할 수 있는 좋은 기회를 제공합니다.

DBT의 주요 목적은 코드를 가져와 SQL로 컴파일하고 리포지토리에서 올바른 순서로 명령을 실행하는 것입니다.

DBT 프로젝트 구조

프로젝트는 다음 두 가지 유형의 디렉터리와 파일로만 구성됩니다.

• 모델(.sql) - SELECT 쿼리로 표현되는 변환 단위
• 구성 파일(.yml) - 매개변수, 설정, 테스트, 문서

기본적으로 작업은 다음과 같이 구성됩니다.

• 사용자는 편리한 IDE에서 모델 코드를 준비합니다.
• CLI를 사용하여 모델이 시작되고 DBT가 모델 코드를 SQL로 컴파일합니다.
• 컴파일된 SQL 코드는 지정된 순서(그래프)로 Storage에서 실행됩니다.

CLI에서 실행하는 모습은 다음과 같습니다.

모든 것이 SELECT입니다

이는 Data Build Tool 프레임워크의 핵심 기능입니다. 즉, DBT는 쿼리를 저장소로 구체화하는 것과 관련된 모든 코드(CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ... 명령의 변형)를 추상화합니다.

모든 모델에는 결과 데이터 세트를 정의하는 하나의 SELECT 쿼리 작성이 포함됩니다.

이 경우 변환 논리는 다중 레벨일 수 있으며 여러 다른 모델의 데이터를 통합할 수 있습니다. 주문 쇼케이스(f_orders)를 구축할 모델의 예:

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}
 
with orders as (
 
   select * from {{ ref('stg_orders') }}
 
),
 
order_payments as (
 
   select * from {{ ref('order_payments') }}
 
),
 
final as (
 
   select
       orders.order_id,
       orders.customer_id,
       orders.order_date,
       orders.status,
       {% for payment_method in payment_methods -%}
       order_payments.{{payment_method}}_amount,
       {% endfor -%}
       order_payments.total_amount as amount
   from orders
       left join order_payments using (order_id)
 
)
 
select * from final

여기서 어떤 흥미로운 점을 볼 수 있나요?

첫 번째: CTE(Common Table Expressions) 사용 - 많은 변환과 비즈니스 논리가 포함된 코드를 구성하고 이해합니다.

둘째: 모델 코드는 SQL과 언어의 혼합입니다. 진자 (템플릿 언어).

예제에서는 루프를 사용합니다. for 표현식에 지정된 각 결제 방법에 대한 금액을 생성합니다. 세트. 기능도 사용됩니다 심판 — 코드 내에서 다른 모델을 참조하는 기능:

• 컴파일 중 심판 Storage의 테이블이나 뷰에 대한 대상 포인터로 변환됩니다.
• 심판 모델 종속성 그래프를 작성할 수 있습니다.

그것은 진자 DBT에 거의 무한한 가능성을 추가합니다. 가장 일반적으로 사용되는 것은 다음과 같습니다.

• If / else 문 - 분기 문
• For 루프 - 사이클
• 변수
• 매크로 - 매크로 생성

구체화: 테이블, 뷰, 증분

구체화 전략은 결과 모델 데이터 세트가 저장소에 저장되는 접근 방식입니다.

기본적으로는 다음과 같습니다.

• 테이블 - 저장소의 물리적 테이블
• 보기 - 저장소의 보기, 가상 테이블

더 복잡한 구체화 전략도 있습니다.

• 증분 - 증분 로드(대형 팩트 테이블) 새 줄이 추가되고, 변경된 줄이 업데이트되고, 삭제된 줄이 지워집니다. 
• 임시 - 모델이 직접적으로 구체화되지는 않지만 다른 모델에 CTE로 참여합니다.
• 직접 추가할 수 있는 다른 전략

구체화 전략 외에도 특정 저장소에 대한 최적화 기회가 있습니다. 예를 들면 다음과 같습니다.

• 눈송이: 임시 테이블, 병합 동작, 테이블 클러스터링, 권한 복사, 보안 뷰
• 적색 편이: Distkey, Sortkey(인터리브, 복합), 후기 바인딩 뷰
• BigQuery: 테이블 파티셔닝 및 클러스터링, 병합 동작, KMS 암호화, 레이블 및 태그
• 불꽃: 파일 형식(parquet, csv, json, orc, delta), partition_by, Clustered_by, buckets, Incremental_strategy

현재 지원되는 저장소는 다음과 같습니다.

• 포스트그레스
• 적색 편이
• BigQuery
• 눈송이
• 프레스토(일부)
• 스파크(일부)
• Microsoft SQL Server(커뮤니티 어댑터)

모델을 개선해 보겠습니다.

• 채우기를 증분적으로 만들어 보겠습니다. (증분)
• Redshift에 대한 분할 및 정렬 키를 추가해 보겠습니다.

-- Конфигурация модели: 
-- Инкрементальное наполнение, уникальный ключ для обновления записей (unique_key)
-- Ключ сегментации (dist), ключ сортировки (sort)
{{
  config(
       materialized='incremental',
       unique_key='order_id',
       dist="customer_id",
       sort="order_date"
   )
}}
 
{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}
 
with orders as (
 
   select * from {{ ref('stg_orders') }}
   where 1=1
   {% if is_incremental() -%}
       -- Этот фильтр будет применен только для инкрементального запуска
       and order_date >= (select max(order_date) from {{ this }})
   {%- endif %} 
 
),
 
order_payments as (
 
   select * from {{ ref('order_payments') }}
 
),
 
final as (
 
   select
       orders.order_id,
       orders.customer_id,
       orders.order_date,
       orders.status,
       {% for payment_method in payment_methods -%}
       order_payments.{{payment_method}}_amount,
       {% endfor -%}
       order_payments.total_amount as amount
   from orders
       left join order_payments using (order_id)
 
)
 
select * from final

모델 종속성 그래프

또한 종속성 트리이기도 합니다. DAG(방향성 비순환 그래프)라고도 합니다.

DBT는 모든 프로젝트 모델의 구성을 기반으로 그래프를 작성하거나 오히려 모델 내의 ref() 링크를 다른 모델에 연결합니다. 그래프를 사용하면 다음 작업을 수행할 수 있습니다.

• 올바른 순서로 모델 실행
• 점포 형성의 병렬화
• 임의의 하위 그래프 실행 

그래프 시각화의 예:

그래프의 각 노드는 모델이며, 그래프의 가장자리는 ref 표현식으로 지정됩니다.

데이터 품질 및 문서화

모델 자체를 생성하는 것 외에도 DBT를 사용하면 결과 데이터 세트에 대해 다음과 같은 다양한 가정을 테스트할 수 있습니다.

• Null 아님
• 유일한
• 참조 무결성 - 참조 무결성(예: 주문 테이블의 customer_id는 고객 테이블의 id에 해당함)
• 허용되는 값 목록 일치

예를 들어 하루, 일주일, 한 달 전 지표를 사용한 수익 % 편차와 같은 자체 테스트(맞춤형 데이터 테스트)를 추가할 수 있습니다. SQL 쿼리로 공식화된 모든 가정은 테스트가 될 수 있습니다.

이러한 방식으로 창고 창의 데이터에서 원치 않는 편차와 오류를 포착할 수 있습니다.

문서화 측면에서 DBT는 모델 및 속성 수준에서 메타데이터와 설명을 추가, 버전 관리 및 배포하기 위한 메커니즘을 제공합니다. 

구성 파일 수준에서 테스트 및 문서를 추가하는 방법은 다음과 같습니다.

 - name: fct_orders
   description: This table has basic information about orders, as well as some derived facts based on payments
   columns:
     - name: order_id
       tests:
         - unique # проверка на уникальность значений
         - not_null # проверка на наличие null
       description: This is a unique identifier for an order
     - name: customer_id
       description: Foreign key to the customers table
       tests:
         - not_null
         - relationships: # проверка ссылочной целостности
             to: ref('dim_customers')
             field: customer_id
     - name: order_date
       description: Date (UTC) that the order was placed
     - name: status
       description: '{{ doc("orders_status") }}'
       tests:
         - accepted_values: # проверка на допустимые значения
             values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

생성된 웹사이트에서 이 문서는 다음과 같습니다.

매크로 및 모듈

DBT의 목적은 SQL 스크립트 세트가 되는 것이 아니라 사용자에게 자체 변환을 구축하고 이러한 모듈을 배포할 수 있는 강력하고 기능이 풍부한 수단을 제공하는 것입니다.

매크로는 모델 내에서 함수로 호출할 수 있는 구문 및 표현식 세트입니다. 매크로를 사용하면 DRY(Don't Repeat Yourself) 엔지니어링 원칙에 따라 모델과 프로젝트 간에 SQL을 재사용할 수 있습니다.

매크로 예:

{% macro rename_category(column_name) %}
case
 when {{ column_name }} ilike  '%osx%' then 'osx'
 when {{ column_name }} ilike  '%android%' then 'android'
 when {{ column_name }} ilike  '%ios%' then 'ios'
 else 'other'
end as renamed_product
{% endmacro %}

사용법은 다음과 같습니다.

{% set column_name = 'product' %}
select
 product,
 {{ rename_category(column_name) }} -- вызов макроса
from my_table

DBT에는 사용자가 개별 모듈과 매크로를 게시하고 재사용할 수 있는 패키지 관리자가 함께 제공됩니다.

이는 다음과 같은 라이브러리를 로드하고 사용할 수 있음을 의미합니다.

• dbt_utils: 날짜/시간, 대리 키, 스키마 테스트, 피벗/피벗 해제 및 기타 작업
• 다음과 같은 서비스를 위한 기성품 쇼케이스 템플릿 제설기 и 스트라이프 
• 특정 데이터 저장소를 위한 라이브러리(예: 적색 편이 
• 로깅 — DBT 작업을 로깅하는 모듈

전체 패키지 목록은 다음에서 확인할 수 있습니다. DBT 허브.

더 많은 기능

여기에서는 팀과 제가 데이터 웨어하우스를 구축하는 데 사용하는 몇 가지 다른 흥미로운 기능과 구현에 대해 설명하겠습니다. Wheely.

런타임 환경 분리 DEV - TEST - PROD

동일한 DWH 클러스터 내에서도(다른 구성표 내에서). 예를 들어 다음 표현식을 사용합니다.

with source as (
 
   select * from {{ source('salesforce', 'users') }}
   where 1=1
   {%- if target.name in ['dev', 'test', 'ci'] -%}           
       where timestamp >= dateadd(day, -3, current_date)   
   {%- endif -%}
 
)

이 코드는 문자 그대로 다음과 같이 말합니다. 개발, 테스트, CI 지난 3일 동안의 데이터만 가져오고 그 이상은 가져오지 않습니다. 즉, 이러한 환경에서 실행하면 훨씬 더 빠르고 더 적은 리소스가 필요합니다. 환경에서 실행할 때 찌르다 필터 조건은 무시됩니다.

대체 열 인코딩을 통한 구체화

Redshift는 각 개별 열에 대한 데이터 압축 알고리즘을 설정할 수 있는 열 기반 DBMS입니다. 최적의 알고리즘을 선택하면 디스크 공간을 20~50%까지 줄일 수 있습니다.

매크로 redshift.compress_table ANALYZE COMPRESSION 명령을 실행하고, 권장 열 인코딩 알고리즘, 지정된 분할 키(dist_key) 및 정렬 키(sort_key)를 사용하여 새 테이블을 생성하고, 데이터를 해당 테이블로 전송하고, 필요한 경우 이전 복사본을 삭제합니다.

매크로 서명:

{{ compress_table(schema, table,
                 drop_backup=False,
                 comprows=none|Integer,
                 sort_style=none|compound|interleaved,
                 sort_keys=none|List<String>,
                 dist_style=none|all|even,
                 dist_key=none|String) }}

로깅 모델 실행

모델의 각 실행에 후크를 연결할 수 있으며, 이는 출시 전이나 모델 생성이 완료된 직후에 실행됩니다.

   pre-hook: "{{ logging.log_model_start_event() }}"
   post-hook: "{{ logging.log_model_end_event() }}"

로깅 모듈을 사용하면 필요한 모든 메타데이터를 별도의 테이블에 기록할 수 있으며, 이후에 병목 현상을 감사하고 분석하는 데 사용할 수 있습니다.

Looker의 로깅 데이터를 기반으로 한 대시보드는 다음과 같습니다.

스토리지 유지 관리 자동화

UDF(사용자 정의 함수)와 같은 사용된 리포지토리 기능의 일부 확장을 사용하는 경우 이러한 기능의 버전 관리, 액세스 제어 및 새 릴리스의 자동 롤아웃이 DBT에서 매우 편리합니다.

우리는 Python에서 UDF를 사용하여 해시, 이메일 도메인 및 비트마스크 디코딩을 계산합니다.

모든 실행 환경(dev, test, prod)에서 UDF를 생성하는 매크로의 예:

{% macro create_udf() -%}
 
 {% set sql %}
       CREATE OR REPLACE FUNCTION {{ target.schema }}.f_sha256(mes "varchar")
           RETURNS varchar
           LANGUAGE plpythonu
           STABLE
       AS $$  
           import hashlib
           return hashlib.sha256(mes).hexdigest()
       $$
       ;
 {% endset %}
  
 {% set table = run_query(sql) %}
 
{%- endmacro %}

Wheely에서는 PostgreSQL을 기반으로 하는 Amazon Redshift를 사용합니다. Redshift의 경우 정기적으로 테이블에 대한 통계를 수집하고 디스크 공간을 확보하는 것이 중요합니다(각각 ANALYZE 및 VACUUM 명령).

이를 위해 redshift_maintenance 매크로의 명령이 매일 밤 실행됩니다.

{% macro redshift_maintenance() %}
 
   {% set vacuumable_tables=run_query(vacuumable_tables_sql) %}
 
   {% for row in vacuumable_tables %}
       {% set message_prefix=loop.index ~ " of " ~ loop.length %}
 
       {%- set relation_to_vacuum = adapter.get_relation(
                                               database=row['table_database'],
                                               schema=row['table_schema'],
                                               identifier=row['table_name']
                                   ) -%}
       {% do run_query("commit") %}
 
       {% if relation_to_vacuum %}
           {% set start=modules.datetime.datetime.now() %}
           {{ dbt_utils.log_info(message_prefix ~ " Vacuuming " ~ relation_to_vacuum) }}
           {% do run_query("VACUUM " ~ relation_to_vacuum ~ " BOOST") %}
           {{ dbt_utils.log_info(message_prefix ~ " Analyzing " ~ relation_to_vacuum) }}
           {% do run_query("ANALYZE " ~ relation_to_vacuum) %}
           {% set end=modules.datetime.datetime.now() %}
           {% set total_seconds = (end - start).total_seconds() | round(2)  %}
           {{ dbt_utils.log_info(message_prefix ~ " Finished " ~ relation_to_vacuum ~ " in " ~ total_seconds ~ "s") }}
       {% else %}
           {{ dbt_utils.log_info(message_prefix ~ ' Skipping relation "' ~ row.values() | join ('"."') ~ '" as it does not exist') }}
       {% endif %}
 
   {% endfor %}
 
{% endmacro %}

DBT 클라우드

DBT를 서비스(Managed Service)로 활용하는 것이 가능합니다. 포함:

• 프로젝트 및 모델 개발을 위한 Web IDE
• 작업 구성 및 예약
• 간단하고 편리한 로그 액세스
• 프로젝트 문서가 포함된 웹사이트
• CI 연결(지속적 통합)

결론

DWH를 준비하고 섭취하는 것은 스무디를 마시는 것만큼 즐겁고 유익합니다. DBT는 Jinja, 사용자 확장(모듈), 컴파일러, 실행기, 패키지 관리자로 구성됩니다. 이러한 요소를 결합하면 데이터 웨어하우스를 위한 완벽한 작업 환경을 얻을 수 있습니다. 오늘날 DWH 내에서 혁신을 관리하는 데 이보다 더 좋은 방법은 없습니다.

DBT 개발자가 따르는 신념은 다음과 같이 공식화됩니다.

• GUI가 아닌 코드는 복잡한 분석 논리를 표현하는 데 가장 적합한 추상화입니다.
• 데이터 작업은 소프트웨어 엔지니어링(소프트웨어 엔지니어링)의 모범 사례를 적용해야 합니다.

• 중요한 데이터 인프라는 오픈 소스 소프트웨어로서 사용자 커뮤니티에 의해 제어되어야 합니다.
• 분석 도구뿐만 아니라 코드도 점차 오픈 소스 커뮤니티의 자산이 될 것입니다.

이러한 핵심 신념은 오늘날 850개 이상의 회사에서 사용되는 제품을 탄생시켰으며, 미래에 만들어질 많은 흥미로운 확장의 기초를 형성합니다.

관심 있는 분들을 위해 제가 몇 달 전 OTUS에서 열린 공개 레슨의 일환으로 진행한 공개 레슨 영상이 있습니다. Amazon Redshift 스토리지용 데이터 구축 도구.

DBT 및 데이터 웨어하우징 외에도 OTUS 플랫폼의 데이터 엔지니어 과정의 일환으로 동료와 저는 기타 관련 있고 현대적인 주제에 대해 강의합니다.

• 빅 데이터 애플리케이션을 위한 아키텍처 개념
• Spark 및 Spark Streaming 실습
• 데이터 소스 로드 방법 및 도구 탐색
• DWH에서 분석 쇼케이스 구축
• NoSQL 개념: HBase, Cassandra, ElasticSearch
• 모니터링 및 조정의 원칙 
• 최종 프로젝트: 멘토링 지원을 통해 모든 기술을 하나로 모으기

링크 :

1. DBT 문서 - 소개 — 공식 문서
2. 정확히 dbt란 무엇인가요? — DBT 저자 중 한 사람의 리뷰 기사 
3. Amazon Redshift 스토리지용 데이터 구축 도구 — YouTube, OTUS 오픈 레슨 녹화
4. 그린플럼 알아보기 — 다음 공개 강의는 15년 2020월 XNUMX일입니다.
5. 데이터 엔지니어링 코스 —OTUS
6. 성숙한 분석 워크플로 구축 — 데이터 및 분석의 미래 살펴보기
7. 이제 오픈 소스 분석이 필요한 때입니다. — 분석의 진화와 오픈 소스의 영향
8. dbtCloud를 사용한 지속적인 통합 및 자동화된 빌드 테스트 — DBT를 활용한 CI 구축 원칙
9. DBT 튜토리얼 시작하기 — 연습, 독립적인 작업을 위한 단계별 지침
10. Jaffle 상점 — Github DBT Tutorial — Github, 교육 프로젝트 코드

강좌에 대해 자세히 알아보세요.

출처 : habr.com