Posted at

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

More than 1 year has passed since last update.


目的

 現状 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 データソースを作成

image

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


SqlServer にリンクサーバ作成

image

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