Query's uitvoeren in Cloud databases met verschillende schema's (preview-versie)Query across cloud databases with different schemas (preview)

VAN TOEPASSING OP: Azure SQL Database

Query's uitvoeren voor meerdere tabellen in verschillende data bases

Verticaal gepartitioneerde data bases gebruiken verschillende sets tabellen in verschillende data bases.Vertically-partitioned databases use different sets of tables on different databases. Dit betekent dat het schema afwijkt in verschillende databases.That means that the schema is different on different 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.For instance, all tables for inventory are on one database while all accounting-related tables are on a second database.

VereistenPrerequisites

  • De gebruiker moet beschikken over de machtiging voor het wijzigen van externe gegevens bronnen.The user must possess ALTER ANY EXTERNAL DATA SOURCE permission. Deze machtiging is opgenomen in de machtiging ALTER DATABASE.This permission is included with the ALTER DATABASE permission.
  • Machtigingen voor ALTER ANY EXTERNAL DATA SOURCE zijn nodig om te verwijzen naar de onderliggende gegevensbron.ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

OverzichtOverview

Notitie

In tegens telling tot horizontale partitionering zijn deze DDL-instructies niet afhankelijk van het definiëren van een gegevenslaag met een Shard-toewijzing via de client bibliotheek voor Elastic data base.Unlike with horizontal partitioning, these DDL statements do not depend on defining a data tier with a shard map through the elastic database client library.

  1. HOOFD SLEUTEL MAKENCREATE MASTER KEY
  2. DATA BASE-SCOPED REFERENTIE MAKENCREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCECREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLECREATE EXTERNAL TABLE

Data base-scoped Master sleutel en referenties makenCreate database scoped master key and credentials

De referentie wordt gebruikt door de elastische query om verbinding te maken met uw externe data bases.The credential is used by the elastic query to connect to your remote 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 ' @ Server naam ' niet wordt vermeld.Ensure that the <username> does not include any "@servername" suffix.

Externe gegevens bronnen makenCreate external data sources

Syntaxis:Syntax:

<External_Data_Source>:: = externe gegevens bron maken <data_source_name> met (TYPE = RDBMS, LOCATION = ' <fully_qualified_server_name> ', DATABASE_NAME = ' <remote_database_name> ',<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>) [;]CREDENTIAL = <credential_name> ) [;]

Belangrijk

De TYPE parameter moet worden ingesteld op RDBMS.The TYPE parameter must be set to RDBMS.

VoorbeeldExample

In het volgende voor beeld ziet u het gebruik van de instructie CREATE voor externe gegevens bronnen.The following example illustrates the use of the CREATE statement for external data sources.

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

De lijst met huidige externe gegevens bronnen ophalen:To retrieve the list of current external data sources:

select * from sys.external_data_sources;

Externe tabellenExternal Tables

Syntaxis:Syntax:

Maak een externe tabel [database_name.CREATE EXTERNAL TABLE [ database_name . [schema_name].[ schema_name ] . | schema_name.| schema_name . ] table_name] table_name
({<column_definition>} [,... n]) {met (<rdbms_external_table_options>)}) [;]( { <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 ',]<rdbms_external_table_options> ::= DATA_SOURCE = <External_Data_Source>, [ SCHEMA_NAME = N'nonescaped_schema_name',] [ OBJECT_NAME = N'nonescaped_object_name',]

VoorbeeldExample

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,
      DATA_SOURCE = RemoteReferenceData
   );

In het volgende voor beeld ziet u hoe u de lijst met externe tabellen kunt ophalen uit de huidige Data Base:The following example shows how to retrieve the list of external tables from the current database:

select * from sys.external_tables;

OpmerkingenRemarks

Met elastische query's wordt de bestaande syntaxis van de externe tabel uitgebreid met het definiëren van externe tabellen die gebruikmaken van externe gegevens bronnen van het type RDBMS.Elastic query extends the existing external table syntax to define external tables that use external data sources of type RDBMS. Een externe tabel definitie voor verticale partitionering heeft betrekking op de volgende aspecten:An external table definition for vertical partitioning covers the following aspects:

  • Schema: de externe tabel DDL definieert een schema dat door uw query's kan worden gebruikt.Schema: The external table DDL defines a schema that your queries can use. Het schema dat is opgegeven in de definitie van de externe tabel moet overeenkomen met het schema van de tabellen in de externe data base waarin de daad werkelijke gegevens zijn opgeslagen.The schema provided in your external table definition needs to match the schema of the tables in the remote database where the actual data is stored.
  • Externe database referentie: de externe tabel DDL verwijst naar een externe gegevens bron.Remote database reference: The external table DDL refers to an external data source. De externe gegevens bron geeft de naam van de server en de data base van de externe data base waar de gegevens van de actuele tabel worden opgeslagen.The external data source specifies the server name and database name of the remote database where the actual table data is stored.

Als u een externe gegevens bron gebruikt, zoals beschreven in de vorige sectie, is de syntaxis voor het maken van externe tabellen als volgt:Using an external data source as outlined in the previous section, the syntax to create external tables is as follows:

De DATA_SOURCE-component definieert de externe gegevens bron (dat wil zeggen de externe data base in het geval van verticale partitionering) die wordt gebruikt voor de externe tabel.The DATA_SOURCE clause defines the external data source (i.e. the remote database in case of vertical partitioning) that is used for the external table.

De componenten SCHEMA_NAME en OBJECT_NAME bieden de mogelijkheid om de definitie van de externe tabel toe te wijzen aan een tabel in een ander schema op de externe data base of aan een tabel met een andere naam.The SCHEMA_NAME and OBJECT_NAME clauses provide the ability to map the external table definition to a table in a different schema on the remote database, or to a table with a different name, respectively. Dit is handig als u een externe tabel wilt definiëren in een catalogus weergave of DMV op uw externe data base, of een andere situatie waarin de naam van de externe tabel al lokaal wordt overgenomen.This is useful if you want to define an external table to a catalog view or DMV on your remote database - or any other situation where the remote table name is already taken locally.

De volgende DDL-instructie verwijdert een bestaande definitie van een externe tabel uit de lokale catalogus.The following DDL statement drops an existing external table definition from the local catalog. Dit heeft geen invloed op de externe data base.It does not impact the remote database.

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

Machtigingen voor maken/verwijderen van externe tabel: machtigingen voor externe gegevens bronnen wijzigen is vereist voor de DDL van externe tabellen die ook nodig is om te verwijzen naar de onderliggende gegevens bron.Permissions for CREATE/DROP EXTERNAL TABLE: ALTER ANY EXTERNAL DATA SOURCE permissions are needed for external table DDL which is also needed to refer to the underlying data source.

BeveiligingsoverwegingenSecurity considerations

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 gegevens bron.Users with access to the external table automatically gain access to the underlying remote tables under the credential given in the external data source definition. U moet de toegang tot de externe tabel zorgvuldig beheren om te voor komen dat er ongewenste bevoegdheden worden uitgebreid via de referentie van de externe gegevens bron.You should carefully manage access to the external table in order to avoid undesired elevation of privileges through the credential of the external data source. Reguliere SQL-machtigingen kunnen worden gebruikt om toegang tot een externe tabel toe te kennen of in te trekken, net alsof het een normale tabel is.Regular SQL permissions can be used to GRANT or REVOKE access to an external table just as though it were a regular table.

Voor beeld: een query uitvoeren op verticaal gepartitioneerde data basesExample: querying vertically partitioned databases

Met de volgende query wordt een drie richtings relatie uitgevoerd tussen de twee lokale tabellen voor orders en order regels en de externe tabel voor klanten.The following query performs a three-way join between the two local tables for orders and order lines and the remote table for customers. Dit is een voor beeld van het gebruik van referentie gegevens voor elastische query's:This is an example of the reference data use case for elastic 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 het uitvoeren van externe T-SQL-uitvoering: SP _ execute_remoteStored procedure for remote T-SQL execution: sp_execute_remote

Met elastische query's wordt ook een opgeslagen procedure geïntroduceerd die directe toegang biedt tot de externe data base.Elastic query also introduces a stored procedure that provides direct access to the remote database. De opgeslagen procedure heet _ _ extern uitvoeren en kan worden gebruikt om externe opgeslagen procedures of T-SQL-code uit te voeren op de externe data base.The stored procedure is called sp_execute _remote and can be used to execute remote stored procedures or T-SQL code on the remote database. Hierbij worden de volgende para meters gebruikt:It takes the following parameters:

  • Naam van de gegevens bron (nvarchar): de naam van de externe gegevens bron van het type RDBMS.Data source name (nvarchar): The name of the external data source of type RDBMS.
  • Query (nvarchar): de T-SQL-query die moet worden uitgevoerd op de externe data base.Query (nvarchar): The T-SQL query to be executed on the remote database.
  • Parameter declaratie (nvarchar)-optioneel: string met definities van gegevens typen voor de para meters die worden gebruikt in de query parameter (zoals sp_executesql).Parameter declaration (nvarchar) - optional: String with data type definitions for the parameters used in the Query parameter (like sp_executesql).
  • Lijst met parameter waarden-optioneel: door Komma's gescheiden lijst met parameter waarden (zoals sp_executesql).Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

De extern _ uitvoeren op _ afstand maakt gebruik van de externe gegevens bron die is opgegeven in de aanroep parameters om de opgegeven T-SQL-instructie uit te voeren op de externe data base.The sp_execute_remote uses the external data source provided in the invocation parameters to execute the given T-SQL statement on the remote database. De referentie van de externe gegevens bron wordt gebruikt om verbinding te maken met de externe data base.It uses the credential of the external data source to connect to the remote database.

Voorbeeld:Example:

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

Connectiviteit voor hulpprogramma'sConnectivity for tools

U kunt gewone SQL Server verbindings reeksen gebruiken om uw BI-en gegevens integratie hulpprogramma's te koppelen aan data bases op de server waarop elastische query's zijn ingeschakeld en externe tabellen zijn gedefinieerd.You can use regular SQL Server connection strings to connect your BI and data integration tools to databases on the server that has elastic query enabled and external tables defined. Zorg ervoor dat SQL Server wordt ondersteund als gegevensbron voor uw hulpprogramma.Make sure that SQL Server is supported as a data source for your tool. Raadpleeg vervolgens de elastische query database en de externe tabellen, net als andere SQL Server Data Base waarmee u verbinding wilt maken met uw hulp programma.Then refer to the elastic query database and its external tables just like any other SQL Server database that you would connect to with your tool.

Aanbevolen proceduresBest practices

  • Zorg ervoor dat de data base van het Elastic query-eind punt toegang heeft gekregen tot de externe data base door toegang in te scha kelen voor Azure-Services in de Azure SQL Database firewall configuratie.Ensure that the elastic query endpoint database has been given access to the remote database by enabling access for Azure Services in its Azure SQL Database firewall configuration. Zorg er ook voor dat de referenties die in de definitie van de externe gegevens bron zijn opgenomen, kunnen worden aangemeld bij de externe data base en de machtigingen hebben voor toegang tot de externe tabel.Also ensure that the credential provided in the external data source definition can successfully log into the remote database and has the permissions to access the remote table.
  • Elastische query's werken het beste voor query's waarbij de meeste berekeningen kunnen worden uitgevoerd voor de externe data bases.Elastic query works best for queries where most of the computation can be done on the remote databases. Normaal gesp roken krijgt u de beste query prestaties met selectieve filter predikaten die kunnen worden geëvalueerd op de externe data bases of samen voegingen die volledig kunnen worden uitgevoerd op de externe data base.You typically get the best query performance with selective filter predicates that can be evaluated on the remote databases or joins that can be performed completely on the remote database. Andere query patronen moeten mogelijk grote hoeveel heden gegevens van de externe data base laden en kunnen goed worden uitgevoerd.Other query patterns may need to load large amounts of data from the remote database and may perform poorly.

Volgende stappenNext steps