Delen via

Gegevens opnemen in uw warehouse met Behulp van Transact-SQL

  • Artikel

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 behulp van CREATE TABLE AS SELECT (CTAS)

Met de instructie CREATE TABLE AS SELECT (CTAS) kunt u een nieuwe tabel in uw magazijn maken op basis van de uitvoer van een SELECT-instructie. De opnamebewerking wordt parallel uitgevoerd 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-tabel die automatisch is gegenereerd via een SQL Analytics-eindpunt voor Lakehouse.
  • Gegevens rechtstreeks vanuit een extern bestand lezen met behulp van de functie OPENROWSET (preview).

Notitie

In de voorbeelden in dit artikel wordt gebruikgemaakt van de voorbeeldgegevensset bing COVID-19. Als u de voorbeeldgegevensset wilt laden, volgt u de stappen voor het opnemen van gegevens in uw magazijn met behulp van de COPY-instructie om de voorbeeldgegevens in uw magazijn te maken.

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

CREATE TABLE [dbo].[bing_covid-19_data_2023]
AS
SELECT * 
FROM [dbo].[bing_covid-19_data] 
WHERE DATEPART(YEAR,[updated]) = '2023';

In plaats van gegevens uit de faseringstabel [bing_covid-19_data] te lezen, kunt u ook rechtstreeks vanuit een extern bestand een nieuwe tabel maken met behulp van de functie OPENROWSET (openbare preview):

CREATE TABLE [dbo].[bing_covid-19_data_2022]
AS
SELECT id, updated, confirmed, deaths, recovered, latitude, longitude, iso2, iso3, country_region
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 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_covid-19_data_with_year_month_day]
AS
SELECT DATEPART(YEAR,[updated]) [year], DATEPART(MONTH,[updated]) [month], DATEPART(DAY,[updated]) [dayofmonth], * 
FROM [dbo].[bing_covid-19_data];

In plaats van gegevens uit de faseringstabel [bing_covid-19_data] te lezen, kunt u ook rechtstreeks vanuit een extern bestand een nieuwe tabel maken en resultaten transformeren:

CREATE TABLE [dbo].[bing_covid-19_data_with_year_month_day]
AS
SELECT DATEPART(YEAR,[updated]) [year], DATEPART(MONTH,[updated]) [month], DATEPART(DAY,[updated]) [dayofmonth],
        id, confirmed, deaths, recovered, latitude, longitude, iso2, iso3, country_region
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

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)) [confirmed_sum]
FROM [dbo].[bing_covid-19_data_with_year_month_day]
GROUP BY [country_region],[month];

CREATE TABLE [dbo].[infections_by_month_2022]
AS
SELECT [country_region], DATEPART(MONTH,[updated]) AS [month], SUM(CAST(confirmed as bigint)) [confirmed_sum]
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'
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 van January, 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;

Schermopname van de queryresultaten met het aantal infecties per maand in de Verenigde Staten, gesorteerd op maand, in aflopende volgorde. Het maandnummer 1 wordt bovenaan weergegeven.

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, gebruikt u INSERT... SELECT-patroon kan worden gebruikt. Met de volgende code worden bijvoorbeeld nieuwe gegevens opgenomen in een bestaande tabel:

INSERT INTO [dbo].[bing_covid-19_data_2023]
SELECT * FROM [dbo].[bing_covid-19_data] 
WHERE [updated] > '2023-02-28';

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. Zie INSERT INTO gebruiken voor meer informatie ... SELECT om gegevens bulksgewijs te importeren met minimale logboekregistratie en parallelle uitvoering.

Gegevens opnemen uit tabellen in verschillende magazijnen en lakehouses

Voor ZOWEL CREATE TABLE AS SELECT als INSERT... SELECT, de SELECT instructie kan 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 werkruimteactiva hebt:

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

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_covid-19_data] cases
INNER JOIN [reference_warehouse].[dbo].[bing_covid-19_data] reference
ON cases.[iso3] = reference.[countrycode];

Zie Een SQL-query voor meerdere databases schrijven voor meer informatie over query's tussen magazijnen.

Gerelateerde inhoud