Compartir a través de

Informes sobre bases de datos de escalado horizontal en la nube (vista previa)

Se aplica a:Azure SQL Database

Importante

La consulta elástica en el modo de administrador de mapas de fragmentos (particionamiento horizontal), usando el tipo EXTERNAL DATA SOURCESHARD_MAP_MANAGER, llegará al final del soporte el 31 de marzo de 2027. Después de esta fecha, las cargas de trabajo existentes seguirán funcionando, pero ya no recibirán soporte técnico y la creación de nuevos orígenes de datos externos de tipo SHARD_MAP_MANAGER ya no será posible. Para ver las opciones de migración, consulte la Guía de migración del modo de administrador del mapa de fragmentos de consultas elásticas.

Las bases de datos particionadas distribuyen las filas en un nivel de datos escalado horizontalmente. El esquema es idéntico en todas las bases de datos participantes, también conocidos como partición horizontal. Utilice una consulta elástica para crear informes que abarquen todas las bases de datos en una base de datos particionada.

Diagrama de cómo funcionan las consultas entre fragmentos.

Para un inicio rápido, consulte Informes de bases de datos escaladas horizontalmente en la nube (versión preliminar).

Para las bases de datos no fragmentadas, consulte Consulta entre bases de datos en la nube con esquemas diferentes (versión preliminar).

Requisitos previos

Información general

Estas instrucciones crean la representación de los metadatos de la capa de datos particionada en la base de datos de consulta elástica.

  1. CREAR CLAVE MAESTRA
  2. CREAR CREDENCIALES ESPECÍFICAS PARA LA BASE DE DATOS
  3. CREAR FUENTE DE DATOS EXTERNA
  4. CREATE EXTERNAL TABLE (CREAR TABLA EXTERNA)

1.1 Crear una clave maestra con ámbito de base de datos y credenciales

La consulta elástica utiliza la credencial para conectarse a tus bases de datos remotas.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

Nota:

Asegúrese de que "<username>" no incluya ningún sufijo "servername".

1.2 Creación de orígenes de datos externos

Sintaxis:

<External_Data_Source> ::=
    CREATE EXTERNAL DATA SOURCE <data_source_name> WITH
        (TYPE = SHARD_MAP_MANAGER,
                   LOCATION = '<fully_qualified_server_name>',
        DATABASE_NAME = '<shardmap_database_name>',
        CREDENTIAL = <credential_name>,
        SHARD_MAP_NAME = '<shardmapname>'
               ) [;]

Ejemplo

CREATE EXTERNAL DATA SOURCE MyExtSrc
WITH
(
    TYPE=SHARD_MAP_MANAGER,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='ShardMapDatabase',
    CREDENTIAL= SMMUser,
    SHARD_MAP_NAME='ShardMap'
);

Recupera la lista de orígenes de datos externos actuales:

select * from sys.external_data_sources;

El origen de datos externo hace referencia al mapa de particiones. Una consulta elástica usa el origen de datos externo y el mapa de particiones subyacente para enumerar las bases de datos que participan en la capa de datos.

Se usan las mismas credenciales para leer el mapa de particiones y para tener acceso a los datos de las particiones durante el procesamiento de una consulta elástica.

1.3 Creación de tablas externas

Sintaxis:

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name  
    ( { <column_definition> } [ ,...n ])
    { WITH ( <sharded_external_table_options> ) }
) [;]  

<sharded_external_table_options> ::=
  DATA_SOURCE = <External_Data_Source>,
  [ SCHEMA_NAME = N'nonescaped_schema_name',]
  [ OBJECT_NAME = N'nonescaped_object_name',]
  DISTRIBUTION = SHARDED(<sharding_column_name>) | REPLICATED |ROUND_ROBIN

Ejemplo

CREATE EXTERNAL TABLE [dbo].[order_line](
     [ol_o_id] int NOT NULL,
     [ol_d_id] tinyint NOT NULL,
     [ol_w_id] int NOT NULL,
     [ol_number] tinyint NOT NULL,
     [ol_i_id] int NOT NULL,
     [ol_delivery_d] datetime NOT NULL,
     [ol_amount] smallmoney NOT NULL,
     [ol_supply_w_id] int NOT NULL,
     [ol_quantity] smallint NOT NULL,
      [ol_dist_info] char(24) NOT NULL
)

WITH
(
    DATA_SOURCE = MyExtSrc,
     SCHEMA_NAME = 'orders',
     OBJECT_NAME = 'order_details',
    DISTRIBUTION=SHARDED(ol_w_id)
);

Recuperación de la lista de tablas externas de la base de datos actual:

SELECT * from sys.external_tables;

Para borrar tablas externas:

DROP EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name[;]

Observaciones

La DATA_SOURCE cláusula define el origen de datos externo (un mapa de particiones) que se usa para la tabla externa.

Las cláusulas SCHEMA_NAME y OBJECT_NAME asignan la definición de la tabla externa a una tabla en un esquema diferente. Si se omite, se considera que el esquema del objeto remoto es dbo y que su nombre es idéntico al de la tabla externa que se define. Esto es útil si el nombre de la tabla remota ya existe en la base de datos donde desea crear la tabla externa. Por ejemplo, quiere definir una tabla externa para obtener una vista agregada de las vistas de catálogo o DMV en la capa de datos con escala horizontal. Puesto que las vistas de catálogo y DMV ya existen localmente, no se pueden usar sus nombres para la definición de la tabla externa. En su lugar, use un nombre diferente y el nombre de la vista de catálogo o la DMV en las cláusulas SCHEMA_NAME y/o OBJECT_NAME. (Vea el ejemplo más adelante).

La DISTRIBUTION cláusula especifica la distribución de datos utilizada para esta tabla. El procesador de consultas utiliza la información proporcionada en la DISTRIBUTION cláusula para crear los planes de consulta más eficaces.

  1. SHARDED significa que los datos se particionan horizontalmente entre las bases de datos. La clave de creación de particiones para la distribución de datos es el parámetro <sharding_column_name>.
  2. REPLICATED significa que hay copias idénticas de la tabla en cada base de datos. Es responsabilidad suya asegurarse de que las réplicas son idénticas en las bases de datos.
  3. ROUND_ROBIN significa que la tabla se particiona horizontalmente mediante un método de distribución dependiente de la aplicación.

Referencia de capa de datos: el DDL de tabla externa hace referencia a un origen de datos externo. El origen de datos externo especifica un mapa de particiones que proporciona a la tabla externa la información necesaria para localizar todas las bases de datos en la capa de datos.

Consideraciones sobre la seguridad

Los usuarios con acceso a la tabla externa obtienen automáticamente acceso a las tablas remotas subyacentes con la credencial proporcionada en la definición del origen de datos externo. Evite la elevación no deseada de privilegios a través de la credencial del origen de datos externo. Use GRANT o REVOKE para una tabla externa como si fuera una tabla normal.

Una vez que defina el origen de datos externo y las tablas externas, puede usar el T-SQL completo en las tablas externas.

Ejemplo: consulta de bases de datos con particiones horizontales

La consulta siguiente realiza una combinación en tres direcciones entre almacenes, pedidos y líneas de pedido y utiliza varios agregados y un filtro selectivo. Asume (1) fragmentación horizontal (sharding) y (2) que los almacenes, pedidos y líneas de pedido se fragmentan por la columna del identificador de almacén, y que la consulta elástica puede co-localizar las uniones en los fragmentos y procesar la parte costosa de la consulta en los fragmentos en paralelo.

    select  
         w_id as warehouse,
         o_c_id as customer,
         count(*) as cnt_orderline,
         max(ol_quantity) as max_quantity,
         avg(ol_amount) as avg_amount,
         min(ol_delivery_d) as min_deliv_date
    from warehouse
    join orders
    on w_id = o_w_id
    join order_line
    on o_id = ol_o_id and o_w_id = ol_w_id
    where w_id > 100 and w_id < 200
    group by w_id, o_c_id

Procedimiento almacenado para la ejecución remota de T-SQL: sp_execute_remote

La consulta elástica también introduce un procedimiento almacenado que proporciona acceso directo a los fragmentos. El procedimiento almacenado se denomina sp_execute_remote y se puede usar para ejecutar procedimientos almacenados remotos o código T-SQL en las bases de datos remotas. Toma los parámetros siguientes:

  • Nombre del origen de datos (nvarchar): nombre del origen de datos externo de tipo RDBMS.
  • Consulta (nvarchar): la consulta T-SQL que se va a ejecutar en cada partición.
  • Declaración de parámetros (nvarchar): opcional: cadena con definiciones de tipo de datos para los parámetros usados en el parámetro Query (por ejemplo, sp_executesql)
  • Lista de valores de parámetro: opcional: lista separada por comas de valores de parámetro (como sp_executesql)

sp_execute_remote usa el origen de datos externo proporcionado en los parámetros de invocación para ejecutar la instrucción T-SQL dada en las bases de datos remotas. Utiliza la credencial del origen de datos externo para conectarse a la base de datos de ShardMapManager y las bases de datos remotas.

Ejemplo:

    EXEC sp_execute_remote
        N'MyExtSrc',
        N'select count(w_id) as foo from warehouse'

Conectividad para herramientas

Use cadenas de conexión de SQL Server normales para conectar su aplicación, sus herramientas de integración de datos o de BI a la base de datos con sus definiciones de tablas externas. Asegúrese de que SQL Server se admite como origen de datos para la herramienta. A continuación, haga referencia a la base de datos de consulta elástica como cualquier otra base de datos de SQL Server conectada a la herramienta y use las tablas externas desde su herramienta o aplicación como si fueran tablas locales.

Procedimientos recomendados

  • Asegúrese de que se ha concedido acceso a la base de datos de puntos de conexión de consulta elástica para la base de datos del mapa de particiones y todas las particiones a través de los firewalls de SQL Database.
  • Valide o aplique la distribución de datos definida por la tabla externa. Si la distribución de datos real es diferente de la distribución especificada en la definición de tabla, las consultas podrían producir resultados inesperados.
  • La consulta elástica actualmente no realiza la eliminación de fragmentos cuando los predicados sobre la clave de fragmentación permitirían excluir de forma segura ciertos fragmentos del procesamiento.
  • Las consultas elásticas funcionan mejor para aquellas consultas en las que la mayor parte del cálculo se puede realizar en los fragmentos. Normalmente, se obtiene el máximo rendimiento de consultas con predicados de filtros selectivos que se pueden evaluar en los fragmentos o con uniones sobre las claves de particiones que se pueden realizar de manera alineada con las particiones en todos los fragmentos. Es posible que otros patrones de consulta necesiten cargar grandes cantidades de datos de los fragmentos en el nodo principal y pueden tener un rendimiento deficiente.

Contenido relacionado

  • Last updated on