SparkSQL遵循ANSI标准

ANSI简介

ANSI Compliance通常指的是遵循美国国家标准学会(American National Standards Institute, ANSI)制定的标准。在计算机科学和技术领域,这通常涉及到数据库管理系统(DBMS)对于SQL语言的支持程度。

ANSI为SQL(Structured Query Language)制定了多个标准,这些标准定义了如何以一致的方式编写SQL查询和程序。这些标准旨在提高不同数据库系统之间的兼容性,使得基于标准的SQL代码可以在多种不同的数据库平台上运行。

ANSI相关的两个配置

  • spark.sql.ansi.enabled
    这个配置决定了Spark SQL是否遵循ANSI SQL标准。当设置为 true 时,Spark SQL将使用一种更接近ANSI SQL标准的方言进行查询处理。这意味着,在遇到无效的输入时,Spark会抛出异常,而不是简单地返回 null 结果。
  • spark.sql.storeAssignmentPolicy
    这个配置控制着在向表中插入数据时的隐式类型转换行为。当这个配置设置为 ANSI 时,Spark SQL会按照ANSI存储分配规则来进行数据类型转换。

ANSI兼容性与Hive兼容性的区别:

  • ANSI兼容性:更加严格地遵循SQL标准,例如对于无效输入抛出异常。
  • Hive兼容性:通常更宽松,可能会返回 null 而不是抛出异常。

官方详解

参数名默认值解释开始版本
spark.sql.ansi.enabledfalse如果为true,Spark会尝试符合ANSI SQL规范:Spark SQL会对无效操作抛出运行时异常,包括整数溢出错误、字符串解析错误等。Spark将使用不同的类型强制规则来解决数据类型之间的冲突。这些规则始终基于数据类型优先级。3.0.0
spark.sql.storeAssignmentPolicyANSI当将值插入到具有不同数据类型的列中时,Spark将执行类型转换。目前,我们支持3种类型强制规则策略:ANSI、遗留和严格。使用ANSI策略,Spark按照ANSI SQL执行类型强制。在实践中,行为与PostgreSQL基本相同。它不允许某些不合理的类型转换,例如将字符串转换为int或将double转换为boolean。插入数值类型列时,如果值超出目标数据类型的范围,将抛出溢出错误。用遗留策略,Spark允许类型强制,只要它是有效的Cast,这是非常宽松的。例如,允许将字符串转换为int或将double转换为boolean。这也是Spark 2.x中唯一的行为,它与Hive兼容。在严格的政策下,Spark不允许在类型强制中出现任何可能的精度损失或数据截断,例如不允许将double转换为int或decimal转换为double。3.0.0

算术运算

在Spark SQL中,默认情况下,对数值类型(除了decimal类型)进行的算术运算不会检查溢出。这意味着,如果一个操作导致溢出,其结果与在Java/Scala程序中相应的操作相同(例如,如果两个整数的和超过了可表示的最大值,则结果是一个负数)。另一方面,Spark SQL对于decimal类型的溢出会返回 null。当 spark.sql.ansi.enabled 设置为 true 并且在数值和区间算术运算中发生溢出时,它会在运行时抛出算术异常。

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. Use 'try_add' to tolerate overflow and return NULL instead. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT 2147483647 + 1^^^^^^^^^^^^^^SELECT abs(-2147483648);
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
+----------------+
|(2147483647 + 1)|
+----------------+
|     -2147483648|
+----------------+SELECT abs(-2147483648);
+----------------+
|abs(-2147483648)|
+----------------+
|     -2147483648|
+----------------+

类型转换

当 spark.sql.ansi.enabled 设置为 true 时,使用 CAST 语法进行显式类型转换时,如果遇到标准中定义的非法类型转换模式(例如从字符串类型转换到整型),将会在运行时抛出异常。

此外,在ANSI SQL模式下,不允许进行以下类型的转换,而在关闭ANSI模式时这些转换是允许的:

  • Numeric <=> Binary
  • Date <=> Boolean
  • Timestamp <=> Boolean
  • Date => Numeric

CAST表达式中源数据类型和目标数据类型的有效组合如下表所示。“Y”表示组合在语法上有效,没有限制,“N”表示组合无效。

在这里插入图片描述
在上表中,所有具有新语法的CAST都标记为红色Y:

  • CAST(Numeric AS Numeric):如果值超出目标数据类型的范围,则引发溢出异常。
  • CAST(String AS (Numeric/Date/Timestamp/Timestamp_NTZ/Interval/Boolean)):如果值无法解析为目标数据类型,则引发运行时异常。
  • CAST(Timestamp AS Numeric):如果自纪元以来的秒数超出目标数据类型的范围,则引发溢出异常。
  • CAST(Numeric AS Timestamp):如果数值乘以1000000(微秒每秒)超出Long类型的范围,则引发溢出异常。
  • CAST(Array AS Array):如果元素转换时出现异常,则引发异常。
  • CAST(Map AS Map):如果键和值的转换有任何异常,则引发异常。
  • CAST(Struct AS Struct):如果在结构字段的转换过程中出现异常,则引发异常。
  • CAST(Numeric AS String):在将十进制值转换为字符串时,始终使用纯字符串表示法,如果需要指数,则不使用科学记数法。CAST(Interval AS Numerical):如果日时间间隔或年月间隔的微秒数超出目标数据类型的范围,则引发溢出异常。
  • CAST(Numeric AS Interval):如果目标区间结束单位的数值时间超出Int类型(表示年月区间)或Long类型(表示日时间区间)的范围,则引发溢出异常。
-- Examples of explicit casting-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT CAST('a' AS INT)^^^^^^^^^^^^^^^^SELECT CAST(2147483648L AS INT);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.SELECT CAST(DATE'2020-01-01' AS INT);
org.apache.spark.sql.AnalysisException: cannot resolve 'CAST(DATE '2020-01-01' AS INT)' due to data type mismatch: cannot cast date to int.
To convert values from date to int, you can use function UNIX_DATE instead.-- `spark.sql.ansi.enabled=false` (This is a default behaviour)
SELECT CAST('a' AS INT);
+--------------+
|CAST(a AS INT)|
+--------------+
|          null|
+--------------+SELECT CAST(2147483648L AS INT);
+-----------------------+
|CAST(2147483648 AS INT)|
+-----------------------+
|            -2147483648|
+-----------------------+SELECT CAST(DATE'2020-01-01' AS INT)
+------------------------------+
|CAST(DATE '2020-01-01' AS INT)|
+------------------------------+
|                          null|
+------------------------------+-- Examples of store assignment rules
CREATE TABLE t (v INT);-- `spark.sql.storeAssignmentPolicy=ANSI`
INSERT INTO t VALUES ('1');
org.apache.spark.sql.AnalysisException: [INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST] Cannot write incompatible data for table `spark_catalog`.`default`.`t`: Cannot safely cast `v`: "STRING" to "INT".-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
+---+
|  v|
+---+
|  1|
+---+

类型转换时四舍五入规则

  1. 规则如下
  • 当你将一个包含小数部分的 decimal 类型值转换为一个以 SECOND 结尾的 interval 类型时(例如,INTERVAL HOUR TO SECOND),Spark SQL 会对小数部分进行舍入处理。

  • 最近邻舍入 (nearest neighbor): 如果小数部分离两个相邻整数的距离不相等,Spark SQL 会将其舍入到最近的整数。

  • 向上舍入 (round up): 如果小数部分离两个相邻整数的距离相等,则向上舍入到较大的整数。

  1. 示例
    假设我们有一个 decimal 类型的值 3.5,我们需要将其转换为 INTERVAL HOUR TO SECOND 类型。
  • 最近邻舍入: 因为 3.534 的距离相等,所以根据舍入规则,我们会将 3.5 向上舍入到 4
  • 向上舍入: 如果 3.5 被转换成 INTERVAL HOUR TO SECOND 类型,那么它会被舍入为 4 秒。

存储分配

如开头所述,当spark.sql.storeAssignmentPolicy设置为ANSI(默认值)时,spark sql在插入表时符合ANSI存储分配规则。下表给出了表插入中源数据类型和目标数据类型的有效组合。

在这里插入图片描述

  • Spark不支持区间类型的表列。
  • 对于Array/Map/Struct类型,数据类型检查规则递归应用于其组成元素。

在表插入过程中,Spark将在数值溢出时抛出异常。

CREATE TABLE test(i INT);INSERT INTO test VALUES (2147483648L);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow. Use `try_cast` on the input value to tolerate overflow and return NULL instead.

强制类型转换

当 spark.sql.ansi.enabled 设置为 true 时,Spark SQL采用了一系列规则来解决数据类型冲突。在这类冲突解决的核心是类型优先级列表,该列表定义了给定数据类型的值是否可以被隐式提升到另一种数据类型。
在这里插入图片描述

  • 对于最不常见的类型,跳过浮点数以避免精度损失。
  • 字符串可以升级为多种数据类型。请注意,字节/短/整数/十进制/浮点不在此先例列表中。Byte/Short/Int和String之间最不常见的类型是Long,而Decimal/Float之间最不常用的类型是Double。
  • 对于复杂类型,优先级规则递归应用于其组成元素。

特殊规则适用于非类型化NULL。NULL可以升级为任何其他类型。
这是优先级列表作为有向树的图形描述:

在这里插入图片描述

最小公共类型解析

最小公共类型是从一组给定类型中找到的能够被所有类型共同转换到的最窄类型。这里的“窄”通常意味着转换的目标类型尽可能保持原始数据的特性和精度。
类型优先级列表定义了从一种类型转换到另一种类型的顺序。

  1. 用途
  • 函数参数类型推导:对于期望多个参数具有相同类型的函数(如 coalesce, least, 或 greatest),最小公共类型解析用于确定这些参数的类型。
  • 操作数类型推导:对于算术运算或比较操作,最小公共类型解析用于确定参与运算的操作数的类型。
  • 表达式结果类型推导:对于如 case 表达式这样的复合表达式,最小公共类型解析用于确定最终结果的类型。
  • 数组和映射构造器类型推导:对于数组和映射构造器,最小公共类型解析用于确定元素、键或值的类型。
  1. FLOAT 类型的特殊规则
  • 如果最小公共类型解析为 FLOAT 类型,那么对于 float 类型值,如果其中任何类型是 INT, BIGINT, 或 DECIMAL,最小公共类型会被提升至 DOUBLE 类型。
  • 这样做是为了避免由于 FLOAT 类型的精度限制而导致的潜在数字丢失,确保数据转换过程中不会损失重要的数值信息。

通过这种方式,Spark SQL能够确保在处理不同类型数据的交互时,能够根据ANSI SQL标准进行合理的类型转换和数据处理,从而保证数据处理的准确性和一致性。

-- The coalesce function accepts any set of argument types as long as they share a least common type. 
-- The result type is the least common type of the arguments.
> SET spark.sql.ansi.enabled=true;
> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
Error: Incompatible types [INT, DATE]> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)));
ARRAY<BIGINT>
> SELECT typeof(coalesce(1, 1F));
DOUBLE
> SELECT typeof(coalesce(1L, 1F));
DOUBLE
> SELECT (typeof(coalesce(1BD, 1F)));
DOUBLE> SELECT typeof(coalesce(1, '2147483648'))
BIGINT
> SELECT typeof(coalesce(1.0, '2147483648'))
DOUBLE
> SELECT typeof(coalesce(DATE'2021-01-01', '2022-01-01'))
DATE

SQL函数

  1. 函数调用的一般规则
  • 在启用ANSI模式时,Spark SQL中的函数调用会遵循存储分配规则,这意味着函数的输入值会被转换为函数参数声明的类型。
  • 这意味着如果函数参数声明了一个特定的数据类型,那么传递给函数的输入值将被尝试转换为该类型。
  1. 未指定类型的 NULL 值的特殊规则
  • 对于未指定类型的 NULL 值,存在特殊的处理规则。在这种情况下,NULL 值可以被提升为任何其他类型。
  • 这意味着如果一个参数是 NULL 并且没有指定类型,那么它可以在函数调用中被当作任何类型的值来处理。
  • 这种灵活性有助于处理在函数调用中可能出现的不同类型的 NULL 值,从而使得函数调用更加灵活和通用。

通过这种方式,Spark SQL在启用ANSI兼容性模式时,能够更加严格地遵循SQL标准,并在处理函数调用时确保数据类型的正确性和一致性。这有助于在处理复杂的数据转换和函数调用时避免潜在的问题。

> SET spark.sql.ansi.enabled=true;
-- implicitly cast Int to String type
> SELECT concat('total number: ', 1);
total number: 1
-- implicitly cast Timestamp to Date type
> select datediff(now(), current_date);
0-- implicitly cast String to Double type
> SELECT ceil('0.1');
1
-- special rule: implicitly cast NULL to Date type
> SELECT year(null);
NULL> CREATE TABLE t(s string);
-- Can't store String column as Numeric types.
> SELECT ceil(s) from t;
Error in query: cannot resolve 'CEIL(spark_catalog.default.t.s)' due to data type mismatch
-- Can't store String column as Date type.
> select year(s) from t;
Error in query: cannot resolve 'year(spark_catalog.default.t.s)' due to data type mismatch

参考文献

https://spark.apache.org/docs/latest/sql-ref-ansi-compliance.html

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/404769.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

基于vue框架的爱学习分享平台ud317(程序+源码+数据库+调试部署+开发环境)系统界面在最后面。

系统程序文件列表 项目功能&#xff1a;用户,学科分类,交流答疑,论坛交流,学习资料 开题报告内容 基于Vue框架的爱学习分享平台 开题报告 一、项目背景与意义 随着互联网技术的飞速发展&#xff0c;知识的获取与传播方式正经历着前所未有的变革。在线教育平台逐渐成为满足…

如何理解:进程控制

文章目录 前言&#xff1a;进程创建&#xff1a;进程终止&#xff1a;如何终止进程&#xff1f;进程等待非阻塞等待&#xff1a; 总结&#xff1a; 前言&#xff1a; ​ 对于前面的地址空间的学习&#xff0c;我们现在了解到原来所谓变量的地址其实是虚拟地址&#xff0c;该虚…

【数学建模备赛】Ep05:斯皮尔曼spearman相关系数

文章目录 一、前言&#x1f680;&#x1f680;&#x1f680;二、斯皮尔曼spearman相关系数&#xff1a;☀️☀️☀️1. 回顾皮尔逊相关系数2. 斯皮尔曼spearman相关系数3. 斯皮尔曼相关系数公式4. 另外一种斯皮尔曼相关系数定义5. matlab的用法5. matlab的用法 三、对斯皮尔曼相…

MySQL(二)——CRUD

文章目录 CRUD新增全列插入指定列插入插入查询结果 查询全列查询指定列查询查询字段为表达式表达式不包含字段表达式包含一个字段表达式包含多个字段 补充&#xff1a;别名去重查询排序条件查询 补充&#xff1a;运算符区间查询模糊查询NULL的查询 分页查询聚合查询聚合函数 分…

C++实现——红黑树

目录 1.红黑树 1.1红黑树的概念 1.2红黑树的性质 1.3红黑树节点的定义 1.4红黑树的插入操作 1.5红黑树的验证 1.6红黑树的删除 1.7红黑树与AVL树的比较 1.8红黑树的应用 1.红黑树 1.1红黑树的概念 红黑树&#xff0c;是一种二叉搜索树&#xff0c;但在每个结点上增加一个存储位…

Chat App 项目之解析(二)

Chat App 项目介绍与解析&#xff08;一&#xff09;-CSDN博客文章浏览阅读76次。Chat App 是一个实时聊天应用程序&#xff0c;旨在为用户提供一个简单、直观的聊天平台。该应用程序不仅支持普通用户的注册和登录&#xff0c;还提供了管理员登录功能&#xff0c;以便管理员可以…

初识指针4の学习笔记

目录 1>>前言 2>>字符指针变量 3>>数组指针变量 4>>函数指针变量 5>>函数指针数组 6>>回调函数是什么&#xff1f; 7>>结语 1>>前言 今天我会继续分享一些我做的笔记&#xff0c;以及我对指针的理解&#xff0c; 后续会…

Vue状态管理工具:vuex

目录 基本概念 使用步骤 核心概念 1.State 2.Getters 3.Mutations 4.Actions 5.Modules 辅助函数 基本概念 基础用法 基本概念 官方&#xff1a;Vuex 是一个专为 Vue.js 应用程序开发的状态管理模式 库。它采用集中式存储管理应用的所有组件的状态&#xff0c;并以…

Android全面解析之context机制(三): 从源码角度分析context创建流程(下)

前言 前面已经讲了什么是context以及从源码角度分析context创建流程&#xff08;上&#xff09;。限于篇幅把四大组件中的广播和内容提供器的context获取流程放在了这篇文章。广播和内容提供器并不是context家族里的一员&#xff0c;所以他们本身并不是context&#xff0c;因而…

查找物理学领域文献的常用数据库

当我们查找文献时如果盲目去各个文献数据库查找不仅浪费时间和精力还不一定能找到自己需要的文献。我们需要对数据库有个简单的了解有方向的去寻找我们研究领域的文献资料&#xff0c;本文就向大家介绍一下查找物理学领域文献的数据库有哪些。 一、物理专业数据库&#xff08;…

Android平台无纸化同屏如何实现实时录像功能

技术背景 我们在做无纸化同屏的时候&#xff0c;好多开发者采集到屏幕、麦克风|扬声器数据&#xff0c;除了需要推RTMP出去&#xff0c;或者启动个轻量级RTSP服务&#xff0c;对外提供个拉流的RTSP URL&#xff0c;别的终端过来拉流&#xff08;小并发场景&#xff09;&#x…

vue3基础ref,reactive,toRef ,toRefs 使用和理解

文章目录 一. ref基本用法在模板中使用ref 与 reactive 的区别使用场景 二. reactive基本用法在模板中使用reactive 与 ref 的区别使用场景性能优化 三. toRef基本用法示例在组件中的应用主要用途对比 ref 和 toRef 四. toRefs基本用法示例在组件中的应用主要用途对比 ref 和 t…

基于Arch的轻量级发行版Archcraft结合内网穿透实现远程SSH连接

文章目录 前言1. 本地SSH连接测试2. Archcraft安装Cpolar3. 配置 SSH公网地址4. 公网远程SSH连接5. 固定SSH公网地址6. SSH固定地址连接 前言 本文主要介绍如何在Archcraft系统中安装Cpolar内网穿透工具,并以实现Windows环境ssh远程连接本地局域网Archcraft系统来说明使用内网…

高性能web服务器详解

一、Web服务的基础介绍 正常情况下单次web服务访问的流程简图&#xff1a; 1.1 Web服务介绍 这里介绍的是 Apache 和 NGINX 1.1.1 Apache 经典的Web服务端 Apache 起初由美国的伊利诺伊大学香槟分校的国家超级计算机应用中心开发 目前经历了两大版本分别是 1.X 和 2.X…

笔试练习day5

目录 游游的you题目解析解法方法一贪心方法二 腐烂的苹果题目解析例子1例子2解法多源BFS最短路径代码代码解析 JZ62 孩子们的游戏(圆圈中最后剩下的数)题目解析解法方法一模拟环形链表模拟数组模拟 方法二递推/递归/动态规划状态表示状态转移方程代码 感谢各位大佬对我的支持,如…

Mysql原理与调优-Mysql的内存结构

1.绪论 前面说过InnoDB每次查询数据或者更新数据&#xff0c;都是先以16kb的大小将数据读取到内存中&#xff0c;然后对内存中的数据页进行操作。为了减少磁盘IO&#xff0c;Innodb的会先单独的申请一块连续的空间&#xff0c;将从磁盘中的数据页缓存到这片内存中。这片内存就…

2D Inpainting 与NeRF 3D重建的多视角一致性问题

一 问题&#xff1a; NeRF依赖于输入图像的一致性。NeRF&#xff08;Neural Radiance Fields&#xff09;在生成三维场景时&#xff0c;依赖于从多个视角拍摄的输入图像之间的一致性来准确地推断场景的三维结构和颜色信息。 具体来说&#xff1a; 多视角一致性&#xff1a; Ne…

Hive3:三种常用的复杂数据类型

一、Array类型 1、数据示例 2、实操 元数据 zhangsan beijing,shanghai,tianjin,hangzhou wangwu changchun,chengdu,wuhan,beijin创建表 CREATE TABLE myhive.test_array(name string, work_locations array<string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t…

LVM 使用以及配置

逻辑卷管理 (LVM) 是一种用于 Linux 系统的存储管理工具&#xff0c;比传统的磁盘分区方法更灵活。LVM 通过将物理存储设备组合成逻辑卷&#xff0c;使得磁盘空间的管理更加动态和便捷。它提供了物理层的抽象&#xff0c;让用户可以创建跨越多个物理磁盘或分区的逻辑卷。 LVM …

2024年软件测试经典面试题(全三篇)【包含答案】做完面试进入大厂不是梦

前言 迎来的便是金九银十&#xff0c;一直想着说分享一些软件测试的面试题&#xff0c;这段时间做了一些收集和整理&#xff0c;下面共有三篇经典面试题&#xff0c;大家可以试着做一下&#xff0c;答案附在后面&#xff0c;希望能帮助到大家。 软件测试经典面试题&#xff0…