hive错误FAILED: SemanticException [Error 10041]: No partition predicate found for

缺乏、安全感 2022-05-25 21:04 170阅读 0赞

FAILED: SemanticException \[Error 10041\]: No partition predicate found for Alias “average\_sale\_num\_per\_7d\_query:temp1” Table “procurement\_forecast\_warehouse\_stock\_daily”  
2018-04-25 17:20:09 \[ INFO \] Traceback (most recent call last):  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver.py”, line 35, in  
2018-04-25 17:20:09 \[ INFO \] task.run()  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver/task.py”, line 300, in run  
2018-04-25 17:20:09 \[ INFO \] engine.start()  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver/engine/engine.py”, line 91, in start  
2018-04-25 17:20:09 \[ INFO \] action\_func()  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver/engine/engine.py”, line 106, in do\_load  
2018-04-25 17:20:09 \[ INFO \] return self.target.load(self.taskconf)  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver/dbhandler/hivehandler.py”, line 180, in load  
2018-04-25 17:20:09 \[ INFO \] self.execute\_hql(hql)  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver/dbhandler/hivehandler.py”, line 117, in execute\_hql  
2018-04-25 17:20:09 \[ INFO \] process\_shell(cmd)  
2018-04-25 17:20:09 \[ INFO \] File “/home/meicai/www/data/etl/sqlweaver/utils/processutil.py”, line 30, in process\_shell  
2018-04-25 17:20:09 \[ INFO \] raise Exception(“shell execute error. command \[\{cmd\}\] exit with \{returncode\}”.format(\*\*locals()))  
2018-04-25 17:20:09 \[ INFO \] Exception: shell execute error. command \[set -e;set -o pipefail;hive -e “set mapred.job.name=procurement.procurement\_inventory\_rotation\_days\_report;set mapreduce.job.queuename=etl-test;set hive.mapred.mode=strict;use test\_procurement;insert overwrite table procurement\_inventory\_rotation\_days\_report partition (dt=’2018-04-24’) SELECT t.sku\_id AS sku\_id, t.operate\_area AS operate\_area, ROUND(average\_sale\_num\_per\_7d\_query.average\_sale\_num\_per\_7days,2) AS average\_sale\_num\_per\_7days, ROUND(per\_7days\_cat1\_query.sale\_money\_per\_7days\_cat1,2) AS sale\_money\_per\_7days\_cat1, ROUND(per\_7days\_cat2\_query.sale\_money\_per\_7days\_cat2,2) AS sale\_money\_per\_7days\_cat2, ROUND(inventory\_money\_7days\_cat1\_query.inventory\_money\_7days\_cat1,2) AS inventory\_money\_7days\_cat1, ROUND(inventory\_money\_7days\_cat2\_query.inventory\_money\_7days\_cat2,2) AS inventory\_money\_7days\_cat2, ROUND(average\_sale\_num\_per\_30d\_query.average\_sale\_num\_per\_30days,2) AS average\_sale\_num\_per\_30days, ROUND(per\_30days\_cat1\_query.sale\_money\_per\_30days\_cat1,2) AS sale\_money\_per\_30days\_cat1, ROUND(per\_30days\_cat2\_query.sale\_money\_per\_30days\_cat2,2) AS sale\_money\_per\_30days\_cat2, ROUND(inventory\_money\_30days\_cat1\_query.inventory\_money\_30days\_cat1,2) AS inventory\_money\_30days\_cat1, ROUND(inventory\_money\_30days\_cat2\_query.inventory\_money\_30days\_cat2,2) AS inventory\_money\_30days\_cat2, CASE WHEN IF(average\_sale\_num\_per\_30d\_query.average\_sale\_num\_per\_30days is NULL,0,average\_sale\_num\_per\_30d\_query.average\_sale\_num\_per\_30days) = 0 THEN 99999 ELSE ROUND(IF(t.inventory\_number is NULL,0,t.inventory\_number)/average\_sale\_num\_per\_30d\_query.average\_sale\_num\_per\_30days, 2) END AS rotation\_days, CASE WHEN IF(per\_30days\_cat1\_query.sale\_money\_per\_30days\_cat1 is NULL,0,per\_30days\_cat1\_query.sale\_money\_per\_30days\_cat1) = 0 THEN 99999 ELSE ROUND(IF(inventory\_money\_30days\_cat1\_query.inventory\_money\_30days\_cat1 is NULL,0,inventory\_money\_30days\_cat1\_query.inventory\_money\_30days\_cat1)/per\_30days\_cat1\_query.sale\_money\_per\_30days\_cat1, 2) END AS rotation\_days\_cat1, CASE WHEN IF(per\_30days\_cat2\_query.sale\_money\_per\_30days\_cat2 is NULL,0,per\_30days\_cat2\_query.sale\_money\_per\_30days\_cat2) = 0 THEN 99999 ELSE ROUND(IF(inventory\_money\_30days\_cat2\_query.inventory\_money\_30days\_cat2 is NULL,0,inventory\_money\_30days\_cat2\_query.inventory\_money\_30days\_cat2)/per\_30days\_cat2\_query.sale\_money\_per\_30days\_cat2, 2) END AS rotation\_days\_cat2, t.data\_date AS data\_date, t.warehouse\_id AS warehouse\_id, t.city\_id AS city\_id FROM procurement.procurement\_forecast\_warehouse\_stock\_daily t LEFT JOIN ( SELECT AVG(temp1.split\_number) AS average\_sale\_num\_per\_7days, temp1.sku\_id AS sku\_id, temp1.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp1 WHERE temp1.data\_date > DATE\_SUB(‘2018-04-24’,7) AND ‘2018-04-24’ >= temp1.data\_date GROUP BY temp1.sku\_id, temp1.operate\_area ) average\_sale\_num\_per\_7d\_query ON t.sku\_id = average\_sale\_num\_per\_7d\_query.sku\_id AND t.operate\_area = average\_sale\_num\_per\_7d\_query.operate\_area LEFT JOIN ( SELECT SUM(temp2.split\_number \* temp2.intime\_price) / 7 AS sale\_money\_per\_7days\_cat1, temp2.class1\_id AS class1\_id, temp2.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp2 WHERE temp2.data\_date >= DATE\_SUB(‘2018-04-24’,7) AND ‘2018-04-24’ > temp2.data\_date GROUP BY temp2.class1\_id, temp2.operate\_area ) per\_7days\_cat1\_query ON t.class1\_id = per\_7days\_cat1\_query.class1\_id AND t.operate\_area = per\_7days\_cat1\_query.operate\_area LEFT JOIN ( SELECT SUM(temp3.split\_number \* temp3.intime\_price) / 7 AS sale\_money\_per\_7days\_cat2, temp3.class1\_id AS class1\_id, temp3.class2\_id AS class2\_id, temp3.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp3 WHERE temp3.data\_date >= DATE\_SUB(‘2018-04-24’,7) AND ‘2018-04-24’ > temp3.data\_date GROUP BY temp3.class1\_id, temp3.class2\_id, temp3.operate\_area ) per\_7days\_cat2\_query ON t.class1\_id = per\_7days\_cat2\_query.class1\_id AND t.class2\_id = per\_7days\_cat2\_query.class2\_id AND t.operate\_area = per\_7days\_cat2\_query.operate\_area LEFT JOIN ( SELECT SUM(temp4.inventory\_number \* temp4.intime\_price) AS inventory\_money\_7days\_cat1, temp4.class1\_id AS class1\_id, temp4.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp4 WHERE temp4.data\_date = ‘2018-04-24’ GROUP BY temp4.class1\_id, temp4.operate\_area ) inventory\_money\_7days\_cat1\_query ON t.class1\_id = inventory\_money\_7days\_cat1\_query.class1\_id AND t.operate\_area = inventory\_money\_7days\_cat1\_query.operate\_area LEFT JOIN ( SELECT SUM(temp5.inventory\_number \* temp5.intime\_price) AS inventory\_money\_7days\_cat2, temp5.class2\_id AS class2\_id, temp5.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp5 WHERE temp5.data\_date = ‘2018-04-24’ GROUP BY temp5.class1\_id,temp5.class2\_id, temp5.operate\_area ) inventory\_money\_7days\_cat2\_query ON t.class2\_id = inventory\_money\_7days\_cat2\_query.class2\_id AND t.operate\_area = inventory\_money\_7days\_cat2\_query.operate\_area LEFT JOIN ( SELECT AVG(temp6.split\_number) AS average\_sale\_num\_per\_30days, temp6.sku\_id AS sku\_id, temp6.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp6 WHERE temp6.data\_date > DATE\_SUB(‘2018-04-24’,30) AND ‘2018-04-24’ >= temp6.data\_date AND temp6.split\_number > 0 GROUP BY temp6.sku\_id, temp6.operate\_area ) average\_sale\_num\_per\_30d\_query ON t.sku\_id = average\_sale\_num\_per\_30d\_query.sku\_id AND t.operate\_area = average\_sale\_num\_per\_30d\_query.operate\_area LEFT JOIN ( SELECT SUM(temp7.split\_number \* temp7.intime\_price) / 30 AS sale\_money\_per\_30days\_cat1, temp7.class1\_id AS class1\_id, temp7.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp7 WHERE temp7.data\_date >= DATE\_SUB(‘2018-04-24’,30) AND ‘2018-04-24’ > temp7.data\_date GROUP BY temp7.class1\_id, temp7.operate\_area ) per\_30days\_cat1\_query ON t.class1\_id = per\_30days\_cat1\_query.class1\_id AND t.operate\_area = per\_30days\_cat1\_query.operate\_area LEFT JOIN ( SELECT SUM(temp8.split\_number \* temp8.intime\_price) / 30 AS sale\_money\_per\_30days\_cat2, temp8.class1\_id AS class1\_id, temp8.class2\_id AS class2\_id, temp8.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp8 WHERE temp8.data\_date >= DATE\_SUB(‘2018-04-24’,30) AND ‘2018-04-24’ > temp8.data\_date GROUP BY temp8.class1\_id, temp8.class2\_id, temp8.operate\_area ) per\_30days\_cat2\_query ON t.class1\_id = per\_30days\_cat2\_query.class1\_id AND t.class2\_id = per\_30days\_cat2\_query.class2\_id AND t.operate\_area = per\_30days\_cat2\_query.operate\_area LEFT JOIN ( SELECT SUM(temp9.inventory\_number \* temp9.intime\_price) AS inventory\_money\_30days\_cat1, temp9.class1\_id AS class1\_id, temp9.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp9 WHERE temp9.data\_date = ‘2018-04-24’ GROUP BY temp9.class1\_id, temp9.operate\_area ) inventory\_money\_30days\_cat1\_query ON t.class1\_id = inventory\_money\_30days\_cat1\_query.class1\_id AND t.operate\_area = inventory\_money\_30days\_cat1\_query.operate\_area LEFT JOIN ( SELECT SUM(temp10.inventory\_number \* temp10.intime\_price) AS inventory\_money\_30days\_cat2, temp10.class2\_id AS class2\_id, temp10.operate\_area AS operate\_area FROM procurement.procurement\_forecast\_warehouse\_stock\_daily temp10 WHERE temp10.data\_date = ‘2018-04-24’ GROUP BY temp10.class1\_id,temp10.class2\_id, temp10.operate\_area ) inventory\_money\_30days\_cat2\_query ON t.class2\_id = inventory\_money\_30days\_cat2\_query.class2\_id AND t.operate\_area = inventory\_money\_30days\_cat2\_query.operate\_area WHERE t.data\_date = ‘2018-04-24’”\] exit with 57  
2018-04-25 17:20:09 \[ ERROR \] 返回值为： 1 ,13051 执行失败

\*\*出现以上问题是分区表设置了严格模式，查询条件如果没有分区条件，就会出现次错误，解决方式：  
1设置非严格模式  
set hive.mapred.mode=unstrict;  
2加上分区条件\*\*