Grokbase Groups Hive user April 2012
FAQ

[Hive-user] to change hive delimiter->'||'

Ylyy-1985
Apr 13, 2012 at 1:24 pm
hi, all. I see something strange. the defaule delimiter is \001 and I want to change to '||'. So I just run my command :

CREATE TABLE table1(
channelid INT,serviceno STRING,regid STRING,pageurl STRING,urlext STRING,pagetitle STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '||' stored as textfile;

then I load the txt data(with one record="12||12003||2131||44123||22222||112") into the table, select * from the table, I see that the 6 fields are filled with

"12","","12003","","2131",""

I think the empty value is "\t",which replace the "||", am I right? I think there is some other ways to replace the delimiter and make hive work fine(of cause we can overwrite the inputformat class).


2012-04-13



Best Regards
Andes

Email:ylyy-1985@163.com
reply

Search Discussions

3 responses

  • Tucker, Matt at Apr 13, 2012 at 1:32 pm
    I’m not sure how multiple-character delimiters work with Hive, but you might try escaping each pipe (“\|\|”). I had a similar issue with using pipes in str_to_map().
    Matt Tucker
    From: ylyy-1985
    Sent: Friday, April 13, 2012 9:24 AM
    To: hive_user
    Subject: to change hive delimiter->'||'

    hi, all. I see something strange. the defaule delimiter is \001 and I want to change to '||'. So I just run my command :

    CREATE TABLE table1(
    channelid INT,serviceno STRING,regid STRING,pageurl STRING,urlext STRING,pagetitle STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '||' stored as textfile;

    then I load the txt data(with one record="12||12003||2131||44123||22222||112") into the table, select * from the table, I see that the 6 fields are filled with

    "12","","12003","","2131",""

    I think the empty value is "\t",which replace the "||", am I right? I think there is some other ways to replace the delimiter and make hive work fine(of cause we can overwrite the inputformat class).


    2012-04-13
    ________________________________
    Best Regards
    Andes

    Email:ylyy-1985@163.com
  • Edward Capriolo at Apr 13, 2012 at 1:35 pm
    You can not have a '||' with the delimted serde. Just insert dummy
    columns in your DML.

    id - name
    5 || bob

    create table people ( id int, dummy1 string , name string)

    On Fri, Apr 13, 2012 at 9:30 AM, Tucker, Matt wrote:
    I’m not sure how multiple-character delimiters work with Hive, but you might
    try escaping each pipe (“\|\|”).  I had a similar issue with using pipes in
    str_to_map().

    Matt Tucker

    From: ylyy-1985
    Sent: Friday, April 13, 2012 9:24 AM
    To: hive_user
    Subject: to change hive delimiter->'||'



    hi, all. I see something strange. the defaule delimiter is \001 and I want
    to change to '||'. So I just run my command :



    CREATE TABLE table1(
    channelid INT,serviceno STRING,regid STRING,pageurl STRING,urlext
    STRING,pagetitle STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '||'
    stored as textfile;



    then I load the txt data(with one
    record="12||12003||2131||44123||22222||112") into the table, select * from
    the table, I see that the 6 fields are filled with



    "12","","12003","","2131",""



    I think the empty value is "\t",which replace the "||", am I right? I think
    there is some other ways to replace the delimiter and make hive work fine(of
    cause we can overwrite the inputformat class).





    2012-04-13

    ________________________________

    Best Regards

    Andes



    Email:ylyy-1985@163.com
  • Ylyy-1985 at Apr 13, 2012 at 2:07 pm
    I had tried this, but not work. and the sys works with '|'.

    2012-04-13



    Best Regards
    Andes

    Email:ylyy-1985@163.com



    发件人:Tucker, Matt
    发送时间:2012-04-13 21:32
    主题:RE: to change hive delimiter->'||'
    收件人:"user@hive.apache.org"<user@hive.apache.org>
    抄送:

    I’m not sure how multiple-character delimiters work with Hive, but you might try escaping each pipe (“\|\|”). I had a similar issue with using pipes in str_to_map().
    Matt Tucker
    From: ylyy-1985
    Sent: Friday, April 13, 2012 9:24 AM
    To: hive_user
    Subject: to change hive delimiter->'||'

    hi, all. I see something strange. the defaule delimiter is \001 and I want to change to '||'. So I just run my command :

    CREATE TABLE table1(
    channelid INT,serviceno STRING,regid STRING,pageurl STRING,urlext STRING,pagetitle STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '||' stored as textfile;

    then I load the txt data(with one record="12||12003||2131||44123||22222||112") into the table, select * from the table, I see that the 6 fields are filled with

    "12","","12003","","2131",""

    I think the empty value is "\t",which replace the "||", am I right? I think there is some other ways to replace the delimiter and make hive work fine(of cause we can overwrite the inputformat class).


    2012-04-13



    Best Regards
    Andes

    Email:ylyy-1985@163.com

Related Discussions

Discussion Navigation
viewthread | post