Share via

Melaporkan seluruh database cloud yang skalanya diperluas (pratinjau)

  • Artikel

Berlaku untuk:Azure SQL Database

Query across shards

Database yang dipecah mendistribusikan baris di seluruh tingkat data yang skalanya diperluas. Skema ini identik pada semua database yang berpartisipasi, juga dikenal sebagai pemartisian horizontal. Menggunakan kueri elastis, Anda bisa membuat laporan yang mencakup semua database dalam database yang dipecah.

Untuk memulai cepat, lihat Pelaporan di seluruh database cloud yang skalanya diperluas.

Untuk database yang tidak dipecah, lihat Membuat kueri di seluruh database awan dengan skema yang berbeda.

Prasyarat

Gambaran Umum

Pernyataan ini membuat representasi metadata dari tingkat data yang dipecah Anda dalam database kueri elastis.

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

1.1. 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 = 'password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

Catatan

Pastikan bahwa "<nama pengguna>" tidak menyertakan akhiran "@servername".

1.2 Membuat sumber data eksternal

Sintaksis:

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

Contoh

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

Mengambil daftar sumber data eksternal saat ini:

select * from sys.external_data_sources;

Sumber data eksternal mereferensikan peta pecahan Anda. Kueri elastis kemudian menggunakan sumber data eksternal dan peta pecahan yang mendasarinya untuk menghitung database yang berpartisipasi dalam tingkat data. Informasi masuk yang sama digunakan untuk membaca peta pecahan dan untuk mengakses data pada pecahan selama pemrosesan kueri elastis.

1.3 Membuat tabel eksternal

Sintaksis:

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

Contoh

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

Ambil daftar tabel eksternal dari database saat ini:

SELECT * from sys.external_tables;

Untuk mengilangkan tabel eksternal:

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

Keterangan

Klausa DATA_SOURCE mendefinisikan sumber data eksternal (peta pecahan) yang digunakan untuk tabel eksternal.

Klausa SCHEMA_NAME dan OBJECT_NAME memetakan definisi tabel eksternal ke tabel dalam skema yang berbeda. Jika dihilangkan, skema objek jarak jauh diasumsikan dbo dan namanya diasumsikan identik dengan nama tabel eksternal yang ditentukan. Skema ini berguna jika nama tabel jarak jauh Anda sudah diambil dalam database tempat Anda ingin membuat tabel eksternal. Misalnya, Anda ingin menentukan tabel eksternal untuk mendapatkan tampilan agregat tampilan katalog atau DMV pada tingkat data yang skalanya diperluas. Karena tampilan katalog dan DMV sudah ada secara lokal, Anda tidak dapat menggunakan nama mereka untuk definisi tabel eksternal. Sebagai gantinya, gunakan nama yang berbeda dan gunakan tampilan katalog atau nama DMV dalam klausa SCHEMA_NAME dan/atau OBJECT_NAME. (Lihat contoh di bawah ini.)

Klausa DISTRIBUTION menentukan distribusi data yang digunakan untuk tabel ini. Prosesor kueri menggunakan informasi yang disediakan dalam klausa DISTRIBUTION untuk menyusun rencana kueri yang paling efisien.

  1. SHARDED berarti data dipartisi secara horizontal di seluruh database. Kunci partisi untuk distribusi data adalah parameter <sharding_column_name>.
  2. REPLICATED berarti salinan tabel yang identik ada di setiap database. Anda bertanggung jawab untuk memastikan bahwa replika identik di seluruh database.
  3. ROUND_ROBIN berarti tabel dipartisi secara horizontal menggunakan metode distribusi yang bergantung pada aplikasi.

Referensi tingkat data: Tabel eksternal DDL merujuk ke sumber data eksternal. Sumber data eksternal menentukan peta pecahan yang menyediakan tabel eksternal dengan informasi yang diperlukan untuk menemukan semua database di tingkat data Anda.

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. Hindari elevasi melalui info masuk sumber data eksternal. Gunakan IZINKAN atau CABUT untuk tabel eksternal seolah-olah tabel tersebut adalah tabel biasa.

Setelah menentukan sumber data eksternal dan tabel eksternal Anda, sekarang Anda dapat menggunakan T-SQL lengkap di atas tabel eksternal.

Contoh: mengkueri database yang dipartisi horizontal

Kueri berikut ini melakukan gabungan tiga arah antara gudang, pesanan, dan garis pesanan dan menggunakan beberapa agregat dan filter selektif. Kueri ini mengasumsikan (1) pemartisian horizontal (pemecahan) dan (2) bahwa gudang, pesanan, dan garis pesanan dipecah oleh kolom ID gudang, dan bahwa kueri elastis dapat ikut menemukan gabungan pada pecahan dan memproses bagian mahal dari kueri pada pecahan secara paralel.

    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

Prosedur tersimpan untuk eksekusi T-SQL jarak jauh: sp_execute_remote

Kueri elastis juga memperkenalkan prosedur tersimpan yang menyediakan akses langsung ke pecahan. 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 dieksekusi pada setiap pecahan.
  • 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. Ini menggunakan informasi masuk sumber data eksternal untuk menyambungkan ke database manajer shardmap dan database jarak jauh.

Contoh:

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

Konektivitas untuk alat

Gunakan string koneksi SQL Server reguler untuk menyambungkan aplikasi, BI, dan alat integrasi data Anda ke database dengan definisi tabel eksternal Anda. Pastikan bahwa SQL Server didukung sebagai sumber data untuk alat Anda. Kemudian referensikan database kueri elastis seperti database SQL Server lainnya yang terhubung ke alat, dan gunakan tabel eksternal dari alat atau aplikasi Anda seolah-olah itu adalah tabel lokal.

Praktik terbaik

  • Pastikan bahwa database titik akhir kueri elastis telah diberikan akses ke database shardmap dan semua pecahan melalui firewall Microsoft Azure SQL Database.
  • Memvalidasi atau memberlakukan distribusi data yang ditentukan oleh tabel eksternal. Jika distribusi data aktual Anda yang sebenarnya berbeda dari distribusi yang ditentukan dalam definisi tabel Anda, kueri Anda dapat menghasilkan hasil yang tidak diharapkan.
  • Kueri elastis saat ini tidak melakukan eliminasi pecahan ketika predikat di atas kunci sharding akan memungkinkannya untuk dengan aman mengecualikan pecahan tertentu dari pemrosesan.
  • Kueri elastis berfungsi paling baik untuk kueri yang sebagian besar komputasinya dapat dilakukan pada pecahan. Anda biasanya mendapatkan kinerja kueri terbaik dengan predikat filter selektif yang dapat dievaluasi pada pecahan atau gabungan di atas tombol pemartisian yang dapat dilakukan dengan cara sejajar dengan partisi pada semua pecahan. Pola kueri lainnya mungkin perlu memuat data dalam jumlah besar dari pecahan ke simpul utama dan mungkin akan membuat performanya menjadi buruk

Langkah berikutnya