快速入门:使用 Azure 门户创建并查询 Synapse SQL 池Quickstart: Create and query a Synapse SQL pool using the Azure portal

使用 Azure 门户在 Azure Synapse Analytics(以前称为 SQL DW)中快速创建和查询 Synapse SQL 池(数据仓库)。Quickly create and query a Synapse SQL pool (data warehouse) in Azure Synapse Analytics (formerly SQL DW) using the Azure portal.

先决条件Prerequisites

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

    备注

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

  2. 下载并安装最新版本的 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.

创建 SQL 池Create a SQL pool

数据仓库是使用 Azure Synapse Analytics 中的 SQL 池创建的。Data warehouses are created using SQL pool in Azure Synapse Analytics. SQL 池是使用定义的一组计算资源创建的。A SQL pool is created with a defined set of compute resources. 数据库在 Azure 资源组逻辑 SQL 服务器中创建。The database is created within an Azure resource group and in a logical SQL server.

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

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

    在 Azure 门户中创建资源

  2. 在“新建”页上选择“数据库”,然后在“特色”列表中选择“Azure Synapse Analytics (前称为 SQL 数据仓库)”。 Select Databases on the New page, and select Azure Synapse Analytics (formerly SQL DW) in the Featured list.

    创建空的数据仓库

  3. 在“基本信息”中,提供订阅、资源组、SQL 池名称和服务器名称:In Basics, provide your subscription, resource group, SQL pool name, and server name:

    设置Setting 建议的值Suggested value 说明Description
    订阅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.
    SQL 池名称SQL pool name 任何全局唯一的名称(例如 mySampleDataWarehouseAny globally unique name (An example is mySampleDataWarehouse) 如需有效的数据库名称,请参阅 Database Identifiers(数据库标识符)。For valid database names, see Database Identifiers. 请注意,SQL 池是一种数据库。Note, a SQL pool is one type of database.
    ServerServer 任何全局唯一名称Any globally unique name 选择现有的服务器,或者选择“新建”创建新的服务器名称。Select existing server, or create a new server name, select Create new. 如需有效的服务器名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid server names, see Naming rules and restrictions.

    创建数据仓库 - 基本详细信息

  4. 在“性能级别”下,选择“选择性能级别”以根据需要使用滑块更改配置。 Under Performance level, select Select performance level to optionally change your configuration with a slider.

    更改数据仓库性能级别

    有关性能级别的详细信息,请参阅管理 Azure Synapse Analytics 中的计算资源For more information about performance levels, see Manage compute in Azure Synapse Analytics.

  5. 填写 Azure Synapse Analytics 窗体的“基本信息”选项卡后,请依次选择“查看 + 创建”、“创建”以创建 SQL 池。 Now that you've completed the Basics tab of the Azure Synapse Analytics form, select Review + Create and then Create to create the SQL pool. 预配需要数分钟。Provisioning takes a few minutes.

    选择“查看 + 创建”

    选择“创建”

  6. 在工具栏上,选择“通知”监视部署过程。On the toolbar, select Notifications to monitor the deployment process.

    通知

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

Azure Synapse 服务将在服务器级别创建防火墙。The Azure Synapse 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.

备注

Azure Synapse 通过端口 1433 进行通信。Azure Synapse 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. 如果是这样,则无法连接到服务器,除非 IT 部门打开了端口 1433。If so, you cannot connect to your server unless your IT department opens port 1433.

  1. 部署完成后,从左侧菜单中选择“所有服务”。After the deployment completes, select All services from the left-hand menu. 选择“数据库”,然后选择“Azure Synapse Analytics”旁边的星形图标将 Azure Synapse Analytics 添加到收藏夹。 Select Databases, select the star next to Azure Synapse Analytics to add Azure Synapse Analytics to your favorites.

  2. 在左侧菜单中选择“Azure Synapse Analytics”,然后在“Azure Synapse Analytics”页上选择“mySampleDataWarehouse”。 Select Azure Synapse Analytics from the left-hand menu and then select mySampleDataWarehouse on the Azure Synapse Analytics page. 此时会打开数据库的概述页,其中显示了完全限定的服务器名称(例如“sqlpoolservername.database.chinacloudapi.cn” ),并提供了用于完成进一步配置的选项。The overview page for your database opens, showing you the fully qualified server name (such as sqlpoolservername.database.chinacloudapi.cn) 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, select the server name.

    查找服务器名称

  4. 选择“显示防火墙设置”。Select Show firewall settings.

    服务器设置

  5. 此时会打开服务器的“防火墙设置”页面。The Firewall settings page for the server opens.

    服务器防火墙规则

  6. 若要将当前的 IP 地址添加到新的防火墙规则,请在工具栏上选择“添加客户端 IP”。To add your current IP address to a new firewall rule, select 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. 选择“保存”。select Save. 此时会针对当前的 IP 地址创建服务器级防火墙规则,在服务器上打开端口 1433。A server-level firewall rule is created for your current IP address opening port 1433 on the server.

  8. 选择“确定”,然后关闭“防火墙设置”页。 select OK and then close the Firewall settings page.

现在,可使用此 IP 地址连接到服务器及其 SQL 池。You can now connect to the server and its SQL pools 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 服务禁用防火墙。 select OFF on this page and then select Save to disable the firewall for all Azure services.

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

请在 Azure 门户中获取服务器的完全限定的服务器名称。Get the fully qualified server name for your 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. 在左侧菜单中选择“Azure Synapse Analytics”,然后在“Azure Synapse Analytics”页上选择你的 SQL 池。 Select Azure Synapse Analytics from the left-hand menu, and select your on the Azure Synapse Analytics page.

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

    连接信息

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

本部分使用 SQL Server Management Studio (SSMS) 来建立与服务器的连接。This section uses SQL Server Management Studio (SSMS) to establish a connection to your 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 例如:“sqlpoolservername.database.chinacloudapi.cn” 。Here's an example: sqlpoolservername.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 创建服务器时指定的帐户。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. 选择“连接”。select 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

不建议在以服务器管理员身份登录时运行大型查询,因为它使用有限的资源类It is not recommended to run large queries while being logged as the server admin, as it uses a limited resource class. 改为按教程中所示配置工作负荷隔离Instead configure Workload Isolation as illustrated in the tutorials.

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-select 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. 选择“执行”。select Execute. 查询结果显示两个数据库:master 和 mySampleDataWarehouse。The query results show two databases: master and mySampleDataWarehouse.

    查询数据库

清理资源Clean up resources

针对 SQL 池的数据仓库单元数和存储的数据收费。You're being charged for data warehouse units and data stored your SQL pool. 这些计算和存储资源是分开计费的。These compute and storage resources are billed separately.

  • 如果想要将数据保留在存储中,可以在不使用 SQL 池时暂停计算。If you want to keep the data in storage, you can pause compute when you aren't using the SQL pool. 如果暂停计算资源,则你只需支付数据存储费用。By pausing compute, you're only charged for data storage. 只要准备好处理数据,便可以恢复计算。You can resume compute whenever you're ready to work with the data.

  • 若要避免将来产生费用,可以删除该 SQL 池。If you want to remove future charges, you can delete the SQL pool.

按照以下步骤清理不再需要的资源。Follow these steps to clean up resources you no longer need.

  1. 登录到 Azure 门户,选择你的 SQL 池。Sign in to the Azure portal, select your SQL pool.

    清理资源

  2. 若要暂停计算,请选择“暂停”按钮。To pause compute, select the Pause button. 暂停 SQL 池后,会看到“恢复”按钮。When the SQL pool is paused, you see a Resume button. 若要恢复计算,请选择“恢复”。To resume compute, select Resume.

  3. 若要删除 SQL 池以免产生计算或存储费用,请选择“删除”。To remove the SQL pool so you aren't charged for compute or storage, select Delete.

  4. 若要删除你创建的服务器,请选择上图中的“sqlpoolservername.database.chinacloudapi.cn”,然后选择“删除” 。To remove the 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

若要详细了解如何将数据加载到 SQL 池中,请阅读将数据加载到 SQL 池一文。To learn more about loading data into your SQL pool, continue to the Load data into SQL pool article.