Diseño de tablas mediante Synapse SQL en Azure Synapse Analytics

Artículo
06/01/2023

En este documento se incluyen conceptos clave para diseñar tablas con un grupo de SQL dedicado y un grupo de SQL sin servidor.

Un grupo de SQL sin servidor es un servicio de consulta de los datos del lago de datos. No tiene almacenamiento local ni ingesta de datos. El grupo de SQL dedicado representa una colección de recursos de análisis que se aprovisionan al usar Synapse SQL. El tamaño de un grupo de SQL dedicado se determina mediante las unidades de almacenamiento de datos (DWU).

En la tabla siguiente se enumeran los temas que son pertinentes para un grupo de SQL dedicado frente a un grupo de SQL sin servidor:

Tema	Grupo de SQL dedicado	Grupo de SQL sin servidor
Determinación de la categoría de tabla	Sí	No
Nombres de esquemas	Sí	Sí
Nombres de tabla	Sí	No
Persistencia de tabla	Sí	No
Tabla normal	Sí	No
Tabla temporal	Sí	Sí
Tabla externa	Sí	Sí
Tipos de datos	Sí	Sí
Tablas distribuidas	Sí	No
Tablas distribuidas mediante una función hash	Sí	No
Tablas replicadas	Sí	No
Tablas round robin	Sí	No
Métodos comunes de distribución para tablas	Sí	No
Particiones	Sí	Sí
Índices de almacén de columnas	Sí	No
estadísticas	Sí	Sí
Clave principal y clave única	Sí	No
Comandos para la creación de tablas	Sí	No
Alineación de los datos de origen con el almacenamiento de datos	Sí	No
Características no compatibles de las tablas	Sí	No
Consultas de tamaño de tabla	Sí	No

Determinación de la categoría de tabla

Un esquema de estrella organiza los datos en tablas de hechos y dimensiones. Algunas tablas se utilizan para datos de integración o de almacenamiento provisional antes de moverlos a una tabla de hechos o dimensiones. Al diseñar una tabla, decidirá si los datos de la misma pertenecen a una tabla de hechos, dimensiones o integración. Esta decisión informa de la distribución y estructura de tabla adecuadas.

Las tablas de hechos contienen datos cuantitativos que se suelen generar en un sistema transaccional y, después, se cargan en el almacenamiento de datos. Por ejemplo, una empresa minorista genera transacciones de ventas todos los días y, después, carga los datos en una tabla de hechos en el almacenamiento de datos para su análisis.
Las tablas de dimensiones contienen datos de atributos que pueden cambiar, pero normalmente no cambian con frecuencia. Por ejemplo, el nombre y la dirección de un cliente se almacenan en una tabla de dimensiones y solo se actualizan cuando el perfil del cliente cambia. Para minimizar el tamaño de una tabla de hechos de gran tamaño, el nombre y la dirección del cliente no necesitan estar en todas las filas de una tabla de hechos. En su lugar, la tabla de hechos y la tabla de dimensiones pueden compartir un identificador de cliente. Una consulta puede combinar las dos tablas para asociar el perfil y las transacciones de un cliente.
Las tablas de integración proporcionan un lugar para integración o almacenamiento provisional de datos. Puede crear una tabla de integración como una tabla normal, una tabla externa o una tabla temporal. Por ejemplo, puede cargar datos a una tabla de almacenamiento provisional, realizar transformaciones en los datos en el almacenamiento provisional y luego insertar dichos datos en una tabla de producción.

Nombres de esquemas

Los esquemas son una buena manera de agrupar objetos que se usan de manera similar. El siguiente código crea un esquema definido por el usuario denominado wwi.

CREATE SCHEMA wwi;

Nombres de tabla

Si está migrando varias bases de datos de una solución local al grupo de SQL dedicado, el procedimiento recomendado es migrar todas las tablas de hechos, dimensiones e integración a un esquema del grupo de SQL. Por ejemplo, podría almacenar todas las tablas en el almacenamiento de datos de ejemplo WideWorldImportersDW dentro de un esquema denominado wwi.

Para mostrar la organización de las tablas en el grupo de SQL dedicado, puede utilizar fact, dim e int como prefijos para los nombres de tabla. En la tabla a continuación se muestran algunos de los nombres de esquema y tabla para WideWorldImportersDW.

Tabla WideWorldImportersDW	Tipo de tabla.	Grupo de SQL dedicado
City	Dimensión	wwi.DimCity
Pedido de	Fact	wwi.FactOrder

Persistencia de tabla

Las tablas almacenan datos de forma permanente en Azure Storage, temporalmente en Azure Storage o en un almacén de datos externo al almacenamiento de datos.

Tabla normal

Una tabla normal almacena datos en Azure Storage como parte del almacenamiento de datos. La tabla y los datos persisten, independientemente de si hay una sesión abierta o no. En el ejemplo a continuación se crea una tabla normal con dos columnas.

CREATE TABLE MyTable (col1 int, col2 int );

Tabla temporal

Una tabla temporal solo existe mientras dura la sesión. Puede usar una tabla temporal para evitar que otros usuarios vean los resultados temporales. El uso de tablas temporales también reduce la necesidad de limpieza. Las tablas temporales utilizan el almacenamiento local y, en los grupos de SQL dedicados, pueden ofrecer un rendimiento más rápido.

El grupo de SQL sin servidor admite tablas temporales. Sin embargo, su uso está limitado, ya que puede seleccionar de una tabla temporal, pero no puede unirla con los archivos en el almacenamiento.

Para obtener más información, consulte Tablas temporales.

Tabla externa

Las tablas externas apuntan a datos ubicados en Azure Storage Blob o Azure Data Lake Storage.

Importe los datos de tablas externas a grupos de SQL dedicados mediante la instrucción CREATE TABLE AS SELECT. Para un tutorial sobre la carga, consulte Uso de PolyBase para cargar datos de Azure Blob Storage en Azure SQL Data Warehouse.

En el caso de un grupo de SQL sin servidor, puede utilizar CETAS para guardar el resultado de la consulta en una tabla externa en Azure Storage.

Tipos de datos

El grupo de SQL dedicado admite los tipos de datos usados más comúnmente. Para obtener una lista de los tipos de datos admitidos, consulte los tipos de datos en la referencia de CREATE TABLE de la instrucción CREATE TABLE. Para obtener más información sobre el uso de los tipos de datos, consulte Tipos de datos.

Tablas distribuidas

Una característica fundamental de un grupo de SQL dedicado es la forma en que puede almacenar y operar en tablas de varias distribuciones. El grupo de SQL dedicado admite tres métodos para distribuir datos:

Round robin (método predeterminado)
Hash
Replicado

Tablas distribuidas mediante una función hash

La distribución hash de la tabla distribuye las filas en función del valor de la columna de distribución. La tabla distribuida de hash está diseñada para lograr un alto rendimiento para consultas en tablas grandes. Hay varios factores que debe tener en cuenta al elegir una columna de distribución.

Para más información, vea Distribución de tablas en SQL Data Warehouse.

Tablas replicadas

Una tabla replicada tiene una copia completa de la tabla disponible en cada nodo de proceso. Las consultas se ejecutan rápidamente en tablas replicadas, ya que las combinaciones en las tablas replicadas no requieren movimiento de datos. Sin embargo, la replicación necesita almacenamiento adicional y este método no resulta práctico para tablas de gran tamaño.

Para más información, consulte Instrucciones de diseño para el uso de tablas replicadas en Azure SQL Data Warehouse.

Tablas round robin

Una tabla round robin distribuye las filas de la tabla uniformemente entre todas las distribuciones. Las filas se distribuyen aleatoriamente. Cargar datos en una tabla round robin es rápido. Pero las consultas pueden requerir más movimiento de datos que los demás métodos de distribución.

Para más información, vea Distribución de tablas en SQL Data Warehouse.

Métodos comunes de distribución para tablas

La categoría de tabla suele determinar la opción óptima para la distribución de tablas.

Categoría de tabla	Opción de distribución recomendada
Fact	Utilice la distribución por hash con el índice de almacén de columnas agrupado. El rendimiento mejora cuando se combinan dos tablas hash en la misma columna de distribución.
Dimensión	Use la distribución replicada para tablas más pequeñas. Si las tablas son demasiado grandes para almacenar en cada nodo de proceso, utilice la distribución por hash.
Ensayo	Use round robin para la tabla de almacenamiento provisional. La carga con CTAS es rápida. Una vez que los datos estén en la tabla de almacenamiento provisional, use INSERT... SELECT para mover los datos a las tablas de producción.

Particiones

En los grupos de SQL dedicados, una tabla con particiones almacena y ejecuta operaciones en las filas de la tabla según los intervalos de datos. Por ejemplo, una tabla puede tener particiones por día, mes o año. Puede mejorar el rendimiento de las consultas mediante la eliminación de particiones, ya que limita el examen de una consulta a los datos dentro de una partición.

También puede mantener los datos a través de modificación de particiones. Puesto que los datos de un grupo de SQL dedicado ya están distribuidos, demasiadas particiones pueden ralentizar el rendimiento de las consultas. Para más información, consulte Creación de particiones de tablas en SQL Data Warehouse.

Sugerencia

Cuando cambie de partición a otras particiones de tabla que no estén vacías, puede usar la opción TRUNCATE_TARGET en la instrucción ALTER TABLE si se deben truncar los datos existentes.

En el código siguiente se cambian los datos diarios transformados a una partición SalesFact y se sobrescriben los datos existentes.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);

En un grupo de SQL sin servidor, puede limitar los archivos o carpetas (particiones) que leerá la consulta. La creación de particiones por ruta de acceso se admite mediante las funciones filepath y fileinfo descritas en Consulta de archivos de almacenamiento. En el ejemplo siguiente se lee una carpeta con datos del año 2017:

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

Índices de almacén de columnas

De manera predeterminada, el grupo de SQL dedicado almacena una tabla como índice de almacén de columnas agrupado. Esta forma de almacenamiento de datos logra una compresión de datos y rendimiento de las consultas altos en tablas grandes. El índice de almacén de columnas agrupado suele ser la mejor opción, pero en algunos casos un índice agrupado o un montón es la estructura de almacenamiento adecuada.

Sugerencia

Una tabla de montón puede ser sumamente útil para cargar datos transitorios, como una tabla de almacenamiento provisional que se transforma en una tabla final.

Para una lista de características de almacén de columnas, vea Novedades de los índices de almacén de columnas. Para mejorar el rendimiento del índice de almacén de columnas, vea Maximización de la calidad del grupo de filas del almacén de columnas.

Estadísticas

El optimizador de consultas utiliza estadísticas de columna cuando crea el plan de ejecución de una consulta. Para mejorar el rendimiento de las consultas, es importante crear estadísticas en columnas individuales, especialmente en las columnas que se usan en combinaciones de consultas. SQL de Synapse admite la creación automática de estadísticas.

La actualización estadística no se realiza automáticamente. Actualice las estadísticas cuando se agregue o cambie un número significativo de filas. Por ejemplo, actualice las estadísticas después de una carga. En el artículo con la guía de estadísticas encontrará información adicional.

Clave principal y clave única

Para un grupo de SQL dedicado, PRIMARY KEY solo se admite cuando se usa tanto NONCLUSTERED como NOT ENFORCED. Solo se admite la restricción UNIQUE cuando se usa NOT ENFORCED. Para más información, consulte el artículo Restricciones de tablas de grupos de SQL dedicados.

Comandos para la creación de tablas

Para un grupo de SQL dedicado, puede crear una tabla como una nueva tabla vacía. También puede crear y rellenar una tabla con los resultados de una instrucción SELECT. A continuación se muestran los comandos de T-SQL para crear una tabla.

Instrucción T-SQL	Descripción
CREATE TABLE	Crea una tabla vacía mediante la definición de todas las opciones y columnas de la tabla.
CREATE EXTERNAL TABLE	Crea una tabla externa. La definición de la tabla se almacena en el grupo de SQL dedicado. Los datos de la tabla se almacenan en Azure Blob Storage o Azure Data Lake Storage.
CREATE TABLE AS SELECT	Rellena una nueva tabla con los resultados de una instrucción SELECT. Las columnas de tabla y los tipos de datos se basan en los resultados de la instrucción SELECT. Para importar datos, puede seleccionar esta instrucción en una tabla externa.
CREATE EXTERNAL TABLE AS SELECT	Crea una nueva tabla externa mediante la exportación de los resultados de una instrucción SELECT a una ubicación externa. La ubicación es Azure Blob Storage o Azure Data Lake Storage.

Alineación de los datos de origen con el almacenamiento de datos

Las tablas del grupo de SQL dedicado se rellenan cargando datos desde otro origen de datos. Para lograr una carga correcta, el número y los tipos de datos de las columnas de los datos de origen se deben alinear con la definición de tabla en el almacenamiento de datos.

Nota:

Obtener los datos que se van a alinear podría ser la parte más complicada de diseñar las tablas.

Si los datos proceden de varios almacenamientos de datos, puede portar dichos datos en el almacenamiento de datos y almacenarlos en una tabla de integración. Una vez que los datos estén en la tabla de integración, podrá utilizar la eficacia del grupo de SQL dedicado para implementar operaciones de transformación. Una vez que los datos están preparados, puede insertarlos en tablas de producción.

Características no compatibles de las tablas

El grupo de SQL dedicado admite muchas, pero no la totalidad, de las características de tabla que ofrecen otras bases de datos. En la lista siguiente se muestran algunas de las características de tabla que no se admiten en el grupo de SQL dedicado.

Clave externa, compruebe Restricciones de tabla
Columnas calculadas
Vistas indizadas
Secuencia
Columnas dispersas
Claves suplentes, implemente con Identity
Sinónimos
Desencadenadores
Índices únicos
Tipos definidos por el usuario

Consultas de tamaño de tabla

En un grupo de SQL dedicado, una forma sencilla de identificar el espacio y las filas que consume una tabla en cada una de las 60 distribuciones es usar DBCC PDW_SHOWSPACEUSED.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Tenga en cuenta que el uso de los comandos DBCC puede resultar muy limitador. Las vistas de administración dinámica (DMV) muestran más detalles que los comandos DBCC. Comience por crear la vista a continuación.

CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
 GETDATE()                                                             AS  [execution_time]
, DB_NAME()                                                            AS  [database_name]
, s.name                                                               AS  [schema_name]
, t.name                                                               AS  [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name)                              AS  [two_part_name]
, nt.[name]                                                            AS  [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL))     AS  [node_table_name_seq]
, tp.[distribution_policy_desc]                                        AS  [distribution_policy_name]
, c.[name]                                                             AS  [distribution_column]
, nt.[distribution_id]                                                 AS  [distribution_id]
, i.[type]                                                             AS  [index_type]
, i.[type_desc]                                                        AS  [index_type_desc]
, nt.[pdw_node_id]                                                     AS  [pdw_node_id]
, pn.[type]                                                            AS  [pdw_node_type]
, pn.[name]                                                            AS  [pdw_node_name]
, di.name                                                              AS  [dist_name]
, di.position                                                          AS  [dist_position]
, nps.[partition_number]                                               AS  [partition_nmbr]
, nps.[reserved_page_count]                                            AS  [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count]                    AS  [unused_space_page_count]
, nps.[in_row_data_page_count]
    + nps.[row_overflow_used_page_count]
    + nps.[lob_used_page_count]                                        AS  [data_space_page_count]
, nps.[reserved_page_count]
 - (nps.[reserved_page_count] - nps.[used_page_count])
 - ([in_row_data_page_count]
         + [row_overflow_used_page_count]+[lob_used_page_count])       AS  [index_space_page_count]
, nps.[row_count]                                                      AS  [row_count]
from
    sys.schemas s
INNER JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
    ON  t.[object_id] = i.[object_id]
    AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
    ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
    ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
    ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
    ON  nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
    ON  nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
    ON nt.[object_id] = nps.[object_id]
    AND nt.[pdw_node_id] = nps.[pdw_node_id]
    AND nt.[distribution_id] = nps.[distribution_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
    ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
    ON cdp.[object_id] = c.[object_id]
    AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
   [execution_time]
,  [database_name]
,  [schema_name]
,  [table_name]
,  [two_part_name]
,  [node_table_name]
,  [node_table_name_seq]
,  [distribution_policy_name]
,  [distribution_column]
,  [distribution_id]
,  [index_type]
,  [index_type_desc]
,  [pdw_node_id]
,  [pdw_node_type]
,  [pdw_node_name]
,  [dist_name]
,  [dist_position]
,  [partition_nmbr]
,  [reserved_space_page_count]
,  [unused_space_page_count]
,  [data_space_page_count]
,  [index_space_page_count]
,  [row_count]
,  ([reserved_space_page_count] * 8.0)                                 AS [reserved_space_KB]
,  ([reserved_space_page_count] * 8.0)/1000                            AS [reserved_space_MB]
,  ([reserved_space_page_count] * 8.0)/1000000                         AS [reserved_space_GB]
,  ([reserved_space_page_count] * 8.0)/1000000000                      AS [reserved_space_TB]
,  ([unused_space_page_count]   * 8.0)                                 AS [unused_space_KB]
,  ([unused_space_page_count]   * 8.0)/1000                            AS [unused_space_MB]
,  ([unused_space_page_count]   * 8.0)/1000000                         AS [unused_space_GB]
,  ([unused_space_page_count]   * 8.0)/1000000000                      AS [unused_space_TB]
,  ([data_space_page_count]     * 8.0)                                 AS [data_space_KB]
,  ([data_space_page_count]     * 8.0)/1000                            AS [data_space_MB]
,  ([data_space_page_count]     * 8.0)/1000000                         AS [data_space_GB]
,  ([data_space_page_count]     * 8.0)/1000000000                      AS [data_space_TB]
,  ([index_space_page_count]  * 8.0)                                   AS [index_space_KB]
,  ([index_space_page_count]  * 8.0)/1000                              AS [index_space_MB]
,  ([index_space_page_count]  * 8.0)/1000000                           AS [index_space_GB]
,  ([index_space_page_count]  * 8.0)/1000000000                        AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;

Resumen de espacio de tabla

Esta consulta devuelve las filas y el espacio por tabla. El resumen del espacio de tabla le permite ver qué tablas son las más grandes. También verá si son round robin, replicadas o distribuidas por hash. Para las tablas distribuidas por hash, la consulta muestra la columna de distribución.

SELECT
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
,    COUNT(distinct partition_nmbr) as nbr_partitions
,    SUM(row_count)                 as table_row_count
,    SUM(reserved_space_GB)         as table_reserved_space_GB
,    SUM(data_space_GB)             as table_data_space_GB
,    SUM(index_space_GB)            as table_index_space_GB
,    SUM(unused_space_GB)           as table_unused_space_GB
FROM
    dbo.vTableSizes
GROUP BY
     database_name
,    schema_name
,    table_name
,    distribution_policy_name
,      distribution_column
,    index_type_desc
ORDER BY
    table_reserved_space_GB desc
;

Espacio de tabla por tipo de distribución

SELECT
     distribution_policy_name
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;

Espacio de tabla por tipo de índice

SELECT
     index_type_desc
,    SUM(row_count)                as table_type_row_count
,    SUM(reserved_space_GB)        as table_type_reserved_space_GB
,    SUM(data_space_GB)            as table_type_data_space_GB
,    SUM(index_space_GB)           as table_type_index_space_GB
,    SUM(unused_space_GB)          as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;

Resumen de espacio de distribución

SELECT
    distribution_id
,    SUM(row_count)                as total_node_distribution_row_count
,    SUM(reserved_space_MB)        as total_node_distribution_reserved_space_MB
,    SUM(data_space_MB)            as total_node_distribution_data_space_MB
,    SUM(index_space_MB)           as total_node_distribution_index_space_MB
,    SUM(unused_space_MB)          as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY     distribution_id
ORDER BY    distribution_id
;

Pasos siguientes

Después de crear las tablas para el almacenamiento de datos, el paso siguiente es cargar datos en la tabla. Para obtener un tutorial de carga, consulte Carga de datos en un grupo de SQL dedicado.

Compartir a través de