OPENROWSET gebruiken met behulp van serverloze SQL-pool in Azure Synapse Analytics

Artikel
09/11/2024

Met de functie OPENROWSET(BULK...) kunt u toegang krijgen tot bestanden in Azure Storage. OPENROWSET leest de inhoud van een externe gegevensbron (bijvoorbeeld een bestand) en retourneert de inhoud als een set rijen. Binnen de resource van serverloze SQL-pool wordt de bulksgewijze rijensetprovider van OPENROWSET benaderd door het aanroepen van de functie OPENROWSET en het opgeven van de optie BULK.

Naar de functie OPENROWSET kan worden verwezen in de FROM-component van een query alsof het een tabelnaam OPENROWSET is. De functie ondersteunt bulkbewerkingen via een ingebouwde BULK-provider waarmee gegevens uit een bestand kunnen worden gelezen om deze vervolgens te retourneren als een rijenset.

Notitie

De functie OPENROWSET wordt niet ondersteund in een toegewezen SQL-pool.

Gegevensbron

De functie OPENROWSET in Synapse SQL leest de inhoud van de bestanden uit een gegevensbron. De gegevensbron is een Azure-opslagaccount waarnaar expliciet kan worden verwezen in de functie OPENROWSET of die kan dynamisch worden afgeleid van de URL van de bestanden die u wilt lezen. De functie OPENROWSET kan optioneel een parameter DATA_SOURCE bevatten om de gegevensbron op te geven die bestanden bevat.

OPENROWSET zonder DATA_SOURCE kan worden gebruikt om de inhoud van de bestanden rechtstreeks te lezen vanaf de URL-locatie die is opgegeven via de optie BULK:
```
SELECT *
FROM OPENROWSET(BULK 'http://<storage account>.dfs.core.windows.net/container/folder/*.parquet',
                FORMAT = 'PARQUET') AS [file]
```

Dit is een snelle en eenvoudige manier om de inhoud van de bestanden te lezen zonder vooraf te configureren. Met deze optie kunt u de basisverificatieoptie gebruiken voor toegang tot de opslag (Microsoft Entra passthrough voor Microsoft Entra-aanmeldingen en SAS-token voor SQL-aanmeldingen).

OPENROWSET met DATA_SOURCE kan worden gebruikt om toegang te krijgen tot bestanden in het opgegeven opslagaccount:
```
SELECT *
FROM OPENROWSET(BULK '/folder/*.parquet',
                DATA_SOURCE='storage', --> Root URL is in LOCATION of DATA SOURCE
                FORMAT = 'PARQUET') AS [file]
```
Met deze optie kunt u de locatie van het opslagaccount in de gegevensbron configureren en de verificatiemethode opgeven die moet worden gebruikt voor toegang tot de opslag.

Belangrijk

OPENROWSET zonder DATA_SOURCE biedt een snelle en eenvoudige manier om toegang te krijgen tot de opslagbestanden, maar met beperkte verificatieopties. Microsoft Entra-principals hebben bijvoorbeeld alleen toegang tot bestanden met hun Microsoft Entra-identiteit of openbaar beschikbare bestanden. Als u krachtigere verificatieopties nodig hebt, gebruikt u DATA_SOURCE optie en definieert u de referenties die u wilt gebruiken voor toegang tot de opslag.

Beveiliging

Een databasegebruiker moet beschikken over de machtiging ADMINISTER BULK OPERATIONS om de functie OPENROWSET te kunnen gebruiken.

De opslagbeheerder moet een gebruiker ook toegang geven tot de bestanden door een geldig SAS-token op te geven of de Microsoft Entra-principal toegang te geven tot opslagbestanden. Meer informatie over toegangsbeheer voor opslag vindt u in dit artikel.

OPENROWSET gebruikt de volgende regels om te bepalen hoe verificatie voor toegang tot opslag moet worden uitgevoerd:

Het verificatiemechanisme OPENROWSET zonder DATA_SOURCE is afhankelijk van het type aanroeper.
- Elke gebruiker kan OPENROWSET gebruiken zonder DATA_SOURCE om openbaar beschikbare bestanden in Azure Storage te lezen.
- Microsoft Entra-aanmeldingen hebben toegang tot beveiligde bestanden met hun eigen Microsoft Entra-identiteit als Azure Storage de Microsoft Entra-gebruiker toegang geeft tot onderliggende bestanden (bijvoorbeeld als de beller machtigingen heeft Storage Reader voor Azure Storage).
- SQL-aanmeldingen kunnen ook OPENROWSET gebruiken zonder DATA_SOURCE om toegang te krijgen tot openbaar beschikbare bestanden, bestanden die zijn beveiligd met een SAS-token of beheerde identiteit van een Synapse-werkruimte. U moet referenties binnen serverbereik maken om toegang tot opslagbestanden toe te staan.
In OPENROWSET met DATA_SOURCE worden verificatiemechanismen gedefinieerd in de referentie binnen databasebereik die is toegewezen aan de gegevensbron waarnaar wordt verwezen. Met deze optie kunt u toegang krijgen tot openbaar beschikbare opslag of toegang krijgen tot opslag met behulp van sas-token, beheerde identiteit van werkruimte of Microsoft Entra-identiteit van beller (als aanroeper Microsoft Entra-principal is). Als DATA_SOURCE verwijst naar Azure-opslag die niet openbaar is, moet u referenties binnen het databasebereik maken en hiernaar verwijzen in DATA SOURCE om toegang tot opslagbestanden te geven.

De aanroeper moet beschikken over de machtiging REFERENCES voor de referenties om deze te kunnen gebruiken voor verificatie bij de opslag.

Syntaxis

--OPENROWSET syntax for reading Parquet or Delta Lake files
OPENROWSET  
( { BULK 'unstructured_data_path' , [DATA_SOURCE = <data source name>, ]
    FORMAT= ['PARQUET' | 'DELTA'] }  
)  
[WITH ( {'column_name' 'column_type' }) ]
[AS] table_alias(column_alias,...n)

--OPENROWSET syntax for reading delimited text files
OPENROWSET  
( { BULK 'unstructured_data_path' , [DATA_SOURCE = <data source name>, ] 
    FORMAT = 'CSV'
    [ <bulk_options> ]
    [ , <reject_options> ] }  
)  
WITH ( {'column_name' 'column_type' [ 'column_ordinal' | 'json_path'] })  
[AS] table_alias(column_alias,...n)
 
<bulk_options> ::=  
[ , FIELDTERMINATOR = 'char' ]    
[ , ROWTERMINATOR = 'char' ] 
[ , ESCAPECHAR = 'char' ] 
[ , FIRSTROW = 'first_row' ]     
[ , FIELDQUOTE = 'quote_characters' ]
[ , DATA_COMPRESSION = 'data_compression_method' ]
[ , PARSER_VERSION = 'parser_version' ]
[ , HEADER_ROW = { TRUE | FALSE } ]
[ , DATAFILETYPE = { 'char' | 'widechar' } ]
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]

<reject_options> ::=  
{  
    | MAXERRORS = reject_value,  
    | ERRORFILE_DATA_SOURCE = <data source name>,
    | ERRORFILE_LOCATION = '/REJECT_Directory'
}

Argumenten

U hebt drie opties voor invoerbestanden die de doelgegevens bevatten voor het uitvoeren van query's. Geldige waarden zijn:

'CSV': een tekstbestand met scheidingstekens voor rijen en kolommen. Elk teken kan worden gebruikt als veldscheidingsteken, zoals TSV: FIELDTERMINATOR = tab.
'PARQUET' - Binair bestand in Parquet-indeling.
'DELTA': een set Parquet-bestanden die zijn ingedeeld in Delta Lake-indeling (preview).

Waarden met lege spaties zijn niet geldig. CSV is bijvoorbeeld geen geldige waarde.

'unstructured_data_path'

Het unstructured_data_path waarmee een pad naar de gegevens wordt vastgesteld, kan een absoluut of relatief pad zijn:

Absoluut pad in de indeling \<prefix>://\<storage_account_path>/\<storage_path> stelt een gebruiker in staat om de bestanden rechtstreeks te lezen.
Relatief pad in de indeling <storage_path> die moet worden gebruikt met de DATA_SOURCE parameter en beschrijft het bestandspatroon binnen de <storage_account_path> locatie die is gedefinieerd in EXTERNAL DATA SOURCE.

Hieronder vindt u de relevante <waarden voor het opslagaccountpad> die worden gekoppeld aan uw specifieke externe gegevensbron.

Externe gegevensbron	Voorvoegsel	Pad van opslagaccount
Azure Blob-opslag	http[s]	<>storage_account.blob.core.windows.net/path/file
Azure Blob-opslag	wasb[s]	<container>@<storage_account.blob.core.windows.net/path/file>
Azure Data Lake Store Gen1	http[s]	<>storage_account.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Store Gen2	http[s]	<>storage_account.dfs.core.windows.net/path/file
Azure Data Lake Store Gen2	abfs[s]	<>file_system@<account_name.dfs.core.windows.net/path/file>

'<storage_path>'

Een pad binnen de opslag dat verwijst naar de map of het bestand dat u wilt lezen. Als het pad naar een container of map verwijst, worden alle bestanden in die specifieke container of map gelezen. Bestanden in submappen worden uitgesloten.

U kunt jokertekens gebruiken om meerdere bestanden of mappen op te geven. Het gebruik van meerdere, niet-opeenvolgende jokertekens is toegestaan. Hieronder ziet u een voorbeeld waarmee alle CSV- bestanden worden gelezen die beginnen met population uit alle mappen die beginnen met /csv/population:
https://sqlondemandstorage.blob.core.windows.net/csv/population*/population*.csv

Als u opgeeft dat unstructured_data_path een map is, haalt een serverloze SQL-pool-query bestanden op uit die map.

U kunt een serverloze SQL-pool instrueren om mappen te doorlopen door /* toe te voegen aan het einde van het pad, zoals in het voorbeeld: https://sqlondemandstorage.blob.core.windows.net/csv/population/**

Notitie

In tegenstelling tot Hadoop en PolyBase, retourneert een serverloze SQL-pool geen submappen tenzij u /* * aan het einde van het pad toevoegt. Net als Hadoop en PolyBase worden er geen bestanden geretourneerd waarvoor de bestandsnaam begint met een onderstreping (_) of een punt (.).

In het onderstaande voorbeeld, als de unstructured_data_path=https://mystorageaccount.dfs.core.windows.net/webdata/, retourneert een serverloze SQL-poolquery rijen uit mydata.txt. Het retourneert niet mydata2.txt en mydata3.txt omdat deze bestanden zich in een submap bevinden.

Recursieve gegevens voor externe tabellen

[WITH ( {'column_name' 'column_type' [ 'column_ordinal'] }) ]

Met de WITH-component kunt u opgeven welke kolommen u uit bestanden wilt lezen.

Als u wilt dat alle kolommen worden gelezen uit CSV-gegevensbestanden, geeft u de kolomnamen en de bijbehorende gegevenstypen op. Als u een subset van deze kolommen wilt opvragen, gebruikt u rangtelwoorden om de kolommen uit de oorspronkelijke gegevensbestanden te kiezen op rangtelwoord. Kolommen worden gebonden op aanduiding via rangnummer. Als HEADER_ROW = TRUE wordt gebruikt, vindt kolombinding plaats op kolomnaam in plaats van rangnummer.

Tip

U kunt ook de WITH-component voor CSV-bestanden weglaten. Gegevenstypen worden automatisch afgeleid van bestandsinhoud. U kunt het argument HEADER_ROW gebruiken om het bestaan van headerrij op te geven. In dat geval worden de kolomnamen afgelezen uit de headerrij. Bekijk automatische schemadetectie voor meer informatie.
Geef voor Parquet- of Delta Lake-bestanden kolomnamen op die overeenkomen met de kolomnamen in de oorspronkelijke gegevensbestanden. Kolommen worden afhankelijk van de naam en zijn hoofdlettergevoelig. Als de WITH-component wordt weggelaten, worden alle kolommen uit Parquet-bestanden geretourneerd.

Belangrijk

Kolomnamen in Parquet- en Delta Lake-bestanden zijn hoofdlettergevoelig. Als u de kolomnaam opgeeft met een andere hoofdletter dan de kolomnaam in de bestanden, worden de NULL waarden voor die kolom geretourneerd.

column_name = De naam voor de uitvoerkolom. Indien opgegeven, overschrijft deze naam de kolomnaam in het bronbestand en de kolomnaam die is opgegeven in het JSON-pad als er een is. Als json_path niet is opgegeven, wordt deze automatisch toegevoegd als $.column_name. Controleer het gedrag van het argument json_path.

column_type = Het gegevenstype voor de uitvoerkolom. De impliciete conversie van het gegevenstype wordt hier uitgevoerd.

column_ordinal = Het rangnummer van de kolom in het bronbestand of de bronbestanden. Dit argument wordt genegeerd voor Parquet-bestanden omdat binding op naam wordt uitgevoerd. In het volgende voorbeeld wordt alleen een tweede kolom geretourneerd uit een CSV-bestand:

WITH (
    --[country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2 2
    --[year] smallint,
    --[population] bigint
)

json_path = expressie van het JSON-pad naar kolom of geneste eigenschap. De standaard voor padenmodus is lax.

Notitie

In de strikte modus zal de query mislukken en een fout weergeven als het gegeven pad niet bestaat. In de lax-modus zal de query slagen en zal de expressie van het JSON-pad worden geëvalueerd als NULL.

<bulk_options>

FIELDTERMINATOR ='field_terminator'

Geeft aan welk teken moet worden gebruikt om het einde van een veld aan te geven. Het standaardeindteken voor velden is een komma (',').

ROWTERMINATOR ='row_terminator''

Geeft aan welk teken moet worden gebruikt om het einde van een rij aan te geven. Als er geen rijeindteken is opgegeven, wordt een van de standaardeindtekens gebruikt. De standaardeindtekens voor PARSER_VERSION = '1.0' zijn \r\n \n en \r. De standaardeindtekens voor PARSER_VERSION = '2.0' zijn \r\n en \n.

Notitie

Wanneer u PARSER_VERSION='1.0' gebruikt en \n (nieuwe regel) opgeeft als het rijeindteken, wordt deze automatisch voorafgegaan door een \r (regelterugloopteken), wat resulteert in een rijeindteken van \r\n.

ESCAPE_CHAR = 'teken'

Het teken in het bestand dat wordt gebruikt om zichzelf te escapen evenals alle scheidingstekens in het bestand. Als het escape-teken wordt gevolgd door een andere waarde dan het teken zelf, of een van de scheidingstekens, wordt het escape-teken genegeerd bij het lezen van de waarde.

De ESCAPECHAR-parameter wordt toegepast, ongeacht of de FIELDQUOTE wel of niet is ingeschakeld. De parameter wordt niet gebruikt om het aanhalingsteken te escapen. Het aanhalingsteken moet worden a”gesloten door een ander aanhalingsteken. Een aanhalingsteken kan alleen in een kolomwaarde worden weer gegeven als de waarde tussen aanhalingstekens staat.

FIRSTROW = 'eerste_rij'

Het nummer van de eerste rij die moet worden geladen. De standaard is 1 en geeft de eerste rij in het opgegeven gegevensbestand aan. De rijnummers worden bepaald door het tellen van het aantal eindtekens voor rijen. FIRSTROW begint bij 1.

FIELDQUOTE = 'aanhalingsteken_veld'

Het teken op dat wordt gebruikt als het aanhalingsteken in het CSV-bestand. Als u niets opgeeft, wordt het aanhalingsteken " gebruikt.

DATA_COMPRESSION = 'methode_voor_gegevenscompressie'

De compressiemethode. Wordt alleen ondersteund in PARSER_VERSION='1.0'. De volgende compressiemethode wordt ondersteund:

GZIP

PARSER_VERSION = 'versie_van_parser'

De parser-versie die moet worden gebruikt bij het lezen van bestanden. De momenteel ondersteunde parser-versies voor CSV zijn 1.0 en 2.0:

PARSER_VERSION = '1.0'
PARSER_VERSION = '2.0'

De CSV-parser versie 1.0 is standaard en zit boordevol functies. Versie 2.0 is gebouwd voor prestaties en biedt geen ondersteuning voor alle opties en coderingen.

Kenmerken van parser-versie 1.0 voor CSV:

De volgende opties worden niet ondersteund: HEADER_ROW.
Standaardeindtekens zijn \r\n, \n en \r.
Als u \n (nieuwe regel) opgeeft als het rijeindteken, wordt deze automatisch voorafgegaan door een \r (regelterugloopteken), wat resulteert in een rijeindteken van \r\n.

Kenmerken van parser-versie 2.0 voor CSV:

Niet alle gegevenstypen worden ondersteund.
De maximale lengte van de tekenkolom is 8000.
De maximale limiet voor de rijgrootte is 8 MB.
De volgende opties worden niet ondersteund: DATA_COMPRESSION.
Een lege tekenreeks tussen aanhalingstekens ("") wordt geïnterpreteerd als een lege tekenreeks.
De optie DATEFORMAT SET wordt niet gehonoreerd.
Ondersteunde indeling voor datumgegevenstype: JJJJ-MM-DD
Ondersteunde indeling voor time-gegevenstype: UU:MM:SS[.fractionele seconden]
Ondersteunde indeling voor DATETIME2 gegevenstype: JJJJ-MM-DD UU:MM:SS[.fractionele seconden]
Standaardeindtekens zijn \r\n en \n.

HEADER_ROW = { TRUE | FALSE }

Hiermee geeft u op of een CSV-bestand veldnamenrij bevat. De standaardwaarde wordt FALSE. ondersteund in PARSER_VERSION='2.0'. Als WAAR is, worden de kolomnamen gelezen uit de eerste rij volgens het argument FIRSTROW. Als TRUE en schema worden opgegeven met behulp van WITH, wordt de binding van kolomnamen uitgevoerd op kolomnaam, niet op rangnummer.

DATAFILETYPE = { 'char' | 'widechar' }

Hiermee geeft u codering op: char wordt gebruikt voor UTF8, widechar wordt gebruikt voor UTF16-bestanden.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Hiermee geeft u de codepagina van de gegevens in het gegevensbestand. De standaardwaarde is 65001 (UTF-8-codering). Bekijk hier meer informatie over deze optie.

ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'

Met deze optie wordt de controle van bestandswijziging uitgeschakeld tijdens de uitvoering van de query en worden de bestanden gelezen die worden bijgewerkt terwijl de query wordt uitgevoerd. Dit is een handige optie wanneer u alleen-lezen bestanden wilt lezen die worden toegevoegd terwijl de query wordt uitgevoerd. In de toevoegbare bestanden wordt de bestaande inhoud niet bijgewerkt en worden alleen nieuwe rijen toegevoegd. Daarom wordt de kans op verkeerde resultaten geminimaliseerd in vergelijking met de bijwerkbare bestanden. Met deze optie kunt u mogelijk de vaak toegevoegde bestanden lezen zonder de fouten te verwerken. Zie meer informatie in de sectie toevoegbare CSV-bestanden opvragen.

Opties voor weigeren

Notitie

De functie Geweigerde rijen bevindt zich in openbare preview. Houd er rekening mee dat de functie geweigerde rijen werkt voor tekstbestanden met scheidingstekens en PARSER_VERSION 1.0.

U kunt geweigerde parameters opgeven die bepalen hoe de service vuile records verwerkt die worden opgehaald uit de externe gegevensbron. Een gegevensrecord wordt beschouwd als 'vuil' als de werkelijke gegevenstypen niet overeenkomen met de kolomdefinities van de externe tabel.

Wanneer u geen opties voor weigeren opgeeft of wijzigt, gebruikt de service standaardwaarden. Service gebruikt de weigeringsopties om het aantal rijen te bepalen dat kan worden geweigerd voordat de werkelijke query mislukt. De query retourneert (gedeeltelijke) resultaten totdat de drempelwaarde voor weigeren is overschreden. Het mislukt vervolgens met het juiste foutbericht.

MAXERRORS = reject_value

Hiermee geeft u het aantal rijen op dat kan worden geweigerd voordat de query mislukt. MAXERRORS moet een geheel getal tussen 0 en 2.147.483.647 zijn.

ERRORFILE_DATA_SOURCE = gegevensbron

Hiermee geeft u de gegevensbron op waarbij geweigerde rijen en het bijbehorende foutbestand moeten worden geschreven.

ERRORFILE_LOCATION = Maplocatie

Hiermee geeft u de map in de DATA_SOURCE, of ERROR_FILE_DATASOURCE indien opgegeven, dat de geweigerde rijen en het bijbehorende foutbestand moeten worden geschreven. Als het opgegeven pad niet bestaat, maakt de service er een namens u. Er wordt een onderliggende map gemaakt met de naam 'rejectedrows'. Het teken '' zorgt ervoor dat de map wordt ontsnapt voor andere gegevensverwerking, tenzij deze expliciet wordt genoemd in de locatieparameter. In deze map is er een map gemaakt op basis van het tijdstip van het verzenden 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 deze heeft gegenereerd. In deze map worden twee bestanden geschreven: error.json bestand en het gegevensbestand.

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

Kenmerk	Beschrijving
Error	Reden waarom rij wordt geweigerd.
Rij	Afgekeurd rijnummer in bestand.
Kolom	Afgekeurd kolomnummer.
Weergegeven als	Geweigerde kolomwaarde. Als de waarde groter is dan 100 tekens, worden alleen de eerste 100 tekens weergegeven.
Bestand	Pad naar het bestand waartoe die rij behoort.

Snel parseren van tekstbestand met scheidingstekens

Er zijn twee parserversies voor tekstbestanden met scheidingstekens die u kunt gebruiken. De CSV-parser versie 1.0 is standaard en bevat een groot aantal functies, terwijl versie 2.0 is gebouwd voor prestaties. Prestatieverbeteringen in parser 2.0 zijn afkomstig van geavanceerde technieken voor parseren en van multithreading. Het verschil in snelheid wordt groter naarmate de bestandsgrootte groeit.

Automatische schemadetectie

U kunt eenvoudig query’s uitvoeren op zowel CSV- als Parquet-bestanden zonder dat u een schema hoeft te kennen of specificeren door de WITH-component weg te laten. Kolomnamen en gegevenstypen worden afgeleid uit bestanden.

Parquet-bestanden bevatten kolommetagegevens, die worden gelezen, typetoewijzingen zijn te vinden in typetoewijzingen voor Parquet. Bekijk het lezen van Parquet-bestanden zonder schema op te geven voor voorbeelden.

Voor de CSV-bestanden kunnen kolomnamen worden gelezen uit de veldnamenrij. U kunt opgeven of de headerrij bestaat met behulp van het argument HEADER_ROW. Als HEADER_ROW = ONWAAR, worden algemene kolomnamen gebruikt: C1, C2, ... Cn waarbij n het aantal kolommen in het bestand is. Gegevenstypen worden afgeleid uit de eerste 100 gegevensrijen. Bekijk het lezen van CSV-bestanden zonder schema op te geven voor voorbeelden.

Houd er rekening mee dat als u het aantal bestanden tegelijk leest, het schema wordt afgeleid van de eerste bestandsservice uit de opslag. Dit kan betekenen dat sommige van de verwachte kolommen worden weggelaten, allemaal omdat het bestand dat door de service wordt gebruikt om het schema te definiëren deze kolommen niet bevat. In dat geval gebruikt u de COMPONENT OPENROWSET WITH.

Belangrijk

Er zijn gevallen waarin het juiste gegevenstype niet kan worden afgeleid omdat er te weinig gegevens zijn en er wordt in plaats daarvan een groter gegevenstype gebruikt. Dit neemt prestatieoverhead met zich mee en is met name belangrijk voor tekenkolommen die worden afgeleid als varchar (8000). Voor optimale prestaties controleert u de uitgestelde gegevenstypen en gebruikt u de juiste gegevenstypen.

Typetoewijzing voor Parquet

Parquet- en Delta Lake-bestanden bevatten typebeschrijvingen voor elke kolom. In de volgende tabel wordt beschreven hoe Parquet-types worden toegewezen aan systeemeigen SQL-typen.

Parquet-type	Logisch type van Parquet (annotatie)	SQL-gegevenstype
BOOLEAN		bit
BINARY / BYTE_ARRAY		varbinary
DOUBLE		zwevend
FLOAT		werkelijk
INT32		int
INT64		bigint
INT96		datetime2
FIXED_LEN_BYTE_ARRAY		binair
BINARY	UTF8	varchar *(UTF8-sortering)
BINARY	STRING	varchar *(UTF8-sortering)
BINARY	ENUM	varchar *(UTF8-sortering)
FIXED_LEN_BYTE_ARRAY	UUID	uniqueidentifier
BINARY	DECIMAL	decimal
BINARY	JSON	varchar(8000) *(UTF8-sortering)
BINARY	BSON	Niet ondersteund
FIXED_LEN_BYTE_ARRAY	DECIMAL	decimal
BYTE_ARRAY	INTERVAL	Niet ondersteund
INT32	INT(8, true)	smallint
INT32	INT(16, true)	smallint
INT32	INT(32, true)	int
INT32	INT(8, false)	tinyint
INT32	INT(16, false)	int
INT32	INT(32, false)	bigint
INT32	DATUM	datum
INT32	DECIMAL	decimal
INT32	TIME (MILLIS)	tijd
INT64	INT(64, true)	bigint
INT64	INT(64, false)	decimal(20,0)
INT64	DECIMAL	decimal
INT64	TIME (MICROS)	tijd
INT64	TIME (NANOS)	Niet ondersteund
INT64	TIMESTAMP (genormaliseerd tot utc) (MILLIS/MICROS)	datetime2
INT64	TIMESTAMP (niet genormaliseerd tot utc) (MILLIS/MICROS)	bigint: zorg ervoor dat u de waarde expliciet aanpast `bigint` met de tijdzone-offset voordat u deze converteert naar een datum/tijd-waarde.
INT64	TIMESTAMP (NANOS)	Niet ondersteund
Complex type	LIST	varchar(8000), geserialiseerd naar JSON
Complex type	MAP	varchar(8000), geserialiseerd naar JSON

Voorbeelden

CSV-bestanden lezen zonder schema op te geven

In het volgende voorbeeld wordt het CSV-bestand dat de headerrij bevat, gelezen zonder dat kolomnamen en gegevenstypen worden opgegeven:

SELECT 
    *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) as [r]

In het volgende voorbeeld wordt het CSV-bestand dat geen headerrij bevat, gelezen zonder dat kolomnamen en gegevenstypen worden opgegeven:

SELECT 
    *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0') as [r]

Parquet-bestanden lezen zonder schema op te geven

In het volgende voorbeeld worden voor de gegevensset Census alle kolommen uit de eerste rij als resultaat gegeven in de Parquet-indeling zonder kolomnamen en gegevenstypen op te geven:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='PARQUET'
    ) AS [r]

Delta Lake-bestanden lezen zonder schema op te geven

In het volgende voorbeeld worden alle kolommen van de eerste rij uit de gegevensset volkstelling geretourneerd, in Delta Lake-indeling en zonder kolomnamen en gegevenstypen op te geven:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='DELTA'
    ) AS [r]

Specifieke kolommen lezen uit CSV-bestand

In het volgende voorbeeld worden slechts twee kolommen met de rangnummers 1 en 4 geretourneerd uit de bestanden population*.csv. Omdat de bestanden geen veldnamenrij bevatten, begint het lezen bij de eerste regel:

SELECT 
    * 
FROM OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/csv/population/population*.csv',
        FORMAT = 'CSV',
        FIRSTROW = 1
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2 1,
    [population] bigint 4
) AS [r]

Specifieke kolommen lezen uit Parquet-bestand

In het volgende voorbeeld worden voor de gegevensset Census slechts twee kolommen uit de eerste rij als resultaat gegeven in de Parquet-indeling:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='PARQUET'
    )
WITH (
    [stateName] VARCHAR (50),
    [population] bigint
) AS [r]

Kolommen opgeven met behulp van JSON-paden

In het volgende voorbeeld ziet u hoe u Expressies van JSON-paden kunt gebruiken in de component WITH. U ziet ook het verschil tussen de strikte en de lax modi:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='PARQUET'
    )
WITH (
    --lax path mode samples
    [stateName] VARCHAR (50), -- this one works as column name casing is valid - it targets the same column as the next one
    [stateName_explicit_path] VARCHAR (50) '$.stateName', -- this one works as column name casing is valid
    [COUNTYNAME] VARCHAR (50), -- STATEname column will contain NULLs only because of wrong casing - it targets the same column as the next one
    [countyName_explicit_path] VARCHAR (50) '$.COUNTYNAME', -- STATEname column will contain NULLS only because of wrong casing and default path mode being lax

    --strict path mode samples
    [population] bigint 'strict $.population' -- this one works as column name casing is valid
    --,[population2] bigint 'strict $.POPULATION' -- this one fails because of wrong casing and strict path mode
)
AS [r]

Meerdere bestanden/mappen opgeven in BULK-pad

In het volgende voorbeeld ziet u hoe u meerdere bestands-/mappaden in bulkparameter kunt gebruiken:

SELECT 
    TOP 10 *
FROM  
    OPENROWSET(
        BULK (
            'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=2000/*.parquet',
            'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=2010/*.parquet'
        ),
        FORMAT='PARQUET'
    )
AS [r]

Volgende stappen

Zie de quickstart voor querygegevensopslag voor meer voorbeelden voor meer informatie over het lezen OPENROWSET van CSV-, PARQUET-, DELTA LAKE- en JSON-bestandsindelingen. Bekijk best practices om optimale prestaties te behalen. U kunt ook lezen hoe u de resultaten van een query kunt opslaan in Azure Storage met behulp van CETAS.

Share via

OPENROWSET gebruiken met behulp van serverloze SQL-pool in Azure Synapse Analytics

Gegevensbron

Beveiliging

Syntaxis

Argumenten

Snel parseren van tekstbestand met scheidingstekens

Automatische schemadetectie

Typetoewijzing voor Parquet

Voorbeelden

CSV-bestanden lezen zonder schema op te geven

Parquet-bestanden lezen zonder schema op te geven

Delta Lake-bestanden lezen zonder schema op te geven

Specifieke kolommen lezen uit CSV-bestand

Specifieke kolommen lezen uit Parquet-bestand

Kolommen opgeven met behulp van JSON-paden

Meerdere bestanden/mappen opgeven in BULK-pad

Volgende stappen

Feedback

Aanvullende resources