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

  • Artikel

Gäller för:Azure SQL Database

Query across tables in different databases

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 ÄNDRA EXTERN DATAKÄLLA. Den här behörigheten ingår i alter database-behörigheten.
  • ÄNDRA EVENTUELLA BEHÖRIGHETER FÖR EXTERN DATAKÄLLA krävs för att referera till den underliggande datakällan.

Översikt

Kommentar

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 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 = 'master_key_password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

Kommentar

Se till 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;

Anmärkningar

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 tabellens 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 tabellens DDL refererar till en extern datakälla. Den externa datakällan anger servernamnet och databasnamnet för fjärrdatabasen där de 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 släpper 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 SKAPA/SLÄPP EXTERN TABELL: ÄNDRA EVENTUELLA BEHÖRIGHETER FÖR EXTERN DATAKÄLLA behövs för den externa tabellenS DDL, som också behövs för att referera till den underliggande datakällan.

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. Hantera åtkomsten till den externa tabellen noggrant för att undvika oönskade utökade privilegier via den externa datakällans autentiseringsuppgifter. 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 vertikalt 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ärrlagrade procedurer 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 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ä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ågor aktiverade och externa tabeller definierade. Kontrollera att SQL Server stöds som datakälla för verktyget. Se sedan den elastiska frågedatabasen och dess externa tabeller precis som alla andra SQL Server-databaser som du skulle ansluta till med verktyget.

Regelverk

  • 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 som anges 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