Wykonywanie zapytań w bazach danych w chmurze z różnymi schematami (wersja zapoznawcza)Query across cloud databases with different schemas (preview)

dotyczy: Azure SQL Database

Zapytanie między tabelami w różnych bazach danych

Bazy danych partycjonowane w pionie korzystają z różnych zestawów tabel w różnych bazach danych.Vertically-partitioned databases use different sets of tables on different databases. Oznacza to, że schemat różni się w różnych bazach danych.That means that the schema is different on different databases. Na przykład wszystkie tabele dla spisu znajdują się w jednej bazie danych, podczas gdy wszystkie tabele związane z ewidencjonowanie aktywności znajdują się w drugiej bazie danych.For instance, all tables for inventory are on one database while all accounting-related tables are on a second database.

Wymagania wstępnePrerequisites

  • Użytkownik musi mieć uprawnienie Zmień każde zewnętrzne źródło danych.The user must possess ALTER ANY EXTERNAL DATA SOURCE permission. To uprawnienie jest dołączone do uprawnienia ALTER DATABASE.This permission is included with the ALTER DATABASE permission.
  • Aby odwołać się do bazowego źródła danych, należy zmienić wszystkie uprawnienia zewnętrznych źródeł danych.ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

OmówienieOverview

Uwaga

W przeciwieństwie do partycjonowania poziomego te instrukcje DDL nie zależą od definiowania warstwy danych za pomocą mapy fragmentu za pomocą biblioteki klienta 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. UTWÓRZ KLUCZ GŁÓWNYCREATE MASTER KEY
  2. UTWÓRZ POŚWIADCZENIA W ZAKRESIE BAZY DANYCHCREATE DATABASE SCOPED CREDENTIAL
  3. UTWÓRZ ZEWNĘTRZNE ŹRÓDŁO DANYCHCREATE EXTERNAL DATA SOURCE
  4. TWORZENIE TABELI ZEWNĘTRZNEJCREATE EXTERNAL TABLE

Utwórz klucz główny i poświadczenia w zakresie bazy danychCreate database scoped master key and credentials

To poświadczenie jest używane przez zapytanie elastyczne do łączenia się ze zdalnymi bazami danych.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>'
[;]

Uwaga

Upewnij się, że nie <username> zawiera żadnego sufiksu " @ servername" .Ensure that the <username> does not include any "@servername" suffix.

Tworzenie zewnętrznych źródeł danychCreate external data sources

Składnia:Syntax:

<External_Data_Source>:: = Utwórz zewnętrzne źródło danych <data_source_name> z (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> ) [;]

Ważne

Parametr typu musi być ustawiony na wartość RDBMS.The TYPE parameter must be set to RDBMS.

PrzykładExample

Poniższy przykład ilustruje użycie instrukcji CREATE dla zewnętrznych źródeł danych.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
      );

Aby pobrać listę bieżących zewnętrznych źródeł danych:To retrieve the list of current external data sources:

select * from sys.external_data_sources;

Tabele zewnętrzneExternal Tables

Składnia:Syntax:

Utwórz tabelę ZEWNĘTRZną [database_name.CREATE EXTERNAL TABLE [ database_name . [schema_name].[ schema_name ] . | schema_name.| schema_name . ] table_name] table_name
({<column_definition>} [,... n]) {WITH (<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',]

PrzykładExample

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

Poniższy przykład pokazuje, jak pobrać listę tabel zewnętrznych z bieżącej bazy danych:The following example shows how to retrieve the list of external tables from the current database:

select * from sys.external_tables;

UwagiRemarks

Zapytanie elastyczne rozszerza istniejącą składnię tabeli zewnętrznej w celu zdefiniowania tabel zewnętrznych, które używają zewnętrznych źródeł danych typu RDBMS.Elastic query extends the existing external table syntax to define external tables that use external data sources of type RDBMS. Definicja tabeli zewnętrznej dla partycjonowania pionowego obejmuje następujące aspekty:An external table definition for vertical partitioning covers the following aspects:

  • Schemat: tabela zewnętrzna DDL definiuje schemat, którego mogą używać zapytania.Schema: The external table DDL defines a schema that your queries can use. Schemat podany w definicji tabeli zewnętrznej musi pasować do schematu tabel w zdalnej bazie danych, w której przechowywane są rzeczywiste dane.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.
  • Odwołanie do zdalnej bazy danych: tablica zewnętrzna DDL odwołuje się do zewnętrznego źródła danych.Remote database reference: The external table DDL refers to an external data source. Zewnętrzne źródło danych określa nazwę serwera i nazwę bazy danych, w której przechowywane są rzeczywiste dane tabeli.The external data source specifies the server name and database name of the remote database where the actual table data is stored.

Korzystając z zewnętrznego źródła danych, jak opisano w poprzedniej sekcji, Składnia służąca do tworzenia tabel zewnętrznych jest następująca:Using an external data source as outlined in the previous section, the syntax to create external tables is as follows:

Klauzula DATA_SOURCE definiuje zewnętrzne źródło danych (tj. zdalną bazę danych w przypadku partycjonowania pionowego), które jest używane dla tabeli zewnętrznej.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.

Klauzule SCHEMA_NAME i OBJECT_NAME umożliwiają mapowanie definicji tabeli zewnętrznej na tabelę w innym schemacie w zdalnej bazie danych lub w tabeli o innej nazwie odpowiednio.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. Jest to przydatne, jeśli chcesz zdefiniować tabelę zewnętrzną w widoku wykazu lub DMV na zdalnej bazie danych, lub dowolną inną sytuację, w której nazwa tabeli zdalnej jest już pobrana lokalnie.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.

Poniższa instrukcja języka DDL powoduje porzucanie istniejącej definicji tabeli zewnętrznej z wykazu lokalnego.The following DDL statement drops an existing external table definition from the local catalog. Nie ma to wpływu na zdalną bazę danych.It does not impact the remote database.

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

Uprawnienia do tworzenia/upuszczania tabeli zewnętrznej: należy zmienić wszystkie uprawnienia zewnętrznych źródeł danych w przypadku kodu DDL tabeli zewnętrznej, który jest również konieczny do odwoływania się do bazowego źródła danych.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.

Zagadnienia dotyczące bezpieczeństwaSecurity considerations

Użytkownicy z dostępem do tabeli zewnętrznej automatycznie uzyskują dostęp do podstawowych tabel zdalnych w ramach poświadczeń podanych w definicji zewnętrznego źródła danych.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. Należy dokładnie zarządzać dostępem do tabeli zewnętrznej, aby uniknąć niepożądanego podniesienia uprawnień za pomocą poświadczeń zewnętrznego źródła danych.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. Regularne uprawnienia SQL mogą służyć do udzielenia lub odwołania dostępu do tabeli zewnętrznej, tak jakby była to zwykła tabela.Regular SQL permissions can be used to GRANT or REVOKE access to an external table just as though it were a regular table.

Przykład: wykonywanie zapytań dotyczących partycjonowanych baz danychExample: querying vertically partitioned databases

Następujące zapytanie wykonuje sprzężenie trójwymiarowe między dwiema lokalnymi tabelami zamówień i wierszy zamówienia oraz tabelą zdalną dla klientów.The following query performs a three-way join between the two local tables for orders and order lines and the remote table for customers. Jest to przykład przypadku użycia danych referencyjnych dla zapytania elastycznego: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

Procedura składowana dla zdalnego wykonywania T-SQL: SP _ execute_remoteStored procedure for remote T-SQL execution: sp_execute_remote

W przypadku zapytań elastycznych wprowadzono również procedurę składowaną, która zapewnia bezpośredni dostęp do zdalnej bazy danych.Elastic query also introduces a stored procedure that provides direct access to the remote database. Procedura składowana jest nazywana SP _ Execute _ Remote i może służyć do wykonywania zdalnych procedur składowanych lub kodu T-SQL w zdalnej bazie danych.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. Przyjmuje następujące parametry:It takes the following parameters:

  • Nazwa źródła danych (nvarchar): Nazwa zewnętrznego źródła danych typu RDBMS.Data source name (nvarchar): The name of the external data source of type RDBMS.
  • Query (nvarchar): zapytanie T-SQL, które ma zostać wykonane na zdalnej bazie danych.Query (nvarchar): The T-SQL query to be executed on the remote database.
  • Deklaracja parametru (nvarchar)-Optional: ciąg z definicjami typu danych dla parametrów używanych w parametrze zapytania (na przykład sp_executesql).Parameter declaration (nvarchar) - optional: String with data type definitions for the parameters used in the Query parameter (like sp_executesql).
  • Lista wartości parametrów — opcjonalne: rozdzielana przecinkami lista wartości parametrów (takich jak sp_executesql).Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

Funkcja Sp _ Execute _ umożliwia zdalne użycie zewnętrznego źródła danych podanego w parametrach wywołania, aby wykonać daną instrukcję T-SQL w zdalnej bazie danych.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. Używa poświadczeń zewnętrznego źródła danych, aby nawiązać połączenie ze zdalną bazą danych.It uses the credential of the external data source to connect to the remote database.

Przykład:Example:

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

Łączność dla narzędziConnectivity for tools

Za pomocą regularnych SQL Server parametrów połączenia można łączyć narzędzia do obsługi analizy biznesowej i danych z bazami danych na serwerze, na którym jest włączona funkcja elastyczne zapytania i zdefiniowane tabele zewnętrzne.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. Upewnij się, że SQL Server jest obsługiwane jako źródło danych dla narzędzia.Make sure that SQL Server is supported as a data source for your tool. Następnie zapoznaj się z bazą danych zapytań elastycznych i jej tabelami zewnętrznymi tak samo jak każda inna baza danych SQL Server, z którą chcesz nawiązać połączenie za pomocą narzędzia.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.

Najlepsze rozwiązaniaBest practices

  • Upewnij się, że baza danych punktu końcowego zapytania elastycznego ma dostęp do zdalnej bazy danych przez włączenie dostępu do usług platformy Azure w Azure SQL Database konfiguracji zapory.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. Upewnij się również, że poświadczenie podane w definicji zewnętrznego źródła danych może pomyślnie zalogować się do zdalnej bazy danych i ma uprawnienia dostępu do tabeli zdalnej.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.
  • Elastyczne zapytanie działa najlepiej w przypadku zapytań, w których można wykonywać większość obliczeń w zdalnych bazach danych.Elastic query works best for queries where most of the computation can be done on the remote databases. Zwykle uzyskuje się najlepszą wydajność zapytań za pomocą predykatów filtrów selektywnych, które mogą być oceniane w zdalnych bazach danych lub sprzężenia, które można całkowicie wykonać w zdalnej bazie danych.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. Inne wzorce zapytań mogą wymagać załadowania dużych ilości danych ze zdalnej bazy danych i mogą działać nieprawidłowo.Other query patterns may need to load large amounts of data from the remote database and may perform poorly.

Następne krokiNext steps