1. hive 严格模式

--设置非严格模式（默认）

set hive.mapred.mode=nonstrict;

--设置严格模式

set hive.mapred.mode=strict;

如果为strict 会对三种情况在compile环节作过滤

1. 读取partitioned table ,但没指定partition

2. order by 排序，必须加limit语句

3. 限制笛卡尔积的查询(表关联join不写关联条件)

hive3.0 在subquery和views中没有limit的order by 会被optimizer移除

不移除，及不优化

set hive.remove.orderby.in.subquery=false

以下是实操

set hive.mapred.mode=strict;set hive.remove.orderby.in.subquery;select * from sales_info;select sku_id from sales_info;select sku_id as id from sales_info;--select sku_id  id from sales_info;--返回数组第一个元素select id_array[0] from sales_info;--查数组中每个元素select explode(id_array) from sales_info;--数组各元素分行显示，-- lateral view explode(数组字段) 虚拟表名 as 虚拟表段-- explode(map|array) 列变行select sku_id,sku_name,id_list from sales_info lateral view explode(id_array) ids as id_list;-- set 集合去重   行变列select sku_id,collect_set(id_array) from sales_info group by sku_id;-- list 不去重   行变列select sku_id,collect_list(id_array) from sales_info group by sku_id;select *,concat_ws("_",id_array) from sales_info;select *,concat_ws("_",id_array),split(concat_ws("_",id_array),'_') from sales_info;

describe function extended explode;

结果：

"explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns "

Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode

Function type:BUILTIN