各製品の資料を入手。
詳細はこちら →
製品をチェック
SQL Server からAmazon Athena へストアドプロシージャで自動連携：SQL Gateway
SQL Server のCDC 機能とストアドプロシージャを使って、リンクサーバー経由でAmazon Athena のデータを定期的に更新する方法
最終更新日：2022-03-22
こんにちは！ドライバー周りのヘルプドキュメントを担当している古川です。
シナリオ
SQL Serverにある既存テーブルの更新された値を、Amazon Athena に自動的に取り込んでいきます。構成は、Amazon Athena とSQL Server の間にSQL Gateway を配置し、SQL Gateway からデータソースへのアクセスはHTTP リクエスト、SQL Gateway からクライアントへはTDS プロトコル（SQL Server）でアクセスできるようにします。
SQL Gateway を使うことで、SQLServer のリンクサーバー経由やMySQL へ接続できるツール、サービスなどからもSaaS にアクセスすることが可能になります。もちろん参照だけでなく更新も可能です。
手順
やることは大枠でこちらの内容です。
- CData ODBC ドライバインストール & DSN 設定
- SQL Gateway TDS プロトコルI/F を作成
- SQL Server リンクサーバーを作成
- SQL Server CDC 機能をオンにする
- SQL Server 更新用ストアドプロシージャの作成
- SQL Server エージェントでスケジュール設定
CData ODBC ドライバとは？
CData ODBC ドライバは、以下のような特徴を持ったリアルタイムデータ連携ソリューションです。
- Amazon Athena をはじめとする、CRM、MA、会計ツールなど多様なカテゴリの270種類以上のSaaS / オンプレミスデータソースに対応
- 多様なアプリケーション、ツールにAmazon Athena のデータを連携
- ノーコードでの手軽な接続設定
- 標準 SQL での柔軟なデータ読み込み・書き込み
CData ODBC ドライバでは、1.データソースとしてAmazon Athena の接続を設定、2.SQL Server 側でODBC Driver との接続を設定、という2つのステップだけでデータソースに接続できます。以下に具体的な設定手順を説明します。
CData ODBC ドライバのインストールとAmazon Athena への接続設定
まずは、本記事右側のサイドバーからAmazonAthena ODBC Driver の無償トライアルをダウンロード・インストールしてください。30日間無償で、製品版の全機能が使用できます。
CData ODBC ドライバをインストールすると SQL Gateway もあわせてインストールされるようになっています。ではこちらのリンクからAmazon Athena をクリックして、インストーラーをダウンロードします。ダウンロードボタンをクリックしてAmazon Athena
ODBC Driver
をダウンロードします。
ダウンロードしたAmazon AthenaODBCDriver.exe をダブルクリックしてインストールを開始します。
インストール自体はデフォルトのまま進んで構いません。インストール後にDSN 設定画面が開きます。
- 接続プロパティの指定がまだの場合は、DSN （データソース名）で行います。Microsoft ODBC データソースアドミニストレーターを使ってODBC DSN
を作成および設定できます。一般的な接続プロパティは以下のとおりです：
- AccessKey
- SecretKey
- Region
- Database
- S3StagingDirectory
Microsoft ODBC データソースアドミニストレーターで必要なプロパティを設定する方法は、ヘルプドキュメントの「はじめに」をご参照ください。
Amazon Athena 接続プロパティの取得・設定方法
Amazon Athena リクエストの認証には、アカウントの管理のクレデンシャルか、IAM ユーザーのカスタムPermission を設定します。 AccessKey にAccess Key Id、SecretKey にはSecret Access Key を設定します。
AWS アカウントアドミニストレータとしてアクセスできる場合でも、AWS サービスへの接続にはIAM ユーザークレデンシャルを使用することが推奨されます。
IAM ユーザーのクレデンシャル取得は以下のとおり：
- IAM コンソールにログイン。
- Navigation ペインで「ユーザー」を選択。
- ユーザーのアクセスキーを作成または管理するには、ユーザーを選択してから「セキュリティ認証情報」タブを選択。
AWS ルートアカウントのクレデンシャル取得は以下のとおり：
- ルートアカウントの資格情報を使用してAWS 管理コンソールにサインイン。
- アカウント名または番号を選択し、表示されたメニューで「My Security Credentials」を選択。
- 「Continue to Security Credentials」をクリックし、「Access Keys」セクションを展開して、ルートアカウントのアクセスキーを管理または作成。
EC2 インスタンスからの認証
EC2 インスタンスからCData 製品を使用していて、そのインスタンスにIAM ロールが割り当てられている場合は、認証にIAM ロールを使用できます。 これを行うには、UseEC2Roles をtrue に設定しAccessKey とSecretKey を空のままにします。 CData 製品は自動的にIAM ロールの認証情報を取得し、それらを使って認証します。
AWS ロールとして認証
多くの場合、認証にはAWS ルートユーザーのダイレクトなセキュリティ認証情報ではなく、IAM ロールを使用することをお勧めします。 代わりにRoleARN を指定してAWS ロールを使用できます。これにより、CData 製品は指定されたロールの資格情報を取得しようと試みます。 （すでにEC2 インスタンスなどで接続されているのではなく）AWS に接続している場合は、役割を担うIAM ユーザーのAccessKeyと SecretKey を追加で指定する必要があります。AWS ルートユーザーのAccessKey およびSecretKey を指定する場合、 ロールは使用できません。
MFA での認証
多要素認証を必要とするユーザーおよびロールには、MFASerialNumber およびMFAToken 接続プロパティを指定してください。 これにより、CData 製品は一時的な認証資格情報を取得するために、リクエストでMFA 認証情報を送信します。一時的な認証情報の有効期間 （デフォルトは3600秒）は、TemporaryTokenDuration プロパティを介して制御できます。
Amazon Athena への接続
AccessKey とSecretKey プロパティに加え、Database、S3StagingDirectory、Region を設定します。Region をAmazon Athena データがホストされているリージョンに設定します。S3StagingDirectory をクエリの結果を格納したいS3内のフォルダに設定します。
接続にDatabase が設定されていない場合は、CData 製品はAmazon Athena に設定されているデフォルトデータベースに接続します。
ここまでで、CData Amazon Athena ODBC Driver の設定が完了しました。続いてはSQL Gateway の設定に入っていきます。
SQL Gateway でTDS プロトコルI/F を作成
Windows メニューにある SQL Gateway クリックして起動します。
SQL Gateway が起動されたらこのようなコンソール画面が表示されます。最初に「サービス」タブから追加ボタンをクリックします。
以下の内容で設定します。
- サービス名：任意
- I/F：サービス名下のラジオボタンでTDS（SQL Server）を選択
- データソース：先ほど設定したDSN
- ポート：未使用のもの
外部からこの SQL Gateway にアクセスする際のユーザー情報を作成します。
作成後は「変更を保存」→「開始」の順でボタンをクリックします。サービスが正常に開始されると、このように緑色のランプが点灯します。
これで データソースからSQL Gateway 間の接続設定と、TDS プロトコルでのインタフェース設定が完了しました。
リンクサーバーの作成
次にSQL Server とSQL Gateway をつなぐために、リンクサーバーを作成していきます。
SSMS で対象の SQL Server に接続し、「サーバーオブジェクト」->「リンクサーバー」から右クリックでリンクサーバーの新規作成を行います。
- リンクサーバー名：任意
- サーバーの種類：その他
- プロバイダー：SQL Server Native Client
- データソース：SQL Gateway を起動しているインスタンスとポート
「インスタンス,ポート」の書き方。 - カタログ：SQL Gateway で指定したDSN
セキュリティに移動して、SQL Gateway
で作成したユーザー情報を入力して設定は完了です。
これでAmazon Athena のオブジェクトがリンクサーバーにテーブルとして表示されるようになりました。
SQL Server の設定（CDC）
まずは CDC（Change Data Capture）
機能をオンにしていきます。ちなみにこの CDC
とは、各テーブルのデータの変更情報を保持してくれる機能です。
※CDC はStandard Edition 以上、もしくはDeveloper Edition
で利用可能です。
今回はこのCDC 機能を使って変更データを抽出し、Amazon Athena に連携していきます。
CDC 機能を利用するデータベースを指定して以下のSQL を実行します。
USE cdata;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
CDC の対象とするテーブルを設定します。
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'顧客データ_東北支店',
@role_name = N'cdc_role',
@supports_net_changes = 1
GO
各種パラメータの説明はMicrosoft の公式ドキュメントをご参照ください。上記以外にも設定できるようです。
これで対象 DB
のシステムテーブルに、変更情報を保持してくれるテーブルが表示されました。
何も変更していない状態では、dbo_顧客データ_東北支店_CT
テーブルのレコードはない状態です。
CDC 対象の顧客データ_東北支店
テーブルにあるメールアドレス列の値を変更してみます。
もう一度、dbo_顧客データ_東北支店_CT テーブルを参照すると変更前後のレコードが格納されました。ちなみに$operation=3 で更新前、4で更新後となります。
これで SQL Server で変更したデータを確認することが出来るようになりました！
ストアドプロシージャの作成
シンプルに CDC テーブルの Name という項目だけを使用して、それをリンクサーバーの [Amazon Athena].[顧客管理（営業支援パック）] テーブルに Insert、Delete、Update で連携するということをやってみます。
CREATE PROCEDURE Amazon AthenaSP
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
--カーソルの値を取得する変数宣言
DECLARE @Id varchar(50)
DECLARE @email varchar(50)
DECLARE @Operation int
--カーソル定義
DECLARE CUR_1 CURSOR FOR
SELECT a.[RecordId], a.[メールアドレス],a.[__$operation]
FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT] AS a,
(SELECT[RecordId] , MAX([__$seqval]) AS seqval
FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT]
GROUP BY [RecordId]) AS b
WHERE a.[RecordId] = b.[RecordId]
AND a.[__$seqval] = b.seqval
--'3'は更新前レコード
AND a.[__$operation] <> '3'
--カーソルオープン
OPEN CUR_1;
FETCH NEXT FROM CUR_1
INTO @Id,@email,@Operation;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 削除レコードの場合
IF @Operation = 1
BEGIN
DELETE FROM [AMAZON ATHENA_GATEWAY].[CData Amazon Athena Sys].[Amazon Athena].[顧客管理（営業支援パック）] WHERE
RecordId=@Id;
END
-- 新規レコードの場合
ELSE IF @Operation = 2
BEGIN
INSERT INTO [AMAZON ATHENA_GATEWAY].[CData Amazon Athena Sys].[Amazon Athena].[顧客管理（営業支援パック）]
([メールアドレス])
Values(@email);
END
-- 更新レコードの場合
ELSE IF @Operation = 4
BEGIN
UPDATE [AMAZON ATHENA_GATEWAY].[CData Amazon Athena Sys].[Amazon Athena].[顧客管理（営業支援パック）] SET
[メールアドレス] =
@email
WHERE
RecordId=@Id;
END
--次のレコード
FETCH NEXT FROM CUR_1
INTO @Id,@email,@Operation;
END
--カーソルクローズ
CLOSE CUR_1;
DEALLOCATE CUR_1;
--CDC変更テーブルのレコード削除
Truncate table [cdc].[dbo_顧客データ_東北支店_CT]
END
スケジュール設定
最後にSQL Server
エージェントでストアドプロシージャのスケジュール実行を設定します。
ステップで新規作成から、先ほど作成したストアドプロシージャを実行するよう設定します。
実行タイミングを設定します。今回は夜中に毎日実行されるようスケジューリングしました。
設定後はこのように次にジョブが実行される時間やステータスなどが表示されています。
これで設定が完了しました！
SQL Server のデータを更新
以下は変更テーブルの中身です。赤枠内のレコードが
リンクサーバーを介してAmazon Athena に反映される想定です。
赤枠レコードの内訳は、
- 1行目：削除レコード（__$operation:1）
- 2行目：更新前レコード（__$operation:3）
- 3行目：更新後レコード（__$operation:4）
- 4行目：追加レコード （__$operation:2）
ではこれでAM 3:00に実行されるのを待ってみると、ジョブが実行されました。
リンクサーバーの[Amazon Athena].[顧客管理（営業支援パック）]
テーブルを参照すると、先ほど確認したレコードが削除も含めて反映されていました。
これであとはこのまま起動しておくだけで、SQL Server で更新したレコードをもとにAmazon Athena が更新されるようになりました。
おわりに
いかがでしたでしょうか。SQL Server の CDC 機能とストアドプロシージャ、リンクサーバーでAmazon Athena に自動連携できるようになりました。
CData ODBC Driver for AmazonAthena は 30日間の無償評価版がご利用可能です。こちらから をぜひお試しください。
CData ODBC ドライバは日本のユーザー向けに、UI の日本語化、ドキュメントの日本語化、日本語でのテクニカルサポートを提供しています。
関連コンテンツ
