目的

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

検証結果

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

検証手順

Redshift 構築

手順の通り構築してみる。
- Getting Started with Amazon Redshift - Amazon Redshift
- クライアントには pgAdmin 3 を導入。
構築時のポイント
- 予め Amazon Redshift に AmazonS3ReadOnlyAccess ロールを作成する
- クラスタ作成の際に上記ロールを付与する
- データロード（copy）時にも上記ロールの arn が必要
pgAdmin
- デフォルトのDB名（dev）は接続した際にDB一覧に表示されない
- データベースがないとクエリエディタが開けない
  - 新しいデータベースを作成 → クエリエディタ開く → 接続先を dev に切り替えた

SQL Server 構築

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

構成
- Windows Server 2012 R2 Standard
- Microsoft SQL Server Express Edition (64-bit)
  - ローカル DB 作成（SQLExpress）しておく
- Redshift ODBC Driver をインストールしておく
  - ODBC 接続を設定する - Amazon Redshift

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

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

目的