CREATE EXTERNAL TABLE (Transact-SQL) - Sistema piattaforma di analisi (PDW)

Si applica a:Piattaforma di strumenti analitici (PDW)

Crea una tabella esterna.

Usare una tabella esterna per:

• Eseguire query sui dati di Hadoop o di Archiviazione BLOB di Azure con istruzioni Transact-SQL.
• Importare e archiviare dati da Hadoop o Archiviazione BLOB di Azure nel sistema della piattaforma di analisi.

Vedere anche CREATE EXTERNAL DATA SOURCE e DROP EXTERNAL TABLE.

Sintassi

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ , ...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
[ ; ]

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

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

}

Arguments

{ nome_database.nome_schema.nome_tabella | nome_schema.nome_tabella | nome_tabella }

Nome della tabella da creare, composto da una, due o tre parti.

Per una tabella esterna, Analytics Platform System archivia solo i metadati della tabella insieme alle statistiche di base relative al file o alla cartella a cui si fa riferimento in Hadoop o Archiviazione BLOB di Azure. I dati effettivi non vengono spostati o archiviati nella piattaforma di strumenti analitici.

< > column_definition [ ,... n ]

CREATE EXTERNAL TABLE supporta la possibilità di configurare il nome della colonna, il tipo di dati, il supporto di valori Null e le regole di confronto. Non è possibile usare in DEFAULT CONSTRAINT tabelle esterne.

Le definizioni di colonna, inclusi i tipi di dati e il numero di colonne, devono corrispondere ai dati nei file esterni. In caso di mancata corrispondenza, le righe di file vengono rifiutate quando si eseguono query sui dati effettivi.

POSIZIONE = 'folder_or_filepath'

Specifica la cartella o il percorso e il nome del file per i dati effettivi in Hadoop o in Archiviazione BLOB di Azure. Il percorso inizia dalla cartella radice. La cartella radice è la posizione dei dati specificata nell'origine dati esterna.

Nel sistema Analytics Platform, l'istruzione CREATE EXTERNAL TABLE AS SELECT (CETAS) crea il percorso e la cartella se non esistono. CREATE EXTERNAL TABLE non crea il percorso e la cartella.

Se si specifica LOCATION di essere una cartella, una query PolyBase selezionata dalla tabella esterna recupera i file dalla cartella e tutte le relative sottocartelle. Proprio come Hadoop, PolyBase non restituisce le cartelle nascoste. Non restituisce anche file per i quali il nome del file inizia con una sottolineatura (_) o un punto (.).

Nell'esempio di immagine seguente, se LOCATION='/webdata/', una query PolyBase restituisce righe da mydata.txt e mydata2.txt. Non viene restituito mydata3.txt perché si trova in una sottocartella di una cartella nascosta e non viene restituito _hidden.txt perché si tratta di un file nascosto.

Per modificare il valore predefinito e leggere solo dalla cartella radice, impostare l'attributo <polybase.recursive.traversal> su 'false' nel core-site.xml file di configurazione. Questo file si trova nella <SqlBinRoot>\PolyBase\Hadoop\Conf\bin radice di SQL Server. Ad esempio: C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn\.

DATA_SOURCE = external_data_source_name

Specifica il nome dell'origine dati esterna che contiene il percorso dei dati esterni. Questo percorso è hadoop o Archiviazione BLOB di Azure. Per creare una fonte di dati esterna, usa CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Specifica il nome dell'oggetto formato file esterno in cui sono archiviati il tipo di file e il metodo di compressione per i dati esterni. Per creare un formato di file esterno, usa CREATE EXTERNAL FILE FORMAT.

Opzioni REJECT

Questa opzione può essere usata solo con origini dati esterne in cui TYPE = HADOOP.

È possibile specificare i parametri di rifiuto che determinano il modo in cui PolyBase gestisce i record dirty recuperati dall'origine dati esterna. Un record di dati è considerato "dirty" se i tipi di dati effettivi o il numero di colonne non corrispondono alle definizioni di colonna della tabella esterna.

Se non si specificano o si modificano i valori di rifiuto, PolyBase usa i valori predefiniti. Queste informazioni sui parametri di rifiuto vengono archiviate come metadati aggiuntivi quando si crea una tabella esterna con CREATE EXTERNAL TABLE un'istruzione . Quando un'istruzione o SELECT un'istruzione futura SELECT INTO SELECT seleziona i dati dalla tabella esterna, PolyBase usa le opzioni di rifiuto per determinare il numero o la percentuale di righe che possono essere rifiutate prima che la query effettiva abbia esito negativo. La query restituisce risultati (parziali) fino a quando non viene superata la soglia di rifiuto. Fallisce quindi con il messaggio di errore appropriato.

REJECT_TYPE = { valore | percentuale }

Indica se l'opzione REJECT_VALUE viene specificata come valore letterale o come percentuale.

• value

  REJECT_VALUE è un valore letterale, non una percentuale. La query PolyBase ha esito negativo quando il numero di righe rifiutate supera reject_value.

  Ad esempio, se REJECT_VALUE = 5 e REJECT_TYPE = value, la query PolyBase SELECT ha esito negativo dopo il rifiuto di cinque righe.

• percentuale

  REJECT_VALUE è una percentuale, non un valore letterale. Una query PolyBase ha esito negativo quando la percentuale di righe non riuscite supera reject_value. La percentuale di righe con esito negativo viene calcolata a intervalli.

REJECT_VALUE = reject_value

Specifica il valore o la percentuale di righe che possono essere rifiutate prima che la query abbia esito negativo.

Per REJECT_TYPE = value, reject_value deve essere un numero intero compreso tra 0 e 2.147.483.647.

Per REJECT_TYPE = percentage, reject_value deve essere un valore float compreso tra 0 e 100.

REJECT_SAMPLE_VALUE = reject_sample_value

Questo attributo è obbligatorio quando si specifica REJECT_TYPE = percentage. Determina il numero di righe che si deve tentare di recuperare prima che PolyBase ricalcoli la percentuale di righe rifiutate.

Il parametro reject_sample_value deve essere un numero intero compreso tra 0 e 2.147.483.647.

Ad esempio, se REJECT_SAMPLE_VALUE = 1000, PolyBase calcola la percentuale di righe non riuscite dopo aver tentato di importare 1.000 righe dal file di dati esterno. Se la percentuale di righe non riuscite è minore di reject_value, PolyBase tenta di recuperare altre 1.000 righe. Continua a ricalcolare la percentuale di righe non riuscite dopo aver tentato di importare ogni 1.000 righe aggiuntive.

Example

Questo esempio mostra come interagiscono tra loro le tre REJECT opzioni. Ad esempio, se REJECT_TYPE = percentage, REJECT_VALUE = 30e REJECT_SAMPLE_VALUE = 100, potrebbe verificarsi lo scenario seguente:

• PolyBase tenta di recuperare le prime 100 righe di cui 25 avranno esito negativo e 75 esito positivo.
• La percentuale di righe con esito negativo viene calcolata come 25%, che è minore del valore di rifiuto pari al 30%. Di conseguenza, PolyBase continua a recuperare i dati dall'origine dati esterna.
• PolyBase tenta di caricare le 100 righe successive: questa volta 25 righe hanno esito positivo e 75 righe hanno esito negativo.
• Percentuale di righe con esito negativo viene ricalcolata come 50%. La percentuale di righe con esito negativo ha superato il valore di rifiuto del 30%.
• La query PolyBase ha esito negativo con il 50% di righe rifiutate dopo aver tentato di restituire le prime 200 righe. Le righe corrispondenti vengono restituite prima che la query PolyBase rilevi che la soglia di rifiuto è stata superata.

Autorizzazioni

Richiede queste autorizzazioni utente:

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT
• CONTROL DATABASE

L'account di accesso che crea l'origine dati esterna deve avere l'autorizzazione per leggere e scrivere nell'origine dati esterna, che si trova in Hadoop o archiviazione BLOB di Azure.

Gestione degli errori

Durante l'esecuzione dell'istruzione CREATE EXTERNAL TABLE, PolyBase tenta di connettersi all'origine dati esterna. Se il tentativo di connessione non riesce, l'istruzione ha esito negativo e la tabella esterna non viene creata. La conferma dell'esito negativo del comando può richiedere almeno un minuto perché PolyBase ritenta la connessione prima di stabilire che la query non riesce.

Osservazioni:

Negli scenari di query ad hoc, ad esempio SELECT FROM EXTERNAL TABLE, PolyBase archivia le righe recuperate dall'origine dati esterna in una tabella temporanea. Dopo il completamento della query, PolyBase rimuove ed elimina la tabella temporanea. Nessun dato permanente viene archiviato nelle tabelle SQL.

Nello scenario di importazione, ad esempio SELECT INTO FROM EXTERNAL TABLE, PolyBase archivia le righe recuperate dall'origine dati esterna come dati permanenti nella tabella SQL. La nuova tabella viene creata durante l'esecuzione della query quando PolyBase recupera i dati esterni.

PolyBase può eseguire il push di parte del calcolo della query in Hadoop per migliorare le prestazioni della query. Questa azione è nota come pushdown del predicato. Per abilitarla, specifica l'opzione di posizione del gestore risorse Hadoop in CREATE EXTERNAL DATA SOURCE.

È possibile creare numerose tabelle esterne che fanno riferimento alle stesse o ad altre origini dati esterne.

Limitations

Poiché i dati per una tabella esterna non sono sotto il controllo di gestione diretto dell'appliance, possono essere modificati o rimossi in qualsiasi momento da un processo esterno. Per questo motivo, non si garantisce che i risultati delle query in una tabella esterna siano deterministici. La stessa query può restituire risultati diversi ogni volta che viene eseguita su una tabella esterna. Analogamente, una query può non riuscire se i dati esterni vengono spostati o rimossi.

È possibile creare più tabelle esterne che fanno tutte riferimento a origini dati esterne differenti. Se si eseguono contemporaneamente query in diverse origini dati Hadoop, ogni origine Hadoop deve usare la stessa impostazione di configurazione del server di "connettività Hadoop". Ad esempio, non è possibile eseguire contemporaneamente una query su un cluster Cloudera Hadoop e un cluster Hortonworks Hadoop poiché usano impostazioni di configurazione diverse. Per le impostazioni di configurazione e le combinazioni supportate, vedere Configurazione della connettività PolyBase.

Solo queste istruzioni Data Definition Language (DDL) sono consentite per le tabelle esterne:

• CREATE TABLE e DROP TABLE
• CREATE STATISTICS e DROP STATISTICS
• CREATE VIEW e DROP VIEW

Costrutti e operazioni non supportati:

• Vincolo DEFAULT per le colonne di tabella esterne
• Operazioni di eliminazione, inserimento e aggiornamento di Data Manipulation Language (DML)
• Maschera dati dinamica nelle colonne della tabella esterna

Limitazioni delle query

PolyBase può utilizzare al massimo 33.000 file per cartella durante l'esecuzione di 32 query PolyBase simultanee. Questo numero massimo include i file e le sottocartelle presenti in ogni cartella HDFS. Se il livello di concorrenza è inferiore a 32, un utente può eseguire le query PolyBase sulle cartelle in HDFS che contengono più di 33.000 file. È consigliabile usare percorsi brevi per i file esterni e non più di 30.000 file per ogni cartella HDFS. Quando si fa riferimento a troppi file, potrebbe verificarsi un'eccezione di memoria insufficiente in Java Virtual Machine (JVM).

Limitazioni della larghezza della tabella

In SQL Server 2016 (13.x), PolyBase ha un limite di larghezza di riga di 32 KB in base alle dimensioni massime di una singola riga valida per definizione di tabella. Se la somma dello schema di colonne è maggiore di 32 KB, PolyBase non sarà in grado di eseguire query sui dati.

In Azure Synapse Analytics questa limitazione è stata aumentata a 1 MB.

Limitazioni dei tipi di dati

Non è possibile usare i tipi di dati seguenti nelle tabelle esterne PolyBase:

• geography
• geometry
• hierarchyid
• image
• text
• ntext
• xml
• Qualsiasi tipo definito dall'utente

Bloccaggio

Blocco condiviso sull'oggetto SCHEMARESOLUTION .

Security

I file di dati per una tabella esterna vengono archiviati in Hadoop o Archiviazione BLOB di Azure. Questi file di dati vengono creati e gestiti dai processi dell'utente, È responsabilità dell'utente gestire la sicurezza dei dati esterni.

Esempi

A. Unire i dati HDFS ai dati della piattaforma di strumenti analitici

SELECT cs.user_ip
FROM ClickStream AS cs
     INNER JOIN [User] AS u
         ON cs.user_ip = u.user_ip
WHERE cs.url = 'www.microsoft.com';

B. Importare i dati delle righe da HDFS in una tabella della piattaforma di strumenti analitici distribuita

CREATE TABLE ClickStream_PDW
WITH (DISTRIBUTION = HASH(url)) AS
SELECT url,
       event_date,
       user_ip
FROM ClickStream;

C. Importare i dati delle righe da HDFS in una tabella della piattaforma di strumenti analitici replicata

CREATE TABLE ClickStream_PDW
WITH (DISTRIBUTION = REPLICATE) AS
SELECT url,
       event_date,
       user_ip
FROM ClickStream;

Contenuti correlati

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)
• CREATE TABLE COME SELECT