CREATE EXTERNAL TABLE AS SELECT (Tabel maken in een externe database als selecteren, ook wel CETAS genoemd)

  • 8 minuten

In deze les wordt de opdracht CREATE EXTERNAL TABLE AS SELECT (CETAS) en de voordelen ervan nader bekeken.

CETAS ondersteunt Azure Blob Storage, Azure Data Lake Storage, S3-compatibele objectopslag en externe databases. CETAS ondersteunt ook het exporteren van bestandsindelingen als CSV- of Parquet-bestanden.

De krachtigste mogelijkheid van CETAS is om te combineren met andere SELECT-bewerkingen, zoals het combineren met OPENROWSET of met andere externe tabellen. Met deze combinatie kan CETAS op verschillende en flexibele manieren worden gebruikt.

Gebruiksvoorbeelden voor CETAS

Er zijn veel verschillende use cases voor CETAS, maar de drie belangrijkste zijn:

  • Berichtgeving
  • nl-NL: Datalagen of ontladen
  • Hub voor gegevensexport

Berichtgeving

De meest voor de hand liggende use case voor CETAS is het vereenvoudigen van rapportage door gebruik te maken van alle mogelijkheden van PolyBase en SQL Server-flexibiliteit. U kunt verbinding maken met elke ondersteunde gegevensbron, tabel of bestand en T-SQL gebruiken om de resultaten te bewerken en te exporteren. In plaats van rapportagehulpprogramma's te verbinden met SQL Server, kunt u CETAS gebruiken om alle benodigde query's uit te voeren, het resultaat als een Parquet-bestand te exporteren en het rapportagehulpprogramma naar de geëxporteerde gegevens te verwijzen.

nl-NL: Datalagen of ontladen

Een ander veelvoorkomend scenario is het exporteren van archief- of alleen-lezengegevens uit SQL Server-tabellen naar externe opslag, terwijl deze gegevens nog steeds op normale wijze worden bevraagd via externe tabellen. Op deze manier kunt u alleen de actieve gegevens op SQL Server bewaren, resources opslaan, maar toch op transparante wijze toegang krijgen tot de gegevens wanneer dat nodig is. Vanuit het oogpunt van een toepassing is het net als bij het openen van een gewone SQL Server-tabel.

Stel dat uw order tabel historische gegevens bevat van de afgelopen jaren die niet veranderen, maar u moet deze nog steeds volledig toegankelijk hebben. CETAS kan helpen bij het exporteren van alle order tabelgegevens naar een andere locatie, waardoor de databasegrootte en de vereiste onderhoudstijd worden verminderd. Met een externe tabel gedragen de geëxporteerde gegevens zich net als een gewone tabel.

Hub voor gegevensexport

Door SQL Server 2025 als een gegevenshub te gebruiken, kunt u CETAS combineren met alle bestaande PolyBase-mogelijkheden, waaronder verbinding maken met andere gegevensbronnen, zoals Oracle, Teradata, ODBC of andere versies van SQL Server.

CETAS-vereisten

Als u CETAS in SQL Server 2025 wilt gebruiken, moet u de eigenschap ALLOW POLYBASE EXPORT inschakelen met behulp van sp_configure. Zie de volgende oefening voor meer informatie.

CETAS-machtigingen

Als u CETAS wilt gebruiken, hebt u drie verschillende machtigingsniveaus nodig:

  • Machtiging voor toegang tot en het lezen van de gegevensbron. Als de gegevens zich buiten SQL Server bevinden, op een netwerkshare of een andere databaseserver, moet het SQL Server-serviceaccount gemachtigd zijn voor toegang tot de gegevensbron.

  • Machtiging voor SQL Server 2025 voor het gebruik van de CETAS-opdracht. Als de databasegebruiker de CETAS-opdracht wil uitvoeren, hebben zij de machtigingen ADMINISTER BULK OPERATIONS, ALTER ANY EXTERNAL DATA SOURCE, en ALTER ANY EXTERNAL FILE FORMAT nodig.

  • Schrijfrechten op de bestemming om de CETAS-resultaten te kunnen opslaan. Om naar de bestemming, Parquet- of CSV-bestand te kunnen schrijven, heeft de gebruiker schrijfmachtigingen op de bestemming nodig. Als u bijvoorbeeld naar Azure Blob Storage of Azure Data Lake Storage wilt schrijven, heeft de gebruiker de machtigingen LIST, READ, CREATE en WRITE nodig voor deze bestemmingen.

Zie CETAS-machtigingen (CREATE EXTERNAL TABLE AS SELECT) voor meer informatie over machtigingen voor CETAS.

Ondersteunde indelingen voor CETAS

CETAS ontvangt een gegevensstroom met een SELECT-opdracht voor invoer en exporteert de resultaten in CSV- of Parquet-formaat. De invoer ondersteunt dezelfde indelingen als een SELECT-opdracht, inclusief OPENROWSET-resultaten.

De invoer kan een tabel zijn die lokaal wordt uitgevoerd op uw SQL Server-exemplaar, een netwerkbestand dat toegankelijk is via een OPENROWSET-bewerking, een tabel in een ander databasesysteem of een Delta-bestand dat is opgeslagen in Azure Blob Storage, Azure Data Lake Storage of S3-compatibele objectopslag.

CETAS T-SQL-structuur

Voor een beter begrip van CETAS kunt u de algehele T-SQL-syntaxis opsplitsen. CETAS T-SQL-structuur volgt een bottom-up logicabenadering. Het is eenvoudiger om aan het einde van de instructie te beginnen en vervolgens naar de bovenkant van de T-SQL-instructie te gaan.

  • De onderkant van de CETAS-structuur bevat een SELECT-instructie die u gebruikt om te definiëren welke gegevens moeten worden geëxporteerd.
  • In het midden zijn er optionele parameters die u kunt gebruiken om gegevens te weigeren die u niet wilt exporteren.
  • Boven aan de CETAS-instructie voegt u na het declareren van CREATE EXTERNAL TABLE informatie toe over de doellocatie, bestandsnaam en bestandsindeling.

Met deze structuur kan CETAS worden gecombineerd met elke SELECT-instructie om query's uit te voeren op gegevens buiten SQL Server, binnen SQL Server of vanuit een andere ondersteunde database. SQL Server 2025 maakt automatisch de bestandsnaam en splitst de resultaten in meerdere bestanden voor optimalisatie. Een tabel die als Parquet wordt geëxporteerd, kan bijvoorbeeld verschillende bestanden genereren, afhankelijk van de geëxporteerde gegevensgrootte. De select-instructie definieert de definitie en het type van de externe tabelkolom.

Een tabel exporteren uit SQL Server als Parquet

In het volgende voorbeeld wordt CETAS gebruikt om een tabel uit SQL Server als Parquet te exporteren:

CREATE EXTERNAL TABLE ext_sales
WITH (
      LOCATION = '/cetas',
      DATA_SOURCE = s3_eds,
      FILE_FORMAT = ParquetFileFormat
     ) AS
SELECT *
FROM AdventureWorks2025.[Sales].[SalesOrderDetail];

Een Delta-bestand lezen en exporteren als Parquet

In het volgende voorbeeld wordt CETAS gebruikt om een Delta-bestand te lezen en te exporteren als Parquet:

CREATE EXTERNAL TABLE Delta_to_Parquet
WITH (
        LOCATION = N'/backup/sales',
        DATA_SOURCE = s3_parquet,
        FILE_FORMAT = ParquetFileFormat
      ) AS
SELECT *
FROM OPENROWSET(BULK N'/delta/sales_fy22/',
FORMAT = 'DELTA',
DATA_SOURCE = 's3_delta') AS [r];

In de volgende oefening gebruikt u CETAS voor het volgende:

  • Een tabel exporteren als Parquet.
  • Verplaats koude gegevens uit een database naar de opslag.
  • Maak een externe tabel voor toegang tot opgeslagen gegevens.
  • Gebruik weergaven, zoeken met jokertekens, het verwijderen van mappen en metagegevens als querystrategieën.