Inserire dati con metodi SQL

7 minuti

I comandi SQL offrono un approccio dichiarativo all'inserimento dati in Azure Databricks. Se si ha già familiarità con la sintassi SQL, questi metodi consentono di creare e popolare tabelle senza scrivere codice procedurale. Le tre tecniche di inserimento SQL principali,CREATE TABLE AS SELECT (CTAS) , CREATE OR REPLACE TABLEe COPY INTO, consentono di gestire scenari di inserimento diversi mantenendo al tempo stesso la compatibilità completa con il catalogo unity.

Creare tabelle da query con CTAS

L'istruzione CREATE TABLE AS SELECT combina la creazione di tabelle e il popolamento dei dati in un'unica operazione. Si definisce una nuova tabella in base ai risultati di una SELECT query, rendendola ideale per la trasformazione dei dati durante l'inserimento.

Si consideri uno scenario in cui è necessario inserire i dati dei clienti da un'origine esterna e applicare trasformazioni. Con CTAS si scrive una query che legge dall'origine, applica le trasformazioni e salva i risultati in una nuova tabella gestita:

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';

Lo schema della tabella viene derivato automaticamente dai risultati della query. Azure Databricks crea la tabella usando il formato Delta per impostazione predefinita, che offre transazioni ACID, spostamenti temporali e prestazioni ottimizzate.

CTAS funziona bene per i caricamenti iniziali dei dati e le migrazioni monouso. Quando è necessario leggere i dati dai file, combinare CTAS con la read_files funzione con valori di tabella:

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

Annotazioni

CTAS crea una nuova tabella ogni volta che viene eseguita. Se la tabella esiste già, il comando non riesce a meno che non si usi la IF NOT EXISTS clausola , ma tale clausola ignora completamente l'esecuzione anziché aggiornare la tabella.

Aggiornare le tabelle con CREATE OR REPLACE TABLE

Quando è necessario aggiornare completamente il contenuto di un sommario, CREATE OR REPLACE TABLE fornisce una soluzione pulita. Questo comando crea una nuova tabella o sostituisce completamente una tabella esistente, conservando la cronologia delle tabelle, concedendo privilegi ed eventuali filtri di riga o maschere di colonna configurate.

Questo approccio è utile per gli aggiornamenti periodici dei dati in cui si vogliono sostituire tutti i dati esistenti:

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;

A differenza dell'eliminazione e della ricreazione di una tabella, CREATE OR REPLACE mantiene i metadati e le autorizzazioni della tabella. Gli utenti downstream e le applicazioni continuano ad accedere alla tabella senza riconfigurazione.

È anche possibile usare questo comando per caricare i dati direttamente dai file. Lo schema viene dedotto automaticamente dai risultati della query:

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

Importante

CREATE OR REPLACE esegue una sostituzione completa della tabella. Per gli aggiornamenti incrementali in cui si vogliono aggiungere solo nuovi record, usare COPY INTO invece .

Caricare i file in modo incrementale con COPY INTO

COPY INTO risolve una sfida di inserimento comune: il caricamento di file dall'archiviazione cloud in modo affidabile e ripetibile. A differenza di CTAS, che viene eseguito una sola volta e crea una tabella, COPY INTO è progettato per flussi di lavoro di inserimento in corso in cui arrivano regolarmente nuovi file.

Il comando legge i file da un percorso specificato e li aggiunge a una tabella Delta esistente. La sua funzionalità chiave è idempotency: i file che sono già stati caricati vengono ignorati automaticamente, anche in più esecuzioni:

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

Prima di eseguire COPY INTO, la tabella di destinazione deve esistere già. Crealo con lo schema appropriato.

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

Configurare la selezione dei file

Quando la directory di origine contiene file con modelli di denominazione diversi o è necessario caricare file specifici, usare le PATTERN opzioni o 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');

Gestire lo schema e la qualità dei dati

COPY INTO offre opzioni per la gestione delle modifiche dello schema e la convalida dei dati prima del caricamento:

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

L'opzione mergeSchema consente allo schema di tabella di evolversi man mano che vengono visualizzate nuove colonne nei file di origine. Per convalidare i dati senza caricarli, aggiungere la clausola VALIDATE:

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

Questa convalida controlla se i dati possono essere analizzati, corrispondono allo schema della tabella e soddisfano i vincoli di nullabilità e di controllo.

Scegliere il metodo corretto

Ogni metodo di inserimento SQL svolge scopi distinti nei flussi di lavoro di progettazione dei dati:

Metodo	Ideale per	Comportamento
CTAS	Caricamenti iniziali dei dati, migrazioni monouso, creazione di tabelle da query	Crea una nuova tabella; fallisce se la tabella esiste
CREARE O SOSTITUIRE	Aggiornamenti completi periodici, sostituzione delle tabelle di staging	Sostituisce l'intera tabella; mantiene le autorizzazioni
COPIARE IN	Inserimento di file in corso, caricamenti incrementali	Aggiunge alla tabella esistente; ignora i file caricati

Per l'inserimento basato su file che richiede l'inferenza automatica dello schema, le notifiche di file o le garanzie di tipo exactly-once, valutare l'uso di Auto Loader come approccio complementare. Quando le esigenze di inserimento sono semplici e si preferisce codice SQL dichiarativo rispetto al codice procedurale, questi tre metodi forniscono un toolkit completo per la gestione del flusso di dati nel catalogo Unity.

Annotazioni

COPY INTO funziona bene quando si inseriscono file nell'ordine di migliaia nel tempo. Per le origini che cresceranno fino a milioni di file o più, il caricatore automatico (illustrato nell'unità successiva) è l'approccio consigliato, individua i file in modo più efficiente, supporta l'evoluzione dello schema più avanzata e si ridimensiona senza il sovraccarico dell'elenco delle directory. Databricks consiglia anche le tabelle di streaming supportate dal caricatore automatico come alternativa scalabile e a lungo termine per l'inserimento di file basato su SQL.

Commenti e suggerimenti

Questa pagina è stata utile?