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

A következőkre vonatkozik: Az SQL Server 2016 (13.x) és újabb verziói az Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPlatform System (PDW)Warehouse a Microsoft Fabricben

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

Microsoft Fabric

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 (vagy újabb)

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, erősen ajánlott megadni a háromrészes nevet.

< > column_definition [ ,... n ]

A CREATE EXTERNAL TABLE támogatja az oszlopok nevének, adattípusának, nullbilitásának és rendezésének konfigurálását. Külső táblák alapértelmezett KORLÁTOZÁSát nem használhatja.

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.

Az SQL Serverben a CREATE EXTERNAL TABLE utasítás létrehozza az elérési utat és a mappát, ha még nem létezik. Ezután az INSERT INTO használatával exportálhat adatokat egy helyi SQL Server-táblából a külső adatforrásba. További információ: PolyBase-lekérdezési forgatókönyvek.

Ha a LOCATION billentyűt mappaként adja meg, a külső táblából kiválasztott PolyBase-lekérdezések lekérik a fájlokat a mappából és annak összes almappájából. A Hadoophoz hasonlóan a PolyBase sem ad vissza rejtett mappákat. Nem ad vissza olyan fájlokat sem, amelyeknek a neve aláhúzással (_) vagy ponttal (.) kezdődik.

A következő képen látható példában, ha LOCATION='/webdata/', egy PolyBase-lekérdezés mydata.txt és mydata2.txtsorait adja vissza. Nem adja vissza mydata3.txt, mert egy rejtett almappában lévő fájl. És nem adja vissza _hidden.txt, mert rejtett fájl.

Az alapértelmezett és csak a gyökérmappából való olvasás módosításához állítsa a <polybase.recursive.traversal> attribútumot "false" értékre a core-site.xml konfigurációs fájlban. Ez a fájl az SQL Server <SqlBinRoot>\PolyBase\Hadoop\Conf gyökerének bin alatt található. Például C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn.

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 CREATE EXTERNAL DATA SOURCE (Transact-SQL).

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 a CREATE EXTERNAL FILE FORMAT (Transact-SQL).

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ások esetén használható, ahol TÍPUS = HADOOP.

Megadhatja az elutasítási paramétereket, amelyek meghatározzák, hogy a PolyBase hogyan fogja kezelni 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 külső táblát hoz létre a CREATE EXTERNAL TABLE utasítással. Ha egy jövőbeli SELECT utasítás vagy a SELECT INTO SELECT utasítás kiválasztja a külső táblából származó adatokat, 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 az elutasítási küszöbérték túllépéséig. 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álérték, nem százalék. A lekérdezés sikertelen lesz, ha az elutasított sorok száma meghaladja a reject_value.

Ha például REJECT_VALUE = 5 és REJECT_TYPE = value, a SELECT lekérdezés ö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 sikertelen lesz, ha a sikertelen sorok százalékos 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.

REJECT_TYPE = érték esetén reject_value 0 és 2 147 483 647 közötti egész számnak kell lennie.

REJECT_TYPE = százalékérték esetén reject_value 0 és 100 közötti lebegőpontosnak kell lennie.

REJECT_SAMPLE_VALUE = reject_sample_value

Ez az attribútum REJECT_TYPE = százalékos érték megadásakor szükséges. 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 = 1000, a 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.

Példa:

Ez a példa bemutatja, hogyan működik együtt a három REJECT beállítás. Ha például REJECT_TYPE = százalék, 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. Egyező sorokat ad vissza a rendszer, mielőtt 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 = címtárhely

A következőkre vonatkozik: SQL Server 2019 CU6 és újabb verzióira, az Azure Synapse Analyticsre.

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 (Transact-SQL).

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:

• TÁBLA LÉTREHOZÁSA
• SÉMA MÓDOSÍTÁSA
• KÜLSŐ ADATFORRÁS MÓDOSÍTÁSA
• KÜLSŐ FÁJLFORMÁTUM MÓDOSÍTÁSA (csak a Hadoop és az Azure Storage külső adatforrásokra vonatkozik)
• CONTROL DATABASE (csak a Hadoopra és az Azure Storage külső adatforrásokra vonatkozik)

Vegye figyelembe, hogy a CREATE EXTERNAL TABLE parancsban használt DATABASE SCOPED HITELESÍTŐ ADATOKban megadott távoli bejelentkezésnek Read engedéllyel kell rendelkeznie a LOCATION paraméterben megadott külső adatforrás elérési útjának/táblájának/gyűjteményének. Ha ezt a KÜLSŐ TÁBLÁt szeretné használni az adatok Hadoopba vagy Azure Storage-beli külső adatforrásba való exportálásához, akkor a megadott bejelentkezésnek írási engedéllyel kell rendelkeznie a LOCATION-ban megadott útvonalon. 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 KÜLSŐ ADATFORRÁS engedély lehetővé teszi bármely egyszerű felhasználó számára, hogy külső adatforrás-objektumokat hozzon létre és módosítson, ezért lehetővé teszi az adatbázis hatókörébe tartozó összes hitelesítő adat 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 sikertelen lesz, é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 a SELECT INTO FROM EXTERNAL TABLE) a 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 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. Az engedélyezéshez adja meg a Hadoop erőforrás-kezelő helybeállítását a KÜLSŐ ADATFORRÁS LÉTREHOZÁSA (Transact-SQL) mezőben.

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 az SQL Server közvetlen felügyelete alatt vannak, külső folyamattal bármikor módosíthatók vagy eltávolíthatók. 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 (Transact-SQL).

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 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 a PolyBase külső tábláiban:

• 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

Ha tömböket tartalmazó MongoDB-gyűjteményekhez szeretne külső táblákat létrehozni, az Azure Data Studio Data Virtualization bővítményével hozzon létre egy CREATE EXTERNAL TABLE utasítást a MongoDB-hez készült PolyBase ODBC-illesztő által észlelt séma alapján. Az illesztő automatikusan végrehajtja az összeolvasztási műveleteket. Másik lehetőségként a sp_data_source_objects (Transact-SQL) is használhatja a gyűjteményséma (oszlopok) észleléséhez és a külső tábla manuális létrehozásához. 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. Az Azure Data Studio Adatvirtualizálási bővítménye és sp_data_source_table_columns ugyanazokat a belső tárolt eljárásokat használják a külső séma lekérdezéséhez.

Zár

Megosztott zárolás a 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 (Transact-SQL) és CREATE EXTERNAL FILE FORMAT (Transact-SQL).

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. 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 (Transact-SQL) és CREATE EXTERNAL FILE FORMAT (Transact-SQL).

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. 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 (Transact-SQL) és CREATE EXTERNAL FILE FORMAT (Transact-SQL).

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-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 cs
JOIN UrlDescription 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 (Transact-SQL) és CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

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

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

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

  -- 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. Külső tábla létrehozása a Teradata számára

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

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

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)
• Data Virtualization bővítmény az Azure Data Studio