[Solved] AND clause not working in hive

EverSQL Database Performance Knowledge Base

AND clause not working in hive

I have a table like idsfortime: 

epochtime       id
1392951600  0
1392952500  15
1392953400  30
1392954300  45
1392955200  60

There is another table with the following columns : 

15916B  5.1815954385269 1392977820
15965A  7.16797368783744    1392977880
16272B  10.6633890639568    1392977865
16707A  37.6028010736386    1392977785
16730A  9.42097617868767    1392977866

The last column in the above table denotes epoch time.

I am trying to find out those speeds (column 2 in above table) which lie between epochtime of table idsfortime and below table .

I am using the below query : 

select t.speed from idsfortime t1 JOIN staging t where t1.epochtime >= t.time AND t1.epochtime <= t.time;

But, this doesnt work. Please suggest

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `idsfortime` ADD INDEX `idsfortime_idx_epochtime` (`epochtime`);
ALTER TABLE `staging` ADD INDEX `staging_idx_time` (`time`);
The optimized query:
SELECT
        t.speed 
    FROM
        idsfortime t1 
    JOIN
        staging t 
    WHERE
        t1.epochtime >= t.time 
        AND t1.epochtime <= t.time

Related Articles



* original question posted on StackOverflow here.