ProHoster > Blog > Pentadbiran > Alat Binaan Data atau perkara biasa antara Gudang Data dan Smoothie
Alat Binaan Data atau perkara biasa antara Gudang Data dan Smoothie
Atas prinsip apakah Gudang Data yang ideal dibina?
Fokus pada nilai perniagaan dan analitik tanpa adanya kod boilerplate. Menguruskan DWH sebagai pangkalan kod: versi, semakan, ujian automatik dan CI. Modulariti, kebolehlanjutan, sumber terbuka dan komuniti. Dokumentasi mesra pengguna dan visualisasi pergantungan (Data Lineage).
Lebih lanjut mengenai semua ini dan tentang peranan DBT dalam ekosistem Data Besar & Analitis - selamat datang ke kucing.
Hello semua orang
Artemy Kozyr sedang berhubung. Selama lebih daripada 5 tahun saya telah bekerja dengan gudang data, membina ETL/ELT, serta analisis dan visualisasi data. Saya sedang bekerja di beroda, saya mengajar di OTUS dalam satu kursus Jurutera Data, dan hari ini saya ingin berkongsi dengan anda artikel yang saya tulis untuk menjangkakan permulaannya pendaftaran baru untuk kursus tersebut.
Ulasan ringkas
Rangka kerja DBT adalah mengenai T dalam akronim ELT (Extract - Transform - Load).
Dengan kemunculan pangkalan data analisis yang produktif dan berskala seperti BigQuery, Redshift, Snowflake, tiada gunanya melakukan transformasi di luar Gudang Data.
DBT tidak memuat turun data daripada sumber, tetapi menyediakan peluang hebat untuk bekerja dengan data yang telah dimuatkan ke dalam Storan (dalam Storan Dalaman atau Luaran).
Tujuan utama DBT adalah untuk mengambil kod, menyusunnya ke dalam SQL, melaksanakan arahan dalam urutan yang betul dalam Repositori.
Struktur Projek DBT
Projek ini terdiri daripada direktori dan fail hanya 2 jenis:
Model (.sql) - unit transformasi yang dinyatakan oleh pertanyaan SELECT
Pada peringkat asas, kerja disusun seperti berikut:
Pengguna menyediakan kod model dalam mana-mana IDE yang mudah
Menggunakan CLI, model dilancarkan, DBT menyusun kod model ke dalam SQL
Kod SQL yang disusun dilaksanakan dalam Storan dalam urutan tertentu (graf)
Inilah yang mungkin kelihatan seperti berjalan dari CLI:
Semuanya PILIH
Ini adalah ciri pembunuh rangka kerja Alat Bina Data. Dalam erti kata lain, DBT mengabstrak semua kod yang dikaitkan dengan mewujudkan pertanyaan anda ke dalam Kedai (variasi daripada arahan CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Mana-mana model melibatkan penulisan satu pertanyaan SELECT yang mentakrifkan set data yang terhasil.
Dalam kes ini, logik transformasi boleh menjadi pelbagai peringkat dan menyatukan data daripada beberapa model lain. Contoh model yang akan membina pameran pesanan (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
Apakah perkara menarik yang boleh kita lihat di sini?
Pertama: CTE (Ungkapan Jadual Biasa) - untuk menyusun dan memahami kod yang mengandungi banyak transformasi dan logik perniagaan
Kedua: Kod model ialah campuran SQL dan bahasa Jinja (bahasa templat).
Contoh menggunakan gelung Untuk untuk menjana amaun bagi setiap kaedah pembayaran yang dinyatakan dalam ungkapan menetapkan. Fungsi ini juga digunakan ref β keupayaan untuk merujuk model lain dalam kod:
Semasa penyusunan ref akan ditukar kepada penunjuk sasaran kepada jadual atau paparan dalam Storan
ref membolehkan anda membina graf pergantungan model
Tepat Jinja menambah kemungkinan hampir tidak terhad kepada DBT. Yang paling biasa digunakan ialah:
Penyata if / else - penyata cawangan
Untuk gelung
Pembolehubah
Makro - mencipta makro
Pewujudan: Jadual, Pandangan, Penambahan
Strategi pewujudan ialah pendekatan yang mengikutnya set data model yang terhasil akan disimpan dalam Storan.
Secara asasnya ialah:
Jadual - jadual fizikal dalam Storan
Lihat - lihat, jadual maya dalam Storan
Terdapat juga strategi pewujudan yang lebih kompleks:
Penambahan - pemuatan tambahan (jadual fakta besar); baris baru ditambah, baris yang diubah dikemas kini, baris yang dipadam dikosongkan
Ephemeral - model tidak menjadi kenyataan secara langsung, tetapi mengambil bahagian sebagai CTE dalam model lain
Sebarang strategi lain yang anda boleh tambah sendiri
Selain strategi pewujudan, terdapat peluang untuk pengoptimuman untuk Storan tertentu, contohnya:
Mari jadikan pengisiannya secara berperingkat (Incremental)
Mari tambahkan pembahagian dan kunci pengisihan untuk 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
Graf pergantungan model
Ia juga merupakan pokok pergantungan. Ia juga dikenali sebagai DAG (Directed Acyclic Graph).
DBT membina graf berdasarkan konfigurasi semua model projek, atau lebih tepat, pautan ref() dalam model kepada model lain. Mempunyai graf membolehkan anda melakukan perkara berikut:
Menjalankan model dalam urutan yang betul
Keselarian pembentukan etalase
Menjalankan subgraf sewenang-wenangnya
Contoh visualisasi graf:
Setiap nod graf ialah model; tepi graf ditentukan oleh ungkapan ref.
Kualiti Data dan Dokumentasi
Selain menjana model itu sendiri, DBT membenarkan anda menguji beberapa andaian tentang set data yang terhasil, seperti:
Bukan Null
Unik
Integriti Rujukan - integriti rujukan (contohnya, customer_id dalam jadual pesanan sepadan dengan id dalam jadual pelanggan)
Memadankan senarai nilai yang boleh diterima
Anda boleh menambah ujian anda sendiri (ujian data tersuai), seperti, sebagai contoh, % sisihan hasil dengan penunjuk dari sehari, seminggu, sebulan yang lalu. Sebarang andaian yang dirumuskan sebagai pertanyaan SQL boleh menjadi ujian.
Dengan cara ini, anda boleh menangkap penyelewengan dan ralat yang tidak diingini dalam data dalam tetingkap Gudang.
Dari segi dokumentasi, DBT menyediakan mekanisme untuk menambah, membuat versi dan mengedarkan metadata dan ulasan pada model dan juga peringkat atribut.
Begini rupa penambahan ujian dan dokumentasi pada peringkat fail konfigurasi:
- 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']
Dan inilah rupa dokumentasi ini pada tapak web yang dijana:
Makro dan Modul
Tujuan DBT bukanlah untuk menjadi satu set skrip SQL, tetapi untuk menyediakan pengguna dengan cara yang berkuasa dan kaya dengan ciri untuk membina transformasi mereka sendiri dan mengedarkan modul ini.
Makro ialah set binaan dan ungkapan yang boleh dipanggil sebagai fungsi dalam model. Makro membolehkan anda menggunakan semula SQL antara model dan projek mengikut prinsip kejuruteraan DRY (Don't Repeat Yourself).
Contoh makro:
{% 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 %}
Dan penggunaannya:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- Π²ΡΠ·ΠΎΠ² ΠΌΠ°ΠΊΡΠΎΡΠ°
from my_table
DBT datang dengan pengurus pakej yang membolehkan pengguna menerbitkan dan menggunakan semula modul dan makro individu.
Ini bermakna boleh memuatkan dan menggunakan perpustakaan seperti:
dbt_utils: bekerja dengan Tarikh/Masa, Kunci Pengganti, ujian Skema, Pivot/Unpivot dan lain-lain
Templat pameran sedia dibuat untuk perkhidmatan seperti Salji salji ΠΈ Jalur
Perpustakaan untuk Stor Data tertentu, mis. Redshift
Di sini saya akan menerangkan beberapa ciri dan pelaksanaan menarik lain yang saya dan pasukan gunakan untuk membina Gudang Data beroda.
Pengasingan persekitaran masa jalan DEV - TEST - PROD
Walaupun dalam kelompok DWH yang sama (dalam skema yang berbeza). Sebagai contoh, menggunakan ungkapan berikut:
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 -%}
)
Kod ini secara literal berkata: untuk persekitaran dev, ujian, ci ambil data hanya untuk 3 hari terakhir dan tidak lebih. Iaitu, berjalan dalam persekitaran ini akan menjadi lebih pantas dan memerlukan lebih sedikit sumber. Apabila berjalan di persekitaran prod keadaan penapis akan diabaikan.
Pewujudan dengan pengekodan lajur ganti
Redshift ialah DBMS kolumnar yang membolehkan anda menetapkan algoritma pemampatan data untuk setiap lajur individu. Memilih algoritma optimum boleh mengurangkan ruang cakera sebanyak 20-50%.
Makro redshift.compress_table akan melaksanakan arahan ANALYZE COMPRESSION, cipta jadual baharu dengan algoritma pengekodan lajur yang disyorkan, kekunci segmentasi yang ditentukan (dist_key) dan pengisihan (sort_key), pindahkan data kepadanya dan, jika perlu, padam salinan lama.
Modul pengelogan akan membolehkan anda merekodkan semua metadata yang diperlukan dalam jadual berasingan, yang kemudiannya boleh digunakan untuk mengaudit dan menganalisis kesesakan.
Inilah rupa papan pemuka berdasarkan data pengelogan dalam Looker:
Automasi Penyelenggaraan Storan
Jika anda menggunakan beberapa sambungan kefungsian Repositori yang digunakan, seperti UDF (Fungsi Ditetapkan Pengguna), maka versi fungsi ini, kawalan akses dan pelancaran automatik keluaran baharu adalah sangat mudah dilakukan dalam DBT.
Kami menggunakan UDF dalam Python untuk mengira cincang, domain e-mel dan penyahkodan bitmask.
Contoh makro yang mencipta UDF pada mana-mana persekitaran pelaksanaan (dev, test, prod):
{% 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 %}
Di Wheely kami menggunakan Amazon Redshift, yang berdasarkan PostgreSQL. Untuk Redshift, adalah penting untuk kerap mengumpul statistik pada jadual dan mengosongkan ruang cakera - arahan ANALYZE dan VACUUM, masing-masing.
Untuk melakukan ini, arahan daripada makro redshift_maintenance dilaksanakan setiap malam:
{% 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 %}
Awan DBT
Anda boleh menggunakan DBT sebagai perkhidmatan (Perkhidmatan Terurus). Termasuk:
IDE Web untuk membangunkan projek dan model
Konfigurasi dan penjadualan kerja
Akses mudah dan mudah kepada log
Laman web dengan dokumentasi projek anda
Menyambungkan CI (Integrasi Berterusan)
Kesimpulan
Menyedia dan mengambil DWH menjadi menyeronokkan dan bermanfaat seperti meminum smoothie. DBT terdiri daripada Jinja, sambungan pengguna (modul), pengkompil, pelaksana dan pengurus pakej. Dengan meletakkan elemen ini bersama-sama anda mendapat persekitaran kerja yang lengkap untuk Gudang Data anda. Hampir tidak ada cara yang lebih baik untuk mengurus transformasi dalam DWH hari ini.
Kepercayaan yang diikuti oleh pembangun DBT dirumuskan seperti berikut:
Kod, bukan GUI, adalah abstraksi terbaik untuk menyatakan logik analitik yang kompleks
Bekerja dengan data harus menyesuaikan amalan terbaik dalam kejuruteraan perisian (Kejuruteraan Perisian)
Infrastruktur data kritikal harus dikawal oleh komuniti pengguna sebagai perisian sumber terbuka
Bukan sahaja alat analisis, tetapi juga kod akan semakin menjadi hak milik komuniti Sumber Terbuka
Kepercayaan teras ini telah melahirkan produk yang digunakan oleh lebih 850 syarikat hari ini, dan ia menjadi asas kepada banyak sambungan menarik yang akan dibuat pada masa hadapan.
Bagi mereka yang berminat, terdapat video pelajaran terbuka yang saya berikan beberapa bulan lalu sebagai sebahagian daripada pelajaran terbuka di OTUS - Alat Binaan Data untuk Storan Amazon Redshift.
Selain DBT dan Data Warehousing, sebagai sebahagian daripada kursus Jurutera Data pada platform OTUS, saya dan rakan sekerja saya mengajar kelas mengenai beberapa topik lain yang berkaitan dan moden:
Konsep Seni Bina untuk Aplikasi Data Besar
Berlatih dengan Spark dan Spark Streaming
Meneroka kaedah dan alatan untuk memuatkan sumber data
Membina pameran analisis di DWH
Konsep NoSQL: HBase, Cassandra, ElasticSearch
Prinsip pemantauan dan orkestrasi
Projek Akhir: menyatukan semua kemahiran di bawah sokongan mentoring