Search code examples
hadoopmemoryhivelimit

HIVE: How does 'LIMIT' on 'SELECT * from' work under-the-hood?

Just wondering how does limit work for the following simple query 

select * from T limit 100

Imagine table T has 13 million records

Will the above query:
1. first load all 13 million into memory & display only 100 records in the result set ?
2. Loads only 100 & gives the result set of 100 records

Was searching for it for quite some time now, most of the pages only talk about using the "LIMIT" but not how Hive deals with it under the hood.

Any useful response appreciated.

Solution

  • If no optimizer applied, hive end up scanning entire table. But Hive optimizes this with hive.fetch.task.conversion released as part of HIVE-2925, To ease simple queries with simple conditions and not to run MR/Tez at all.

    Supported values are none, minimal and more.

    none: Disable hive.fetch.task.conversion (value added in Hive 0.14.0 with HIVE-8389)

    minimal: SELECT *, FILTER on partition columns (WHERE and HAVING clauses), LIMIT only

    more: SELECT, FILTER, LIMIT only (including TABLESAMPLE, virtual columns)

    Your question is more likely what happens when minimal or more is set. It just scans through the added files and read rows until reach leastRows() For more refer gitCode, Config and here