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

Van toepassing op: Azure SQL-database

Query's uitvoeren op tabellen in verschillende 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 shard-toewijzing via de clientbibliotheek van de elastische database.

  1. HOOFDSLEUTEL MAKEN
  2. DATABASESPECIFIEKE REFERENTIES MAKEN
  3. CREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLE

Hoofdsleutel en referenties met 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 uit de huidige database ophaalt:

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 omvat de volgende aspecten:

  • Schema: De externe tabel DDL definieert een schema dat uw query's kunnen gebruiken. Het schema dat is opgegeven 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: De externe tabel DDL verwijst naar een externe gegevensbron. De externe gegevensbron geeft de servernaam en databasenaam op van de externe database waar 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 DATA_SOURCE-component 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 in 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 op uw externe database, of een andere situatie waarin de naam van de externe tabel al lokaal wordt genomen.

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 zorgvuldig de toegang tot de externe tabel 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 te verlenen of in te trekken, net zoals 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 gebruikt 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 HULPPROGRAMMA's voor BI- en gegevensintegratie te verbinden met databases op de server waarop elastische query's zijn 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 als 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 Azure SQL databasefirewallconfiguratie. 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 de machtigingen heeft voor toegang 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 kunnen worden uitgevoerd op de externe database. Andere querypatronen moeten mogelijk grote hoeveelheden gegevens uit de externe database laden en kunnen slecht presteren.

Volgende stappen