Fråga över moln databaser med olika scheman (förhands granskning)Query across cloud databases with different schemas (preview)

gäller för: Azure SQL Database

Fråga över tabeller i olika databaser

Lodrätt partitionerade databaser använder olika uppsättningar tabeller i olika databaser.Vertically-partitioned databases use different sets of tables on different databases. Det innebär att schemat är olika i olika databaser.That means that the schema is different on different databases. Till exempel är alla tabeller för inventering i en databas medan alla redovisnings tabeller finns på en andra databas.For instance, all tables for inventory are on one database while all accounting-related tables are on a second database.

FörutsättningarPrerequisites

  • Användaren måste ha ÄNDRINGs behörighet för en extern DATA källa.The user must possess ALTER ANY EXTERNAL DATA SOURCE permission. Den här behörigheten ingår i ALTER DATABASE-behörigheten.This permission is included with the ALTER DATABASE permission.
  • ÄNDRA behörigheter för en extern DATA källa krävs för att referera till den underliggande data källan.ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

ÖversiktOverview

Anteckning

Till skillnad från vågrät partitionering, är dessa DDL-instruktioner inte beroende av att definiera en data nivå med en Shard-karta via klient biblioteket för Elastic Database.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. SKAPA HUVUD NYCKELCREATE MASTER KEY
  2. SKAPA DATABASENS BEGRÄNSADE AUTENTISERINGSUPPGIFTERCREATE DATABASE SCOPED CREDENTIAL
  3. SKAPA EXTERN DATA KÄLLACREATE EXTERNAL DATA SOURCE
  4. SKAPA EXTERN TABELLCREATE EXTERNAL TABLE

Skapa databasens begränsade huvud nyckel och autentiseringsuppgifterCreate database scoped master key and credentials

Autentiseringsuppgiften används av den elastiska frågan för att ansluta till dina fjärrdatabaser.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>'
[;]

Anteckning

Se till att <username> inte innehåller något " @ servername" -suffix.Ensure that the <username> does not include any "@servername" suffix.

Skapa externa data källorCreate external data sources

Syntax:Syntax:

<External_Data_Source>:: = skapa extern DATA källa <data_source_name> med (typ = 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> ) [;]

Viktigt

TYP parametern måste anges som RDBMS.The TYPE parameter must be set to RDBMS.

ExempelExample

I följande exempel visas användningen av CREATE-instruktionen för externa data källor.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
      );

Hämta listan över aktuella externa data Källor:To retrieve the list of current external data sources:

select * from sys.external_data_sources;

Externa tabellerExternal Tables

Syntax:Syntax:

SKAPA en extern tabell [database_name.CREATE EXTERNAL TABLE [ database_name . [schema_name].[ schema_name ] . | schema_name.| schema_name . ] table_name] table_name
({<column_definition>} [,... n]) {med (<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',]

ExempelExample

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

I följande exempel visas hur du hämtar listan över externa tabeller från den aktuella databasen:The following example shows how to retrieve the list of external tables from the current database:

select * from sys.external_tables;

KommentarerRemarks

Elastisk fråga utökar den befintliga syntaxen för den externa tabellen för att definiera externa tabeller som använder externa data källor av typen RDBMS.Elastic query extends the existing external table syntax to define external tables that use external data sources of type RDBMS. En extern tabell definition för vertikal partitionering omfattar följande aspekter:An external table definition for vertical partitioning covers the following aspects:

  • Schema: DDL: en för den externa tabellen definierar ett schema som dina frågor kan använda.Schema: The external table DDL defines a schema that your queries can use. Det schema som anges i den externa tabell definitionen måste matcha schemat för tabellerna i fjärrdatabasen där faktiska data lagras.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.
  • Fjärran sluten databas referens: det externa tabell-DDL: en refererar till en extern data källa.Remote database reference: The external table DDL refers to an external data source. Den externa data källan anger Server namnet och databas namnet för fjärrdatabasen där de faktiska tabell data lagras.The external data source specifies the server name and database name of the remote database where the actual table data is stored.

Med hjälp av en extern data källa som beskrivs i föregående avsnitt är syntaxen för att skapa externa tabeller följande:Using an external data source as outlined in the previous section, the syntax to create external tables is as follows:

DATA_SOURCE-satsen definierar den externa data källan (t. ex. fjärrdatabasen vid vertikal partitionering) som används för den externa tabellen.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.

SCHEMA_NAME-och OBJECT_NAME-satserna ger möjlighet att mappa den externa tabell definitionen till en tabell i ett annat schema på fjärrdatabasen eller till en tabell med ett annat namn.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. Detta är användbart om du vill definiera en extern tabell till en katalogvy eller DMV på din fjärrdatabas, eller någon annan situation där fjärrtabellens namn redan används lokalt.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.

Följande DDL-instruktion släpper en befintlig extern tabell definition från den lokala katalogen.The following DDL statement drops an existing external table definition from the local catalog. Den påverkar inte fjärrdatabasen.It does not impact the remote database.

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

Behörigheter för att skapa/släppa extern tabell: ändra behörigheter för externa data källor krävs för extern tabell-DDL, vilket också krävs för att referera till den underliggande data källan.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.

SäkerhetsövervägandenSecurity considerations

Användare med åtkomst till den externa tabellen får automatiskt till gång till de underliggande fjärrtabellerna under de autentiseringsuppgifter som angavs i definitionen för den externa data källan.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. Du bör noggrant hantera åtkomst till den externa tabellen för att undvika oönskad höjning av privilegier genom den externa data källans autentiseringsuppgifter.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. 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.Regular SQL permissions can be used to GRANT or REVOKE access to an external table just as though it were a regular table.

Exempel: fråga lodrätt partitionerade databaserExample: querying vertically partitioned databases

Följande fråga utför en tre vägs koppling mellan de två lokala tabellerna för beställningar och order rader och fjär tabellen för kunder.The following query performs a three-way join between the two local tables for orders and order lines and the remote table for customers. Detta är ett exempel på användnings fall för referens data för elastisk fråga: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

Lagrad procedur för fjärran sluten T-SQL-körning: SP _ execute_remoteStored procedure for remote T-SQL execution: sp_execute_remote

Elastiska frågor introducerar också en lagrad procedur som ger direkt åtkomst till fjärrdatabasen.Elastic query also introduces a stored procedure that provides direct access to the remote database. Den lagrade proceduren heter SP _ execute _ Remote och kan användas för att köra fjärrlagrade procedurer eller T-SQL-kod i fjärrdatabasen.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. Det tar följande parametrar:It takes the following parameters:

  • Data källans namn (nvarchar): namnet på den externa data källan av typen RDBMS.Data source name (nvarchar): The name of the external data source of type RDBMS.
  • Fråga (nvarchar): T-SQL-frågan som ska köras på fjärrdatabasen.Query (nvarchar): The T-SQL query to be executed on the remote database.
  • Parameter deklaration (nvarchar) – valfritt: sträng med data typs definitioner för de parametrar som används i Frågeparametern (till exempel sp_executesql).Parameter declaration (nvarchar) - optional: String with data type definitions for the parameters used in the Query parameter (like sp_executesql).
  • Parameter värde List – valfritt: kommaavgränsad lista över parameter värden (till exempel sp_executesql).Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

Tjänsten SP _ execute _ använder den externa data källan som tillhandahölls i anrops parametrarna för att köra angivet T-SQL-uttryck på fjärrdatabasen.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. Den externa data källans autentiseringsuppgifter används för att ansluta till fjärrdatabasen.It uses the credential of the external data source to connect to the remote database.

Exempel:Example:

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

Anslutning för verktygConnectivity for tools

Du kan använda vanliga SQL Server anslutnings strängar för att ansluta dina BI-och data integrerings verktyg till databaser på den server där elastisk fråga har Aktiver ATS och externa tabeller har definierats.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. Kontrol lera att SQL Server stöds som data källa för ditt verktyg.Make sure that SQL Server is supported as a data source for your tool. Referera sedan till den elastiska fråge databasen och dess externa tabeller precis som andra SQL Server databas som du skulle ansluta till med ditt verktyg.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.

Bästa praxisBest practices

  • Se till att den elastiska frågans slut punkts databas har fått åtkomst till fjärrdatabasen genom att aktivera åtkomst för Azure-tjänster i den Azure SQL Database brand Väggs konfigurationen.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. Se också till att de autentiseringsuppgifter som anges i definitionen för den externa data källan kan logga in i fjärrdatabasen och har behörighet att komma åt fjärrtabellen.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.
  • Elastisk fråga fungerar bäst för frågor där merparten av beräkningen kan utföras på fjärrdatabaserna.Elastic query works best for queries where most of the computation can be done on the remote databases. Du får vanligt vis bästa möjliga frågeresultat med selektiva filter-predikat som kan utvärderas på fjärrdatabaser eller kopplingar som kan utföras helt på fjärrdatabasen.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. Andra fråge mönster kan behöva läsa in stora mängder data från fjärrdatabasen och kan fungera dåligt.Other query patterns may need to load large amounts of data from the remote database and may perform poorly.

Nästa stegNext steps