Eseguire query in database cloud con schemi diversi (anteprima)Query across cloud databases with different schemas (preview)

Eseguire una query tra tabelle in vari database

I database con partizionamento verticale usano set di tabelle diversi su database diversi.Vertically-partitioned databases use different sets of tables on different databases. Lo schema risulta quindi diverso nei diversi database.That means that the schema is different on different databases. Ad esempio, tutte le tabelle per l'inventario si trovano in un database, mentre le tabelle correlate alla contabilità si trovano in un altro database.For instance, all tables for inventory are on one database while all accounting-related tables are on a second database.

PrerequisitiPrerequisites

  • L'utente deve disporre dell'autorizzazione ALTER ANY origine dei dati esterni.The user must possess ALTER ANY EXTERNAL DATA SOURCE permission. Questa autorizzazione è inclusa nell'autorizzazione ALTER DATABASE.This permission is included with the ALTER DATABASE permission.
  • Per il riferimento all'origine dati sottostante sono necessarie autorizzazioni ALTER ANY EXTERNAL DATA SOURCE.ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

PanoramicaOverview

Nota

A differenza del partizionamento orizzontale, queste istruzioni DDL non dipendono dalla definizione di un livello dati con una mappa partizioni tramite la libreria client del database elastico.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. CREATE MASTER KEYCREATE MASTER KEY
  2. CREATE DATABASE SCOPED CREDENTIALCREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCECREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLECREATE EXTERNAL TABLE

Creare la chiave master e le credenziali con ambito databaseCreate database scoped master key and credentials

Le credenziali vengono usate dalla query elastica per connettersi ai database remoti.The credential is used by the elastic query to connect to your remote databases.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE DATABASE SCOPED CREDENTIAL <credential_name>  WITH IDENTITY = '<username>',  
SECRET = '<password>'
[;]

Nota

Assicurarsi che <username> non includa alcun suffisso "@servername".Ensure that the <username> does not include any "@servername" suffix.

Creare origini dati esterneCreate external data sources

Sintassi:Syntax:

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

Importante

Il parametro TYPE deve essere impostato su RDBMS.The TYPE parameter must be set to RDBMS.

EsempioExample

Nell'esempio seguente viene illustrato l'utilizzo dell'istruzione CREATE per origini dati esterne.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 
); 

Per recuperare l'elenco di origini dati esterne correnti:To retrieve the list of current external data sources:

select * from sys.external_data_sources; 

Tabelle esterneExternal Tables

Sintassi:Syntax:

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

EsempioExample

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, 
    [country] nvarchar(50) NOT NULL, 
) 
WITH 
( 
       DATA_SOURCE = RemoteReferenceData 
); 

Nell'esempio seguente viene illustrato come recuperare l'elenco di tabelle esterni dal database corrente:The following example shows how to retrieve the list of external tables from the current database:

select * from sys.external_tables; 

OsservazioniRemarks

La query elastica estende la sintassi esistente della tabella esterna per definire le tabelle esterne che usano origini dati esterne di tipo RDBMS.Elastic query extends the existing external table syntax to define external tables that use external data sources of type RDBMS. Una definizione di tabella esterna per il partizionamento verticale comprende gli aspetti seguenti:An external table definition for vertical partitioning covers the following aspects:

  • Schema: il DDL della tabella esterna definisce uno schema che può essere usato dalle query.Schema: The external table DDL defines a schema that your queries can use. Lo schema fornito nella definizione della tabella esterna deve corrispondere allo schema delle tabelle nel database remoto in cui sono archiviati i dati effettivi.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.
  • Riferimento al database remoto: il DDL della tabella esterna fa riferimento a un'origine dati esterna.Remote database reference: The external table DDL refers to an external data source. L'origine dati esterna specifica il nome del server logico e il nome del database remoto in cui sono archiviati i dati effettivi della tabella.The external data source specifies the logical server name and database name of the remote database where the actual table data is stored.

Se si usa un'origine dati esterna, come illustrato nella sezione precedente, la sintassi per la creazione di tabelle esterne è la seguente:Using an external data source as outlined in the previous section, the syntax to create external tables is as follows:

La clausola DATA_SOURCE definisce l'origine dati esterna, ovvero il database remoto nel caso del partizionamento verticale, usata per la tabella esterna.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.

Le clausole SCHEMA_NAME e OBJECT_NAME consentono di mappare la definizione della tabella esterna a una tabella in uno schema diverso sul database remoto o a una tabella con un nome diverso, rispettivamente.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. Ciò risulta utile se si vuole definire una tabella esterna per una vista del catalogo o una DMV nel database remoto o in qualsiasi altra situazione in cui il nome della tabella remota è già usato a livello locale.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.

L'istruzione DDL seguente elimina una definizione di tabella esterna esistente da un catalogo locale.The following DDL statement drops an existing external table definition from the local catalog. Non ha alcun impatto sul database remoto.It does not impact the remote database.

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

Autorizzazioni per CREATE/DROP EXTERNAL TABLE: le autorizzazioni di tipo ALTER ANY EXTERNAL DATA SOURCE sono necessarie per il DDL di tabelle esterne, che è richiesto anche per fare riferimento all'origine dati sottostante.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.

Considerazioni relative alla sicurezzaSecurity considerations

Gli utenti con accesso alla tabella esterna ottengono automaticamente l'accesso alle tabelle remote sottostanti con le credenziali specificate nella definizione dell'origine dati esterna.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. È necessario gestire con attenzione l'accesso alla tabella esterna, in modo da evitare l'elevazione indesiderata dei privilegi tramite le credenziali dell'origine dati esterna.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. È possibile usare le normali autorizzazioni SQL per CONCEDERE o REVOCARE l'accesso a una tabella esterna, come se fosse una tabella normale.Regular SQL permissions can be used to GRANT or REVOKE access to an external table just as though it were a regular table.

Esempio: esecuzione di query su database partizionati verticalmenteExample: querying vertically partitioned databases

La query seguente esegue un join a tre vie tra le due tabelle locali per gli ordini e le righe di ordine e la tabella remota per i clienti.The following query performs a three-way join between the two local tables for orders and order lines and the remote table for customers. Ecco un esempio di caso di utilizzo dei dati di riferimento per la query elastica: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

Stored procedure per l'esecuzione remota di T-SQL: sp_execute_remoteStored procedure for remote T-SQL execution: sp_execute_remote

La query elastica introduce anche una stored procedure che fornisce l'accesso diretto alle partizioni.Elastic query also introduces a stored procedure that provides direct access to the shards. La stored procedure è denominata sp_execute _remote e può essere usata per eseguire stored procedure remote o codice T-SQL sui database remoti.The stored procedure is called sp_execute _remote and can be used to execute remote stored procedures or T-SQL code on the remote databases. È necessario specificare i seguenti parametri:It takes the following parameters:

  • Nome dell'origine dati (nvarchar): il nome dell'origine dati esterna di tipo RDBMS.Data source name (nvarchar): The name of the external data source of type RDBMS.
  • Query (nvarchar): la query T-SQL da eseguire in ogni partizione.Query (nvarchar): The T-SQL query to be executed on each shard.
  • Dichiarazione del parametro (nvarchar) - Facoltativo: stringa con definizioni del tipo di dati per i parametri usati nel parametro della query, ad esempio sp_executesql.Parameter declaration (nvarchar) - optional: String with data type definitions for the parameters used in the Query parameter (like sp_executesql).
  • Elenco di valori dei parametri (facoltativo): elenco delimitato da virgole di valori dei parametri, ad esempio sp_executesql.Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

La stored procedure sp_execute_remote usa l'origine dati esterna specificata nei parametri di chiamata per eseguire l'istruzione T-SQL inclusa nei database remoti.The sp_execute_remote uses the external data source provided in the invocation parameters to execute the given T-SQL statement on the remote databases. Usa le credenziali dell'origine dati esterna per connettersi al database di gestione shardmap e ai database remoti.It uses the credential of the external data source to connect to the shardmap manager database and the remote databases.

Esempio:Example:

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

Connettività per gli strumentiConnectivity for tools

È possibile usare le normali stringhe di connessione di SQL Server per connettere gli strumenti di Business Intelligence e di integrazione dei dati ai database nel server del database SQL per cui sono abilitate le query elastiche e sono state definite tabelle esterne.You can use regular SQL Server connection strings to connect your BI and data integration tools to databases on the SQL DB server that has elastic query enabled and external tables defined. Assicurarsi che SQL Server sia supportato come origine dati per lo strumento.Make sure that SQL Server is supported as a data source for your tool. Fare quindi riferimento al database elastico sottoposto a query e alle relative tabelle esterne come se fosse un qualsiasi altro database di SQL Server a cui ci si può connettere con lo strumento.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.

Procedure consigliateBest practices

  • Assicurarsi che il database di endpoint della query elastica disponga di accesso al database remoto mediante l'abilitazione dell'accesso per i servizi di Azure nella rispettiva configurazione del firewall del database SQL.Ensure that the elastic query endpoint database has been given access to the remote database by enabling access for Azure Services in its SQL DB firewall configuration. Assicurarsi anche che le credenziali fornite nella definizione dell'origine dati esterna possano accedere correttamente al database remoto e abbiano le autorizzazioni necessarie per accedere alla tabella remota.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.
  • La query elastica funziona in modo ottimale per le query in cui la maggior parte dei calcoli può essere eseguita sui database remoti.Elastic query works best for queries where most of the computation can be done on the remote databases. In genere si ottengono le prestazioni migliori per le query tramite i predicati di filtro selettivo, che possono essere valutati sui database remoti, o mediante join che possono essere eseguiti completamente nel database remoto.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. Altri modelli di query potrebbero richiedere il caricamento di quantità elevate di dati dal database remoto e potrebbero offrire prestazioni ridotte.Other query patterns may need to load large amounts of data from the remote database and may perform poorly.

Passaggi successiviNext steps