The most optimal way would be to create an InputFormat and or SerDe.
On Wed, Apr 18, 2012 at 4:37 PM, shashwat shriparv <dwivedishash...@gmail.com> wrote: > Check out this thread too : > > http://mail-archives.apache.org/mod_mbox/hbase-user/201204.mbox/%3ccaaxmexxpho7fr4939aljyse1j2unvqxom3h+zfvagroaqhg...@mail.gmail.com%3E > or > http://comments.gmane.org/gmane.comp.java.hadoop.hbase.user/25507 > > On Thu, Apr 19, 2012 at 1:31 AM, Mark Grover <mgro...@oanda.com> wrote: >> >> Gopi, >> I was thinking something very similar to Tim's suggestion: >> >> CREATE EXTERNAL TABLE table_stg(ip STRING, id1 STRING, >> ts STRING, id2 STRING) >> ROW FORMAT DELIMITED FIELDS TERMINATED BY '~' >> LOCATION 'my_hdfs_location'; >> >> CREATE VIEW my_view(ip, id1, ts, id2) AS SELECT substr(ip, 2, >> length(ip)-2) as ip, substr(id1, 2, length(id1)-2) as id1, substr(ts, 2, >> length(ts)-2) as ts, substr(id2, 2, length(id2)-2) as id2 from table_stg; >> >> >> I am just using substr instead of regexp_replace. Neither of them are >> optimal. Perhaps, you can create your own UDF that trims off the first and >> last character of the string if it's a quotation mark (if there isn't a UDF >> for that already). >> >> Mark >> >> Mark Grover, Business Intelligence Analyst >> OANDA Corporation >> >> www: oanda.com www: fxtrade.com >> e: mgro...@oanda.com >> >> "Best Trading Platform" - World Finance's Forex Awards 2009. >> "The One to Watch" - Treasury Today's Adam Smith Awards 2009. >> >> >> ----- Original Message ----- >> From: "Tim Robertson" <timrobertson...@gmail.com> >> To: user@hive.apache.org, "Gopi Kodumur" <gkodu...@yahoo.com> >> Sent: Wednesday, April 18, 2012 3:46:19 PM >> Subject: Re: Can we define external table Fields enclosed in " >> >> Hi again, >> >> >> How about defining a table (t1) with ~ as the delimiter and then creating >> a view to that table which uses the regexp_replace UDF? >> >> >> CREATE VIEW v_user_access AS >> SELECT regexp_replace(ip, "\"", "") as ip, ... >> FROM t1; >> >> >> Not sure the implications on joining, but basic queries should work ok I >> would think. >> >> >> HTH, >> Tim >> >> >> >> >> >> >> >> >> >> >> >> >> >> On Wed, Apr 18, 2012 at 9:20 PM, Gopi Kodumur < gkodu...@yahoo.com > >> wrote: >> >> >> >> >> >> Thanks Tim, Sorry for not explaining the problem clearly... >> >> >> >> I have data in this format , I wanted to store the data in Ext-Hive table >> without the Double Quote >> >> >> >> "127.0.0.17"~"444c1c9a-8820-11e1-aaa8-00219b8a879e"~"2012-04-17T00:00:01Z"~"476825ea-8820-11e1-a105-0200ac1d1c3d >> >> "127.0.0.12"~"544c1c9a-8820-11e1-aaa8-00219b8a879e"~"2012-04-17T00:00:01Z"~"476825ea-8820-11e1-a105-0200ac1d1c3d >> >> "127.0.0.13"~"644c1c9a-8820-11e1-aaa8-00219b8a879e"~"2012-04-17T00:00:01Z"~"476825ea-8820-11e1-a105-0200ac1d1c3d >> >> >> >> >> >> >> >> >> >> >> >> From: Tim Robertson < timrobertson...@gmail.com > >> To: user@hive.apache.org ; Gopi Kodumur < gkodu...@yahoo.com > >> Sent: Wednesday, April 18, 2012 12:14 PM >> Subject: Re: Can we define external table Fields enclosed in " >> >> >> >> >> I believe so. From the tutorial [1] : >> >> >> CREATE EXTERNAL TABLE page_view_stg(viewTime INT, userid BIGINT, >> page_url STRING, referrer_url STRING, >> ip STRING COMMENT 'IP Address of the User', >> country STRING COMMENT 'country of origination') >> COMMENT 'This is the staging page view table' >> ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12' >> >> >> HTH, >> Tim >> >> >> [1] https://cwiki.apache.org/confluence/display/Hive/Tutorial >> >> >> >> >> >> >> On Tue, Apr 17, 2012 at 11:20 PM, Gopi Kodumur < gkodu...@yahoo.com > >> wrote: >> >> >> >> >> Is it possible to specify enclosed by character " for fields , while >> defining external table >> >> Thanks >> Gopi >> >> >> >> > > > > -- > > > ∞ > > Shashwat Shriparv > >
