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

Van toepassing op:Azure SQL Database

Verticaal gepartitioneerde databases maken gebruik van verschillende sets tabellen in verschillende databases. Dit betekent dat het schema verschilt van verschillende databases. Alle tabellen voor inventaris bevinden zich bijvoorbeeld in één database, terwijl alle tabellen met betrekking tot boekhouding zich in een tweede database bevinden.

Vereiste voorwaarden

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

Aan de slag met verticale partitionering

1. HOOFDSLEUTEL MAKEN
2. DATABASESPECIFIEKE REFERENTIES MAKEN
3. MAAK EXTERNE GEGEVENSBRON AAN
4. CREËER EXTERNE TABEL

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

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

Voorbeeld

In het volgende voorbeeld wordt het gebruik van de CREATE-instructie voor externe gegevensbronnen geïllustreerd.

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: De 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.

De syntaxis om externe tabellen te maken, met gebruik van een externe gegevensbron zoals beschreven in de vorige sectie, is als volgt:

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

Met SCHEMA_NAME de 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 machtigingen zijn nodig voor externe tabel DDL, wat ook nodig is om naar de onderliggende gegevensbron te verwijzen.

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 gebruikssituatie voor referentiegegevens voor een 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. sp_execute_remote gebruikt de volgende parameters:

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

Hierbij sp_execute_remote wordt gebruikgemaakt 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 een 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.

Beste praktijken

• 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 op de externe database kunnen worden geëvalueerd, of met 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.

Verwante inhoud

• overzicht van elastische query's in Azure SQL Database (preview)
• Preview-beperkingen
• Aan de slag met query's voor meerdere databases (verticale partitionering) (preview)
• rapport over uitgeschaalde clouddatabases (preview)
• rapportage over geschaalde clouddatabases (voorbeeldweergave)
• sp_execute_remote