Configuración de una base de datos SQL en una actividad de copia (versión preliminar)
En este artículo se describe cómo usar la actividad de copia en la canalización de datos para copiar datos desde y hacia SQL Database.
Configuración admitida
Para la configuración de cada pestaña en la actividad de copia, vaya a las secciones siguientes respectivamente.
General
Consulte la guía de Configuración general para configurar la pantalla de configuración General.
Fuente
Las siguientes propiedades son admitidas para la base de datos SQL en la pestaña Origen de una actividad de copia.
Las siguientes propiedades son necesarias:
Conexión: seleccione una base de datos SQL existente que haga referencia al paso de este artículo.
Usar consulta: puede elegir Tabla, Consulta o Procedimiento almacenado. En la lista siguiente se describe la configuración de cada configuración:
Tabla: especifique el nombre de la base de datos SQL para leer los datos. Elija una tabla existente en la lista desplegable o seleccione Escriba manualmente para escribir el esquema y el nombre de la tabla.
Consulta: especifique la consulta SQL personalizada para leer datos. Un ejemplo es
select * from MyTable. O bien, seleccione el icono de lápiz que se va a editar en el editor de código.
procedimiento almacenado: seleccione el procedimiento almacenado en la lista desplegable.
En Advanced, puede especificar los siguientes campos:
tiempo de espera de consulta (minutos): especifique el tiempo de espera para la ejecución del comando de consulta; el valor predeterminado es de 120 minutos. Si el parámetro se establece para esta propiedad, los valores permitidos son intervalos de tiempo, como "02:00:00" (120 minutos).
nivel de aislamiento: especifica el comportamiento de bloqueo de transacciones para el origen de SQL. Los valores permitidos son: Lectura confirmada, Lectura no confirmada, Lectura repetible, Serializableo Instantánea. Consulte IsolationLevel Enum para obtener más detalles.
opción de partición: Especifique las opciones de partición de datos utilizadas para cargar datos desde la base de datos SQL. Los valores permitidos son: Ninguno (valor predeterminado), particiones físicas de la tablay intervalo dinámico. Cuando se habilita una opción de partición (es decir, no Ninguno), el grado de paralelismo para cargar datos simultáneamente desde una base de datos SQL se controla mediante Grado de paralelismo de copia en la pestaña de configuración de la actividad de copia.
Ninguno: elija esta opción para no usar una partición.
particiones físicas de la tabla: cuando se usa una partición física, la columna de partición y el mecanismo se determinan automáticamente en función de la definición de la tabla física.
intervalo dinámico: cuando se usa la consulta con la opción paralela habilitada, se necesita el parámetro de partición de intervalo(
?DfDynamicRangePartitionCondition). Consulta de ejemplo:SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition.Nombre de la columna de origen: especifique el nombre de la columna de origen de tipo entero o fecha/hora (
int,smallint,bigint,date,smalldatetime,datetime,datetime2odatetimeoffset) que usa la partición de rangos para la copia paralela. Si no se especifica, el índice o la clave principal de la tabla se detectan automáticamente y se usan como columna de partición.Si usa una consulta para recuperar los datos de origen, conecte
?DfDynamicRangePartitionConditionen la cláusula WHERE. Para obtener un ejemplo, consulte la sección Copia en paralelo de la base de datos SQL.Límite superior de partición: Especifique el valor máximo de la columna de partición para dividir el rango de particiones. Este valor se utiliza para determinar el intervalo de partición, no para filtrar las filas de la tabla. Todas las filas de la tabla o el resultado de la consulta se particionarán y copiarán. Si no se especifica, la actividad de copia detecta automáticamente el valor. Para ver un ejemplo, consulte la sección Copia en paralelo desde una base de datos SQL.
Límite inferior de la partición: especifique el valor mínimo de la columna de partición para la división del rango de partición. Este valor se usa para decidir el intervalo de particiones, no para filtrar las filas de la tabla. Todas las filas de la tabla o el resultado de la consulta se particionarán y copiarán. Si no se especifica, la actividad de copia detecta automáticamente el valor. Para ver un ejemplo, consulte la sección Copia en paralelo desde una base de datos SQL.
Columnas adicionales: agregue más columnas de datos para almacenar la ruta de acceso relativa o el valor estático de los archivos de origen. La expresión se admite para este último. Para más información, vaya a Agregar columnas adicionales durante la copia.
Destino
Las siguientes propiedades son compatibles con la base de datos SQL en la ficha Destino de una actividad de copia.
Las siguientes propiedades son obligatorias:
Conexión: seleccione una base de datos SQL existente que haga referencia al paso de este artículo.
Opción de tabla: seleccione entre Usar existente o Crear tabla automáticamente.
Si selecciona Usar existente:
- Tabla: especifique el nombre de la base de datos SQL para escribir datos. Elija una tabla existente en la lista desplegable o seleccione Escriba manualmente para escribir el esquema y el nombre de la tabla.
Si selecciona Crear tabla automáticamente:
- Tabla: crea automáticamente la tabla (si no existe) en el esquema de origen, lo cual no es compatible cuando se usa un procedimiento almacenado como método de escritura.
En Advanced, puede especificar los siguientes campos:
Comportamiento de escritura: define el comportamiento de escritura cuando el origen son los archivos de un almacén de datos basado en archivos. Puede elegir Insertar, Upsert o Procedimiento almacenado.
Insertar: elija esta opción si los datos de origen tienen inserciones.
upsert: Elija esta opción si los datos de origen incluyen tanto inserciones como actualizaciones.
Use TempDB: especifique si se debe usar una tabla temporal global o una tabla física como tabla intermedia para upsert. De forma predeterminada, el servicio usa la tabla temporal global como tabla provisional y esta casilla está activada.
Si escribe una gran cantidad de datos en SQL Database, desmarque esta opción y especifique un nombre de esquema con el que Data Factory creará una tabla de preparación para cargar los datos de entrada y limpiar automáticamente al finalizar. Asegúrese de que el usuario tiene permiso para crear tabla en la base de datos y modificar el permiso en el esquema. Si no se especifica, se usa una tabla temporal global como almacenamiento provisional.
Seleccionar esquema de base de datos de usuario: cuando no se ha seleccionado Usar TempDB, especifique un nombre de esquema con el que Data Factory creará una tabla de preparación para cargar datos preprocesados y limpiarlos automáticamente tras la finalización del proceso. Asegúrese de que tiene permiso para crear tabla en la base de datos y modificar el permiso en el esquema.
Nota
Debe tener el permiso para crear y eliminar tablas. De forma predeterminada, una tabla provisional compartirá el mismo esquema que una tabla de destino.
columnas clave: elija la columna que se usa para determinar si una fila del origen coincide con una fila del destino.
nombre del procedimiento almacenado: seleccione el procedimiento almacenado en la lista desplegable.
Bloquear tabla de inserción masiva: elija Sí o No. Use esta configuración para mejorar el rendimiento de la copia durante una operación de inserción masiva en una tabla sin ningún índice de varios clientes. Para más información, consulte BULK INSERT (Transact-SQL)
script de copia previa: especifique un script para que se ejecute la actividad de copia antes de escribir datos en una tabla de destino en cada ejecución. Puede usar esta propiedad para limpiar los datos cargados previamente.
Tiempo de espera de escritura por lotes: especifique el tiempo que se concede a la operación de inserción por lotes para que finalice antes de que se agote el tiempo de espera. El valor permitido es timespan. El valor predeterminado es "00:30:00" (30 minutos).
Escribir tamaño de lote: especifique el número de filas que se van a insertar en la tabla SQL por lote. El valor permitido es entero (número de filas). De forma predeterminada, el servicio determina dinámicamente el tamaño de lote adecuado en función del tamaño de fila.
número máximo de conexiones simultáneas: especifique el límite superior de conexiones simultáneas establecidas en el almacén de datos durante la ejecución de la actividad. Especifique un valor solo cuando desee limitar las conexiones simultáneas.
Cartografía
Para la configuración de la pestaña Asignación, si no utiliza una base de datos SQL con creación automática de tablas como destino, diríjase a Asignación.
Si utiliza SQL Database con creación automática de tablas, excepto en la configuración de Asignación, puede editar el tipo de las columnas de destino. Después de seleccionar Importar esquemas, puede especificar el tipo de columna en el destino.
Por ejemplo, el tipo de la columna ID del origen es int, y puede cambiarlo al tipo float cuando se asigna a la columna de destino.
Configuración
Para la configuración de la pestaña Configuración, vaya a Configurar los otros parámetros en la pestaña configuración.
Copia paralela desde base de datos SQL
El conector de SQL Database en la actividad de copia proporciona particiones de datos integradas para copiar datos en paralelo. Puede encontrar opciones de particionado de datos en la pestaña Fuente de la actividad de copia.
Al habilitar la copia con particiones, la actividad de copia ejecuta consultas paralelas en el origen de la base de datos SQL para cargar datos por particiones. El grado paralelo se controla mediante el grado de paralelismo de copia en la pestaña configuración de la actividad de copia. Por ejemplo, si establece grado de paralelismo de copia en cuatro, el servicio genera y ejecuta simultáneamente cuatro consultas basadas en la configuración y la opción de partición especificadas, y cada consulta recupera una parte de los datos de la base de datos SQL.
Se recomienda habilitar la copia en paralelo con la creación de particiones de datos, especialmente cuando se carga una gran cantidad de datos de la base de datos SQL. A continuación se sugieren configuraciones para diferentes escenarios. Al copiar datos en el almacén de datos basado en archivos, se recomienda escribir en una carpeta como varios archivos (solo especificar el nombre de carpeta), en cuyo caso el rendimiento es mejor que escribir en un solo archivo.
| Escenario | Configuración sugerida |
|---|---|
| Carga completa de una tabla grande con particiones físicas. | Opción de partición: particiones físicas de la tabla. Durante la ejecución, el servicio detecta automáticamente las particiones físicas y copia los datos por particiones. Para comprobar si la tabla tiene una partición física o no, puede hacer referencia a esta consulta. |
| Carga completa desde una tabla grande, sin particiones físicas, utilizando una columna entera o de fecha y hora para la creación de particiones de datos. | Opciones de partición: partición de intervalo dinámico. Columna de partición (opcional): especifique la columna que se usa para particionar datos. Si no se especifica, se usa el índice o la columna de clave principal. Partición del límite superior y Partición del límite inferior (opcional): especifique si desea determinar el paso de partición. Esto no es para filtrar las filas de la tabla, todas las filas de la tabla se particionarán y copiarán. Si no se especifica, la actividad de copia detecta automáticamente los valores y puede tardar mucho tiempo en función de los valores MIN y MAX. Se recomienda proporcionar límite superior y límite inferior. Por ejemplo, si la columna de partición "ID" tiene valores comprendidos entre 1 y 100, y establece el límite inferior como 20 y el límite superior como 80, con la copia en paralelo como 4, el servicio recupera los datos por 4 particiones: identificadores en el intervalo <=20, [21, 50], [51, 80] y >=81, respectivamente. |
| Cargue una gran cantidad de datos mediante una consulta personalizada, sin utilizar particiones físicas, pero usando una columna de tipo entero o fecha/datetime para la partición de datos. | Opciones de partición: partición de intervalo dinámico. Consulta: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>.Columna de Partición: especifique la columna utilizada para particionar los datos. Partición del límite superior y Partición del límite inferior (opcional): especifique si desea determinar el paso de partición. Esto no es para filtrar las filas de la tabla, todas las filas del resultado de la consulta se particionarán y copiarán. Si no se especifica, la actividad de copia detecta automáticamente el valor. Por ejemplo, si la columna de partición "ID" tiene valores comprendidos entre 1 y 100, y establece el límite inferior como 20 y el límite superior como 80, con la copia en paralelo como 4, el servicio recupera los datos por 4 particiones: identificadores en el intervalo <=20, [21, 50], [51, 80] y >=81, respectivamente. Estas son más consultas de ejemplo para diferentes escenarios: • Consultar toda la tabla: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition• Consulta desde una tabla con selección de columnas y filtros de cláusulas WHERE adicionales: SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>• Consulta con subconsultas: SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>• Consulta con partición en una subconsulta: SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T |
Procedimientos recomendados para cargar datos con la opción de partición:
- Elija una columna distintiva como columna de partición (como clave principal o clave única) para evitar la asimetría de datos.
- Si la tabla tiene una partición integrada, use la opción de partición Particiones físicas de la tabla para obtener un mejor rendimiento.
Consulta de ejemplo para comprobar la partición física
SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
WHERE s.name='[your schema]' AND t.name = '[your table name]'
Si la tabla tiene particiones físicas, se verá "HasPartition" como "sí", como en el ejemplo siguiente.
Resumen de tabla
Las tablas siguientes contienen más información sobre la actividad de copia en SQL Database.
Fuente
| Nombre | Descripción | Valor | Obligatorio | Propiedad de script JSON |
|---|---|---|---|---|
| Conexión | La conexión al almacén de datos de origen. | <la conexión> | Sí | conexión |
| Usar consulta | La manera de leer datos. Aplique Tabla para leer datos de la tabla especificada o aplique Consulta para leer datos mediante consultas SQL. | • Tabla • Consulta • Procedimiento almacenado |
Sí | / |
| Para Tabla | ||||
| nombre de esquema | Nombre del esquema. | < nombre de esquema > | No | esquema |
| nombre de la tabla | Nombre de la tabla. | < nombre de la tabla > | No | table |
| Para Consulta | ||||
| Consultar | Especifique la consulta SQL personalizada para leer datos. Por ejemplo: SELECT * FROM MyTable. |
< Consultas SQL > | No | sqlReaderQuery |
| Para Procedimiento almacenado | ||||
| nombre de procedimiento almacenado | Nombre del procedimiento almacenado. | < el nombre del procedimiento almacenado > | No | sqlReaderStoredProcedureName |
| tiempo de espera de consulta (minutos) | El tiempo de espera para la ejecución del comando de consulta, el valor predeterminado es de 120 minutos. Si el parámetro se establece para esta propiedad, los valores permitidos son intervalos de tiempo, como "02:00:00" (120 minutos). | intervalo de tiempo | No | queryTimeout |
| nivel de aislamiento | Especifica el comportamiento de bloqueo de transacciones para el origen de SQL. | • Lectura confirmada • Lectura no confirmada • Lectura repetible • Serializable • Snapshot |
No | isolationLevel: • ReadCommitted • ReadUncommitted • RepeatableRead • Serializable • Snapshot |
| Opción de partición | Las opciones de particionado de datos usadas para cargar datos de la base de datos SQL. | •Ninguno • Particiones físicas de la tabla • Intervalo dinámico |
No | opción de partición • PhysicalPartitionsOfTable • DynamicRange |
| Para Rango dinámico | ||||
| Nombre de columna de partición | El nombre de la columna de origen de tipo entero o fecha/datetime (int, smallint, bigint, date, smalldatetime, datetime, datetime2 o datetimeoffset) que se usa en la creación de particiones por rangos para la copia en paralelo. Si no se especifica, el índice o la clave principal de la tabla se detectan automáticamente y se usan como columna de partición. Si usa una consulta para recuperar los datos de origen, incluya ?DfDynamicRangePartitionCondition en la cláusula WHERE. |
< los nombres de columna de partición > | No | partitionColumnName |
| Límite superior de partición | Valor máximo de la columna de partición para la división del rango de partición. Este valor se usa para decidir el intervalo de particiones, no para filtrar las filas de la tabla. Todas las filas de la tabla o el resultado de la consulta se particionarán y copiarán. Si no se especifica, la actividad de copia detecta automáticamente el valor. | < límite superior de partición > | No | partitionUpperBound |
| Límite inferior de partición | Valor mínimo de la columna de partición para la división del rango de partición. Este valor se usa para decidir el salto de partición, no para filtrar las filas en una tabla. Todas las filas de la tabla o el resultado de la consulta se particionarán y copiarán. Si no se especifica, la actividad de copia detecta automáticamente el valor. | < límite inferior de partición > | No | partitionLowerBound |
| Columnas adicionales | Agregue más columnas de datos para almacenar la ruta de acceso relativa o el valor estático de los archivos de origen. La expresión se admite para este último. | •Nombre • Valor |
No | additionalColumns: •nombre •valor |
Destino
| Nombre | Descripción | Valor | Obligatorio | Propiedad de script JSON |
|---|---|---|---|---|
| Conexión | La conexión al almacén de datos de destino. | <su conexión> | Sí | conexión |
| Opción de tabla | La tabla de datos de destino. Seleccione entre Usar existente o Crear tabla automáticamente. | Usar lo existente • Crear tabla automáticamente |
Sí | esquema table |
| Comportamiento de escritura | Define el comportamiento de escritura cuando el origen es archivos de un almacén de datos basado en archivos. | • Insert • Upsert • Procedimiento almacenado |
No | writeBehavior: • insert • upsert • sqlWriterStoredProcedureName |
| Bloqueo de tabla de inserción masiva | Use esta configuración para mejorar el rendimiento de la copia durante una operación de inserción masiva en una tabla sin ningún índice de varios clientes. | Sí o No (valor predeterminado) | No | sqlWriterUseTableLock: true o false (valor predeterminado) |
| Para Actualizar/Insertar | ||||
| Usar TempDB | Decidir si usar una tabla temporal global o una tabla física como tabla interina para una operación de inserción o actualización. | seleccionado (valor predeterminado) o no seleccionado | No | useTempDB: true (valor predeterminado) o false |
| Columnas de clave | Elija qué columna se usa para determinar si una fila del origen coincide con una fila del destino. | < columna de clave> | No | claves |
| Para Procedimiento almacenado | ||||
| nombre de procedimiento almacenado | Esta propiedad es el nombre del procedimiento almacenado que lee los datos de la tabla de origen. La última instrucción SQL debe ser una instrucción SELECT en el procedimiento almacenado. | < nombre de procedimiento almacenado > | No | sqlWriterStoredProcedureName |
| Script de copia previa | Script para que la actividad de copia se ejecute antes de escribir datos en una tabla de destino en cada ejecución. Puede usar esta propiedad para limpiar los datos cargados previamente. | <Script de copia previa> (cadena) |
No | preCopyScript |
| Tiempo de espera de escritura por lotes | Tiempo que se concede a la operación de inserción por lotes para que finalice antes de que se agote el tiempo de espera. El valor permitido es TimeSpan. El valor predeterminado es "00:30:00" (30 minutos). | intervalo de tiempo | No | writeBatchTimeout |
| Tamaño del lote de escritura | Número de filas que se van a insertar en la tabla SQL por lote. De forma predeterminada, el servicio determina dinámicamente el tamaño de lote adecuado en función del tamaño de fila. | <número de filas > (entero) |
No | writeBatchSize |
| número máximo de conexiones simultáneas | Límite superior de conexiones simultáneas establecidas en el almacén de datos durante la ejecución de la actividad. Especifique un valor solo cuando desee limitar las conexiones simultáneas. | <límite superior de conexiones simultáneas> (entero) |
No | máximo de conexiones concurrentes |