LoginSignup
5
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ymasaoka(まっぴぃ)

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

Last updated at Posted at 2019-11-06

はじめに

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 をインストールしています。

image.png

TCP/IP の有効化

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

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

image.png

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

image.png

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

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

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

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

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

image.png

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

image.png

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

image.png

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

image.png

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

インストールウィザードの実行(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

image.png

上記のデータベースは 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 エンジン (対象インスタンス名)

image.png

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 スケールアウトグループを選択した際に必須となる作業です。
　スタンドアロンでインストールした際は、設定不要です。

image.png

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

Twitter ODBC Driver のダウンロード

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

image.png

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

image.png

Twitter ODBC Driver のインストール

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

image.png

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

image.png

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

image.png

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

image.png

[次へ] を選択します。

image.png

[次へ] を選択します。

image.png

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

image.png

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

image.png

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

image.png

ODBC ドライバーのテスト

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

image.png

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

image.png

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

image.png

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

image.png

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

(実行結果)

image.png

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

5
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?