LoginSignup
0
0
@lowgain(老害 おじさん)

初めての、PolyBase

Last updated at Posted at 2023-06-17

最初に公式からの抜粋を確認します。

PolyBase とは

PolyBaseは、異種のデータソースとのシームレスな統合を実現し、データ処理の柔軟性と効率性を向上させるためのツールです。

PolyBase を使用すると、SQL Server インスタンスにより、クライアント接続ソフトウェアを別途インストールしなくても、SQL Server、Oracle、Teradata、MongoDB、Hadoop クラスター、Cosmos DB、S3 互換オブジェクト ストレージから T-SQL を使用してデータを直接照会できます。 また、汎用 ODBC コネクタを使用して、サードパーティの ODBC ドライバーを使用して追加のプロバイダーに接続することもできます。 PolyBase を使用すると、T-SQL クエリで、外部ソースからのデータを SQL Server のインスタンス内のリレーショナル テーブルに結合できるようになります。

PolyBase 機能を使用したデータの仮想化の主な用途は、データを元の場所と形式で維持されるようにすることです。 SQL Server インスタンスを介してデータを仮想化して、SQL Server 内の他のテーブルと同じようにクエリを行うことができます。 このプロセスにより、データ移動に必要な ETL プロセスを最小化できます。 このデータ仮想化シナリオは、PolyBase コネクタを使用することによって実現します。

PolyBase とリンク サーバー

次の表に、PolyBase とリンク サーバーの機能の違いを示します。

PolyBase リンク サーバー
データベース スコープ オブジェクト インスタンス スコープ オブジェクト
ODBC ドライバーを使用します OLEDB プロバイダーを使用します
すべてのデータ ソースに対する読み取り専用操作と一部の外部データ ソースへの書き込みをサポート 読み取りと書き込み両方の操作をサポートします
単一の接続からのリモート データ ソースに対するクエリをスケールアウトできます 単一の接続からのリモート データ ソースに対するクエリをスケールアウトできません
述語のプッシュダウンがサポートされています 述語のプッシュダウンがサポートされています
可用性グループについて個別に構成する必要はありません 可用性グループの各インスタンスについて個別に構成する必要があります
基本認証のみ 基本 & 統合認証
多くの行を処理する分析クエリに適しています 1 つまたは少数の行を返す OLTP クエリに適しています
外部テーブルを使用するクエリは分散トランザクションに参加できません 分散クエリが分散トランザクションに参加できます

SQL ServerのPolyBase機能を使用するためには、いくつかの手順を実行する必要があります。

SQL Serverのインストール: PolyBaseを使用するには、SQL Server 2019以降のエディションが必要です。適切なエディションのSQL Serverをインストールしてください。

PolyBase構成の有効化: SQL Serverのインストール後、PolyBase機能を有効にする必要があります。SQL Server Configuration Managerを使用して、PolyBase構成を有効にします。

外部データソースの作成: PolyBaseを使用してクエリを実行するためには、外部データソースを作成する必要があります。外部データソースは、データの格納場所や接続情報などの詳細を指定します。たとえば、HadoopクラスターやAzure Blob Storageなどが外部データソースの例です。

外部ファイル フォーマットの作成: 外部データソースに格納されたデータの形式を指定するために、外部ファイル フォーマットを作成する必要があります。フォーマットには、データの区切り文字や列の型などの情報を指定します。

外部テーブルの作成: 外部データソースからデータをクエリするために、外部テーブルを作成する必要があります。外部テーブルは、外部データソースと外部ファイル フォーマットを参照し、データにアクセスするための仮想テーブルです。

クエリの実行: 外部テーブルを使用して、PolyBaseを介してデータソースに対してクエリを実行できます。SQLクエリを通常どおり記述し、PolyBaseが適切なデータソースに対してクエリを実行します。

これらの手順に従うことで、SQL ServerのPolyBase機能を使用して異種のデータソースとのデータ統合やクエリを行うことができます。

試してみます

ローカルのSQLサーバーからAzuewデーターベースへ接続します。

前提として、SQLserver 2019以降でpolybaseがインストールされていることです。

後、TCP/IPを有効にしてください

image.png

インストール

以前セットアップインストールされていれば問題ありません。

image.png

そうでなれれば、追加でインストールをしましょう。

polyBaseを有効にする

インストールが完了したら、PolyBase を有効にしてその機能にアクセスできるようにする必要があります。 次の Transact-SQL コマンドを使用します。

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

次のクエリーを流して

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

で結果が1ならインストールされています。

polybaseを使ってみます

接続

Polyaseを使ってAzure Sql Databaseに接続してみます。

当然、Azure Sql Databaseへの認証情報が必要です。
これをデータベースのマスターキーに設定します。

CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
 WITH IDENTITY = '<admin id>', SECRET = '<password>';

『この操作を実行するには、マスター キーをデータベースに作成するか、またはセッション内のマスター キーを開いてください。』

というエラーが出たなら、次のクエリを実行してマスターキーを作り再度チャレンジしてみてください。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<pass token>';

これで「SqlServerCredentials」という名前の認証情報が用意できました。

この認証情報で、Azure SQL データーベースへ接続します。

CREATE EXTERNAL DATA SOURCE AzureSample
    WITH ( LOCATION = 'sqlserver:\\AzureSample.database.windows.net',
    PUSHDOWN = ON,
    CREDENTIAL = SQLServerCredentials);

AzureSampleという名前の外部データベースの定義ができました。

外部テーブル

MyAzureDataBaseに対して、外部テーブルを作ります。
外部

CREATE EXTERNAL TABLE [dbo].[ExternalTestTable](
	[data] [nvarchar](50) NULL,
	[unmber] [bigint] NOT NULL,
	[updataDate] [datetimeoffset](7) NOT NULL,
	[createDate] [datetimeoffset](7) NOT NULL
)WITH (
DATA_SOURCE = [AzureSample],
LOCATION = N'SampleDataBase.dbo.TestTable')

私の環境だと

105083;ユーザー定義スキーマにある次の列が、
テーブル 'TestTable' の外部テーブル スキーマと互換性がありません: 
ユーザー定義列 ([data] NVARCHAR(50)) は、
検出された外部テーブル列の種類 (
[data] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS)
 と互換性がありません. 
検出された外部テーブル スキーマ: (
[data] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, 
[unmber] BIGINT NOT NULL, 
[updataDate] DATETIMEOFFSET(7) NOT NULL, 
[createDate] DATETIMEOFFSET(7) NOT NULL)。

というエラーが出ます。

これは、Azure側とローカル側の照合順位という奴が一致していないために起こります。
ローカルがJapanese_CI_ASなのに対してAzureはSQL_Latin1_General_CP1_CI_ASとなっいるからですね。

どちらかのデーターベースを作り直せばよいのかもしれませんが、流石はSQL SERVER です。
カラムごとに照合順位が設定できてしまうので

親切なエラーからコピペして

CREATE EXTERNAL TABLE [dbo].[ExternalTestTable](
	[data] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[number] [bigint] NOT NULL,
	[updataDate] [datetimeoffset](7) NOT NULL,
	[createDate] [datetimeoffset](7) NOT NULL
)WITH (
DATA_SOURCE = [AzureSample],
LOCATION = N'SampleDataBase.dbo.TestTable')

いやいや、至れり尽くせりですね。

で使ってみます

その前にこのテストテーブルは

DECLARE @p_NumOfRows BIGINT
SELECT @p_NumOfRows=100000000; 

WITH Base AS ( 
  SELECT 1 AS n 
  UNION ALL 
  SELECT n+1 FROM Base WHERE n < CEILING(SQRT(@p_NumOfRows)) 
), 
Expand AS ( 
  SELECT 1 AS c FROM Base AS b1, Base AS b2 
), 
Nums AS ( 
  SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM Expand 
)

INSERT INTO TestTable 
SELECT 'DATA' + RIGHT('00000000' + CONVERT(NVARCHAR, n), 8) ,n,SYSDATETIMEOFFSET ( ),SYSDATETIMEOFFSET ( )
FROM Nums WHERE n <= @p_NumOfRows

OPTION (MaxRecursion 0);

で作った一億件入ったテーブルなんですね

なのて、

SELECT [data]
      ,[number]
      ,[updataDate]
      ,[createDate]
  FROM [polybasetest].[dbo].[ExternalTestTable]

なんてしたら、終わりません。
直接でも途中で接続切れちゃいますから。

でも、

SELECT TOP (1000) [data]
      ,[number]
      ,[updataDate]
      ,[createDate]
  FROM [polybasetest].[dbo].[ExternalTestTable]

なら、一秒程度で帰ってきます

では小さなテーブルをjoinしてみます。

image.png

CREATE TABLE [dbo].[titles](
	[id] [int] NOT NULL,
	[title] [nvarchar](50) NULL,
 CONSTRAINT [PK_titles] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[titles]
		   ([id],[title])
     VALUES
           (1,'りふれくしょんず')
		  ,(2,'山田君')

SELECT [id]
      ,[title]
	  ,[data]
      ,[number]
      ,[updataDate]
      ,[createDate]
  FROM [dbo].[titles]
  INNER JOIN ExternalTestTable ON [titles].[id] = ExternalTestTable.number

すると全く帰ってきません。

じゃどうするの
例えば、必要なところをコビーしてから処理する！

IF OBJECT_ID(N'tempdb..#extTemp', N'U') IS NOT NULL DROP TABLE #extTemp;

select * into #extTemp from ExternalTestTable where number >= 0 and number < 100000 And data >= 'DATA00000001' and data < 'DATA00100000'

SELECT TOP (1000) [data]
      ,[number]
      ,[updataDate]
      ,[createDate]
  FROM #extTemp
inner join titles on titles.id = #extTemp.number

当然、巨大テーブルが相手でも

IF OBJECT_ID(N'tempdb..#extTemp', N'U') IS NOT NULL DROP TABLE #extTemp;

select * into #extTemp from ExternalTestTable where number >= 0 and number < 100000 And data >= 'DATA00000001' and data < 'DATA00100000'

SELECT TOP (1000) #extTemp.[data] 
      ,#extTemp.[number]
      ,#extTemp.[updataDate]
      ,#extTemp.[createDate]
	  ,[TestTable].[data] 
      ,[TestTable].[number]
      ,[TestTable].[updataDate]
      ,[TestTable].[createDate]
  FROM #extTemp
inner join [TestTable] on [TestTable].[data] = #extTemp.[data] COLLATE Japanese_CI_AS

大丈夫ですね

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