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
