Öğretici: Sunucusuz SQL havuzu ile Mantıksal Veri Ambarı oluşturma

  • Makale

Bu öğreticide, Azure depolama ve Azure Cosmos DB üzerinde Mantıksal Veri Ambarı (LDW) oluşturmayı öğreneceksiniz.

LDW, Azure Data Lake storage (ADLS), Azure Cosmos DB analiz depolaması veya Azure Blob depolama gibi Azure veri kaynaklarının üzerine kurulmuş bir ilişkisel katmandır.

LDW veritabanı oluşturma

Dış veri kaynaklarına başvuran dış tablolarınızı ve görünümlerinizi depoladığınız özel bir veritabanı oluşturmanız gerekir.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Bu harmanlama, Parquet ve Azure Cosmos DB okurken en iyi performansı sağlar. Veritabanı harmanlamasını belirtmek istemiyorsanız, sütun tanımında bu harmanlamayı belirttiğinizden emin olun.

Veri kaynaklarını ve biçimlerini yapılandırma

İlk adım olarak, veri kaynağını yapılandırmanız ve uzaktan depolanan verilerin dosya biçimini belirtmeniz gerekir.

Veri kaynağı oluşturma

Veri kaynakları, verilerinizin nereye yerleştirildiğini ve veri kaynağınızda nasıl kimlik doğrulaması yapılacağını açıklayan bağlantı dizesi bilgileri temsil eder.

Genel ECDC COVID 19 Azure Açık Veri Kümesi'ne başvuran veri kaynağı tanımının bir örneği aşağıdaki örnekte gösterilmiştir:

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

Veri kaynağının sahibi anonim erişime izin veriyorsa veya çağıranın Microsoft Entra kimliğine açık erişim veriyorsa, arayan kişi kimlik bilgileri olmadan veri kaynağına erişebilir.

Dış veri kaynağındaki verilere erişirken kullanılacak özel bir kimlik bilgilerini açıkça tanımlayabilirsiniz.

Önkoşul olarak veritabanında bir ana anahtar oluşturmanız gerekir:

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

Aşağıdaki dış veri kaynağında Synapse SQL havuzu, depolamadaki verilere erişmek için çalışma alanının yönetilen kimliğini kullanmalıdır.

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 analiz depolama alanına erişmek için salt okunur bir Azure Cosmos DB hesap anahtarı içeren bir kimlik bilgisi tanımlamanız gerekir.

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

Synapse Yönetici istrator rolüne sahip tüm kullanıcılar bu kimlik bilgilerini kullanarak Azure Data Lake depolamaya veya Azure Cosmos DB analiz depolamasına erişebilir. Synapse Yönetici istrator rolüne sahip olmayan düşük ayrıcalıklı kullanıcılarınız varsa, bu veritabanı kapsamlı kimlik bilgilerine başvurmaları için onlara açık bir izin vermeniz gerekir:

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

DATABASE SCOPED CREDENTIAL izinleri verme sayfasında daha fazla ayrıntı bulabilirsiniz.

Dış dosya biçimlerini tanımlama

Dış dosya biçimleri, dış veri kaynağında depolanan dosyaların yapısını tanımlar. Parquet ve CSV dış dosya biçimlerini tanımlayabilirsiniz:

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

Daha fazla bilgi için bkz . CSV veya Parquet dosyalarının biçimini açıklamak için Synapse SQL ve CREATE EXTERNAL FILE FORMAT ile dış tabloları kullanma.

Verilerinizi keşfetme

Veri kaynaklarınızı ayarladıktan sonra, verilerinizi keşfetmek için işlevini kullanabilirsiniz OPENROWSET . OPENROWSET işlevi, uzak veri kaynağının (örneğin dosya) içeriğini okur ve içeriği bir satır kümesi olarak döndürür.

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

işlevi size OPENROWSET dış dosya veya kapsayıcılardaki sütunlar hakkında bilgi verir ve dış tablolarınızın ve görünümlerinizin şemasını tanımlamanızı sağlar.

Azure depolamada dış tablolar oluşturma

Şemayı keşfettikten sonra, dış veri kaynaklarınızın üzerinde dış tablolar ve görünümler oluşturabilirsiniz. İyi bir uygulama, veritabanları şemalarındaki tablolarınızı ve görünümlerinizi düzenlemektir. Aşağıdaki sorguda, ECDC COVID veri kümesine erişen tüm nesneleri Azure data Lake storage'a yerleştireceğiniz bir şema oluşturabilirsiniz:

create schema ecdc_adls;

Veritabanı şemaları, nesneleri gruplandırma ve şema başına izinleri tanımlama için kullanışlıdır.

Şemaları tanımladıktan sonra, dosyalara başvuran dış tablolar oluşturabilirsiniz. Aşağıdaki dış tablo, Azure depolama alanına yerleştirilen ECDC COVID parquet dosyasına başvuruyor:

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

Sorgularınızın performansını iyileştirmek için dize ve sayı sütunları için mümkün olan en küçük türleri kullandığınızdan emin olun.

Azure Cosmos DB'de görünüm oluşturma

Dış tablolara alternatif olarak, dış verilerinizin üzerinde görünümler oluşturabilirsiniz.

Önceki örnekte gösterilen tablolara benzer şekilde, görünümleri ayrı şemalara yerleştirmelisiniz:

create schema ecdc_cosmosdb;

Artık şemada Azure Cosmos DB kapsayıcısına başvuran bir görünüm oluşturabilirsiniz:

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

Performansı iyileştirmek için şema tanımında mümkün olan en küçük türleri WITH kullanmanız gerekir.

Dekont

Azure Cosmos DB hesap anahtarınızı ayrı bir kimlik bilgilerine yerleştirmeniz ve işlevden bu kimlik bilgilerine OPENROWSET başvurmanız gerekir. Hesap anahtarınızı görünüm tanımında tutmayın.

Erişim ve izinler

Son adım olarak, LDW'nize erişebilmesi gereken veritabanı kullanıcıları oluşturmalı ve onlara dış tablolardan ve görünümlerden veri seçme izinleri vermelisiniz. Aşağıdaki betikte, Microsoft Entra kimliği kullanılarak kimliği doğrulanacak yeni bir kullanıcının nasıl ekleneceğini görebilirsiniz:

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

Microsoft Entra sorumluları yerine, oturum açma adı ve parolayla kimlik doğrulaması sağlayan SQL sorumluları oluşturabilirsiniz.

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

Her iki durumda da kullanıcılara izin atayabilirsiniz.

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

Güvenlik kuralları, güvenlik ilkelerinize bağlıdır. Bazı genel yönergeler şunlardır:

  • Yalnızca sizin hazırladığınız dış tabloları ve görünümleri kullanarak verileri okuyabilmeleri gerektiğinden, yeni kullanıcılar için izinleri reddetmeniz ADMINISTER DATABASE BULK OPERATIONS gerekir.
  • Yalnızca bazı kullanıcıların kullanabilmesi gereken tablolara izin vermelisiniz SELECT .
  • Görünümleri kullanarak verilere erişim sağlıyorsanız, dış veri kaynağına erişmek için kullanılacak kimlik bilgilerine izin vermelisiniz REFERENCES .

Bu kullanıcının dış verileri sorgulamak için gereken en düşük izinleri var. İzinleri, dış tabloları ve görünümleri ayarlayabilen bir power-user oluşturmak istiyorsanız, kullanıcıya izin verebilirsiniz CONTROL :

GRANT CONTROL TO [jovan@contoso.com]

Rol tabanlı güvenlik

Bireysel kullanımlara izin atamak yerine, kullanıcıları roller halinde düzenlemek ve izinleri rol düzeyinde yönetmek için iyi bir uygulamadır. Aşağıdaki kod örneği, COVID-19 vakalarını analiz eden kişileri temsil eden yeni bir rol oluşturur ve bu role üç kullanıcı ekler:

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

İzinleri gruba ait tüm kullanıcılara atayabilirsiniz:

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

Bu rol tabanlı güvenlik erişim denetimi, güvenlik kurallarınızın yönetimini basitleştirebilir.

Sonraki adımlar