Creazione di report tra database cloud con scalabilità orizzontale (anteprima)Reporting across scaled-out cloud databases (preview)

Eseguire una query tra partizioni

I database partizionati distribuiscono righe su un livello di dati a scalabilità orizzontale.Sharded databases distribute rows across a scaled out data tier. Lo schema è identico in tutti i database partecipanti, ed è denominato anche partizionamento orizzontale.The schema is identical on all participating databases, also known as horizontal partitioning. Con una query elastica è possibile creare report che si estendono a tutti i database di un database partizionato.Using an elastic query, you can create reports that span all databases in a sharded database.

Per l'avvio rapido, vedere Creazione di report tra database cloud con scalabilità orizzontale.For a quick start, see Reporting across scaled-out cloud databases.

Per i database non partizionati, vedere Eseguire query in database cloud con schemi diversi.For non-sharded databases, see Query across cloud databases with different schemas.

PrerequisitiPrerequisites

PanoramicaOverview

Queste istruzioni creano la rappresentazione dei metadati del livello dati con partizionamento orizzontale nel database elastico sottoposto a query.These statements create the metadata representation of your sharded data tier in the elastic query database.

  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

1.1 Creare la chiave master e le credenziali con ambito database1.1 Create 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 il "<nome utente>" non includa alcun suffisso "@servername".Make sure that the "<username>" does not include any "@servername" suffix.

1.2 Creare origini dati esterne1.2 Create external data sources

Sintassi:Syntax:

<External_Data_Source> ::=    
CREATE EXTERNAL DATA SOURCE <data_source_name> WITH                                              
        (TYPE = SHARD_MAP_MANAGER,
                   LOCATION = '<fully_qualified_server_name>',
        DATABASE_NAME = ‘<shardmap_database_name>',
        CREDENTIAL = <credential_name>, 
        SHARD_MAP_NAME = ‘<shardmapname>’ 
               ) [;] 

EsempioExample

CREATE EXTERNAL DATA SOURCE MyExtSrc 
WITH 
( 
    TYPE=SHARD_MAP_MANAGER,
    LOCATION='myserver.database.windows.net', 
    DATABASE_NAME='ShardMapDatabase', 
    CREDENTIAL= SMMUser, 
    SHARD_MAP_NAME='ShardMap' 
);

Recuperare l'elenco di origini dati esterne correnti:Retrieve the list of current external data sources:

select * from sys.external_data_sources; 

L'origine dati esterna fa riferimento alla mappa partizioni.The external data source references your shard map. Una query elastica usa quindi l'origine dati esterna e la mappa partizioni sottostante per enumerare i database che partecipano al livello dati.An elastic query then uses the external data source and the underlying shard map to enumerate the databases that participate in the data tier. Le stesse credenziali vengono usate per leggere la mappa partizioni e per accedere ai dati nelle partizioni durante l'elaborazione di una query elastica.The same credentials are used to read the shard map and to access the data on the shards during the processing of an elastic query.

1.3 Creare tabelle esterne1.3 Create external tables

Sintassi:Syntax:

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name  
    ( { <column_definition> } [ ,...n ])     
    { WITH ( <sharded_external_table_options> ) }
) [;]  

<sharded_external_table_options> ::= 
  DATA_SOURCE = <External_Data_Source>,       
  [ SCHEMA_NAME = N'nonescaped_schema_name',] 
  [ OBJECT_NAME = N'nonescaped_object_name',] 
  DISTRIBUTION = SHARDED(<sharding_column_name>) | REPLICATED |ROUND_ROBIN

EsempioExample

CREATE EXTERNAL TABLE [dbo].[order_line]( 
     [ol_o_id] int NOT NULL, 
     [ol_d_id] tinyint NOT NULL,
     [ol_w_id] int NOT NULL, 
     [ol_number] tinyint NOT NULL, 
     [ol_i_id] int NOT NULL, 
     [ol_delivery_d] datetime NOT NULL, 
     [ol_amount] smallmoney NOT NULL, 
     [ol_supply_w_id] int NOT NULL, 
     [ol_quantity] smallint NOT NULL, 
     [ol_dist_info] char(24) NOT NULL 
) 

WITH 
( 
    DATA_SOURCE = MyExtSrc, 
     SCHEMA_NAME = 'orders', 
     OBJECT_NAME = 'order_details', 
    DISTRIBUTION=SHARDED(ol_w_id)
); 

Recuperare l'elenco di tabelle esterne dal database corrente:Retrieve the list of external tables from the current database:

SELECT * from sys.external_tables; 

Per eliminare le tabelle esterne:To drop external tables:

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

OsservazioniRemarks

La clausola _DATA_SOURCE definisce l'origine dati esterna (una mappa partizioni) usata per la tabella esterna.The DATA_SOURCE clause defines the external data source (a shard map) that is used for the external table.

Le clausole SCHEMA_NAME e OBJECT_NAME eseguono il mapping della definizione della tabella esterna a una tabella in uno schema diverso.The SCHEMA_NAME and OBJECT_NAME clauses map the external table definition to a table in a different schema. Se queste clausole vengono omesse, si presupporrà che lo schema dell'oggetto remoto sia "dbo" e che il relativo nome sia identico al nome della tabella esterna in fase di definizione.If omitted, the schema of the remote object is assumed to be “dbo” and its name is assumed to be identical to the external table name being defined. Questo è utile se il nome della tabella remota è già in uso nel database in cui si vuole creare la tabella esterna.This is useful if the name of your remote table is already taken in the database where you want to create the external table. Ad esempio, si vuole definire una tabella esterna per ottenere una visualizzazione aggregata delle viste del catalogo o delle viste a gestione dinamica (DMV) nel livello dati con scalabilità orizzontale.For example, you want to define an external table to get an aggregate view of catalog views or DMVs on your scaled out data tier. Poiché le viste del catalogo e le DMV esistono già localmente, non sarà possibile usare i rispettivi nomi per la definizione della tabella esterna.Since catalog views and DMVs already exist locally, you cannot use their names for the external table definition. Usare invece un nome diverso e usare il nome della vista del catalogo o della DMV nelle clausole SCHEMA_NAME e/o OBJECT_NAME.Instead, use a different name and use the catalog view’s or the DMV’s name in the SCHEMA_NAME and/or OBJECT_NAME clauses. Vedere l'esempio seguente.(See the example below.)

La clausola DISTRIBUTION specifica la distribuzione dei dati usata per questa tabella.The DISTRIBUTION clause specifies the data distribution used for this table. Query Processor utilizza le informazioni fornite nella clausola DISTRIBUTION per generare i piani di query più efficienti.The query processor utilizes the information provided in the DISTRIBUTION clause to build the most efficient query plans.

  1. SHARDED significa che i dati sono partizionati orizzontalmente tra i database.SHARDED means data is horizontally partitioned across the databases. La chiave di partizionamento per la distribuzione dei dati è il parametro <sharding_column_name>.The partitioning key for the data distribution is the <sharding_column_name> parameter.
  2. REPLICATED indica che in ogni database sono presenti copie identiche della tabella.REPLICATED means that identical copies of the table are present on each database. Sarà quindi necessario assicurarsi che le repliche siano identiche in tutti i database.It is your responsibility to ensure that the replicas are identical across the databases.
  3. ROUND_ROBIN significa che la tabella è partizionata orizzontalmente con un metodo di distribuzione dipendente dall'applicazione.ROUND_ROBIN means that the table is horizontally partitioned using an application-dependent distribution method.

Riferimento al livello dati: il DDL della tabella esterna fa riferimento a un'origine dati esterna.Data tier reference: The external table DDL refers to an external data source. L'origine dati esterna specifica una mappa partizioni che fornisce alla tabella esterna le informazioni necessarie per individuare tutti i database nel livello dati.The external data source specifies a shard map which provides the external table with the information necessary to locate all the databases in your data tier.

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. Evitare l'elevazione dei privilegi indesiderata mediante le credenziali dell'origine dati esterna.Avoid undesired elevation of privileges through the credential of the external data source. Usare GRANT o REVOKE per la tabella esterna, come se fosse una tabella comune.Use GRANT or REVOKE for an external table just as though it were a regular table.

Dopo aver definito l'origine dati esterna e le tabelle esterne, è ora possibile usare la sintassi T-SQL completa sulle tabelle esterne.Once you have defined your external data source and your external tables, you can now use full T-SQL over your external tables.

Esempio: esecuzione di query su database partizionati orizzontalmenteExample: querying horizontal partitioned databases

La query seguente esegue un join a tre vie tra magazzini, ordini e righe di ordine e usa diverse funzioni di aggregazione e un filtro selettivo.The following query performs a three-way join between warehouses, orders and order lines and uses several aggregates and a selective filter. Presuppone che (1) venga usato il partizionamento orizzontale, che (2) i magazzini, gli ordini e le righe di ordine siano partizionati orizzontalmente in base alla colonna warehouse id e che la query elastica possa condividere i join sulle partizioni ed elaborare la parte più onerosa della query sulle partizioni in parallelo.It assumes (1) horizontal partitioning (sharding) and (2) that warehouses, orders and order lines are sharded by the warehouse id column, and that the elastic query can co-locate the joins on the shards and process the expensive part of the query on the shards in parallel.

select  
     w_id as warehouse,
     o_c_id as customer,
     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 warehouse 
join orders 
on w_id = o_w_id
join order_line 
on o_id = ol_o_id and o_w_id = ol_w_id 
where w_id > 100 and w_id < 200 
group by w_id, o_c_id 

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

Usare le normali stringhe di connessione di SQL Server per connettere l'applicazione, gli strumenti di Business Intelligence e di integrazione dei dati al database con le definizioni delle tabelle esterne.Use regular SQL Server connection strings to connect your application, your BI and data integration tools to the database with your external table definitions. 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 in modo analogo a qualsiasi altro database di SQL Server connesso allo strumento e usare le tabelle esterne dallo strumento o dall'applicazione come se fossero tabelle locali.Then reference the elastic query database like any other SQL Server database connected to the tool, and use external tables from your tool or application as if they were local tables.

Procedure consigliateBest practices

  • Assicurarsi che il database di endpoint della query elastica abbia l'accesso al database di mappe partizioni e a tutte le partizioni attraverso i firewall del database SQL.Ensure that the elastic query endpoint database has been given access to the shardmap database and all shards through the SQL DB firewalls.
  • Convalidare o imporre la distribuzione di dati definita dalla tabella esterna.Validate or enforce the data distribution defined by the external table. Se la distribuzione di dati effettivo è diversa dalla distribuzione specificata nella definizione di tabella, le query possono restituire risultati imprevisti.If your actual data distribution is different from the distribution specified in your table definition, your queries may yield unexpected results.
  • La query elastica non esegue attualmente l'eliminazione di partizioni quando i predicati sulla chiave di partizionamento consentono l'esclusione sicura di alcune partizioni dall'elaborazione.Elastic query currently does not perform shard elimination when predicates over the sharding key would allow it to safely exclude certain shards from processing.
  • La query elastica funziona in modo ottimale per le query in cui la maggior parte dei calcoli può essere eseguita sulle partizioni.Elastic query works best for queries where most of the computation can be done on the shards. In genere si ottiene le migliori prestazioni di query con predicati del filtro selettivo che può essere valutata in join le partizioni tramite le chiavi di partizionamento che possono essere eseguite in modo su tutte le partizioni allineate alle partizioni.You typically get the best query performance with selective filter predicates that can be evaluated on the shards or joins over the partitioning keys that can be performed in a partition-aligned way on all shards. Altri modelli di query potrebbero richiedere il caricamento di quantità elevate di dati dalle partizioni al nodo head e potrebbero offrire prestazioni ridotte.Other query patterns may need to load large amounts of data from the shards to the head node and may perform poorly

Passaggi successiviNext steps