跨扩展云数据库进行报告(预览)Report across scaled-out cloud databases (preview)

可以使用弹性查询从单个连接点中的多个 Azure SQL 数据库中创建报告。You can create reports from multiple Azure SQL databases from a single connection point using an elastic query. 数据库必须进行横向分区(也称为“分片”)。The databases must be horizontally partitioned (also known as "sharded").

如果有现有的数据库,请参阅将现有数据库迁移到扩展数据库If you have an existing database, see Migrating existing databases to scaled-out databases.

若要了解需要查询的 SQL 对象,请参阅跨横向分区的数据库进行查询To understand the SQL objects needed to query, see Query across horizontally partitioned databases.

先决条件Prerequisites

下载并运行弹性数据库工具示例入门Download and run the Getting started with Elastic Database tools sample.

使用示例应用程序创建分片映射管理器Create a shard map manager using the sample app

在此处,用户将创建分片映射管理器以及多个分片,并将数据插入分片。Here you will create a shard map manager along with several shards, followed by insertion of data into the shards. 如果分片中正好设置了分片数据,则可以跳过下面的步骤,直接转到下一部分。If you happen to already have shards setup with sharded data in them, you can skip the following steps and move to the next section.

  1. 生成并运行 弹性数据库工具入门 示例应用程序。Build and run the Getting started with Elastic Database tools sample application. 一直执行到下载和运行示例应用部分中的步骤 7。Follow the steps until step 7 in the section Download and run the sample app. 在步骤 7 结束时,可以看到以下命令提示符:At the end of Step 7, you will see the following command prompt:

    命令提示符

  2. 在命令窗口中键入“1”,并按 EnterIn the command window, type "1" and press Enter. 这会创建分片映射管理器,并将两个分片添加到服务器。This creates the shard map manager, and adds two shards to the server. “然”后“键”入“3”并按“Enter”;重复该操作四次。Then type "3" and press Enter; repeat the action four times. 这会在分片中插入示例数据行。This inserts sample data rows in your shards.

  3. Azure 门户应显示服务器中的 3 个新数据库:The Azure portal should show three new databases in your server:

    Visual Studio 确认

    目前,通过弹性数据库客户端库支持跨数据库查询。At this point, cross-database queries are supported through the Elastic Database client libraries. 例如,在命令窗口中使用第 4 个选项。For example, use option 4 in the command window. 来自多分片查询的结果始终是所有分片结果的 UNION ALLThe results from a multi-shard query are always a UNION ALL of the results from all shards.

    在下一部分,我们创建支持更丰富的跨分片数据查询的示例数据库终结点。In the next section, we create a sample database endpoint that supports richer querying of the data across shards.

创建弹性查询数据库Create an elastic query database

  1. 打开 Azure 门户并登录。Open the Azure portal and log in.

  2. 在与分片设置相同的服务器中创建新的 Azure SQL 数据库。Create a new Azure SQL database in the same server as your shard setup. 将数据库命名为“ElasticDBQuery”。Name the database "ElasticDBQuery."

    Azure 门户和定价层

    Note

    可以使用现有数据库。you can use an existing database. 如果这样做,该数据库不能是你想要对其运行查询的某一个分片。If you can do so, it must not be one of the shards that you would like to execute your queries on. 此数据库将用于为弹性数据库查询创建元数据对象。This database will be used for creating the metadata objects for an elastic database query.

创建数据库对象Create database objects

数据库范围的主密钥和凭据Database-scoped master key and credentials

它们用来连接到分片映射管理器和分片:These are used to connect to the shard map manager and the shards:

  1. 在 Visual Studio 中打开 SQL Server Management Studio 或 SQL Server Data Tools。Open SQL Server Management Studio or SQL Server Data Tools in Visual Studio.

  2. 连接到 ElasticDBQuery 数据库,并执行以下 T-SQL 命令:Connect to ElasticDBQuery database and execute the following T-SQL commands:

     CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
    
     CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
     WITH IDENTITY = '<username>',
     SECRET = '<password>';
    

    “username”和“password”应该与弹性数据库工具入门下载和运行示例应用的步骤 6 中使用的登录信息相同。"username" and "password" should be the same as login information used in step 6 of Download and run the sample app in Getting started with elastic database tools.

外部数据源External data sources

若要创建外部数据源,请对 ElasticDBQuery 数据库执行以下命令:To create an external data source, execute the following command on the ElasticDBQuery database:

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
  (TYPE = SHARD_MAP_MANAGER,
  LOCATION = '<server_name>.database.chinacloudapi.cn',
  DATABASE_NAME = 'ElasticScaleStarterKit_ShardMapManagerDb',
  CREDENTIAL = ElasticDBQueryCred,
   SHARD_MAP_NAME = 'CustomerIDShardMap'
) ;

如果使用弹性数据库工具示例创建了分片映射和分片映射管理器,“CustomerIDShardMap”是分片映射的名称。"CustomerIDShardMap" is the name of the shard map, if you created the shard map and shard map manager using the elastic database tools sample. 但是,如果为此示例使用了自定义设置,则它应该是你在应用程序中选择的分片映射名称。However, if you used your custom setup for this sample, then it should be the shard map name you chose in your application.

外部表External tables

通过对 ElasticDBQuery 数据库执行以下命令,创建与分片上的客户表匹配的外部表:Create an external table that matches the Customers table on the shards by executing the following command on ElasticDBQuery database:

CREATE EXTERNAL TABLE [dbo].[Customers]
( [CustomerId] [int] NOT NULL,
  [Name] [nvarchar](256) NOT NULL,
  [RegionId] [int] NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc,
  DISTRIBUTION = SHARDED([CustomerId])
) ;

执行示例弹性数据库 T-SQL 查询Execute a sample elastic database T-SQL query

定义外部数据源和外部表后,可以对外部表使用完整的 T-SQL。Once you have defined your external data source and your external tables you can now use full T-SQL over your external tables.

对 ElasticDBQuery 数据库执行以下查询:Execute this query on the ElasticDBQuery database:

select count(CustomerId) from [dbo].[Customers]

你将注意到,查询会从所有分片聚合结果并提供以下输出:You will notice that the query aggregates results from all the shards and gives the following output:

输出详细信息

将弹性数据库查询结果导入 ExcelImport elastic database query results to Excel

可以将查询结果导入到 Excel 文件。You can import the results from of a query to an Excel file.

  1. 启动 Excel 2013。Launch Excel 2013.

  2. 导航到“数据”功能区 。Navigate to the Data ribbon.

  3. 单击“从其他源”,然后单击“从 SQL Server”。Click From Other Sources and click From SQL Server.

    从其他源导入 Excel

  4. 在“数据连接向导”中,键入服务器名称和登录凭据 。In the Data Connection Wizard type the server name and login credentials. Then click Next.

  5. 在“选择包含所需数据的数据库”对话框中,选择 ElasticDBQuery 数据库。In the dialog box Select the database that contains the data you want, select the ElasticDBQuery database.

  6. 在列表视图中选择“客户”表并单击“下一步”。Select the Customers table in the list view and click Next. 然后单击“完成” 。Then click Finish.

  7. 在“导入数据”窗体中的“请选择该数据在工作簿中的显示方式”下,选择“表”,然后单击“确定”。In the Import Data form, under Select how you want to view this data in your workbook, select Table and click OK.

存储在不同分片中、来自“客户”表的所有行将填入 Excel 工作表 。All the rows from Customers table, stored in different shards populate the Excel sheet.

现在,可以使用 Excel 的强大数据可视化功能。You can now use Excel's powerful data visualization functions. 可以使用包含服务器名称、数据库名称和凭据的连接字符串,将 BI 和数据集成工具连接到弹性查询数据库。You can use the connection string with your server name, database name and credentials to connect your BI and data integration tools to the elastic query database. 请确保支持将 SQL Server 用作工具的数据源。Make sure that SQL Server is supported as a data source for your tool. 可以引用弹性查询数据库和外部表,就如同使用工具连接的任何其他 SQL Server 数据库和 SQL Server 表一样。You can refer to the elastic query database and external tables just like any other SQL Server database and SQL Server tables that you would connect to with your tool.

成本Cost

使用弹性数据库查询功能不会产生额外的费用。There is no additional charge for using the Elastic Database Query feature.

有关价格信息,请参阅 SQL 数据库定价详细信息For pricing information see SQL Database Pricing Details.

后续步骤Next steps