hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maciek <mac...@sonra.io>
Subject Re: CREATE (PARTITIONED) TABLE AS error
Date Mon, 10 Nov 2014 09:59:38 GMT
Could I ask any of you who are on Hive v0.14 to check if such CTAS syntax
works?
Many thanks!

On Thu, Nov 6, 2014 at 10:32 AM, Maciek <maciek@sonra.io> wrote:

> What I'm referring to is CTAS statement and as per documentation it
> doesn't seem to work (Hive 0.13).
> I'm setting:
>
> SET hive.exec.dynamic.partition=true;
> SET hive.exec.dynamic.partition.mode=nonstrict;
> and
> CREATE TABLE dropme(key int, value string) PARTITIONED BY (yr int, mth
> int) AS
> SELECT 2 key, 'val' value, 2014 yr, 5 mth FROM singlerow;
> gives error:
>
> FAILED: SemanticException [Error 10065]: CREATE TABLE AS SELECT command
> cannot specify the list of columns for the target table
>
> According to the documentation (
> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions) it
> should work.
> A bug?
>
> On Wed, Nov 5, 2014 at 5:47 PM, Nishant Kelkar <nishant.k02@gmail.com>
> wrote:
>
>> The following worked for me:
>>
>> > CREATE TABLE dropme(key int, value string) PARTITIONED BY (yr int, mth
>> int);
>>
>> > SET hive.exec.dynamic.partition.mode=nonstrict
>>
>> > INSERT INTO TABLE dropme PARTITION(yr,mth)
>> SELECT stack(1,
>> 2, 'val', 2014, 5) AS
>> (key, value, yr, mth) FROM singlerow;
>>
>> Note that you need the second SET command to allow for dynamic
>> partitioning over all partition fields. "stack" is a in-built UDTF that
>> takes in as first argument, the number of rows to insert N (in this case
>> N=1) followed by "NK" arguments, where K is the number of columns you have.
>>
>>
>> Best Regards,
>> Nishant Kelkar
>>
>> On Wed, Nov 5, 2014 at 9:29 AM, Maciek <maciek@sonra.io> wrote:
>>
>>> Based on the documentation
>>> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions
>>> the following CTAS should work:
>>>
>>> CREATE TABLE dropme(key int, value string) PARTITIONED BY (yr int, mth
>>> int) AS
>>> SELECT 2 key, 'val' value, 2014 yr, 5 mth FROM singlerow;
>>>
>>> but instead it gives me the error:
>>>
>>> FAILED: SemanticException [Error 10065]: CREATE TABLE AS SELECT command
>>> cannot specify the list of columns for the target table
>>> Any ideas what is wrong here?
>>> PS. I'm on hive 0.13 but the releavant documentation entry is dated so
>>> it should be supported in 0.13 I supposeā€¦
>>>
>>


-- 
Kind Regards
Maciek Kocon

Mime
View raw message