Wykonywanie zapytań dotyczących baz danych w chmurze przy użyciu różnych schematów (wersja zapoznawcza)

Dotyczy: baza danych Azure SQL

Wykonywanie zapytań względem tabel w różnych bazach danych

Bazy danych partycjonowane w pionie używają różnych zestawów tabel w różnych bazach danych. Oznacza to, że schemat różni się w różnych bazach danych. Na przykład wszystkie tabele spisu znajdują się w jednej bazie danych, podczas gdy wszystkie tabele związane z księgowością znajdują się w drugiej bazie danych.

Wymagania wstępne

  • Użytkownik musi mieć uprawnienie ALTER ANY EXTERNAL DATA SOURCE. To uprawnienie jest dołączone do uprawnienia ALTER DATABASE.
  • ABY odwołać się do bazowego źródła danych, potrzebne są uprawnienia ALTER ANY EXTERNAL DATA SOURCE.

Omówienie

Uwaga

W przeciwieństwie do partycjonowania poziomego te instrukcje DDL nie zależą od definiowania warstwy danych z mapą fragmentów za pośrednictwem elastycznej biblioteki klienta bazy danych.

  1. TWORZENIE KLUCZA GŁÓWNEGO
  2. TWORZENIE POŚWIADCZENIA W ZAKRESIE BAZY DANYCH
  3. TWORZENIE ZEWNĘTRZNEGO ŹRÓDŁA DANYCH
  4. TWORZENIE TABELI ZEWNĘTRZNEJ

Tworzenie klucza głównego o zakresie bazy danych i poświadczeń

Poświadczenie jest używane przez elastyczne zapytanie do łączenia się ze zdalnymi bazami danych.

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

Uwaga

Upewnij się, że sufiks "@servername" nie zawiera żadnego sufiksu <username> .

Tworzenie zewnętrznych źródeł danych

Składnia:

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

Ważne

Parametr TYPE musi być ustawiony na RDBMS.

Przykład

Poniższy przykład ilustruje użycie instrukcji CREATE dla zewnętrznych źródeł danych.

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:

select * from sys.external_data_sources;

Tabele zewnętrzne

Składnia:

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

Przykład

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

W poniższym przykładzie pokazano, jak pobrać listę tabel zewnętrznych z bieżącej bazy danych:

select * from sys.external_tables;

Uwagi

Zapytanie elastyczne rozszerza istniejącą składnię tabeli zewnętrznej w celu zdefiniowania tabel zewnętrznych używających zewnętrznych źródeł danych typu RDBMS. Definicja tabeli zewnętrznej na potrzeby partycjonowania pionowego obejmuje następujące aspekty:

  • Schemat: tabela zewnętrzna DDL definiuje schemat, którego mogą używać zapytania. Schemat podany w definicji tabeli zewnętrznej musi być zgodny ze schematem tabel w zdalnej bazie danych, w której są przechowywane rzeczywiste dane.
  • Odwołanie do zdalnej bazy danych: tabela zewnętrzna DDL odwołuje się do zewnętrznego źródła danych. Zewnętrzne źródło danych określa nazwę serwera i nazwę bazy danych zdalnej bazy danych, w której są przechowywane rzeczywiste dane tabeli.

Użycie zewnętrznego źródła danych, jak opisano w poprzedniej sekcji, składnia tworzenia tabel zewnętrznych jest następująca:

Klauzula DATA_SOURCE definiuje zewnętrzne źródło danych (tj. zdalną bazę danych w partycjonowaniu pionowym), które jest używane dla tabeli zewnętrznej.

Klauzule SCHEMA_NAME i OBJECT_NAME umożliwiają mapowanie definicji tabeli zewnętrznej na tabelę w innym schemacie w zdalnej bazie danych lub do tabeli o innej nazwie. To mapowanie jest przydatne, jeśli chcesz zdefiniować tabelę zewnętrzną w widoku katalogu lub widoku DMV w zdalnej bazie danych — lub w innej sytuacji, w której nazwa tabeli zdalnej jest już zajęta lokalnie.

Poniższa instrukcja DDL odrzuca istniejącą definicję tabeli zewnętrznej z wykazu lokalnego. Nie ma to wpływu na zdalną bazę danych.

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

Uprawnienia do tabeli CREATE/DROP EXTERNAL TABLE: DO odwoływania się do bazowego źródła danych są wymagane uprawnienia ALTER ANY EXTERNAL DATA SOURCE( ALTER ANY EXTERNAL DATA SOURCE).

Zagadnienia dotyczące bezpieczeństwa

Użytkownicy z dostępem do tabeli zewnętrznej automatycznie uzyskują dostęp do bazowych tabel zdalnych w ramach poświadczeń podanych w definicji zewnętrznego źródła danych. Starannie zarządzaj dostępem do tabeli zewnętrznej, aby uniknąć niepożądanego podniesienia uprawnień za pomocą poświadczeń zewnętrznego źródła danych. Zwykłe uprawnienia SQL mogą służyć do UDZIELANIA lub ODWOŁYWANIE dostępu do tabeli zewnętrznej, tak jakby była to zwykła tabela.

Przykład: wykonywanie zapytań dotyczących baz danych partycjonowanych w pionie

Poniższe zapytanie wykonuje trzykierunkowe sprzężenia między dwiema tabelami lokalnymi dla zamówień i wierszy zamówień oraz tabelą zdalną dla klientów. Jest to przykład przypadku użycia danych referencyjnych dla zapytania elastycznego:

    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 dotycząca zdalnego wykonywania języka T-SQL: sp_execute_remote

Elastyczne zapytanie wprowadza również procedurę składowaną, która zapewnia bezpośredni dostęp do zdalnej bazy danych. 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. Przyjmuje następujące parametry:

  • Nazwa źródła danych (nvarchar): nazwa zewnętrznego źródła danych typu RDBMS.
  • Zapytanie (nvarchar): zapytanie T-SQL do wykonania w zdalnej bazie danych.
  • Deklaracja parametru (nvarchar) — opcjonalnie: ciąg z definicjami typów danych dla parametrów używanych w parametrze zapytania (na przykład sp_executesql).
  • Lista wartości parametrów — opcjonalnie: rozdzielona przecinkami lista wartości parametrów (na przykład sp_executesql).

Sp_execute_remote używa zewnętrznego źródła danych podanego w parametrach wywołania, aby wykonać daną instrukcję języka T-SQL w zdalnej bazie danych. Używa poświadczeń zewnętrznego źródła danych do nawiązywania połączenia z zdalną bazą danych.

Przykład:

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

Łączność dla narzędzi

Można używać zwykłych parametrów połączenia SQL Server do łączenia narzędzi analizy biznesowej i integracji danych z bazami danych na serwerze z włączonymi elastycznymi zapytaniami i zdefiniowanymi tabelami zewnętrznymi. Upewnij się, że SQL Server jest obsługiwana jako źródło danych dla narzędzia. Następnie zapoznaj się z bazą danych zapytań elastycznych i jej tabelami zewnętrznymi, podobnie jak każda inna baza danych SQL Server, z którą można nawiązać połączenie z narzędziem.

Najlepsze rozwiązania

  • Upewnij się, że baza danych punktu końcowego zapytań elastycznych ma dostęp do zdalnej bazy danych, włączając dostęp dla usług platformy Azure w konfiguracji zapory usługi Azure SQL Database. Upewnij się również, że poświadczenia podane w zewnętrznej definicji źródła danych mogą pomyślnie zalogować się do zdalnej bazy danych i mieć uprawnienia dostępu do tabeli zdalnej.
  • Zapytanie elastyczne działa najlepiej w przypadku zapytań, w których większość obliczeń można wykonywać na zdalnych bazach danych. Zazwyczaj uzyskujesz najlepszą wydajność zapytań z predykatami filtru selektywnego, które można ocenić na zdalnych bazach danych lub sprzężeniach, które mogą być wykonywane całkowicie w zdalnej bazie danych. Inne wzorce zapytań mogą wymagać załadowania dużych ilości danych ze zdalnej bazy danych i mogą działać źle.

Następne kroki