Erstellen von Berichten für horizontal hochskalierte Clouddatenbanken (Vorschau)Reporting across scaled-out cloud databases (preview)

Abfrage über Shards hinweg

Horizontal partitionierte Datenbanken verteilen Zeilen auf einer horizontal hochskalierten Datenebene.Sharded databases distribute rows across a scaled out data tier. Das Schema ist auf allen teilnehmenden Datenbanken identisch, auch bekannt als horizontale Partitionierung.The schema is identical on all participating databases, also known as horizontal partitioning. Mit einer flexiblen Abfrage können Sie Berichte erstellen, die alle Datenbanken in einer horizontal partitionierten Datenbank umfassen.Using an elastic query, you can create reports that span all databases in a sharded database.

Wie Sie einen Schnellstart durchführen, erfahren Sie unter Erstellen von Berichten für horizontal hochskalierte Clouddatenbanken.For a quick start, see Reporting across scaled-out cloud databases.

Informationen zu nicht partitionierten Datenbanken finden Sie unter Ausführen von Abfragen über Clouddatenbanken mit unterschiedlichen Schemas hinweg.For non-sharded databases, see Query across cloud databases with different schemas.

VoraussetzungenPrerequisites

ÜbersichtOverview

Diese Anweisungen erstellen die Metadatendarstellung Ihrer Shardingdatenebene in der elastischen Abfragedatenbank.These statements create the metadata representation of your sharded data tier in the elastic query database.

  1. CREATE MASTER KEYCREATE MASTER KEY
  2. CREATE DATABASE SCOPED CREDENTIALCREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCECREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLECREATE EXTERNAL TABLE

1.1 Erstellen des Datenbankhauptschlüssels und der Anmeldeinformationen1.1 Create database scoped master key and credentials

Die Anmeldeinformationen werden von der elastischen Abfrage für die Verbindung mit Ihren Remotedatenbanken verwendet.The credential is used by the elastic query to connect to your remote databases.

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.Make sure that the "<username>" does not include any "@servername" suffix.

1.2 Erstellen externer Datenquellen1.2 Create external data sources

Syntax: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>’
               ) [;]

BeispielExample

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:Retrieve the list of current external data sources:

select * from sys.external_data_sources;

Die externe Datenquelle verweist auf Ihre Shardzuordnung.The external data source references your shard map. Eine elastische Abfrage verwendet anschließend die externe Datenquelle und zugrunde liegende Shardzuordnung zum Auflisten der Datenbanken, die zur Datenebene gehören.An elastic query then uses the external data source and the underlying shard map to enumerate the databases that participate in the data tier. 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 .The same credentials are used to read the shard map and to access the data on the shards during the processing of an elastic query.

1.3 Erstellen externer Tabellen1.3 Create external tables

Syntax: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

BeispielExample

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:Retrieve the list of external tables from the current database:

SELECT * from sys.external_tables;

So löschen Sie externe Tabellen:To drop external tables:

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

AnmerkungenRemarks

Die DATA_SOURCE-Klausel definiert die externe Datenquelle (eine Shardzuordnung), die für die externe Tabelle verwendet wird.The DATA_SOURCE clause defines the external data source (a shard map) that is used for the external table.

Die Klauseln SCHEMA_NAME und OBJECT_NAME ordnen die Definition der externen Tabelle einer Tabelle in einem anderen Schema zu.The SCHEMA_NAME and OBJECT_NAME clauses map the external table definition to a table in a different schema. Falls nicht angegeben, wird davon ausgegangen, dass das Schema des Remoteobjekts „dbo“ und sein Name mit dem definierten Namen der externen Tabelle identisch ist.If omitted, the schema of the remote object is assumed to be “dbo” and its name is assumed to be identical to the external table name being defined. Dies ist nützlich, wenn der Name der Remotetabelle bereits in der Datenbank verwendet wird, in der Sie die externe Tabelle erstellen möchten.This is useful if the name of your remote table is already taken in the database where you want to create the external table. Sie möchten z.B. eine externe Tabelle zum Abrufen einer aggregierten Sicht von Katalogsichten oder DMVs für Ihre horizontal hochskalierte Datenebene definieren.For example, you want to define an external table to get an aggregate view of catalog views or DMVs on your scaled out data tier. Da Katalogsichten und DMVs bereits lokal vorhanden sind, können Sie ihre Namen nicht für die Definition der externen Tabelle verwenden.Since catalog views and DMVs already exist locally, you cannot use their names for the external table definition. Verwenden Sie stattdessen in den Klauseln SCHEMA_NAME und/oder OBJECT_NAME einen anderen Namen und den Namen der Katalogsicht oder DMV.Instead, use a different name and use the catalog view’s or the DMV’s name in the SCHEMA_NAME and/or OBJECT_NAME clauses. (Betrachten Sie das folgende Beispiel.)(See the example below.)

Die DISTRIBUTION-Klausel gibt die Datenverteilung für diese Tabelle an:The DISTRIBUTION clause specifies the data distribution used for this table. Der Abfrageprozessor nutzt die Informationen in der DISTRIBUTION-Klausel, um die effizientesten Abfragepläne zu erstellen.The query processor utilizes the information provided in the DISTRIBUTION clause to build the most efficient query plans.

  1. SHARDED bedeutet, dass Daten Datenbanken übergreifend horizontal partitioniert werden.SHARDED means data is horizontally partitioned across the databases. Der Partitionierungsschlüssel für die Datenverteilung wird im <sharding_column_name> -Parameter erfasst.The partitioning key for the data distribution is the <sharding_column_name> parameter.
  2. REPLICATED bedeutet, dass identische Kopien der Tabelle in jeder Datenbank vorhanden sind.REPLICATED means that identical copies of the table are present on each database. Sie müssen sicherstellen, dass die Replikate in allen Datenbanken identisch sind.It is your responsibility to ensure that the replicas are identical across the databases.
  3. ROUND_ROBIN bedeutet, dass die Tabelle mit einer anwendungsabhängigen Verteilungsmethode horizontal partitioniert wurde.ROUND_ROBIN means that the table is horizontally partitioned using an application-dependent distribution method.

Datenschichtverweis: Die DDL für externe Tabellen verweist auf eine externe Datenquelle.Data tier reference: The external table DDL refers to an external data source. 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.The external data source specifies a shard map which provides the external table with the information necessary to locate all the databases in your data tier.

SicherheitshinweiseSecurity considerations

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.Users with access to the external table automatically gain access to the underlying remote tables under the credential given in the external data source definition. Vermeiden Sie eine unerwünschte Erhöhung von Berechtigungen durch die Anmeldeinformationen der externen Datenquelle.Avoid undesired elevation of privileges through the credential of the external data source. Verwenden Sie GRANT oder REVOKE für eine externe Tabelle, als handele es sich um eine normale Tabelle.Use GRANT or REVOKE for an external table just as though it were a regular table.

Nachdem Sie die externe Datenquelle und die externen Tabellen definiert haben, können Sie jetzt vollständiges T-SQL in den externen Tabellen verwenden.Once you have defined your external data source and your external tables, you can now use full T-SQL over your external tables.

Beispiel: Abfragen von horizontal partitionierten DatenbankenExample: querying horizontal partitioned databases

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.The following query performs a three-way join between warehouses, orders and order lines and uses several aggregates and a selective filter. Es wird Folgendes vorausgesetzt: 1.) eine horizontale Partitionierung, 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.It assumes (1) horizontal partitioning (sharding) and (2) that warehouses, orders and order lines are sharded by the warehouse id column, and that the elastic query can co-locate the joins on the shards and process the expensive part of the query on the shards in parallel.

    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_remoteStored procedure for remote T-SQL execution: sp_execute_remote

Mit der elastischen Abfrage wurde auch eine gespeicherte Prozedur eingeführt, die einen Direktzugriff auf die Shards bietet.Elastic query also introduces a stored procedure that provides direct access to the shards. Die gespeicherte Prozedur heißt sp_execute _remote und kann verwendet werden, um gespeicherte Remoteprozeduren oder T-SQL-Code in den Remotedatenbanken auszuführen.The stored procedure is called sp_execute _remote and can be used to execute remote stored procedures or T-SQL code on the remote databases. Hierfür werden die folgenden Parameter verwendet:It takes the following parameters:

  • Datenquellenname (nvarchar): Name der externen Datenquelle vom Typ RDBMS.Data source name (nvarchar): The name of the external data source of type RDBMS.
  • Abfrage (nvarchar): T-SQL-Abfrage, die für die einzelnen Shards ausgeführt wird.Query (nvarchar): The T-SQL query to be executed on each shard.
  • Parameterdeklaration (nvarchar) – optional: Zeichenfolge mit Datentypdefinitionen für die Parameter, die im Query-Parameter verwendet werden (wie sp_executesql).Parameter declaration (nvarchar) - optional: String with data type definitions for the parameters used in the Query parameter (like sp_executesql).
  • Parameterwertliste – optional: Durch Trennzeichen getrennte Liste von Parameterwerten (wie sp_executesql).Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

Die Prozedur „sp_execute_remote“ verwendet die externe Datenquelle, die in den Aufrufparametern angegeben ist, um die jeweilige T-SQL-Anweisung in den Remotedatenbanken auszuführen.The sp_execute_remote uses the external data source provided in the invocation parameters to execute the given T-SQL statement on the remote databases. Die Anmeldeinformationen der externen Datenquelle werden verwendet, um die Verbindung mit der ShardMapManager-Datenbank und den Remotedatenbanken herzustellen.It uses the credential of the external data source to connect to the shardmap manager database and the remote databases.

Beispiel:Example:

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

Konnektivität für ToolsConnectivity for 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.Use regular SQL Server connection strings to connect your application, your BI and data integration tools to the database with your external table definitions. Stellen Sie sicher, dass SQL Server als Datenquelle für das Tool unterstützt wird.Make sure that SQL Server is supported as a data source for your tool. 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.Then reference the elastic query database like any other SQL Server database connected to the tool, and use external tables from your tool or application as if they were local tables.

Bewährte MethodenBest practices

  • Stellen Sie sicher, dass die Endpunktdatenbank für elastische Abfragen durch die Firewalls von Azure SQL-Datenbank hindurch Zugriff auf die Datenbank mit der Shardzuordnung und alle Shards hat.Ensure that the elastic query endpoint database has been given access to the shardmap database and all shards through the SQL DB firewalls.
  • Überprüfen oder erzwingen Sie die in der externen Tabelle definierte Verteilung der Daten.Validate or enforce the data distribution defined by the external table. Wenn Ihre tatsächliche Datenverteilung sich von der Verteilung in der Tabellendefinition unterscheidet, können Ihre Abfragen zu unerwarteten Ergebnissen führen.If your actual data distribution is different from the distribution specified in your table definition, your queries may yield unexpected results.
  • 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.Elastic query currently does not perform shard elimination when predicates over the sharding key would allow it to safely exclude certain shards from processing.
  • Eine elastische Abfrage funktioniert am besten für Abfragen, in denen der größte Teil der Berechnung in den Shards erfolgt.Elastic query works best for queries where most of the computation can be done on the shards. 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.You typically get the best query performance with selective filter predicates that can be evaluated on the shards or joins over the partitioning keys that can be performed in a partition-aligned way on all shards. Für andere Abfragemuster müssen möglicherweise große Mengen von Daten aus den Shards auf den Hauptknoten geladen werden, wodurch Leistungseinbußen auftreten.Other query patterns may need to load large amounts of data from the shards to the head node and may perform poorly

Nächste SchritteNext steps