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

發行項
11/09/2024

在本教學課程中，您將瞭解如何在 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 身分識別，呼叫端可能會存取數據源，而不需要認證。

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

Synapse 工作區的受控識別
Azure 記憶體的共用存取簽章
自訂服務主體名稱或 Azure 應用程式身分識別。
只讀的 Azure Cosmos DB 帳戶密鑰，可讓您讀取 Azure Cosmos DB 分析記憶體。

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

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 系統管理員角色的任何使用者都可以使用這些認證來存取 Azure Data Lake 記憶體或 Azure Cosmos DB 分析記憶體。如果您有沒有 Synapse 系統管理員角色的低許可權使用者，您必須授與他們明確許可權，才能參考這些資料庫範圍認證：

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的資料庫使用者，並授與他們從外部數據表和檢視中選取數據的許可權。在下列腳本中，您可以瞭解如何新增將使用 Entra 身分識別 Microsoft進行驗證的新使用者：

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

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

下一步

若要瞭解如何將無伺服器 SQL 集區連線到 Power BI Desktop 並建立報表，請參閱將無伺服器 SQL 集區連線到 Power BI Desktop 並建立報表。
若要瞭解如何在無伺服器 SQL 集區中使用外部數據表，請參閱搭配 Synapse SQL 使用外部數據表

共用方式為