Czym są wirtualne kolumny w Snowflake?

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()