Webhostingtalk资讯网

尝试使用Azure SQL数据仓库

2017-09-05 13:37
作者:admin

Azure SQL数据仓库利用SQL来查询数据,这与SQL Server的TSQL方言之间存在着一些差异,而且查询不一定会以相同的方式进行工作。数据库管理员们(DBA)也需要使用SSDT来访问Azure SQL数据仓库。因此,您值得花费时间来查看Azure SQL数据仓库的产品预览。

您可能已经了解了各种SQL数据仓库概念,以及如何开始使用该服务并连接到数据库。 本文将向您解释如何使用Visual Studio中的SQL Server数据工具(SSDT)来针对SQL数据仓库数据库运行不同类型的T-SQL查询。

本文将创建一个SQL数据仓库数据库,并将对象添加到该数据库中,然后再对数据库进行查询。 如果您想尝试本文中的示例,那您必须拥有一个活跃的Azure订阅,并且设置了一个资源组和V12逻辑SQL服务器

请记住,除非您在Azure提供的30天免费试用期内运行该服务,或者您持有额外的MSDN积分,否则将数据库添加到SQL数据仓库会产生Azure订阅费用。 另外,请确定您在开始之前已经了解了自己的订阅类型和费用结构。当您没有使用数据库时,请确保您暂停了该数据库,从而避免产生意外或不必要的费用。

创建数据库

您可以使用Azure门户、PowerShell或T-SQL 的CREATE DATABASE(创建数据库)语句来创建SQL数据仓库数据库。 尽管SQL数据仓库的CREATE DATABASE语句和用于本地SQL Server或Azure SQL数据库的CREATE DATABASE语句相似,但该版本却遵循着自己的语法:

语法相当一目了然,但您还是需要了解各种选项。 例如,如果您指定了COLLATE(核对)选项,那您就只能从两个核对选项中作出选择了,它们是:默认的SQL_Latin1_General_CP1_CI_AS或Latin1_General_100_CI_AS_KS_WS。

可选的MAXSIZE(最大大小)设置受到了SQL数据仓库定义的特定大小的限制,而其默认值为10,240 GB(10 TB)。 不过,您可以任意指定以下值:250、500、750、1024、 5120、 10240、 20480、30720、40960、51200或 61440。

此外,您必须添加EDITION(版本)选项,而且也必须为该选项指定datawarehouse(数据仓库)值,因为这是唯一可用的SQL数据仓库设置。

另外,您还需要SERVICE_OBJECTIVE选项,其值必须是以下预定义数据仓库单位(DWU)中的任意一个:DW100、DW200、 DW300、 DW400、DW500、 DW600、DW1000、DW1200、 DW1500或 DW2000。

让我们来查看一个集合这些元素的示例。 以下的CREATE DATABASE(创建数据库)语句创建了一个名为SdwDB2的数据库,其最大大小值为250,而DWU设置为DW100:

这就是定义SQL数据仓库数据库的步骤。 在运行语句之后,该服务将为您创建数据库,而这一过程可能需要花费几分钟的时间来完成。

当您在SSDT中使用T-SQL时,请注意IntelliSense在指出语法问题时并不总是准确的。 例如,在上述示例中,我看到IntelliSense将MAXSIZE(最大大小)选项突出显示为不正确的,但是语句却是正常运行的。

我在许多T-SQL语句中都发现过类似的问题,而在每个案例中,除非真的存在错误,否则语句也会正常运行。 请记住,SQL数据仓库仍在开发当中,并且也存在着一些问题有待解决(不过微软还是会向您收取服务费用)。

您还要注意的是,SQL数据仓库并不支持所有的T-SQL语句。 比如,您不能发出DROP DATABASE(删除数据库)语句来移除数据库,而是需要使用PowerShell或Azure门户来完成这一操作。

由于我们讨论的是有关数据库和不受支持的T-SQL语句的话题,所以我还要提醒您的是,USE(使用)语句也不可用于更改数据库内容。 在SSDT中,实现这一目标的最简单方法是在查询选项卡的顶部选择一个不同的数据库。

使用表格

在使用T-SQL语句的SQL数据仓库版本时,您通常会发现语法和在SQL Server或SQL数据库中的语句的实施方式类似。 但是,语句的SQL 数据仓库版本通常不是很广泛,而且有时支持特定于SQL数据仓库的选项,而这正如我们在CREATE DATABASE(创建数据库)语句中所看到的那样。

CREATE TABLE(创建表格)语句也是如此。 它不支持SQL Server或SQL数据库中的许多可用选项,而且也有几个特定于SQL数据仓库的选项。 例如,该语句不支持主键、外键、check约束、唯一约束、唯一索引、计算列、稀疏列、用户定义的数据类型、索引视图、标识、序列、触发器或同义词。 此外,该语句也不支持某些数据类型,如:geometry、geography、hierarchyid等。 同时,CREATE TABLE语句还提供了SQL数据仓库特有或拥有着不同于标准T-SQL处理方式的表格选项。

让我们来查看一个示例,从而了解一些可用的表格选项。以下的CREATE TABLE语句创建了一个FactSales表格,而该表格包含了理论上与数据仓库中的维度相关的几个关键列:

如预期的那样,该表格不包括主键、外键或任何其它禁止元素。 但是,它在WITH子句中却包含着几个有趣的表格选项。

第一个是CLUSTERED COLUMNSTORE INDEX(聚簇COLUMNSTORE索引),它会创建一个包含所有表数据的内存优化索引。这与SQL Server CREATE TABLE语句中的可用CLUSTERED COLUMNSTORE索引选项相似,但却与SQL Server语句不同,因为我们无法在SQL数据仓库表格上创建非聚簇columnstore索引。

下一个表格选项是DISTRIBUTION(分布),它指定了用于跨多个位置(分布)分发数据的方式。 您可以从以下两个选项中进行选择:ROUND_ROBIN或ROUND_ROBIN。 其中,ROUND_ROBIN选项是默认的,并且会在所有分布中均匀分布行。

HASH 选项则通过对指定列(在本示例中指的是Customer Key)中的值进行散列,以便将每个行分配给一个分发,而这有助于连接表格并将某些类型的数据聚合在一起,从而提高查询性能。 有关选择ROUND_ROBIN还是HASH的信息,请参阅以下的Azure帮助主题文章《SQL数据仓库中的表格设计》。

最后一个表格选项是PARTITION(分区),它决定了行在每个分发中的分组和存储方式。 在本示例中,分区是基于ProductKey列和指定的RANGE(范围)选项的。 因此,共有五个分区将被创建而成,而数据会被根据以下规则分类到这些分区之中:

· 分区1: ProductKey 《= 1000

· 分区2: ProductKey 》 1000 且 《= 2000

· 分区 3: ProductKey 》 2000 且 《= 3000

· 分区4:ProductKey 》 3000 且 《= 4000

· 分区5: ProductKey 》 4000

当CREATE TABLE语句运行时,它会将FactSales表格添加到默认模式dbo中。 但是,我们可以使用CREATE SCHEMA(创建模式)语句在SQL数据仓库数据库中创建其它模式。 与其它T-SQL语句一样,该语句的语法不像SQL Server语句那样能支持那么多的选项,但支持的基本选项仍是相同的。 比如,以下的语句就创建了一个名为sales的模式:

现在,我们可以使用ALTER SCHEMA(更改模式)语句将FactSales表转移至sales模式了:

有了SQL数据仓库,ALTER SCHEMA语句就只能用于基本的数据库对象了。 而在使用SQL Server或SQL数据库时,您还可以使用该语句来移动用户定义的数据类型和XML模式集合。

在极少数情况下,您将发现SQL Server或SQL数据库都不支持用于SQL数据仓库的T-SQL语句。 例如,SQL数据仓库提供了RENAME(重命名)语句,它允许您如以下示例所示那样重命名用户表:

该语句将FactSales表的名称更改为FactOrders。 而在SQL Server和SQL数据库中,您必须使用sp_rename系统存储过程来重命名表和用户定义的其它对象。

在创建表格之后,您可能需要添加直接来源于Visual Studio的数据,而不是执行批量加载操作,因为这可能会在之后才进行。 SQL数据仓库支持INSERT(插入)语句以及其它数据修改语言(DML)语句。 与其它类型的语句一样,DML语句往往不比SQL Server或SQL数据库中的语句那么强大,但其还是具备了基本的功能。

比如,在使用SQL数据仓库时,您不能使用公用表表达式来将INSERT语句提前,而且也不能使用单个INSERT语句来进行多行传递。 但是,您仍然可以如以下示例所示的那样将数据传递到特定列或所有列中:

这些语句会将数据插入到目标表中,就像它们在SQL Server中会做的那样。 但正如这些语句所示,您不得不严重依赖Microsoft文档来了解有关如何在不同的平台上实施语句的具体信息。 例如,MSDN帮助主题INSERT(Transact-SQL)描述了在SQL Server、SQL数据库、SQL数据仓库和并行数据仓库中实施的INSERT语句。

使用SELECT语句创建表格

与SQL数据仓库相关的一个有趣的T-SQL难题是微软将其称之为的create table as select (CTAS)语句,它是一个CREATE TABLE语句,会从一个SELECT(选择)语句中导出其模式和初始数据。

据微软所述,CTAS语句是一个完全并行的操作,也是复制所有或部分表格内容的最简单和最快速的方法。 另外,CTAS语句也对创建临时表很有帮助,其正如以下示例所示:

#CustomerSales表格基于一个简单的SELECT语句,该语句可以查找每位客户的总销售额。 用于创建临时表的CTAS语句以WITH子句开头,它与在常规的CREATE TABLE语句中使用的WITH子句的作用相同。 在这种情况下,子句会创建一个聚簇columnstore索引,并将分发类型设置为ROUND_ROBIN。

SQL数据仓库中的临时表类似于SQL Server或SQL数据库中的临时表,但您不能创建全局临时表,也不能根据临时表创建视图。 不过,SQL数据仓库临时表存在于会话级别,这意味着您可以从会话内的任何位置访问这些表格。

除了复制表格和创建临时表之外,CTAS语句对于解决SQL数据仓库不支持的T-SQL选项这一问题也很有帮助,这些选项包括包含ANSI连接的SELECT 。.. INTO(选择。.成)、MERGE(混合)、UPDATE(升级)和DELETE(删除)语句:

· 您只需移植逻辑就可以将SELECT 。.. INTO语句重写为CTAS语句。

· 您可以将MERGE语句替换成多语句事务。请首先从CTAS语句开始,该语句会使用UNIONALL运算符来创建基于连接在一起的SELECT语句的表格。

· 如果UPDATE或DELETE语句包含ANSI连接,那您可以使用多语句事务来替换该语句,该多语句事务会首先根据SELECT连接创建表格,然后会使用该表来创建要修改的表格的隐式联接。

您可以在Azure帮助主题《SQL数据仓库中的Create Table As Select (CTAS)》中找到这三个替代方案以及其相应示例的详细信息。

创建视图和存储过程

与SQL Server和SQL数据库一样,视图会为您提供一种简便方法来抽象使用优化表连接或其它T-SQL元素的基础模式和持续查询。 另外,在SQL数据仓库中创建视图也与在SQL Server或SQL数据库中创建视图相似,其如以下示例所示:

正如您现在可能预测的那样,SQL数据仓库视图并不像SQL Server和SQL数据库中的视图那么强大。 首先,SQL数据仓库视图只能包含元数据,而且您无法创建索引(实例化)视图或可更新视图。 另外,这些视图不支持加密、模式绑定或向DB-Library、ODBC和OLE DB API传送元数据信息。

SQL数据仓库还允许您创建存储过程,不过这些过程也不如SQL Server中的存储过程那么强大。实际上,它们是相当基本的,主要由参数定义和主语句块组成,其如下例所示:

我们在这里正在做的是创建一个根据特定客户ID来检索该客户总销售额的过程。 接着,我们可以使用EXECTUTE(执行)语句来运行该过程,就像在SQL Server中所做的那样:

在此示例中,我们传入了4367 这一客户ID ,从而获取该客户的总销售额。

从其最基本的层面上来说,使用存储过程非常简单,但是您还是要注意SQL数据仓库中存在着一系列的限制。 例如,您无法创建CLR存储过程或临时的、编号的或扩展的过程。而且,这些存储过程也不支持默认的、表值的或只读的参数,也不提供加密或复制选项。

查询SQL数据仓库表

在查询SQL数据仓库数据库时,您还会发现一些其它限制或差异。比如,微软希望您能在单个数据库上运行整个数据仓库工作负载,因为SQL数据仓库不支持跨数据库查询。而且,您使用的所有表格都必须包含在同一个数据库中。

鉴于这种限制,该服务也不支持分布式交易就不足为奇了。 此外,您也不能创建嵌套事务或在事务中设置保存点。

另一T-SQL限制与变量有关。 虽然您可以像在SQL Server中那样定义变量,但是您无法使用SELECT或UPDATE语句为变量赋值。

此外,SQL数据仓库也不支持blob数据类型,这包括varchar(max)和nvarchar(max)。 如果您计划构建将超过8000字节最大值的动态SQL语句,那您必须如以下示例所示的那样在可管理的块中拆分字符串:

虽然这只是一个简单的例子,但它却能为您演示如何拆分T-SQL语句,然后使用EXECUTE语句来连接字符串和完整运行语句。

您在SQL数据仓库中能获得一个无法在SQL Server或SQL数据库中获得的T-SQL元素,即:LABEL(标签)查询选项。 该选项能允许您为查询分配名称,然后您就可以使用该名称来检索与该查询有关的数据了。 如果您要添加标签,那就必须在查询中添加OPTION(选择)子句和LABEL选项,其如以下示例所示:

您可以像我一样自己指定LABEL选项,或者也可以向其添加一个或多个查询提示,如HASH JOIN或MERGE JOIN。 而在运行查询之后,您就可以根据其标签引用查询了。 比如,以下SELECT语句在 sys.dm_pdw_exec_requests动态管理视图中检索了状态和已用的时间:

当我运行该查询时,SELECT语句会输出Completed(完整的) 状态值和为1016(毫秒)的total_elapsed_time(总耗时)值。

SQL数据仓库中的T-SQL

相较于我们在这里介绍的知识而言,查询SQL数据仓库数据库的知识其实还有很多,但本文足以让您好好开展工作了。其中,最重要的一点是您不能仅仅因为您的查询是针对SQL Server数据库运行的就假定它们也是针对SQL数据仓库数据库运行的。 即便SQL数据仓库利用了SQL数据库技术,您针对SQL数据库运行的语句的工作方式也不一定是相同的。 首次开始时,您可能需要经常转到MSDN网站上参阅Transact-SQL参考(数据库引擎)。

由于SQL数据仓库仍在开发当中,并且也像任何云服务那样正在发展之中,所以我们可能会看到T-SQL元素的扩展和改进,并且它们至少会在实际情况下更适用于SQL Server和SQL数据库。不过,即便如此,您现在可做的工作已经有很多了,而且如果您计划使用其数据库,那您也值得预先花费时间来开始熟悉在SQL数据仓库中实施T-SQL的方式。

网友评论
暂无评论!