You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Maciek <ma...@sonra.io> on 2014/11/05 18:29:21 UTC
CREATE (PARTITIONED) TABLE AS error
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…
Re: CREATE (PARTITIONED) TABLE AS error
Posted by Maciek <ma...@sonra.io>.
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 <ma...@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 <ni...@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 <ma...@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
Re: CREATE (PARTITIONED) TABLE AS error
Posted by Maciek <ma...@sonra.io>.
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 <ni...@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 <ma...@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…
>>
>
Re: CREATE (PARTITIONED) TABLE AS error
Posted by Nishant Kelkar <ni...@gmail.com>.
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 <ma...@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…
>