Externe tabellen gebruiken met Synapse SQL

Een externe tabel verwijst naar gegevens die zich in Hadoop, Azure Storage Blob of Azure Data Lake Storage bevinden. Externe tabellen worden gebruikt voor het lezen van gegevens uit bestanden of het schrijven van gegevens naar bestanden in Azure Storage. Met Synapse SQL kunt u externe tabellen gebruiken om externe gegevens te lezen met behulp van toegewezen SQL-pool of serverloze SQL-pool.

Afhankelijk van het type externe gegevensbron kunt u twee typen externe tabellen gebruiken:

• Externe Hadoop-tabellen die u kunt gebruiken om gegevens te lezen en exporteren in verschillende gegevensindelingen, zoals CSV, Parquet en ORC. Externe Hadoop-tabellen zijn beschikbaar in SQL-groepen, maar ze zijn niet beschikbaar in serverloze SQL pools.
• Native externe tabellen die u kunt gebruiken om gegevens te lezen en exporteren in verschillende gegevensindelingen, zoals CSV en Parquet. Native externe tabellen zijn beschikbaar in serverloze SQL pools en ze zijn in openbare preview in toegewezen SQL pools.

De belangrijkste verschillen tussen Hadoop en systeemeigen externe tabellen worden weergegeven in de volgende tabel:

Externe tabellen in een toegewezen SQL-pool en een serverloze SQL-pool

U kunt externe tabellen gebruiken voor het volgende:

• Query's uitvoeren op Azure Blob Storage en Azure Data Lake Gen2 met Transact-SQL-instructies.
• Sla queryresultaten op in bestanden in Azure Blob Storage of Azure Data Lake Storage met behulp van CETAS.
• Gegevens importeren uit Azure Blob Storage en Azure Data Lake Storage en opslaan in een toegewezen SQL-pool (alleen Hadoop-tabellen in toegewezen pool).

U kunt externe tabellen maken in Synapse SQL pools via de volgende stappen:

1. CREATE EXTERNAL DATA SOURCE om te verwijzen naar een externe Azure-opslag en geef de referentie op die moet worden gebruikt voor toegang tot de opslag.
2. CREATE EXTERNAL FILE FORMAT om de indeling van CSV- of Parquet-bestanden te beschrijven.
3. CREATE EXTERNAL TABLE boven op de bestanden die in de gegevensbron zijn geplaatst met dezelfde bestandsindeling.

Beveiliging

De gebruiker moet SELECT machtigingen hebben voor een externe tabel om de gegevens te lezen. Externe tabellen hebben toegang tot onderliggende Azure-opslag met behulp van de referentie voor het databasebereik die is gedefinieerd in de gegevensbron met behulp van de volgende regels:

• Een gegevensbron zonder referenties stelt externe tabellen in staat om toegang te krijgen tot openbaar beschikbare bestanden in Azure Storage.
• De gegevensbron kan een referentie hebben waarmee externe tabellen alleen toegang hebben tot de bestanden in Azure Storage met behulp van een SAS-token of beheerde identiteit voor de werkruimte. Zie het artikel Opslagopslagtoegangsbeheer ontwikkelen voor voorbeelden.

CREATE EXTERNAL DATA SOURCE

Externe gegevensbronnen worden gebruikt om verbinding te maken met opslagaccounts. De volledige documentatie vindt u hier.

Syntaxis voor 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> ]
)
[;]

Argumenten voor CREATE EXTERNAL DATA SOURCE

data_source_name

Hiermee geeft u de naam voor de gegevensbron op die door de gebruiker is gedefinieerd. De naam moet uniek zijn binnen de database.

Locatie

LOCATION = '<prefix>://<path>': geeft het verbindingsprotocol en het pad naar de externe gegevensbron aan. De volgende patronen kunnen worden gebruikt voor locatie:

Met het voorvoegsel https: kunt u een submap gebruiken in het pad.

Referentie

CREDENTIAL = <database scoped credential> is een optionele referentie die wordt gebruikt voor verificatie in Azure Storage. Een externe gegevensbron zonder referentie heeft toegang tot een openbaar opslagaccount of kan de Azure AD-identiteit van de aanroeper gebruiken om toegang te krijgen tot bestanden in de opslag.

• In een toegewezen SQL-pool kan een databasereferentie een aangepaste toepassings-id, beheerde identiteit voor de werkruimte of SAK-sleutel bevatten.
• In serverloze SQL databasereferenties kunnen beheerde identiteiten voor werkruimten of SAS-sleutels worden opgegeven.

TYPE

TYPE = HADOOP is de optie waarmee wordt aangegeven dat java-technologie moet worden gebruikt voor toegang tot onderliggende bestanden. Deze parameter kan niet worden gebruikt in de serverloze SQL-pool die gebruikmaakt van een systeemeigen ingebouwde lezer.

Voorbeeld van CREATE EXTERNAL DATA SOURCE

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

Hiermee maakt u een object voor externe bestandsindeling waarmee externe gegevens worden gedefinieerd die zijn opgeslagen in Azure Blob Storage of Azure Data Lake Storage. Als u een externe tabel wilt maken, moet u een externe bestandsindeling maken. De volledige documentatie vindt u hier.

Door een externe bestandsindeling te maken, geeft u de opmaak aan van de gegevens waarnaar wordt verwezen door een externe tabel.

Syntaxis voor 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'}
}

Argumenten voor CREATE EXTERNAL FILE FORMAT

file_format_name: hiermee geeft u een naam op voor de externe bestandsindeling.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]: hiermee geeft u de indeling van de externe gegevens op.

• PARQUET: Hiermee geeft u een Parquet-indeling op.
• DELIMITEDTEXT: hiermee geeft u een tekstindeling op met kolomscheidingstekens, ook wel veldeindtekens genoemd.

FIELD_TERMINATOR = field_terminator: alleen van toepassing op tekstbestanden met scheidingstekens. Het veldeindteken duidt een of meer tekens aan die het einde van elk veld (kolom) in het tekstbestand met scheidingstekens aangeven. De standaardwaarde is het sluisteken (ꞌ|ꞌ).

Voorbeelden:

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

STRING_DELIMITER = string_delimiter: hiermee geeft u het veldeindteken op voor gegevens van het type Tekenreeks (String) in het tekstbestand met scheidingstekens. De tekenreeksafsluiting bevat een of meer tekens en staat tussen enkele aanhalingstekens. De standaardwaarde is een lege tekenreeks ("").

Voorbeelden:

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

FIRST_ROW = First_row_int: hiermee geeft u het rijnummer op dat het eerst wordt gelezen en van toepassing is op alle bestanden. Als u de waarde instelt op twee, wordt de eerste rij in elk bestand (de koprij) overgeslagen wanneer de gegevens worden geladen. Rijen worden overgeslagen op basis van de rij-eindtekens (/r/n, /r, /n) die worden vermeld.

USE_TYPE_DEFAULT = { TRUE | FALSE }: hiermee geeft u op hoe ontbrekende waarden in tekstbestanden met scheidingstekens moeten worden verwerkt bij het ophalen van gegevens uit het tekstbestand.

TRUE: als u gegevens ophaalt uit het tekstbestand, slaat u elke ontbrekende waarde op met behulp van het gegevenstype van de standaardwaarde voor de betreffende kolom in de definitie van de externe tabel. Vervang een ontbrekende waarde bijvoorbeeld door:

• 0 als de kolom is gedefinieerd als een numerieke kolom. Decimale kolommen worden niet ondersteund en veroorzaken een fout.
• Een lege tekenreeks ("") als de kolom een tekenreekskolom is.
• 1900-01-01 als de kolom een datumkolom is.

FALSE: sla alle ontbrekende waarden op als NULL. NULL-waarden die zijn opgeslagen met behulp van het woord NULL in het tekstbestand met scheidingstekens worden geïmporteerd als de tekenreeks NULL.

Codering = {'UTF8' | 'UTF16'} -Serverloze SQL-pool kan met UTF8 en UTF16 gecodeerde tekstbestanden met scheidingstekens lezen.

DATA_COMPRESSION = data_compression_method: met dit argument geeft u de gegevenscompressiemethode voor de externe gegevens op.

Het bestandsindelingstype PARQUET ondersteunt de volgende compressiemethoden:

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

Bij het lezen van externe PARQUET-tabellen wordt dit argument genegeerd, maar het wordt gebruikt bij het schrijven naar externe tabellen met CETAS.

Het bestandsindelingstype DELIMITEDTEXT ondersteunt de volgende compressiemethode:

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

PARSER_VERSION = 'parser_version' hiermee geeft u de parserversie op die moet worden gebruikt bij het lezen van CSV-bestanden. De beschikbare parserversies zijn 1.0 en 2.0 . Deze optie is alleen beschikbaar in serverloze SQL pools.

Voorbeeld voor CREATE EXTERNAL FILE FORMAT

In het volgende voorbeeld wordt een externe bestandsindeling gemaakt voor volkstellingsbestanden:

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

CREATE EXTERNAL TABLE

Met de opdracht CREATE EXTERNAL TABLE maakt u een externe tabel voor Synapse SQL om toegang te krijgen tot gegevens die zijn opgeslagen in Azure Blob Storage of Azure Data Lake Storage.

Syntaxis voor 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'
}   

Argumenten voor CREATE EXTERNAL TABLE

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

De uit een tot drie delen bestaande naam van de tabel die u wilt maken. Voor een externe tabel slaat Synapse SQL pool alleen de metagegevens van de tabel op. Er worden geen werkelijke gegevens verplaatst of opgeslagen in Synapse SQL database.

<column_definition>, ...n ]

CREATE EXTERNAL TABLE ondersteunt de mogelijkheid om de kolomnaam, het gegevenstype en de collatie te configureren. U kunt de STANDAARDBEPERKING niet gebruiken voor externe tabellen.

Bij het lezen van Parquet-bestanden kunt u alleen de kolommen opgeven die u wilt lezen en de rest overslaan.

LOCATION = folder_or_filepath

Hiermee geeft u de map of het bestandspad en de bestandsnaam op voor de werkelijke gegevens in Azure Blob Storage. De locatie gaat uit van de hoofdmap. De hoofdmap is de gegevenslocatie die is opgegeven in de externe gegevensbron.

In tegenstelling tot externe Hadoop-tabellen retourneren native externe tabellen geen submappen, tenzij u /** aan het einde van het pad opgeeft. Als in dit voorbeeld LOCATION='/webdata/' een serverloze SQL poolquery wordt uitgevoerd, worden rijen uit de mydata.txt. Het retourneert niet mydata2.txt en mydata3.txt omdat deze bestanden zich in een submap bevinden. Hadoop-tabellen retourneren alle bestanden in een submap.

Zowel Hadoop als native externe tabellen slaan de bestanden over met de namen die beginnen met een onderstreping (_) of een punt (.).

DATA_SOURCE = external_data_source_name

Hiermee wordt de naam opgegeven van de externe gegevensbron die de locatie van de externe gegevens bevat. Als u een externe gegevensbron wilt maken, gebruikt u CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

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

Weigeringsopties

U kunt afwijzingsparameters opgeven die bepalen hoe de service omgaat met vervuilde records die worden opgehaald uit de externe gegevensbron. Een gegevensrecord wordt beschouwd als 'vervuild' als de werkelijke gegevenstypen niet overeenkomen met de kolomdefinities van de externe tabel.

Wanneer u geen afwijzingsopties opgeeft of wijzigt, gebruikt de service standaardwaarden. Deze informatie over de afwijzingsparameters 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 de service de weigeringsopties om het aantal rijen te bepalen dat kan worden afgewezen voordat de werkelijke query mislukt. De query retourneerde (gedeeltelijke) resultaten totdat de drempelwaarde voor weigeren is overschreden. Het mislukt vervolgens met het juiste foutbericht.

REJECT_TYPE = waarde

Dit is momenteel de enige ondersteunde waarde. Verduidelijkt dat de REJECT_VALUE optie is opgegeven als een letterlijke waarde.

waarde

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

Als u bijvoorbeeld REJECT_VALUE = 5 en REJECT_TYPE = waarde, mislukt de SELECT-query nadat vijf rijen zijn afgewezen.

REJECT_VALUE = reject_value

Hiermee geeft u het aantal 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.

REJECTED_ROW_LOCATION = Maplocatie

Hiermee geeft u de map in de externe gegevensbron op dat de afgewezen rijen en het bijbehorende foutbestand moeten worden geschreven. Als het opgegeven pad niet bestaat, maakt de service er namens u een. Er wordt een onderliggende map gemaakt met de naam 'rejectedrows'. Het teken '' zorgt ervoor dat de map een escape-teken krijgt voor andere gegevensverwerking, tenzij expliciet de naam is opgenomen in de locatieparameter. In deze map is een map gemaakt op basis van het tijdstip van verzending van de belasting in de indeling YearMonthDay_HourMinuteSecond_StatementID (bijvoorbeeld 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891). U kunt de instructie-id gebruiken om de map te correleren met de query die de id heeft gegenereerd. In deze map worden twee bestanden geschreven: het bestand error.json en het gegevensbestand.

het bestand error.json bevat een JSON-matrix met aangetroffen fouten met betrekking tot geweigerde rijen. Elk element dat de fout vertegenwoordigt, bevat de volgende kenmerken:

TABLE_OPTIONS

TABLE_OPTIONS = json-opties: hiermee geeft u de set opties op die beschrijven hoe de onderliggende bestanden moeten worden gelezen. Op dit moment is de enige beschikbare optie die de externe tabel opdracht geeft om de updates te negeren die zijn aangebracht op de onderliggende bestanden, zelfs als dit tot inconsistente leesbewerkingen kan "READ_OPTIONS":["ALLOW_INCONSISTENT_READS"] leiden. Gebruik deze optie alleen in speciale gevallen waarin u vaak bestanden hebt toegevoegd. Deze optie is beschikbaar in serverloze SQL voor CSV-indeling.

Machtigingen voor CREATE EXTERNAL TABLE

Als u een externe tabel wilt selecteren, moet u over de juiste referenties met weergave- en leesmachtigingen beschikken.

Voorbeeld van CREATE EXTERNAL TABLE

In het volgende voorbeeld wordt een externe tabel gemaakt. Hiermee wordt de eerste rij geretourneerd:

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

Externe tabellen maken en hierop query's uitvoeren via een bestand in Azure Data Lake

Met data lake-verkenningsmogelijkheden van Synapse Studio kunt u nu een externe tabel maken en er query's op uitvoeren met behulp van een Synapse SQL-pool met een eenvoudige rechtermuisknop op het bestand. De mogelijkheid om vanuit het ADLS Gen2-opslagaccount met één klik externe tabellen te maken wordt alleen ondersteund voor Parquet-bestanden.

Vereisten

• U moet toegang hebben tot de werkruimte met ten minste de toegangsrol voor het ADLS Gen2-account of Access Control Lists (ACL) waarmee u query's kunt uitvoeren op Storage Blob Data Contributor de bestanden.

• U moet ten minste machtigingen hebben om externe tabellen te maken en op te vragen in de Synapse SQL-pool (toegewezen of serverloos).

Selecteer in het deelvenster Gegevens het bestand waaruit u de externe tabel wilt maken:

Er wordt een dialoogvenster geopend. Selecteer toegewezen SQL-pool of serverloze SQL-pool, geef de tabel een naam en selecteer Script openen:

Het SQL-script wordt automatisch gegenereerd, waarbij het schema wordt afgeleid van het bestand:

Voer het script uit. Het script voert automatisch een Select Top 100 *. uit:

De externe tabel wordt nu gemaakt. In het vervolg kan de gebruiker de inhoud van deze externe tabel bekijken door rechtstreeks vanuit het deelvenster Gegevens query's hierop uit te voeren:

Volgende stappen

Zie het CETAS-artikel voor het opslaan van queryresultaten in een externe tabel in Azure Storage. U kunt ook beginnen met het uitvoeren van query's op externe tabellen van Apache Spark voor Azure Synapse.