Inserting data into order_items table partitioned by order_month

hive
#1

–Retail_stage DATABASE has table order_items with input format/output format as AVRO.
–Retail stage database has table orders with input/output format as text.
–Objective is to insert data into order_items table stored in retail_ods table

Command used-
INSERT OVERWRITE TABLE RETAIL_ODS.ORDER_ITEMS PARTITION(ORDER_MONTH)
> Select oi.order_item_order_id,oi.order_item_id,oi.order_item_product_id,oi.order_item_quantity,oi.order_item_subtotal,
> o.order_date, substr(order_date,1,7) order_month from retail_stage.order_items oi join retail_stage.orders o on oi.order_item_order_id=o.order_id;

Error-
java.io.IOException: java.lang.reflect.InvocationTargetException
java.io.IOException:Not a data file

Query is related to the last video of the “Creating tables is hive/impala”

0 Likes

#2

The issue here may be because of the the order in which the the columns are selected in the SELECT clause. The columns must be selected in the same order in which they appear in the retail_ods.order_items table (of course the partition column should come last as above).

0 Likes

#3

It is throwing the same error even after entering names in same order as in the table.
Can this be because of the fact that orders table has input/output format as text and order_items has avro?

0 Likes

#4

I stored the order_items table with io format as text in retail_stage db and then tried to load order_items in retail_ods and it worked.
But in video Durga sir has shown that order_items has avro io format.
@itversity Request you to please help.

0 Likes