Could you try it with CP/PPD disabled? set hive.optimize.cp=false; set hive.optimize.ppd=false;
2012/12/13 java8964 java8964 <java8...@hotmail.com>: > Hi, > > I played my query further, and found out it is very puzzle to explain the > following behaviors: > > 1) The following query works: > > select c_poi.provider_str, c_poi.name from (select darray(search_results, > c.rank) as c_poi from nulf_search lateral view explode(search_clicks) > clickTable as c) a > > I get get all the result from the above query without any problem. > > 2) The following query NOT works: > > select c_poi.provider_str, c_poi.name from (select darray(search_results, > c.rank) as c_poi from nulf_search lateral view explode(search_clicks) > clickTable as c) a where c_poi.provider_str = 'POI' > > As long as I add the where criteria on provider_str, or even I added another > level of sub query like following: > > select > ps, name > from > (select c_poi.provider_str as ps, c_poi.name as name from (select > darray(search_results, c.rank) as c_poi from nulf_search lateral view > explode(search_clicks) clickTable as c) a ) b > where ps = 'POI' > > any kind of criteria I tried to add on provider_str, the hive MR jobs failed > in the same error I shown below. > > Any idea why this happened? Is it related to the data? But provider_str is > just a simple String type. > > Thanks > > Yong > > ________________________________ > From: java8...@hotmail.com > To: user@hive.apache.org > Subject: RE: Array index support non-constant expresssion > Date: Wed, 12 Dec 2012 12:15:27 -0500 > > > OK. > > I followed the hive source code of > org.apache.hadoop.hive.ql.udf.generic.GenericUDFArrayContains and wrote the > UDF. It is quite simple. > > It works fine as I expected for simple case, but when I try to run it under > some complex query, the hive MR jobs failed with some strange errors. What I > mean is that it failed in HIVE code base, from stuck trace, I can not see > this failure has anything to do with my custom code. > > I would like some help if some one can tell me what went wrong. > > For example, I created this UDF called darray, stand for dynamic array, > which supports the non-constant value as the index location of the array. > > The following query works fine as I expected: > > hive> select c_poi.provider_str as provider_str, c_poi.name as name from > (select darray(search_results, c.index_loc) as c_poi from search_table > lateral view explode(search_clicks) clickTable as c) a limit 5; > POI xxxx > ADDRESS some address > POI xxxx > POI xxxx > ADDRESSS some address > > Of course, in this case, I only want the provider_str = 'POI' returned, and > filter out any rows with provider_str != 'POI', so it sounds simple, I > changed the query to the following: > > hive> select c_poi.provider_str as provider_str, c_poi.name as name from > (select darray(search_results, c.rank) as c_poi from search_table lateral > view explode(search_clicks) clickTable as c) a where c_poi.provider_str = > 'POI' limit 5; > Total MapReduce jobs = 1 > Launching Job 1 out of 1 > Number of reduce tasks is set to 0 since there's no reduce operator > Cannot run job locally: Input Size (= 178314025) is larger than > hive.exec.mode.local.auto.inputbytes.max (= 134217728) > Starting Job = job_201212031001_0100, Tracking URL = > http://blevine-desktop:50030/jobdetails.jsp?jobid=job_201212031001_0100 > Kill Command = /home/yzhang/hadoop/bin/hadoop job > -Dmapred.job.tracker=blevine-desktop:8021 -kill job_201212031001_0100 > 2012-12-12 11:45:24,090 Stage-1 map = 0%, reduce = 0% > 2012-12-12 11:45:43,173 Stage-1 map = 100%, reduce = 100% > Ended Job = job_201212031001_0100 with errors > FAILED: Execution Error, return code 2 from > org.apache.hadoop.hive.ql.exec.MapRedTask > > I am only add a Where limitation, but to my surprise, the MR jobs generated > by HIVE failed. I am testing this in my local standalone cluster, which is > running CDH3U3 release. When I check the hadoop userlog, here is what I got: > > 2012-12-12 11:40:22,421 INFO org.apache.hadoop.hive.ql.exec.SelectOperator: > SELECT > struct<_col0:bigint,_col1:string,_col2:string,_col3:string,_col4:string,_col5:string,_col6:boolean,_col7:boolean,_col8:boolean,_col9:boolean,_col10:boolean,_col11:boolean,_col12:string,_col13:string,_col14:struct<lat:double,lon:double,query_text_raw:string,query_text_normalized:string,query_string:string,llcountry:string,ipcountry:string,request_cnt:int,address:struct<country:string,state:string,zip:string,city:string,street:string,house:string>,categories_id:array<int>,categories_name:array<string>,lang_raw:string,lang_rose:string,lang:string,viewport:struct<top_lat:double,left_lon:double,bottom_lat:double,right_lon:double>>,_col15:struct<versions:int,physical_host:string,nose_request_id:string,client_type:string,ip:int,time_taken:int,user_agent:string,http_host:string,http_referrer:string,http_status:smallint,http_size:int,accept_language:string,md5:string,datacenter:string,tlv_map_data_version:string,tlv_devide_software_version:string,csid:int,rid:string,xncrid:string,cbfn:string,sources:array<struct<tm:bigint,tm_date:string,tm_time:string,md5:string,time_taken:int>>>,_col16:array<struct<provider_str:string,name:string,lat:double,lon:double,dyn:boolean,authoritative:boolean,search_center:boolean>>,_col17:array<struct<rank:int,action:int,tm:bigint,event:string,is_csid:boolean,is_rid:boolean,is_pbapi:boolean,is_nac:boolean>>,_col18:string,_col19:struct<rank:int,action:int,tm:bigint,event:string,is_csid:boolean,is_rid:boolean,is_pbapi:boolean,is_nac:boolean>> > 2012-12-12 11:40:22,440 WARN org.apache.hadoop.mapred.Child: Error running > child > java.lang.RuntimeException: Error in configuring object > at > org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:93) > at > org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:64) > at > org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:117) > at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:387) > at org.apache.hadoop.mapred.MapTask.run(MapTask.java:325) > at org.apache.hadoop.mapred.Child$4.run(Child.java:270) > at java.security.AccessController.doPrivileged(Native Method) > at javax.security.auth.Subject.doAs(Subject.java:396) > at > org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1157) > at org.apache.hadoop.mapred.Child.main(Child.java:264) > Caused by: java.lang.reflect.InvocationTargetException > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:597) > at > org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:88) > ... 9 more > Caused by: java.lang.RuntimeException: Error in configuring object > at > org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:93) > at > org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:64) > at > org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:117) > at org.apache.hadoop.mapred.MapRunner.configure(MapRunner.java:34) > ... 14 more > Caused by: java.lang.reflect.InvocationTargetException > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:597) > at > org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:88) > ... 17 more > Caused by: java.lang.RuntimeException: Map operator initialization failed > at > org.apache.hadoop.hive.ql.exec.ExecMapper.configure(ExecMapper.java:121) > ... 22 more > Caused by: java.lang.RuntimeException: cannot find field results from > [0:_col0, 1:_col1, 2:_col2, 3:_col3, 4:_col4, 5:_col5, 6:_col6, 7:_col7, > 8:_col8, 9:_col9, 10:_col10, 11:_col11, 12:_col12, 13:_col13, 14:_col14, > 15:_col15, 16:_col16, 17:_col17, 18:_col18, 19:_col19] > at > org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils.getStandardStructFieldRef(ObjectInspectorUtils.java:321) > at > org.apache.hadoop.hive.serde2.objectinspector.StandardStructObjectInspector.getStructFieldRef(StandardStructObjectInspector.java:119) > at > org.apache.hadoop.hive.ql.exec.ExprNodeColumnEvaluator.initialize(ExprNodeColumnEvaluator.java:57) > at > org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.initialize(ExprNodeGenericFuncEvaluator.java:77) > at > org.apache.hadoop.hive.ql.exec.Operator.initEvaluators(Operator.java:878) > at > org.apache.hadoop.hive.ql.exec.Operator.initEvaluatorsAndReturnStruct(Operator.java:904) > at > org.apache.hadoop.hive.ql.exec.SelectOperator.initializeOp(SelectOperator.java:60) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:433) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:389) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeOp(Operator.java:374) > at > org.apache.hadoop.hive.ql.exec.LateralViewJoinOperator.initializeOp(LateralViewJoinOperator.java:109) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:433) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:389) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeOp(Operator.java:374) > at > org.apache.hadoop.hive.ql.exec.UDTFOperator.initializeOp(UDTFOperator.java:85) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:433) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:389) > at > org.apache.hadoop.hive.ql.exec.SelectOperator.initializeOp(SelectOperator.java:62) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:433) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:389) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeOp(Operator.java:374) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:433) > at > org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:389) > at > org.apache.hadoop.hive.ql.exec.TableScanOperator.initializeOp(TableScanOperator.java:133) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.MapOperator.initializeOp(MapOperator.java:444) > at > org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:357) > at > org.apache.hadoop.hive.ql.exec.ExecMapper.configure(ExecMapper.java:98) > ... 22 more > 2012-12-12 11:40:22,443 INFO org.apache.hadoop.mapred.Task: Runnning cleanup > for the task > > Of course, this whole table is built on very complex nested (struct + array > ....., you can see the struct in the top part of stuck trace) structure > with my own custom InputFormat/SerDe class. But everything so far works > fine. I can query this table anyway I want without seeing errors. This is > the first problem I met when I tried to query it in a way I want, but is > given an error I am not sure the reason. If you need more information, I > will be happy to provide, but right now I am stuck with the above error, and > not sure is it a HIVE bug, or I did something wrong. > > Thanks > > Yong > > ________________________________ > From: java8...@hotmail.com > To: user@hive.apache.org > Subject: Array index support non-constant expresssion > Date: Tue, 11 Dec 2012 17:24:52 -0500 > > Hi, In our project to use the HIVE on CDH3U4 release (Hive 0.7.1), I have a > hive table like the following: > > Table foo ( > search_results array<struct<id:bigint,..............>> > search_clicks array<struct<index_loc:int, ..............>> > ) > > As you can see, the 2nd column, which represents the list of search results > clicked, contains the index location of which results was clicked (starting > from 0). Now I need to find out the click count of some IDs, but this IDs > only exists in the search_result struct. > > When I tried to write a query for this, I will try to do following first: > > > select search_results[c.index_loc] from foo lateral view > explode(search_clicks) clickTable as c; > > But it won't work in hive, as the following error message returned: > AILED: Error in semantic analysis: Line 1:7 Non-constant expressions for > array indexes not supported rank. > > It looks like that currently Hive (at least 0.7) does NOT support > non-constant expressions as the array index. > > I searched on google, and found out the following HIVE jira ticket: > > https://issues.apache.org/jira/browse/HIVE-1955 > > It looks like someone has the same request to support it, but not yet. > > But there is a comment in the above ticket that it can be done in an UDF. My > question is, can anyone share some ideas about how to archive this in an > UDF, as it maybe the only option for me right now? > > Thanks
