Delen via

Rapportage over uitgeschaalde clouddatabases (preview)

  • Artikel

Van toepassing op: Azure SQL Database

Query across shards

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 Rapportage over uitgeschaalde clouddatabases voor een quickstart.

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

Vereisten

Overzicht

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

  1. HOOFDSLEUTEL MAKEN
  2. DATABASESPECIFIEKE REFERENTIES MAKEN
  3. EXTERNE GEGEVENSBRON MAKEN
  4. CREATE EXTERNAL TABLE

1.1 Hoofdsleutel en referenties voor databasebereik maken

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

Notitie

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-toewijzing. 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 referenties worden gebruikt om de shard-toewijzing te lezen en om toegang te krijgen tot de gegevens op 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-component definieert de externe gegevensbron (een shard-toewijzing) 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 in de SCHEMA_NAME- en/of OBJECT_NAME-componenten. (Zie het onderstaande voorbeeld.)

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.

  1. SHARDED betekent dat gegevens horizontaal worden gepartitioneerd over de databases. De partitioneringssleutel voor de gegevensdistributie is de <sharding_column_name> parameter.
  2. GEREPLICEERD 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.
  3. 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 drierichtingsdeelname uit tussen magazijnen, orders en orderregels en maakt gebruik van verschillende aggregaties en een selectief filter. Hierbij wordt ervan uitgegaan dat (1) horizontale partitionering (sharding) en (2) dat magazijnen, orders en orderlijnen worden geshard door de kolom magazijn-id en dat de elastische query de joins op de shards kan vinden en het dure deel 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

Elastische query introduceert ook een opgeslagen procedure die directe toegang biedt tot de shards. 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).

De sp_execute_remote maakt gebruik 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 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.

Aanbevolen procedures

  • 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 uw 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 via de shardingsleutel toestaan dat bepaalde shards veilig worden uitgesloten van verwerking.
  • 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

Volgende stappen