Hive 严格模式

定义

hive提供了一个严格模式，可以防止用户执行那些可能产生意想不到的不好的效果的查询。即某些查询在严格在严格模式下无法执行，通过设置hive.mapred.mode的值为strict，可以禁止以下3种类型的查询：

设置方法

//设置严格模式
set hive.mapred.mode=strict;
//设置非严格模式
set hive.mapred.mode=nostrict;

以下三种查询场景在严格模式下会报错

1、分区表查询需过滤分区

select * from dwd_db.user_info;

提示

com.cloudera.hiveserver2.support.exceptions.GeneralException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 10056, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10056]:
 Queries against partitioned tables without a partition filter are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.no.partition.filter to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features.
 No partition predicate for Alias "dwd_db.user_info" Table "user_info":17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:335, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:207, org.apache.hive.service ...

2. 出现笛卡尔积

select *
from zb_dim.dim_prov_info p
         join dim_area_info p1 on p.prov_id = p.prov_id;

提示

com.cloudera.hiveserver2.support.exceptions.GeneralException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS,
 infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Cartesian products are disabled for safety reasons.
 If you know what you are doing, please set hive.strict.checks.cartesian.product to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features.:17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:335, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:207, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290, org.apache.hive.service.cli.operation.Operation:run:Operation.java:266, org.apache.hive.service.cli.session.HiveSessionImpl: ...

3、order by 子句不加limit

select *
from zb_dim.dim_prov_info
order by prov_id

提示

com.cloudera.hiveserver2.support.exceptions.GeneralException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, 
infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException 1:938 Order by-s without limit are disabled for safety reasons.
 If you know what you are doing, please set hive.strict.checks.orderby.no.limit to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features.. Error encountered near token 'zb_dim_dim_provprov_id':17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:335, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:207, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290, org.apache.hive.service.cli.operation.Operation:run:Operat ...

Hive 严格模式总结：

1. 对分区表查询必须带分区条件，否则会查询失败;
2. 带order by的查询，必须使用limit限制查询数据条数，否则会查询失败;
3. 不能进行笛卡尔积的查询;
4. 查询条件里面字段类型赋值时必须一致，比如日期分区dt字段类型为字符串，那么分区条件必须指定为dt='20221203'，而不能用dt=20221203;
5. 在生成动态分区时，会失败，需要单独设置为非严格模式 ;