Strict Mode HIVE

We have the partition in Hive to save lots of our time whereas performing analysis on the area only we want and not the entire dataset. Although it's true, partitioned off tables usually hold terribly massive data sets that may be growing quickly. And Hive needs to search the entire dataset before giving a result. AN unrestricted partition would possibly consume intolerably large resources over such an oversized table.

But what if the instruction itself is restricted to search partial dataset then provide a result.

This will increase the execution time tremendously. Users are prevented from queries which will scan all partitions.

Yes, this could be done by turning STRICT mode up.

Strict mode is a setting in Hive that forestalls users from issuing queries that would have

unintended and undesirable effects.

In different words, if you'd prefer to run a query across all partitions(full table scan) you'll have to set the mode to 'nonstrict'. Use this property with care, as a result,  it triggers huge MapReduce jobs.

Setting the property hive.mapred.mode to strict disables 3 varieties of queries.

• queries on partitioned tables are not permitted unless they include a partition filter in the WHERE clause
• It restricts ORDER BY operation without a LIMIT clause ( since it uses a single reducer which can choke your processing if not handled properly.
• The third type of query prevented is a Cartesian product

We will see these 3 restriction benefit with handson ahead in this blog.

We can do the setting in 2 ways that (a)Hive CLI(set hive.mapred.mode=strict) OR (b)Property file(Hive-default.xml)

To understand strict mode we need a partitioned table.Let’s create one now.

Commands to create partition table(strict) and inserting some data

create table par_user (first_name varchar(60),lastname varchar(60),roll_no varchar(60)) partitioned by(country varchar(60),state varchar(60)) row format delimited fields terminated by ',' stored as textfile;

load data local inpath '/home/acadgild/usr_info' into table par_user partition (country = 'us',state = 'fl');

load data local inpath '/home/acadgild/usr_info1' into table par_user partition (country = 'ca',state = 'au');

Readers can download data working in above code HERE.

Before the property is set to strict, Below in screenshot we see the data present in my table par_user.

We find our query execution without any interruption, Just imagine the time to execute the same on BigData(which is huge).

FIRST

Queries on partitioned tables are not permitted unless they include a partition

filter in the WHERE clause, limiting their scope.,

If your partitioned table is very large, you could block any full table scan queries by putting Hive into strict mode using the set hive.mapred.mode=strict command. In this mode, when users submit a query that would result in a full table scan (i.e. queries without any partitioned columns) an error is issued.

Commands

hive>set hive.mapred.mode=strict;

hive>select * from par_user where roll_no=4;

error

The following enhancement adds a partition filter—the table partitions—to the

WHERE clause:

hive> select * from par_user where roll_no=4 and state='au';

normal result

The second type of restricted query are those with ORDER BY clauses, but no LIMIT clause.

Because ORDER BY sends all results to a single reducer to perform the ordering, forcing the user to specify a LIMIT clause prevents the reducer from executing for an extended period of time:

hive>select * from par_user where state='au' order by roll_no;

error

To issue this query, add a LIMIT clause:

hive>select * from par_user where state='au' order by roll_no limit 3;

normal result

The third and final type of query prevented is a Cartesian product.

To perform this JOIN operation I have replicated the data in par_user to another table named par_user2

hive>select * from par_user JOIN par_user2;

error

Here is a properly constructed query with JOIN and ON clauses:

hive>set hive.mapred.mode=nonstrict;

hive>select * from par_user JOIN par_user2;

Hope you got the concept and when to use the strict option in hive to reduce execution time. This lead to optimization of hive query.