Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Need help running query over "yesterday". getting "No partition predicate found" errors


Copy link to this message
-
Re: Need help running query over "yesterday". getting "No partition predicate found" errors
Hi Matthew,
unix_timestamp() is being executed on the Hive server in your case. Therefore, as your query progresses, the timestamp returned by your unix_timestamp() would change. Based on your usage, this would lead to unsuspecting problems when this queries runs across UTC midnight. In any case, I'd recommend that you get your Hive client code to get the present unix_timestamp and generate your partition pruning constant(s) on the client before you send your query to the server. When you do so, partition pruning will occur as expected.

Mark

----- Original Message -----
From: "Matthew Hooker" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Monday, June 4, 2012 8:01:26 PM
Subject: Need help running query over "yesterday". getting "No partition predicate found" errors

Hello,
I'm trying to construct a query which will do some simple counts over a table of events, and insert them in to a summary table.
The query I came up with looks something like

INSERT OVERWRITE TABLE activity
PARTITION(date_utc)
select count(1) views,
from impressions

where dt >= date_sub(to_date(from_unixtime(unix_timestamp())),1)
and dt < to_date(from_unixtime(unix_timestamp()))
group by d_theme;
This seemed to work fine, but I realized it was doing a full table scan, and not using the partitions in "views" appropriately.
I set hive.mapred.mode=strict and the above query fails to run.
If I change the expressions in the where clause to literals, it works as I would expect.
Can anyone help me figure out how to do what I want?
I put together a test script to illustrate my problem:

set hive.mapred.mode=strict;
set hive.optimize.ppd=true;
DROP TABLE IF EXISTS test_where_expr;
CREATE TABLE test_where_expr (
ts int
)
PARTITIONED BY ( dt STRING );
explain SELECT * from test_where_expr WHERE dt = to_date(date_sub(from_unixtime(unix_timestamp()),1));
explain SELECT * from test_where_expr WHERE dt = '2012-06-01';
This is the output:

hive> set hive.mapred.mode=strict;
hive> set hive.optimize.ppd=true;
hive>
> DROP TABLE IF EXISTS test_where_expr;
OK
Time taken: 3.405 seconds
hive>
> CREATE TABLE test_where_expr (
> ts int
> )
> PARTITIONED BY ( dt STRING );
OK
Time taken: 0.189 seconds
hive>
> explain SELECT * from test_where_expr
> WHERE dt = to_date(date_sub(from_unixtime(unix_timestamp()),1));
FAILED: Error in semantic analysis: No partition predicate found for Alias "test_where_expr" Table "test_where_expr"
hive>
> explain SELECT * from test_where_expr
> WHERE dt = '2012-06-01';
OK
ABSTRACT SYNTAX TREE:
(TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME test_where_expr))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (= (TOK_TABLE_OR_COL dt) '2012-06-01'))))
STAGE DEPENDENCIES:
Stage-0 is a root stage
STAGE PLANS:
Stage: Stage-0
Fetch Operator
limit: -1
Time taken: 0.166 seconds
hive> hadoop@ip-10-68-190-136:~$ hive --version
Hive version 0.8.1.
hadoop@ip-10-68-190-136:~$ hadoop -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03)
Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02, mixed mode)

Thanks,
--Matthew Hooker
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB