SQL Server 2019 ビッグデータクラスターを AKS で使う - データ仮想化で Azure SQL をクエリする

Last updated at 2020-02-22Posted at 2020-02-10

サンプルデータを試すで HDFS 上にある CSV をデータ仮想化でクエリする方法を見ていきましたが、今回は外部のリレーショナルデータベースをデータ仮想化機能でクエリしてみます。

以下記事では Oracle をクエリしますが、本記事では Azure SQL をクエリします。
参照:チュートリアル:SQL Server ビッグデータクラスターから Oracle にクエリを実行する

Azure SQL の用意

クイックスタート:Azure portal、PowerShell、Azure CLI を使用して Azure SQL Database で単一データベースを作成しますを参考にデータベースを作成します。

1. リソースグループを作成。

az group create --name azuresql --location japaneast

2. SQL Server を作成。

名前は kenakamuazuresql としているが、グローバルで一意のものを指定
任意のロケーション、ユーザー名、パスワードを指定

az sql server create --name kenakamuazuresql -g azuresql --location eastus --admin-user <adminname> --admin-password <password>

3. ファイアウォールルールを作成。

1 つ目はローカルクライアント用
2 つ目は Azure リソース用

az sql server firewall-rule create -g azuresql -s kenakamuazuresql -n AllowYourIp --start-ip-address <your ip address> --end-ip-address <your ip address>
az sql server firewall-rule create -g azuresql -s kenakamuazuresql -n AllowAzureAccess --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

4. データベースを作成。

スペックは任意で設定
AdventureWorksLT サンプルデータベースを復元

az sql db create -g azuresql -s kenakamuazuresql --name azuresqldb --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2

作成できるとリソースグループに SQL Server とデータベースが表示されます。

またサンプルデータベースを指定しているため、以下のようなテーブルが存在します。

外部テーブルの作成

では早速 Azure SQL に対して外部テーブルを作成してみます。

1. サンプルデータベースである sales に外部テーブルを作成するため、データベースを選択。

USE sales

2. 接続に使う認証情報を作成。

ここでは azuresqluser としたが、資格情報名は任意
ユーザー名とパスワードを指定

CREATE DATABASE SCOPED CREDENTIAL [azuresqluser] WITH IDENTITY = N'<user name>', SECRET = N'<password>';

3. データソースを作成。

ここでは kenakamuazuresql としたが、データソース名は任意
sqlserver:// 以降に実際の SQL Server 名を指定
先ほど作成した資格情報名を指定

CREATE EXTERNAL DATA SOURCE [kenakamuazuresql] WITH (LOCATION = N'sqlserver://kenakamuazuresql.database.windows.net', CREDENTIAL = [azuresqluser]);

4. 外部テーブルを作成。

先ほど作成したデータソース名を指定

CREATE EXTERNAL TABLE [dbo].[Product]
(
    [ProductID] INT NOT NULL,
    [Name] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ProductNumber] NVARCHAR(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Color] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS,
    [StandardCost] MONEY NOT NULL,
    [ListPrice] MONEY NOT NULL,
    [Size] NVARCHAR(5) COLLATE SQL_Latin1_General_CP1_CI_AS,
    [Weight] DECIMAL(8, 2),
    [ProductCategoryID] INT,
    [ProductModelID] INT,
    [SellStartDate] DATETIME2(3) NOT NULL,
    [SellEndDate] DATETIME2(3),
    [DiscontinuedDate] DATETIME2(3),
    [ThumbNailPhoto] VARBINARY(MAX),
    [ThumbnailPhotoFileName] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
    [rowguid] UNIQUEIDENTIFIER NOT NULL,
    [ModifiedDate] DATETIME2(3) NOT NULL
)
WITH (LOCATION = N'[azuresqldb].[SalesLT].[Product]', DATA_SOURCE = [kenakamuazuresql]);