Diseño de tablas mediante Synapse SQL en Azure Synapse Analytics
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.
Comentaris
Properament: al llarg del 2024 eliminarem gradualment GitHub Issues com a mecanisme de retroalimentació del contingut i el substituirem per un nou sistema de retroalimentació. Per obtenir més informació, consulteu: https://aka.ms/ContentUserFeedback.
Envieu i consulteu els comentaris de