取り込み用にSQL Serverを構成する

プレビュー

LakeFlow Connect はゲート付きパブリックプレビュー段階です。プレビューに参加するには、Databricks アカウントチームにお問い合わせください。

この記事では、取り込み用に SQL Server を構成する方法について説明します。これは、LakeFlow Connect を使用して SQL Server からデータを取り込み、Databricks にロードするための前提条件です。

変更追跡と変更データキャプチャ ( CDC )

、Databricks Microsoftからデータを抽出するための (CDC ) またはMicrosoft Change Tracking をサポートしています。SQL Serverテーブルに主キーがある場合、Databricks では最適なパフォーマンスを得るために変更追跡を使用することをお勧めします。主キーが存在しない場合は、CDC を使用する必要があります。これら 2 つのオプションの詳細については、ドキュメントの「データの変更の追跡 (SQL Server )」 SQL Serverを参照してください。

Microsoft チェンジデータキャプチャ (CDC) を使用するには、SQL Server 2017 以上が必要です。Microsoft 変更追跡を使用するには、SQL Server 2012 以降が必要です。

SQL Server ユーザーを作成する

Databricks では、Databricks の取り込み専用に使用するデータベースユーザーを作成することをお勧めします。

このデータベースユーザーには、ソースデータベースでのデータ変更を追跡するために採用されている方法に関係なく、次の権限が必要です。

次のシステムテーブルとビューへの読み取りアクセス:
- sys.databases
- sys.schemas
- sys.tables
- sys.columns
- sys.key_constraints
- sys.foreign_keys
- sys.check_constraints
- sys.default_constraints
- sys.change_tracking_tables
- sys.change_tracking_databases
- sys.objects
- sys.triggers
次のシステムストアドプロシージャに対する実行権限:
- sp_tables
- sp_columns
- sp_columns_100
- sp_pkeys
- sp_statistics
SELECT 取り込むスキーマとテーブル。

変更の追跡を有効にする

変更追跡を使用するには、取り込まれたすべてのデータベースとテーブルで変更追跡を有効にする必要があります。

注：

変更履歴は、プライマリキーを持つテーブルでのみ使用できます。

データベースでの変更追跡の有効化

次のコマンドを実行し、 <database-name> 、変更追跡を有効にするデータベースの名前に置き換えます。

ALTER DATABASE <database-name> SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)

テーブルで変更の追跡を有効にする

次のコマンドを実行し、 <schema-name>.<table-name>を、変更追跡を有効にするスキーマとテーブルの名前に置き換えます。

ALTER TABLE <schema-name>.<table-name> ENABLE CHANGE_TRACKING

追加の読み取り権限

取り込まれたテーブル、または追跡対象のテーブルを含むスキーマに対する VIEW CHANGE TRACKING 権限が必要です。

スキーマレベルの権限を付与するには、次のコマンドを実行します。

GRANT VIEW CHANGE TRACKING ON SCHEMA::<schema-name> TO <cdc-username>;

テーブルレベルの権限を付与するには、次のコマンドを実行します。

GRANT VIEW CHANGE TRACKING ON OBJECT::<schema-name>.<table-name> TO <cdc-username>;

DDLキャプチャとスキーマ進化の設定

コネクタには、取り込まれたデータベースオブジェクトのデータ定義言語 (DDL) を追跡し、関連するテーブルスキーマの変更を宛先テーブルに適用したり、完全なスキーマレプリケーションの場合は新しいテーブルを追加したりする機能があります。

DDL キャプチャを実行するには、追加のデータベースオブジェクト (内部テーブル、ストアドプロシージャ、トリガーなど) を設定する必要があります。設定には 2 つの方法があります。

自動: レプリケーション・ユーザーに追加の権限を付与する必要があります。
手動: データベースオブジェクトを手動で作成する必要があります。

オプション 1: DDL キャプチャの自動セットアップ

Databricks では、変更追跡を使用する場合、自動 DDL キャプチャのための追加の権限が必要です。次のコマンドを実行して、Databricks で使用するために構成されたユーザーにこれらの権限を付与します。

CREATE PROCEDURE データベース上で、次の操作を行います。
```
GRANT CREATE PROCEDURE TO <cdc-username>;
```
CREATE TABLE データベース上で、次の操作を行います。
```
GRANT CREATE TABLE TO <cdc-username>;
```

SELECT、 EXECUTE、およびスキーマの INSERT :

GRANT SELECT,INSERT,EXECUTE ON SCHEMA::dbo TO <cdc-username>;

ALTER データベース上で、次の操作を行います。

GRANT ALTER ON DATABASE::<database-name> TO <cdc-username>;

ALTER スキーマまたは取り込むすべてのテーブルで:

GRANT ALTER ON SCHEMA::[<schema-name>] TO [<cdc-username>];

オプション 2: DDL キャプチャの手動セットアップ

DDL キャプチャを手動で設定するには、次のコマンドを実行します。

CREATE TABLE "dbo"."replicate_io_audit_ddl_1"(

    "SERIAL_NUMBER" INT IDENTITY NOT NULL,

    "CURRENT_USER" NVARCHAR(128),

    "SCHEMA_NAME" NVARCHAR(128),

    "TABLE_NAME" NVARCHAR(128),

    "TYPE" NVARCHAR(30),

    "OPERATION_TYPE" NVARCHAR(30),

    "SQL_TXT" NVARCHAR(2000),

    "LOGICAL_POSITION" BIGINT,

    CONSTRAINT "replicate_io_audit_ddlPK" PRIMARY KEY("SERIAL_NUMBER","LOGICAL_POSITION")

)

ALTER TABLE dbo.replicate_io_audit_ddl_1 ENABLE CHANGE_TRACKING;

GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.replicate_io_audit_ddl_1 TO [CdcUserName];

CREATE TABLE dbo.replicate_io_audit_tbl_schema_1(

    "COLUMN_ID" BIGINT,

    "DATA_DEFAULT" BIGINT,

    "COLUMN_NAME" VARCHAR(128) NOT NULL,

         "TABLE_NAME" NVARCHAR(128) NOT NULL,

         "SCHEMA_NAME" NVARCHAR(128) NOT NULL,

         "HIDDEN_COLUMN" NVARCHAR(3),

         "DATA_TYPE" NVARCHAR(128),

         "DATA_LENGTH" BIGINT,

         "CHAR_LENGTH" BIGINT,

         "DATA_SCALE" BIGINT,

        "DATA_PRECISION" BIGINT,

        "IDENTITY_COLUMN" NVARCHAR(3),

    "VIRTUAL_COLUMN" NVARCHAR(3),

    "NULLABLE" NVARCHAR(1),

    "LOGICAL_POSITION" BIGINT);

CREATE TABLE dbo.replicate_io_audit_tbl_cons_1(

    "SCHEMA_NAME" VARCHAR(128),

    "TABLE_NAME" VARCHAR(128),

    "COLUMN_NAME" VARCHAR(4000),

    "COL_POSITION" BIGINT,

    "CONSTRAINT_NAME" VARCHAR(128),

    "CONSTRAINT_TYPE" VARCHAR(1),

    "LOGICAL_POSITION" BIGINT);

CREATE OR ALTER TRIGGER replicate_io_audit_ddl_trigger_1

    ON DATABASE

    AFTER ALTER_TABLE

    AS SET NOCOUNT ON;

       DECLARE @DbName nvarchar(255), @SchemaName nvarchar(max), @TableName nvarchar(255), @data XML, @operation NVARCHAR(30), @isCTEnabledDBLevel bit, @isCTEnabledTableLevel bit;

       SET @data = EVENTDATA();

       SET @DbName = DB_NAME();

       SET @SchemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(MAX)');

       SET @TableName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');

       SET @operation = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(30)');

       SET @isCTEnabledDBLevel = (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=DB_ID(@DbName));

       SET @isCTEnabledTableLevel = (SELECT COUNT(*) FROM sys.change_tracking_tables WHERE object_id = object_id(@SchemaName + '.' + @TableName));

IF (@isCTEnabledDBLevel = 1 AND @isCTEnabledTableLevel = 1)

BEGIN

   INSERT INTO

dbo.replicate_io_audit_ddl_1

       ("CURRENT_USER","SCHEMA_NAME", "TABLE_NAME", "TYPE", "OPERATION_TYPE", "SQL_TXT", "LOGICAL_POSITION")

       VALUES (SUSER_NAME(),

               @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'),

               @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(128)'),

               @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(30)'),

               @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(30)'),

               @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

                            'NVARCHAR(2000)'),

               CHANGE_TRACKING_CURRENT_VERSION() );

END

GRANT VIEW DEFINITION ON DATABASE::[YourDatabaseName] TO [CdcUserName];

ソースデータベースの組み込みCDC有効にする

CDC のソースデータベースを有効にするには、Azure SQL で次のストアドプロシージャを実行し、 <database-name>の値を置き換えます。 CDC を有効にするデータベースにログインする必要があります。

USE <database-name>
EXEC sys.sp_cdc_enable_db

Amazon RDS for SQL Server のデータベースで CDC を有効にするには、次のコマンドを実行します。

EXEC msdb.dbo.rds_cdc_enable_db '<database-name>'

詳細については、ドキュメントの「データベースの変更データキャプチャを有効にする」 SQL Serverを参照してください。

ソーステーブルで組み込みCDC有効にする

ソーステーブルで CDC を有効にするには、Azure SQL で次のストアドプロシージャを実行します。

EXEC sys.sp_cdc_enable_table
@source_schema = N'MySchema',
@source_name   = N'MyTable',
@role_name     = NULL,
@supports_net_changes = 1

source_schema、 source_name、 role_nameの値を置き換えます。
@support_net_changes テーブルにプライマリキーがある場合にのみ、 1 の値をサポートします。

詳細については、ドキュメントの「テーブルの変更データキャプチャを有効にする」 SQL Serverを参照してください。

追加の読み取り権限

CDC が有効な場合に作成される変更テーブルを含むスキーマcdcに対するSELECT権限が必要です。

GRANT SELECT ON SCHEMA::cdc to [cdc-username];

次のステップ

SQL Server からデータを取り込む