Kueri di seluruh database cloud dengan skema berbeda (pratinjau)

  • Artikel

Berlaku untuk:Azure SQL Database

Query across tables in different databases

Database yang dipartisi secara vertikal menggunakan set tabel yang berbeda pada database yang berbeda. Itu berarti bahwa skemanya berbeda pada database yang berbeda. Misalnya, semua tabel untuk inventaris berada di satu database sementara semua tabel terkait akuntansi berada di database kedua.

Prasyarat

  • Pengguna harus memiliki izin MENGUBAH SUMBER DATA EKSTERNAL. Izin ini disertakan dengan izin MENGUBAH DATABASE.
  • MENGUBAH izin SUMBER DATA EKSTERNAL diperlukan untuk merujuk ke sumber data yang mendasarinya.

Gambaran Umum

Catatan

Tidak seperti pemartisian horizontal, pernyataan DDL ini tidak bergantung pada mendefinisikan tingkat data dengan peta shard melalui pustaka klien database elastis.

  1. CREATE MASTER KEY
  2. CREATE DATABASE SCOPED CREDENTIAL
  3. CREATE EXTERNAL DATA SOURCE
  4. CREATE EXTERNAL TABLE

Membuat kunci induk dan info masuk yang dicakup database

Info masuk digunakan oleh kueri elastis untuk menyambungkan ke database jarak jauh Anda.

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

Catatan

Pastikan bahwa <username> tidak menyertakan akhiran "@servername".

Buat sumber data eksternal

Sintaksis:

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

Penting

Parameter TYPE harus diset ke RDBMS.

Contoh

Contoh berikut mengilustrasikan penggunaan pernyataan CREATE untuk sumber data eksternal.

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

Untuk mengambil daftar sumber data eksternal saat ini:

select * from sys.external_data_sources;

Tabel Eksternal

Sintaksis:

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

Contoh

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

Contoh berikut ini menunjukkan cara mengambil daftar tabel eksternal dari database saat ini:

select * from sys.external_tables;

Keterangan

Kueri elastis memperluas sintaks tabel eksternal yang ada untuk menentukan tabel eksternal yang menggunakan sumber data eksternal dari jenis RDBMS. Definisi tabel eksternal untuk pemartisian vertikal mencakup aspek-aspek berikut:

  • Skema: Tabel eksternal DDL mendefinisikan skema yang dapat digunakan oleh kueri Anda. Skema yang disediakan dalam definisi tabel eksternal Anda harus sesuai dengan skema tabel dalam database jarak jauh tempat data aktual disimpan.
  • Referensi database jarak jauh: Tabel eksternal DDL merujuk ke sumber data eksternal. Sumber data eksternal menentukan nama server dan nama database dari database jarak jauh tempat data tabel aktual disimpan.

Dengan menggunakan sumber data eksternal sebagaimana diuraikan di bagian sebelumnya, sintaks yang digunakan untuk membuat tabel eksternal adalah sebagai berikut:

Klausul DATA_SOURCE menentukan sumber data eksternal (yaitu database jarak jauh pada partisi vertikal) yang digunakan untuk tabel eksternal.

Klausul SCHEMA_NAME dan OBJECT_NAME mengizinkan pemetaan definisi tabel eksternal ke satu tabel di dalam skema yang berbeda pada database jarak jauh, atau ke tabel dengan nama yang berbeda, secara berurutan. Pemetaan ini berguna jika Anda ingin menentukan tabel eksternal ke tampilan katalog atau DMV pada database jarak jauh Anda - atau situasi apa pun ketika nama tabel jarak jauh sudah diambil secara lokal.

Pernyataan DDL berikut menghilangkan definisi tabel eksternal yang sudah ada dari katalog lokal. Hal ini tidak berdampak pada database jarak jauh.

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

Izin untuk MEMBUAT/MENGHILANGKAN TABEL EKSTERNAL: Izin untuk MENGUBAH SETIAP SUMBER DATA EKSTERNAL diperlukan untuk DDL tabel eksternal yang juga diperlukan untuk merujuk ke sumber data yang mendasarinya.

Pertimbangan keamanan

Pengguna dengan akses ke tabel eksternal secara otomatis akan mendapatkan akses ke tabel jarak jauh yang mendasarinya di bawah info masuk yang diberikan dalam definisi sumber data eksternal. Kelola akses ke tabel eksternal dengan hati-hati untuk menghindari elevasi yang tidak diinginkan melalui info masuk dari sumber data eksternal. Izin SQL reguler dapat digunakan untuk MEMBERIKAN atau MENCABUT akses ke tabel eksternal seolah-olah itu adalah tabel biasa.

Contoh: mengkueri database yang dipartisi secara vertikal

Kueri berikut melakukan penggabungan tiga arah antara dua tabel lokal untuk pesanan dan baris pesanan dan tabel jarak jauh untuk pelanggan. Ini adalah contoh kasus penggunaan data referensi untuk kueri elastis:

    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

Prosedur tersimpan untuk eksekusi T-SQL jarak jauh: sp_execute_remote

Kueri elastis juga memperkenalkan prosedur tersimpan yang menyediakan akses langsung ke database jarak jauh. Prosedur tersimpan disebut sp_execute _remote dan dapat digunakan untuk menjalankan prosedur tersimpan jarak jauh atau kode T-SQL pada database jarak jauh. Prosedur ini membutuhkan parameter berikut:

  • Nama sumber data (nvarchar): Nama sumber data eksternal dari jenis RDBMS.
  • Kueri (nvarchar): Kueri T-SQL yang akan dijalankan pada database jarak jauh.
  • Deklarasi parameter (nvarchar) - opsional: Untai dengan definisi jenis data untuk parameter yang digunakan dalam parameter Kueri (seperti sp_executesql).
  • Daftar nilai parameter - opsional: Daftar nilai parameter yang dipisahkan koma (seperti sp_executesql).

sp_execute_remote menggunakan sumber data eksternal yang disediakan dalam parameter pemanggilan untuk mengeksekusi pernyataan T-SQL yang diberikan pada database jarak jauh. Hal ini menggunakan info masuk dari sumber data eksternal untuk menyambungkan ke database jarak jauh.

Contoh:

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

Konektivitas untuk alat

Anda bisa menggunakan string koneksi SQL Server reguler untuk menyambungkan BI dan alat integrasi data Anda ke database di server yang memiliki kueri elastis yang aktif dan tabel eksternal yang ditentukan. Pastikan bahwa SQL Server didukung sebagai sumber data untuk alat Anda. Kemudian lihat database kueri elastis dan tabel eksternalnya seperti database SQL Server lainnya yang akan Anda sambungkan dengan alat Anda.

Praktik terbaik

  • Pastikan bahwa database titik akhir kueri elastis telah diberikan akses ke database jarak jauh dengan mengaktifkan akses untuk Layanan Azure dalam konfigurasi firewall Azure SQL Database-nya. Pastikan juga bahwa info masuk yang disediakan dalam definisi sumber data eksternal dapat masuk ke database jarak jauh dan memiliki izin untuk mengakses tabel jarak jauh.
  • Kueri elastis berfungsi paling baik untuk kueri yang sebagian besar komputasinya dapat dilakukan pada database jarak jauh. Umumnya, Anda akan mendapatkan performa kueri terbaik dengan predikat aturan selektif yang dapat dievaluasi pada database jarak jauh atau gabungan yang dapat dilakukan sepenuhnya pada database jarak jauh. Pola kueri lainnya mungkin perlu memuat data dalam jumlah besar dari database jarak jauh dan mungkin akan membuat performanya menjadi buruk.

Langkah berikutnya