How to build a hive table for data separated by delimiter '^ P'

Question

My request:

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'm not sure that “^ P” can be used as a delimiter, but tried it)

As a result, all “none” fields are displayed when I load data into the hive table.

The data looks like this:

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

Please help me.

Answer 1

Here are the options:

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

, , Unicode ('\ u0010'), 2.1, . https://issues.apache.org/jira/browse/HIVE-13434

Answer 2

^P 020. ,

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';