您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

快速入门:在 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 订阅,请在开始之前创建一个免费帐户。If you don't have an Azure subscription, create a free account before you begin.

备注

创建 SQL 数据仓库可能会导致新的计费服务。Creating a SQL Data Warehouse may result in a new billable service. 有关详细信息,请参阅 Azure 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 DescriptionDescription
    订阅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.
    数据仓库名称Data warehouse name mySampleDataWarehousemySampleDataWarehouse 如需有效的数据库名称,请参阅 Database Identifiers(数据库标识符)。For valid database names, see Database Identifiers. 请注意,数据仓库是一种数据库。Note, a data warehouse is one type of database.

    创建数据仓库

  4. 选择一个现有服务器,或者单击“新建”,为新数据库创建并配置新服务器。 Select an existing Server or click Create new 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've completed the Basics tab of the SQL Data Warehouse form, click Review + 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. 此防火墙会阻止外部应用程序和工具连接到服务器或服务器上的任何数据库。This firewall 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.

备注

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. 部署完成后,从左侧菜单中选择“所有服务” 。After the deployment completes, select All services from the left-hand menu. 选择“数据库” ,选择“SQL 数据仓库” 旁边的星号,以将 SQL 数据仓库添加到收藏夹中。Select Databases, select the star next to SQL data warehouses to add SQL data warehouses to your favorites.

  2. 从左侧菜单中选择“SQL 数据仓库” ,然后单击“SQL 数据仓库” 页上的“mySampleDataWarehouse” 。Select SQL data warehouses from the left-hand menu and then click mySampleDataWarehouse on the SQL data warehouses page. 此时会打开数据库的概览页,其中显示了完全限定的服务器名称(例如 mynewserver-20180430.database.windows.net),并提供了其他配置的选项 。The overview page for your database opens, showing you the fully qualified server name (such as mynewserver-20180430.database.windows.net) and provides options for further configuration.

  3. 复制此完全限定的服务器名称,以便在此快速入门和其他快速入门中使用它连接到服务器及其数据库。Copy this fully qualified server name for use to connect to your server and its databases in this and other quick starts. 单击服务器名称,打开服务器设置。To open server settings, click the server name.

    查找服务器名称

  4. 单击“显示防火墙设置” 。Click Show firewall settings.

    服务器设置

  5. 此时会打开 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.

重要

默认情况下,所有 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 data warehouses page.

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

    连接信息

以服务器管理员的身份连接到服务器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-20180430.database.windows.net 。Here's an example: mynewserver-20180430.database.windows.net.
    AuthenticationAuthentication SQL Server 身份验证SQL Server Authentication SQL 身份验证是本教程中配置的唯一身份验证类型。SQL Authentication is the only authentication type that is configured in this tutorial.
    登录Login 服务器管理员帐户The server admin account 创建服务器时指定的帐户。Account that you specified when you created the server.
    密码Password 服务器管理员帐户的密码The password for your server admin account 创建服务器时指定的密码。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.

    查询数据库

  4. 若要查看某些数据,请使用以下命令查看姓氏为 Adams 家中有三个孩子的客户数。To look at some data, use the following command to see the number of customers with last name of Adams that have three children at home. 结果列出六个客户。The results list six customers.

    SELECT LastName, FirstName FROM dbo.dimCustomer
    WHERE LastName = 'Adams' AND NumberChildrenAtHome = 3;
    

    查询 dbo.dimCustomer

清理资源Clean up resources

针对数据仓库单位数和数据仓库存储的数据,会向你收取费用。You're 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're only charged for data storage. 只要准备好处理数据,便可以恢复计算。You can resume compute whenever you're 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 you no longer need.

  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 Resume button. 要恢复计算,请单击“恢复” 。To resume compute, click Resume.

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

  4. 要删除所创建的 SQL Server,请单击上图中的“mynewserver-20180430.database.windows.net”,然后单击“删除” 。To remove the SQL server you created, click mynewserver-20180430.database.windows.net 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've 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.