Hive error while creating table in ORC format

Question

I am getting error while creating table in hive in ORC format. Below is the create table script.

CREATE TABLE tgt_ebr_agreements_item_notes STORED AS INPUTFORMAT 
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs://ss-pit-
linx-012.abccorp.abc.com:8020/user/hive/warehouse/abc_sqlserver_test.db/abc_sqlserver
_test_TGT_EBR_AGREEMENTS_ITEM_NOTES' TBLPROPERTIES ( 
'COLUMN_STATS_ACCURATE'='false', 'numFiles'='0', 'numRows'='-1', 'orc.compress'='SNAPPY', 'rawDataSize'='-1', 'totalSize'='0') 
AS SELECT price_schedule_item.legal_entity,
         region.rn_descriptor,
         price_schedule_item.account_manager,
         price_schedule_item.agreement_number,
         company.company_name,
         price_schedule_item.vendor,
         price_schedule_item.line_start_date,
         price_schedule_item.line_end_date,
         price_schedule_item.activity,
         price_schedule_item.tracking_status,
         price_schedule_item.product_line,
         price_schedule_item.grade,
         price_schedule_item.Color_Category,
         price_schedule_item.color,
         price_schedule_item.product_item,
         price_schedule_item_price.Release_Quantity,
         price_schedule_item_price.Projected_Volume,
         price_schedule_item_price.List_Price,
         price_schedule_item_price.Target,
         price_schedule_item_price.Guideline1,
         price_schedule_item_price.invoice_price,
         price_schedule_item_price.Requested_Price,
         price_schedule_item_price.Misc_Impacts,
         price_schedule_item_price.Payment_Terms,
         price_schedule_item_price.Net_Price,
         currency_.currency_code,
         Market.Market_Name,
         Applications.application_name,
         price_schedule_item.Delta_Comp_Name,
         price_schedule_item.Delta_Comp_Product,
         price_schedule_item.Delta_Comp_Grade,
         price_schedule_item.Delta_Comp_Price,
         price_schedule_item.Delta_Comp_Evid_Code,
         price_schedule_item.Comp_Bracket,
         Rn_Appointments.Appt_Date,
         Rn_Appointments.Notes,
         Rn_Appointments.Assigned_to_descriptor
  FROM price_schedule_item_price price_schedule_item_price
  JOIN price_schedule_item price_schedule_item
      ON price_schedule_item_price.price_schedule_item_id=price_schedule_item.price_schedule_item_id
  JOIN agreement agreement
      ON price_schedule_item.agreement_id=agreement.agreement_id
  JOIN company company
      ON agreement.company_id=company.company_id
  JOIN currency_ currency_
      ON price_schedule_item.currency_id=currency_.currency_id
  JOIN territory territory
      ON company.territory_id=territory.territory_id
  JOIN region region
      ON territory.region_id=region.region_id left outer
  JOIN Market Market
      ON Market.Market_Id = price_schedule_item.Market_Segment_Id left outer
  JOIN Application Applications
      ON price_schedule_item.application_ID = Applications.application_ID left outer
  JOIN Rn_Appointments Rn_Appointments
      ON price_schedule_item_price.price_schedule_item_id=Rn_Appointments.price_schedule_item_id 
  where   price_schedule_item.tracking_status ='Active' and    price_schedule_item.activity ='Active Price Schedule'
  ORDER BY  region.rn_descriptor,price_schedule_item.account_manager,price_schedule_item.Agreement_number,price_schedule_item.Product_Line, price_schedule_item.grade,price_schedule_item.color

I am getting " FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask"

After checked log, noticed below error.

java.lang.ClassCastException: org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.hive.ql.io.orc.OrcSerde$OrcSerdeRow

Can anyone please help in this regards?

Answer 1

Your table definition is missing SerDe declaration, so Hive uses text as a default.

Use STORED AS ORC, it's equivalent to explicitly specifying input format, output format, and SerDe.