Fråga mellan molndatabaser med olika scheman (förhandsversion)

Gäller för: Azure SQL databas

Fråga mellan tabeller i olika databaser

Vertikalt partitionerade databaser använder olika uppsättningar tabeller i olika databaser. Det innebär att schemat skiljer sig från olika databaser. Till exempel finns alla tabeller för inventering på en databas medan alla redovisningsrelaterade tabeller finns i en andra databas.

Förutsättningar

  • Användaren måste ha behörigheten ALTER ANY EXTERNAL DATA SOURCE (ÄNDRA EXTERN DATAKÄLLA). Den här behörigheten ingår i ALTER DATABASE-behörigheten.
  • ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.THE ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

Översikt

Anteckning

Till skillnad från horisontell partitionering är dessa DDL-instruktioner inte beroende av att definiera en datanivå med en shardkarta via klientbiblioteket för elastisk databas.

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

Skapa databasomfattande huvudnyckel och autentiseringsuppgifter

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

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

Anteckning

Kontrollera att suffixet <username>"@servername" inte ingår.

Skapa externa datakällor

Syntax:

<External_Data_Source> ::=
CREATE EXTERNAL DATA SOURCE <data_source_name> WITH
    (TYPE = RDBMS,
    LOCATION = ’<fully_qualified_server_name>’,
    DATABASE_NAME = ‘<remote_database_name>’,  
    CREDENTIAL = <credential_name>
    ) [;]

Viktigt

Parametern TYPE måste anges till RDBMS.

Exempel

I följande exempel visas användningen av CREATE-instruktionen för externa datakällor.

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
   WITH
      (
         TYPE=RDBMS,
         LOCATION='myserver.database.windows.net',
         DATABASE_NAME='ReferenceData',
         CREDENTIAL= SqlUser
      );

Så här hämtar du listan över aktuella externa datakällor:

select * from sys.external_data_sources;

Externa tabeller

Syntax:

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name  
    ( { <column_definition> } [ ,...n ])
    { WITH ( <rdbms_external_table_options> ) }
    )[;]

<rdbms_external_table_options> ::=
    DATA_SOURCE = <External_Data_Source>,
    [ SCHEMA_NAME = N'nonescaped_schema_name',]
    [ OBJECT_NAME = N'nonescaped_object_name',]

Exempel

CREATE EXTERNAL TABLE [dbo].[customer]
   (
      [c_id] int NOT NULL,
      [c_firstname] nvarchar(256) NULL,
      [c_lastname] nvarchar(256) NOT NULL,
      [street] nvarchar(256) NOT NULL,
      [city] nvarchar(256) NOT NULL,
      [state] nvarchar(20) NULL
   )
   WITH
   (
      DATA_SOURCE = RemoteReferenceData
   );

I följande exempel visas hur du hämtar listan över externa tabeller från den aktuella databasen:

select * from sys.external_tables;

Kommentarer

Elastisk fråga utökar den befintliga externa tabellsyntaxen för att definiera externa tabeller som använder externa datakällor av typen RDBMS. En extern tabelldefinition för vertikal partitionering omfattar följande aspekter:

  • Schema: Den externa tabellen DDL definierar ett schema som dina frågor kan använda. Schemat som anges i den externa tabelldefinitionen måste matcha schemat för tabellerna i fjärrdatabasen där faktiska data lagras.
  • Fjärrdatabasreferens: Den externa tabellen DDL refererar till en extern datakälla. Den externa datakällan anger servernamnet och databasnamnet för fjärrdatabasen där faktiska tabelldata lagras.

Med hjälp av en extern datakälla enligt beskrivningen i föregående avsnitt är syntaxen för att skapa externa tabeller följande:

Satsen DATA_SOURCE definierar den externa datakällan (dvs. fjärrdatabasen i vertikal partitionering) som används för den externa tabellen.

Satserna SCHEMA_NAME och OBJECT_NAME tillåter mappning av den externa tabelldefinitionen till en tabell i ett annat schema på fjärrdatabasen eller till en tabell med ett annat namn. Den här mappningen är användbar om du vill definiera en extern tabell till en katalogvy eller DMV på fjärrdatabasen – eller någon annan situation där fjärrtabellnamnet redan tas lokalt.

Följande DDL-instruktion tar bort en befintlig extern tabelldefinition från den lokala katalogen. Det påverkar inte fjärrdatabasen.

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

Behörigheter för CREATE/DROP EXTERNAL TABLE: ALTER ANY EXTERNAL DATA SOURCE permissions krävs för den externa tabellen DDL, som också behövs för att referera till den underliggande datakällan.

Säkerhetsöverväganden

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. Hantera åtkomsten till den externa tabellen noggrant för att undvika oönstrade privilegier genom autentiseringsuppgifter för den externa datakällan. Vanliga SQL-behörigheter kan användas för att bevilja eller återkalla åtkomst till en extern tabell precis som om det vore en vanlig tabell.

Exempel: köra frågor mot lodrätt partitionerade databaser

Följande fråga utför en trevägskoppling mellan de två lokala tabellerna för beställningar och orderrader och fjärrtabellen för kunder. Det här är ett exempel på referensdataanvändningsfallet för elastisk fråga:

    SELECT
     c_id as customer,
     c_lastname as customer_name,
     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 customer
    JOIN orders
    ON c_id = o_c_id
    JOIN  order_line
    ON o_id = ol_o_id and o_c_id = ol_c_id
    WHERE c_id = 100

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 fjärrdatabasen. Den lagrade proceduren kallas sp_execute _remote och kan användas för att köra fjärrlagringsprocedurer eller T-SQL-kod på fjärrdatabasen. 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å fjärrdatabasen.
  • 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 över 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ärrdatabasen. Den använder autentiseringsuppgifterna för den externa datakällan för att ansluta till fjärrdatabasen.

Exempel:

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

Anslutning för verktyg

Du kan använda vanliga SQL Server anslutningssträngar för att ansluta dina BI- och dataintegreringsverktyg till databaser på servern som har elastiska frågeaktiverade och externa tabeller definierade. Kontrollera att SQL Server stöds som datakälla för ditt verktyg. Referera sedan till den elastiska frågedatabasen och dess externa tabeller precis som andra SQL Server databas som du skulle ansluta till med verktyget.

Bästa praxis

  • Kontrollera att den elastiska frågeslutpunktsdatabasen har fått åtkomst till fjärrdatabasen genom att aktivera åtkomst för Azure Services i brandväggskonfigurationen för Azure SQL Database. Se också till att autentiseringsuppgifterna i definitionen för den externa datakällan kan logga in på fjärrdatabasen och har behörighet att komma åt fjärrtabellen.
  • Elastisk fråga fungerar bäst för frågor där merparten av beräkningen kan göras på fjärrdatabaserna. Du får vanligtvis bästa frågeprestanda med selektiva filterpredikat som kan utvärderas på fjärrdatabaser eller kopplingar som kan utföras helt på fjärrdatabasen. Andra frågemönster kan behöva läsa in stora mängder data från fjärrdatabasen och kan ge dåliga prestanda.

Nästa steg