I'm trying run this a query, with a JOIN without ON property.
I'm running the query like:
hive -v -f my_file.hql
I got this message:
In strict mode, cartesian product is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict
I update the hql file with:
set hive.mapred.mode=nonstrict on top of it.
But then I got this message:
SET hive.mapred.mode=nonstrict Query returned non-zero code: 1, cause: Cannot modify hive.mapred.mode at runtime. It is in the listof parameters that can't be modified at runtime
How I can solve this issue?
ps: I want to make this cartesian product.
How I make it happen? Where I can set this variable hive.mapred.mode works ?
As you already know a cartesian product is not allowed in strict mode (and for good reasons). In your use case it seems like you don't have permissions to make changes to these type of hive settings.
To hack around this problem what you could do is the following. First create two new tables
create table new_1 as SELECT *,1 as join_key from table1;
create table new_2 as SELECT *,1 as join_key2 from table2;
Then join these tables on this join_key. The result will be the cartesian product since it will match each row of table1 with each row of table2.
select * from new_1 join new_2 on join_key=join_key2
Just found out that using --hiveconf solves the problem:
hive -v -f my_file.hql --hiveconf hive.mapred.mode=nonstrict
will allow the nonstrict mode specifically for this query.