KÜLSŐ TÁBLA LÉTREHOZÁSA (Transact-SQL)

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Raktár a Microsoft FabricbanSQL-adatbázis a Microsoft Fabricban

Létrehoz egy külső táblát.

Ez a cikk a választott SQL-termék szintaxisát, argumentumait, megjegyzéseit, engedélyeit és példáit ismerteti.

Termék kiválasztása

A következő sorban válassza ki a kívánt terméknevet, és csak az adott termék adatai jelenjenek meg.

* SQL Server *  

SQL Database-

felügyelt Azure SQL-példány

Szövet adattárház

Fabric SQL-adatbázis

Azure Synapse
Elemzési

Elemzési platform
Rendszer (PDW)

 

Áttekintés: SQL Server

Ez a parancs létrehoz egy külső táblát a PolyBase számára a Hadoop-fürtben vagy az Azure Blob Storage PolyBase külső táblában tárolt adatok eléréséhez, amely a Hadoop-fürtben vagy az Azure Blob Storage-ban tárolt adatokra hivatkozik.

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók.

Használjon külső táblát egy külső adatforrással a PolyBase-lekérdezésekhez. A külső adatforrások kapcsolat létesítésére és az elsődleges használati esetek támogatására szolgálnak:

• Adatvirtualizálás és adatbetöltés adatvirtualizálással a PolyBase használatával az SQL Serverben
• Tömeges betöltési műveletek SQL Server vagy SQL Database használatával BULK INSERT vagy OPENROWSET használatával

A külső táblák külső adatforráson alapulnak.

Transact-SQL szintaxis konvenciók

Szintaxis

-- 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 ]

Érvek

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

A létrehozandó tábla egy-háromrészes neve.

Külső táblák esetében az SQL csak a tábla metaadatait tárolja, valamint a Hadoopban vagy az Azure Blob Storage-ban hivatkozott fájlra vagy mappára vonatkozó alapvető statisztikákat. A rendszer nem helyez át vagy tárol tényleges adatokat az SQL Serveren.

Fontos

A legjobb teljesítmény érdekében, ha a külső adatforrás-illesztőprogram támogatja a háromrészes nevet, meg kell adnia a háromrészes nevet.

< > column_definition [ ,... n ]

CREATE EXTERNAL TABLE támogatja az oszlopnév, az adattípus, a nullhihetőség és a rendezés konfigurálását. Külső táblákon nem használható DEFAULT CONSTRAINT .

Az oszlopdefinícióknak, beleértve az adattípusokat és az oszlopok számát, meg kell egyeznie a külső fájlok adataival. Ha eltérés van, a fájlsorokat a rendszer elutasítja a tényleges adatok lekérdezésekor.

LOCATION = "folder_or_filepath"

Megadja a Hadoopban vagy az Azure Blob Storage-ban tárolt tényleges adatok mappáit vagy fájlelérési útját és fájlnevét. Emellett az S3-kompatibilis objektumtárolás az SQL Server 2022-től (16.x)) is támogatott. A hely a gyökérmappából indul ki. A gyökérmappa a külső adatforrásban megadott adathely.

DATA_SOURCE = external_data_source_name

Megadja annak a külső adatforrásnak a nevét, amely a külső adatok helyét tartalmazza. Ez a hely egy Hadoop fájlrendszer (HDFS), egy Azure Blob Storage-tároló vagy az Azure Data Lake Store. Külső adatforrás létrehozásához használja a CREATE EXTERNAL DATA SOURCE (KÜLSŐ ADATFORRÁS LÉTREHOZÁSA) parancsot.

FILE_FORMAT = external_file_format_name

Megadja annak a külső fájlformátum objektumnak a nevét, amely a külső adatok fájltípusát és tömörítési módját tárolja. Külső fájlformátum létrehozásához használja CREATE EXTERNAL FILE FORMAT.

A külső fájlformátumokat több hasonló külső fájl is újra felhasználhatja.

ELUTASÍTÁSi beállítások

Ez a beállítás csak olyan külső adatforrásokkal használható, ahol TYPE = HADOOP.

Megadhatja azokat az elutasítási paramétereket, amelyek meghatározzák, hogy a PolyBase hogyan kezeli a külső adatforrásból lekért piszkos rekordokat. Az adatrekordok akkor minősülnek "piszkosnak", ha a tényleges adattípusok vagy az oszlopok száma nem egyezik a külső tábla oszlopdefinícióival.

Ha nem ad meg vagy módosít elutasító értékeket, a PolyBase alapértelmezett értékeket használ. Az elutasítási paraméterekkel kapcsolatos információk további metaadatokként lesznek tárolva, amikor utasítással rendelkező CREATE EXTERNAL TABLE külső táblát hoz létre. Amikor egy jövőbeli SELECT utasítás vagy SELECT INTO SELECT utasítás adatokat választ ki a külső táblából, a PolyBase az elutasítási beállításokkal határozza meg azoknak a soroknak a számát vagy százalékát, amelyeket a tényleges lekérdezés meghiúsulása előtt el lehet utasítani. A lekérdezés (részleges) eredményeket ad vissza, amíg meg nem lépi az elutasítási küszöbértéket. Ezután a megfelelő hibaüzenettel meghiúsul.

REJECT_TYPE = { érték | százalék }

Tisztázza, hogy a REJECT_VALUE beállítás konstans értékként vagy százalékként van-e megadva.

• érték

  REJECT_VALUE egy literális érték, nem százalék. A lekérdezés meghiúsul, ha az elutasított sorok száma meghaladja a reject_value.

  Ha és, REJECT_VALUE = 5REJECT_TYPE = valuea SELECT lekérdezés például öt sor elutasítása után meghiúsul.

• százalékos

  REJECT_VALUE százalékos érték, nem pedig literális érték. A lekérdezés akkor hiúsul meg, ha a sikertelen sorok százalékos aránya meghaladja a reject_value. A sikertelen sorok százalékos aránya időközönként lesz kiszámítva.

REJECT_VALUE = reject_value

Megadja a lekérdezés sikertelensége előtt elutasítható sorok értékét vagy százalékos arányát.

A REJECT_TYPE = valuereject_value 0 és 2 147 483 647 közötti egész számnak kell lennie.

A REJECT_TYPE = percentagereject_value 0 és 100 közötti lebegőpontosnak kell lennie.

REJECT_SAMPLE_VALUE = reject_sample_value

Ez az attribútum megadása kötelező.REJECT_TYPE = percentage Meghatározza, hogy hány sort kíséreljen meg lekérni, mielőtt a PolyBase újraszámítja az elutasított sorok százalékos arányát.

A reject_sample_value paraméternek 0 és 2 147 483 647 közötti egész számnak kell lennie.

Ha például REJECT_SAMPLE_VALUE = 1000a PolyBase kiszámítja a sikertelen sorok százalékos arányát, miután 1000 sort próbált importálni a külső adatfájlból. Ha a sikertelen sorok százalékos aránya kisebb, mint reject_value, a PolyBase további 1000 sort kísérel meg lekérni. A sikertelen sorok százalékos arányát továbbra is újraszámolja, miután minden további 1000 sort megpróbál importálni.

Jegyzet

Mivel a PolyBase időközönként kiszámítja a sikertelen sorok százalékos arányát, a sikertelen sorok tényleges százalékos aránya meghaladhatja reject_value.

Example

Ez a példa bemutatja, hogyan működnek együtt a három REJECT lehetőség. Például, ha REJECT_TYPE = percentage, REJECT_VALUE = 30és REJECT_SAMPLE_VALUE = 100, a következő forgatókönyv fordulhat elő:

• A PolyBase megpróbálja lekérni az első 100 sort; 25 sikertelen és 75 sikeres.
• A sikertelen sorok százalékos értéke 25%lesz, ami kisebb, mint a 30%elvetésének értéke. Ennek eredményeként a PolyBase továbbra is beolvassa az adatokat a külső adatforrásból.
• A PolyBase megkísérli betölteni a következő 100 sort; ezúttal 25 sor sikeres és 75 sor meghiúsul.
• A sikertelen sorok százalékos aránya 50%lesz újraszámolva. A sikertelen sorok százalékos aránya túllépte a 30% elutasítási értéket.
• A PolyBase-lekérdezés 50% elutasított sortal meghiúsul, miután megkísérelte visszaadni az első 200 sort. Az egyező sorokat a rendszer még azelőtt adja vissza, hogy a PolyBase-lekérdezés észleli az elutasítási küszöbérték túllépésének észlelését.

REJECTED_ROW_LOCATION = könyvtár helye

A következőkre vonatkozik: AZ SQL Server 2019 (15.x) CU 6-os és újabb verziói és az Azure Synapse Analytics.

Megadja a külső adatforrás azon könyvtárát, amelybe az elutasított sorokat és a megfelelő hibafájlt meg kell írni.

Ha a megadott elérési út nem létezik, a PolyBase létrehoz egyet az Ön nevében. A rendszer létrehoz egy gyermekkönyvtárat _rejectedrowsnéven. A _ karakter biztosítja, hogy a könyvtár más adatfeldolgozás céljából ne legyen elérhető, kivéve, ha a helyparaméter kifejezetten el van nevezve. Ebben a könyvtárban létrejön egy mappa a betöltési idő alapján YearMonthDay -HourMinuteSecond formátumban (például 20230330-173205). Ebben a mappában kétféle fájltípus van megírva, a _reason fájl és az adatfájl. Ez a beállítás csak olyan külső adatforrásokhoz használható, ahol TYPE = HADOOP és külső táblákhoz DELIMITEDTEXTFORMAT_TYPE. További információ: CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL FILE FORMAT.

Annak oka, hogy a fájlok és az adatfájlok is rendelkeznek a CTAS-utasításhoz társított queryID. Mivel az adatok és az ok külön fájlokban találhatók, a megfelelő fájloknak egyező utótagjuk van.

Engedélyek

A következő felhasználói engedélyek szükségesek:

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT (csak a Hadoopra és az Azure Storage-ra vonatkozik külső adatforrásokra)
• CONTROL DATABASE (csak a Hadoopra és az Azure Storage-ra vonatkozik külső adatforrásokra)

Vegye figyelembe, hogy a DATABASE SCOPED CREDENTIAL parancsban CREATE EXTERNAL TABLE használt távoli bejelentkezésnek olvasási engedéllyel kell rendelkeznie a paraméterben megadott külső adatforrás elérési útjára/táblájára/gyűjteményére LOCATION vonatkozóan. Ha ezzel EXTERNAL TABLE szeretné exportálni az adatokat egy Hadoop- vagy Azure Storage-külső adatforrásba, akkor a megadott bejelentkezésnek írási engedéllyel kell rendelkeznie a megadott LOCATIONelérési úton. A Hadoop nem támogatott az SQL Server 2022 -ben (16.x).

Az Azure Blob Storage esetében az Azure Portalon, az Azure Blob Storage- vagy ADLS Gen2-tárfiókokban a hozzáférési kulcsok és a közös hozzáférésű jogosultságkód (SAS) konfigurálásakor konfigurálja az Engedélyezett engedélyeket, adjon meg legalább Olvasási és Írási engedélyeket. listaengedélyre is szükség lehet a mappák közötti kereséshez. Az engedélyezett erőforrástípusok között tároló és objektum is ki kell jelölnie.

Fontos

Az ALTER ANY EXTERNAL DATA SOURCE engedély lehetővé teszi bármely tag számára, hogy bármilyen külső adatforrás-objektumot hozzon létre és módosítson, ezért lehetővé teszi az adatbázis hatókörébe tartozó hitelesítő adatok elérését is az adatbázisban. Ezt az engedélyt kiemelt jogosultságúnak kell tekinteni, ezért csak megbízható rendszerbiztonsági tagoknak adható.

Hibakezelés

A CREATE EXTERNAL TABLE utasítás végrehajtása során a PolyBase megpróbál csatlakozni a külső adatforráshoz. Ha a kapcsolódási kísérlet meghiúsul, az utasítás meghiúsul, és a külső tábla nem jön létre. Egy vagy több percig is eltarthat, amíg a parancs meghibásodik, mivel a PolyBase újrapróbálkozza a kapcsolatot, mielőtt végül meghiúsul a lekérdezés.

Megjegyzések

Alkalmi lekérdezési forgatókönyvekben, például SELECT FROM EXTERNAL TABLEa PolyBase egy ideiglenes táblában tárolja a külső adatforrásból lekért sorokat. A lekérdezés befejezése után a PolyBase eltávolítja és törli az ideiglenes táblát. A rendszer nem tárol állandó adatokat SQL-táblákban. Ezzel szemben az importálási forgatókönyvben, például SELECT INTO FROM EXTERNAL TABLEa PolyBase a külső adatforrásból lekért sorokat állandó adatként tárolja az SQL-táblában. Az új tábla a lekérdezés végrehajtása során jön létre, amikor a PolyBase lekéri a külső adatokat.

A Hadoop formátumot csak az SQL Server 2016 (13.x), az SQL Server 2017 (14.x) és az SQL Server 2019 (15.x) támogatja.

A PolyBase a lekérdezési számítások egy részét leküldi a Hadoopba a lekérdezési teljesítmény javítása érdekében. Ezt a műveletet predikátum leküldéses leküldésnek nevezzük. Ennek engedélyezéséhez adja meg a Hadoop erőforrás-kezelő helybeállítását KÜLSŐ ADATFORRÁS LÉTREHOZÁSA.

Számos olyan külső táblát hozhat létre, amelyek ugyanarra vagy különböző külső adatforrásokra hivatkoznak.

Korlátozások

Mivel egy külső tábla adatai nem tartoznak az SQL Server közvetlen felügyelete alá, külső folyamat bármikor módosíthatja vagy eltávolíthatja őket. Ennek eredményeképpen a külső táblák lekérdezési eredményei nem garantáltan determinisztikusak. Ugyanez a lekérdezés különböző eredményeket adhat vissza minden alkalommal, amikor egy külső táblán fut. Hasonlóképpen előfordulhat, hogy egy lekérdezés meghiúsul, ha a külső adatokat áthelyezik vagy eltávolítják.

Több külső táblát is létrehozhat, amelyek mindegyike különböző külső adatforrásokra hivatkozik. Ha egyszerre futtat lekérdezéseket különböző Hadoop-adatforrásokkal, akkor minden Hadoop-forrásnak ugyanazt a "hadoop-kapcsolat" kiszolgálókonfigurációs beállítást kell használnia. Például nem futtathat egyszerre lekérdezést Egy Cloudera Hadoop-fürt és egy Hortonworks Hadoop-fürt között, mivel ezek különböző konfigurációs beállításokat használnak. A konfigurációs beállításokról és a támogatott kombinációkról lásd a PolyBase kapcsolati konfigurációját.

Ha a külső tábla DELIMITEDTEXT, CSV, PARQUETvagy DELTA használ adattípusként, a külső táblák csak CREATE STATISTICS parancsonként egy oszlop statisztikáit támogatják.

Külső táblákban csak ezek az adatdefiníciós nyelvi (DDL-) utasítások engedélyezettek:

• CREATE TABLE és DROP TABLE
• CREATE STATISTICS és DROP STATISTICS
• CREATE VIEW és DROP VIEW

A szerkezetek és műveletek nem támogatottak:

• Külső DEFAULT táblaoszlopokra vonatkozó korlátozás
• Adatmanipulációs nyelv (DML) a törlési, beszúrási és frissítési műveletekhez

Lekérdezési korlátozások

A PolyBase legfeljebb 33 000 fájlt használhat fel mappánként 32 egyidejű PolyBase-lekérdezés futtatásakor. Ez a maximális szám az egyes HDFS-mappákban lévő fájlokat és almappákat is tartalmazza. Ha az egyidejűség mértéke kisebb, mint 32, a felhasználó több mint 33 ezer fájlt tartalmazó HDFS-mappákon futtathat PolyBase-lekérdezéseket. Javasoljuk, hogy tartsa rövidre a külső fájl elérési útját, és HDFS-mappánként legfeljebb 30 ezer fájlt használjon. Ha túl sok fájlra hivatkozik, előfordulhat, hogy a Java virtuális gép (JVM) memóriakimaradást tapasztal.

Táblázatszélesség korlátozásai

Az SQL Server 2016-ban (13.x) a PolyBase sorszélesség-korlátja 32 KB egy érvényes sor tábladefiníciónkénti maximális mérete alapján. Ha az oszlopséma összege nagyobb, mint 32 KB, a PolyBase nem tudja lekérdezni az adatokat.

Adattípus korlátozásai

A következő adattípusok nem használhatók külső PolyBase-táblákban:

• földrajzi
• geometriai
• hierarchiaazonosító
• kép
• szöveg
• kontextus
• xml
• Bármely felhasználó által definiált típus

Az adatforrásokra vonatkozó korlátozások

Jóslat

Az Oracle szinonimái nem támogatottak a PolyBase használata esetén.

Tömböket tartalmazó mongoDB-gyűjtemények külső táblái

A sp_data_source_objects segítségével észlelhető a tömböket tartalmazó MongoDB-gyűjtemények gyűjteményséma (oszlopai), és manuálisan hozhatja létre a külső táblát. A sp_data_source_table_columns tárolt eljárás automatikusan elvégzi az összesimítást a MongoDB-illesztőHöz készült PolyBase ODBC-illesztőprogramon keresztül.

Zár

Megosztott zárolás az SCHEMARESOLUTION objektumon.

Biztonság

A külső táblák adatfájljait a Hadoop vagy az Azure Blob Storage tárolja. Ezeket az adatfájlokat a saját folyamatai hozzák létre és kezelik. A külső adatok biztonságának kezelése az Ön feladata.

Példák

Egy. Külső táblázat létrehozása szöveghatárolt formátumban lévő adatokkal

Ez a példa egy szöveghatárolt fájlokban formázott adatokat tartalmazó külső tábla létrehozásához szükséges összes lépést mutatja be. Meghatározza a külső adatforrás mydatasource és egy külső fájlformátumot myfileformat. Ezekre az adatbázisszintű objektumokra a CREATE EXTERNAL TABLE utasítás hivatkozik. További információ: CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL FILE FORMAT.

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

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

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

B. Külső tábla létrehozása RCFile formátumú adatokkal

Ez a példa egy RCFiles formátumú adatokat tartalmazó külső tábla létrehozásához szükséges összes lépést mutatja be. Meghatározza a külső adatforrás mydatasource_rc és egy külső fájlformátumot myfileformat_rc. Ezekre az adatbázisszintű objektumokra a CREATE EXTERNAL TABLE utasítás hivatkozik. További információ: CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL FILE FORMAT.

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

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

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

C. Külső táblázat létrehozása ORC formátumú adatokkal

Ez a példa egy ORC-fájlként formázott adatokat tartalmazó külső tábla létrehozásához szükséges összes lépést mutatja be. Meghatározza a külső adatforrás mydatasource_orc és egy külső fájlformátumot myfileformat_orc. Ezekre az adatbázisszintű objektumokra a CREATE EXTERNAL TABLE utasítás hivatkozik. További információ: CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL FILE FORMAT.

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

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

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-adatok lekérdezése

ClickStream egy külső tábla, amely egy Hadoop-fürt employee.tbl tagolt szövegfájlhoz csatlakozik. Az alábbi lekérdezés ugyanúgy néz ki, mint egy standard táblán lévő lekérdezés. Ez a lekérdezés azonban lekéri az adatokat a Hadoopból, majd kiszámítja az eredményeket.

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

E. Hadoop-adatok csatlakoztatása SQL-adatokkal

Ez a lekérdezés úgy néz ki, mint egy szabványos JOIN két SQL-táblán. A különbség az, hogy a PolyBase lekéri a kattintásfolyam adatait a Hadoopból, majd csatlakoztatja azokat a UrlDescription táblához. Az egyik tábla egy külső tábla, a másik pedig egy szabványos SQL-tábla.

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

F. Adatok importálása a Hadoopból EGY SQL-táblába

Ez a példa létrehoz egy új SQL-táblát ms_user, amely véglegesen tárolja a standard SQL-tábla user és a külső tábla ClickStreamközötti illesztés eredményét.

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. Külső tábla létrehozása az SQL Serverhez

Az adatbázis hatókörébe tartozó hitelesítő adatok létrehozása előtt a felhasználói adatbázisnak rendelkeznie kell egy fő kulccsal a hitelesítő adatok védelméhez. További információ: CREATE MASTER KEY and CREATE DATABASE SCOPED CREDENTIAL.

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
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>';

Hozzon létre egy SQLServerInstancenevű új külső adatforrást és sqlserver.customernevű külső táblát:

/* 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;

/* 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
);

Én. Külső tábla létrehozása az Oracle számára

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

/*
* 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>';
GO

/*
* 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. 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 (
    DATA_SOURCE = external_data_source_name,
    LOCATION = 'DB1.mySchema.customer'
);

J. Külső tábla létrehozása a Teradata számára

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

/*
* 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>';
GO

/* 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
);
GO

/* 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. Külső tábla létrehozása a MongoDB-hez

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

/*
* 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>';
GO

/* 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. S3-kompatibilis objektumtároló lekérdezése külső táblán keresztül

A következőkre vonatkozik: SQL Server 2022 (16.x) és újabb verziók.

Az alábbi példa bemutatja, hogy a T-SQL használatával lekérdezhető egy S3-kompatibilis objektumtárolóban tárolt parquet fájl külső tábla lekérdezésével. A minta egy relatív elérési utat használ a külső adatforráson belül.

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

Kapcsolódó tartalom

• KÜLSŐ ADATFORRÁS LÉTREHOZÁSA (Transact-SQL)
• KÜLSŐ FÁJLFORMÁTUM LÉTREHOZÁSA (Transact-SQL)
• sp_data_source_objects (Transact-SQL)