Databricks 第9篇：Spark SQL 基础（数据类型、NULL语义）

Spark SQL 支持多种数据类型，并兼容Python、Scala等语言的数据类型。

一，Spark SQL支持的数据类型

整数系列：

• BYTE, TINYINT：表示1B的有符号整数
• SHORT, SMALLINT：表示2B的有符号整数
• INT, INTEGER：表示4B的有符号整数
• LONG, BIGINT：表示8B的有符号整数

小数系列：

• FLOAT, REAL：表示4B的单精度浮点数
• DOUBLE：表示8B的双精度浮点数
• DECIMAL, DEC, NUMERIC：表示任意精度的带符号十进制数字，精确数

日期和时间类型：

• DATE：表示日期
• TIMESTAMP：表示日期和时间
• INTERVAL：表示Calendar Interval

其他类型：

• STRING：文本类型
• BINARY：字节序列
• BOOLEAN：布尔值

复合类型：

• ARRAY<element_type>
• STRUCT<field1_name: field1_type, field2_name: field2_type, …>
• MAP<key_type, value_type>

二，Spark SQL支持的数据类型和pyspark.sql.types 之间的映射关系

• datetime.datetime 对应 TIMESTAMP
• datetime.date 对应 DATE
• list, tuple, array 对应 ARRAY<element_type> 和 STRUCT<field1_name: field1_type, field2_name: field2_type, …>
• dict 对应MAP<key_type, value_type>
• int 或 long 对应整数
• float 对应浮点小数
• decimal.Decimal 对应 精确数
• bool 对应 布尔值
• bytearray 对应 BINARY
• string 对应 文本类型

三，Spark SQL的Date和Timestamp函数

Spark SQL通常使用字符串来表示Date和Timestamp类型的值，字符串要跟Date和Timestamp相互转换，在转换时，可以设置格式参数fmt，按照特定的格式来相互转换。

fmt是格式字符串，由相应的模式来指定格式：

• dd：以两位数字显示月份中的天数
• MM：以两位数字显示月份
• yyyy：以4位数字显示年份
• HH：以2位数字显示24小时制
• ss：以2位数字显示秒数
• S：小数秒

1，表示Date或timestamp

方法1：用字符串转换为Date或timestamp

date '1970-1-03'
timestamp '1970-1-03 04:05:06.78'

方法2：把字符串按照特定的格式转换为Date或timestamp

to_date(date_str[,fmt])
to_timestamp(timestamp_str[,fmt])

方法2：用数字构造Date或timestamp

make_date(year,month,day)
make_timestamp(year,month,day,hour,min,sec[,timezone])

2，把Date和timestamp转换为string

date_format(timestamp,fmt)

举个例子，把Date和timestamp按照特定的格式转换位字符串：

select date_format(date '1970-1-03', "yyyy-MM-dd");
--1970-01-03
select date_format(timestamp '1970-1-03 04:05:06.78', "yyyy-MM-dd HH:mm:ss.SS");
--1970-01-03 04:05:06.78

3，当前的Date和Timestamp

current_date()
current_timestamp()
now()  -- current timestamp

4，提取Date和Timestamp的成分

field：是指year、month、day、hour、minute、second，

source：是指Date或Timestamp

date_part(field,source)

year(date)
month(date)
day(date)
hour(timestamp)
minute(timestamp)
second(timestamp)

5，unixtimestamp

unix timestamp是用数字来表示timestamp

unix_timestamp([timeExp[format]])

from_unixtime(unix_time,format)
to_unix_timestamp(timeExp[,format])

6，Date和Timestamp操作

以day或month为单位来对Date和Timestamp进行操作：

add_months(start_date,num_months)
months_between(timestamp1,timestamp2[,roundOff])

date_add(start_date,num_days)
date_sub(start_date,num_days)
datediff(endDate,startDate)

截断到特定的时间成分：

-- truncate timestamp
date_trunc(fmt,ts)

-- truncate date
trunc(date,fmt)

7，UTC时间

from_utc_timestamp(timestamp,timezone)
to_utc_timestamp(timestamp,timezone)

四，文本（literal）

文本常量是指用文本表示一个固定不变的量，

1，16进制的字节序列

X { 'num [ ... ]' | "num [ ... ]" }

2，Date 和 Timestamp Literal

DATE  'yyyy-[m]m-[d]d[T]' 
TIMESTAMP  'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s.[ms][ms][ms][us][us][us][zone_id]'

五，NULL语义

Spark SQL也支持三值逻辑，任何两个值比较的结果是：True、False和Unknown，NULL代表Unknown（未知值）。

1，比较运算

NULL和任何值（包括NULL）进行比较，返回的都是NULL，为了比较NULL值，Spark提供了一个null-safe的“等于运算符” <=>，该运算符的运算逻辑是：

NULL <=> NULL，返回True

NULL <=> 任意非NULL， 返回False

2，逻辑运算符

Spark支持的逻辑运算符是AND, OR和 NOT

NOT NULL 返回NULL

NULL AND false 返回false，NULL AND true， NULL AND NULL都返回NULL

NULL OR true 返回true， NULL OR NULL，NULL OR false 都返回 NULL

3，判断NULL值的函数

ISNULL(null) 返回true 

ISNAN(null) 返回false

参考文档：

SQL reference for Databricks Runtime 7.x

NULL semantics