Diseño de tablas mediante el grupo de SQL Synapse
En este artículo se explican los conceptos clave para diseñar tablas con un grupo de SQL dedicado y un grupo de SQL sin servidor en Azure Synapse Analytics.
- Grupo de SQL sin servidor es un servicio de consulta que funciona sobre los datos del lago de datos. No tiene almacenamiento local ni ingesta de datos.
- Grupo de SQL dedicado representa una colección de recursos analíticos 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).
Los temas siguientes son relevantes para el grupo de SQL dedicado frente al grupo de SQL sin servidor:
| Tema | Grupo de SQL dedicado | Grupo de SQL sin servidor |
|---|---|---|
| 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 round robin | Sí | No |
| Tablas distribuidas mediante una función hash | Sí | No |
| Tablas replicadas | 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 |
| Alinear 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 |
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 código siguiente crea un esquema definido por el usuario denominado wwi.
CREATE SCHEMA wwi;
Nombres de tabla
Si va a migrar varias bases de datos de una solución local a un grupo de SQL dedicado, el procedimiento recomendado es migrar todas las tablas de hechos, dimensiones e integración a un esquema de grupo de SQL. Por ejemplo, podría almacenar todas las tablas de la WideWorldImportersDW almacenamiento de datos de ejemplo en un esquema denominado wwi.
Para mostrar la organización de las tablas del grupo de SQL dedicado, puede usar fact, dimy int como prefijos para los nombres de tabla. En la tabla siguiente se muestran algunos de los nombres de esquema y tabla de 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 siguiente 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, pero su uso es limitado, ya que puede seleccionar entre una tabla temporal, pero no puede combinarla con 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.
Puede importar datos de tablas externas en grupos de SQL dedicados mediante la instrucción CREATE TABLE AS SELECT (CTAS). Para ver un tutorial de carga, consulte Carga del conjunto de datos de Taxicab de Nueva York.
En el caso del grupo de SQL sin servidor, puede usar CREATE EXTERNAL TABLE AS SELECT (CETAS) para guardar el resultado de la consulta en una tabla externa de 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 tipo de datos en la referencia CREATE TABLE. Para obtener más información sobre el uso de tipos de datos, consulte Tipos de datos table en Synapse SQL.
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:
- Tablas round robin (valor predeterminado)
- Tablas distribuidas mediante una función hash
- Tablas replicadas
Tablas round robin
Una tabla round robin distribuye las filas de la tabla uniformemente entre todas las distribuciones. Las filas se distribuyen aleatoriamente. La carga de datos en una tabla round-robin es rápida, pero las consultas pueden requerir más movimiento de datos que los otros métodos de distribución.
Para más información, vea Distribución de tablas en SQL Data Warehouse.
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.
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
Al cambiar de partición a particiones de tabla que no están vacías, considere la posibilidad de usar la TRUNCATE_TARGET opción en la instrucción ALTER TABLE si los datos existentes se van a truncar.
El código siguiente cambia los datos diarios transformados en una partición SalesFact y sobrescribe los datos existentes.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
En el grupo de SQL sin servidor, puede limitar los archivos o carpetas (particiones) leídos por 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 para el 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 obtener una lista de las características del almacén de columnas, consulte Novedades de los índices de almacén de columnas. Para mejorar el rendimiento del índice de almacén de columnas, consulte Maximizar la calidad del grupo de filas para los índices de 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. Puede actualizar las estadísticas después de agregar o cambiar un número significativo de filas. Por ejemplo, actualice las estadísticas después de una carga. Para obtener más información, consulte Estadísticas en Synapse SQL.
Clave principal y clave única
En el caso del grupo de SQL dedicado, PRIMARY KEY solo se admite cuando NONCLUSTERED se usan y NOT ENFORCED ambos.
UNIQUE La restricción solo se admite cuando NOT ENFORCED se usa. Para más información, consulte la Clave principal, clave externa y clave única mediante el grupo de SQL dedicado.
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 Identidad
- 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. Empiece por crear la siguiente vista.
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 puede ver si son round-robin, replicados o distribuidos 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
;
Contenido relacionado
Después de crear una tabla para el almacenamiento de datos, el siguiente paso es cargar datos en la tabla.
- Tutorial de: Carga de los datos en el grupo de SQL