Creación y uso de vistas mediante un grupo de SQL sin servidor en Azure Synapse Analytics

Artículo
05/08/2023

En esta sección, aprenderá a crear y usar vistas para encapsular consultas de un grupo de SQL sin servidor. Las vistas le permitirán volver a usar esas consultas. Las vistas también son necesarias si se desea usar herramientas, como Power BI, junto con el grupo de SQL sin servidor.

Prerrequisitos

El primer paso es crear la base de datos en que se va a crear la vista e inicializar los objetos necesarios para realizar la autenticación en Azure Storage mediante la ejecución de un script de instalación en esa base de datos. Todas las consultas de este artículo se ejecutarán en la base de datos de ejemplo.

Vistas sobre datos externos

Las vistas se crean de la misma manera que las vistas de SQL Server normales. La siguiente consulta crea una vista que lee el archivo population.csv.

Nota

Cambie la primera línea de la consulta, es decir, [mydbname], para usar la base de datos que ha creado.

USE [mydbname];
GO

DROP VIEW IF EXISTS populationView;
GO

CREATE VIEW populationView AS
SELECT * 
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', 
        FIELDTERMINATOR =',', 
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r];

La vista usa un elemento EXTERNAL DATA SOURCE con una dirección URL raíz del almacenamiento, como DATA_SOURCE y agrega una ruta de acceso relativa a los archivos.

Vistas de Delta Lake

Si va a crear las vistas encima de la carpeta de Delta Lake, debe especificar la ubicación de la carpeta raíz después de la opción BULK en lugar de especificar la ruta de acceso del archivo.

Carpeta ECDC COVID-19 de Delta Lake

La función OPENROWSET que lee datos de la carpeta de Delta Lake examinará la estructura de carpetas e identificará automáticamente las ubicaciones de los archivos.

create or alter view CovidDeltaLake
as
select *
from openrowset(
           bulk 'covid',
           data_source = 'DeltaLakeStorage',
           format = 'delta'
    ) with (
           date_rep date,
           cases int,
           geo_id varchar(6)
           ) as rows

Para más información, consulte la página de autoayuda del grupo de SQL sin servidor de Synapse y los problemas conocidos de Azure Synapse Analytics.

Vistas con particiones

Si tiene un conjunto de archivos con particiones en la estructura jerárquica de carpetas, puede describir el patrón de partición mediante los caracteres comodín en la ruta de acceso del archivo. Use la función FILEPATH para exponer partes de la ruta de acceso de carpeta como columnas de partición.

CREATE VIEW TaxiView
AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]
FROM
    OPENROWSET(
        BULK 'parquet/taxi/year=*/month=*/*.parquet',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT='PARQUET'
    ) AS nyc

Las vistas con particiones pueden mejorar el rendimiento de las consultas mediante la eliminación de particiones al consultarlas con filtros en las columnas de particiones. Sin embargo, no todas las consultas admiten la eliminación de particiones, por lo que es importante seguir algunos procedimientos recomendados.

Para garantizar la eliminación de particiones, evite el uso de subconsultas en filtros, ya que podrían interferir con la capacidad de eliminar particiones. En su lugar, pase el resultado de la subconsulta como una variable al filtro.

Al usar JOIN en consultas SQL, declare el predicado de filtro como NVARCHAR para reducir la complejidad del plan de consulta y aumentar la probabilidad de eliminación de particiones correcta. Normalmente, las columnas de partición se deducen como NVARCHAR(1024), por lo que el uso del mismo tipo para el predicado evitaría la necesidad de una conversión implícita, lo que podría aumentar la complejidad del plan de consulta.

Vistas con particiones de Delta Lake

Si va a crear las vistas con particiones encima del almacenamiento de Delta Lake, puede especificar solo una carpeta de Delta Lake raíz; no es necesario exponer explícitamente las columnas de partición mediante la función FILEPATH:

CREATE OR ALTER VIEW YellowTaxiView
AS SELECT *
FROM  
    OPENROWSET(
        BULK 'yellow',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT='DELTA'
    ) nyc

La función OPENROWSET examinará la estructura de la carpeta subyacente de Delta Lake e identificará y expondrá automáticamente las columnas de partición. La eliminación de particiones se realizará automáticamente si coloca la columna de partición en la cláusula WHERE de una consulta.

El nombre de la carpeta en la función OPENROWSET (yellow en este ejemplo) que se concatena con el URI LOCATION definido en el origen de datos DeltaLakeStorage debe hacer referencia a la carpeta raíz Delta Lake que contiene una subcarpeta denominada _delta_log.

Carpeta Yellow Taxi de Delta Lake

Para más información, consulte la página de autoayuda del grupo de SQL sin servidor de Synapse y los problemas conocidos de Azure Synapse Analytics.

Vistas JSON

Las vistas son una buena opción si necesita realizar algún procesamiento adicional a partir del conjunto de resultados que se captura de los archivos. Un ejemplo podría ser el análisis de archivos JSON en los que es necesario aplicar funciones JSON para extraer los valores de los documentos JSON:

CREATE OR ALTER VIEW CovidCases
AS 
select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')

La función OPENJSON analiza cada línea del archivo JSONL que contiene un documento JSON por línea en formato textual.

Vistas de Azure Cosmos DB en contenedores

Las vistas se pueden crear a partir de los contenedores de Azure Cosmos DB si el almacenamiento analítico de Cosmos DB está habilitado en el contenedor. Como parte de la vista se debe agregar el nombre de la cuenta, el nombre de la base de datos y el nombre del contenedor de Azure Cosmos DB, y la clave de acceso de solo lectura debe colocarse en la credencial de ámbito de base de datos a la que hace referencia la vista.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

Para más información, consulte Consulta de datos de Azure Cosmos DB con un grupo de SQL sin servidor en Azure Synapse Link.

Uso de una vista

Puede usar vistas en las consultas de la misma manera que las utiliza en las consultas de SQL Server.

En la consulta siguiente se muestra el uso de la vista de population_csv que creamos en la sección Creación de una vista. Devuelve los nombres de país/región con su población en 2019, en orden descendente.