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

# <i class="fa fa-map-marker" /> 目的
　現状 SQL Server を使用しているシステムが AWS Redshift 上のデータソースを参照するため、SQL Server から Redshift への DBLink 接続を行い、データ参照ができることを確認する。

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

# <i class="fa fa-location-arrow" /> 検証手順
## Redshift 構築
- 手順の通り構築してみる。
    - [Getting Started with Amazon Redshift - Amazon Redshift](http://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html)
    - クライアントには [pgAdmin 3](https://www.pgadmin.org/download/windows.php) を導入。

- 構築時のポイント
    -  予め 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)](https://www.microsoft.com/ja-jp/download/details.aspx?id=22985)
        - ローカル DB 作成（SQLExpress）しておく
    - Redshift ODBC Driver をインストールしておく
        - [ODBC 接続を設定する - Amazon Redshift](http://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/configure-odbc-connection.html)

### ODBC データソースを作成
![image](https://qiita-image-store.s3.amazonaws.com/0/130874/799869e2-49af-8f2b-ed52-9da1d30196e2.png)

　ポスグレの場合ですが、参考資料 [SQL Server and PostgreSQL Linked Server Configuration - Part 2](https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration--part-2/)

### SqlServer にリンクサーバ作成
![image](https://qiita-image-store.s3.amazonaws.com/0/130874/1c8ec020-87e5-d9b7-2df1-1cafd897fc8a.png)


```sql
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 するテーブルを例にする。

```sql
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
```sql
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]')
```

<!--
# postgre odbc driver を使った場合
## 問題1：Nullable なカラムを含むテーブルはエラー（7356）が起きる

```sql
select * from [REDSHIFT].[dev].[public].[users]

メッセージ 7356、レベル 16、状態 1、行 1
リンク サーバー "REDSHIFT" の OLE DB プロバイダー "MSDASQL" により、一貫性のないメタデータが列に指定されました。オブジェクト ""dev"."public"."users"" の列 "likesports" (コンパイル時の序数 9) に、コンパイル時には 128 の "DBCOLUMNFLAGS_ISLONG" が含まれ、実行時には 0 が含まれていることがレポートされました。
```
[PRB：MSDAORAでの分散クエリを返すエラー7356](https://support.microsoft.com/ja/help/251238)

### 解決策 OpenQuery
```sql
select * from openquery ([REDSHIFT],'select * from [dev].[public].[users]')
```

## 問題2： SQL Server (SQLEXPRESS)サービスが落ちる
　特定のテーブルで起きるわけではないようです。謎。

```sql
select * from [REDSHIFT].[dev].[public].[event]

メッセージ 109、レベル 20、状態 0、行 0
サーバーから結果を受信しているときに、トランスポート レベルのエラーが発生しました。 (provider: 共有メモリ プロバイダ, error: 0 - パイプは終了しました。)
```
　サービスを再度起動する必要があります。

### 解決策 OpenQuery
（略）
-->


# <i class="fa fa-external-link" /> 参考リンク
[Amazon Redshiftで使えるPostgreSQL管理ツールを幾つか試してみた ｜ Developers.IO](http://dev.classmethod.jp/cloud/aws/amazon-redshift-client-tools/)
[【AWS】Amazon Redshift のクラスター作成と起動から接続までの方法（入門編） - TASK NOTES](http://www.task-notes.com/entry/20150807/1438916400)