SQL Server 2019 PolyBaseでTwitterのタイムラインに流れたツイートの一覧を取得してみる

# はじめに

[SQL Server 2019][SQLServer2019] が 2019/11/4 に GA \(General Availability\) されました！！
今回は [JSSUG #23][JSSUG #23] で説明があった SQL Server 2019 PolyBase クエリエンジンを使用した Twitter のハッシュタグツイート取得を実践してみます。


# SQL Server PolyBase

## SQL Server PolyBase の歴史

[SQL Server 2016][SQLServer2016の新機能] から PolyBase クエリエンジンがサポートされました。当初は Hadoop や Azure Blob Storage、Azure Data Lake Store 内の外部データと SQL Server を統合する機能として提供されました。
[SQL Server 2019][SQLServer2019] では、これらに加えて

- **リレーショナルデータベース**: SQL Server、Oracle、MySQL
- **NoSQL**: MongoDB、Azure Cosmos DB
- **Big Data**: Apache Spark
- **ODBC データソース**: Excel、IBM DB2   etc.

のコネクターが提供されるようになり、PolyBase クエリエンジン経由でアクセスできる外部データが増えました。UTF-8 にも対応できるようになっています。
これによって、ETL ツールなどを使って無理にデータを統合したりせずに、T-SQL 言語を使用して構造化されたデータと非構造化されているデータの連携を簡単に行うことができるようになりました。


# Windows への SQL Server PolyBase インストール

それでは、SQL Server PolyBase をインストールしていきます。

## 前提条件

SQL Server Polybase を動作させるための前提条件です。

```
・ 64bit 版 SQL Server ※32bit では ODBC ドライバーが対応していません
・ Microsoft .NET Framework 4.5
・ メモリ 4GB 以上
・ ディスク容量 2GB 以上
・ 16GB 以上の RAM ※推奨
```

## 使用環境

今回使用した OS 環境です。

```:systeminfo
OS 名:                  Microsoft Windows 10 Pro
OS バージョン:          10.0.18362 N/A ビルド 18362
OS 製造元:              Microsoft Corporation
OS 構成:                スタンドアロン ワークステーション
OS ビルドの種類:        Multiprocessor Free
システム モデル:        Surface Laptop 2
システムの種類:         x64-based PC
プロセッサ:             1 プロセッサインストール済みです。
                        [01]: Intel64 Family 6 Model 142 Stepping 10 GenuineIntel ~1600 Mhz
BIOS バージョン:        Microsoft Corporation 137.2706.768, 2019/04/18
Windows ディレクトリ:   C:\WINDOWS
システム ディレクトリ:  C:\WINDOWS\system32
起動デバイス:           \Device\HarddiskVolume1
システム ロケール:      ja;日本語
入力ロケール:           ja;日本語
タイム ゾーン:          (UTC+09:00) 大阪、札幌、東京
物理メモリの合計:       8,117 MB
```

SQL Server については、SQL Server 2019 RC1 Developer Edition をインストールしています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/1cef6d4d-e001-172f-6cbe-04d11cd8f7e9.png)

## TCP/IP の有効化

SQL Server PolyBase では、TCP/IP のネットワークプロトコルを有効化しておく必要があります。Express Edition や Developer Edition では、このプロトコルが初期状態では無効化されているため、有効化するよう設定を変更します。

\[SQL Server 2019 RC1 構成マネージャー\] を起動します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/476e2c6e-5713-9dd6-74f6-547b281a1a5d.png)

\[SQL Server ネットワークの構成\] から \[<対象の SQL Server インスタンス名> のプロトコル\] を選択し、\[TCP/IP\] の状態を有効化します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/69c79588-8511-8adf-42d8-d0736444267f.png)

有効化後は、SQL Server インスタンスの再起動が必要になるため、SQL Server サービスを再起動します。


## シングルノードまたは PolyBase スケールアウトグループの選択

PolyBase をインストールする前に、シングルノードインストールを行うか、[PolyBase スケールアウト グループ][PolyBaseスケールアウトグループ] が必要なのかを選択します。
今回はデモのため、シングルノードインストールで進めます。
※実運用環境では、処理負荷分散やパフォーマンスの観点から、[PolyBase スケールアウト グループ][PolyBaseスケールアウトグループ] を選択することを推奨します。


## インストールウィザードの実行(GUI)

SQL Server の setup.exe を実行し、PolyBase クエリエンジンをインストールします。
SQL Server インストールセンターを起動し、\[インストール\] -> \[SQL Server の新規スタンドアロン インストールを実行するか、既存のインストールに機能を追加\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/f617c458-cf57-7919-9cd4-8fbeb798fbe8.png)

\[機能の選択\] の画面にて、\[外部データ用の PolyBase クエリサービス\]、および \[HDFS データソースの Java コネクタ\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/469c48bc-fa14-cb29-03fa-380675954de3.png)

\[PolyBase の構成\] の画面にて、\[この SQL Server をスタンドアロンの PolyBase 対応インスタンスとして使います\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/c5841748-8597-c1bc-e3da-b4e2917ca9f0.png)

\[Java のインストール場所\] の画面にて、\[このインストールに含まれている Open JRE 11.0.3 をインストールします\] を選択します。
※SQL Server 2019 からは、言語拡張機能として Java が新たにサポートされるようになり、Azure Zulu Embedded が同梱されるようになりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/eff6f262-0a18-2411-219e-036cab1e02ce.png)

こちらでインストールを実行します。

## インストールウィザードの実行(CUI)

パラメータの詳細は、[こちら](https://docs.microsoft.com/ja-jp/sql/relational-databases/polybase/polybase-installation?view=sql-server-ver15#installing) を参照してください。

```powershell:スケールアウトしない場合
Setup.exe /Q /ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine,Polybase /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="\<fablic-domain>\Administrator" /INSTANCEDIR="C:\Program Files\Microsoft SQL Server" /PBSCALEOUT=FALSE /SECURITYMODE=SQL /SAPWD="<strong_your_password>"
```

```powershell:スケールアウトする場合
Setup.exe /Q /ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine,Polybase /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="\<fablic-domain>\Administrator" /INSTANCEDIR="C:\Program Files\Microsoft SQL Server" /PBSCALEOUT=TRUE /PBPORTRANGE=16450-16460 /SECURITYMODE=SQL /SAPWD="<strong__password>" /PBENGSVCACCOUNT="<DomainName>\<UserName>" /PBENGSVCPASSWORD="<strong_password>" /PBDMSSVCACCOUNT="<DomainName>\<UserName>" /PBDMSSVCPASSWORD="<strong_password>"
```

## PolyBase 有効化

PolyBase のインストールに成功したら、PolyBase の機能を有効化します。
対象の SQL Server に sysadmin 権限でログインします。
ログイン後、SQL Server インスタンス内に、以下の PolyBase 用ユーザーデータベースが存在していることを確認します。

- DWConfiguration
- DWDiagnostic
- DWQueue

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/2c32c44e-4b57-16c4-4ba8-9b704ebd0408.png)

上記のデータベースは PolyBase で使用されるので、絶対に変更や削除は行わないでください。

データベースの存在を確認出来たら、以下の SQL を \[master\] データベースに対して実行し、PolyBase がインストールされたことを確認します。

```sql
SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;
GO

IsPolyBaseInstalled
-------------------------
1
```

PolyBase が正常にインストールされたことを確認できたら、以下の SQL を実行して PolyBase を有効化します。

```sql
exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;
GO
```

## サービスの再起動

[PolyBase 有効化](#polybase-有効化) を行ったため、以下の Windows サービスを再起動します。

- SQL Server (対象インスタンス名)

また、PolyBase 関連の Windows サービスが起動していることを確認します。起動していない場合は起動します。

- SQL Server PolyBase DataMovement (対象インスタンス名)
- SQL Server PolyBase エンジン (対象インスタンス名)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/bd55c1d2-9be7-7e37-e01a-e5e241f863e3.png)


## PolyBase 用 ファイアウォール規則の有効化

SQL Server PolyBase をインストールすると、コンピュータに下記の PolyBase 用ファイアウォール規則が作成されます。

- SQL Server PolyBase - Database Engine - <SQLServerInstanceName> (TCP-In)
- SQL Server PolyBase - PolyBase Services - <SQLServerInstanceName> (TCP-In)
- SQL Server PolyBase - SQL Browser - (UDP-In) 

\[コントロール パネル\] -> \[システム セキュリティ\] -> \[Windows ファイアウォール\] を選択します。
\[詳細設定\] を選択し、\[セキュリティが強化された Windows Defender ファイアウォール\] を開きます。
\[受信の規則\] を選択し、上記３つの規則を有効化します。
※上記は、PolyBase スケールアウトグループを選択した際に必須となる作業です。
　スタンドアロンでインストールした際は、設定不要です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/abc413ad-45bf-1e0e-91da-f6958565e7fa.png)


# ODBC ドライバーのインストール

## Twitter ODBC Driver のダウンロード

[CDATA社][Twitter_ODBC_Driver] のサイトから、無料評価版の Twitter ODBC Driver をダウンロードします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/13f3e6a0-33aa-1ab2-a920-996adbbfe1ee.png)

ダウンロードには、メールアドレスのみ必須の入力が求められます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/244f6ef9-9f7d-1884-e463-02f6ecd2ab57.png)

## Twitter ODBC Driver のインストール

ダウンロードした TwitterODBCDriver.exe を起動します。
\[次へ\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/21162861-ca59-1db8-4640-862035ba1641.png)

\[同意する\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/b9eafe9c-fef5-224f-a278-34eb30657028.png)

インストール先のフォルダを入力し、\[次へ\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/81d71894-6f72-2f04-9998-2dc7504a343c.png)

今回は、PolyBase の要件となる 64 bit の ODBC ドライバーがインストールできれば良いので、\[64ビットODBCドライバ(x64)\] のみチェックを入れ、\[次へ\] を選択します。
※SQL Server 2019 は、32 bit には対応しておらず、64 bit 版のみリリースとなります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/5aa227bb-bcd5-14ab-e5b6-8f6af90655f6.png)

\[次へ\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/809d4429-9006-b32b-0346-e36fbee0854f.png)

\[次へ\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/95d80faf-cc25-39f7-ca6b-f62ca930fd72.png)

\[インストール\] を選択し、インストールを開始します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/592e2e6f-0432-a231-5929-fdfe25c88556.png)

インストールが終了するまで待機します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/e9e7f01d-198d-7dbd-29ad-497b2f58001b.png)

インストールが完了したら、\[完了\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/656d8863-4c6c-45e5-78be-76dbbd0812fd.png)

## ODBC ドライバーのテスト

\[Windows 管理ツール\] -> \[ODBC データソース (64 ビット)\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/e54bad2e-06cc-5f01-8f34-a2ee34518cd5.png)

\[システム DSN\] のタブを選択します。
インストールした Twitter ODBC Driver を選択し、\[構成\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/622fd62d-7202-55e4-c448-764e4688dd59.png)

\[接続のテスト\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/412cfcad-498c-ddd3-ba01-d063342aa7e5.png)

Twitter 認証の画面が表示されるので、\[Authorize app\] を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/99db379e-d6f8-6ba5-73c7-1c4bf01d629e.png)


# Twitter 接続準備

## PolyBase 用データベースの作成

PolyBase 用のデータベースを作成します。

```sql
CREATE DATABASE [PolyBase_Twitter]
 CONTAINMENT = NONE
 ON PRIMARY 
( NAME = N'PolyBase_Twitter', FILENAME = N'{任意のインストール先フォルダ}\PolyBase_Twitter.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'PolyBase_Twitter_log', FILENAME = N'{任意のインストール先フォルダ}\PolyBase_Twitter.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [PolyBase_Twitter] SET RECOVERY SIMPLE 
GO
```

## データベースマスターキーの作成

PolyBase 用のデータベースにて、データベース マスター キー を作成します。

```sql
USE [PolyBase_Twitter]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO
```

## Twitter 資格情報の作成

Twitter の資格情報を作成します。

```sql
USE [PolyBase_Twitter]
GO
CREATE DATABASE SCOPED CREDENTIAL [TwitterCredential]
WITH IDENTITY = 'MyTwitter', Secret = ''
GO
SELECT * FROM sys.database_scoped_credentials;
GO
```

## 外部データソースの作成

先ほど作成した PolyBase 用のデータベースにて、外部データソースの設定を行います。

```sql
CREATE EXTERNAL DATA SOURCE [TwitterInstance]
WITH ( 
  LOCATION = 'odbc://<ServerName>', -- ServerName は マシン名に変更
  CONNECTION_OPTIONS ='DRIVER={CData ODBC Driver for Twitter};DSN=CData Twitter Sys',
  CREDENTIAL = [TwitterCredential])
GO
-- 作成した外部データソースの確認
SELECT * FROM sys.external_data_sources;
GO
```

## 外部テーブルの作成

Twitter のツイート情報に紐づく、外部テーブルを作成します。

```sql
CREATE EXTERNAL TABLE [extTwitter_Tweets](
	[ID] NVARCHAR(60) COLLATE Japanese_CI_AS,
	[IDLong] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[SearchTerms] NVARCHAR(400) COLLATE Japanese_CI_AS,
	[Created_At] DATETIME2(0),
	[Text] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Lang] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Source] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Favorited] BIT,
	[Favorite_Count] INT,
	[Retweeted] BIT,
	[Retweet_Count] INT,
	[Retweeted_Status_Id] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Truncated] BIT,
	[Filter_Level] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Possibly_Sensitive] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Withheld_Copyright] BIT,
	[Withheld_Scope] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Withheld_In_Countries] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Contributors] NVARCHAR(MAX) COLLATE Japanese_CI_AS,
	[Coordinates_Coordinates] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Coordinates_Type] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Place_Full_Name] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Place_Country] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Current_User_Retweet_Id] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[Scopes] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[In_Reply_To_Status_Id] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[From_User_Id] NVARCHAR(510) COLLATE Japanese_CI_AS,
	[From_User_Screen_Name] NVARCHAR(510) COLLATE Japanese_CI_AS,
	[From_User_Name] NVARCHAR(510) COLLATE Japanese_CI_AS,
	[From_User_Location] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[From_User_Profile_URL] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[From_User_Profile_Image_Url] NVARCHAR(4000) COLLATE Japanese_CI_AS,
	[To_User_Id] NVARCHAR(510) COLLATE Japanese_CI_AS,
	[To_User_Screen_Name] NVARCHAR(510) COLLATE Japanese_CI_AS,
	[User_Mentions] NVARCHAR(MAX) COLLATE Japanese_CI_AS,
	[URLs] NVARCHAR(MAX) COLLATE Japanese_CI_AS,
	[Hashtags] NVARCHAR(MAX) COLLATE Japanese_CI_AS,
	[Media] NVARCHAR(MAX) COLLATE Japanese_CI_AS,
	[NextPageToken] NVARCHAR(4000) COLLATE Japanese_CI_AS)
WITH
(LOCATION = 'Tweets',DATA_SOURCE = [TwitterInstance])
GO
```

# ツイートの取得

以下の SQL を実行し、ツイート情報を取得します。

```sql
SELECT
  [From_User_Name],
  [Text],
  [Hashtags],
  [Created_At]
FROM
  [PolyBase_Twitter].[dbo].[extTwitter_Tweets]
ORDER BY
  [Created_At] DESC
```

(実行結果)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/c3a5bfdd-2c07-290b-9d5e-7718857d0673.png)

Text カラムや Hashtags カラムにハッシュタグ情報があるため、WHERE 文で ハッシュタグツイートを絞り込むことも可能です。

```sql
SELECT
  [From_User_Name],
  [Text],
  [Created_At]
FROM
  [PolyBase_Twitter].[dbo].[extTwitter_Tweets]
WHERE
  [Text] LIKE N'%#MSIgnite%'
ORDER BY
  [Created_At] DESC
GO
```



# 参考資料

## Microsoft Docs

- [SQL Server 2016 の新機能][SQLServer2016の新機能]
- [SQL Server 2019 の新機能][SQLServer2019の新機能]
- [PolyBase スケールアウト グループ][PolyBaseスケールアウトグループ]
- [Windows への PolyBase のインストール][WindowsへのPolyBaseのインストール]
- [SQL Server 上の外部データにアクセスするための PolyBase の構成][SQLServer上の外部データにアクセスするためのPolyBaseの構成]
- [CREATE MASTER KEY (Transact-SQL)][CREATE_MASTER_KEY_(Transact-SQL)]

## JSSUG 関連

- [第23回 SQL Server 2019勉強会][JSSUG #23]
- [SQL Server 2019 PolyBase@Yang Jiayi][SQL Server 2019 PolyBase@Yang Jiayi]

## CDATA

- [Twitter ODBC Driver][Twitter_ODBC_Driver]



[SQLServer2019]:https://www.microsoft.com/en-us/sql-server/sql-server-2019

[SQLServer2016の新機能]:https://docs.microsoft.com/ja-jp/sql/sql-server/what-s-new-in-sql-server-2016?view=sql-server-ver15]
[SQLServer2017の新機能]:https://docs.microsoft.com/ja-jp/sql/sql-server/what-s-new-in-sql-server-2017?view=sql-server-ver15
[SQLServer2019の新機能]:https://docs.microsoft.com/ja-jp/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15
[PolyBaseスケールアウトグループ]:https://docs.microsoft.com/ja-jp/sql/relational-databases/polybase/polybase-scale-out-groups?view=sql-server-ver15
[WindowsへのPolyBaseのインストール]:https://docs.microsoft.com/ja-jp/sql/relational-databases/polybase/polybase-installation?view=sql-server-ver15
[SQLServer上の外部データにアクセスするためのPolyBaseの構成]:https://docs.microsoft.com/ja-jp/sql/relational-databases/polybase/polybase-configure-odbc-generic?view=sql-server-ver15
[CREATE_MASTER_KEY_(Transact-SQL)]:https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver15

[JSSUG #23]:https://sqlserver.connpass.com/event/142199/
[SQL Server 2019 PolyBase@Yang Jiayi]:https://blog.jssug.jp/archives/367

[Twitter_ODBC_Driver]:https://www.cdata.com/jp/drivers/twitter/odbc/