Índices de almacén de columnas: Guía de carga de datosColumnstore indexes - Data loading guidance

SE APLICA A: síSQL Server síAzure SQL Database síAzure Synapse Analytics (SQL DW) síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

En este artículo se describen las opciones y las recomendaciones para cargar datos en un índice de almacén de columnas mediante los métodos de inserción gradual y carga masiva SQL estándar.Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. La carga de datos en un índice de almacén de columnas es una parte esencial de cualquier proceso de almacenamiento de datos, ya que mueve la información al índice que se está preparando para el análisis.Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

¿Es la primera vez que utiliza índices de almacén de columnas?New to columnstore indexes? Vea Introducción a los índices de almacén de columnas y Arquitectura de los índices de almacén de columnas.See Columnstore indexes - overview and Columnstore Index Architecture.

¿Qué es la carga masiva?What is bulk loading?

Con "carga masiva" nos referimos a la forma en que se agregan grandes cantidades de filas a un almacén de datos.Bulk loading refers to the way large numbers of rows are added to a data store. Se trata de la forma más eficaz para mover datos a un índice de almacén de columnas, ya que funciona en lotes de filas.It is the most performant way to move data into a columnstore index because it operates on batches of rows. La carga masiva llena los grupos de filas hasta su capacidad máxima y los comprime directamente en el almacén de columnas.Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. Solo las filas al final de una carga que no cumplan los requisitos mínimos de 102 400 filas por grupo de filas pasan al almacén delta.Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.

Para realizar una carga masiva, puede usar la utilidad bcp, Integration Services o seleccionar las filas de una tabla de almacenamiento provisional.To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

Carga en un índice agrupado de almacén de columnasLoading into a clustered columnstore index

Tal y como se recomienda en el diagrama, una carga masiva:As the diagram suggests, a bulk load:

  • No ordena los datos previamente.Does not pre-sort the data. Los datos se insertan en grupos de filas en el orden en que se reciben.Data is inserted into rowgroups in the order it is received.
  • Si el tamaño de lote es mayor o igual que 102400, las filas estarán directamente en el grupo de filas comprimido.If the batch size is >= 102400, the rows are directly into the compressed rowgroups. Se recomienda elegir un tamaño de lote mayor o igual que 102400 para que la importación en bloque se realice de forma eficaz, ya que puede evitar que se muevan las filas de datos a un grupo de filas delta antes de que las filas se terminen moviendo a grupos de filas comprimidos mediante el motor de tupla (TM), un subproceso en segundo plano.It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).
  • Si el tamaño del lote o el de las filas restantes es menor que 102 400, las filas se cargarán en grupos de filas delta.If the batch size < 102,400 or if the remaining rows are < 102,400, the rows are loaded into delta rowgroups.

Nota

En una tabla de almacén de filas con datos de índices de almacén de columnas no agrupados, SQL ServerSQL Server siempre inserta los datos en la tabla base.On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. Nunca se insertan directamente en el índice de almacén de columnas.The data is never inserted directly into the columnstore index.

La carga masiva tiene estas optimizaciones de rendimiento integradas:Bulk loading has these built-in performance optimizations:

  • Cargas en paralelo: Puede realizar varias importaciones simultáneas masivas (bcp o inserción masiva) cargando un archivo de datos independiente.Parallel loads: You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. A diferencia de las cargas masivas del almacén de filas en SQL ServerSQL Server, no hay que especificar TABLOCK, ya que cada subproceso de importación en bloque cargará los datos solamente en grupos de filas independientes (grupos de filas delta o comprimidos) aplicando un bloqueo exclusivo.Unlike rowstore bulk loads into SQL ServerSQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into separate rowgroups (compressed or delta rowgroups) with exclusive lock on it.

  • Registro reducido: Los datos que se cargan directamente en grupos de filas comprimidos conducen a una reducción significativa del tamaño del registro.Reduced Logging: The data that is directly loaded into compressed row groups leads to significant reduction in the size of the log. Por ejemplo, si los datos se han comprimido 10 veces, el registro de transacciones correspondiente será aproximadamente 10 veces más pequeño sin necesidad de TABLOCK o de un modelo de recuperación simple u optimizado para cargas masivas de registros.For example, if data was compressed 10x, the corresponding transaction log will be roughly 10x smaller without requiring TABLOCK or Bulk-logged/Simple recovery model. Los datos que van a un grupo de filas delta se registran por completo,Any data that goes to a delta rowgroup is fully logged. incluidos los tamaños de lote con menos de 102 400 filas.This includes any batch sizes that are less than 102,400 rows. El procedimiento recomendado es usar batchsize >= 102400.Best practice is to use batchsize >= 102400. Dado que no se requiere TABLOCK, puede cargar los datos en paralelo.Since there is no TABLOCK required, you can load the data in parallel.

  • Registro mínimo: Puede obtener una mayor reducción del registro si sigue los requisitos previos para el registro mínimo.Minimal logging: You can get further reduction in logging if you follow the prerequisites for minimal logging. Sin embargo, a diferencia de la carga de datos en un almacén de filas, TABLOCK conduce a un bloqueo X en la tabla en lugar de un bloqueo BU (actualización masiva) y, por lo tanto, no se puede realizar la carga de datos paralela.However, unlike loading data into a rowstore, TABLOCK leads to an X lock on the table rather than a BU (Bulk Update) lock and therefore parallel data load cannot be done. Para obtener más información acerca del bloqueo [Versiones de fila y bloqueo[(../sql-server-transaction-locking-and-row-versioning-guide.md).For more information on locking, see [Locking and row versioning[(../sql-server-transaction-locking-and-row-versioning-guide.md).

  • Optimization de bloqueo: El bloqueo X de un grupo de filas se adquiere automáticamente al cargar los datos en un grupo de filas comprimido.Locking Optimization: The X lock on a row group is automatically acquired when loading data into a compressed row group. Sin embargo, cuando la carga masiva se realiza en un grupo de filas delta, se obtiene un bloqueo X en el grupo de filas, pero SQL ServerSQL Server continúa bloqueando los bloqueos de página y extensión, ya que el bloqueo de grupos de filas X no forma parte de la jerarquía de bloqueo.However, when bulk loading into a delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

Si tiene un índice no agrupado de árbol B en un índice de almacén de columnas, no habrá ninguna optimización de registro ni de bloqueo para el propio índice. Sin embargo, las optimizaciones del índice agrupado de almacén de columnas se seguirán aplicando, tal como se ha descrito anteriormente.If you have a nonclustered B-tree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are applicable.

Planeación de los tamaños de carga masiva para minimizar los grupos de filas deltaPlan bulk load sizes to minimize delta rowgroups

Los índices de almacén de columnas funcionan mejor cuando la mayoría de las filas se comprimen en el almacén de columnas y no se ubican en grupos de filas delta.Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. Se recomienda cambiar el tamaño de las cargas para que las filas pasen directamente al almacén de columnas y omitan lo máximo posible el almacén delta.It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

En estos escenarios siguientes se describe cuándo las filas cargadas pasan directamente al almacén de columnas o cuándo van al almacén delta.These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. En este ejemplo, cada grupo de filas puede tener de 102.400 a 1.048.576 filas.In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. En la práctica, el tamaño máximo de un grupo de filas puede ser inferior a 1 048 576 filas cuando hay presión de memoria.In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

Filas que se cargarán de forma masivaRows to Bulk Load Filas agregadas al grupo de filas comprimidoRows Added to the Compressed Rowgroup Filas agregadas al grupo de filas deltaRows Added to the Delta Rowgroup
102,000102,000 00 102,000102,000
145,000145,000 145,000145,000

Tamaño del grupo de filas: 145,000Rowgroup size: 145,000
00
1,048,5771,048,577 1,048,5761,048,576

Tamaño del grupo de filas: 1 048 576.Rowgroup size: 1,048,576.
11
2,252,1522,252,152 2,252,1522,252,152

Tamaños de los grupos de filas: 1 048 576, 1 048 576, 155 000.Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00
     

En el ejemplo siguiente se muestran los resultados de cargar 1 048 577 filas en una tabla.The following example shows the results of loading 1,048,577 rows into a table. Los resultados muestran un grupo de filas COMPRESSED en el almacén de columnas (como segmentos de columna comprimidos) y una fila en el almacén delta.The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id, 
  state, state_desc, total_rows, deleted_rows, size_in_bytes   
FROM sys.dm_db_column_store_row_group_physical_stats  

Grupo de filas y almacenamiento delta para una carga por lotesRowgroup and deltastore for a batch load

Uso de una tabla de almacenamiento provisional para mejorar el rendimientoUse a staging table to improve performance

Si está cargando datos solo para colocarlos en etapa antes de ejecutar más transformaciones, será mucho más rápido cargar la tabla en una tabla del montón que cargar los datos en una tabla agrupada de almacén de columnas.If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. Además, si carga los datos en una [tabla temporal][Temporary], el proceso también será mucho más rápido que cargar una tabla en un almacenamiento permanente.In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

Un patrón común de carga de datos consiste en cargar los datos en una tabla de almacenamiento provisional, realizar alguna transformación y, después, cargarlos en la tabla de destino con el siguiente comando:A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  
SELECT <list of columns> FROM <Staging Table>  

Este comando carga los datos en el índice de almacén de columnas de una forma similar a bcp o la tarea de inserción masiva, solo que en un único lote.This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. Si el número de filas de la tabla de almacenamiento provisional es menor que 102400, las filas se cargarán en un grupo de filas delta; en caso contrario, se cargarán directamente en un grupo de filas comprimido.If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. Antes existía una limitación importante: esta operación INSERT era de un solo subproceso.One key limitation was that this INSERT operation was single threaded. Para cargar los datos en paralelo, podía crear varias tablas de almacenamiento provisional o ejecutar INSERT/SELECT con intervalos no superpuestos de filas desde la tabla de almacenamiento provisional.To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. Esta limitación desaparece en SQL Server 2016 (13.x)SQL Server 2016 (13.x).This limitation goes away with SQL Server 2016 (13.x)SQL Server 2016 (13.x). El siguiente comando carga los datos en paralelo de la tabla de almacenamiento provisional, pero tendrá que especificar TABLOCK.The command below loads the data from staging table in parallel but you will need to specify TABLOCK. Puede que le resulte contradictorio con respecto a lo que se dijo anteriormente de la carga masiva, pero la principal diferencia es que la carga de datos en paralelo desde la tabla de almacenamiento provisional se ejecuta en la misma transacción.You may find this contradictory to what was said earlier with bulkload but the key difference is the parallel data load from the staging table is executed under the same transaction.

INSERT INTO <columnstore index> WITH (TABLOCK) 
SELECT <list of columns> FROM <Staging Table>  

Las siguientes optimizaciones están disponibles cuando se cargan datos en índices de almacén de columnas agrupados desde tablas de almacenamiento provisional:There are following optimizations available when loading into clustered columnstore index from staging table:

  • Optimización del registro: Registro reducido cuando los datos se cargan en el grupo de filas comprimido.Log Optimization: Reduced logging when the data is loaded into compressed rowgroup.
  • Optimization de bloqueo: Cuando se cargan datos en un grupo de filas comprimido, se obtiene el bloqueo X en el grupo de filas.Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. Sin embargo, con el grupo de filas delta, se obtiene un bloqueo X en el grupo de filas, pero SQL ServerSQL Server continúa bloqueando los bloqueos de página y extensión, ya que el bloqueo de grupos de filas X no forma parte de la jerarquía de bloqueo.However, with delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

Si tiene uno o varios índices no agrupados, no habrá ninguna optimización de registro o bloqueo para el propio índice; sin embargo, las optimizaciones en el índice de almacén de columnas agrupado siguen estando disponibles, tal y como se describió anteriormente.If you have or more nonclustered indexes, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there

¿Qué es la inserción gradual?What is trickle insert?

Con "inserción gradual" nos referimos a la forma de mover filas individuales al índice de almacén de columnas.Trickle insert refers to the way individual rows move into the columnstore index. Esta característica usa la instrucción INSERT INTO.Trickle inserts use the INSERT INTO statement. Gracias a la inserción gradual, todas las filas pasan al almacén delta.With trickle insert, all of the rows go to the deltastore. Es útil para un número reducido de filas, pero no es práctico para cargas de gran tamaño.This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

Nota

Los subprocesos simultáneos que usan INSERT INTO para insertar valores en un índice de almacén de columnas agrupado pueden insertar filas en el mismo grupo de filas de almacén delta.Concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

Una vez que el grupo de filas contiene 1 048 576 filas, el grupo de filas delta se marca como cerrado, pero sigue estando disponible para las consultas y las operaciones de actualización o eliminación. No obstante, las filas recién insertadas se pasan a un grupo de filas de almacén delta existente o recién creado.Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. Hay un subproceso en segundo plano, el motor de tupla (TM) , que comprime los grupos de filas delta cerrados cada 5 minutos aproximadamente.There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. Puede invocar expresamente el siguiente comando para comprimir el grupo de filas delta cerrado.You can explicitly invoke the following command to compress the closed delta rowgroup

ALTER INDEX <index-name> on <table-name> REORGANIZE  

Si desea forzar el cierre o la compresión de un grupo de filas delta, ejecute el siguiente comando.If you want force a delta rowgroup closed and compressed, you can execute the following command. Se recomienda ejecutar este comando si ha terminado de cargar las filas y no espera insertar nuevas filas.You may want run this command if you are done loading the rows and don't expect any new rows. Al cerrar y comprimir expresamente el grupo de filas delta, puede ahorrar más espacio de almacenamiento y mejorar el rendimiento de las consultas de análisis.By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. Sugerimos invocar este comando si no espera insertar nuevas filas.A best practice is to invoke this command if you don't expect new rows to be inserted.

ALTER INDEX <index-name> on <table-name> REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)  

Funcionamiento de la carga en una tabla con particionesHow loading into a partitioned table works

Para los datos con particiones, SQL ServerSQL Server primero asigna cada fila a una partición y después realiza operaciones del almacén de columnas con los datos de la partición.For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. Cada partición tiene sus propios grupos de filas y, al menos, un grupo de filas delta.Each partition has its own rowgroups and at least one delta rowgroup.

Pasos siguientesNext steps

Entrada de blog hospedada ahora en techcommunity, escrita el 11 de marzo de 2015: Data Loading performance considerations with Clustered Columnstore indexes (Consideraciones de rendimiento con los índices de almacén de columnas en clúster de carga de datos).Blog post now hosted on techcommunity, written 2015-03-11: Data Loading performance considerations with Clustered Columnstore indexes.