Wiedzieliście, że Snowflake utrzymuje pewne funkcjonalności w „tajemnicy”? Jedną z takich funkcjonalności są kolumny wirtualne, których definicji i opisu próżno szukać w oficjalnej dokumentacji (poza drobnymi wzmiankami dotyczącymi bezpieczeństwa danych). Szukając ich przez google trafisz w pierwszej kolejności na wpisy w community Snowflake’a i artykuł w Medium.
Dowiedziałem się o nich czytając świetną książkę „Data Modeling with Snowflake” autorstwa Serge Gershkovich’a.
Jak zdefiniować wirtualną kolumnę?
Zróbmy sobie przykładową tabelę, gdzie zdefiniujemy kolumny full_name i age jako kolumny wirtualne za pomocą składni [...] as [definicja obliczeniowa kolumny]:
create or replace transient table users
(
id number(10,0) not null unique
,first_name varchar not null
,last_name varchar not null
,full_name varchar not null as (concat(first_name,' ',last_name))
,birthday date not null
,age number (3,0) as (datediff(year,birthday,getdate()))
);zapiszmy 2 wiersze i odczytajmy dane:
insert into users (id, first_name, last_name, birthday)
values
(1,'Jan','Kowalski','1980-05-15')
,(2,'John','Doe','2005-10-14');
select *
from users;
Jak widać kolumny full_name i age obliczyły się prawidłowo zgodnie ze zdefiniowanymi warunkami.
Tabele z takimi kolumnami bez problemu można wyszukiwać indywidualnie jak i wyszukiwać po nich:
select full_name
from users
where id = 2;
select *
from users
where age > 20;
Aktualizacja kolumn wirtualnych
Kolumny wirtualne działają jedynie obliczeniowo, więc próbując je zaktualizować Snowflake zwróci jedynie błąd.
update users
set full_name = 'Mikołaj Nowak'
where id = 1;
Przy aktualizacji kolumn bazowych widać, że wszystko działa zgodnie z oczekiwaniami:
update users
set last_name = 'Nowak'
where id = 1;
select *
from users;
Kolumny wirtualne vs zwykłe
Nie ma zdziwienia, że z punktu widzenia wydajności i kosztu odczytu kolumny wirtualnej są droższy niż zwykłej. Porównajmy je.
create or replace transient table users_2 as select * from users;
show tables;Przede wszystkim po rozmiarze tabel widać wyraźnie, że kolumny wirtualne nie są przechowywane fizycznie w tabeli.
desc table users;
desc table users_2;
W opisie tabel możemy zauważyć, że kolumny wirtualne mają przypisane wyrażenie z definicją ich obliczania.
Podsumowując
Zalety kolumn wirtualnych:
- Uproszczenie modelu danych – zamiast definiować obliczenia w dalszych widokach można je ustalić już na poziomie definicji tabeli.
- Poprawa spójności danych – kolumny pochodne, utworzone na podstawie obliczeń z innych kolumn w tabeli mając jednakową definicję obliczeń ustalonych na poziomie kolumny wirtualnej zapewniają zawsze spójne dane bez konieczności aktualizacji również tych kolumn, gdy aktualizowane są kolumny bazowe.
- Koszt przechowywania tabeli jest mniejszy, gdyż te kolumny obliczane są podczas odczytu tabeli.
Wady kolumn wirtualnych:
- Brak oficjalnej dokumentacji…
- Dodatkowy koszt przy odczycie danych
- Utrudnione utrzymanie i edycja:
- brak możliwości aktualizacji kolumny w miejscu – należy ją usunąć i dodać spowrotem
- brak obsługi niektórych funkcji przy dodawaniu nowych kolumn wirtualnych – przykładowo nie działa funkcja LEN(), ale zadziała funkcja LENGTH()
Dodaj komentarz