Raportowanie w skalowanych w poziomie baz danych w chmurze (wersja zapoznawcza)

  • Artykuł

Dotyczy:Azure SQL Database

Query across shards

Podzielone na fragmenty bazy danych dystrybuują wiersze w skalowanej w poziomie warstwie danych. Schemat jest identyczny we wszystkich uczestniczących bazach danych, nazywanych również partycjonowaniem poziomym. Za pomocą zapytania elastycznego można tworzyć raporty obejmujące wszystkie bazy danych w bazie danych podzielonej na fragmenty.

Aby zapoznać się z przewodnikiem Szybki start, zobacz Raportowanie w skalowanych w poziomie baz danych w chmurze.

W przypadku nieuhardowanych baz danych zobacz Query across cloud databases with different schemas (Wykonywanie zapytań między bazami danych w chmurze przy użyciu różnych schematów).

Wymagania wstępne

Omówienie

Te instrukcje tworzą reprezentację metadanych warstwy danych podzielonych na fragmenty w elastycznej bazie danych zapytań.

  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

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

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

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

Uwaga

Upewnij się, że "<nazwa użytkownika>" nie zawiera żadnego sufiksu "@servername".

1.2. Tworzenie zewnętrznych źródeł danych

Składnia:

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

Przykład

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

Pobierz listę bieżących zewnętrznych źródeł danych:

select * from sys.external_data_sources;

Zewnętrzne źródło danych odwołuje się do mapy fragmentów. Następnie zapytanie elastyczne używa zewnętrznego źródła danych i bazowej mapy fragmentów, aby wyliczyć bazy danych, które uczestniczą w warstwie danych. Te same poświadczenia są używane do odczytywania mapy fragmentów i uzyskiwania dostępu do danych na fragmentach podczas przetwarzania zapytania elastycznego.

1.3. Tworzenie tabel zewnętrznych

Składnia:

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

Przykład

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

Pobierz listę tabel zewnętrznych z bieżącej bazy danych:

SELECT * from sys.external_tables;

Aby usunąć tabele zewnętrzne:

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

Uwagi

Klauzula DATA_SOURCE definiuje zewnętrzne źródło danych (mapę fragmentów), które jest używane dla tabeli zewnętrznej.

Klauzule SCHEMA_NAME i OBJECT_NAME mapuje definicję tabeli zewnętrznej na tabelę w innym schemacie. W przypadku pominięcia przyjmuje się, że schemat obiektu zdalnego ma być dbo i przyjmuje się, że jego nazwa jest taka sama jak zdefiniowana nazwa tabeli zewnętrznej. Jest to przydatne, jeśli nazwa tabeli zdalnej jest już zajęta w bazie danych, w której chcesz utworzyć tabelę zewnętrzną. Na przykład chcesz zdefiniować tabelę zewnętrzną, aby uzyskać zagregowany widok widoków wykazu lub widoków DMV w warstwie danych skalowanej w poziomie. Ponieważ widoki wykazu i dynamiczne widoki zarządzania już istnieją lokalnie, nie można używać ich nazw dla definicji tabeli zewnętrznej. Zamiast tego użyj innej nazwy i użyj nazwy widoku wykazu lub dynamicznego widoku zarządzania w klauzulach SCHEMA_NAME i/lub OBJECT_NAME. (Zobacz poniższy przykład).

Klauzula DISTRIBUTION określa dystrybucję danych używaną dla tej tabeli. Procesor zapytań wykorzystuje informacje podane w klauzuli DISTRIBUTION w celu utworzenia najbardziej wydajnych planów zapytań.

  1. SHARDED oznacza, że dane są partycjonowane w poziomie w bazach danych. Kluczem partycjonowania dla dystrybucji danych jest parametr sharding_column_name>.<
  2. REPLIKOWANE oznacza, że identyczne kopie tabeli znajdują się w każdej bazie danych. Twoim zadaniem jest zapewnienie, że repliki są identyczne w bazach danych.
  3. ROUND_ROBIN oznacza, że tabela jest partycjonowana poziomo przy użyciu metody dystrybucji zależnej od aplikacji.

Odwołanie do warstwy danych: tabela zewnętrzna DDL odnosi się do zewnętrznego źródła danych. Zewnętrzne źródło danych określa mapę fragmentów, która udostępnia tabelę zewnętrzną z informacjami niezbędnymi do zlokalizowania wszystkich baz danych w warstwie danych.

Zagadnienia dotyczące zabezpieczeń

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. Unikaj niepożądanego podniesienia uprawnień za pośrednictwem poświadczeń zewnętrznego źródła danych. Użyj funkcji GRANT lub REVOKE dla tabeli zewnętrznej, tak jakby była to zwykła tabela.

Po zdefiniowaniu zewnętrznego źródła danych i tabel zewnętrznych można teraz używać pełnego języka T-SQL w tabelach zewnętrznych.

Przykład: wykonywanie zapytań względem baz danych partycjonowanych w poziomie

Poniższe zapytanie wykonuje trzykierunkowe sprzężenia między magazynami, zamówieniami i wierszami zamówień oraz używa kilku agregacji i filtru selektywnego. Przyjęto założenie(1) partycjonowanie poziome (fragmentowanie) i (2), które magazyny, zamówienia i wiersze zamówień są fragmentowane według kolumny identyfikatora magazynu, a zapytanie elastyczne może współlokować sprzężenia na fragmentach i przetwarzać kosztowną część zapytania na fragmentach równolegle.

    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

Procedura składowana dotycząca zdalnego wykonywania języka T-SQL: sp_execute_remote

Zapytanie elastyczne wprowadza również procedurę składowaną, która zapewnia bezpośredni dostęp do fragmentów. Procedura składowana jest wywoływana sp_execute _remote i może służyć do wykonywania zdalnych procedur składowanych lub kodu T-SQL w zdalnych bazach 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, które ma zostać wykonane na każdym fragmentie.
  • Deklaracja parametru (nvarchar) — opcjonalnie: ciąg z definicjami typu 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 zdalnych bazach danych. Używa poświadczeń zewnętrznego źródła danych, aby nawiązać połączenie z bazą danych menedżera map fragmentów i zdalnymi bazami danych.

Przykład:

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

Łączność dla narzędzi

Użyj zwykłych parametrów połączenia programu SQL Server, aby połączyć aplikację, twoją usługę BI i narzędzia integracji danych z bazą danych przy użyciu zewnętrznych definicji tabel. Upewnij się, że program SQL Server jest obsługiwany jako źródło danych dla narzędzia. Następnie odwołaj się do elastycznej bazy danych zapytań, takiej jak każda inna baza danych programu SQL Server połączona z narzędziem, i użyj tabel zewnętrznych z narzędzia lub aplikacji tak, jakby były to tabele lokalne.

Najlepsze praktyki

  • Upewnij się, że elastyczna baza danych punktu końcowego zapytań ma dostęp do bazy danych mapy fragmentów i wszystkich fragmentów za pośrednictwem zapór usługi SQL Database.
  • Zweryfikuj lub wymuś dystrybucję danych zdefiniowaną przez tabelę zewnętrzną. Jeśli rzeczywista dystrybucja danych różni się od dystrybucji określonej w definicji tabeli, zapytania mogą powodować nieoczekiwane wyniki.
  • Zapytanie elastyczne obecnie nie wykonuje eliminacji fragmentów, gdy predykaty nad kluczem fragmentowania umożliwiają bezpieczne wykluczanie niektórych fragmentów z przetwarzania.
  • Zapytanie elastyczne działa najlepiej w przypadku zapytań, w których większość obliczeń można wykonywać na fragmentach. Zazwyczaj uzyskujesz najlepszą wydajność zapytań z predykatami filtru selektywnego, które można ocenić na fragmentach lub sprzężeniach na kluczach partycjonowania, które można wykonać w sposób dopasowany do partycji na wszystkich fragmentach. Inne wzorce zapytań mogą wymagać załadowania dużych ilości danych z fragmentów do węzła głównego i może działać źle

Następne kroki