Databricks 第9篇：Spark SQL 基礎（資料型別、NULL語義）

悅光陰發表於2021-01-25
SparkSQL資料型別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

相關文章