How to build a hive table on data which is separated by '^P' delimiter

Question

My query is:

CREATE EXTERNAL TABLE gateway_staging (
  poll int,
  total int,
  transaction_id int,
  create_time timestamp,
  update_time timestamp
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '^P';

(I am not sure whether '^P' can be used as a delimiter but tried it out)

The result is showing all fields 'none' when I load the data into hive table.

The data looks like:

4307421698^P200^P138193920770^P2017-03-08 02:46:18.021204^P2017-03-08 02:46:18.021204

Please help me out.

Answer 1

Here are the options:

• ... fields terminated by '\020' (Octal)
• ... fields terminated by '16' (Decimal)
• ... fields terminated by '\u0010' (Hexadecimal)

Please note that there was a bug related to Unicode literals ('\u0010') that is suppose to be fixed in version 2.1, so using the 3rd option won't work on earlier versions. https://issues.apache.org/jira/browse/HIVE-13434

Answer 2

The octal value of ^P is 020. Try,

CREATE EXTERNAL TABLE gateway_staging (
  poll int,
  total int,
  transaction_id int,
  create_time timestamp,
  update_time timestamp
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\020';