Simplest Hive query just doesn't work

(3 posts) (1 voice)

• Started 1 year ago by ayons
• Latest reply from ayons
• This topic is not resolved

Tags:

No tags yet.

1. 

   ayons
   Member

   I installed Brisk effortlessly on a single node of RHEL 5 Server. My version is beta2.
   I got my hive prompt.
   I created my table:
   create database ayon_brisk_test;
   create table ayon_brisk_test.actions_by_day (dait string,scanned_products_id bigint,action_type_id int,action_success int,actions_count bigint);
   load data local inpath '/tmp/actions_by_day.txt' overwrite into table ayon_brisk_test.actions_by_day;

   Sor far so great!

   I fired a query:
   select dait, count(*) from ayon_brisk_test.actions_by_day group by dait order by dait desc;
   It dumps the whole table!

   select dait, count(*) from ayon_brisk_test.actions_by_day group by dait;
   It dumps the whole table

   select dait, sum(actions_count) from ayon_brisk_test.actions_by_day where action_success=1 group by dait;
   DUMPS THE WHOLE TABLE!

   So the question is:
   How do I make Brisk work?
   At this point Brisk is doing a select * from <table> regardless of what query is given to it.

   Here is the table desc:
   hive> desc formatted actions_by_day;
   OK
   # col_name data_type comment

   dait string None
   scanned_products_id bigint None
   action_type_id int None
   action_success int None
   actions_count bigint None

   # Detailed Table Information
   Database: ayon_brisk_test
   Owner: ayon
   CreateTime: Fri Sep 02 01:07:54 UTC 2011
   LastAccessTime: UNKNOWN
   Protect Mode: None
   Retention: 0
   Location: cfs://null/user/hive/warehouse/ayon_brisk_test.db/actions_by_day
   Table Type: MANAGED_TABLE
   Table Parameters:
   transient_lastDdlTime 1314925949

   # Storage Information
   SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
   InputFormat: org.apache.hadoop.mapred.TextInputFormat
   OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
   Compressed: No
   Num Buckets: -1
   Bucket Columns: []
   Sort Columns: []
   Storage Desc Params:
   serialization.format 1

   Posted 1 year ago #
2. 

   ayons
   Member

   Any kind of filter operation returns empty result set

   hive> select * from actions_by_day where dait = '2011-08-31' limit 10;
   Total MapReduce jobs = 1
   Launching Job 1 out of 1
   Number of reduce tasks is set to 0 since there's no reduce operator
   Starting Job = job_201109020100_0008, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201109020100_0008
   Kill Command = /usr/bin/brisk hadoop job -Dmapred.job.tracker=localhost:8012 -kill job_201109020100_0008
   2011-09-02 01:35:01,755 Stage-1 map = 0%, reduce = 0%
   2011-09-02 01:35:04,779 Stage-1 map = 50%, reduce = 0%
   2011-09-02 01:35:07,806 Stage-1 map = 100%, reduce = 0%
   2011-09-02 01:35:10,969 Stage-1 map = 100%, reduce = 100%
   Ended Job = job_201109020100_0008
   OK
   Time taken: 19.708 seconds

   Posted 1 year ago #
3. 

   ayons
   Member

   Figured it out.

   The load data local infile did not do what I had expected it to do. It did not use \t as the default delimiter (seems obvious now since ^A is the default hive field delimiter). Hence the entire row was just one field instead of 5.

   Fixed it by creating an EXTERNAL TABLE
   CREATE EXTERNAL TABLE IF NOT EXISTS actions_by_day (dait string,scanned_products_id bigint,action_type_id int,action_success int,actions_count bigint) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION 'cfs://null/user/ayon/brisk_test/'

   Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.