Rapportering över utskalade molndatabaser (förhandsversion)

  • Artikel

Gäller för:Azure SQL Database

Query across shards

Fragmenterade databaser distribuerar rader över en utskalad datanivå. Schemat är identiskt för alla deltagande databaser, även kallat horisontell partitionering. Med hjälp av en elastisk fråga kan du skapa rapporter som sträcker sig över alla databaser i en fragmenterad databas.

En snabbstart finns i Rapportering över utskalade molndatabaser.

För icke-shardade databaser, se Fråga mellan molndatabaser med olika scheman.

Förutsättningar

Översikt

Dessa instruktioner skapar metadatarepresentationen av din fragmenterade datanivå i den elastiska frågedatabasen.

  1. SKAPA HUVUDNYCKEL
  2. SKAPA DATABASBEGRÄNSADE AUTENTISERINGSUPPGIFTER
  3. SKAPA EN EXTERN DATAKÄLLA
  4. SKAPA EXTERN TABELL

1.1 Skapa huvudnyckel och autentiseringsuppgifter för databasomfattning

Autentiseringsuppgifterna används av den elastiska frågan för att ansluta till dina fjärrdatabaser.

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

Kommentar

Kontrollera att användarnamnet <>inte innehåller något suffix för "@servername".

1.2 Skapa externa datakällor

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

Exempel

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

Hämta listan över aktuella externa datakällor:

select * from sys.external_data_sources;

Den externa datakällan refererar till fragmentkartan. En elastisk fråga använder sedan den externa datakällan och den underliggande fragmentkartan för att räkna upp de databaser som ingår i datanivån. Samma autentiseringsuppgifter används för att läsa fragmentkartan och för att komma åt data på fragmenten under bearbetningen av en elastisk fråga.

1.3 Skapa externa tabeller

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

Exempel

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

Hämta listan över externa tabeller från den aktuella databasen:

SELECT * from sys.external_tables;

Så här släpper du externa tabeller:

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

Anmärkningar

Satsen DATA_SOURCE definierar den externa datakällan (en fragmentkarta) som används för den externa tabellen.

Satserna SCHEMA_NAME och OBJECT_NAME mappar den externa tabelldefinitionen till en tabell i ett annat schema. Om det utelämnas antas schemat för fjärrobjektet vara dbo och namnet antas vara identiskt med det externa tabellnamn som definieras. Detta är användbart om namnet på fjärrtabellen redan finns i databasen där du vill skapa den externa tabellen. Du vill till exempel definiera en extern tabell för att få en sammanställd vy över katalogvyer eller DMV:er på den utskalade datanivån. Eftersom katalogvyer och DMV:er redan finns lokalt kan du inte använda deras namn för den externa tabelldefinitionen. Använd i stället ett annat namn och använd katalogvyns eller DMV:ets namn i satserna SCHEMA_NAME och/eller OBJECT_NAME. (Se exemplet nedan.)

DISTRIBUTION-satsen anger den datadistribution som används för den här tabellen. Frågeprocessorn använder informationen i DISTRIBUTION-satsen för att skapa de mest effektiva frågeplanerna.

  1. SHARDED innebär att data partitioneras vågrätt mellan databaserna. Partitioneringsnyckeln för datadistributionen är parametern <sharding_column_name> .
  2. REPLIKERAD innebär att identiska kopior av tabellen finns i varje databas. Det är ditt ansvar att se till att replikerna är identiska mellan databaserna.
  3. ROUND_ROBIN innebär att tabellen partitioneras vågrätt med hjälp av en programberoende distributionsmetod.

Referens till datanivå: Den externa tabellens DDL refererar till en extern datakälla. Den externa datakällan anger en fragmentkarta som ger den externa tabellen den information som krävs för att hitta alla databaser på datanivån.

Säkerhetsfrågor

Användare med åtkomst till den externa tabellen får automatiskt åtkomst till de underliggande fjärrtabellerna under de autentiseringsuppgifter som anges i definitionen för den externa datakällan. Undvik oönskade utökade privilegier via autentiseringsuppgifterna för den externa datakällan. Använd GRANT eller REVOKE för en extern tabell som om det vore en vanlig tabell.

När du har definierat din externa datakälla och dina externa tabeller kan du nu använda fullständig T-SQL över dina externa tabeller.

Exempel: köra frågor mot vågräta partitionerade databaser

Följande fråga utför en trevägskoppling mellan lager, order och orderrader och använder flera aggregat och ett selektivt filter. Det förutsätter (1) horisontell partitionering (horisontell partitionering) och (2) att lager, order och orderrader fragmenteras av kolumnen för lager-ID och att den elastiska frågan kan samplacera kopplingarna på fragmenten och bearbeta den dyra delen av frågan på shards parallellt.

    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

Lagrad procedur för fjärrkörning av T-SQL: sp_execute_remote

Elastisk fråga introducerar också en lagrad procedur som ger direkt åtkomst till shards. Den lagrade proceduren kallas sp_execute _remote och kan användas för att köra fjärrlagrade procedurer eller T-SQL-kod på fjärrdatabaserna. Det tar följande parametrar:

  • Datakällans namn (nvarchar): Namnet på den externa datakällan av typen RDBMS.
  • Fråga (nvarchar): T-SQL-frågan som ska köras på varje shard.
  • Parameterdeklaration (nvarchar) – valfritt: Sträng med datatypsdefinitioner för parametrarna som används i frågeparametern (till exempel sp_executesql).
  • Parametervärdelista – valfritt: Kommaavgränsad lista med parametervärden (till exempel sp_executesql).

Sp_execute_remote använder den externa datakällan som anges i anropsparametrarna för att köra den angivna T-SQL-instruktionen på fjärrdatabaserna. Den använder autentiseringsuppgifterna för den externa datakällan för att ansluta till shardmap manager-databasen och fjärrdatabaserna.

Exempel:

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

Anslutning för verktyg

Använd vanliga SQL Server-anslutningssträngar för att ansluta ditt program, dina BI- och dataintegreringsverktyg till databasen med dina externa tabelldefinitioner. Kontrollera att SQL Server stöds som datakälla för verktyget. Referera sedan till den elastiska frågedatabasen som alla andra SQL Server-databaser som är anslutna till verktyget och använd externa tabeller från ditt verktyg eller program som om de vore lokala tabeller.

Regelverk

  • Kontrollera att den elastiska frågeslutpunktsdatabasen har fått åtkomst till shardmap-databasen och alla shards via SQL Database-brandväggarna.
  • Verifiera eller framtvinga den datadistribution som definierats av den externa tabellen. Om din faktiska datadistribution skiljer sig från den distribution som anges i tabelldefinitionen kan dina frågor ge oväntade resultat.
  • Elastisk fråga utför för närvarande inte shard-eliminering när predikat över partitioneringsnyckeln skulle göra det möjligt för den att på ett säkert sätt utesluta vissa shards från bearbetning.
  • Elastisk fråga fungerar bäst för frågor där merparten av beräkningen kan göras på shards. Du får vanligtvis bästa frågeprestanda med selektiva filterpredikat som kan utvärderas på shards eller kopplingar över partitioneringsnycklarna som kan utföras på ett partitionsjusterat sätt på alla shards. Andra frågemönster kan behöva läsa in stora mängder data från shards till huvudnoden och kan fungera dåligt

Nästa steg