はじめに
Automationでやるのは意外と面倒そうということでAzure Data Factoryから実行する方法をまとめます
参考
参考コード
https://github.com/furmangg/automating-azure-sql-dw
公式
Azure SQL Data Warehouse の REST API
手順概要
- Azure Data FactoryでPipelineを作成
- SQL Data WarehouseのあるSQL Serverの各種情報をPipelineに設定
- SQL ServerのロールをData Factoryに割り当て
権限について
Data Warehouseの再開、停止、性能変更は下記のような操作で実行されるため、最小の権限ロールを作成する場合は以下を実行できるロールを作成します。
再開 "action": "Microsoft.Sql/servers/databases/resume/action"
停止 "action": "Microsoft.Sql/servers/databases/pause/action"
性能変更 "action": "Microsoft.Sql/servers/databases/write"
性能変更はdatabaseリソース自体の書き換えになってますね
組み込みロールにはないので、以下の許可を持っているSQL DB Contributorあたりを振っておけばOK.(もちろん共同作成者でもOK)
SQL DB Contributor
Microsoft.Sql/servers/databases/*
手順
Azure Data FactoryでPipelineを作成
再開用Pipeline
名前を「ResumeAzureSQLDW」にして空のPipelineを作ります
右上のCodeをクリックするとコードビューになりますので、下記コードを貼り付けます
貼り付けコード
クリックで展開します
{
"name": "ResumeAzureSQLDW",
"properties": {
"activities": [
{
"name": "IfSqlDwPaused",
"type": "IfCondition",
"dependsOn": [
{
"activity": "CheckIfSqlDwPaused",
"dependencyConditions": [
"Succeeded"
]
}
],
"typeProperties": {
"expression": {
"value": "@not(equals('Online',string(activity('CheckIfSqlDwPaused').output.properties.status)))",
"type": "Expression"
},
"ifTrueActivities": [
{
"name": "ResumeSqlDw",
"type": "WebActivity",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'/resume?api-version=2014-04-01-preview')",
"type": "Expression"
},
"method": "POST",
"body": {
"value": "@string('')",
"type": "Expression"
},
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
}
]
}
},
{
"name": "CheckIfSqlDwPaused",
"type": "WebActivity",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
},
{
"name": "UntilSqlDwResumed",
"type": "Until",
"dependsOn": [
{
"activity": "IfSqlDwPaused",
"dependencyConditions": [
"Succeeded"
]
}
],
"typeProperties": {
"expression": {
"value": "@equals('Online',string(activity('CheckSqlDwStatus').output.properties.status))",
"type": "Expression"
},
"activities": [
{
"name": "CheckSqlDwStatus",
"type": "WebActivity",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
}
],
"timeout": "7.00:00:00"
}
}
],
"parameters": {
"SubscriptionID": {
"type": "String",
"defaultValue": ""
},
"ResourceGroup": {
"type": "String",
"defaultValue": ""
},
"Server": {
"type": "String",
"defaultValue": ""
},
"DW": {
"type": "String",
"defaultValue": ""
}
}
},
"type": "Microsoft.DataFactory/factories/pipelines"
}
貼り付け後Finish
停止用Pipeline
名前を「[PauseAzureSQLDW」にして空のPipelineを作ります
右上のCodeをクリックするとコードビューになりますので、下記コードを貼り付けます
貼り付けコード
クリックで展開します
{
"name": "PauseAzureSQLDW",
"properties": {
"activities": [
{
"name": "IfSqlDwNotPaused",
"type": "IfCondition",
"dependsOn": [
{
"activity": "CheckIfSqlDwPaused",
"dependencyConditions": [
"Succeeded"
]
}
],
"typeProperties": {
"expression": {
"value": "@equals('Online',string(activity('CheckIfSqlDwPaused').output.properties.status))",
"type": "Expression"
},
"ifTrueActivities": [
{
"name": "PauseSqlDw",
"type": "WebActivity",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'/pause?api-version=2014-04-01-preview')",
"type": "Expression"
},
"method": "POST",
"body": {
"value": "@string('')",
"type": "Expression"
},
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
}
]
}
},
{
"name": "CheckIfSqlDwPaused",
"type": "WebActivity",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
},
{
"name": "UntilSqlDwPaused",
"type": "Until",
"dependsOn": [
{
"activity": "IfSqlDwNotPaused",
"dependencyConditions": [
"Succeeded"
]
}
],
"typeProperties": {
"expression": {
"value": "@equals('Paused',string(activity('CheckSqlDwStatus').output.properties.status))",
"type": "Expression"
},
"activities": [
{
"name": "CheckSqlDwStatus",
"type": "WebActivity",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
}
],
"timeout": "7.00:00:00"
}
}
],
"parameters": {
"SubscriptionID": {
"type": "String",
"defaultValue": ""
},
"ResourceGroup": {
"type": "String",
"defaultValue": ""
},
"Server": {
"type": "String",
"defaultValue": ""
},
"DW": {
"type": "String",
"defaultValue": ""
}
}
},
"type": "Microsoft.DataFactory/factories/pipelines"
}
貼り付け後Finish
性能変更用Pipeline
既定では100DWUです
右上のCodeをクリックするとコードビューになりますので、下記コードを貼り付けます
貼り付けコード
クリックで展開します
{
"name": "ScaleAzureSQLDW",
"properties": {
"activities": [
{
"name": "IfSqlDwNotPaused",
"type": "IfCondition",
"dependsOn": [
{
"activity": "CheckIfSqlDwPaused",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@equals('Online',string(activity('CheckIfSqlDwPaused').output.properties.status))",
"type": "Expression"
},
"ifTrueActivities": [
{
"name": "ScaleSqlDw",
"type": "WebActivity",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "PATCH",
"headers": {
"Content-Type": "application/json"
},
"body": {
"value": "{ \"properties\": {\"requestedServiceObjectiveName\": \"@{pipeline().parameters.scale}\" }}",
"type": "Expression"
},
"linkedServices": [],
"datasets": [],
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
}
]
}
},
{
"name": "CheckIfSqlDwPaused",
"type": "WebActivity",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
},
{
"name": "UntilSqlDwScaling",
"type": "Until",
"dependsOn": [
{
"activity": "IfSqlDwNotPaused",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@equals('Online',string(activity('CheckSqlDwStatus').output.properties.status))",
"type": "Expression"
},
"activities": [
{
"name": "CheckSqlDwStatus",
"type": "WebActivity",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false
},
"userProperties": [],
"typeProperties": {
"url": {
"value": "@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Sql/servers/',pipeline().parameters.Server,'/databases/',pipeline().parameters.DW,'?api-version=2014-04-01')",
"type": "Expression"
},
"method": "GET",
"authentication": {
"type": "MSI",
"resource": "https://management.core.windows.net/"
}
}
}
],
"timeout": "7.00:00:00"
}
}
],
"parameters": {
"SubscriptionID": {
"type": "String",
"defaultValue": "3ecf14ad-4309-4b8d-b9c3-84bbaa8d3167"
},
"ResourceGroup": {
"type": "String",
"defaultValue": "MySandbox"
},
"Server": {
"type": "String",
"defaultValue": "mysandboxsql"
},
"DW": {
"type": "String",
"defaultValue": "MySandbox-SQLDW"
},
"scale": {
"type": "string",
"defaultValue": "DW100c"
}
},
"annotations": []
},
"type": "Microsoft.DataFactory/factories/pipelines"
}
貼り付け後Finish
SQL Data WarehouseのあるSQL Serverの各種情報をPipelineに設定
Parametersに値入力箇所があります。
入力値はこちらから
SQL ServerのロールをData Factoryに割り当て
動作確認
下記シナリオのパイプラインを作ってみます。
再開、性能アップ（200DWU）、性能ダウン（100DWU）、停止
貼り付けコード
クリックで展開します
{
"name": "resume-up-down-pause",
"properties": {
"activities": [
{
"name": "call-resume",
"type": "ExecutePipeline",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"pipeline": {
"referenceName": "ResumeAzureSQLDW",
"type": "PipelineReference"
},
"waitOnCompletion": true
}
},
{
"name": "call-pause",
"type": "ExecutePipeline",
"dependsOn": [
{
"activity": "call-scaledown",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"pipeline": {
"referenceName": "PauseAzureSQLDW",
"type": "PipelineReference"
},
"waitOnCompletion": true
}
},
{
"name": "call-scaleup",
"type": "ExecutePipeline",
"dependsOn": [
{
"activity": "call-resume",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"pipeline": {
"referenceName": "ScaleAzureSQLDW",
"type": "PipelineReference"
},
"waitOnCompletion": true,
"parameters": {
"scale": {
"value": "@pipeline().parameters.DWU",
"type": "Expression"
}
}
}
},
{
"name": "call-scaledown",
"type": "ExecutePipeline",
"dependsOn": [
{
"activity": "call-scaleup",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"pipeline": {
"referenceName": "ScaleAzureSQLDW",
"type": "PipelineReference"
},
"waitOnCompletion": true
}
}
],
"parameters": {
"DWU": {
"type": "string",
"defaultValue": "DW200c"
}
},
"annotations": []
},
"type": "Microsoft.DataFactory/factories/pipelines"
}
貼り付け後Finish
パイプラインのDWUパラメータを変更することでアップ時の性能を制御しています。
Trigger nowかdebugで動作確認してみましょう
まとめ
これを利用すればData Factoryでバッチジョブ実行をする際にフロー中で再開→性能アップ→処理→性能ダウン→休止が可能になります。
補足
UntilSqlDwOnlineなどのチェック部分が毎秒チェックして回数＝課金がかさむかもしれないのである程度waitを入れてもいいかもしれません
あとDWの情報は親のパイプラインに持たせて共通のパラメータにしたりね。。
今回のソースはこちらに