CREATE TABLE with different separator in hive and impala

Question

I need your help with a table in Hive and Impala. My problem is that I have to insert the following data:

   HD_4K;Number_Channel;ID_Channels;Type;Name_Channel; 

   4K; 45; "1;2;3;4;5;6" ; Series ; Channel 1;  

   HD; 24; "1;6"; Film; Channel 2;

I want to separate the fields by ";" but the "ID_CHANNELS" field is causing me problems since your information also involves ";"

Would anyone have any ideas to solve this problem? Thank you very much in advance :)

CREATE EXTERNAL TABLE channels(
HD_4K String, Number_Channel Int, ID_Channels String, Type String, Name_Channel String ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0059' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '/database/channels/' TBLPROPERTIES("textfile.compress"="snappy");

CURRENT CHANNELS TABLE :(

 HD_4K |    NUMBER_CHANNEL |  ID_CHANNELS   | TYPE  | NAME_CHANNELS  
(String)        (Int)            (String)    (String)     (String)
  4K              45                "1          2            3 
  HD              23                "1         6"          Film
  ""              ""                ""         ""           ""

DREAM CHANNELS TABLE

 HD_4K |    NUMBER_CHANNEL |  ID_CHANNELS   | TYPE  | NAME_CHANNELS  
(String)        (Int)            (String)    (String)     (String)
  4K              45          "1;2;3;4;5;6"   Series   Cook Channel 
  HD              23              "1;6"        Film      Channel 1
  ""              ""                ""         ""           ""

Naga Naga · Accepted Answer · 2019-11-04T20:24:07

You can use OpenCSVSerDe


CREATE EXTERNAL TABLE channels_csv(
HD_4K String, 
Number_Channel Int, 
ID_Channels String, 
Type String, 
Name_Channel String 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ";",
   "quoteChar"     = "\""
)
LOCATION '/path/to/file' 
;

Hope this helps

CREATE TABLE with different separator in hive and impala

1 Answers