Gegevens opnemen in uw warehouse met Behulp van Transact-SQL

Van toepassing op:✅ Warehouse in Microsoft Fabric

De Transact-SQL-taal biedt opties die u kunt gebruiken om gegevens op schaal te laden vanuit bestaande tabellen in uw lakehouse en magazijn in nieuwe tabellen in uw magazijn. Deze opties zijn handig als u nieuwe versies van een tabel wilt maken met geaggregeerde gegevens, versies van tabellen met een subset van de rijen of om een tabel te maken als gevolg van een complexe query. Laten we enkele voorbeelden bekijken.

Een nieuwe tabel maken met het resultaat van een query

Met Warehouse in Microsoft Fabric kunt u eenvoudig een nieuwe tabel maken op basis van een resultaat van T-SQL-query met behulp van de volgende T-SQL-instructies:

• CREATE TABLE AS SELECT (CTAS)-instructie waarmee u een nieuwe tabel in uw magazijn kunt maken op basis van de uitvoer van een SELECT instructie.
• SELECT INTO querycomponent waarmee u resultaten uit een tabelbron kunt selecteren en de resultaten omleidt naar een nieuwe tabel. Dit is een standaardfunctie in de T-SQL-taal.

Deze twee instructies zijn vergelijkbaar, dus de volgende voorbeelden zijn gericht op de CTAS-instructie.

De CTAS-instructie voert de opnamebewerking parallel uit in de nieuwe tabel, waardoor deze zeer efficiënt is voor gegevenstransformatie en het maken van nieuwe tabellen in uw werkruimte.

U kunt de volgende opties gebruiken voor het SELECT deel van de CTAS-statement:

• Een magazijntabel lezen, zoals een faseringstabel.
• Het lezen van een Lakehouse Delta Lake-directory met behulp van een automatisch gegenereerde tabel binnen het SQL Analytics-eindpunt voor Lakehouse.
• CSV-, Parquet- of JSONL-bestanden rechtstreeks vanuit Azure Data Lake of Azure Blob Storage lezen met behulp van de OPENROWSET functie.

Tabel maken vanuit magazijntabel

In het eerste voorbeeld ziet u hoe u een nieuwe tabel maakt die een kopie is van de bestaande dbo.bing_covid19_data_2023 tabel, maar alleen is gefilterd op gegevens uit het jaar 2023:

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM dbo.bing_covid19_data 
WHERE DATEPART(YEAR, updated) = '2023';

U kunt ook een nieuwe tabel maken met nieuwe year, monthdayofmonth kolommen, met waarden die zijn verkregen uit updated de kolom in de brontabel. Dit kan handig zijn als u infectiegegevens per jaar probeert te visualiseren of maanden wilt zien wanneer de meeste COVID-19-gevallen worden waargenomen:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year],
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       * 
FROM dbo.bing_covid19_data;

In een ander voorbeeld kunt u een nieuwe tabel maken met een overzicht van het aantal gevallen dat in elke maand wordt waargenomen, ongeacht het jaar, om te evalueren hoe seizoensgebondenheid van invloed is op de verspreiding in een bepaald land/bepaalde regio. Hierbij wordt de tabel gebruikt die in het vorige voorbeeld is gemaakt met de nieuwe month kolom als bron:

CREATE TABLE dbo.infections_by_month
AS
SELECT country_region, [month],
       SUM(CAST(confirmed as bigint)) AS confirmed_sum
FROM dbo.bing_covid19_data_with_year_month_day
GROUP BY country_region, [month];

Op basis van deze nieuwe tabel kunnen we zien dat de Verenigde Staten in alle jaren in de maand January meer bevestigde gevallen hebben waargenomen, gevolgd door December en October. April is de maand met het laagste aantal gevallen in het algemeen:

SELECT * FROM dbo.infections_by_month
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Tabel maken vanuit de map Delta Lake

De Delta Lake-mappen die in OneLake worden bewaard, worden automatisch weergegeven als tabellen als ze zijn opgeslagen in de map /Tables in een lakehouse. Met de volgende code maakt u een nieuwe tabel bing_covid19_data_2023 uit de map Delta Lake /Tables/bing_covid19_delta_lake in het Lakehouse MyLakehouse :

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

U kunt verwijzen naar de map Delta Lake met behulp van de driedelige naam notatie die verwijst naar het lakehouse waar de bestanden zijn opgeslagen. Alle voorbeelden die in de vorige sectie worden weergegeven, zijn van toepassing op Delta Lake-mappen.

Tabel maken vanuit CSV-/Parquet-/JSONL-bestand

In plaats van gegevens uit de tabel Warehouse bing_covid19_data te lezen, kunt u ook rechtstreeks vanuit een extern bestand een nieuwe tabel maken met behulp van de OPENROWSET functie:

CREATE TABLE dbo.bing_covid19_data_2022
AS
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2022';

U kunt ook een nieuwe tabel maken door gegevens te transformeren vanuit een extern CSV-bestand:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year], 
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv') AS data;

In een ander voorbeeld kunt u een nieuwe tabel maken met een overzicht van het aantal gevallen dat in elke maand wordt waargenomen, ongeacht het jaar, om te evalueren hoe seizoensgebondenheid van invloed is op de verspreiding in een bepaald land/bepaalde regio. Hierbij wordt de tabel gebruikt die in het vorige voorbeeld is gemaakt met de nieuwe month kolom als bron:

CREATE TABLE dbo.infections_by_month_2022
AS
SELECT country_region,
       DATEPART(MONTH, updated) AS [month],
       SUM(CAST(confirmed as bigint)) AS [confirmed_sum]
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') AS data
WHERE DATEPART(YEAR, updated) = '2022'
GROUP BY country_region, DATEPART(MONTH, updated);

Op basis van deze nieuwe tabel kunnen we zien dat de Verenigde Staten in alle jaren in de maand January meer bevestigde gevallen hebben waargenomen, gevolgd door December en October. April is de maand met het laagste aantal gevallen in het algemeen:

SELECT * FROM dbo.infections_by_month_2022
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Zie CREATE TABLE AS SELECT (Transact-SQL) voor meer voorbeelden en syntaxisverwijzing.

Gegevens opnemen in bestaande tabellen met T-SQL-query's

In de vorige voorbeelden worden nieuwe tabellen gemaakt op basis van het resultaat van een query. Als u de voorbeelden wilt repliceren, maar in bestaande tabellen, kan het INSERT ... SELECT patroon worden gebruikt.

Gegevens opnemen uit de magazijntabel

Met de volgende code worden nieuwe gegevens uit een magazijntabel opgenomen in een bestaande tabel:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM dbo.bing_covid19_data
WHERE DATEPART(YEAR, updated) = '2023';

De querycriteria voor de SELECT instructie kunnen elke geldige query zijn, zolang de resulterende querykolomtypen overeenkomen met de kolommen in de doeltabel. Als kolomnamen zijn opgegeven en alleen een subset van de kolommen uit de doeltabel bevatten, worden alle andere kolommen geladen als NULL. Voor meer informatie, zie Gebruik van INSERT INTO...SELECT om gegevens bulkgewijs te importeren met minimale logboekregistratie en parallelisme.

Gegevens opnemen uit de map Delta Lake

De Delta Lake-mappen die in OneLake worden bewaard, worden automatisch weergegeven als tabellen als ze zijn opgeslagen in /Tables een map in een lakehouse.

De volgende code neemt nieuwe gegevens op uit de Delta Lake-map /Tables/bing_covid19_delta_lake in het lakehouse MyLakehouse*.

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Gegevens opnemen uit CSV-/Parquet-/JSONL-bestand

U kunt de OPENROWSET functie als bron gebruiken om Parquet-, CSV- of JSON-bestanden op te nemen uit de opslag:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2023';

U kunt meerdere bestanden lezen met wildcards zoals *.parquet, of door zich te richten op gepartitioneerde mappen zoals /year=*/month=*. Als u de prestaties wilt optimaliseren, past u filters toe in de WHERE-component om onnodige rijen en partities te elimineren tijdens het uitvoeren van query's.

Deze voorbeelden zijn vergelijkbaar met die gebruikt in opname met COPY INTO. De opdracht COPY INTO is eenvoudiger te gebruiken, met name voor eenvoudige bron-naar-doelgegevensbelastingen. Als u echter brongegevens wilt transformeren (zoals het converteren van waarden of het samenvoegen met andere tabellen), geeft INSERT ... SELECT u de flexibiliteit om transformaties uit te voeren tijdens de invoer.

Gegevens opnemen uit OneLake

U kunt de OPENROWSET functie als bron gebruiken om gegevens op te nemen uit Fabric OneLake-opslag. Vervang {workspaceId} en {lakehouseId} door de bijbehorende werkruimte- en lakehouse-GUID's in het volgende voorbeeld:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://onelake.dfs.fabric.microsoft.com/{workspaceId}/{lakehouseId}/Files/year=*/month=*/*.parquet') AS data
WHERE data.filepath(1) = '2023'

Dit voorbeeld is gebaseerd op het vorige voorbeeld dat gegevens leest uit Azure Data Lake Storage. Gebruik deze methode wanneer u brongegevens wilt transformeren, bijvoorbeeld het converteren van waarden, samenvoegen met andere tabellen of het lezen van specifieke partities. In dergelijke gevallen biedt het gebruik INSERT ... SELECT van de flexibiliteit om transformaties toe te passen tijdens gegevensopname.

Gegevens opnemen uit tabellen in verschillende magazijnen en lakehouses

Voor beide CREATE TABLE AS SELECT en INSERT ... SELECTkan de SELECT instructie ook verwijzen naar tabellen in magazijnen die verschillen van het magazijn waar uw doeltabel wordt opgeslagen, met behulp van query's voor meerdere magazijnen. Dit kan worden bereikt met behulp van de driedelige naamconventie [warehouse_or_lakehouse_name.][schema_name.]table_name. Stel dat u de volgende werkruimte-elementen hebt:

• Een lakehouse genaamd cases_lakehouse met de meest recente casegegevens.
• Een magazijn genaamd reference_warehouse met tabellen die worden gebruikt voor referentiegegevens.
• Een magazijn met de naam research_warehouse waar de doeltabel wordt aangemaakt.

Er kan een nieuwe tabel worden gemaakt die gebruikmaakt van driedelige naamgeving om gegevens uit tabellen op deze werkruimteassets te combineren:

CREATE TABLE research_warehouse.dbo.cases_by_continent
AS
SELECT *
FROM cases_lakehouse.dbo.bing_covid19_data AS cases
INNER JOIN reference_warehouse.dbo.bing_covid19_data AS reference
ON cases.iso3 = reference.countrycode;

Voor meer informatie over query's over magazijnen heen, zie Een database-overschrijdende SQL-query schrijven.

T-SQL-opname controleren en bewaken

Zowel CTAS als INSERT ... SELECT bewerkingen die via T-SQL worden uitgevoerd, worden weergegeven in de querygeschiedenis en -activiteit van het magazijn en kunnen naast andere magazijnbewerkingen worden bewaakt.

Opties voor gegevensopname

Andere manieren om gegevens op te nemen in uw magazijn zijn:

• Gegevens opnemen met behulp van de COPY-instructie
• Gegevens opnemen met behulp van pijplijnen
• Gegevens opnemen met behulp van een gegevensstroom

Verwante inhoud

• Gegevens opnemen in het magazijn
• Een SQL-query voor meerdere databases schrijven