Pozyskiwanie danych za pomocą metod SQL

Ukończone

Wskazówka

Aby uzyskać więcej szczegółów, zobacz kartę Tekst i obrazy .

Polecenia SQL zapewniają deklaratywne podejście do pozyskiwania danych w usłudze Azure Databricks. Jeśli masz już doświadczenie ze składnią SQL, te metody umożliwiają tworzenie i wypełnianie tabel bez konieczności pisania kodu proceduralnych. Trzy podstawowe techniki pobierania SQL—CREATE TABLE AS SELECT (CTAS), CREATE OR REPLACE TABLE i COPY INTO—każda z nich dotyczy innego scenariusza pobierania przy zachowaniu pełnej zgodności z Unity Catalog.

Tworzenie tabel na podstawie zapytań za pomocą usługi CTAS

Instrukcja CREATE TABLE AS SELECT łączy tworzenie tabel i populację danych w jedną operację. Możesz zdefiniować nową tabelę na podstawie wyników SELECT zapytania, co czyni ją idealną do przekształcania danych w trakcie pobierania.

Rozważmy scenariusz, w którym należy pozyskiwać dane klientów z zewnętrznego źródła i stosować przekształcenia. W przypadku funkcji CTAS piszesz zapytanie, które odczytuje ze źródła, stosuje przekształcenia i zapisuje wyniki w nowej tabeli zarządzanej:

CREATE TABLE catalog.schema.customers AS
SELECT 
    customer_id,
    UPPER(customer_name) AS customer_name,
    email,
    created_date
FROM external_staging.raw_customers
WHERE customer_status = 'active';

Schemat tabeli jest automatycznie uzyskiwany z wyników zapytania. Usługa Azure Databricks domyślnie tworzy tabelę przy użyciu formatu delta, który zapewnia transakcje ACID, podróże czasowe i zoptymalizowaną wydajność.

Funkcja CTAS dobrze sprawdza się w przypadku początkowych obciążeń danych i jednorazowych migracji. Gdy musisz odczytać dane z plików, połącz funkcję CTAS z funkcją read_files wartości tabeli:

CREATE TABLE catalog.schema.sales_data AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/sales/*.parquet',
    format => 'parquet'
);

Uwaga / Notatka

Funkcja CTAS tworzy nową tabelę za każdym razem, gdy jest uruchamiana. Jeśli tabela już istnieje, polecenie zakończy się niepowodzeniem, chyba że zostanie użyta IF NOT EXISTS klauzula — lecz ta klauzula nie wykonuje się, zamiast tego pomija aktualizację tabeli.

Odświeżanie tabel za pomocą polecenia CREATE OR REPLACE TABLE

Jeśli musisz w pełni odświeżyć zawartość tabeli, CREATE OR REPLACE TABLE zapewnia czyste rozwiązanie. To polecenie tworzy nową tabelę lub całkowicie zastępuje istniejącą, zachowując historię tabel, przyznane uprawnienia i wszystkie skonfigurowane filtry wierszy lub maski kolumn.

Takie podejście jest przydatne w przypadku okresowych odświeżeń danych, w których chcesz zastąpić wszystkie istniejące dane:

CREATE OR REPLACE TABLE catalog.schema.daily_metrics AS
SELECT 
    report_date,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.transactions
WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY report_date;

W przeciwieństwie do upuszczania i ponownego tworzenia tabeli, CREATE OR REPLACE zachowuje metadane i uprawnienia tabeli. Użytkownicy podrzędni i aplikacje nadal uzyskują dostęp do tabeli bez ponownej konfiguracji.

Za pomocą tego polecenia można również załadować dane bezpośrednio z plików. Schemat jest automatycznie wnioskowany z wyników zapytania:

CREATE OR REPLACE TABLE catalog.schema.products AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/products.csv',
    format => 'csv',
    header => true
);

Ważne

CREATE OR REPLACE Przeprowadza pełne zastąpienie tabeli. W przypadku aktualizacji przyrostowych, w których chcesz dodać tylko nowe rekordy, użyj COPY INTO zamiast tego.

Przyrostowe ładowanie plików za pomocą funkcji COPY INTO

COPY INTO rozwiązuje typowe wyzwanie dotyczące wczytywania: ładowanie plików z magazynu w chmurze w niezawodny, powtarzalny sposób. W przeciwieństwie do usługi CTAS, która jest uruchamiana raz i tworzy tabelę, COPY INTO jest przeznaczona do bieżących przepływów pracy pozyskiwania danych, w których nowe pliki są regularnie dostarczane.

Polecenie odczytuje pliki z określonej lokalizacji i dołącza je do istniejącej tabeli delty. Jego kluczową funkcją jest idempotencyjność — pliki, które zostały już załadowane, są automatycznie pomijane, nawet w wielu wykonaniach:

COPY INTO catalog.schema.events
FROM '/Volumes/catalog/schema/volume/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiline' = 'true');

Przed uruchomieniem COPY INTO tabela docelowa musi już istnieć. Utwórz go przy użyciu odpowiedniego schematu:

CREATE TABLE IF NOT EXISTS catalog.schema.events (
    event_id STRING,
    event_type STRING,
    event_timestamp TIMESTAMP,
    payload STRING
);

Konfigurowanie wyboru pliku

Jeśli katalog źródłowy zawiera pliki z różnymi wzorcami nazewnictwa lub musisz załadować określone pliki, użyj PATTERN opcji lub FILES :

-- Load only files matching a pattern
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
PATTERN = 'orders_2024*.parquet';

-- Load specific files by name
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
FILES = ('orders_001.parquet', 'orders_002.parquet');

Obsługa schematu i jakości danych

COPY INTO Udostępnia opcje obsługi zmian schematu i weryfikowania danych przed załadowaniem:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
    'header' = 'true',
    'inferSchema' = 'true'
)
COPY_OPTIONS ('mergeSchema' = 'true');

Opcja mergeSchema umożliwia rozwijanie schematu tabeli w miarę pojawiania się nowych kolumn w plikach źródłowych. Aby zweryfikować dane bez ich ładowania, dodaj klauzulę VALIDATE:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
VALIDATE ALL;

Ta walidacja sprawdza, czy dane mogą być analizowane, pasują do schematu tabeli i spełniają wymagania dotyczące wartości null oraz ograniczeń kontrolnych.

Wybieranie właściwej metody

Każda metoda pozyskiwania SQL służy do odrębnych celów w przepływach pracy inżynierii danych:

Metoda	Najlepsze dla	Zachowanie
CTAS	Początkowe ładowanie danych, jednorazowe migracje, tworzenie tabel na podstawie zapytań	Tworzy nową tabelę; niepowodzi się, jeśli tabela istnieje
TWORZENIE LUB ZASTĘPOWANIE	Okresowe pełne odświeżanie, zastępując tabele przejściowe	Zamienia całą tabelę; zachowuje uprawnienia
KOPIUJ DO	Ciągłe pozyskiwanie plików, przyrostowe obciążenia	Dołącza do istniejącej tabeli; pomija załadowane pliki

W przypadku pozyskiwania opartego na plikach, które wymaga automatycznego wnioskowania schematu, powiadomień o plikach lub dokładnie jednokrotnych gwarancji, rozważ użycie automatycznego modułu ładującego jako uzupełniające podejście. Gdy potrzeby pozyskiwania danych są proste i wolisz deklaratywne SQL od kodu proceduralnego, te trzy metody zapewniają kompletny zestaw narzędzi do zarządzania przepływem danych do Unity Catalog.

Uwaga / Notatka

COPY INTO działa dobrze podczas pozyskiwania plików w ilości dochodzącej do tysięcy w skali czasu. W przypadku źródeł, które będą rosnąć do milionów plików lub więcej, automatyczne ładowanie (omówione w następnej lekcji) jest zalecanym podejściem — odnajduje pliki wydajniej, obsługuje bardziej zaawansowaną ewolucję schematu i skaluje bez konieczności wprowadzania listy katalogów. Databricks również zaleca tabele strumieniowe wsparte przez Auto Loader jako skalowalną, długoterminową alternatywę dla pozyskiwania plików opartych na języku SQL.