在Hive中实现限制查询 [英] Implementing Limit query in Hive

问题描述

对于我的要求，我必须在配置单元中实现上限和下限。为此，我尝试写这样的查询。

  SELECT * FROM`your_table` LIMIT 0，5 
 SELECT * FROM`your_table` LIMIT 5，5 
  

但hive只支持1个限制，它不支持upper和下限。我尝试过使用RANK（），ROWNUM（）来实现这一点，但没有成功。

任何人都可以帮我解决这个问题。

解决方案

你好，你可以使用Facebook的UDF和rownum功能

从GITHUB下载Facbook UDF
https://github.com/

从UDF项目创建一个jar文件

您可以从配置单元控制台中的本地路径添加jar文件。

 添加JAR s3n：//obfuscated-path/assets/jars/facebook-udfs-1.0.jar; 
 CREATE TEMPORARY FUNCTION NumberRows AS'com.facebook.hive.udf.UDFNumberRows'; 
 
 SELECT 
 A.product_id，
 A.category，
 A.product_name，
 A.brand，
 A.rank_score，
 CAST（NumberRows（A.category）as FLOAT），
 FROM（
 SELECT 
 product_id，
 category，
 product_name，
 brand， 
 A.rank_score 
 FROM 
 source_table 
 DISTRIBUTE BY 
类别
 SORT BY 
类别，rank_score desc 
）A; 
  

更多参考
https://issues.apache.org/jira/browse/HIVE-1545

如何添加行号对于PIG或HIVE中的行？

For my requirement i have to implement upper and lower limit in hive. For that i am trying to write query something like this

SELECT * FROM `your_table` LIMIT 0, 5 
SELECT * FROM `your_table` LIMIT 5, 5 

But hive supports only 1 limit, it's not supporting upper and lower limit. I tried with with other alternatives to achieve this by using RANK(), ROWNUM() but didn't succeeded.

Can anyone please help me to solve this. Thanks in advance.

解决方案

Hi you can use the Facebook UDF and rownum functionality

Download the Facbook UDF's from GITHUB https://github.com/brndnmtthws/facebook-hive-udfs

Create a jar file from the the UDF project

You can add the jar file from the local path in the hive console.

ADD JAR s3n://obfuscated-path/assets/jars/facebook-udfs-1.0.jar;
CREATE TEMPORARY FUNCTION NumberRows AS 'com.facebook.hive.udf.UDFNumberRows';

SELECT 
  A.product_id, 
  A.category, 
  A.product_name, 
  A.brand, 
  A.rank_score,
  CAST(NumberRows(A.category) as FLOAT), 
FROM (
  SELECT 
    product_id, 
    category, 
    product_name, 
    brand,
    A.rank_score
  FROM
    source_table
  DISTRIBUTE BY 
    category 
  SORT BY
    category, rank_score desc
  ) A ;

Some more reference https://issues.apache.org/jira/browse/HIVE-1545

How can I add row numbers for rows in PIG or HIVE?

这篇关于在Hive中实现限制查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！