Tutorial: Membuat Gudang Data Logis dengan kumpulan SQL tanpa server

Dalam tutorial ini, Anda akan mempelajari cara membuat Gudang Data Logis (LDW) di atas penyimpanan Azure dan Azure Cosmos DB.

LDW adalah lapisan relasional yang dibangun di atas sumber data Azure seperti penyimpanan Azure Data Lake (ADLS), penyimpanan analitik Azure Cosmos DB, atau penyimpanan Azure Blob.

Create an LDW database

Anda perlu membuat database kustom tempat Anda akan menyimpan tabel dan tampilan eksternal yang mereferensikan sumber data eksternal.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Kolase ini akan memberikan performa optimal saat membaca Parquet dan Azure Cosmos DB. Jika Anda tidak ingin menentukan kolase database, pastikan Anda menentukan kolase ini dalam definisi kolom.

Mengonfigurasi sumber dan format data

Sebagai langkah pertama, Anda perlu mengonfigurasi sumber data dan menentukan format file dari data yang disimpan dari jarak jauh.

Buatlah sumber data

Sumber data mewakili informasi string koneksi yang menjelaskan lokasi penempatan data Anda dan cara mengautentikasi ke sumber data Anda.

Salah satu contoh definisi sumber data yang mereferensikan Kumpulan Data Terbuka Azure ECDC COVID 19 publik ditunjukkan dalam contoh berikut:

CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/'
);

Pemanggil dapat mengakses sumber data tanpa kredensial jika pemilik sumber data mengizinkan akses anonim atau memberikan akses eksplisit ke identitas Microsoft Entra pemanggil.

Anda dapat menentukan kredensial kustom secara eksplisit, yang akan digunakan saat mengakses data di sumber data eksternal.

Sebagai prasyarat, Anda harus membuat kunci master dalam database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Setup you password - you need to create master key only once';

Dalam sumber data eksternal berikut, kumpulan Synapse SQL harus menggunakan identitas ruang kerja yang dikelola untuk mengakses data dalam penyimpanan.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/',
    CREDENTIAL = WorkspaceIdentity
);

Untuk mengakses penyimpanan analitik Azure Cosmos DB, Anda perlu menentukan kredensial yang berisi kunci akun Azure Cosmos DB baca-saja.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<yourcosmosdbaccountkey>';

Setiap pengguna dengan peran Administrator Synapse dapat menggunakan kredensial ini untuk mengakses penyimpanan Azure Data Lake atau penyimpanan analitik Azure Cosmos DB. Jika Anda memiliki pengguna istimewa tingkat rendah yang tidak memiliki peran Administrator Sinapsis, Anda harus memberi mereka izin eksplisit untuk merujuk kredensial lingkup database ini:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO <user>
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO <user>
GO

Find more details in grant DATABASE SCOPED CREDENTIAL permissions page.

Define external file formats

Format file eksternal menentukan struktur file yang disimpan di sumber data eksternal. Anda dapat menentukan format file eksternal Parquet dan CSV:

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT CsvFormat WITH (  FORMAT_TYPE = DELIMITEDTEXT );

Untuk informasi selengkapnya, lihat Menggunakan tabel eksternal dengan Synapse SQL dan CREATE EXTERNAL FILE FORMAT untuk menjelaskan format file CSV atau Parquet.

Jelajahi sampel Anda

Setelah menyiapkan sumber data, Anda dapat menggunakan OPENROWSET fungsi ini untuk menjelajahi data. Fungsi OPENROWSET membaca konten sumber data jarak jauh (misalnya file) dan mengembalikan konten sebagai set barisan.

select top 10  *
from openrowset(bulk 'latest/ecdc_cases.parquet',
                data_source = 'ecdc_cases',
                format='parquet') as a

Fungsi OPENROWSET ini akan memberi informasi tentang kolom dalam file atau kontainer eksternal dan memungkinkan Anda menentukan skema tabel dan tampilan eksternal Anda.

Membuat tabel eksternal di penyimpanan Azure

Setelah menemukan skema, Anda dapat membuat tabel dan tampilan eksternal di atas sumber data eksternal. Praktik yang baik adalah mengatur tabel dan tampilan Anda dalam skema database. Dalam kueri berikut ini Anda dapat membuat skema lokasi untuk menempatkan semua objek yang mengakses himpunan data ECDC COVID di penyimpanan data Lake Azure:

create schema ecdc_adls;

Skema database berguna untuk mengelompokkan objek dan menentukan izin per skema.

Setelah menentukan skema, Anda dapat membuat tabel eksternal yang mereferensikan file. Tabel eksternal berikut ini mereferensikan file parket ECDC COVID yang ditempatkan di penyimpanan Azure:

create external table ecdc_adls.cases (
    date_rep                   date,
    day                        smallint,
    month                      smallint,
    year                       smallint,
    cases                      smallint,
    deaths                     smallint,
    countries_and_territories  varchar(256),
    geo_id                     varchar(60),
    country_territory_code     varchar(16),
    pop_data_2018              int,
    continent_exp              varchar(32),
    load_date                  datetime2(7),
    iso_country                varchar(16)
) with (
    data_source= ecdc_cases,
    location = 'latest/ecdc_cases.parquet',
    file_format = ParquetFormat
);

Pastikan Anda menggunakan tipe terkecil untuk kolom string dan angka untuk mengoptimalkan kinerja kueri Anda.

Membuat tampilan di Azure Cosmos DB

As an alternative to external tables, you can create views on top of your external data.

Serupa dengan tabel yang ditampilkan di contoh sebelumnya, Anda harus menempatkan tampilan dalam skema terpisah:

create schema ecdc_cosmosdb;

Sekarang Anda dapat membuat tampilan dalam skema yang mereferensikan kontainer Azure Cosmos DB:

CREATE OR ALTER VIEW ecdc_cosmosdb.Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=yourcosmosdbaccount;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) WITH
     ( date_rep varchar(20), 
       cases bigint,
       geo_id varchar(6) 
     ) as rows

Untuk mengoptimalkan performa, Anda harus menggunakan tipe sekecil mungkin dalam WITH definisi skema.

Catatan

Anda harus menempatkan kunci akun Azure Cosmos DB di dalam kredensial terpisah dan mereferensikan informasi kredensial ini dari fungsi OPENROWSET. Jangan menyimpan kunci akun Anda dalam definisi tampilan.

Akses dan izin

Sebagai langkah terakhir, Anda harus membuat pengguna database yang dapat mengakses LDW Anda, dan memberi mereka izin untuk memilih data dari tabel dan tampilan eksternal. Dalam skrip berikut, Anda dapat melihat cara menambahkan pengguna baru yang akan diautentikasi menggunakan identitas Microsoft Entra:

CREATE USER [jovan@contoso.com] FROM EXTERNAL PROVIDER;
GO

Instead of Microsoft Entra principals, you can create SQL principals that authenticate with the login name and password.

CREATE LOGIN [jovan] WITH PASSWORD = 'My Very strong Password ! 1234';
CREATE USER [jovan] FROM LOGIN [jovan];

Dalam kedua kasus, Anda dapat menetapkan izin kepada pengguna.

DENY ADMINISTER DATABASE BULK OPERATIONS TO [jovan@contoso.com]
GO
GRANT SELECT ON SCHEMA::ecdc_adls TO [jovan@contoso.com]
GO
GRANT SELECT ON OBJECT::ecdc_cosmosDB.cases TO [jovan@contoso.com]
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO [jovan@contoso.com]
GO

Aturan keamanan bergantung pada kebijakan keamanan Anda. Beberapa pedoman umumnya adalah:

  • Anda harus menolak ADMINISTER DATABASE BULK OPERATIONS izin kepada pengguna baru karena mereka seharusnya dapat membaca data hanya dengan menggunakan tabel dan tampilan eksternal yang telah disiapkan.
  • Anda harus memberikan SELECT izin hanya untuk tabel yang seharusnya dapat digunakan oleh beberapa pengguna.
  • Jika Anda menyediakan akses ke data menggunakan tampilan, Anda harus memberikan REFERENCES izin ke kredensial yang akan digunakan untuk mengakses sumber data eksternal.

Pengguna ini memiliki izin minimal yang diperlukan untuk mengkueri data eksternal. Jika Anda ingin membuat pengguna tingkat lanjut yang dapat menyiapkan izin, tabel eksternal, dan tampilan, Anda dapat memberikan izin kepada pengguna CONTROL.

GRANT CONTROL TO [jovan@contoso.com]

Keamanan berbasis peran

Alih-alih menetapkan izin untuk penggunaan individual, praktik yang baik adalah mengatur pengguna ke dalam peran dan mengelola izin di tingkat peran. Sampel kode berikut membuat peran baru yang mewakili orang-orang yang dapat menganalisis kasus COVID-19, dan menambahkan tiga pengguna ke peran ini:

CREATE ROLE CovidAnalyst;

ALTER ROLE CovidAnalyst ADD MEMBER [jovan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [milan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [petar@contoso.com];

Anda bisa menetapkan izin untuk semua pengguna yang termasuk dalam grup:

GRANT SELECT ON SCHEMA::ecdc_cosmosdb TO [CovidAnalyst];
GO
DENY SELECT ON SCHEMA::ecdc_adls TO [CovidAnalyst];
GO
DENY ADMINISTER DATABASE BULK OPERATIONS TO [CovidAnalyst];

Kontrol akses keamanan berbasis peran ini dapat menyederhanakan pengelolaan aturan keamanan Anda.

Langkah berikutnya

  • Last updated on