Re: Hive beeline and ACID tables.

[Colin Williams]

Hi all. Thanks for the help. I was able to set the properties with multiple
--hiveconf flags. It appears to have worked. However I do think it's a good
idea to set the properties globally in hive-site.conf.

On Thu, Feb 15, 2018 at 9:32 AM, Eugene Koifman <ekoif...@hortonworks.com>
wrote:

> “This command is not allowed on an ACID table 
> analytics_user_vws.liveramp_events
> with a non-ACID transaction manager.”
>
>
>
> Indicates that 
> “hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager”
> is not taking effect.  When enabling acid you usually want to set this (and
> other relevant properties) hive-site.xml so that all sessions agree on
> which lock manager/txn manager they are using.
>
>
>
> Eugene
>
>
>
> *From: *Colin Williams <colin.williams.seat...@gmail.com>
> *Reply-To: *"user@hive.apache.org" <user@hive.apache.org>
> *Date: *Thursday, February 15, 2018 at 8:54 AM
> *To: *"user@hive.apache.org" <user@hive.apache.org>
> *Subject: *Re: Hive beeline and ACID tables.
>
>
>
> Looking back I may have omitted the --hiveconf setting. I will try
> creating the table again today and see if that was the issue.
>
>
>
> On Thu, Feb 15, 2018 at 8:51 AM, Colin Williams <
> colin.williams.seat...@gmail.com> wrote:
>
> Hi Johannes. I did set the "transactional" property to true like:
>
> tblproperties("transactional"="true");
>
> And also stored as orc and bucketed and partitioned the table. Finally I
> did use double -- for setting the hiveconf. Then there must be some other
> issue regarding using the ACID tables.
>
>
>
>
>
> On Wed, Feb 14, 2018 at 9:08 PM, Johannes Alberti <johan...@altiscale.com>
> wrote:
>
> Hi Collin,
>
>
>
> you need to set the table property “transactional” to true, this will mark
> the table as ACID.
>
>
>
> See also the example all the way at the bottom of this link.
>
>
>
> https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions
>
>
>
> For your error, the SELECT, maybe it is simply a copy-n-paste issue in
> your mail, but your hiveconf parameter is missing a - ... it is beeline
> --hiveconf ..., not beeline -hiveconf 
>
>
>
> Regards,
>
>
>
> Johannes
>
>
>
> Sent from my iPhone
>
>
> On Feb 14,  2018, at 5:27 PM, Colin Williams <
> colin.williams.seat...@gmail.com> wrote:
>
> Hello,
>
> I'm new to hive and had been porting some Redshift SQL to Hive. Today I
> ran across a table that uses update.
>
> After a somewhat time consuming translation process I was able to load the
> create table definition. However the update part returned an error about
> update not supported on non-acid table.
>
> Then I read some documentation, added bucketing to the table definition,
> added beeline xml file, etc. Then I launch the create table script like
>
> `beeline --hiveconf hive.support.concurrency=true
> hive.exec.dynamic.partition.mode=nonstrict hive.txn.manager=org.apache.
> hadoop.hive.ql.lockmgr.DbTxnManager -f ./events.sql`
>
> beeline executes part of the SQL file but then provides no more output.
>
> To figure out what was going on I tried to query the table
>
>
>
> beeline -hiveconf hive.support.concurrency=true
> hive.exec.dynamic.partition.mode=nonstrict hive.txn.manager=org.apache.
> hadoop.hive.ql.lockmgr.DbTxnManager -e "Select * from analytics.events
> limit 10;"
> Connecting to jdbc:hive2://REDACTED
> REDACTED
> Connected to: Apache Hive (version 2.3.2-amzn-0)
> Driver: Hive JDBC (version 2.3.2-amzn-0)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> Error: Error while compiling statement: FAILED: SemanticException [Error
> 10265]: This command is not allowed on an ACID table
> analytics_user_vws.liveramp_events with a non-ACID transaction manager.
> Failed command: Select * from analytics_user_vws.liveramp_events limit 10
> (state=42000,code=10265)
> Closing: 0: jdbc:hive2://
>
> Why am I not getting that error from creating the table? ( and no INFO
> from beeline)
>
> Also can somebody help me figure out what I need to do to be able to
> create the ACID table?
>
> Thanks
>
>
>
>
>

Re: Hive beeline and ACID tables.

[Eugene Koifman]

“This command is not allowed on an ACID table 
analytics_user_vws.liveramp_events with a non-ACID transaction manager.”

Indicates that 
“hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager” is not taking 
effect.  When enabling acid you usually want to set this (and other relevant 
properties) hive-site.xml so that all sessions agree on which lock manager/txn 
manager they are using.

Eugene

From: Colin Williams <colin.williams.seat...@gmail.com>
Reply-To: "user@hive.apache.org" <user@hive.apache.org>
Date: Thursday, February 15, 2018 at 8:54 AM
To: "user@hive.apache.org" <user@hive.apache.org>
Subject: Re: Hive beeline and ACID tables.

Looking back I may have omitted the --hiveconf setting. I will try creating the 
table again today and see if that was the issue.

On Thu, Feb 15, 2018 at 8:51 AM, Colin Williams 
<colin.williams.seat...@gmail.com<mailto:colin.williams.seat...@gmail.com>> 
wrote:
Hi Johannes. I did set the "transactional" property to true like:

tblproperties("transactional"="true");
And also stored as orc and bucketed and partitioned the table. Finally I did 
use double -- for setting the hiveconf. Then there must be some other issue 
regarding using the ACID tables.


On Wed, Feb 14, 2018 at 9:08 PM, Johannes Alberti 
<johan...@altiscale.com<mailto:johan...@altiscale.com>> wrote:
Hi Collin,

you need to set the table property “transactional” to true, this will mark the 
table as ACID.

See also the example all the way at the bottom of this link.

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

For your error, the SELECT, maybe it is simply a copy-n-paste issue in your 
mail, but your hiveconf parameter is missing a - ... it is beeline --hiveconf 
..., not beeline -hiveconf 

Regards,

Johannes

Sent from my iPhone

On Feb 14,  2018, at 5:27 PM, Colin Williams 
<colin.williams.seat...@gmail.com<mailto:colin.williams.seat...@gmail.com>> 
wrote:
Hello,
I'm new to hive and had been porting some Redshift SQL to Hive. Today I ran 
across a table that uses update.

After a somewhat time consuming translation process I was able to load the 
create table definition. However the update part returned an error about update 
not supported on non-acid table.
Then I read some documentation, added bucketing to the table definition, added 
beeline xml file, etc. Then I launch the create table script like

`beeline --hiveconf hive.support.concurrency=true 
hive.exec.dynamic.partition.mo<http://hive.exec.dynamic.partition.mo>de=nonstrict
 hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager -f 
./events.sql`
beeline executes part of the SQL file but then provides no more output.
To figure out what was going on I tried to query the table


beeline -hiveconf hive.support.concurrency=true 
hive.exec.dynamic.partition.mo<http://hive.exec.dynamic.partition.mo>de=nonstrict
 hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager -e "Select * 
from analytics.events limit 10;"
Connecting to jdbc:hive2://REDACTED
REDACTED
Connected to: Apache Hive (version 2.3.2-amzn-0)
Driver: Hive JDBC (version 2.3.2-amzn-0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Error: Error while compiling statement: FAILED: SemanticException [Error 
10265]: This command is not allowed on an ACID table 
analytics_user_vws.liveramp_events with a non-ACID transaction manager. Failed 
command: Select * from analytics_user_vws.liveramp_events limit 10 
(state=42000,code=10265)
Closing: 0: jdbc:hive2://

Why am I not getting that error from creating the table? ( and no INFO from 
beeline)

Also can somebody help me figure out what I need to do to be able to create the 
ACID table?
Thanks

Re: Hive beeline and ACID tables.

[Colin Williams]

Looking back I may have omitted the --hiveconf setting. I will try creating
the table again today and see if that was the issue.

On Thu, Feb 15, 2018 at 8:51 AM, Colin Williams <
colin.williams.seat...@gmail.com> wrote:

> Hi Johannes. I did set the "transactional" property to true like:
>
> tblproperties("transactional"="true");
>
> And also stored as orc and bucketed and partitioned the table. Finally I
> did use double -- for setting the hiveconf. Then there must be some other
> issue regarding using the ACID tables.
>
>
>
> On Wed, Feb 14, 2018 at 9:08 PM, Johannes Alberti 
> wrote:
>
>> Hi Collin,
>>
>> you need to set the table property “transactional” to true, this will
>> mark the table as ACID.
>>
>> See also the example all the way at the bottom of this link.
>>
>> https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions
>>
>> For your error, the SELECT, maybe it is simply a copy-n-paste issue in
>> your mail, but your hiveconf parameter is missing a - ... it is beeline
>> --hiveconf ..., not beeline -hiveconf 
>>
>> Regards,
>>
>> Johannes
>>
>> Sent from my iPhone
>>
>> On Feb 14,  2018, at 5:27 PM, Colin Williams <
>> colin.williams.seat...@gmail.com> wrote:
>>
>> Hello,
>>
>> I'm new to hive and had been porting some Redshift SQL to Hive. Today I
>> ran across a table that uses update.
>>
>> After a somewhat time consuming translation process I was able to load
>> the create table definition. However the update part returned an error
>> about update not supported on non-acid table.
>>
>> Then I read some documentation, added bucketing to the table definition,
>> added beeline xml file, etc. Then I launch the create table script like
>>
>>
>> `beeline --hiveconf hive.support.concurrency=true
>> hive.exec.dynamic.partition.mode=nonstrict hive.txn.manager=org.apache.ha
>> doop.hive.ql.lockmgr.DbTxnManager -f ./events.sql`
>>
>> beeline executes part of the SQL file but then provides no more output.
>>
>> To figure out what was going on I tried to query the table
>>
>>
>> beeline -hiveconf hive.support.concurrency=true
>> hive.exec.dynamic.partition.mode=nonstrict hive.txn.manager=org.apache.ha
>> doop.hive.ql.lockmgr.DbTxnManager -e "Select * from analytics.events
>> limit 10;"
>> Connecting to jdbc:hive2://REDACTED
>> REDACTED
>> Connected to: Apache Hive (version 2.3.2-amzn-0)
>> Driver: Hive JDBC (version 2.3.2-amzn-0)
>> Transaction isolation: TRANSACTION_REPEATABLE_READ
>> Error: Error while compiling statement: FAILED: SemanticException [Error
>> 10265]: This command is not allowed on an ACID table
>> analytics_user_vws.liveramp_events with a non-ACID transaction manager.
>> Failed command: Select * from analytics_user_vws.liveramp_events limit
>> 10 (state=42000,code=10265)
>> Closing: 0: jdbc:hive2://
>>
>>
>> Why am I not getting that error from creating the table? ( and no INFO
>> from beeline)
>>
>> Also can somebody help me figure out what I need to do to be able to
>> create the ACID table?
>>
>> Thanks
>>
>>
>

Re: Hive beeline and ACID tables.

[Colin Williams]

Hi Johannes. I did set the "transactional" property to true like:

tblproperties("transactional"="true");

And also stored as orc and bucketed and partitioned the table. Finally I
did use double -- for setting the hiveconf. Then there must be some other
issue regarding using the ACID tables.



On Wed, Feb 14, 2018 at 9:08 PM, Johannes Alberti 
wrote:

> Hi Collin,
>
> you need to set the table property “transactional” to true, this will mark
> the table as ACID.
>
> See also the example all the way at the bottom of this link.
>
> https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions
>
> For your error, the SELECT, maybe it is simply a copy-n-paste issue in
> your mail, but your hiveconf parameter is missing a - ... it is beeline
> --hiveconf ..., not beeline -hiveconf 
>
> Regards,
>
> Johannes
>
> Sent from my iPhone
>
> On Feb 14,  2018, at 5:27 PM, Colin Williams <
> colin.williams.seat...@gmail.com> wrote:
>
> Hello,
>
> I'm new to hive and had been porting some Redshift SQL to Hive. Today I
> ran across a table that uses update.
>
> After a somewhat time consuming translation process I was able to load the
> create table definition. However the update part returned an error about
> update not supported on non-acid table.
>
> Then I read some documentation, added bucketing to the table definition,
> added beeline xml file, etc. Then I launch the create table script like
>
>
> `beeline --hiveconf hive.support.concurrency=true
> hive.exec.dynamic.partition.mode=nonstrict hive.txn.manager=org.apache.
> hadoop.hive.ql.lockmgr.DbTxnManager -f ./events.sql`
>
> beeline executes part of the SQL file but then provides no more output.
>
> To figure out what was going on I tried to query the table
>
>
> beeline -hiveconf hive.support.concurrency=true
> hive.exec.dynamic.partition.mode=nonstrict hive.txn.manager=org.apache.
> hadoop.hive.ql.lockmgr.DbTxnManager -e "Select * from analytics.events
> limit 10;"
> Connecting to jdbc:hive2://REDACTED
> REDACTED
> Connected to: Apache Hive (version 2.3.2-amzn-0)
> Driver: Hive JDBC (version 2.3.2-amzn-0)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> Error: Error while compiling statement: FAILED: SemanticException [Error
> 10265]: This command is not allowed on an ACID table
> analytics_user_vws.liveramp_events with a non-ACID transaction manager.
> Failed command: Select * from analytics_user_vws.liveramp_events limit 10
> (state=42000,code=10265)
> Closing: 0: jdbc:hive2://
>
>
> Why am I not getting that error from creating the table? ( and no INFO
> from beeline)
>
> Also can somebody help me figure out what I need to do to be able to
> create the ACID table?
>
> Thanks
>
>

Re: Hive beeline and ACID tables.

[Johannes Alberti]

Hi Collin,

you need to set the table property “transactional” to true, this will mark the 
table as ACID.

See also the example all the way at the bottom of this link.

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

For your error, the SELECT, maybe it is simply a copy-n-paste issue in your 
mail, but your hiveconf parameter is missing a - ... it is beeline --hiveconf 
..., not beeline -hiveconf 

Regards,

Johannes

Sent from my iPhone

> On Feb 14,  2018, at 5:27 PM, Colin Williams 
>  wrote:
> 
> Hello,
> 
> I'm new to hive and had been porting some Redshift SQL to Hive. Today I ran 
> across a table that uses update. 
> 
> After a somewhat time consuming translation process I was able to load the 
> create table definition. However the update part returned an error about 
> update not supported on non-acid table.
> 
> Then I read some documentation, added bucketing to the table definition, 
> added beeline xml file, etc. Then I launch the create table script like
> 
> 
> `beeline --hiveconf hive.support.concurrency=true 
> hive.exec.dynamic.partition.mode=nonstrict 
> hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager -f 
> ./events.sql`
> 
> beeline executes part of the SQL file but then provides no more output.
> 
> To figure out what was going on I tried to query the table
> 
> 
> beeline -hiveconf hive.support.concurrency=true 
> hive.exec.dynamic.partition.mode=nonstrict 
> hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager -e "Select * 
> from analytics.events limit 10;"
> Connecting to jdbc:hive2://REDACTED
> REDACTED
> Connected to: Apache Hive (version 2.3.2-amzn-0)
> Driver: Hive JDBC (version 2.3.2-amzn-0)
> Transaction isolation: TRANSACTION_REPEATABLE_READ
> Error: Error while compiling statement: FAILED: SemanticException [Error 
> 10265]: This command is not allowed on an ACID table 
> analytics_user_vws.liveramp_events with a non-ACID transaction manager. 
> Failed command: Select * from analytics_user_vws.liveramp_events limit 10 
> (state=42000,code=10265)
> Closing: 0: jdbc:hive2://
> 
> 
> Why am I not getting that error from creating the table? ( and no INFO from 
> beeline)
> 
> Also can somebody help me figure out what I need to do to be able to create 
> the ACID table?
> 
> Thanks