AWS Redshift に SQL Server から DBLink 接続検証

10673 ワード
SQLServer redshift AWS AWS テキストリンク

目的

　現状 SQL Server を使用しているシステムが AWS Redshift 上のデータソースを参照するため、SQL Server から Redshift への DBLink 接続を行い、データ参照ができることを確認する。

検証結果

  • OpenQuery を使用すれば Select 可能
    • View ← OpenQuery ← DBLink ← Redshift という構成が現実的
  • DBLink 先のテーブルを直接、または SYNONYM 経由で Select することはできない
    • View 上に Redhisft から取得するカラムを定義する必要がある

検証手順

Redshift 構築

  • 手順の通り構築してみる。

  • 構築時のポイント

    • 予め Amazon Redshift に AmazonS3ReadOnlyAccess ロールを作成する
    • クラスタ作成の際に上記ロールを付与する
    • データロード（copy）時にも上記ロールの arn が必要

  • pgAdmin

    • デフォルトのDB名（dev）は接続した際にDB一覧に表示されない
    • データベースがないとクエリエディタが開けない
      • 新しいデータベースを作成 → クエリエディタ開く → 接続先を dev に切り替えた

SQL Server 構築

　RDS/SQL Server は Link Server 作成の権限がないので、EC2 上に構築する必要がある。

ODBC データソースを作成

　ポスグレの場合ですが、参考資料 SQL Server and PostgreSQL Linked Server Configuration - Part 2

SqlServer にリンクサーバ作成

EXEC master.dbo.sp_addlinkedserver 
    @server = N'REDSHIFT', --リンクサーバ 名
    @srvproduct=N'AWS Redshift', 
    @provider=N'MSDASQL', 
    @datasrc=N'MyRedshift' -- データソース
EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'REDSHIFT',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=NULL,
    @rmtpassword=NULL

SQL Server 上に OpenSql 経由のView を作成して Select できること確認

　上記 Redshift の Getting Started で Create, Copy するテーブルを例にする。

Create view [dbo].[viw_users]
(
    userid ,
    username ,
    firstname,
    lastname,
    city,
    state,
    email,
    phone ,
    likesports,
    liketheatre,
    likeconcerts,
    likejazz,
    likeclassical,
    likeopera,
    likerock,
    likevegas,
    likebroadway,
    likemusicals
) as select * from openquery ([REDSHIFT],'select * from [dev].[public].[users]')

select top 10 * from viw_users

問題： リンクサーバは直接 Select できない - (40470) Conversion error

select * from [REDSHIFT].[dev].[public].[users]
select * from [REDSHIFT].[dev].[public].[venue]
select * from [REDSHIFT].[dev].[public].[category]
select * from [REDSHIFT].[dev].[public].[date]
select * from [REDSHIFT].[dev].[public].[listing]
select * from [REDSHIFT].[dev].[public].[sales]

リンク サーバー "REDSHIFT" の OLE DB プロバイダー "MSDASQL" から、メッセージ "エラーを特定できません" が返されました。
リンク サーバー "REDSHIFT" の OLE DB プロバイダー "MSDASQL" から、メッセージ "[Amazon][Support] (40470) Conversion error at column 16 and row 1: Numeric value out of range." が返されました。
メッセージ 7311、レベル 16、状態 2、行 1
リンク サーバー "REDSHIFT" の OLE DB プロバイダー "MSDASQL" のスキーマ行セット "DBSCHEMA_COLUMNS" を取得できません。プロバイダーはインターフェイスをサポートしていますが、インターフェイス使用時にエラー コードが返されました。

※ シノニム経由でも変わらず

create synonym [dbo].[s_users] FOR [REDSHIFT].[dev].[public].[users]
select * from [s_users]

対応：OpenQuery 経由ならいける

select * from OPENQUERY([REDSHIFT],'select * from [dev].[public].[users]')
select * from OPENQUERY([REDSHIFT],'select * from [dev].[public].[venue]')
select * from OPENQUERY([REDSHIFT],'select * from [dev].[public].[category]')
select * from OPENQUERY([REDSHIFT],'select * from [dev].[public].[date]')
select * from OPENQUERY([REDSHIFT],'select * from [dev].[public].[listing]')
select * from OPENQUERY([REDSHIFT],'select * from [dev].[public].[sales]')

参考リンク

Amazon Redshiftで使えるPostgreSQL管理ツールを幾つか試してみた ｜ Developers.IO
【AWS】Amazon Redshift のクラスター作成と起動から接続までの方法（入門編） - TASK NOTES