快速入门:使用 Azure 门户创建 Synapse SQL 池工作负载分类器Quickstart: Create a Synapse SQL pool workload classifier using the Azure portal

在本快速入门中,你将创建一个工作负载分类器,用于将查询分配到工作负载组。In this quickstart, you will create a workload classifier for assigning queries to a workload group. 该分类器将 ELTLogin SQL 用户的请求分配到 DataLoads 工作负载组。The classifier will assign requests from the ELTLogin SQL user to the DataLoads workload group. 按照快速入门:配置工作负载隔离教程创建 DataLoads 工作负载组。Follow the Quickstart: Configure workload isolation tutorial to create the DataLoads workload group. 本教程将使用 WLM_LABEL 选项创建工作负载分类器,这样有助于对请求进一步进行正确的分类。This tutorial will create a workload classifier with the WLM_LABEL option to help further classify requests correctly. 该分类器还会将 HIGH 工作负载重要性分配到这些请求。The classifier will assign HIGH workload importance to these requests as well.

如果没有 Azure 订阅,请在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

登录到 Azure 门户Sign in to the Azure portal

登录 Azure 门户Sign in to the Azure portal.

备注

在 Azure Synapse Analytics 中创建 SQL 池实例可能会产生一个新的可计费服务。Creating a SQL pool instance in Azure Synapse Analytics may result in a new billable service. 有关详细信息,请参阅 Azure Synapse Analytics 定价For more information, see Azure Synapse Analytics pricing.

先决条件Prerequisites

本快速入门假定你已在 Synapse SQL 中有 SQL 池实例,并且具有 CONTROL DATABASE 权限。This quickstart assumes you already have a SQL pool instance in Synapse SQL and that you have CONTROL DATABASE permissions. 如果需要创建一个 SQL 数据仓库,可使用创建并连接 - 门户创建名为“mySampleDataWarehouse”的数据仓库 。If you need to create one, use Create and Connect - portal to create a data warehouse called mySampleDataWarehouse.

存在工作负载组 DataLoadsA workload group DataLoads exists. 请参阅快速入门:配置工作负载隔离教程以创建工作负载组。See the Quickstart: Configure workload isolation tutorial to create the workload group.

重要

SQL 池必须联机才能配置工作负载管理。Your SQL pool must be online to configure workload management.

创建 ELTLogin 的登录名Create a login for ELTLogin

使用 CREATE LOGINmaster 数据库中为 ELTLogin 创建 SQL Server 身份验证登录名。Create a SQL Server authentication login in the master database using CREATE LOGIN for ELTLogin.

IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'ELTLogin')
BEGIN
CREATE LOGIN [ELTLogin] WITH PASSWORD='<strongpassword>'
END
;

创建用户并授予权限Create user and grant permissions

创建登录名后,需要在数据库中创建一个用户。After the login is created, a user needs to be created in the database. 使用 CREATE USER 在 mySampleDataWarehouse 中创建 SQL 用户 ELTRoleUse CREATE USER to create the SQL user ELTRole in the mySampleDataWarehouse. 由于我们将在本教程中测试分类,因此请授予对 mySampleDataWarehouse 的 ELTLogin 权限 。Since we will test the classification during this tutorial, grant ELTLogin permissions to mySampleDataWarehouse.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'ELTLogin')
BEGIN
CREATE USER [ELTLogin] FOR LOGIN [ELTLogin]
GRANT CONTROL ON DATABASE::mySampleDataWarehouse TO ELTLogin 
END
;

配置工作负载分类Configure workload classification

分类允许你基于一组规则将请求路由到工作负载组。Classification allows you to route requests, based on a set of rules, to a workload group. 快速入门:配置工作负载隔离教程中,我们已创建 DataLoads 工作负载组。In the Quickstart: Configure workload isolation tutorial we created the DataLoads workload group. 现在,你将创建一个工作负载分类器,用于将查询路由到 DataLoads 工作负载组。Now you will create a workload classifier to route queries to the DataLoads workload group.

  1. 在 Azure 门户的左侧页中单击“Azure Synapse Analytics (前称为 SQL 数据仓库)”。 Click Azure Synapse Analytics (formerly SQL DW) in the left page of the Azure portal.

  2. 从“Azure Synapse Analytics (以前称为 SQL DW)” 页中选择 mySampleDataWarehouseSelect mySampleDataWarehouse from the Azure Synapse Analytics (formerly SQL DW) page. 此时将打开 SQL 池。The SQL pool opens.

  3. 单击“工作负载管理” 。Click Workload management.

    单击“菜单”

  4. 单击 DataLoads 工作负载组右侧的“设置和分类器” 。Click Settings & classifiers on the right-hand side of the DataLoads workload group.

    单击“法律条款” 

  5. 单击“分类器” 。Click on Classifiers.

  6. 单击“添加分类器” 。Click on Add classifier.

    单击“添加”

  7. 在“名称”中输入 ELTLoginDataLoadsEnter ELTLoginDataLoads for Name.

  8. 在“成员”中输入 ELTLoginEnter ELTLogin for Member.

  9. 为“请求重要性”选择 HighChoose High for Request Importance. 这是可选字段,默认设置为一般重要性 。Optional, normal importance is default.

  10. 在“标签”中输入 fact_loadsEnter fact_loads for Label.

  11. 单击“添加” 。Click Add.

  12. 单击“ 保存”。Click Save.

    单击“配置”

验证并测试分类Verify and test classification

检查 sys.workload_management_workload_classifiers 目录视图,验证是否存在 ELTLoginDataLoads 分类器。Check the sys.workload_management_workload_classifiers catalog view to verify existence of the ELTLoginDataLoads classifier.

SELECT * FROM sys.workload_management_workload_classifiers WHERE name = 'ELTLoginDataLoads'

检查 sys.workload_management_workload_classifier_details 目录视图,验证分类器详细信息。Check the sys.workload_management_workload_classifier_details catalog view to verify classifier details.

SELECT c.[name], c.group_name, c.importance, cd.classifier_type, cd.classifier_value
  FROM sys.workload_management_workload_classifiers c
  JOIN sys.workload_management_workload_classifier_details cd
    ON cd.classifier_id = c.classifier_id
  WHERE c.name = 'ELTLoginDataLoads'

运行以下语句以测试分类。Run the following statements to test classification. 确保以 ELTLogin 身份连接并在查询中使用 LabelEnsure you are connected as ELTLogin and Label is used in query.

CREATE TABLE factstaging (ColA int)
INSERT INTO factstaging VALUES(0)
INSERT INTO factstaging VALUES(1)
INSERT INTO factstaging VALUES(2)
GO

CREATE TABLE testclassifierfact WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT * FROM factstaging
OPTION (LABEL='fact_loads')

使用 ELTLoginDataLoads 工作负载分类器验证已分类到 DataLoads 工作负载组的 CREATE TABLE 语句。Verify the CREATE TABLE statement classified to the DataLoads workload group using the ELTLoginDataLoads workload classifier.

SELECT TOP 1 request_id, classifier_name, group_name, resource_allocation_percentage, submit_time, [status], [label], command 
FROM sys.dm_pdw_exec_requests 
WHERE [label] = 'fact_loads'
ORDER BY submit_time DESC

清理资源Clean up resources

若要删除在本教程中创建的 ELTLoginDataLoads 工作负载分类器,请执行以下步骤:To delete the ELTLoginDataLoads workload classifier created in this tutorial:

  1. 单击 DataLoads 工作负载组右侧的“1 分类器” 。Click on 1 Classifier on the right-hand side of the DataLoads workload group.

    单击“删除”

  2. 单击“分类器” 。Click on Classifiers.

  3. 单击 ELTLoginDataLoads 工作负载分类器右侧的“...” 。Click on the ... to the right of the ELTLoginDataLoads workload classifier.

  4. 单击“删除” 。Click on Delete.

  5. 单击“保存” 。Click on Save.

    点击“保存”

我们会针对数据仓库中存储的数据,按数据仓库单位收费。You're being charged for data warehouse units and data stored in your data warehouse. 这些计算和存储资源是分开计费的。These compute and storage resources are billed separately.

  • 如果想要将数据保留在存储中,可以在不使用数据仓库时暂停计算。If you want to keep the data in storage, you can pause compute when you aren't using the data warehouse. 如果暂停计算资源,则你只需支付数据存储费用。By pausing compute, you're only charged for data storage. 准备好处理数据时,可以恢复计算。When you're ready to work with the data, resume compute.
  • 如果不想支付将来的费用,则可以删除数据仓库。If you want to remove future charges, you can delete the data warehouse.

遵循以下步骤清理资源。Follow these steps to clean up resources.

  1. 登录到 Azure 门户,选择你的数据仓库。Sign in to the Azure portal, select on your data warehouse.

    清理资源

  2. 若要暂停计算,请选择“暂停” 按钮。To pause compute, select the Pause button. 暂停数据仓库后,可看到“启动” 按钮。When the data warehouse is paused, you see a Start button. 若要恢复计算,请选择“启动” 。To resume compute, select Start.

  3. 若要删除数据仓库以免产生计算或存储费用,请选择“删除” 。To remove the data warehouse so you're not charged for compute or storage, select Delete.

  4. 若要删除你创建的 SQL 服务器,请选择上图中的“sqlpoolservername.database.chinacloudapi.cn”,然后选择“删除”。 To remove the SQL server you created, select sqlpoolservername.database.chinacloudapi.cn in the previous image, and then select Delete. 请谨慎执行此删除操作,因为删除服务器的同时也会删除分配给该服务器的所有数据库。Be careful with this deletion, since deleting the server also deletes all databases assigned to the server.

  5. 若要删除资源组,请选择“myResourceGroup” ,然后选择“删除资源组” 。To remove the resource group, select myResourceGroup, and then select Delete resource group.

后续步骤Next steps

使用 Azure 门户监视指标监视工作负载。Monitor your workload using the Azure portal monitoring metrics. 有关详细信息,请参阅管理和监视工作负载管理See Manage and monitor Workload Management for details.