CODEWITHSUNDEEP
hadoophivehiveql
CleanSock
CleanSock

Reputation: 383

Where clause matches but returns null in Hive

I have a table with data in string format. When I am trying to select all the records that match a specific product id, map reduce runs its job and gives OK iin the end without the actual results. I know for a fact that data pertaining to that product id exists in the table. Where am I going wrong? My query is

select * from tablename where product_id='xxxx';

Solved

I figured out why it was happening. For others facing the same problem, try checking your string format. My product_id was saved as "xxxx" in the file. So while querying, if I did select * from tablename where product_id= '"xxxx"'; it worked. I got rid of those extra quotes.

Upvotes: 1

Views: 1592

Answers (3)

agentv
agentv

Reputation: 779

...I ran into this with some data I inherited, and since I was going to do a lot of operations on the data (and because the table was only in the range of 10k records), I did something to it like this:

CREATE TABLE a AS
SELECT 
        regexp_extract(`origin`,  '^.([A-Za-z0-9]*)', 1) as origin,
        regexp_extract(`dest`,  '^.([^"]*)', 1) as dest,
FROM a-gummy

The second of the two regular expressions is the most precise. It asks for a string beginning at the second character of the original field that runs as far as the first " character encountered.

In the case where the data set is too large to simply create a second instance of the table, you might use a view created in this fashion.

Upvotes: 0

woopi
woopi

Reputation: 368

To see a bit better if there are white spaces in your content try something like

select concat('#',product_id,'#') from tablename where product_id like '#%XXX%#';

Upvotes: 0

Carter Shanklin
Carter Shanklin

Reputation: 3047

Often this is because of whitespace which is hard to see, try using trim(product_id) = "xxxx"

Upvotes: 1

Related Questions