CETAS con Synapse SQL

  • Artículo

Puede usar la instrucción CREATE EXTERNAL TABLE AS SELECT (CETAS) en el grupo de SQL dedicado o sin servidor para completar las siguientes tareas:

  • Crear una tabla externa

  • Exportar, en paralelo, los resultados de una instrucción SELECT de Transact-SQL a:

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

CETAS en el grupo de SQL dedicado

Para más información sobre el grupo de SQL dedicado, y el uso y la sintaxis de CETAS, consulte el artículo CREATE EXTERNAL TABLE AS SELECT. Además, para instrucciones sobre CTAS con el grupo de SQL dedicado, consulte el artículo CREATE TABLE AS SELECT.

CETAS en el grupo de SQL sin servidor

Al usar el grupo de SQL sin servidor, con CETAS se crea una tabla externa y se exportan los resultados de la consulta a Azure Storage Blob o Azure Data Lake Storage Gen2.

Para obtener una sintaxis completa, consulte CREATE EXTERNAL TABLE AS SELECT (Transact-SQL).

Ejemplos

En estos ejemplos se usa CETAS para guardar la población total agregada por año y estado a una carpeta aggregated_data que se encuentra en el origen de datos population_ds.

Este ejemplo se basa en las credenciales, el origen de datos y el formato de archivo externo que se crearon anteriormente. Consulte el documento tablas externas. Para guardar los resultados de la consulta en una carpeta diferente del mismo origen de datos, cambie el argumento LOCATION.

Para guardar los resultados en una cuenta de almacenamiento distinta, cree y use un origen de datos diferente para el argumento DATA_SOURCE.

Nota

Los ejemplos siguientes usan una cuenta de almacenamiento pública de Azure Open Data. Es de solo lectura. Para ejecutar estas consultas, debe proporcionar el origen de datos para el que tenga permisos de escritura.

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

En el ejemplo siguiente se usa una tabla externa como origen de CETAS. Esta se basa en las credenciales, el origen de datos, el formato de archivo externo y la tabla externa que se crearon anteriormente. Consulte el documento tablas externas.

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

Ejemplo general

En este ejemplo, podemos ver un ejemplo de código de plantilla para escribir CETAS con una vista como origen y usar la identidad administrada como autenticación.

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

Tipos de datos admitidos

CETAS se puede usar para almacenar conjuntos de resultados con los siguientes tipos de datos de SQL:

  • binary
  • varbinary
  • char
  • varchar
  • NCHAR
  • NVARCHAR
  • smalldate
  • date
  • datetime
  • datetime2
  • datetimeoffset
  • time
  • Decimal
  • NUMERIC
  • FLOAT
  • real
  • bigint
  • TINYINT
  • SMALLINT
  • int
  • bigint
  • bit
  • money
  • SMALLMONEY
  • UNIQUEIDENTIFIER

Nota

Los objetos grandes mayores de 1 MB no se pueden usar con CETAS.

Pasos siguientes

Pruebe a consultar Tablas externas de Apache Spark para Azure Synapse.