CETAS avec Synapse SQL

  • Article

Vous pouvez utiliser CREATE EXTERNAL TABLE AS SELECT (CETAS) dans le pool SQL dédié ou dans le pool SQL serverless pour effectuer les tâches suivantes :

  • Créer une table externe

  • Exporter, en parallèle, les résultats d'une instruction Transact-SQL SELECT vers :

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

CETAS dans un pool SQL dédié

Pour plus d’informations sur le pool SQL dédié, et sur l’utilisation et la syntaxe de CETAS, consultez l’article CREATE EXTERNAL TABLE AS SELECT. Pour obtenir des conseils sur CTAS avec un pool SQL dédié, consultez l’article CREATE TABLE AS SELECT.

CETAS dans un pool SQL serverless

Lors de l’utilisation d’un pool SQL serverless, CETAS est utilisé pour créer une table externe et exporter les résultats de la requête vers Stockage Blob Azure ou Azure Data Lake Storage Gen2.

Pour obtenir la syntaxe complète, reportez-vous à CREATE EXTERNAL TABLE AS SELECT (Transact-SQL).

Exemples

Ces exemples utilisent CETAS pour enregistrer la population totale agrégée par année et par état dans le dossier aggregated_data qui se trouve dans la source de données population_ds.

Cet exemple s’appuie sur les informations d’identification, la source de données et le format de fichier externe créés précédemment. Reportez-vous au document tables externes. Pour enregistrer les résultats d’une requête dans un autre dossier d’une même source de données, modifiez l’argument LOCATION.

Pour enregistrer les résultats dans un autre compte de stockage, créez une autre source de données pour l’argument DATA_SOURCE.

Notes

Les exemples qui suivent utilisent un compte Azure Open Data Storage public. Ce compte est en lecture seule. Pour exécuter ces requêtes, vous devez fournir la source de données pour laquelle vous disposez d’autorisations en écriture.

-- 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

L’exemple suivant utilise une table externe comme source pour CETAS. Il s’appuie sur les informations d’identification, la source de données, le format de fichier externe et la table externe créés précédemment. Reportez-vous au document tables externes.

-- 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

Exemple général

Dans cet exemple, nous pouvons voir un exemple de code de modèle pour l’écriture de CETAS avec une vue en tant que source et l’utilisation de l’identité managée comme authentification.

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

Types de données pris en charge

CETAS peut être utilisé pour stocker les jeux de résultats avec les types de données SQL suivants :

  • binary
  • varbinary
  • char
  • varchar
  • NCHAR
  • NVARCHAR
  • smalldate
  • Date
  • DATETIME
  • datetime2
  • datetimeoffset
  • time
  • Décimal
  • numeric
  • float
  • real
  • bigint
  • TINYINT
  • SMALLINT
  • int
  • bigint
  • bit
  • money
  • SMALLMONEY
  • UNIQUEIDENTIFIER

Notes

Les LOB supérieurs à 1 Mo ne peuvent pas être utilisés avec CETAS.

Étapes suivantes

Essayez d'interroger des Tables externes Apache Spark pour Azure Synapse.