Sdílet prostřednictvím

Dotazování napříč cloudovými databázemi s různými schématy (Preview)

  • Článek

Platí pro: Azure SQL Database

Dotazování napříč tabulkami v různých databázích

Vertikálně dělené databáze používají různé sady tabulek v různých databázích. To znamená, že schéma se liší v různých databázích. Například všechny tabulky pro inventář jsou v jedné databázi, zatímco všechny tabulky související s účetnictvím jsou v druhé databázi.

Požadavky

  • Uživatel musí mít oprávnění ALTER ANY EXTERNAL DATA SOURCE. Toto oprávnění je součástí oprávnění ALTER DATABASE.
  • K odkazování na podkladový zdroj dat jsou potřeba oprávnění ALTER ANY EXTERNAL DATA SOURCE.

Přehled

Poznámka:

Na rozdíl od horizontálního dělení tyto příkazy DDL nezávisí na definování datové vrstvy s mapou horizontálních oddílů prostřednictvím klientské knihovny elastické databáze.

  1. VYTVOŘENÍ HLAVNÍHO KLÍČE
  2. VYTVOŘENÍ PŘIHLAŠOVACÍCH ÚDAJŮ S ROZSAHEM DATABÁZE
  3. VYTVOŘENÍ EXTERNÍHO ZDROJE DAT
  4. VYTVOŘENÍ EXTERNÍ TABULKY

Vytvoření hlavního klíče a přihlašovacích údajů s vymezeným oborem databáze

Přihlašovací údaje používá elastický dotaz pro připojení ke vzdáleným databázím.

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

Poznámka:

Ujistěte se, že neobsahuje příponu <username> "@servername" .

Vytvoření externích zdrojů dat

Syntaxe:

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

Důležité

Parametr TYPE musí být nastaven na RDBMS.

Příklad

Následující příklad ukazuje použití příkazu CREATE pro externí zdroje dat.

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
   WITH
      (
         TYPE=RDBMS,
         LOCATION='myserver.database.windows.net',
         DATABASE_NAME='ReferenceData',
         CREDENTIAL= SqlUser
      );

Načtení seznamu aktuálních externích zdrojů dat:

select * from sys.external_data_sources;

Externí tabulky

Syntaxe:

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

Příklad

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

Následující příklad ukazuje, jak načíst seznam externích tabulek z aktuální databáze:

select * from sys.external_tables;

Poznámky

Elastický dotaz rozšiřuje existující syntaxi externí tabulky a definuje externí tabulky, které používají externí zdroje dat typu RDBMS. Definice externí tabulky pro vertikální dělení pokrývá následující aspekty:

  • Schéma: DDL externí tabulky definuje schéma, které můžou vaše dotazy používat. Schéma poskytované v definici externí tabulky musí odpovídat schématu tabulek ve vzdálené databázi, ve které jsou uložena skutečná data.
  • Odkaz na vzdálenou databázi: Externí tabulka DDL odkazuje na externí zdroj dat. Externí zdroj dat určuje název serveru a název databáze vzdálené databáze, kde jsou uložena skutečná data tabulky.

Použití externího zdroje dat, jak je popsáno v předchozí části, syntaxe pro vytvoření externích tabulek je následující:

Klauzule DATA_SOURCE definuje externí zdroj dat (tj. vzdálenou databázi ve vertikálním dělení), která se používá pro externí tabulku.

Klauzule SCHEMA_NAME a OBJECT_NAME umožňují mapování definice externí tabulky na tabulku v jiném schématu vzdálené databáze nebo na tabulku s jiným názvem. Toto mapování je užitečné, pokud chcete definovat externí tabulku pro zobrazení katalogu nebo zobrazení dynamické správy ve vzdálené databázi – nebo jakoukoli jinou situaci, kdy se název vzdálené tabulky už používá místně.

Následující příkaz DDL zahodí existující definici externí tabulky z místního katalogu. Nemá vliv na vzdálenou databázi.

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

Oprávnění pro CREATE/DROP EXTERNAL TABLE: ALTER ANY EXTERNAL DATA SOURCE permissions are needed for external table DDL, což je také potřeba k odkazování na podkladový zdroj dat.

Bezpečnostní aspekty

Uživatelé s přístupem k externí tabulce automaticky získají přístup k podkladovým vzdáleným tabulkám v rámci přihlašovacích údajů zadaných v definici externího zdroje dat. Pečlivě spravujte přístup k externí tabulce, abyste se vyhnuli nežádoucímu zvýšení oprávnění prostřednictvím přihlašovacích údajů externího zdroje dat. Běžná oprávnění SQL se dají použít k udělení nebo odvolání přístupu k externí tabulce stejně jako běžná tabulka.

Příklad: Dotazování vertikálně dělených databází

Následující dotaz provede třícestné spojení mezi dvěma místními tabulkami pro objednávky a řádky objednávek a vzdálenou tabulkou pro zákazníky. Toto je příklad případu použití referenčních dat pro elastický dotaz:

    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

Uložená procedura pro vzdálené spouštění T-SQL: sp_execute_remote

Elastický dotaz také zavádí uloženou proceduru, která poskytuje přímý přístup ke vzdálené databázi. Uložená procedura se nazývá sp_execute _remote a lze ji použít ke spouštění vzdálených uložených procedur nebo kódu T-SQL ve vzdálené databázi. Přebírá následující parametry:

  • Název zdroje dat (nvarchar): Název externího zdroje dat typu RDBMS.
  • Dotaz (nvarchar): Dotaz T-SQL, který se má spustit ve vzdálené databázi.
  • Deklarace parametru (nvarchar) – volitelné: Řetězec s definicemi datového typu pro parametry použité v parametru dotazu (například sp_executesql).
  • Seznam hodnot parametrů – volitelný: Seznam hodnot parametrů oddělený čárkami (například sp_executesql).

Sp_execute_remote používá externí zdroj dat zadaný v parametrech vyvolání ke spuštění daného příkazu T-SQL ve vzdálené databázi. Používá přihlašovací údaje externího zdroje dat pro připojení ke vzdálené databázi.

Příklad:

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

Možnosti připojení pro nástroje

Běžné připojovací řetězec SQL Serveru můžete použít k připojení nástrojů pro integraci BI a dat k databázím na serveru s povoleným elastickým dotazem a externími tabulkami. Ujistěte se, že sql Server je podporovaný jako zdroj dat pro váš nástroj. Pak se podívejte na elastickou databázi dotazů a její externí tabulky stejně jako jakoukoli jinou databázi SQL Serveru, ke které byste se připojili pomocí nástroje.

Osvědčené postupy

  • Ujistěte se, že databáze koncového bodu elastického dotazu získala přístup ke vzdálené databázi povolením přístupu ke službám Azure v konfiguraci brány firewall služby Azure SQL Database. Ujistěte se také, že přihlašovací údaje zadané v definici externího zdroje dat se mohou úspěšně přihlásit ke vzdálené databázi a mít oprávnění pro přístup ke vzdálené tabulce.
  • Elastický dotaz je nejvhodnější pro dotazy, ve kterých lze většinu výpočtů provádět ve vzdálených databázích. Obvykle získáte nejlepší výkon dotazů pomocí predikátů selektivního filtru, které je možné vyhodnotit ve vzdálených databázích nebo spojeních, které je možné provést úplně ve vzdálené databázi. Jiné vzory dotazů mohou vyžadovat načtení velkého množství dat ze vzdálené databáze a jejich výkon může být nízký.

Další kroky