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

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.

Solution

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