CETAS met Synapse SQL

U kunt CETAS (CREATE EXTERNAL TABLE AS SELECT) in toegewezen SQL-pools of serverloze SQL-pools gebruiken om de volgende taken uit te voeren:

• Een externe tabel maken

• De resultaten van de Transact-SQL-instructie SELECT gelijktijdig exporteren naar:

  • Hadoop
  • Azure Storage Blob
  • Azure Data Lake Storage Gen2

CETAS in toegewezen SQL-pools

Bekijk het artikel CREATE EXTERNAL TABLE AS SELECT voor het gebruik en de syntaxis van CETAS in toegewezen SQL-pools. Raadpleeg daarnaast het artikel CREATE TABLE AS SELECT voor meer informatie over CETAS met toegewezen SQL-pools.

CETAS in serverloze SQL-pools

Wanneer u serverloze SQL-pools gebruikt, wordt CETAS gebruikt om een externe tabel te maken en queryresultaten te exporteren naar Azure Storage Blob of Azure Data Lake Storage Gen2.

Raadpleeg CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) voor de volledige syntaxis.

Voorbeelden

In deze voorbeelden wordt CETAS gebruikt om de totale populatie die is samengevoegd op jaar en staat op te slaan in de map aggregated_data die zich in de gegevensbron population_ds bevindt.

Dit voorbeeld is afhankelijk van de referentie, de gegevensbron en de externe bestandsindeling die eerder zijn gemaakt. Raadpleeg het document over externe tabellen. Als u de queryresultaten in een andere map in dezelfde gegevensbron wilt opslaan, wijzigt u het argument LOCATION.

Als u de resultaten in een ander opslagaccount wilt opslaan, moet u voor het argument DATA_SOURCE een andere gegevensbron maken en gebruiken.

Notitie

In de volgende voorbeelden wordt gebruikgemaakt van een openbaar Azure Open Data-opslagaccount. Het is alleen-lezen. Als u deze query's wilt uitvoeren, moet u de gegevensbron opgeven waarvoor u over schrijfmachtigingen beschikt.

-- use CETAS to export select statement with OPENROWSET result to  storage
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM
    OPENROWSET(BULK 'https://azureopendatastorage.dfs.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',
    FORMAT='PARQUET') AS [r]
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

In het volgende voorbeeld wordt een externe tabel gebruikt als de bron voor CETAS. Het is afhankelijk van de referentie, de gegevensbron, de externe bestandsindeling en de externe tabel die eerder zijn gemaakt. Raadpleeg het document over externe tabellen.

-- use CETAS with select from external table
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM census_external_table
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

Algemeen voorbeeld

In dit voorbeeld zien we een voorbeeld van een sjablooncode voor het schrijven van CETAS met een Weergave als bron en het gebruik van beheerde identiteit als verificatie.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'Managed Identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Ondersteunde gegevenstypen

CETAS kan worden gebruikt om resultatensets met de volgende SQL-gegevenstypen op te slaan:

• binair
• varbinary
• char
• varchar
• nchar
• nvarchar
• smalldate
• datum
• datum/tijd
• datetime2
• datetimeoffset
• tijd
• decimal
• numeriek
• float
• werkelijk
• bigint
• tinyint
• smallint
• int
• bigint
• bit
• money
• smallmoney
• uniqueidentifier

Notitie

LOB's groter dan 1 MB kunnen niet worden gebruikt met CETAS.

Volgende stappen

U kunt proberen om query's uit te voeren op externe tabellen van Apache Spark voor Azure Synapse.