はじめに

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

SQL Server PolyBase

SQL Server PolyBase の歴史

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

リレーショナルデータベース: 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 をインストールしています。

TCP/IP の有効化

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

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

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

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

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

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

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

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

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

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

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

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

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

パラメータの詳細は、こちらを参照してください。

スケールアウトしない場合

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>"

スケールアウトする場合

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

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

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

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;
GO

IsPolyBaseInstalled
-------------------------
1

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

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

サービスの再起動

PolyBase 有効化を行ったため、以下の Windows サービスを再起動します。

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

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

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

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

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

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

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

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

Twitter ODBC Driver のダウンロード

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

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

Twitter ODBC Driver のインストール

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

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

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

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

[次へ] を選択します。

[次へ] を選択します。

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

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

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

ODBC ドライバーのテスト

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

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

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

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

Twitter 接続準備

PolyBase 用データベースの作成

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

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 用のデータベースにて、データベースマスターキーを作成します。

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

Twitter 資格情報の作成

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

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

外部データソースの作成

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

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 のツイート情報に紐づく、外部テーブルを作成します。

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 を実行し、ツイート情報を取得します。

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

(実行結果)

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

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

JSSUG 関連

CDATA

Twitter ODBC Driver