CREATE EXTERNAL TABLE (Transact-SQL)

Hiermee maakt u een externe tabel.

Dit artikel bevat de syntaxis, argumenten, opmerkingen, machtigingen en voorbeelden voor het SQL-product dat u kiest.

Zie Transact-SQL syntaxisconventiesvoor meer informatie over de syntaxisconventies.

Een product selecteren

Selecteer in de volgende rij de productnaam waarin u geïnteresseerd bent en alleen de informatie van dat product wordt weergegeven.

* SQL Server *  

SQL Database-

azure SQL Managed Instance

Azure Synapse
Analyse-

Analytics Platform
Systeem (PDW)

 

Overzicht: SQL Server

Met deze opdracht maakt u een externe tabel voor PolyBase voor toegang tot gegevens die zijn opgeslagen in een Hadoop-cluster of een externe Azure Blob Storage PolyBase-tabel die verwijst naar gegevens die zijn opgeslagen in een Hadoop-cluster of Azure Blob Storage.

van toepassing op: SQL Server 2016 (of hoger)

Gebruik een externe tabel met een externe gegevensbron voor PolyBase-query's. Externe gegevensbronnen worden gebruikt om connectiviteit tot stand te brengen en deze primaire use cases te ondersteunen:

• Gegevensvirtualisatie en gegevensbelasting met PolyBase-
• Bewerkingen voor bulksgewijs laden met behulp van SQL Server of SQL Database met behulp van BULK INSERT of OPENROWSET

Zie ook CREATE EXTERNAL DATA SOURCE en DROP EXTERNAL TABLE.

Syntaxis

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

Argumenten

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

De naam van één tot drie delen van de tabel die u wilt maken. Voor een externe tabel slaat SQL alleen de metagegevens van de tabel op, samen met basisstatistieken over het bestand of de map waarnaar wordt verwezen in Hadoop of Azure Blob Storage. Er worden geen werkelijke gegevens verplaatst of opgeslagen in SQL Server.

Belangrijk

Als het stuurprogramma van de externe gegevensbron een driedelige naam ondersteunt, wordt het ten zeerste aangeraden om de driedelige naam op te geven.

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE biedt ondersteuning voor de mogelijkheid om kolomnaam, gegevenstype, null-functionaliteit en sortering te configureren. U kunt de STANDAARDBEPERKING niet gebruiken voor externe tabellen.

De kolomdefinities, inclusief de gegevenstypen en het aantal kolommen, moeten overeenkomen met de gegevens in de externe bestanden. Als er sprake is van een onjuiste overeenkomst, worden de bestandsrijen geweigerd bij het opvragen van de werkelijke gegevens.

LOCATION = 'folder_or_filepath'

Hiermee geeft u de map of het bestandspad en de bestandsnaam op voor de werkelijke gegevens in Hadoop of Azure Blob Storage. Daarnaast wordt S3-compatibele objectopslag ondersteund vanaf SQL Server 2022 (16.x)). De locatie begint vanuit de hoofdmap. De hoofdmap is de gegevenslocatie die is opgegeven in de externe gegevensbron.

In SQL Server maakt de CREATE EXTERNAL TABLE instructie het pad en de map als deze nog niet bestaat. Vervolgens kunt u INSERT INTO gebruiken om gegevens uit een lokale SQL Server-tabel te exporteren naar de externe gegevensbron. Zie PolyBase-query'svoor meer informatie.

Als u LOCATION opgeeft als een map, haalt een PolyBase-query die uit de externe tabel selecteert bestanden op uit de map en alle bijbehorende submappen. Net als Hadoop retourneert PolyBase geen verborgen mappen. Het retourneert ook geen bestanden waarvoor de bestandsnaam begint met een onderstreping (_) of een punt (.).

Als LOCATION='/webdata/'in het volgende afbeeldingsvoorbeeld retourneert een PolyBase-query rijen uit mydata.txt en mydata2.txt. Het retourneert geen mydata3.txt omdat het een bestand in een verborgen submap is. En het retourneert geen _hidden.txt omdat het een verborgen bestand is.

Als u de standaardinstelling wilt wijzigen en alleen wilt lezen uit de hoofdmap, stelt u het kenmerk <polybase.recursive.traversal> in op 'false' in het core-site.xml configuratiebestand. Dit bestand bevindt zich onder <SqlBinRoot>\PolyBase\Hadoop\Conf onder de bin hoofdmap van SQL Server. Bijvoorbeeld C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn.

DATA_SOURCE = external_data_source_name

Hiermee geeft u de naam op van de externe gegevensbron die de locatie van de externe gegevens bevat. Deze locatie is een Hadoop File System (HDFS), een Azure Blob Storage-container of Azure Data Lake Store. Als u een externe gegevensbron wilt maken, gebruikt u CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Hiermee geeft u de naam op van het externe bestandsindelingsobject waarin het bestandstype en de compressiemethode voor de externe gegevens worden opgeslagen. Als u een externe bestandsindeling wilt maken, gebruikt u CREATE EXTERNAL FILE FORMAT.

Externe bestandsindelingen kunnen opnieuw worden gebruikt door meerdere vergelijkbare externe bestanden.

Opties voor weigeren

Deze optie kan alleen worden gebruikt met externe gegevensbronnen waarbij TYPE = HADOOP.

U kunt weigeringsparameters opgeven die bepalen hoe PolyBase vuile records verwerkt die worden opgehaald uit de externe gegevensbron. Een gegevensrecord wordt beschouwd als 'vuil' als het werkelijke gegevenstypen of het aantal kolommen niet overeenkomt met de kolomdefinities van de externe tabel.

Wanneer u geen afkeuringswaarden opgeeft of wijzigt, gebruikt PolyBase standaardwaarden. Deze informatie over de geweigerde parameters wordt opgeslagen als aanvullende metagegevens wanneer u een externe tabel maakt met de instructie CREATE EXTERNAL TABLE. Wanneer een toekomstige SELECT-instructie of SELECT INTO SELECT-instructie gegevens uit de externe tabel selecteert, gebruikt PolyBase de opties voor weigeren om het aantal of het percentage rijen te bepalen dat kan worden geweigerd voordat de werkelijke query mislukt. De query retourneert (gedeeltelijke) resultaten totdat de drempelwaarde voor weigeren is overschreden. Het mislukt vervolgens met het juiste foutbericht.

REJECT_TYPE = waarde | percentage

Verduidelijkt of de optie REJECT_VALUE is opgegeven als een letterlijke waarde of een percentage.

waarde

REJECT_VALUE is een letterlijke waarde, geen percentage. De query mislukt wanneer het aantal geweigerde rijen groter is dan reject_value.

Als bijvoorbeeld REJECT_VALUE = 5 en REJECT_TYPE = value, mislukt de SELECT-query nadat vijf rijen zijn geweigerd.

percentage

REJECT_VALUE is een percentage, geen letterlijke waarde. Een query mislukt wanneer het percentage mislukte rijen groter is dan reject_value. Het percentage mislukte rijen wordt berekend met intervallen.

REJECT_VALUE = reject_value

Hiermee geeft u de waarde of het percentage rijen op dat kan worden geweigerd voordat de query mislukt.

Voor REJECT_TYPE = waarde moet reject_value een geheel getal tussen 0 en 2.147.483.647 zijn.

Voor REJECT_TYPE = percentage moet reject_value een float tussen 0 en 100 zijn.

REJECT_SAMPLE_VALUE = reject_sample_value

Dit kenmerk is vereist wanneer u REJECT_TYPE = percentage opgeeft. Het bepaalt het aantal rijen dat moet worden opgehaald voordat polybase het percentage geweigerde rijen opnieuw berekent.

De parameter reject_sample_value moet een geheel getal tussen 0 en 2.147.483.647 zijn.

Als bijvoorbeeld REJECT_SAMPLE_VALUE = 1000, berekent PolyBase het percentage mislukte rijen nadat is geprobeerd 1000 rijen uit het externe gegevensbestand te importeren. Als het percentage mislukte rijen kleiner is dan reject_value, probeert PolyBase nog eens 1000 rijen op te halen. Het percentage mislukte rijen wordt nog steeds herberekend nadat wordt geprobeerd om elke extra 1000 rijen te importeren.

Notitie

Omdat PolyBase het percentage mislukte rijen berekent met intervallen, kan het werkelijke percentage mislukte rijen groter zijn dan reject_value.

Voorbeeld:

In dit voorbeeld ziet u hoe de drie weigeringsopties met elkaar communiceren. Als bijvoorbeeld REJECT_TYPE = percentage, REJECT_VALUE = 30 en REJECT_SAMPLE_VALUE = 100, kan het volgende scenario optreden:

• PolyBase probeert de eerste 100 rijen op te halen; 25 mislukken en 75 slagen.
• Het percentage mislukte rijen wordt berekend als 25%, wat kleiner is dan de weigeringswaarde van 30%. Als gevolg hiervan blijft PolyBase gegevens ophalen uit de externe gegevensbron.
• PolyBase probeert de volgende 100 rijen te laden; deze keer slagen 25 rijen en mislukken 75 rijen.
• Het percentage mislukte rijen wordt opnieuw berekend als 50%. Het percentage mislukte rijen heeft de waarde van 30% weigeren overschreden.
• De PolyBase-query mislukt met 50% geweigerde rijen na een poging om de eerste 200 rijen te retourneren. U ziet dat overeenkomende rijen zijn geretourneerd voordat de PolyBase-query detecteert dat de drempelwaarde voor weigeren is overschreden.

REJECTED_ROW_LOCATION = maplocatie

van toepassing op: SQL Server 2019 CU6 en latere versies, Azure Synapse Analytics.

Hiermee geeft u de map in de externe gegevensbron op dat de geweigerde rijen en het bijbehorende foutbestand moeten worden geschreven.

Als het opgegeven pad niet bestaat, maakt PolyBase er een namens u. Er wordt een onderliggende map gemaakt met de naam _rejectedrows. Het _ teken zorgt ervoor dat de map wordt ontsnapt voor andere gegevensverwerking, tenzij deze expliciet is benoemd in de locatieparameter. In deze map is er een map gemaakt op basis van het tijdstip van het indienen van de belasting in de indeling YearMonthDay -HourMinuteSecond (bijvoorbeeld 20230330-173205). In deze map worden twee typen bestanden geschreven, het _reason-bestand en het gegevensbestand. Deze optie kan alleen worden gebruikt met externe gegevensbronnen waarbij TYPE = HADOOP en voor externe tabellen met behulp van DELIMITEDTEXTFORMAT_TYPE. Zie CREATE EXTERNAL DATA SOURCE en CREATE EXTERNAL FILE FORMATvoor meer informatie.

De redenbestanden en de gegevensbestanden hebben beide de queryID gekoppeld aan de CTAS-instructie. Omdat de gegevens en de reden zich in afzonderlijke bestanden bevinden, hebben de bijbehorende bestanden een overeenkomend achtervoegsel.

Machtigingen

Hiervoor zijn deze gebruikersmachtigingen vereist:

• CREATE TABLE-
• ALLE SCHEMA- WIJZIGEN
• EEN EXTERNE GEGEVENSBRON WIJZIGEN
• ALTER ANY EXTERNAL FILE FORMAT (alleen van toepassing op Externe Gegevensbronnen van Hadoop en Azure Storage)
• CONTROL DATABASE- (alleen van toepassing op externe Hadoop- en Azure Storage-gegevensbronnen)

Opmerking: de externe aanmelding die is opgegeven in de DATABASE SCOPED CREDENTIAL die in de opdracht CREATE EXTERNAL TABLE wordt gebruikt, moet machtiging lezen hebben voor het pad/de tabel/verzameling van de externe gegevensbron die is opgegeven in de parameter LOCATION. Als u van plan bent om deze EXTERNE TABEL te gebruiken om gegevens te exporteren naar een externe Hadoop- of Azure Storage-gegevensbron, moet de opgegeven aanmelding schrijfmachtiging hebben voor het pad dat is opgegeven in LOCATION. Houd er rekening mee dat Hadoop niet wordt ondersteund in SQL Server 2022 (16.x).

Voor Azure Blob Storage configureert u bij het configureren van de toegangssleutels en SAS (Shared Access Signature) in Azure Portal de Azure Blob Storage- of ADLS Gen2-opslagaccounts de Toegestane machtigingen om ten minste lezen en machtigingen voor schrijven te verlenen. machtiging lijst kan ook vereist zijn bij het zoeken in verschillende mappen. U moet ook zowel Container als Object selecteren als de toegestane resourcetypen.

Belangrijk

Met de machtiging ALTER ANY EXTERNAL DATA SOURCE verleent elke principal de mogelijkheid om een extern gegevensbronobject te maken en te wijzigen, waardoor het ook de mogelijkheid verleent om toegang te krijgen tot alle referenties binnen het databasebereik. Deze machtiging moet worden beschouwd als zeer bevoegd en moet daarom alleen worden verleend aan vertrouwde principals in het systeem.

Foutafhandeling

Tijdens het uitvoeren van de CREATE EXTERNAL TABLE-instructie probeert PolyBase verbinding te maken met de externe gegevensbron. Als de poging om verbinding te maken mislukt, mislukt de instructie en wordt de externe tabel niet gemaakt. Het kan een minuut of langer duren voordat de opdracht mislukt, omdat PolyBase de verbinding opnieuw probeert uit te voeren voordat de query uiteindelijk mislukt.

Opmerkingen

In ad-hocqueryscenario's, zoals SELECT FROM EXTERNAL TABLE, slaat PolyBase de rijen op die worden opgehaald uit de externe gegevensbron in een tijdelijke tabel. Nadat de query is voltooid, verwijdert PolyBase de tijdelijke tabel en verwijdert deze. Er worden geen permanente gegevens opgeslagen in SQL-tabellen.

In het importscenario, zoals SELECT INTO FROM EXTERNAL TABLE, slaat PolyBase daarentegen de rijen op die uit de externe gegevensbron worden opgehaald als permanente gegevens in de SQL-tabel. De nieuwe tabel wordt gemaakt tijdens het uitvoeren van query's wanneer PolyBase de externe gegevens ophaalt.

PolyBase kan een deel van de queryberekening naar Hadoop pushen om de queryprestaties te verbeteren. Deze actie wordt predicaatpushdown genoemd. Als u dit wilt inschakelen, geeft u de locatieoptie Hadoop resource manager op in CREATE EXTERNAL DATA SOURCE.

U kunt veel externe tabellen maken die verwijzen naar dezelfde of verschillende externe gegevensbronnen.

Beperkingen en beperkingen

Omdat de gegevens voor een externe tabel niet onder het directe beheer van SQL Server staan, kunnen deze op elk gewenst moment door een extern proces worden gewijzigd of verwijderd. Als gevolg hiervan zijn queryresultaten voor een externe tabel niet gegarandeerd deterministisch. Dezelfde query kan verschillende resultaten retourneren telkens wanneer deze wordt uitgevoerd op een externe tabel. Op dezelfde manier kan een query mislukken als de externe gegevens worden verplaatst of verwijderd.

U kunt meerdere externe tabellen maken die elk verwijzen naar verschillende externe gegevensbronnen. Als u gelijktijdig query's uitvoert op verschillende Hadoop-gegevensbronnen, moet elke Hadoop-bron dezelfde configuratie-instelling voor hadoop-connectiviteit gebruiken. U kunt bijvoorbeeld geen query tegelijk uitvoeren op een Cloudera Hadoop-cluster en een Hortonworks Hadoop-cluster, omdat deze verschillende configuratie-instellingen gebruiken. Zie PolyBase Connectivity Configurationvoor de configuratie-instellingen en ondersteunde combinaties.

Wanneer de externe tabel gebruikmaakt van DELIMITEDTEXT, CSV, PARQUETof DELTA als gegevenstypen, ondersteunen externe tabellen alleen statistieken voor één kolom per CREATE STATISTICS opdracht.

Alleen deze DDL-instructies (Data Definition Language) zijn toegestaan voor externe tabellen:

• CREATE TABLE en DROP TABLE
• STATISTIEKEN MAKEN EN STATISTIEKEN VERWIJDEREN
• WEERGAVE EN DROP VIEW MAKEN

Constructies en bewerkingen worden niet ondersteund:

• De standaardbeperking voor externe tabelkolommen
• DML-bewerkingen (Data Manipulation Language) voor verwijderen, invoegen en bijwerken

Querybeperkingen

PolyBase kan maximaal 33.000 bestanden per map gebruiken bij het uitvoeren van 32 gelijktijdige PolyBase-query's. Dit maximumaantal omvat zowel bestanden als submappen in elke HDFS-map. Als de mate van gelijktijdigheid kleiner is dan 32, kan een gebruiker PolyBase-query's uitvoeren op mappen in HDFS die meer dan 33k-bestanden bevatten. U wordt aangeraden externe bestandspaden kort te houden en niet meer dan 30k bestanden per HDFS-map te gebruiken. Wanneer er te veel bestanden naar worden verwezen, kan er een JVM-uitzondering (Java Virtual Machine) optreden.

Beperkingen voor tabelbreedte

PolyBase in SQL Server 2016 heeft een rijbreedtelimiet van 32 kB op basis van de maximale grootte van één geldige rij per tabeldefinitie. Als de som van het kolomschema groter is dan 32 kB, kan PolyBase geen query's uitvoeren op de gegevens.

Beperkingen voor gegevenstypen

De volgende gegevenstypen kunnen niet worden gebruikt in externe PolyBase-tabellen:

• geografie
• geometrie
• hierarchyid-
• afbeelding
• tekst
• xml--
• Elk door de gebruiker gedefinieerd type

Specifieke beperkingen voor gegevensbronnen

Orakel

Oracle-synoniemen worden niet ondersteund voor gebruik met PolyBase.

Externe tabellen voor MongoDB-verzamelingen die matrices bevatten

Als u externe tabellen wilt maken voor MongoDB-verzamelingen die matrices bevatten, moet u de extensie Data Virtualization voor Azure Data Studio gebruiken om een CREATE EXTERNAL TABLE-instructie te maken op basis van het schema dat is gedetecteerd door het PolyBase ODBC-stuurprogramma voor MongoDB. De afvlakkende acties worden automatisch uitgevoerd door het stuurprogramma. U kunt ook sp_data_source_objects (Transact-SQL) gebruiken om het verzamelingsschema (kolommen) te detecteren en de externe tabel handmatig te maken. De sp_data_source_table_columns opgeslagen procedure voert ook automatisch het platmaken uit via het PolyBase ODBC-stuurprogramma voor mongoDB-stuurprogramma. De datavirtualisatie-extensie voor Azure Data Studio en sp_data_source_table_columns dezelfde interne opgeslagen procedures gebruiken om een query uit te voeren op het externe schema.

Vergrendeling

Gedeelde vergrendeling op het OBJECT SCHEMARESOLUTION.

Veiligheid

De gegevensbestanden voor een externe tabel worden opgeslagen in Hadoop of Azure Blob Storage. Deze gegevensbestanden worden gemaakt en beheerd door uw eigen processen. Het is uw verantwoordelijkheid om de beveiliging van de externe gegevens te beheren.

Voorbeelden

Een. Een externe tabel maken met gegevens in tekstscheidingstekens

In dit voorbeeld ziet u alle stappen die nodig zijn voor het maken van een externe tabel met gegevens die zijn opgemaakt in door tekst gescheiden bestanden. Het definieert een externe gegevensbron mydatasource en een externe bestandsindeling myfileformat. Deze objecten op databaseniveau worden vervolgens in de CREATE EXTERNAL TABLE-instructie verwezen. Zie CREATE EXTERNAL DATA SOURCE en CREATE EXTERNAL FILE FORMATvoor meer informatie.

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. Een externe tabel maken met gegevens in RCFile-indeling

In dit voorbeeld ziet u alle stappen die nodig zijn voor het maken van een externe tabel met gegevens die zijn opgemaakt als RCFiles. Het definieert een externe gegevensbron mydatasource_rc en een externe bestandsindeling myfileformat_rc. Deze objecten op databaseniveau worden vervolgens in de CREATE EXTERNAL TABLE-instructie verwezen. Zie CREATE EXTERNAL DATA SOURCE en CREATE EXTERNAL FILE FORMATvoor meer informatie.

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. Een externe tabel maken met gegevens in ORC-indeling

In dit voorbeeld ziet u alle stappen die nodig zijn voor het maken van een externe tabel met gegevens die zijn opgemaakt als ORC-bestanden. Het definieert een externe gegevensbron mydatasource_orc en een externe bestandsindeling myfileformat_orc. Deze objecten op databaseniveau worden vervolgens in de CREATE EXTERNAL TABLE-instructie verwezen. Zie CREATE EXTERNAL DATA SOURCE en CREATE EXTERNAL FILE FORMATvoor meer informatie.

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. Hadoop-gegevens opvragen

ClickStream is een externe tabel die verbinding maakt met het employee.tbl tekstbestand met scheidingstekens in een Hadoop-cluster. De volgende query ziet eruit als een query op basis van een standaardtabel. Deze query haalt echter gegevens op uit Hadoop en berekent vervolgens de resultaten.

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. Hadoop-gegevens samenvoegen met SQL-gegevens

Deze query ziet eruit als een standaard-JOIN in twee SQL-tabellen. Het verschil is dat PolyBase de clickstream-gegevens van Hadoop ophaalt en deze vervolgens aan de UrlDescription tabel koppelt. De ene tabel is een externe tabel en de andere is een standaard SQL-tabel.

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. Gegevens uit Hadoop importeren in een SQL-tabel

In dit voorbeeld wordt een nieuwe SQL-tabel gemaakt ms_user waarmee het resultaat van een join tussen de standaard SQL-tabel user en de externe tabel permanent wordt opgeslagen ClickStream.

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. Een externe tabel maken voor SQL Server

Voordat u een databasereferentie in het bereik maakt, moet de gebruikersdatabase een hoofdsleutel hebben om de referentie te beveiligen. Zie CREATE MASTER KEY en CREATE DATABASE SCOPED CREDENTIALvoor meer informatie.

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

Maak een nieuwe externe gegevensbron met de naam SQLServerInstanceen een externe tabel met de naam sqlserver.customer:

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

Ik. Een externe tabel maken voor Oracle

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. Een externe tabel maken voor Teradata

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

K. Een externe tabel maken voor MongoDB

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

L. Query's uitvoeren op S3-compatibele objectopslag via externe tabel

Van toepassing op: SQL Server 2022 (16.x) en hoger

In het volgende voorbeeld ziet u hoe u met T-SQL een query uitvoert op een parquet-bestand dat is opgeslagen in S3-compatibele objectopslag via het uitvoeren van query's op externe tabellen. In het voorbeeld wordt een relatief pad in de externe gegevensbron gebruikt.

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

Volgende stappen

Meer informatie over gerelateerde concepten vindt u in de volgende artikelen:

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects (Transact-SQL)
• Data Virtualization-extensie voor Azure Data Studio-