I want to inject data from local file system to HDFS.I have chosen spooling directory source to that.But i need to write custome event deserelizer to read excel files.How to write that?Any help? ... View more

I have column in hive table like UNIONTYPE<int, double, array<string>, struct<a:int,b:string> This one will results when select that column like following. 1)How to get particular tag/column type eg: struct (tag no: 3) 2)If the selected type is struct ,then how to access fields(filelds of struct) in it {0:1} {1:2.0} {2:["three","four"]} {3:{"a":5,"b":"five"}} {2:["six","seven"]} {3:{"a":8,"b":"eight"}} {0:9} {1:10.0} ... View more

given_data = load '/clickstream/total_hitdata/05/hit_data.tsv' using PigStorage('\t'); filtered = FILTER given_data by ($133!=0); req_cols = foreach filtered generate GetYear(ToDate((chararray)$25,'yyyy-MM-dd HH:mm:ss','GMT')) as year:int,GetMonth(ToDate((chararray)$25,'yyyy-MM-dd HH:mm:ss','GMT')) as month:int,GetDay(ToDate((chararray)$25,'yyyy-MM-dd HH:mm:ss','GMT')) as day:int,($161-1400000000) as time,$343 as cust_id1:chararray,$344 as cust_id2:chararray,$256 as post_page_url,$466 as visit_num:int; gprd = group req_cols by (year,month,day,cust_id1,cust_id2,visit_num); lead_result = foreach gprd { C1 = order req_cols by time ASC; generate flatten(org.apache.pig.piggybank.evaluation.Stitch(C1, org.apache.pig.piggybank.evaluation.Over(C1.time, 'lead', 0, 1, 1, 0))); }; In lead_result relation i used 'lead' function according to my requirement. $8 is the column which is generated by lead function along with old schema.But i unable to cast to anytype.I am getting following error when try to cast to chararray with name my. <line 57, column 4> pig script failed to validate: org.apache.pig.impl.logicalLayer.FrontendException: ERROR 1031: Incompatable field schema: declared is "my:chararray", infered is ":NULL" The following is the schema overall. lead_result: {stitched::year: int,stitched::month: int,stitched::day: int,stitched::time: int,stitched::cust_id1: chararray,stitched::cust_id2: chararray,stitched::post_page_url: bytearray,stitched::visit_num: int,NULL} ... View more

I have two table bucket_small and bucket_big as following hive> show create table bucket_big; OK CREATE TABLE `bucket_big`( `id` int, `student_id` string, `student_name` string, `course_id` int) CLUSTERED BY ( course_id) INTO 4 BUCKETS ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://hdp1.stratapps.com:8020/apps/hive/warehouse/bucket_big' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='true', 'numFiles'='4', 'numRows'='10', 'rawDataSize'='148', 'totalSize'='158', 'transient_lastDdlTime'='1451302285') Time taken: 0.166 seconds, Fetched: 23 row(s) hive> hive> show create table bucket_small; OK CREATE TABLE `bucket_small`( `course_id` int, `course_name` string) CLUSTERED BY ( course_id) INTO 2 BUCKETS ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://hdp1.stratapps.com:8020/apps/hive/warehouse/bucket_small' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='true', 'numFiles'='2', 'numRows'='6', 'rawDataSize'='39', 'totalSize'='45', 'transient_lastDdlTime'='1451302349') Time taken: 0.172 seconds, Fetched: 21 row(s) And i inserted Data as following. hive>insert overwrite table bucket_big select *from table_one; hive>insert overwrite table bucket_small select *from table_two; Now tables have buckets as i expected.And i set the following configurations. hive>set hive.auto.convert.join=true; hive>set hive.auto.convert.sortmerge.join=true; hive>set hive.optimize.bucketmapjoin = true; hive>set hive.optimize.bucketmapjoin.sortedmerge = true; When i run the following query, hive> explain select /*+ MAPJOIN(a) */ b.student_id,a.course_name FROM bucket_small a JOIN bucket_big b ON a.course_id = b.course_id; OK STAGE DEPENDENCIES: Stage-4 is a root stage Stage-3 depends on stages: Stage-4 Stage-0 depends on stages: Stage-3 STAGE PLANS: Stage: Stage-4 Map Reduce Local Work Alias -> Map Local Tables: a Fetch Operator limit: -1 Alias -> Map Local Operator Tree: a TableScan alias: a filterExpr: course_id is not null (type: boolean) Statistics: Num rows: 6 Data size: 39 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: course_id is not null (type: boolean) Statistics: Num rows: 3 Data size: 19 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator condition expressions: 0 {course_name} 1 {student_id} keys: 0 course_id (type: int) 1 course_id (type: int) Stage: Stage-3 Map Reduce Map Operator Tree: TableScan alias: b filterExpr: course_id is not null (type: boolean) Statistics: Num rows: 10 Data size: 148 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: course_id is not null (type: boolean) Statistics: Num rows: 5 Data size: 74 Basic stats: COMPLETE Column stats: NONE Map Join Operator ----------------- condition map: Inner Join 0 to 1 condition expressions: 0 {course_name} 1 {student_id} keys: 0 course_id (type: int) 1 course_id (type: int) outputColumnNames: _col1, _col6 Statistics: Num rows: 5 Data size: 81 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col6 (type: string), _col1 (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 5 Data size: 81 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 5 Data size: 81 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Local Work: Map Reduce Local Work Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink Time taken: 0.299 seconds, Fetched: 70 row(s) hive> It is generating only Map Only join not Bucket Map join or SMB Map join.What is the problem ??? ... View more