Typy danych w Google BigQuery: Praktyczne wykorzystanie ARRAY i STRUCT

Wyobraźmy sobie typowy scenariusz w firmie e-commerce. Nasze dane o zamówieniach pochodzą bezpośrednio z systemu transakcyjnego lub są strumieniowane jako zdarzenia w formacie JSON. Każde zamówienie to nie tylko pojedynczy wiersz z ID klienta i datą. To złożony obiekt, który zawiera w sobie listę zakupionych produktów, gdzie każdy produkt ma swoje SKU, cenę i zamówioną liczbę sztuk.

W tradycyjnej, relacyjnej bazie danych SQL, obsługa takiej struktury jest kłopotliwa. Zazwyczaj wymaga stworzenia co najmniej dwóch tabel: orders i order_items, a następnie łączenia ich za pomocą klucza order_id przy każdej analizie. Przy milionach zamówień takie operacje stają się nieefektywne, a zapytania skomplikowane. Model danych w bazie nie odzwierciedla naturalnej, zagnieżdżonej struktury naszych danych źródłowych.

Reasumując, praca z danymi, które nie są "płaskie", jest trudna, a możliwości analityczne ograniczone.

Rozwiązanie: Zagnieżdżone i powtarzające się pola w BigQuery

Rozwiązaniem tego problemu jest wykorzystanie natywnej obsługi zagnieżdżonych i powtarzających się pól w Google BigQuery. Zamiast dzielić dane na wiele tabel, możemy przechowywać całą, złożoną strukturę zamówienia – wraz z pełną listą produktów – w jednym wierszu.

Schematycznie, kluczowe koncepcje do pracy z takimi danymi można podsumować następująco:

W tym artykule pokażemy kolejno:

• Czym są i jak działają typy STRUCT i ARRAY.
• Jak zamodelować złożone dane (np. zamówienia) przy ich użyciu.
• Jak analizować dane zagnieżdżone za pomocą funkcji UNNEST.

Jak to działa w praktyce? Dwa kluczowe typy danych

Aby zamodelować nasze zamówienie, użyjemy kombinacji dwóch typów:

1. STRUCT: Grupowanie powiązanych danych w obiekty

Typ STRUCT działa jak kontener lub obiekt, który grupuje w sobie różne, ale powiązane ze sobą informacje. W naszym przypadku, każdy pojedynczy produkt w zamówieniu możemy opisać jako STRUCT, który będzie zawierał jego SKU, ilość oraz cenę jednostkową.

Kluczowe cechy STRUCT:

• Działa jak obiekt JSON lub mała tabela wewnątrz komórki.
• Pola wewnątrz struktury mogą mieć różne typy danych (STRING, INT64, FLOAT64 itd.).
• Dostęp do pól odbywa się za pomocą kropki (np. nazwa_struktury.pole).
   

2. ARRAY: Przechowywanie list obiektów

Typ ARRAY to po prostu uporządkowana lista wartości tego samego typu. Co najważniejsze, tym typem może być również STRUCT. Dzięki temu możemy stworzyć listę (tablicę) produktów dla każdego zamówienia.

Kluczowe cechy ARRAY:

• Uporządkowana lista, gdzie kolejność ma znaczenie.
• Wszystkie elementy muszą być tego samego typu (np. ARRAY<STRING> lub, w naszym przypadku, ARRAY<STRUCT<...>>).
   

Przykład: Tabela zamówień w e-commerce

Połączmy teraz oba typy, aby stworzyć tabelę zamówień. Kolumna items będzie tablicą (ARRAY) struktur (STRUCT), przechowującą wszystkie pozycje z danego zamówienia.

WITH Orders AS (
  SELECT
    12345 AS order_id,
    '[email protected]' AS customer_email,
    [
      STRUCT('SKU-TSHIRT-01' AS sku, 2 AS quantity, 79.99 AS unit_price),
      STRUCT('SKU-SOCKS-B' AS sku, 1 AS quantity, 29.99 AS unit_price)
    ] AS items
  UNION ALL
  SELECT
    12346 AS order_id,
    '[email protected]' AS customer_email,
    [
      STRUCT('SKU-HEADPHONES-X' AS sku, 1 AS quantity, 499.00 AS unit_price)
    ] AS items
)
SELECT * FROM Orders;

W rezultacie otrzymujemy czytelną tabelę, gdzie każdy wiersz to kompletne zamówienie.

Praca z danymi zagnieżdżonymi: Funkcja UNNEST

Przechowywanie danych w ten sposób jest wydajne, ale jak je analizować? Jak policzyć łączną wartość sprzedaży dla produktu SKU-TSHIRT-01?

Tutaj z pomocą przychodzi kluczowa funkcja: UNNEST(). "Spłaszcza" ona tablicę, tworząc osobny wiersz dla każdego elementu z tej tablicy. Dzięki temu możemy wykonywać standardowe operacje SQL, takie jak agregacje i złączenia.

Zobaczmy, jak wyciągnąć pozycje z zamówienia o ID 12345:

WITH Orders AS (
  SELECT
    12345 AS order_id,
    '[email protected]' AS customer_email,
    [
      STRUCT('SKU-TSHIRT-01' AS sku, 2 AS quantity, 79.99 AS unit_price),
      STRUCT('SKU-SOCKS-B' AS sku, 1 AS quantity, 29.99 AS unit_price)
    ] AS items
  UNION ALL
  SELECT
    12346 AS order_id,
    '[email protected]' AS customer_email,
    [
      STRUCT('SKU-HEADPHONES-X' AS sku, 1 AS quantity, 499.00 AS unit_price)
    ] AS items
)
-- Używamy UNNEST do "rozpakowania" tablicy items
SELECT
  order_id,
  item.sku,
  item.quantity,
  (item.quantity * item.unit_price) AS total_item_price
FROM
  Orders,
  UNNEST(items) AS item
WHERE order_id = 12345;

Podsumowanie

Opanowanie typów ARRAY i STRUCT jest fundamentalne dla efektywnej pracy z nowoczesnymi zbiorami danych w Google BigQuery. Pozwalają one na tworzenie bardziej naturalnych i wydajnych modeli danych, które lepiej odzwierciedlają strukturę danych źródłowych (np. z API czy systemów NoSQL). Efektem są prostsze zapytania, mniejsza liczba operacji JOIN i często znacznie lepsza wydajność analiz.