Rafał Gogłoza

Tło wydarzeń:

Masz tabelę stworzoną w Databricks. Klucz główny ustawiony jest jako kolumna identity. Dzięki temu masz obsłużone wstawianie unikalnych wartości. Nie musisz też tego robić samodzielnie, co tworzy miejsce na pominięcie czegoś. Jednak pojawia się wyzwanie. Twoja "sekwencja" zaczynała wstawianie danych od jedności a teraz chciałbyś wstawić singletony (-1, -2).

Problem:

Chciałbyś wstawić singletony do tabeli, gdzie jedna z kolumn jest typu Identity. W jaki sposób wstawić do niej oczekiwane wartości?

Rozwiązanie:

Zdefiniuj kolumnę klucza głównego jako:

generated by default as identity

Ale uważaj, jest jeden przypadek, gdy tak zdefiniowana kolumna może sprawić problemy.

Generte always or by default as indentity?

Zdefiniujmy prostą tabelę z walutami, która zawiera klucz główny (currency_tk) generowany utomatycznie, gdy wstawiany nowy rekord.

create or replace table next_level_dm.d_currency (
  currency_tk bigint generated always as identity,
  currency_code string
);

insert into trsales_dm_plab.d_currency (currency_tk, currency_code) values (-1, 'UNK')

Próba wstawienia singletonów i ustawienia wartości do dla kolumny currency_tk zakończy się takim błędem:

AnalysisException: Providing values for GENERATED ALWAYS AS IDENTITY column currency_tk is not supported.

Zdefiniujmy tabelę inaczej, zamieńmy always na by default:

create or replace table next_level_dm.d_currency2 (
  currency_tk bigint generated by default as identity,
  currency_code string
);

Wtedy wstawienie rekordu:

insert into trsales_dm_plab.d_currency2 (currency_tk, currency_code) values (-1, 'UNK')

Zakończy się powodzeniem:

generate_by_default_as_identity

Dlaczego to tak działa?

Klauzula always, zawarta w wyrażeniu:

currency_tk bigint generated always as identity,

Sprawia, że zawsze generowana jest wartość w tej kolumnie. Wstawiając dane do tabeli d_currency zrobił byś to w ten sposób.

insert into trsales_dm_plab.d_currency (currency_code) values ('PLN')

Zawsze musisz pominąć kolumnę, którą definiujesz jako identity. Nie ma możliwości samodzielnego ustawienia tej wartości.

Natomiast drugi przypadek definiowania kolumny identity dopuszcza ustawienie wartości klucza przez użytkownika.

Zwróć uwagę

Jest jednak pewien przypadek, gdy przy wykorzystaniu default trzeba być ostrożnym.

Dobra praktyka to nie wstawianie kluczy, które mogą być wykorzystane przez sekwencje.

Czyli zawsze wybieraj wartości z poza sekwencji do wstawienia.

W tym przypadku licznik zaczyna chodzić od jedności i jest incrementowany co 1. Bezpieczne jest wstawianie wartości ujemnych do tabeli, gdyż one nigdy nie będą częścią sekwencji.

Jeżeli sam próbujesz wstawiać wartości, które mogą pokryć się z sekwencją i jednocześnie wykorzystujesz sekwencję możesz skończyć z niepoprawnymi danymi, gdzie jeden klucz główny wstawiony jest wiele razy: