Gegevens opnemen met SQL-methoden

7 minuten

SQL-opdrachten bieden een declaratieve benadering van gegevensopname in Azure Databricks. Als u al vertrouwd bent met de SQL-syntaxis, kunt u met deze methoden tabellen maken en vullen zonder procedurele code te schrijven. De drie primaire SQL-opnametechnieken (CREATE TABLE AS SELECT (CTAS)CREATE OR REPLACE TABLE en COPY INTO- elk hebben betrekking op verschillende opnamescenario's, terwijl volledige compatibiliteit met Unity Catalog behouden blijft.

Tabellen maken van query's met CTAS

De CREATE TABLE AS SELECT instructie combineert het maken van tabellen en gegevenspopulatie tot één bewerking. U definieert een nieuwe tabel op basis van de resultaten van een SELECT query, waardoor deze ideaal is voor het transformeren van gegevens tijdens opname.

Overweeg een scenario waarin u klantgegevens uit een externe bron moet opnemen en transformaties moet toepassen. Met CTAS schrijft u een query die uit de bron wordt gelezen, past u uw transformaties toe en slaat u de resultaten op in een nieuwe beheerde tabel:

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

Het tabelschema wordt automatisch afgeleid van de queryresultaten. Azure Databricks maakt de tabel standaard met de Delta-indeling, die ACID-transacties, tijdreizen en geoptimaliseerde prestaties biedt.

CTAS werkt goed voor het laden van initiële gegevens en eenmalige migraties. Wanneer u gegevens uit bestanden wilt lezen, combineert u CTAS met de read_files tabelwaardefunctie:

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

Opmerking

CTAS maakt telkens wanneer deze wordt uitgevoerd een nieuwe tabel. Als de tabel al bestaat, mislukt de opdracht, tenzij u de IF NOT EXISTS component gebruikt, maar die component slaat de uitvoering volledig over in plaats van de tabel bij te werken.

Tabellen vernieuwen met CREATE OR REPLACE TABLE

Wanneer u de inhoud van een tabel volledig moet vernieuwen, CREATE OR REPLACE TABLE biedt u een schone oplossing. Met deze opdracht maakt u een nieuwe tabel of vervangt u een bestaande volledig, terwijl u de tabelgeschiedenis, verleende bevoegdheden en eventuele rijfilters of kolommaskers die u hebt geconfigureerd behoudt.

Deze methode is waardevol voor periodieke gegevensvernieuwing waarbij u alle bestaande gegevens wilt vervangen:

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;

In tegenstelling tot het verwijderen en opnieuw maken van een tabel, CREATE OR REPLACE blijven de metagegevens en machtigingen van de tabel behouden. Downstreamgebruikers en -toepassingen blijven toegang krijgen tot de tabel zonder opnieuw te configureren.

U kunt deze opdracht ook gebruiken om gegevens rechtstreeks vanuit bestanden te laden. Het schema wordt automatisch afgeleid van de queryresultaten:

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

Belangrijk

CREATE OR REPLACE voert een volledige tabelvervanging uit. Voor incrementele updates waarbij u alleen nieuwe records wilt toevoegen, gebruikt COPY INTO u in plaats daarvan.

Bestanden incrementeel laden met COPY INTO

COPY INTO behandelt een veelvoorkomende opnamevraag: bestanden laden vanuit cloudopslag op een betrouwbare, herhaalbare manier. In tegenstelling tot CTAS, die eenmaal wordt uitgevoerd en een tabel maakt, COPY INTO is ontworpen voor doorlopende opnamewerkstromen waar nieuwe bestanden regelmatig binnenkomen.

Met de opdracht worden bestanden van een opgegeven locatie gelezen en toegevoegd aan een bestaande Delta-tabel. De belangrijkste functie is idempotentie: bestanden die al zijn geladen, worden automatisch overgeslagen, zelfs bij meerdere uitvoeringen:

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

Voordat u COPY INTO uitvoert, moet de doeltabel al bestaan. Maak het met het juiste schema:

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

Bestandsselectie configureren

Wanneer uw bronmap bestanden bevat met verschillende naamgevingspatronen of als u specifieke bestanden wilt laden, gebruikt u de PATTERN of FILES opties:

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

Schema- en gegevenskwaliteit beheren

COPY INTO biedt opties voor het afhandelen van schemawijzigingen en het valideren van gegevens voordat u laadt:

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

Met de mergeSchema optie kan het tabelschema zich ontwikkelen naarmate nieuwe kolommen worden weergegeven in bronbestanden. Voor het valideren van gegevens zonder deze te laden, voegt u de CLAUSULE VALIDATE toe:

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

Met deze validatie wordt gecontroleerd of gegevens kunnen worden geparseerd, overeenkomen met het tabelschema en voldoen aan de nullbaarheid en controlebeperkingen.

De juiste methode kiezen

Elke SQL-opnamemethode dient verschillende doeleinden in uw data engineering-werkstromen:

Methode	Ideaal voor	Gedrag
CTAS	Eerste gegevens worden geladen, eenmalige migraties, het maken van tabellen op basis van query's	Hiermee maakt u een nieuwe tabel; mislukt als de tabel bestaat
MAKEN OF VERVANGEN	Periodieke volledige vernieuwingen, waarbij faseringstabellen worden vervangen	Vervangt de hele tabel; machtigingen behouden
KOPIËREN NAAR	Doorlopende bestandsopname, incrementele belastingen	Toevoegt aan bestaande tabel; slaat geladen bestanden over

Voor opname op basis van bestanden waarvoor automatische schemadeductie, bestandsmeldingen of exactly-oncegaranties zijn vereist, kunt u overwegen autolader te gebruiken als een complementaire benadering. Wanneer uw opnamebehoeften eenvoudig zijn en u de voorkeur geeft aan declaratieve SQL boven procedurele code, bieden deze drie methoden een volledige toolkit voor het beheren van de gegevensstroom in Unity Catalog.

Opmerking

COPY INTO werkt goed bij het verwerken van bestanden in omvang van duizenden over tijd. Voor bronnen die zullen groeien tot miljoenen bestanden of meer, is Auto Loader (behandeld in de volgende eenheid) de aanbevolen benadering: het detecteert bestanden efficiënter, ondersteunt uitgebreidere schemaontwikkeling en schaalt zonder de overhead van directoryvermelding. Databricks raadt ook streamingtabellen aan die worden ondersteund door Auto Loader als een schaalbaar, langetermijn alternatief voor bestandsopname op basis van SQL.

Feedback

Is deze pagina nuttig?