快速入门:在 Azure 门户中创建和查询 Azure SQL 数据仓库Quickstart: Create and query an Azure SQL data warehouse in the Azure portal

使用 Azure 门户快速创建和查询 Azure SQL 数据仓库。Quickly create and query an Azure SQL data warehouse by using the Azure portal.

如果没有 Azure 订阅,请在开始之前创建一个1元人民币帐户。If you don't have an Azure subscription, create a 1rmb account before you begin.

Note

创建 SQL 数据仓库可能会导致新的计费服务。Creating a SQL Data Warehouse may result in a new billable service. 有关详细信息,请参阅 SQL 数据仓库定价For more information, see SQL Data Warehouse pricing.

准备阶段Before you begin

下载并安装最新版本的 SQL Server Management Studio (SSMS)。Download and install the newest version of SQL Server Management Studio (SSMS).

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

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

创建数据仓库Create a data warehouse

创建 Azure SQL 数据仓库时,会使用一组定义好的计算资源An Azure SQL data warehouse is created with a defined set of compute resources. 数据库在 Azure 资源组Azure SQL 逻辑服务器中创建。The database is created within an Azure resource group and in an Azure SQL logical server.

按照这些步骤创建包含 AdventureWorksDW 示例数据的 SQL 数据仓库。Follow these steps to create a SQL data warehouse that contains the AdventureWorksDW sample data.

  1. 在 Azure 门户的左上角单击“创建资源”。Click Create a resource in the upper left-hand corner of the Azure portal.

  2. 从“新建”页中选择“数据库”,然后从“新建”页的“特色”下选择“SQL 数据仓库”。Select Databases from the New page, and select SQL Data Warehouse under Featured on the New page.

    创建空的数据仓库

  3. 使用以下信息填写“SQL 数据仓库”表单:Fill out the SQL Data Warehouse form with the following information:

    设置Setting 建议的值Suggested value 说明Description
    数据库名称Database name mySampleDataWarehousemySampleDataWarehouse 如需有效的数据库名称,请参阅 Database Identifiers(数据库标识符)。For valid database names, see Database Identifiers. 请注意,数据仓库是一种数据库。Note, a data warehouse is a type of database.
    订阅Subscription 你的订阅Your subscription 有关订阅的详细信息,请参阅订阅For details about your subscriptions, see Subscriptions.
    资源组Resource group MyResourceGroupmyResourceGroup 有关有效的资源组名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid resource group names, see Naming rules and restrictions.
    选择源Select source 空白数据库Blank database 创建空数据库。Create a blank database. 请注意,数据仓库是一种数据库。Note, a data warehouse is one type of database.

    创建数据仓库

  4. 单击“服务器”,为新数据库创建并配置新服务器。Click Server to create and configure a new server for your new database. 使用以下信息填写“新建服务器”窗体:Fill out the New server form with the following information:

    设置Setting 建议的值Suggested value 说明Description
    服务器名称Server name 任何全局唯一名称Any globally unique name 如需有效的服务器名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid server names, see Naming rules and restrictions.
    服务器管理员登录名Server admin login 任何有效的名称Any valid name 如需有效的登录名,请参阅 Database Identifiers(数据库标识符)。For valid login names, see Database Identifiers.
    密码Password 任何有效的密码Any valid password 密码必须至少有八个字符,且必须包含以下类别中的三个类别的字符:大写字符、小写字符、数字以及非字母数字字符。Your password must have at least eight characters and must contain characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters.
    位置Location 任何有效的位置Any valid location 有关区域的信息,请参阅 Azure 区域For information about regions, see Azure Regions.

    创建数据库服务器

  5. 单击“选择”。Click Select.

  6. 单击“性能级别”以指定数据仓库的性能配置。Click Performance level to specify the performance configuration for the data warehouse.

  7. 对于本教程,请选择“第 2 代”。For this tutorial, select Gen2. 滑块默认设置为“DW1000c”。The slider, by default, is set to DW1000c. 请尝试上下移动滑块,以查看其工作原理。Try moving it up and down to see how it works.

    配置性能

  8. 单击“应用” 。Click Apply.

  9. 填写 SQL 数据仓库表单后,即可单击“创建”进行数据库预配。Now that you have completed the SQL Data Warehouse form, click Create to provision the database. 预配需要数分钟。Provisioning takes a few minutes.

    单击“创建”

  10. 在工具栏上,单击“通知”可监视部署过程。On the toolbar, click Notifications to monitor the deployment process.

    通知

创建服务器级防火墙规则Create a server-level firewall rule

SQL 数据仓库服务在服务器级别创建一个防火墙,阻止外部应用程序和工具连接到服务器或服务器上的任何数据库。The SQL Data Warehouse service creates a firewall at the server-level that prevents external applications and tools from connecting to the server or any databases on the server. 要启用连接,可以添加防火墙规则,为特定 IP 地址启用连接。To enable connectivity, you can add firewall rules that enable connectivity for specific IP addresses. 按照以下步骤为客户端的 IP 地址创建服务器级防火墙规则Follow these steps to create a server-level firewall rule for your client's IP address.

Note

SQL 数据仓库通过端口 1433 进行通信。SQL Data Warehouse communicates over port 1433. 如果尝试从企业网络内部进行连接,则该网络的防火墙可能不允许经端口 1433 的出站流量。If you are trying to connect from within a corporate network, outbound traffic over port 1433 might not be allowed by your network's firewall. 如果是这样,则无法连接到 Azure SQL 数据库服务器,除非 IT 部门打开了端口 1433。If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 1433.

  1. 部署完成后,在左侧菜单中单击“SQL 数据仓库”,然后在“SQL 数据仓库”页上单击“mySampleDatabase”。After the deployment completes, click SQL data warehouses from the left-hand menu and then click mySampleDatabase on the SQL data warehouses page. 此时会打开数据库的概述页,显示完全限定的服务器名称(例如 mynewserver-20181129.database.chinacloudapi.cn),并且会提供进行进一步配置所需的选项。The overview page for your database opens, showing you the fully qualified server name (such as mynewserver-20181129.database.chinacloudapi.cn) and provides options for further configuration.

  2. 在后续的快速入门中,请复制此完全限定的服务器名称,将其用于连接到服务器及其数据库。Copy this fully qualified server name for use to connect to your server and its databases in subsequent quick starts. 单击服务器名称,打开服务器设置。To open server settings, click the server name.

    查找服务器名称

  3. 若要打开服务器设置,To open server settings,

  4. 请单击服务器名称。click the server name.

    服务器设置

  5. 单击“显示防火墙设置”。Click Show firewall settings. 此时会打开 SQL 数据库服务器的“防火墙设置”页。The Firewall settings page for the SQL Database server opens.

    服务器防火墙规则

  6. 在工具栏上单击“添加客户端 IP”,将当前的 IP 地址添加到新的防火墙规则。To add your current IP address to a new firewall rule, click Add client IP on the toolbar. 防火墙规则可以针对单个 IP 地址或一系列 IP 地址打开端口 1433。A firewall rule can open port 1433 for a single IP address or a range of IP addresses.

  7. 单击“保存”。Click Save. 此时会针对当前的 IP 地址创建服务器级防火墙规则,在逻辑服务器上打开 端口 1433。A server-level firewall rule is created for your current IP address opening port 1433 on the logical server.

  8. 单击“确定”,并关闭“防火墙设置”页。Click OK and then close the Firewall settings page.

现在,可使用此 IP 地址连接到 SQL Server 及其数据仓库。You can now connect to the SQL server and its data warehouses using this IP address. 可从 SQL Server Management Studio 或另一种所选工具进行连接。The connection works from SQL Server Management Studio or another tool of your choice. 连接时,请使用之前创建的 ServerAdmin 帐户。When you connect, use the ServerAdmin account you created previously.

Important

默认情况下,所有 Azure 服务都允许通过 SQL 数据库防火墙进行访问。By default, access through the SQL Database firewall is enabled for all Azure services. 单击此页上的“关闭”,然后单击“保存”,对所有 Azure 服务禁用防火墙。Click OFF on this page and then click Save to disable the firewall for all Azure services.

获取完全限定的服务器名称Get the fully qualified server name

请在 Azure 门户中获取 SQL Server 的完全限定的服务器名称。Get the fully qualified server name for your SQL server in the Azure portal. 稍后,在连接到服务器时,将使用该完全限定的名称。Later you use the fully qualified name when connecting to the server.

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

  2. 从左侧菜单中选择“SQL 数据仓库”,然后单击“SQL 数据仓库”页上的数据仓库。Select SQL Data warehouses from the left-hand menu, and click your data warehouse on the SQL datawarehouses page.

  3. 在数据库的“Azure 门户”页的“概要”窗格中,找到并复制“服务器名称”。In the Essentials pane in the Azure portal page for your database, locate and then copy the Server name. 在此示例中,完全限定的名称为 mynewserver-20181129.database.chinacloudapi.cn。In this example, the fully qualified name is mynewserver-20181129.database.chinacloudapi.cn.

    连接信息

以服务器管理员的身份连接到服务器Connect to the server as server admin

本部分使用 SQL Server Management Studio (SSMS) 来建立与 Azure SQL Server 的连接。This section uses SQL Server Management Studio (SSMS) to establish a connection to your Azure SQL server.

  1. 打开 SQL Server Management Studio。Open SQL Server Management Studio.

  2. 在“连接到服务器”对话框中,输入以下信息:In the Connect to Server dialog box, enter the following information:

    设置Setting 建议的值Suggested value 说明Description
    服务器类型Server type 数据库引擎Database engine 此值是必需的This value is required
    服务器名称Server name 完全限定的服务器名称The fully qualified server name 这是一个示例:mynewserver-20181129.database.chinacloudapi.cnHere's an example: mynewserver-20181129.database.chinacloudapi.cn.
    身份验证Authentication SQL Server 身份验证SQL Server Authentication SQL 身份验证是本教程中配置的唯一身份验证类型。SQL Authentication is the only authentication type that is configured in this tutorial.
    登录Login 服务器管理员帐户The server admin account 这是在创建服务器时指定的帐户。This is the account that you specified when you created the server.
    密码Password 服务器管理员帐户的密码The password for your server admin account 这是在创建服务器时指定的密码。This is the password that you specified when you created the server.

    连接到服务器

  3. 单击“连接” 。Click Connect. 此时会在 SSMS 中打开“对象资源管理器”窗口。The Object Explorer window opens in SSMS.

  4. 在对象资源管理器中,展开“数据库”。In Object Explorer, expand Databases. 然后展开“mySampleDatabase”,查看新数据库中的对象。Then expand mySampleDatabase to view the objects in your new database.

    数据库对象

运行一些查询Run some queries

SQL 数据仓库使用 T-SQL 作为查询语言。SQL Data Warehouse uses T-SQL as the query language. 打开一个查询窗口并运行一些 T-SQL 查询,请使用以下步骤:To open a query window and run some T-SQL queries, use the following steps:

  1. 右键单击“mySampleDataWarehouse”,然后选择“新建查询”。Right-click mySampleDataWarehouse and select New Query. 此时将打开一个新的查询窗口。A new query window opens.

  2. 在查询窗口中,输入以下命令以查看数据库列表。In the query window, enter the following command to see a list of databases.

    SELECT * FROM sys.databases
    
  3. 单击“执行” 。Click Execute. 查询结果显示两个数据库:master 和 mySampleDataWarehouse。The query results show two databases: master and mySampleDataWarehouse.

    查询数据库

清理资源Clean up resources

针对数据仓库资源用量和数据仓库存储的数据,将会收取你的费用。You are being charged for data warehouse units and data stored 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 are only charged for data storage. 只要准备好处理数据,便可以恢复计算。You can resume compute whenever you are ready to work with the data.
  • 如果不想支付将来的费用,则可以删除数据仓库。If you want to remove future charges, you can delete the data warehouse.

请按照下列步骤按需清理资源。Follow these steps to clean up resources as you desire.

  1. 登录到 Azure 门户,单击数据仓库。Sign in to the Azure portal, click on your data warehouse.

    清理资源

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

  3. 要删除数据仓库,以便不再为计算或存储付费,请单击“删除”。To remove the data warehouse so you are not charged for compute or storage, click Delete.

  4. 若要删除已创建的 SQL Server,请单击上图中的“mynewserver-20181129.database.chinacloudapi.cn”,然后单击“删除”。To remove the SQL server you created, click mynewserver-20181129.database.chinacloudapi.cn in the previous image, and then click 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, click myResourceGroup, and then click Delete resource group.

后续步骤Next steps

现已创建数据仓库、防火墙规则、已连接到数据仓库,并运行了几个查询。You have now created a data warehouse, created a firewall rule, connected to your data warehouse, and run a few queries. 若要了解有关 Azure SQL 数据仓库的详细信息,请继续有关加载数据的教程。To learn more about Azure SQL Data Warehouse, continue to the tutorial for loading data.