Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Can we define external table Fields enclosed in "


Copy link to this message
-
Re: Can we define external table Fields enclosed in "
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: [EMAIL PROTECTED]

"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" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], "Gopi Kodumur" <[EMAIL PROTECTED]>
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 < [EMAIL PROTECTED] > 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 < [EMAIL PROTECTED] >
To: [EMAIL PROTECTED] ; Gopi Kodumur < [EMAIL PROTECTED] >
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 < [EMAIL PROTECTED] > wrote:
Is it possible to specify enclosed by character " for fields , while defining external table

Thanks
Gopi