hive> create external table test_pos_ext2(
> trxno string,
> trx_dt string,
> reporter_id string,
> buyer_id string
> ) row format delimited fields terminated by ','
> LOCATION '/gcw/testing/pos_test_ext/test';
OK
Time taken: 0.153 seconds
hive> select * from test_pos_ext2;
OK
Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.io.FileFormatException: Malformed ORC file hdfs://hdpdevnn/gcw/testing/pos_test_ext/test/pos3.txt. Invalid postscript.
> trxno string,
> trx_dt string,
> reporter_id string,
> buyer_id string
> ) row format delimited fields terminated by ','
> LOCATION '/gcw/testing/pos_test_ext/test';
OK
Time taken: 0.153 seconds
hive> select * from test_pos_ext2;
OK
Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.io.FileFormatException: Malformed ORC file hdfs://hdpdevnn/gcw/testing/pos_test_ext/test/pos3.txt. Invalid postscript.
Solutiion:--
Add - STORED AS TEXTFILE in table while creating.
hive> create external table test_pos_ext2(
> trxno string,
> trx_dt string,
> reporter_id string,
> buyer_id string
> ) row format delimited fields terminated by ','
> STORED AS TEXTFILE
> LOCATION '/gcw/testing/pos_test_ext/test';
hive> select * from test_pos_ext2;
OK
9106956188 3/7/2017 3-HWJW-516 3-2SS-2763
9106956189 3/7/2017 3-HWJW-516 3-2SS-2763
Time taken: 0.12 seconds, Fetched: 2 row(s)
CREATE TABLE NATION (N_NATIONKEY INT,N_NAME VARCHAR(25),N_REGIONKEY INT,N_COMMENT VARCHAR(152)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS ORC tblproperties ("orc.compress"="SNAPPY");
ReplyDeleteOK
Time taken: 0.019 seconds
hive> LOAD DATA LOCAL INPATH '/user/hive/tpch/nation.tbl' INTO TABLE NATION; Copying data from file:/user/hive/tpch/nation.tbl
Copying file: file:/user/hive/tpch/nation.tbl
Loading data to table anujdb.nation
Table anujdb.nation stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 2224, raw_data_size: 0]
OK
Time taken: 0.081 seconds
hive> select * from nation; OK
Failed with exception java.io.IOException:java.io.IOException: Malformed ORC file file:/user/hive/warehouse/anujdb.db/nation/nation.tbl. Invalid postscript.
0|ALGERIA|0| haggle. carefully final deposits detect slyly agai|
ReplyDelete1|ARGENTINA|1|al foxes promise slyly according to the regular accounts. bold requests alon|
2|BRAZIL|1|y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
I have table nation.tbl and i want it store as orc.
ReplyDeleteIf possible any solution