hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: LINES TERMINATED BY only supports newline '\n' right now
Date Thu, 09 Jun 2016 07:23:10 GMT
Same issues.

Dudu

From: abhishek [mailto:ec.abhishek@gmail.com]
Sent: Thursday, June 09, 2016 9:23 AM
To: user@hive.apache.org
Subject: Re: LINES TERMINATED BY only supports newline '\n' right now


Did you try defining the table with hive In built SerDe. 'Stored as ORC'
This should resolve your issue. Plz try and let me know if it works.

Abhi
Sent from my iPhone

On Jun 3, 2016, at 3:33 AM, Markovitz, Dudu <dmarkovitz@paypal.com<mailto:dmarkovitz@paypal.com>>
wrote:
Here is an example, but first – some warnings:


·         You should set textinputformat.record.delimiter not only for the populating of
the table but also for querying it



·         There seems to be many issues around this area –

o   When I tried to insert multiple values in a single statement (“insert into table …
values (…),(…),(…)”) only the first set of values was inserted correctly

o   2 new lines were added to the end of each text (‘lyrics’) although there should be
none.

o   Aggregative queries seems to return null for the last column. Sometimes.

o   The function ‘sentences’ does not work as expected. It treated the whole text as a
single line.



Dudu





Example


hive> create table songs (id int,name string,lyrics string)
;

hive> set textinputformat.record.delimiter='\0'
;

hive> insert into table songs values
(
1
,'All For Leyna'
,'She stood on the tracks
Waving her arms
Leading me to that third rail shock
Quick as a wink
She changed her mind'
)
;

hive> insert into table songs values
(
2
,'Goodnight Saigon'
,'We met as soul mates
On Parris Island
We left as inmates
From an asylum
And we were sharp
As sharp as knives
And we were so gung ho
To lay down our lives'
)
;

hive> select id,name,length(lyrics) from songs;

1              All For Leyna      114
2              Goodnight Saigon            155


hive> select id,name,hex(lyrics) from songs;

1              All For Leyna                5368652073746F6F64206F6E2074686520747261636B730A576176696E67206865722061726D730A4C656164696E67206D6520746F2074686174207468697264207261696C2073686F636B0A517569636B20617320612077696E6B0A536865206368616E67656420686572206D696E640A0A
2              Goodnight Saigon                5765206D657420617320736F756C206D617465730A4F6E205061727269732049736C616E640A5765206C65667420617320696E6D617465730A46726F6D20616E206173796C756D0A416E6420776520776572652073686172700A4173207368617270206173206B6E697665730A416E64207765207765726520736F2067756E6720686F0A546F206C617920646F776E206F7572206C697665730A0A

hive> select id,name,regexp_replace(lyrics,'\n','<<<NEWLINE>>>') from
songs;

1              All For Leyna      She stood on the tracks<<<NEWLINE>>>Waving
her arms<<<NEWLINE>>>Leading me to that third rail shock<<<NEWLINE>>>Quick
as a wink<<<NEWLINE>>>She changed her mind<<<NEWLINE>>><<<NEWLINE>>>
2              Goodnight Saigon            We met as soul mates<<<NEWLINE>>>On
Parris Island<<<NEWLINE>>>We left as inmates<<<NEWLINE>>>From
an asylum<<<NEWLINE>>>And we were sharp<<<NEWLINE>>>As
sharp as knives<<<NEWLINE>>>And we were so gung ho<<<NEWLINE>>>To
lay down our lives<<<NEWLINE>>><<<NEWLINE>>>

hive> select id,name,split(lyrics,'\n') from songs;

1              All For Leyna      ["She stood on the tracks","Waving her arms","Leading me
to that third rail shock","Quick as a wink","She changed her mind","",""]
2              Goodnight Saigon            ["We met as soul mates","On Parris Island","We
left as inmates","From an asylum","And we were sharp","As sharp as knives","And we were so
gung ho","To lay down our lives","",""]

hive> select id,name,sentences(lyrics) from songs;

1              All For Leyna                [["She","stood","on","the","tracks","Waving","her","arms","Leading","me","to","that","third","rail","shock","Quick","as","a","wink","She","changed","her","mind"]]
2              Goodnight Saigon                [["We","met","as","soul","mates","On","Parris","Island","We","left","as","inmates","From","an","asylum","And","we","were","sharp","As","sharp","as","knives","And","we","were","so","gung","ho","To","lay","down","our","lives"]]

hive> select count (*) from songs;

NULL

hive> select count (*),123,456,789 from songs;

2              123         456         NULL

hive> select count (*),'A','B','C' from songs;

2              A             B             C


From: Radha krishna [mailto:grkmca95@gmail.com]
Sent: Thursday, June 02, 2016 12:42 PM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: LINES TERMINATED BY only supports newline '\n' right now

For some of the columns '\n' character is there as part of value, i want to create a hive
table for this data i tried by creating the hive table with US as the line separator but it
showing the below message

Ex:
CREATE EXTERNAL TABLE IF NOT EXISTS emp (name String,id int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '28'
LINES TERMINATED BY '31'
LOCATION 'emp.txt file path';

FAILED: SemanticException 4:20 LINES TERMINATED BY only supports newline '\n' right now. Error
encountered near token ''31''

how can we create hive table with out removing the \n character as part of column value (
requirement data need to maintain as it is)

can any one implemented hive tables with line separated other than \n

Thanks & Regards
   Radha krishna


Mime
View raw message