教學課程：使用無伺服器 SQL 集區建立邏輯資料倉儲

  • 發行項

在本教學課程中，您將瞭解如何在 Azure 儲存體和 Azure Cosmos DB 上建立邏輯資料倉儲 （LDW）。

LDW 是以 Azure Data Lake Storage （ADLS）、Azure Cosmos DB 分析儲存體或 Azure Blob 儲存體等 Azure 資料來源為基礎的關係層。

建立 LDW 資料庫

您必須建立自訂資料庫，以便儲存參考外部資料源的外部資料表和檢視表。

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

讀取 Parquet 和 Azure Cosmos DB 時，此定序可提供最佳效能。 如果您不想指定資料庫定序，請務必在資料行定義中指定此定序。

設定資料來源和格式

在第一個步驟中，您必須設定資料來源，並指定遠端儲存資料的檔案格式。

建立資料來源

資料來源代表連接字串資訊，說明資料的位置，以及如何向資料來源進行驗證。

下列範例顯示參考公用 ECDC COVID 19 Azure Open Data Set 的資料來源定義範例：

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

如果資料來源擁有者允許匿名存取或明確存取呼叫端的 Microsoft Entra 身分識別，呼叫端可能會存取資料來源，而不需要認證。

您可以明確定義自訂認證，以在存取外部資料源上的資料時使用。

在必要條件中，您必須在資料庫中建立主要金鑰：

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

在下列外部資料源中，Synapse SQL 集區應該使用工作區的受控識別來存取儲存體中的資料。

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

若要存取 Azure Cosmos DB 分析儲存體，您必須定義包含唯讀 Azure Cosmos DB 帳戶金鑰的認證。

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

任何具有 Synapse 管理員istrator 角色的使用者都可以使用這些認證來存取 Azure Data Lake 儲存體或 Azure Cosmos DB 分析儲存體。 如果您有沒有 Synapse 管理員istrator 角色的低許可權使用者，您必須授與他們參考這些資料庫範圍認證的明確許可權：

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

在 授與 DATABASE SCOPED CREDENTIAL 許可權 頁面中尋找更多詳細資料。

定義外部檔案格式

外部檔案格式會定義儲存在外部資料源上的檔案結構。 您可以定義 Parquet 和 CSV 外部檔案格式：

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

如需詳細資訊，請參閱 搭配 Synapse SQL 使用外部資料表和 CREATE EXTERNAL FILE FORMAT 來描述 CSV 或 Parquet 檔案的格式。

探索您的資料

設定資料來源之後，您可以使用 函 OPENROWSET 式來探索您的資料。 OPENROWSET 函 式會讀取遠端資料源的內容（例如檔案），並以一組資料列的形式傳回內容。

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

函 OPENROWSET 式會提供外部檔案或容器中資料行的相關資訊，並讓您定義外部資料表和檢視的架構。

在 Azure 儲存體上建立外部資料表

探索架構之後，您就可以在外部資料源之上建立外部資料表和檢視表。 最佳做法是在資料庫架構中組織資料表和檢視。 在下列查詢中，您可以建立架構，在其中放置存取 Azure data Lake Storage 中 ECDC COVID 資料集的所有物件：

create schema ecdc_adls;

資料庫架構對於群組物件和定義每個架構的許可權很有用。

定義架構之後，您可以建立參考檔案的外部資料表。 下列外部資料表參考放在 Azure 儲存體中的 ECDC COVID parquet 檔案：

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

請確定您使用字串和數位資料行的最小可能類型，以優化查詢的效能。

在 Azure Cosmos DB 上建立檢視

作為外部資料表的替代方案，您可以在外部資料上建立檢視。

類似于上一個範例所示的資料表，您應該將檢視放在不同的架構中：

create schema ecdc_cosmosdb;

現在，您可以在參考 Azure Cosmos DB 容器的架構中建立檢視：

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

若要將效能優化，您應該在架構定義中使用 WITH 最小的可能類型。

注意

您應該將 Azure Cosmos DB 帳戶金鑰放在個別的認證中，並從 函 OPENROWSET 式參考此認證。 請勿在檢視定義中保留您的帳戶金鑰。

存取權和許可權

最後一個步驟是，您應該建立應該能夠存取 LDW 的資料庫使用者，並授與他們從外部資料表和檢視中選取資料的許可權。 在下列腳本中，您可以看到如何新增將使用 Microsoft Entra 身分識別進行驗證的新使用者：

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

您可以建立使用登入名稱和密碼進行驗證的 SQL 主體，而不是 Microsoft Entra 主體。

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

在這兩種情況下，您可以指派許可權給使用者。

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

安全性規則取決於您的安全性原則。 一些泛型指導方針如下：

  • 您應該拒絕 ADMINISTER DATABASE BULK OPERATIONS 新使用者的許可權，因為它們應該只能使用您準備的外部資料表和檢視來讀取資料。
  • 您應該只提供 SELECT 某些使用者應該能夠使用的資料表許可權。
  • 如果您使用檢視來提供資料的存取權，則應該將許可權授 REFERENCES 與將用來存取外部資料源的認證。

此使用者具有查詢外部資料所需的最低許可權。 如果您想要建立可設定許可權、外部資料表和檢視的進階使用者，您可以授 CONTROL 與使用者許可權：

GRANT CONTROL TO [jovan@contoso.com]

角色型安全性

最好不要將許可權指派給個別用途，而是將使用者組織成角色，並在角色層級管理許可權。 下列程式碼範例會建立新的角色，代表可分析 COVID-19 案例的人員，並將三個使用者新增至此角色：

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

您可以將許可權指派給屬於群組的所有使用者：

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

此角色型安全性存取控制可能會簡化安全性規則的管理。

下一步