3
1

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.

Azure Data FactoryでAzure SQL Data Warehouseを再開、性能変更、停止する

Last updated at Posted at 2019-10-18

#はじめに
Automationでやるのは意外と面倒そうということでAzure Data Factoryから実行する方法をまとめます

#参考
参考コード
https://github.com/furmangg/automating-azure-sql-dw

公式
Azure SQL Data Warehouse の REST API

#手順概要

  1. Azure Data FactoryでPipelineを作成
  2. SQL Data WarehouseのあるSQL Serverの各種情報をPipelineに設定
  3. 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を作ります
image.png

右上の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
image.png

###停止用Pipeline
名前を「[PauseAzureSQLDW」にして空のPipelineを作ります
image.png

右上の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
image.png

###性能変更用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

image.png

##SQL Data WarehouseのあるSQL Serverの各種情報をPipelineに設定
Parametersに値入力箇所があります。

image.png

入力値はこちらから

image.png

##SQL ServerのロールをData Factoryに割り当て

image.png

##動作確認
下記シナリオのパイプラインを作ってみます。
###再開、性能アップ(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
image.png

パイプラインのDWUパラメータを変更することでアップ時の性能を制御しています。
image.png

Trigger nowかdebugで動作確認してみましょう

#まとめ
これを利用すればData Factoryでバッチジョブ実行をする際にフロー中で再開→性能アップ→処理→性能ダウン→休止が可能になります。

#補足
UntilSqlDwOnlineなどのチェック部分が毎秒チェックして回数=課金がかさむかもしれないのである程度waitを入れてもいいかもしれません
あとDWの情報は親のパイプラインに持たせて共通のパラメータにしたりね。。

今回のソースはこちらに

3
1
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
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?