weird error in hive query:SemanticException Failed to breakup Windowing invocations into Groups

1
votes

I want a set of random data from hive, for example row_number between 772001 and 773000.

My sql is as below:

select *  from (
        select *, row_number() over (order by `name`) as row_dsa  
               from  `jck_bonc_demo`.`frjc_jbxx`
       )tmp_table where row_dsa between 772001 and 773000

and I get the following error:

[Cloudera][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.

What can I do for this error, anyone can help?

3
sqlhivehiveql

3 Answers

0
votes

I think this is the syntax you want:

select * 
from (select *, row_number() over (order by `name`) as row_dsa  
      from  `jck_bonc_demo`.`frjc_jbxx`
     ) x
where row_dsa between 772001 and 773000;

You need a subquery to use row_dsa in a where clause.

0
votes

Use select s.*, ... (with table alias) if you want to select all from table plus one more calculated column, not select *. Also, no need to back-quote non-reserved words: 

select * 
from (select s.*, row_number() over (order by name) as row_dsa  
      from jck_bonc_demo.frjc_jbxx s
     ) x
where row_dsa between 772001 and 773000;
0
votes

there is a bug in my program,name is not the col of the specified table,the error message is weird. Tanks for your answer @Gordon Linoff @eftjoin