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

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

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

公式
[Azure SQL Data Warehouse の REST API](https://docs.microsoft.com/ja-jp/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-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](https://docs.microsoft.com/ja-jp/azure/role-based-access-control/built-in-roles#sql-db-contributor)

Microsoft.Sql/servers/databases/*
#手順
##Azure Data FactoryでPipelineを作成
###再開用Pipeline
名前を「ResumeAzureSQLDW」にして空のPipelineを作ります
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/5441b7ea-b829-313f-2b61-685a04f055dd.png)

右上のCodeをクリックするとコードビューになりますので、下記コードを貼り付けます
####貼り付けコード
<details>
<summary>**クリックで展開します**</summary>
<div>

```JSON:
{
    "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"
}

```

</div>

</details>

貼り付け後Finish
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/dd5c97bd-b420-00f0-b24c-c705c69203bf.png)


###停止用Pipeline
名前を「[PauseAzureSQLDW」にして空のPipelineを作ります
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/a6d4e0aa-1d85-f0d1-340b-a5376fe331b8.png)


右上のCodeをクリックするとコードビューになりますので、下記コードを貼り付けます
####貼り付けコード

<details>
<summary>**クリックで展開します**</summary>
<div>

```JSON:

{
    "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"
}
```

</div>
</details>

貼り付け後Finish
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/76722f41-b978-f7a1-ee76-ca1a6418ee13.png)


###性能変更用Pipeline
既定では100DWUです

右上のCodeをクリックするとコードビューになりますので、下記コードを貼り付けます
####貼り付けコード
<details>
<summary>**クリックで展開します**</summary>
<div>

```
{
    "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"
}
```

</div>

</details>

貼り付け後Finish

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/2c952621-8f46-e9cf-7397-1e7433903875.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/d7adc3be-fc91-67de-5fda-07257dbda7e6.png)

入力値はこちらから

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/c981cee1-7047-a4a6-a6b8-17e56cefe452.png)




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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/6d523651-2cf4-e141-da97-296caf61a06c.png)

##動作確認
下記シナリオのパイプラインを作ってみます。
###再開、性能アップ（200DWU）、性能ダウン（100DWU）、停止



####貼り付けコード
<details>
<summary>**クリックで展開します**</summary>
<div>

```JSON:
{
    "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"
}
```

</div>

</details>

貼り付け後Finish
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/c880b782-60bd-980b-3545-6e1e10c96fbd.png)

パイプラインのDWUパラメータを変更することでアップ時の性能を制御しています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/f228c221-95c0-6e93-3bb3-a9aca0bb62a3.png)

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




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

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

今回のソースはこちらに

https://github.com/ryoma-nagata/MyDataFactory/tree/master/resume-up-down-pause-DW/pipeline