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
