3

I want to load a table with input data into hive. I have data in the following format. 

"153662";"0002241447";"0"
"153662";"000647036X";"0"
"153662";"0020434901";"0"
"153662";"0020973403";"0"
"153662";"0028604202";"0"
"153662";"0030437512";"0"

I want to load this data into a table with two varchar columns and one int column.But the surrounding double quotes trouble me. I have created the following table.

CREATE EXTERNAL TABLE Table(A varchar(50),B varchar(50),C varchar(50))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\;'
LINES TERMINATED BY '\n'

STORED AS TEXTFILE

but the quotes around the field also become part of field as shown below.

"276725"    "034545104X"    "0"
"276726"    "0155061224"    "5"

I want to ignore them. Also I want the third field to be read as INT. Currently it becomes NULL when I provide third field as INT while making table.

nobody
  • 10,892
  • 8
  • 45
  • 63
Madhur Maurya
  • 1,016
  • 4
  • 19
  • 42

3 Answers3

7

You will have to use Csv-Serde for this.

CREATE TABLE Table(A varchar(50),B varchar(50),C varchar(50))
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES 
(
    "separatorChar" = ";",
    "quoteChar"     = "\""
)  
STORED AS TEXTFILE;
nobody
  • 10,892
  • 8
  • 45
  • 63
  • I do not want to use SERDE as I do not fully understand it. Is there any native hive feature for the same ? also I ma geting problems in using it. I get the error below : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot validate serde: org.apache.hadoop.hive.serde2.OpenCSVSerde – Madhur Maurya Jul 27 '16 at 16:13
  • 1
    You will have to use the serde jar that is compatible with the distribution. – nobody Jul 27 '16 at 16:28
  • Note that this SerDe is not supported by Impala. – Vitaly Olegovitch Jan 15 '20 at 10:44
2

Multiple ways to achieve this:

  1. Use CSV serde
  2. Use regex serde- regex "\"(.*)\"\;\"(.*)\"\;\"(.*)\""
  3. Load data to external table then remove double quotes:

CREATE EXTERNAL TABLE source( a string, b String, c String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;' LOCATION 'xyz';

CREATE TABLE destination AS SELECT REGEXP_REPLACE(a,'"',''), REGEXP_REPLACE(b,'"',''), CAST ( REGEXP_REPLACE(c,'"','') AS BIGINT) FROM source;

Rahul Sharma
  • 5,614
  • 10
  • 57
  • 91
0

Hive query to remove double quotes around the string.

Example: col2 value: "my name is, abc"

select col1, (regexp_replace(col2,'"','')) as col2 from table;

Output: my name is, abc

Kaushal
  • 908
  • 1
  • 8
  • 19
steffi stallin
  • 1
  • 2