You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mich Talebzadeh <mi...@gmail.com> on 2016/07/29 19:04:58 UTC
Updating ORC table fails with [Error 10122]: Bucketized tables do not
support INSERT INTO:
Table was created as ORC transactional table
CREATE TABLE `payees`(
`transactiondescription` string,
`hits` int,
`hashtag` string)
CLUSTERED BY (
transactiondescription)
INTO 256 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://rhes564:9000/user/hive/warehouse/accounts.db/payees'
TBLPROPERTIES (
'last_modified_by'='hduser',
'last_modified_time'='1469818104',
'numFiles'='256',
'numRows'='620',
'orc.compress'='ZLIB',
'ransactional'='true',
'rawDataSize'='0',
'totalSize'='113650',
'transient_lastDdlTime'='1469818104')
Updating column hashtag in this table based on column transactiondescription
it fails
hive> update payees set hashtag = "HARRODS" here transactiondescription
like "%HARRODS%";
FAILED: ParseException line 1:38 missing EOF at 'here' near '"HARRODS"'
hive> update payees set hashtag = "HARRODS" where transactiondescription
like "%HARRODS%";
FAILED: SemanticException [Error 10122]: Bucketized tables do not support
INSERT INTO: Table: accounts.payees
What would be the least painful solution without some elaborate means?
Thanks
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
Re: Updating ORC table fails with [Error 10122]: Bucketized tables do
not support INSERT INTO:
Posted by Mich Talebzadeh <mi...@gmail.com>.
Many thanks Bala well spotted. It was getting me confused. Sorted now :)
I have seen this before. The syntax is not checked when table is created!
Anyway
hive> select * from payees where hashtag is not null;
OK
HARRODS INTERNATIO CD 4628 1 HARRODS
HARRODS LTD CD 5710 42 HARRODS
HARRODS LTD CD 4610 4 HARRODS
HARRODS LTD CD 4636 13 HARRODS
HARRODS LTD CD 5916 28 HARRODS
HARRODS LTD CD 4628 111 HARRODS
cheers
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 29 July 2016 at 20:43, Bala Krishna Gangisetty <ba...@altiscale.com>
wrote:
> 'ransactional'='true',
>
>
> This looks suspicious.
>
> On Fri, Jul 29, 2016 at 12:04 PM, Mich Talebzadeh <
> mich.talebzadeh@gmail.com> wrote:
>
>>
>> Table was created as ORC transactional table
>>
>> CREATE TABLE `payees`(
>> `transactiondescription` string,
>> `hits` int,
>> `hashtag` string)
>> CLUSTERED BY (
>> transactiondescription)
>> INTO 256 BUCKETS
>> ROW FORMAT SERDE
>> 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
>> STORED AS INPUTFORMAT
>> 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
>> OUTPUTFORMAT
>> 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
>> LOCATION
>> 'hdfs://rhes564:9000/user/hive/warehouse/accounts.db/payees'
>> TBLPROPERTIES (
>> 'last_modified_by'='hduser',
>> 'last_modified_time'='1469818104',
>> 'numFiles'='256',
>> 'numRows'='620',
>> 'orc.compress'='ZLIB',
>> 'ransactional'='true',
>> 'rawDataSize'='0',
>> 'totalSize'='113650',
>> 'transient_lastDdlTime'='1469818104')
>>
>>
>> Updating column hashtag in this table based on column
>> transactiondescription
>>
>> it fails
>>
>> hive> update payees set hashtag = "HARRODS" here transactiondescription
>> like "%HARRODS%";
>> FAILED: ParseException line 1:38 missing EOF at 'here' near '"HARRODS"'
>> hive> update payees set hashtag = "HARRODS" where transactiondescription
>> like "%HARRODS%";
>>
>> FAILED: SemanticException [Error 10122]: Bucketized tables do not support
>> INSERT INTO: Table: accounts.payees
>>
>>
>> What would be the least painful solution without some elaborate means?
>>
>> Thanks
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>
>
Re: Updating ORC table fails with [Error 10122]: Bucketized tables do
not support INSERT INTO:
Posted by Bala Krishna Gangisetty <ba...@altiscale.com>.
>
> 'ransactional'='true',
This looks suspicious.
On Fri, Jul 29, 2016 at 12:04 PM, Mich Talebzadeh <mich.talebzadeh@gmail.com
> wrote:
>
> Table was created as ORC transactional table
>
> CREATE TABLE `payees`(
> `transactiondescription` string,
> `hits` int,
> `hashtag` string)
> CLUSTERED BY (
> transactiondescription)
> INTO 256 BUCKETS
> ROW FORMAT SERDE
> 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
> LOCATION
> 'hdfs://rhes564:9000/user/hive/warehouse/accounts.db/payees'
> TBLPROPERTIES (
> 'last_modified_by'='hduser',
> 'last_modified_time'='1469818104',
> 'numFiles'='256',
> 'numRows'='620',
> 'orc.compress'='ZLIB',
> 'ransactional'='true',
> 'rawDataSize'='0',
> 'totalSize'='113650',
> 'transient_lastDdlTime'='1469818104')
>
>
> Updating column hashtag in this table based on column
> transactiondescription
>
> it fails
>
> hive> update payees set hashtag = "HARRODS" here transactiondescription
> like "%HARRODS%";
> FAILED: ParseException line 1:38 missing EOF at 'here' near '"HARRODS"'
> hive> update payees set hashtag = "HARRODS" where transactiondescription
> like "%HARRODS%";
>
> FAILED: SemanticException [Error 10122]: Bucketized tables do not support
> INSERT INTO: Table: accounts.payees
>
>
> What would be the least painful solution without some elaborate means?
>
> Thanks
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>