CETAS con Synapse SQL

  • Articolo

È possibile usare CREATE EXTERNAL TABLE AS SELECT (CETAS) nel pool SQL dedicato o nel pool SQL serverless per completare le attività seguenti:

  • Creare una tabella esterna

  • Esportare, in parallelo, i risultati di un'istruzione Transact-SQL SELECT in:

    • Hadoop
    • BLOB di Archiviazione di Azure
    • Azure Data Lake Storage Gen2

CETAS nel pool SQL dedicato

Per informazioni su utilizzo e sintassi di CETAS nel pool SQL dedicato, vedere l'articolo CREATE EXTERNAL TABLE AS SELECT. Inoltre, per informazioni su CTAS con il pool SQL dedicato, vedere l'articolo CREATE TABLE AS SELECT.

CETAS nel pool SQL serverless

Se si usa il pool SQL serverless, CETAS consente di creare una tabella esterna e di esportare i risultati delle query in BLOB del servizio di archiviazione di Azure o in Azure Data Lake Storage Gen2.

Per la sintassi completa, vedere CREATE EXTERNAL TABLE AS SELECT (Transact-SQL).

Esempi

Questi esempi usano CETAS per salvare i dati aggregati sulla popolazione totale per anno e stato in una cartella aggregated_data che si trova nell'origine dati population_ds.

Questo esempio si basa su credenziali, origine dati e formato di file esterno creati in precedenza. Vedere il documento Tabelle esterne. Per salvare i risultati delle query in una cartella diversa nella stessa origine dati, cambiare l'argomento LOCATION.

Per salvare i risultati in un account di archiviazione diverso, creare e usare un'origine dati diversa per l'argomento DATA_SOURCE.

Nota

Gli esempi che seguono usano un account di archiviazione di dati aperti di Azure. È di sola lettura. Per eseguire queste query, è necessario specificare l'origine dati per chi si hanno autorizzazioni di scrittura.

-- 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'esempio seguente usa una tabella esterna come origine per CETAS. Si basa su credenziali, origine dati e formato di file esterno e tabella esterna creati in precedenza. Vedere il documento Tabelle esterne.

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

Esempio generale

In questo esempio è possibile vedere un esempio di codice modello per la scrittura di CETAS con una visualizzazione come origine e l'uso di Identità gestita come autenticazione.

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

Tipi di dati supportati

È possibile usare CETAS per archiviare i set di risultati con i tipi di dati SQL seguenti:

  • BINARY
  • varbinary
  • char
  • varchar
  • NCHAR
  • NVARCHAR
  • smalldate
  • Data
  • Datetime
  • datetime2
  • datetimeoffset
  • time
  • decimal
  • NUMERIC
  • float
  • real
  • bigint
  • TINYINT
  • smallint
  • int
  • bigint
  • bit
  • money
  • SMALLMONEY
  • UNIQUEIDENTIFIER

Nota

Non è possibile usare LOB di dimensioni maggiori di 1 MB con CETAS.

Passaggi successivi

Provare a eseguire query su tabelle esterne di Apache Spark per Azure Synapse.