数据库中Sql语言的演变:默认的Ansi标准和更容易从数据仓库迁移
2021年11月16日 在bob体育客户端下载平台的博客
今天,我们激动地宣布Databricks SQL将默认使用ANSI标准SQL方言。这是在本月早些时候宣布Databricks SQL的记录设置性能这标志着我们寻求支持开放标准的一个重要里程碑。这篇博客文章讨论了这个更新如何更容易地将数据仓库工作负载迁移到Databricks lakehouse平台。bob体育客户端下载此外,我们很高兴地宣布SQL支持方面的改进,使查询JSON和执行常见任务变得更容易。
轻松迁移到Databricks SQL
我们相信Databricks SQL是数据仓库工作负载的最佳地方,而且应该很容易迁移到它。实际上,这意味着尽可能少地更改SQL代码。为此,我们将默认的SQL方言从Spark SQL切换到Standard SQL,对其进行扩展以增加与现有数据仓库的兼容性,并为SQL查询添加质量控制。
我们都同意的标准SQL
使用SQL标准,在行为或不熟悉的语法方面不需要查找和学习。
字符串连接是一种非常常见的操作,因此SQL标准设计者给了它自己的操作符。双管道操作符比必须执行concat()函数调用更简单:
选择跨度><跨度>o_orderstatus | |跨度>< span风格="color:#183691">' '跨度><跨度>||o_shippriority跨度>< span风格="color:#a71d5d">作为跨度><跨度>order_info跨度><跨度>跨度>< span风格="color:#a71d5d">从跨度><跨度>跨度>订单;
的过滤器子句,自2003年以来一直存在于SQL标准中,它限制了在聚合期间计算的行。大多数数据仓库需要在聚合中嵌套一个复杂的CASE表达式:
选择跨度><跨度>跨度><跨度>跨度>数跨度><跨度>(跨度>< span风格="color:#a71d5d">截然不同的跨度><跨度>o_orderkey)跨度>< span风格="color:#a71d5d">作为跨度><跨度>order_volume,跨度><跨度>跨度>< span风格="color:#0086b3">数跨度><跨度>(跨度>< span风格="color:#a71d5d">截然不同的跨度><跨度>o_orkerkey)跨度>< span风格="color:#a71d5d">过滤器跨度><跨度>(跨度>< span风格="color:#a71d5d">在哪里跨度><跨度>o_totalprice跨度><跨度class="hljs-operator">>跨度><跨度>跨度>< span风格="color:#0086b3">100.0跨度><跨度>)跨度>< span风格="color:#a71d5d">作为跨度><跨度>big_orders跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——使用传递谓词的行跨度><跨度>跨度><跨度>跨度>从跨度><跨度>订单;跨度>
SQL用户定义函数(udf)使扩展和模块化业务逻辑变得容易,而无需学习一门新的编程语言:
创建跨度><跨度>跨度>< span风格="color:#a71d5d">函数跨度><跨度>inch_to_cm (英寸跨度>< span风格="color:#a71d5d">双跨度><跨度>)跨度><跨度>跨度>< span风格="color:#a71d5d">返回跨度><跨度>跨度>< span风格="color:#a71d5d">双跨度><跨度>跨度>< span风格="color:#a71d5d">返回跨度><跨度>跨度>< span风格="color:#0086b3">2.54跨度><跨度>跨度><跨度class="hljs-operator">*跨度><跨度>英寸;跨度><跨度>跨度>< span风格="color:#a71d5d">选择跨度><跨度>在ch_to_cm (跨度>< span风格="color:#0086b3">5跨度><跨度>);跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——返回12.70跨度><跨度>跨度>
与其他数据仓库的兼容性
在迁移过程中,通常会将数百甚至数千个查询移植到Databricks SQL。现有数据仓库中的大多数SQL都可以放入其中,并且只能在Databricks SQL上工作。为了使这个过程对客户来说更简单,我们继续添加SQL特性,以消除重写查询的需要。
例如,新的合格条款简化筛选窗口函数,使其更容易从Teradata迁移。下面的查询可以找到每天消费最高的5位客户:
选择跨度><跨度>跨度>o_orderdate,o_custkey,<跨度>跨度>< span风格="color:#0086b3">排名跨度><跨度>(跨度>< span风格="color:#0086b3">总和跨度><跨度>(o_totalprice))跨度>< span风格="color:#a71d5d">在跨度><跨度>(跨度>< span风格="color:#a71d5d">分区跨度><跨度>跨度>< span风格="color:#a71d5d">通过跨度><跨度>o_orderdate跨度>< span风格="color:#a71d5d">订单跨度><跨度>跨度>< span风格="color:#a71d5d">通过跨度><跨度>跨度>< span风格="color:#0086b3">总和跨度><跨度>(o_totalprice)跨度>< span风格="color:#a71d5d">DESC跨度><跨度>)跨度>< span风格="color:#a71d5d">作为跨度><跨度>排名跨度><跨度>跨度>< span风格="color:#a71d5d">从跨度><跨度>订单跨度><跨度>跨度>< span风格="color:#a71d5d">集团跨度><跨度>跨度>< span风格="color:#a71d5d">通过跨度><跨度>o_orderdate, o_custkey跨度><跨度>资格等级跨度>< span风格="color:#408080;font-style:italic">——应用于window函数之后跨度><跨度>跨度>
我们将在未来几个月继续增加兼容性功能。如果您想要我们添加一个特定的SQL特性,请不要犹豫与我们联系。
SQL的质量控制
随着ANSI SQL方言的采用,Databricks SQL现在可以主动提醒分析师有问题的查询。这些查询并不常见,但最好尽早发现,这样您就可以保持湖屋的新鲜和高质量的数据。下面是此类更改的选择(参见我们的文档完整的列表)。
- 将STRING转换为INTEGER时输入值无效
- 导致溢出的算术操作
- 除以0
轻松高效地查询和转换JSON
如果您是一名分析师或数据工程师,您很可能使用过JSON形式的非结构化数据。Databricks SQL本身支持摄取、存储和高效查询JSON。在这个版本中,我们很高兴地宣布改进,使分析人员查询JSON比以往任何时候都更容易。
让我们看一个简单的例子查询JSON以现代的方式。在下面的查询中,原始列包含一个JSON blob。如上所示,我们可以在执行类型转换时查询并轻松地从数组中提取嵌套字段和项:
选择跨度><跨度>跨度><跨度>生:customer.full_name,跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——嵌套字段跨度><跨度>跨度><跨度>生:customer.addresses (跨度>< span风格="color:#0086b3">0跨度><跨度>),跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——数组< / span >跨度><跨度>跨度><跨度>生:customer.age::跨度>< span风格="color:#0086b3">整数跨度><跨度>,跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——type cast跨度><跨度>跨度><跨度>跨度>从跨度><跨度>customer_data;跨度>
使用Databricks SQL,您可以轻松地运行这些查询,而不会牺牲性能,也不必将列从JSON中提取到单独的表中。这只是我们为让分析师的生活更轻松而感到兴奋的一种方式。
用于常见任务的简单、优雅的SQL
我们还花时间对SQL支持进行了大扫除,以简化其他常见任务。有太多的新功能无法在一篇博客文章中介绍,但这里有一些最受欢迎的功能。
不区分大小写的字符串比较现在更容易:
选择跨度><跨度>跨度><跨度>跨度>*跨度><跨度>跨度><跨度>跨度>从跨度><跨度>跨度>订单<跨度>跨度>< span风格="color:#a71d5d">在哪里跨度><跨度>跨度><跨度>o_orderpriority我喜欢跨度>< span风格="color:#183691">%紧急的跨度><跨度>;跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——不区分大小写的字符串比较跨度><跨度>跨度>
共享窗口框架使您不必重复一个WINDOW子句。考虑下面的例子,我们重用winwindow框架来计算一个表的统计数据:
选择跨度><跨度>跨度><跨度>圆(跨度>< span风格="color:#0086b3">avg跨度><跨度>(o_totalprice)跨度>< span风格="color:#a71d5d">在跨度><跨度>赢了,跨度>< span风格="color:#0086b3">1跨度><跨度>)跨度>< span风格="color:#a71d5d">作为跨度><跨度>价格,跨度><跨度>圆(跨度>< span风格="color:#0086b3">avg跨度><跨度>(o_totalprice)跨度>< span风格="color:#a71d5d">在跨度><跨度>赢了,跨度>< span风格="color:#0086b3">1跨度><跨度>)跨度>< span风格="color:#a71d5d">作为跨度><跨度>avg_price,跨度><跨度>跨度>< span风格="color:#0086b3">最小值跨度><跨度>(o_totalprice)跨度>< span风格="color:#a71d5d">在跨度><跨度>赢得跨度>< span风格="color:#a71d5d">作为跨度><跨度>min_price,跨度><跨度>跨度>< span风格="color:#0086b3">马克斯跨度><跨度>(o_totalprice)跨度>< span风格="color:#a71d5d">在跨度><跨度>赢得跨度>< span风格="color:#a71d5d">作为跨度><跨度>max_price,跨度><跨度>跨度>< span风格="color:#0086b3">数跨度><跨度>(跨度>< span风格="color:#0086b3">1跨度><跨度>)跨度>< span风格="color:#a71d5d">在跨度><跨度>赢得跨度>< span风格="color:#a71d5d">作为跨度><跨度>order_count跨度><跨度>跨度>< span风格="color:#a71d5d">从跨度><跨度>订单跨度><跨度>跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——这是一个共享的窗口框架跨度><跨度>跨度><跨度>跨度>窗口跨度><跨度>赢得跨度>< span风格="color:#a71d5d">作为跨度><跨度>(跨度>< span风格="color:#a71d5d">订单跨度><跨度>跨度>< span风格="color:#a71d5d">通过跨度><跨度>o_orderdate跨度>< span风格="color:#a71d5d">行跨度><跨度>跨度>< span风格="color:#a71d5d">之间的跨度><跨度>跨度>< span风格="color:#0086b3">2跨度><跨度>前跨度>< span风格="color:#a71d5d">和跨度><跨度>跨度>< span风格="color:#0086b3">2跨度><跨度>后);跨度>
多值插入可以轻松地将多个值插入到一个表中,而不必使用UNION关键字,这是大多数其他数据仓库常见的:
创建跨度><跨度>跨度>< span风格="color:#a71d5d">表格跨度><跨度>员工跨度><跨度>(姓名字符串,部门字符串,薪水跨度>< span风格="color:#0086b3">INT跨度><跨度>、年龄跨度>< span风格="color:#0086b3">INT跨度><跨度>);跨度><跨度>跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——这是一个多值INSERT跨度><跨度>跨度><跨度>跨度>插入跨度><跨度>跨度>< span风格="color:#a71d5d">成跨度><跨度>员工跨度><跨度>跨度>< span风格="color:#a71d5d">值跨度><跨度>(跨度>< span风格="color:#183691">“丽莎”跨度><跨度>,跨度>< span风格="color:#183691">“销售”跨度><跨度>,跨度>< span风格="color:#0086b3">10000跨度><跨度>,跨度>< span风格="color:#0086b3">35跨度><跨度>),跨度><跨度>(跨度>< span风格="color:#183691">“伊万”跨度><跨度>,跨度>< span风格="color:#183691">“销售”跨度><跨度>,跨度>< span风格="color:#0086b3">32000跨度><跨度>,跨度>< span风格="color:#0086b3">38跨度><跨度>),跨度><跨度>(跨度>< span风格="color:#183691">“弗雷德”跨度><跨度>,跨度>< span风格="color:#183691">“工程”跨度><跨度>,跨度>< span风格="color:#0086b3">21000跨度><跨度>,跨度>< span风格="color:#0086b3">28跨度><跨度>);跨度>
Lambda函数是可以传递给某些SQL函数以控制其行为的参数化表达式。函数传递了一个lambda变换函数,将数组的索引和值连接在一起(它们本身是Databricks SQL中的结构化类型的一个例子)。
< span风格=跨度>< span风格="color:#183691">”颜色:# a56319;“跨度><跨度>>--跨度>< span风格="color:#0086b3">这跨度><跨度>查询返回[跨度>< span风格="color:#183691">“0:“跨度><跨度>,跨度>< span风格="color:#183691">“1:b”跨度><跨度>,跨度>< span风格="color:#183691">“2:c”跨度><跨度>) span >跨度>选择变换(<跨度>阵列(跨度>< span风格="color:#183691">“一个”跨度><跨度>,跨度>< span风格="color:#183691">“b”跨度><跨度>,跨度>< span风格="color:#183691">“c”跨度><跨度>),跨度><跨度>(x, i) -> i::string ||跨度>< span风格="color:#183691">“:”跨度><跨度>||x < span风格="color:#183691">”颜色:# a56319;“跨度><跨度>>--跨度>< span风格="color:#0086b3">这跨度><跨度>是跨度><跨度class="hljs-function" style="color:#a71d5d">函数跨度><跨度class="hljs-function">跨度><跨度class="hljs-function" style="color:#458;font-weight:bold">跨度跨度><跨度class="hljs-function">>跨度><跨度class="hljs-function">)跨度><跨度>;跨度>
使用标准SQL轻松更新数据
数据很少是静态的,根据另一个表的变化更新一个表是很常见的。我们使用现代的标准SQL语法,使用户可以轻松地删除表中的重复数据,创建缓慢变化的数据等等。
让我们来看看更新客户表有多简单,并在新数据到达时合并:
合并跨度><跨度>跨度>< span风格="color:#a71d5d">成跨度><跨度>客户跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——目标表跨度><跨度>跨度><跨度>跨度>使用跨度><跨度>customer_updates跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——更新的源表跨度><跨度>跨度><跨度>跨度>在跨度><跨度>customers.customer_id跨度><跨度class="hljs-operator">=跨度><跨度>customer_updates.customer_id跨度><跨度>跨度>< span风格="color:#a71d5d">当跨度><跨度>匹配跨度>< span风格="color:#a71d5d">然后跨度><跨度>跨度><跨度>更新跨度>< span风格="color:#a71d5d">集跨度><跨度>customers.address跨度><跨度class="hljs-operator">=跨度><跨度>customer_updates.address跨度>
不用说,这个功能不会牺牲性能,因为表更新非常快。您可以了解更多关于更新、合并和删除表中的数据的功能在这里.
开着它兜风
我们知道语言方言的变化是破坏性的。为了便于推出,我们很高兴地宣布一个新功能,渠道,以帮助客户安全地预览即将到来的变化。
创建或编辑SQL端点时,现在可以选择通道。“当前”通道包含一般可用的特性,而预览通道包含即将到来的特性,如ANSI SQL方言。
要测试ANSI SQL方言,单击左侧导航菜单中的SQL端点,单击一个端点并更改其通道。更改通道将重新启动端点,并且您始终可以稍后恢复此更改。现在可以在这个端点上测试查询和仪表板。
你也可以使用SET命令测试ANSI SQL方言,它只对当前会话启用:
集跨度><跨度>ansi_mode跨度><跨度class="hljs-operator">=跨度><跨度>跨度>< span风格="color:#954121">真正的跨度><跨度>;跨度><跨度class="hljs-operator"><跨度><跨度>跨风格跨度><跨度class="hljs-operator">=跨度><跨度>”颜色:# a56319;“跨度><跨度class="hljs-operator">>跨度>< span风格="color:#408080;font-style:italic">——仅在测试时使用此设置跨度><跨度>跨度><跨度>跨度>选择跨度><跨度>跨度>< span风格="color:#0086b3">投跨度><跨度>(跨度>< span风格="color:#183691">“一个”跨度><跨度>跨度>< span风格="color:#a71d5d">作为跨度><跨度>跨度>< span风格="color:#0086b3">整数跨度><跨度>);跨度>
请注意,我们不建议在生产环境中将ANSI_MODE设置为false。这个参数将在将来被删除,因此您应该仅为测试目的将其临时设置为FALSE。
Databricks的SQL的未来是开放、包容和快速的
Databricks SQL已设置世界表演纪录,通过这些更改,它是符合标准的。我们对这个里程碑感到兴奋,因为它是显著提高可用性和简化从数据仓库到lakehouse平台的工作负载迁移的关键。bob体育客户端下载
请了解更多BOB低频彩变化包括在ANSI SQL方言中。请注意,对于Databricks数据科学和工程工作空间中的现有或新集群,ANSI方言还没有作为默认启用。我们接下来正在努力。