Is LIMIT clause in HIVE really random?

sql hive hiveql shark-sql
visakh picture visakh · May 22, 2014 · Viewed 46.2k times · Source

The documentation of HIVE notes that LIMIT clause returns rows chosen at random. I have been running a SELECT table on a table with more than 800,000 records with LIMIT 1, but it always return me the same record.

I'm using the Shark distribution, and I am wondering whether this has got anything to do with this not expected behavior? Any thoughts would be appreciated.

Thanks, Visakh

Answer

user3036342 picture user3036342 · May 22, 2014

Even though the documentation states it returns rows at random, it's not actually true.

It returns "chosen rows at random" as it appears in the database without any where/order by clause. This means that it's not really random (or randomly chosen) as you would think, just that the order the rows are returned in can't be determined.

As soon as you slap a order by x DESC limit 5 on there, it returns the last 5 rows of whatever you're selecting from.

To get rows returned at random, you would need to use something like: order by rand() LIMIT 1

However it can have a speed impact if your indexes aren't setup properly. Usually I do a min/max to get the ID's on the table, and then do a random number between them, then select those records (in your case, would be just 1 record), which tends to be faster than having the database do the work, especially on a large dataset

Related questions

Hive insert query like SQL

I am new to hive, and want to know if there is anyway to insert data into hive table like we do in SQL. I want to insert my data into hive like INSERT INTO tablename VALUES (value1,value2..) I …

Read More
How to get/generate the create statement for an existing hive table?

Assuming you have "table" already in Hive, is there a quick way like other databases to be able to get the "CREATE" statement for that table?

Read More
Combine many tables in Hive using UNION ALL?

I'm trying to append one variable from several tables together (aka row-bind, concatenate) to make one longer table with a single column in Hive. I think this is possible using UNION ALL based on this question ( HiveQL UNION ALL ), but …

Read More