Freigeben über

Erstellen von Berichten für horizontal hochskalierte Clouddatenbanken (Vorschau)

Gilt für::Azure SQL-Datenbank

Horizontal partitionierte Datenbanken verteilen Zeilen auf einer horizontal hochskalierten Datenebene. Das Schema ist auf allen teilnehmenden Datenbanken identisch, auch bekannt als horizontale Partitionierung. Mit einer flexiblen Abfrage können Sie Berichte erstellen, die alle Datenbanken in einer horizontal partitionierten Datenbank umfassen.

Diagramm, wie Abfragen über Shards hinweg funktionieren.

Eine Schnellstartanleitung finden Sie unter Bericht über skalierte Clouddatenbanken (Vorschau).

Informationen zu nicht shardierten Datenbanken finden Sie unter Abfrage in Clouddatenbanken mit unterschiedlichen Schemas (Vorschau).For non-sharded databases, see Query across cloud databases with different schemas (preview).

Voraussetzungen

Übersicht

Diese Anweisungen erstellen die Metadatendarstellung Ihrer Shardingdatenebene in der elastischen Abfragedatenbank.

  1. MASTERSCHLÜSSEL ERSTELLEN
  2. DATENBANKBEREICHSBEZOGENE ANMELDEINFORMATIONEN ERSTELLEN
  3. EXTERNE DATENQUELLE ERSTELLEN
  4. EXTERNE TABELLE ERSTELLEN

1.1 Erstellen des Datenbankhauptschlüssels und der Anmeldeinformationen

Die Anmeldeinformationen werden von der elastischen Abfrage für die Verbindung mit Ihren Remotedatenbanken verwendet.

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

Hinweis

Stellen Sie sicher, dass der „<Benutzername>“ kein „@Servername“-Suffix enthält.

1.2 Erstellen externer Datenquellen

Syntax:

<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>'
               ) [;]

Beispiel

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

Rufen Sie die Liste der aktuellen externen Datenquellen ab:

select * from sys.external_data_sources;

Die externe Datenquelle verweist auf Ihre Shardzuordnung. Eine elastische Abfrage verwendet anschließend die externe Datenquelle und zugrunde liegende Shardzuordnung zum Auflisten der Datenbanken, die zur Datenebene gehören.

Die gleichen Anmeldeinformationen werden während der Verarbeitung der elastischen Abfrage zum Lesen der Shardzuordnung und für den Zugriff auf die Daten in den Shards verwendet .

1.3 Erstellen externer Tabellen

Syntax:

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

Beispiel

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)
);

Rufen Sie die Liste der externen Tabellen aus der aktuellen Datenbank ab:

SELECT * from sys.external_tables;

So löschen Sie externe Tabellen:

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

Bemerkungen

Die DATA_SOURCE-Klausel definiert die externe Datenquelle (eine Shard-Map), die für die externe Tabelle verwendet wird.

Die SCHEMA_NAME- und OBJECT_NAME-Klauseln bilden die Definition der externen Tabelle auf eine Tabelle in einem anderen Schema ab. Falls nicht angegeben, wird davon ausgegangen, dass das Schema des Remoteobjekts dbo und sein Name mit dem definierten Namen der externen Tabelle identisch ist. Dies ist nützlich, wenn der Name der Remotetabelle bereits in der Datenbank verwendet wird, in der Sie die externe Tabelle erstellen möchten. Sie möchten z. B. eine externe Tabelle zum Abrufen einer aggregierten Sicht von Katalogsichten oder DMVs für Ihre horizontal hochskalierte Datenebene definieren. Da Katalogsichten und DMVs bereits lokal vorhanden sind, können Sie ihre Namen nicht für die Definition der externen Tabelle verwenden. Verwenden Sie stattdessen einen anderen Namen und nutzen Sie den Namen der Katalogansicht oder den DMV-Namen in den SCHEMA_NAME und/oder OBJECT_NAME Bedingungen. (Siehe das Beispiel später.)

Die DISTRIBUTION Klausel gibt die für diese Tabelle verwendete Datenverteilung an. Der Abfrageprozessor verwendet die in der DISTRIBUTION Klausel bereitgestellten Informationen, um die effizientesten Abfragepläne zu erstellen.

  1. SHARDED bedeutet, dass Daten horizontal in den Datenbanken partitioniert werden. Der Partitionierungsschlüssel für die Datenverteilung ist der Parameter <sharding_column_name>.
  2. REPLICATED bedeutet, dass in jeder Datenbank identische Kopien der Tabelle vorhanden sind. Sie müssen sicherstellen, dass die Replikate in allen Datenbanken identisch sind.
  3. ROUND_ROBIN bedeutet, dass die Tabelle mithilfe einer anwendungsabhängigen Verteilungsmethode horizontal partitioniert wird.

Datenschichtverweis: Die DDL für externe Tabellen verweist auf eine externe Datenquelle. Die externe Datenquelle gibt eine Shardzuordnung an, die der externen Tabelle die Informationen bereitstellt, die benötigt werden, um alle Datenbanken in Ihrer Datenebene zu finden.

Sicherheitshinweise

Benutzer mit Zugriff auf die externe Tabelle erhalten automatisch Zugriff auf die zugrunde liegenden Remotetabellen gemäß den Anmeldeinformationen, die in der externen Datenquellendefinition angegeben sind. Vermeiden Sie eine unerwünschte Erhöhung von Berechtigungen durch die Anmeldeinformationen der externen Datenquelle. Verwenden Sie GRANT oder REVOKE für eine externe Tabelle, als handele es sich um eine normale Tabelle.

Nachdem Sie die externe Datenquelle und die externen Tabellen definiert haben, können Sie jetzt vollständiges T-SQL in den externen Tabellen verwenden.

Beispiel: Abfragen von horizontal partitionierten Datenbanken

Die folgende Abfrage führt eine Verknüpfung in drei Richtungen zwischen Lagern, Bestellungen und Auftragspositionen aus. Sie nutzt mehrere Aggregate und einen selektiven Filter. Es wird Folgendes vorausgesetzt: 1.) eine horizontale Partitionierung (Sharding), 2.) dass für Lager, Aufträge und Auftragspositionen ein Sharding anhand der Spalte „warehouse ID“ erfolgt ist, und 3.) dass die elastische Abfrage die Verknüpfungen für die Shards anordnen und den aufwendigen Teil der Abfrage in den Shards parallel verarbeiten kann.

    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

Gespeicherte Prozedur für T-SQL-Remoteausführung: sp_execute_remote

Mit der elastischen Abfrage wurde auch eine gespeicherte Prozedur eingeführt, die einen Direktzugriff auf die Shards bietet. Die gespeicherte Prozedur sp_execute_remote ermöglicht das Ausführen von entfernten gespeicherten Prozeduren oder T-SQL-Code in den Remotedatenbanken. Hierfür werden die folgenden Parameter verwendet:

  • Datenquellenname (nvarchar): Der Name der externen Datenquelle vom Typ RDBMS.
  • Abfrage (nvarchar): Die T-SQL-Abfrage, die für jede Shard ausgeführt werden soll.
  • Parameterdeklaration (nvarchar) – optional: Zeichenfolge mit Datentypdefinitionen für die parameter, die im Abfrageparameter verwendet werden (z. B. sp_executesql)
  • Parameterwertliste - optional: Durch Trennzeichen getrennte Liste von Parameterwerten (z. B sp_executesql. )

Die sp_execute_remote externe Datenquelle wird in den Aufrufparametern verwendet, um die angegebene T-SQL-Anweisung für die Remotedatenbanken auszuführen. Die Anmeldeinformationen der externen Datenquelle werden verwendet, um die Verbindung mit der ShardMapManager-Datenbank und den Remotedatenbanken herzustellen.

Beispiel:

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

Konnektivität für Tools

Verwenden Sie herkömmliche SQL Server-Verbindungszeichenfolgen, um Ihre Anwendung sowie Ihre BI- und Datenintegrationstools für die Datenbank mit Ihren Definitionen externer Tabellen zu verbinden. Stellen Sie sicher, dass SQL Server als Datenquelle für das Tool unterstützt wird. Verweisen Sie dann auf die elastische Abfragedatenbank wie auf beliebige andere mit dem Tool verbundenen SQL Server-Datenbanken, und nutzen Sie externe Tabellen in Ihren Tools oder Anwendungen, als wären es lokale Tabellen.

Bewährte Methoden

  • Stellen Sie sicher, dass die Endpunktdatenbank für elastische Abfragen durch die Firewalls von SQL-Datenbank hindurch Zugriff auf die Datenbank mit der Shardzuordnung und alle Shards hat.
  • Überprüfen oder erzwingen Sie die in der externen Tabelle definierte Verteilung der Daten. Wenn sich die tatsächliche Datenverteilung von der in der Tabellendefinition angegebenen Verteilung unterscheidet, ergeben Ihre Abfragen möglicherweise unerwartete Ergebnisse.
  • Die elastische Abfrage führt derzeit keine Shardlöschung durch, wenn Prädikate für Shardingschlüssel ein gefahrloses Ausschließen der Verarbeitung bestimmter Shards zulassen würden.
  • Eine elastische Abfrage funktioniert am besten für Abfragen, in denen der größte Teil der Berechnung in den Shards erfolgt. In der Regel erhalten Sie optimale Abfrageleistung mit benutzerdefinierten Filterprädikaten, die in den Shards oder Verknüpfungen über die Partitionierungsschlüssel ausgewertet werden können, die auf partitionsbezogene Weise auf allen Shards ausgeführt werden können. Andere Abfragemuster müssen möglicherweise große Datenmengen aus den Shards in den Kopfknoten laden und können schlecht ausgeführt werden.

Verwandte Inhalte

  • Last updated on