Return-Path: Delivered-To: apmail-hadoop-hive-user-archive@locus.apache.org Received: (qmail 55281 invoked from network); 12 Jan 2009 01:57:29 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 12 Jan 2009 01:57:29 -0000 Received: (qmail 23547 invoked by uid 500); 12 Jan 2009 01:57:29 -0000 Delivered-To: apmail-hadoop-hive-user-archive@hadoop.apache.org Received: (qmail 23524 invoked by uid 500); 12 Jan 2009 01:57:28 -0000 Mailing-List: contact hive-user-help@hadoop.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: hive-user@hadoop.apache.org Delivered-To: mailing list hive-user@hadoop.apache.org Received: (qmail 23515 invoked by uid 99); 12 Jan 2009 01:57:28 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 11 Jan 2009 17:57:28 -0800 X-ASF-Spam-Status: No, hits=3.4 required=10.0 tests=HTML_MESSAGE,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [209.85.200.169] (HELO wf-out-1314.google.com) (209.85.200.169) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jan 2009 01:57:19 +0000 Received: by wf-out-1314.google.com with SMTP id 24so10485473wfg.2 for ; Sun, 11 Jan 2009 17:56:57 -0800 (PST) Received: by 10.142.170.6 with SMTP id s6mr8000205wfe.58.1231725417310; Sun, 11 Jan 2009 17:56:57 -0800 (PST) Received: from ?192.168.1.100? (c-71-202-152-68.hsd1.ca.comcast.net [71.202.152.68]) by mx.google.com with ESMTPS id 32sm6497256wfc.39.2009.01.11.17.56.56 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sun, 11 Jan 2009 17:56:56 -0800 (PST) Message-Id: From: Josh Ferguson To: hive-user@hadoop.apache.org In-Reply-To: <34fd060d0901102338s65bf578cg579f30f38902aaef@mail.gmail.com> Content-Type: multipart/alternative; boundary=Apple-Mail-6-15148449 Mime-Version: 1.0 (Apple Message framework v929.2) Subject: Re: Overwrite into table using only custom mapper Date: Sun, 11 Jan 2009 17:56:58 -0800 References: <19a16e8d0901091152w1049d1h7068f6986ac7ba60@mail.gmail.com> <19a16e8d0901101216r4eecab22scb4c584672eed943@mail.gmail.com> <19a16e8d0901102225te5f8231v44258a97290a69bd@mail.gmail.com> <34fd060d0901102246p213ad97eke8618aec3f90e76e@mail.gmail.com> <0A867331-1FAB-4C36-AF68-F17D646022B1@besquared.net> <34fd060d0901102338s65bf578cg579f30f38902aaef@mail.gmail.com> X-Mailer: Apple Mail (2.929.2) X-Virus-Checked: Checked by ClamAV on apache.org --Apple-Mail-6-15148449 Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit I just want to note that conceptually using a transform like this seems like it is a series of 3 steps: 1) dump 2) some stuff in the middle the semantic analyzer shouldn't care about 3 ) load I'm not really sure what the difference is between what is actually happening and those steps, but I know that it feels wrong to have to specify my delimiting information in multiple places and in every script I want to run that inserts data into a table containing a column with a MAP or a LIST type. Maybe hive isn't set up like this right now. I think all the information you need to do any of those steps (even repeatedly) is already available somewhere long before this query is ever run, so I should be able to use that information and not have to specify it again every time I want to run a query like this. Maybe there is something I'm missing? What was the use case for the "multiple processing steps" that you mentioned in your last email? Josh F. On Jan 10, 2009, at 11:38 PM, Zheng Shao wrote: > I don't think it's a good idea to rely on the information from the > table. The data might go through multiple processing steps before it > reaches the final destination table. And the destination table may > store the data in any way (may not be delimited). > > > What about allowing some syntax like this: > > SELECT TRANSFORM(myint, mymap) ROW FORMAT DELIMITED KEY TERMINATED > BY '3' COLLECTION ITEM TERMINATED BY '2' > USING '/bin/cat' > AS (myint INT, mymap MAP) ROW FORMAT DELIMITED KEY > TERMINATED BY '3' COLLECTION ITEM TERMINATED BY '2' > > The first ROW FORMAT describes the input format for the script, and > the second describes the output format of the script. > > > Zheng > > On Sat, Jan 10, 2009 at 11:22 PM, Josh Ferguson > wrote: > My initial assumption when I tried to write the query was that it > would use the same delimiters I defined in the schema definition of > the target table. That led to my confusion because I thought hive > had enough information (in the schema) to do proper string -> > map data conversion. > > Maybe something like that could work? > > Josh F. > > > On Jan 10, 2009, at 10:46 PM, Zheng Shao wrote: > >> Hi Josh, >> >> Yes the transform assumes every output column will be string. >> And, if the input of the transform is not a string, it will be >> converted to a string. But we don't have a mechanism to convert >> string back to map in the language. What do you >> think we should do to support that? >> >> Zheng >> >> On Sat, Jan 10, 2009 at 10:25 PM, Josh Ferguson >> wrote: >> One more small update, it seems that transform doesn't work at all >> for inserting into columns of type MAP. I suspect this is >> because the semantic analyzer treats all columns out of a custom >> map phase as type 'STRING' and then complains when it can't convert >> the assumed type into the type necessary, which is MAP> STRING> in this case. Is this correct? Is anyone else using a MAP >> type with custom map or reduce scripts? What queries have you >> gotten to work? >> >> Jos >> >> >> On Sat, Jan 10, 2009 at 12:16 PM, Josh Ferguson >> wrote: >> I want to follow up on this a little, here are the schemas for the >> source and destination tables and the query I am trying to run. >> >> Source table: >> >> hive> DESCRIBE EXTENDED >> users >> ; OK >> occurred_at int >> id string >> properties map >> account string >> application string >> dataset string >> hour int >> Detailed Table Information: >> Table(tableName:users,dbName:default,owner:Josh,createTime: >> 1231485489,lastAccessTime:0,retention:0,sd:StorageDescriptor(cols: >> [FieldSchema(name:occurred_at,type:int,comment:null), >> FieldSchema(name:id,type:string,comment:null), >> FieldSchema >> (name:properties,type:map,comment:null)],location:/ >> user/hive/warehouse/ >> users >> ,inputFormat:org >> .apache >> .hadoop >> .mapred >> .TextInputFormat >> ,outputFormat:org >> .apache >> .hadoop >> .hive.ql.io.IgnoreKeyTextOutputFormat,compressed:false,numBuckets: >> 32 >> ,serdeInfo:SerDeInfo >> (name:null >> ,serializationLib:org >> .apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe,parameters: >> {colelction >> .delim >> = >> 44 >> ,mapkey >> .delim >> = >> 58 >> ,serialization >> .format >> = >> org >> .apache >> .hadoop.hive.serde2.thrift.TCTLSeparatedProtocol}),bucketCols: >> [id],sortCols:[],parameters:{}),partitionKeys: >> [FieldSchema(name:account,type:string,comment:null), >> FieldSchema(name:application,type:string,comment:null), >> FieldSchema(name:dataset,type:string,comment:null), >> FieldSchema(name:hour,type:int,comment:null)],parameters:{}) >> >> >> Destination table: >> >> hive> DESCRIBE EXTENDED distinct_users; >> OK >> occurred_at int >> id string >> properties map >> account string >> application string >> dataset string >> hour int >> Detailed Table Information: >> Table(tableName:distinct_users,dbName:default,owner:Josh,createTime: >> 1231488500,lastAccessTime:0,retention:0,sd:StorageDescriptor(cols: >> [FieldSchema(name:occurred_at,type:int,comment:null), >> FieldSchema(name:id,type:string,comment:null), >> FieldSchema >> (name:properties,type:map,comment:null)],location:/ >> user/hive/warehouse/ >> distinct_users >> ,inputFormat:org >> .apache >> .hadoop >> .mapred >> .TextInputFormat >> ,outputFormat:org >> .apache >> .hadoop >> .hive.ql.io.IgnoreKeyTextOutputFormat,compressed:false,numBuckets: >> 32 >> ,serdeInfo:SerDeInfo >> (name:null >> ,serializationLib:org >> .apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe,parameters: >> {colelction >> .delim >> = >> 44 >> ,mapkey >> .delim >> = >> 58 >> ,serialization >> .format >> = >> org >> .apache >> .hadoop.hive.serde2.thrift.TCTLSeparatedProtocol}),bucketCols: >> [id],sortCols:[],parameters:{}),partitionKeys: >> [FieldSchema(name:account,type:string,comment:null), >> FieldSchema(name:application,type:string,comment:null), >> FieldSchema(name:dataset,type:string,comment:null), >> FieldSchema(name:hour,type:int,comment:null)],parameters:{}) >> >> The query: >> >> hive> INSERT OVERWRITE TABLE distinct_users SELECT >> TRANSFORM(users.occurred_at, users.id, users.properties) USING '/ >> bin/cat' AS (occurred_at, id, properties) FROM users; >> FAILED: Error in semantic analysis: line 1:23 Cannot insert into >> target table because column number/types are different >> distinct_users: Cannot convert column 2 from string to >> map. >> >> I'm really confused because the two tables are the exact same >> except for their names and I'm just trying to do an insert from one >> of them into the other using a script. >> >> For reference this appears to work: >> >> hive> INSERT OVERWRITE TABLE distinct_users SELECT occurred_at, id, >> properties FROM users; >> >> What is it about transforming that is messing up the semantic >> analysis? >> >> Josh Ferguson >> >> On Fri, Jan 9, 2009 at 11:52 AM, Josh Ferguson >> wrote: >> Is it possible to do a query like the following: >> >> INSERT OVERWRITE TABLE table1 PARTITION(...) >> FROM table2 >> SELECT TRANSFORM(table2.col1, table2.col2, ...) USING '/my/script' >> AS (col1, col2, ...) >> WHERE (...) >> >> I can run the select transform segment of the query by itself fine >> and I get the results I expect. >> >> When I try and do the insert as well I'm getting errors with column >> type mismatches even though my script is outputting 3 columns with >> the exact same types in the exact order that they appear in table1. >> I tried doing this with both a mapper and reducer similar to what >> was shown in the Apache Con slides and it still didn't work. Am I >> doing something wrong query wise? >> >> I'm using the 0.19 release. >> >> Josh Ferguson >> >> >> >> >> >> -- >> Yours, >> Zheng > > > > > -- > Yours, > Zheng --Apple-Mail-6-15148449 Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I just want to note that = conceptually using a transform like this seems like it is a series of 3 = steps:

1) dump 
2) some stuff in the = middle the semantic analyzer shouldn't care about
3 ) = load

I'm not really sure what the difference is = between what is actually happening and those steps, but I know that it = feels wrong to have to specify my delimiting information in multiple = places and in every script I want to run that inserts data into a table = containing a column with a MAP or a LIST = type. 

Maybe hive isn't set up like this = right now. I think all the information you need to do any of those steps = (even repeatedly) is already available somewhere long before this query = is ever run, so I should be able to use that information and not have to = specify it again every time I want to run a query like = this.

Maybe there is something I'm missing? = What was the use case for the "multiple processing steps" that you = mentioned in your last email?

Josh = F.

On Jan 10, 2009, at 11:38 PM, Zheng Shao = wrote:

I don't think it's a good idea to rely on the information = from the table. The data might go through multiple processing steps = before it reaches the final destination table. And the destination table = may store the data in any way (may not be delimited).


What = about allowing some syntax like this:

SELECT TRANSFORM(myint, = mymap) ROW FORMAT DELIMITED KEY TERMINATED BY '3' COLLECTION ITEM = TERMINATED BY '2'
USING '/bin/cat'
AS (myint INT, mymap = MAP<STRING,STRING>) ROW FORMAT DELIMITED KEY TERMINATED BY '3' = COLLECTION ITEM TERMINATED BY '2'

The first ROW FORMAT describes = the input format for the script, and the second describes the output = format of the script.


Zheng

On Sat, Jan 10, 2009 at 11:22 PM, Josh Ferguson = <josh@besquared.net> = wrote:
My initial assumption when I tried to write the = query was that it would use the same delimiters I defined in the schema = definition of the target table. That led to my confusion because I = thought hive had enough information (in the schema) to do proper string = -> map<x,x> data conversion. 

Maybe = something like that could work?

Josh = F.


On Jan = 10, 2009, at 10:46 PM, Zheng Shao wrote:

Hi Josh,

Yes the transform assumes every output = column will be string.
And, if the input of the transform is not a = string, it will be converted to a string. But we don't have a mechanism = to convert string back to map<string,string> in the language. What do = you think we should do to support that?

Zheng

On Sat, Jan 10, 2009 at 10:25 PM, Josh Ferguson = <josh@besquared.net> wrote:
One more small = update, it seems that transform doesn't work at all for inserting into = columns of type MAP<X,Y>. I suspect this is because the semantic = analyzer treats all columns out of a custom map phase as type 'STRING' = and then complains when it can't convert the assumed type into the type = necessary, which is MAP<STRING, STRING> in this case. Is this = correct? Is anyone else using a MAP type with custom map or reduce = scripts? What queries have you gotten to work?
=
Jos


On Sat, Jan 10, 2009 at 12:16 PM, Josh Ferguson = <josh@besquared.net> wrote:
I want to follow = up on this a little, here are the schemas for the source and destination = tables and the query I am trying to run.

Source = table:

hive> DESCRIBE EXTENDED users; =                     =                     =                     =                     =                     =                     =                     =      OK               =              
=
occurred_at = int
id = string
properties = map<string,string>
account string
application string
dataset string
hour int
Detailed Table = Information:
Table(tableName:users,dbName:default,owner:Josh,cre= ateTime:1231485489,lastAccessTime:0,retention:0,sd:StorageDescriptor(cols:= [FieldSchema(name:occurred_at,type:int,comment:null), = FieldSchema(name:id,type:string,comment:null), = FieldSchema(name:properties,type:map<string,string>,comment:null)],loca= tion:/user/hive/warehouse/users,inputFormat:org.apache.hadoop.mapred.TextI= nputFormat,outputFormat:org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFo= rmat,compressed:false,numBuckets:32,serdeInfo:SerDeInfo(name:null,serializ= ationLib:org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe,parameter= s:{colelction.delim=3D44,mapkey.delim=3D58,serialization.format=3Dorg.apac= he.hadoop.hive.serde2.thrift.TCTLSeparatedProtocol}),bucketCols:[id],sortC= ols:[],parameters:{}),partitionKeys:[FieldSchema(name:account,type:string,= comment:null), FieldSchema(name:application,type:string,comment:null), = FieldSchema(name:dataset,type:string,comment:null), = FieldSchema(name:hour,type:int,comment:null)],parameters:{})
=


Destination = table:

hive> DESCRIBE EXTENDED = distinct_users;
OK
occurred_at int
id string
properties = map<string,string>
account string
application string
dataset string
hour = int
Detailed Table = Information:
Table(tableName:distinct_users,dbName:default,owner= :Josh,createTime:1231488500,lastAccessTime:0,retention:0,sd:StorageDescrip= tor(cols:[FieldSchema(name:occurred_at,type:int,comment:null), = FieldSchema(name:id,type:string,comment:null), = FieldSchema(name:properties,type:map<string,string>,comment:null)],loca= tion:/user/hive/warehouse/distinct_users,inputFormat:org.apache.hadoop.map= red.TextInputFormat,outputFormat:org.apache.hadoop.hive.ql.io.IgnoreKeyTex= tOutputFormat,compressed:false,numBuckets:32,serdeInfo:SerDeInfo(name:null= ,serializationLib:org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe,= parameters:{colelction.delim=3D44,mapkey.delim=3D58,serialization.format=3D= org.apache.hadoop.hive.serde2.thrift.TCTLSeparatedProtocol}),bucketCols:[i= d],sortCols:[],parameters:{}),partitionKeys:[FieldSchema(name:account,type= :string,comment:null), = FieldSchema(name:application,type:string,comment:null), = FieldSchema(name:dataset,type:string,comment:null), = FieldSchema(name:hour,type:int,comment:null)],parameters:{})
=

The query:

hive> = INSERT OVERWRITE TABLE distinct_users SELECT = TRANSFORM(users.occurred_at, users.id, users.properties) USING '/bin/cat' AS = (occurred_at, id, properties) FROM users; 
FAILED: Error = in semantic analysis: line 1:23 Cannot insert into target table because = column number/types are different distinct_users: Cannot convert column = 2 from string to map<string,string>.

=
I'm really confused because the two tables are the exact same = except for their names and I'm just trying to do an insert from one of = them into the other using a script.

For = reference this appears to work:

hive> INSERT = OVERWRITE TABLE distinct_users SELECT occurred_at, id, properties FROM = users;

What is it about transforming that = is messing up the semantic analysis?

Josh = Ferguson

On Fri, Jan 9, 2009 at 11:52 AM, Josh Ferguson = <josh@besquared.net> wrote:
Is it possible to = do a query like the following:

INSERT OVERWRITE TABLE = table1 PARTITION(...)
FROM table2
SELECT = TRANSFORM(table2.col1, table2.col2, ...) USING '/my/script' AS (col1, = col2, ...)
WHERE (...)

I can run the = select transform segment of the query by itself fine and I get the = results I expect.

When I try and do the insert = as well I'm getting errors with column type mismatches even though my = script is outputting 3 columns with the exact same types in the exact = order that they appear in table1. I tried doing this with both a = mapper and reducer similar to what was shown in the Apache Con slides = and it still didn't work. Am I doing something wrong query = wise?

I'm using the 0.19 = release.

Josh = Ferguson

=




-- =
Yours,
Zheng




-- =
Yours,
Zheng

= --Apple-Mail-6-15148449--