Använda externa tabeller med Synapse-SQL

En extern tabell pekar på data som finns i Hadoop, Azure Storage blob eller Azure Data Lake Storage. Externa tabeller används för att läsa data från filer eller skriva data till filer i Azure Storage. Med Synapse SQL kan du använda externa tabeller för att läsa externa data med hjälp av dedikerad SQL-pool eller serverlös SQL pool.

Beroende på typen av extern datakälla kan du använda två typer av externa tabeller:

• Externa Hadoop-tabeller som du kan använda för att läsa och exportera data i olika dataformat som CSV, Parquet och ORC. Externa Hadoop-tabeller är tillgängliga i dedikerade SQL pooler, men de är inte tillgängliga i serverlösa SQL pooler.
• Interna externa tabeller som du kan använda för att läsa och exportera data i olika dataformat, till exempel CSV och Parquet. Interna externa tabeller är tillgängliga i serverlösa SQL och de finns i offentlig förhandsversion i dedikerade SQL pooler.

De viktigaste skillnaderna mellan Hadoop och interna externa tabeller visas i följande tabell:

Externa tabeller i dedikerad SQL och serverlös SQL pool

Du kan använda externa tabeller för att:

• Fråga Azure Blob Storage Azure Data Lake Gen2 med Transact-SQL-instruktioner.
• Lagra frågeresultat till filer i Azure Blob Storage eller Azure Data Lake Storage med HJÄLP av CETAS.
• Importera data från Azure Blob Storage och Azure Data Lake Storage och lagra dem i en dedikerad SQL-pool (endast Hadoop-tabeller i dedikerad pool).

Du kan skapa externa tabeller i Synapse SQL pooler med följande steg:

1. SKAPA EXTERN DATAKÄLLA för att referera till en extern Azure-lagring och ange de autentiseringsuppgifter som ska användas för åtkomst till lagringen.
2. SKAPA EXTERNT FILFORMAT för att beskriva formatet för CSV- eller Parquet-filer.
3. CREATE EXTERNAL TABLE ovanpå filerna som placerats på datakällan med samma filformat.

Säkerhet

Användaren måste ha SELECT behörighet för en extern tabell för att kunna läsa data. Externa tabeller har åtkomst till underliggande Azure-lagring med hjälp av databasomfångsuppgifterna som definierats i datakällan med hjälp av följande regler:

• Datakälla utan autentiseringsuppgifter gör att externa tabeller kan komma åt offentligt tillgängliga filer i Azure Storage.
• Datakällan kan ha autentiseringsuppgifter som gör att externa tabeller endast kan komma åt filer i Azure Storage med hjälp av SAS-token eller hanterad identitet för arbetsytan – Exempel finns i artikeln Utveckla lagringsfiler med åtkomstkontroll för lagring.

SKAPA EN EXTERN DATAKÄLLA

Externa datakällor används för att ansluta till lagringskonton. Den fullständiga dokumentationen beskrivs här.

Syntax för CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
)
[;]

Argument för CREATE EXTERNAL DATA SOURCE

data_source_name

Anger det användardefinierade namnet för datakällan. Namnet måste vara unikt i databasen.

Location

LOCATION = '<prefix>://<path>' – Tillhandahåller anslutningsprotokollet och sökvägen till den externa datakällan. Följande mönster kan användas på plats:

https: med prefixet kan du använda undermappen i sökvägen.

Autentiseringsuppgift

CREDENTIAL = <database scoped credential> är valfria autentiseringsuppgifter som ska användas för att autentisera i Azure Storage. Extern datakälla utan autentiseringsuppgifter kan komma åt ett offentligt lagringskonto eller använda anroparens Azure AD-identitet för att komma åt filer i lagringen.

• I dedikerad SQL-pool kan databasomfångsindelade autentiseringsuppgifter ange anpassad programidentitet, hanterad identitet för arbetsytan eller SAK-nyckel.
• I en serverlös SQL kan databasomfångs-autentiseringsuppgifter ange arbetsytans hanterade identitet eller SAS-nyckel.

TYP

TYPE = HADOOP är det alternativ som anger att Java-baserad teknik ska användas för att komma åt underliggande filer. Den här parametern kan inte användas i en serverlös SQL som använder inbyggd inbyggd läsare.

Exempel för CREATE EXTERNAL DATA SOURCE (SKAPA EXTERN DATAKÄLLA)

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

CREATE EXTERNAL FILE FORMAT

Skapar ett externt filformatobjekt som definierar externa data som lagras i Azure Blob Storage eller Azure Data Lake Storage. Att skapa ett externt filformat är en förutsättning för att skapa en extern tabell. Den fullständiga dokumentationen finns här.

Genom att skapa ett externt filformat anger du den faktiska layouten för de data som en extern tabell refererar till.

Syntax för CREATE EXTERNAL FILE FORMAT

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | First_Row = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | Encoding = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}
}

Argument för CREATE EXTERNAL FILE FORMAT

file_format_name- Anger ett namn för det externa filformatet.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]- Anger formatet för externa data.

• PARQUET – Anger ett Parquet-format.
• DELIMITEDTEXT – Anger ett textformat med kolumnavgränsare, som även kallas fältavgränsare.

FIELD_TERMINATOR = field_terminator – Gäller endast för avgränsade textfiler. Fältavgränsaren anger ett eller flera tecken som markerar slutet av varje fält (kolumn) i den textavgränsade filen. Standardvärdet är pipe-tecknet (ꞌ|ꞌ).

Exempel:

• FIELD_TERMINATOR = "|"
• FIELD_TERMINATOR = ' '
• FIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter – Anger fältavgränsaren för data av typen sträng i den textavgränsade filen. Strängavgränsaren är ett eller flera tecken lång och omges av enkla citattecken. Standardvärdet är den tomma strängen ("").

Exempel:

• STRING_DELIMITER = """
• STRING_DELIMITER = *'
• STRING_DELIMITER = ꞌ,ꞌ

FIRST_ROW = First_row_int – Anger radnumret som läses först och gäller för alla filer. Om du anger värdet till två hoppas den första raden i varje fil (rubrikrad) över när data läses in. Rader hoppas över baserat på förekomsten av radterminatorer (/r/n, /r, /n).

USE_TYPE_DEFAULT = { TRUE | FALSE } - Anger hur du hanterar saknade värden i avgränsade textfiler när data hämtas från textfilen.

TRUE – Om du hämtar data från textfilen lagrar du varje värde som saknas med hjälp av standardvärdets datatyp för motsvarande kolumn i definitionen för den externa tabellen. Ersätt till exempel ett värde som saknas med:

• 0 om kolumnen definieras som en numerisk kolumn. Decimalkolumner stöds inte och kommer att orsaka ett fel.
• Tom sträng ("") om kolumnen är en strängkolumn.
• 1900-01-01 om kolumnen är en datumkolumn.

FALSE – Lagra alla saknade värden som NULL. Alla NULL-värden som lagras med hjälp av ordet NULL i den avgränsade textfilen importeras som strängen "NULL".

Encoding = {'UTF8' | "UTF16" – Serverlös SQL kan läsa UTF8- och UTF16-kodade avgränsade textfiler.

DATA_COMPRESSION = data_compression_method – Det här argumentet anger datakomprimeringsmetoden för externa data.

Filformattypen PARQUET stöder följande komprimeringsmetoder:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

Vid läsning från externa PARQUET-tabeller ignoreras det här argumentet, men används när du skriver till externa tabeller med HJÄLP av CETAS.

Filformattypen DELIMITEDTEXT stöder följande komprimeringsmetod:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = "parser_version" Anger vilken parserversion som ska användas vid läsning av CSV-filer. De tillgängliga parserversionerna är 1.0 och 2.0 . Det här alternativet är endast tillgängligt i serverlösa SQL pooler.

Exempel för CREATE EXTERNAL FILE FORMAT (SKAPA EXTERNT FILFORMAT)

I följande exempel skapas ett externt filformat för censusfiler:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

SKAPA EXTERN TABELL

Kommandot CREATE EXTERNAL TABLE skapar en extern tabell för Synapse SQL för att komma åt data som lagras i Azure Blob Storage eller Azure Data Lake Storage.

Syntax för CREATE 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
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ] 
    )
[;] 

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

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

Argument CREATE EXTERNAL TABLE

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

Namnet på tabellen i en till tre delar som ska skapas. För en extern tabell lagrar Synapse SQL poolen endast tabellmetadata. Inga faktiska data flyttas eller lagras i Synapse SQL databasen.

<column_definition>, ... n ]

CREATE EXTERNAL TABLE stöder möjligheten att konfigurera kolumnnamn, datatyp och sortering. Du kan inte använda DEFAULT CONSTRAINT för externa tabeller.

När du läser från Parquet-filer kan du bara ange de kolumner som du vill läsa och hoppa över resten.

LOCATION ='folder_or_filepath'

Anger mappen eller filsökvägen och filnamnet för faktiska data i Azure Blob Storage. Platsen börjar från rotmappen. Rotmappen är den dataplats som anges i den externa datakällan.

Till skillnad från externa Hadoop-tabeller returnerar interna externa tabeller inte undermappar om du inte anger /** i slutet av sökvägen. I det här exemplet, om LOCATION='/webdata/', en serverlös SQL-poolfråga, returnerar rader från mydata.txt. Den returnerar inte mydata2.txt och mydata3.txt eftersom de finns i en undermapp. Hadoop-tabeller returnerar alla filer i alla undermappar.

Både Hadoop och interna externa tabeller hoppar över filerna med de namn som börjar med en understrykning (_) eller en punkt (.).

DATA_SOURCE = external_data_source_name

Anger namnet på den externa datakälla som innehåller platsen för externa data. Om du vill skapa en extern datakälla använder du CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Anger namnet på det externa filformatsobjekt som lagrar filtypen och komprimeringsmetoden för externa data. Om du vill skapa ett externt filformat använder du CREATE EXTERNAL FILE FORMAT.

Avvisar alternativ

Du kan ange avslagsparametrar som avgör hur tjänsten ska hantera felkorsade poster som hämtas från den externa datakällan. En datapost anses vara "dirty" om faktiska datatyper inte matchar kolumndefinitionerna för den externa tabellen.

När du inte anger eller ändrar avvisar alternativ använder tjänsten standardvärden. Den här informationen om avslagsparametrarna lagras som ytterligare metadata när du skapar en extern tabell med instruktionen CREATE EXTERNAL TABLE. När en framtida SELECT-instruktion eller SELECT INTO SELECT-instruktion väljer data från den externa tabellen använder tjänsten avvisaralternativen för att fastställa antalet rader som kan avvisas innan den faktiska frågan misslyckas. Frågan returnerar (partiella) resultat tills tröskelvärdet för avvisar överskrids. Det misslyckas sedan med lämpligt felmeddelande.

REJECT_TYPE = värde

Detta är det enda värde som stöds för tillfället. Förtydligar att REJECT_VALUE anges som ett literalvärde.

värde

REJECT_VALUE är ett literalvärde. Frågan misslyckas när antalet avvisade rader överskrider reject_value.

Om du till exempel REJECT_VALUE = 5 REJECT_TYPE = värdet misslyckas SELECT-frågan när fem rader har avvisats.

REJECT_VALUE = reject_value

Anger antalet rader som kan avvisas innan frågan misslyckas.

För REJECT_TYPE = värde reject_value ett heltal mellan 0 och 2 147 483 647.

REJECTED_ROW_LOCATION = Katalogplats

Anger katalogen i den externa datakällan som de avvisade raderna och motsvarande felfil ska skrivas. Om den angivna sökvägen inte finns skapar tjänsten en för din räkning. En underordnad katalog skapas med namnet "rejectedrows". Tecknet " " garanterar att katalogen är undantagen för annan databearbetning om det inte uttryckligen namnges i platsparametern. I den här katalogen finns det en mapp som skapats baserat på tidpunkten för inläsningen i formatet YearMonthDay_HourMinuteSecond_StatementID (t.ex. 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891). Du kan använda instruktions-ID för att korrelera mappen med frågan som genererade den. I den här mappen skrivs två filer: error.json-filen och datafilen.

filen error.json innehåller json-matrisen med påträffade fel relaterade till avvisade rader. Varje element som representerar fel innehåller följande attribut:

TABLE_OPTIONS

TABLE_OPTIONS = json-alternativ – Anger den uppsättning alternativ som beskriver hur de underliggande filerna ska läsas. För närvarande är det enda tillgängliga alternativet att instruera den externa tabellen att ignorera de uppdateringar som görs på de underliggande filerna, även om detta kan orsaka vissa "READ_OPTIONS":["ALLOW_INCONSISTENT_READS"] inkonsekventa läsåtgärder. Använd bara det här alternativet i särskilda fall där du ofta har bifogat filer. Det här alternativet är tillgängligt i serverlös SQL för CSV-format.

Behörigheter CREATE EXTERNAL TABLE

Om du vill välja från en extern tabell behöver du rätt autentiseringsuppgifter med list- och läsbehörigheter.

Exempel på CREATE EXTERNAL TABLE

I följande exempel skapas en extern tabell. Den returnerar den första raden:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Skapa och fråga externa tabeller från en fil i Azure Data Lake

Med datasjöutforskningsfunktionerna i Synapse Studio kan du nu skapa och fråga en extern tabell med hjälp av Synapse SQL-pool med ett enkelt högerklick på filen. Gesten med ett klick för att skapa externa tabeller ADLS Gen2 lagringskontot stöds endast för Parquet-filer.

Förutsättningar

• Du måste ha åtkomst till arbetsytan med minst åtkomstrollen Storage Blob Data Contributor till ADLS Gen2-kontot eller Access Control-listor (ACL) som gör att du kan köra frågor mot filerna.

• Du måste minst ha behörighet att skapa och fråga externa tabeller i Synapse SQL poolen (dedikerad eller serverlös).

Från panelen Data väljer du den fil som du vill skapa den externa tabellen från:

Ett dialogfönster öppnas. Välj dedikerad SQL eller serverlös SQL, ge tabellen ett namn och välj öppna skript:

Skriptet SQL skapas automatiskt och härgenererar schemat från filen:

Kör skriptet. Skriptet kör automatiskt en Select Top 100 *.:

Den externa tabellen skapas nu. För framtida utforskning av innehållet i den externa tabellen kan användaren fråga den direkt från fönstret Data:

Nästa steg

I CETAS-artikeln finns information om hur du sparar frågeresultat till en extern tabell i Azure Storage. Eller så kan du börja fråga Apache Spark för Azure Synapse externa tabellerna.