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 <[email protected]> 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:[email protected]] > Sent: Thursday, June 02, 2016 12:42 PM > To: [email protected] > 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 >
