Rapporteren over horizontaal geschaalde clouddatabases (voorbeeld)

2025-06-16

Van toepassing op:Azure SQL Database

Shard-databases verdelen rijen over een uitgeschaalde gegevenslaag. Het schema is identiek voor alle deelnemende databases, ook wel horizontale partitionering genoemd. Met behulp van een elastische query kunt u rapporten maken die alle databases in een shard-database omvatten.

Zie Rapport over uitgeschaalde clouddatabases (preview) voor een snelle start.

Zie Query's uitvoeren in clouddatabases met verschillende schema's (preview) voor niet-sharddatabases.

Vereiste voorwaarden

Maak een shard-kaart met behulp van de Elastic Database-clientbibliotheek. zie Databases uitschalen met shard-toewijzingsbeheer. Of gebruik de voorbeeld-app in Aan de slag met Elastic Database Tools.
U kunt ook bestaande databases migreren om uit te schalen.
De gebruiker moet de machtiging ALTER ANY EXTERNAL DATA SOURCE hebben. Deze machtiging is opgenomen in de machtiging ALTER DATABASE.
ALTER ANY EXTERNAL DATA SOURCE machtigingen zijn nodig om naar de onderliggende gegevensbron te verwijzen.

Overzicht

Met deze instructies maakt u de metagegevensweergave van uw shard-gegevenslaag in de elastische querydatabase.

1.1 Master-sleutel en inloggegevens maken die specifiek zijn voor het databasebereik

De referentie wordt gebruikt door de elastische query om verbinding te maken met uw externe databases.

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

Opmerking

Zorg ervoor dat de gebruikersnaam<> geen achtervoegsel '@servername' bevat.

1.2 Externe gegevensbronnen maken

Syntaxis:

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

Voorbeeld

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

Haal de lijst met huidige externe gegevensbronnen op:

select * from sys.external_data_sources;

De externe gegevensbron verwijst naar uw shard map. Een elastische query maakt vervolgens gebruik van de externe gegevensbron en de onderliggende shardtoewijzing om de databases op te sommen die deelnemen aan de gegevenslaag.

Dezelfde inloggegevens worden gebruikt om de shard-toewijzing te lezen en om toegang te krijgen tot de gegevens van de shards tijdens de verwerking van een elastische query.

1.3 Externe tabellen maken

Syntaxis:

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

Voorbeeld

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

Haal de lijst met externe tabellen op uit de huidige database:

SELECT * from sys.external_tables;

Externe tabellen verwijderen:

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

Opmerkingen

De DATA_SOURCE clausule definieert de externe gegevensbron (een shard-map) die wordt gebruikt voor de externe tabel.

De SCHEMA_NAME en OBJECT_NAME componenten wijzen de definitie van de externe tabel toe aan een tabel in een ander schema. Als u dit weglaat, wordt ervan uitgegaan dat het schema van het externe object is dbo en dat de naam ervan identiek is aan de naam van de externe tabel die wordt gedefinieerd. Dit is handig als de naam van de externe tabel al wordt gebruikt in de database waarin u de externe tabel wilt maken. U wilt bijvoorbeeld een externe tabel definiëren om een geaggregeerde weergave van catalogusweergaven of DMV's op de uitgeschaalde gegevenslaag op te halen. Omdat catalogusweergaven en DMV's al lokaal bestaan, kunt u hun namen niet gebruiken voor de definitie van de externe tabel. Gebruik in plaats daarvan een andere naam en gebruik de naam van de catalogusweergave of de DMV-naam in de SCHEMA_NAME en/of OBJECT_NAME clausules. (Zie het voorbeeld later.)

De DISTRIBUTION component geeft de gegevensdistributie op die voor deze tabel wordt gebruikt. De queryprocessor maakt gebruik van de informatie in de DISTRIBUTION component om de meest efficiënte queryplannen te bouwen.

SHARDED betekent dat gegevens horizontaal worden gepartitioneerd in de databases. De partitioneringssleutel voor de gegevensdistributie is de <sharding_column_name> parameter.
REPLICATED betekent dat identieke kopieën van de tabel aanwezig zijn in elke database. Het is uw verantwoordelijkheid om ervoor te zorgen dat de replica's identiek zijn in de databases.
ROUND_ROBIN betekent dat de tabel horizontaal wordt gepartitioneerd met behulp van een toepassingsafhankelijke distributiemethode.

Naslaginformatie over de gegevenslaag: De externe tabel-DDL verwijst naar een externe gegevensbron. De externe gegevensbron geeft een shard-toewijzing op die de externe tabel voorziet van de informatie die nodig is om alle databases in uw gegevenslaag te vinden.

Beveiligingsoverwegingen

Gebruikers met toegang tot de externe tabel krijgen automatisch toegang tot de onderliggende externe tabellen onder de referentie die is opgegeven in de definitie van de externe gegevensbron. Vermijd ongewenste uitbreiding van bevoegdheden via de referentie van de externe gegevensbron. Gebruik GRANT of REVOKE voor een externe tabel alsof deze een gewone tabel was.

Zodra u uw externe gegevensbron en uw externe tabellen hebt gedefinieerd, kunt u nu volledige T-SQL gebruiken voor uw externe tabellen.

Voorbeeld: query's uitvoeren op horizontale gepartitioneerde databases

De volgende query voert een driedelige join uit tussen magazijnen, orders en orderregels en maakt gebruik van meerdere aggregaten en een selectief filter. Er wordt aangenomen dat (1) horizontale partitionering (sharding) plaatsvindt en (2) dat magazijnen, orders en orderlijnen worden geshard door de kolom magazijn-id en dat een elastische query de joins op de shards kan coördineren en de zware delen van de query parallel op de shards kan verwerken.

    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

Opgeslagen procedure voor externe T-SQL-uitvoering: sp_execute_remote

Elastic Query introduceert ook een opgeslagen procedure die directe toegang tot de shards biedt. De opgeslagen procedure wordt sp_execute_remote genoemd en kan worden gebruikt voor het uitvoeren van externe opgeslagen procedures of T-SQL-code op de externe databases. Hiervoor worden de volgende parameters gebruikt:

Naam van gegevensbron (nvarchar): de naam van de externe gegevensbron van het type RDBMS.
Query (nvarchar): De T-SQL-query die op elke shard moet worden uitgevoerd.
Parameterdeclaratie (nvarchar) - optioneel: Tekenreeks met gegevenstypedefinities voor de parameters die worden gebruikt in de queryparameter (zoals sp_executesql)
Lijst met parameterwaarden - optioneel: door komma's gescheiden lijst met parameterwaarden (zoals sp_executesql)

Hierbij sp_execute_remote wordt gebruikgemaakt van de externe gegevensbron die is opgegeven in de aanroepparameters om de opgegeven T-SQL-instructie uit te voeren op de externe databases. Hierbij wordt de referentie van de externe gegevensbron gebruikt om verbinding te maken met de shardmap manager-database en de externe databases.

Voorbeeld:

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

Connectiviteit voor hulpprogramma's

Gebruik reguliere SQL Server-verbindingsreeksen om uw toepassing, uw BI en hulpprogramma's voor gegevensintegratie te verbinden met de database met uw externe tabeldefinities. Zorg ervoor dat SQL Server wordt ondersteund als een gegevensbron voor uw hulpprogramma. Verwijs vervolgens naar de elastische querydatabase zoals elke andere SQL Server-database die is verbonden met het hulpprogramma en gebruik externe tabellen uit uw hulpprogramma of toepassing alsof het lokale tabellen zijn.

Beste praktijken

Zorg ervoor dat de eindpuntdatabase voor elastische query's toegang heeft gekregen tot de shardmapdatabase en alle shards via de SQL Database-firewalls.
Valideer of dwing de gegevensdistributie af die is gedefinieerd door de externe tabel. Als de werkelijke gegevensdistributie verschilt van de distributie die is opgegeven in de tabeldefinitie, kunnen uw query's onverwachte resultaten opleveren.
Elastische query voert momenteel geen shard-verwijdering uit wanneer predicaten over de shardingsleutel het toelaten om bepaalde shards veilig van verwerking uit te sluiten.
Elastische query werkt het beste voor query's waarbij de meeste berekeningen op de shards kunnen worden uitgevoerd. Doorgaans krijgt u de beste queryprestaties met selectief filterpredicaten die kunnen worden geëvalueerd op de shards of joins via de partitioneringssleutels die kunnen worden uitgevoerd op een partitiegerichte manier op alle shards. Andere querypatronen moeten mogelijk grote hoeveelheden gegevens laden van de shards naar het hoofdknooppunt en kunnen slecht presteren.