Query's uitvoeren in clouddatabases met verschillende schema's (preview)

  • Artikel

Van toepassing op: Azure SQL Database

Query across tables in different databases

Verticaal gepartitioneerde databases maken gebruik van verschillende sets tabellen in verschillende databases. Dit betekent dat het schema afwijkt in verschillende databases. Zo bevinden alle tabellen voor de inventarisatie zich in één database, terwijl alle aan de administratie gerelateerde tabellen zich in een tweede database bevinden.

Vereisten

  • De gebruiker moet de machtiging ALTER ANY EXTERNAL DATA SOURCE hebben. Deze machtiging is opgenomen in de machtiging ALTER DATABASE.
  • Machtigingen voor ALTER ANY EXTERNAL DATA SOURCE zijn nodig om te verwijzen naar de onderliggende gegevensbron.

Overzicht

Notitie

In tegenstelling tot horizontale partitionering zijn deze DDL-instructies niet afhankelijk van het definiëren van een gegevenslaag met een shardtoewijzing via de clientbibliotheek voor elastische databases.

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

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

Notitie

Zorg ervoor dat het <username> achtervoegsel '@servername' niet bevat.

Externe gegevensbronnen maken

Syntaxis:

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

Belangrijk

De parameter TYPE moet worden ingesteld op RDBMS.

voorbeeld

In het volgende voorbeeld ziet u het gebruik van de CREATE-instructie voor externe gegevensbronnen.

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

De lijst met huidige externe gegevensbronnen ophalen:

select * from sys.external_data_sources;

Externe tabellen

Syntaxis:

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',]

voorbeeld

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

In het volgende voorbeeld ziet u hoe u de lijst met externe tabellen ophaalt uit de huidige database:

select * from sys.external_tables;

Opmerkingen

Elastische query breidt de bestaande syntaxis van de externe tabel uit om externe tabellen te definiëren die gebruikmaken van externe gegevensbronnen van het type RDBMS. Een externe tabeldefinitie voor verticale partitionering heeft betrekking op de volgende aspecten:

  • Schema: De externe tabel DDL definieert een schema dat uw query's kunnen gebruiken. Het schema in de definitie van de externe tabel moet overeenkomen met het schema van de tabellen in de externe database waarin de werkelijke gegevens worden opgeslagen.
  • Externe databaseverwijzing: DDL van de externe tabel verwijst naar een externe gegevensbron. De externe gegevensbron geeft de servernaam en databasenaam op van de externe database waarin de werkelijke tabelgegevens worden opgeslagen.

Het gebruik van een externe gegevensbron, zoals beschreven in de vorige sectie, is de syntaxis voor het maken van externe tabellen als volgt:

De component DATA_SOURCE definieert de externe gegevensbron (de externe database in verticale partitionering) die wordt gebruikt voor de externe tabel.

Met de SCHEMA_NAME- en OBJECT_NAME-componenten kunt u de definitie van de externe tabel toewijzen aan een tabel in een ander schema op de externe database of aan een tabel met respectievelijk een andere naam. Deze toewijzing is handig als u een externe tabel wilt definiëren voor een catalogusweergave of DMV in uw externe database, of een andere situatie waarin de naam van de externe tabel al lokaal wordt gebruikt.

Met de volgende DDL-instructie wordt een bestaande externe tabeldefinitie uit de lokale catalogus verwijderd. Dit heeft geen invloed op de externe database.

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

Machtigingen voor CREATE/DROP EXTERNAL TABLE: ALTER ANY EXTERNAL DATA SOURCE permissions are needed for external table DDL, which is also needed to refer to the onderliggende data source.

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. Beheer de toegang tot de externe tabel zorgvuldig om ongewenste uitbreiding van bevoegdheden te voorkomen via de referentie van de externe gegevensbron. Reguliere SQL-machtigingen kunnen worden gebruikt om toegang tot een externe tabel toe te kennen of in te trekken, net als bij een gewone tabel.

Voorbeeld: query's uitvoeren op verticaal gepartitioneerde databases

Met de volgende query wordt een drierichtingsdeelname uitgevoerd tussen de twee lokale tabellen voor orders en orderregels en de externe tabel voor klanten. Dit is een voorbeeld van de use-case voor referentiegegevens voor elastische query:

    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

Opgeslagen procedure voor externe T-SQL-uitvoering: sp_execute_remote

Elastische query introduceert ook een opgeslagen procedure die directe toegang biedt tot de externe database. 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 database. 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 moet worden uitgevoerd op de externe database.
  • 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 database. Hierbij wordt de referentie van de externe gegevensbron gebruikt om verbinding te maken met de externe database.

Voorbeeld:

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

Connectiviteit voor hulpprogramma's

U kunt reguliere SQL Server-verbindingsreeksen gebruiken om uw BI- en gegevensintegratiehulpprogramma's te verbinden met databases op de server waarop elastische query is ingeschakeld en externe tabellen zijn gedefinieerd. Zorg ervoor dat SQL Server wordt ondersteund als gegevensbron voor uw hulpprogramma. Raadpleeg vervolgens de elastische querydatabase en de bijbehorende externe tabellen, net zoals elke andere SQL Server-database waarmee u verbinding maakt met uw hulpprogramma.

Aanbevolen procedures

  • Zorg ervoor dat de eindpuntdatabase voor elastische query's toegang heeft gekregen tot de externe database door toegang in te schakelen voor Azure Services in de firewallconfiguratie van Azure SQL Database. Zorg er ook voor dat de referentie die is opgegeven in de definitie van de externe gegevensbron, zich kan aanmelden bij de externe database en over de machtigingen beschikt om toegang te krijgen tot de externe tabel.
  • Elastische query werkt het beste voor query's waarbij de meeste berekeningen kunnen worden uitgevoerd op de externe databases. Doorgaans krijgt u de beste queryprestaties met selectieve filterpredicaten die kunnen worden geëvalueerd op de externe databases of joins die volledig op de externe database kunnen worden uitgevoerd. Andere querypatronen moeten mogelijk grote hoeveelheden gegevens uit de externe database laden en kunnen slecht presteren.

Volgende stappen