Creación de tablas de almacenamiento de datos

  • 10 minutos

Ahora que comprende los principios arquitectónicos básicos de un esquema de almacenamiento de datos relacional, vamos a explorar cómo crear un almacenamiento de datos.

Creación de un grupo de SQL dedicado

Para crear un almacenamiento de datos relacional en Azure Synapse Analytics, debe crear un grupo de SQL dedicado. La manera más sencilla de hacerlo en un área de trabajo de Azure Synapse Analytics es usar la página Administrar de Azure Synapse Studio, como se muestra aquí:

A screenshot of the SQL pools tab in the Manage page of Synapse Studio.

Al aprovisionar un grupo de SQL dedicado, puede especificar los siguientes valores de configuración:

  • Nombre único del grupo de SQL dedicado.
  • Un nivel de rendimiento para el grupo de SQL, que puede oscilar entre DW100c y DW30000c, y que determina el costo por hora del grupo cuando está en ejecución.
  • Si se va a empezar con un grupo vacío o se va a restaurar una base de datos a partir de una copia de seguridad.
  • La intercalación del grupo de SQL, que determina el criterio de ordenación y las reglas de comparación de cadenas para la base de datos. (No se puede cambiar la intercalación después de la creación).

Después de crear un grupo de SQL dedicado, puede controlar su estado de ejecución en la página Administrar de Synapse Studio, pausándolo cuando no sea necesario para evitar costos innecesarios.

Cuando se ejecuta el grupo, puede explorarlo en la página Datos y crear SQL scripts para ejecutarlos.

Consideraciones para la creación de grupos

Para crear tablas en el grupo de SQL dedicado, use la instrucción Transact-SQL CREATE TABLE (o, a veces, CREATE EXTERNAL TABLE). Las opciones específicas usadas en la instrucción dependen del tipo de tabla que se esté creando, lo que puede incluir:

  • Tablas de hechos
  • Tablas de dimensiones
  • Tablas de ensayo

Nota

El almacenamiento de datos se compone de tablas de hechos y dimensiones, como se explicó anteriormente. Las tablas de almacenamiento provisional se suelen usar como parte del proceso de carga de almacenamiento de datos para ingerir datos de sistemas de origen.

Al diseñar un modelo de esquema de estrella para conjuntos de datos pequeños o medianos, puede usar su base de datos preferida, como Azure SQL. Para conjuntos de datos más grandes, puede beneficiarse de la implementación del almacenamiento de datos en Azure Synapse Analytics en lugar de en SQL Server. Es importante comprender algunas diferencias clave al crear tablas en Synapse Analytics.

Restricciones de integridad de datos

Los grupos de SQL dedicados en Synapse Analytics no admiten restricciones únicas y de claves externas, como se encuentra en otros sistemas de bases de datos relacionales, como SQL Server. Esto significa que los trabajos que se usan para cargar datos deben mantener la unicidad y la integridad referencial de las claves, sin depender de las definiciones de tabla de la base de datos para hacerlo.

Sugerencia

Para obtener más información sobre las restricciones de los grupos de SQL dedicados de Azure Synapse Analytics, consulte Clave principal, clave externa y clave única mediante un grupo de SQL dedicado en Azure Synapse Analytics.

Índices

Aunque los grupos de SQL dedicados de Synapse Analytics admiten índices agrupados tal y como se encuentran en SQL Server, el tipo de índice predeterminado es el almacén de columnas agrupado. Este tipo de índice ofrece una ventaja significativa de rendimiento al consultar grandes cantidades de datos en un esquema de almacenamiento de datos típico y se debe usar siempre que sea posible. Sin embargo, algunas tablas pueden incluir tipos de datos que no se pueden incluir en un índice de almacén de columnas agrupado, por ejemplo, VARBINARY(MAX), en cuyo caso se puede usar un índice agrupado en su lugar.

Sugerencia

Para obtener más información sobre la indexación en grupos de SQL dedicados de Azure Synapse Analytics, consulte Índices en tablas de grupo de SQL dedicadas en Azure Synapse Analytics.

Distribución

Los grupos de SQL dedicados de Azure Synapse Analytics usan una arquitectura de procesamiento paralelo masivo (MPP), en lugar de la arquitectura de multiprocesamiento simétrico (SMP) que se usa en la mayoría de los sistemas de base de datos OLTP. En un sistema MPP, los datos de una tabla se distribuyen para procesarse en un grupo de nodos. Synapse Analytics admite los siguientes tipos de distribución:

  • Hash: se calcula un valor hash determinista para la columna especificada y se usa para asignar la fila a un nodo de proceso.
  • Round robin: las filas se distribuyen uniformemente en todos los nodos de proceso.
  • Replicado: una copia de la tabla se almacena en cada nodo de proceso.

El tipo de tabla a menudo determina qué opción elegir para distribuir la tabla.

Tipo de tabla. Opción de distribución recomendada
Dimensión Use la distribución replicada para tablas más pequeñas con el fin de evitar el orden aleatorio de datos al combinarlos con tablas de hechos distribuidas. Si las tablas son demasiado grandes para almacenar en cada nodo de proceso, utilice la distribución por hash.
Fact Use la distribución hash con el índice de almacén de columnas agrupado para distribuir tablas de hechos entre nodos de proceso.
Ensayo Use la distribución round robin para las tablas de almacenamiento provisional con el fin de distribuir uniformemente los datos entre los nodos de proceso.

Sugerencia

Para obtener más información sobre las estrategias de distribución para las tablas de Azure Synapse Analytics, consulte Guía para diseñar tablas distribuidas mediante un grupo de SQL dedicado en Azure Synapse Analytics.

Creación de tablas de dimensiones

Al crear una tabla de dimensiones, asegúrese de que la definición de tabla incluya claves suplentes y alternativas, así como columnas para los atributos de la dimensión que desea usar para agrupar agregaciones. A menudo, es más fácil usar una columna IDENTITY para generar automáticamente una clave suplente de incremento (de lo contrario, debe generar claves únicas cada vez que cargue los datos). En el ejemplo siguiente se muestra una instrucción CREATE TABLE para una tabla hipotética de dimensiones DimCustomer.

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Nota

Si lo desea, puede crear un esquema específico como espacio de nombres para las tablas. En este ejemplo, se usa el esquema dbo predeterminado.

Si tiene previsto usar un esquema de copo de nieve en el que las tablas de dimensiones están relacionadas entre sí, debe incluir la clave de la dimensión primaria en la definición de la tabla de dimensiones secundarias. Por ejemplo, el siguiente código de SQL podría usarse para mover los detalles de dirección geográfica de la tabla DimCustomer a una tabla de dimensiones DimGeography independiente:

CREATE TABLE dbo.DimGeography
(
    GeographyKey INT IDENTITY NOT NULL,
    GeographyAlternateKey NVARCHAR(10) NULL,
    StreetAddress NVARCHAR(100),
    City NVARCHAR(20),
    PostalCode NVARCHAR(10),
    CountryRegion NVARCHAR(20)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

CREATE TABLE dbo.DimCustomer
(
    CustomerKey INT IDENTITY NOT NULL,
    CustomerAlternateKey NVARCHAR(15) NULL,
    GeographyKey INT NULL,
    CustomerName NVARCHAR(80) NOT NULL,
    EmailAddress NVARCHAR(50) NULL,
    Phone NVARCHAR(25) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Tablas de dimensiones de tiempo

La mayoría de los almacenamientos de datos incluyen una tabla de dimensiones de tiempo que permite agregar datos por varios niveles jerárquicos de intervalo de tiempo. Por ejemplo, en el ejemplo siguiente se crea una tabla DimDate con atributos relacionados con fechas específicas.

CREATE TABLE dbo.DimDate
( 
    DateKey INT NOT NULL,
    DateAltKey DATETIME NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(15) NOT NULL,
    MonthOfYear INT NOT NULL,
    MonthName NVARCHAR(15) NOT NULL,
    CalendarQuarter INT  NOT NULL,
    CalendarYear INT NOT NULL,
    FiscalQuarter INT NOT NULL,
    FiscalYear INT NOT NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Sugerencia

Un patrón común al crear una tabla de dimensiones para fechas es usar la fecha numérica en formato DDMMAAAA oAAAAMMDD como clave suplente de entero, y la fecha como un tipo de datos DATE o DATETIME como clave alternativa.

Creación de tablas de hechos

Las tablas de hechos incluyen las claves de cada dimensión con la que están relacionadas y los atributos y las medidas numéricas para eventos u observaciones específicos que desea analizar.

En el ejemplo de código siguiente se crea una tabla de hechos hipotética denominada FactSales que está relacionada con varias dimensiones a través de columnas de clave (fecha, cliente, producto y almacén).

CREATE TABLE dbo.FactSales
(
    OrderDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    ProductKey INT NOT NULL,
    StoreKey INT NOT NULL,
    OrderNumber NVARCHAR(10) NOT NULL,
    OrderLineItem INT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice DECIMAL NOT NULL,
    Discount DECIMAL NOT NULL,
    Tax DECIMAL NOT NULL,
    SalesAmount DECIMAL NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(OrderNumber),
    CLUSTERED COLUMNSTORE INDEX
);

Creación de tablas de almacenamiento provisional

Las tablas de almacenamiento provisional se usan como almacenamiento temporal para los datos a medida que se cargan en el almacenamiento de datos. Un patrón típico consiste en estructurar la tabla para que sea lo más eficaz posible ingerir los datos de su origen externo (a menudo, archivos en un lago de datos) en la base de datos relacional y, a continuación, usar instrucciones SQL para cargar los datos de las tablas de almacenamiento provisional en las tablas de dimensiones y hechos.

En el ejemplo de código siguiente se crea una tabla de almacenamiento provisional para los datos del producto que, en última instancia, se cargarán en una tabla de dimensiones:

CREATE TABLE dbo.StageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

Uso de tablas externas

En algunos casos, si los datos que se van a cargar están en archivos con una estructura adecuada, puede ser más eficaz crear tablas externas que hagan referencia a la ubicación del archivo. De este modo, los datos se pueden leer directamente desde los archivos de origen en lugar de cargarse en el almacén relacional. En el ejemplo siguiente se muestra cómo crear una tabla externa que haga referencia a archivos del lago de datos asociado al área de trabajo de Synapse:


-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO

-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
    ProductID NVARCHAR(10) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    ProductCategory NVARCHAR(200) NOT NULL,
    Color NVARCHAR(10),
    Size NVARCHAR(10),
    ListPrice DECIMAL NOT NULL,
    Discontinued BIT NOT NULL
)
WITH
(
    DATA_SOURCE = StagedFiles,
    LOCATION = 'products/*.parquet',
    FILE_FORMAT = ParquetFormat
);
GO

Nota

Para obtener más información sobre el uso de tablas externas, consulte Uso de tablas externas con Synapse SQL en la documentación de Azure Synapse Analytics.