Re: create transactional table issue
I rolled my own service by docker.
I just figured out it's a problem with standalone hive metastore 3.0.0.
After downgrading to hive 2.3.7 metastore service, everything works fine.
FYI, the ddl was executed failed on (apache hive 3.1.2, standalone hive
metastore 3.0.0, apache hadoop 3.2.1), but successful on (apache hive
2.3.7, apache hive 2.3.7 metastore service, apache hadoop 2.10.0).
Maybe I should stick with 2.x, instead of 3.x.
Thank you for help!
On Mon, May 18, 2020, 00:50 Mich Talebzadeh
wrote:
> In Hive 3.1.1 the thread owner table creation works fine
>
> hive --version
> Hive 3.1.1
>
> 0: jdbc:hive2://rhes75:10099/default> create table test.dllm ( b string )
> partitioned by (a int) clustered by (b) into 2 buckets stored as orc
> tblproperties('transactional'='true')
> . . . . . . . . . . . . . . . . . . > No rows affected (0.246 seconds)
>
> HTH
>
> [image: image.png]
>
>
>
> LinkedIn *
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> 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 Sun, 17 May 2020 at 17:30, Shawn Weeks
> wrote:
>
>> The Hive Streaming API has changed in Hive 3.x and that might have
>> something to do with it I’m not sure as I’ve never used Flume. Are you
>> using a vendor distribution of Hive 3.x or are you rolling your own?
>>
>>
>>
>> What does “show create table your_table;” show? It is possible to turn
>> off acid if your setting Hive up manually.
>>
>>
>>
>> Thanks
>>
>>
>>
>> *From: *Huang Chiming
>> *Reply-To: *"user@hive.apache.org"
>> *Date: *Saturday, May 16, 2020 at 11:09 AM
>> *To: *"user@hive.apache.org"
>> *Subject: *Re: create transactional table issue
>>
>>
>>
>> Hi @Shawn,
>>
>>
>>
>> Can tblproperties('transactional'='true') be omitted starting from 3.x?
>>
>>
>>
>> I just create a hive table without tblproperties('transactional'='true')
>> and ingest data from flume hive sink (
>> https://flume.apache.org/releases/content/1.9.0/FlumeUserGuide.html#hive-sink),
>> flume throws an exception like `Caused by:
>> org.apache.hive.hcatalog.streaming.InvalidTable: Invalid table db:test,
>> table:dllm: is not an Acid table`.
>>
>>
>>
>> How can I create a hive ACID table? I'd like to ingest stream data from
>> flume to hive.
>>
>>
>>
>> Thank you for help!
>>
>>
>>
>> Best Wishes,
>>
>> Chiming HUANG
>>
>>
>>
>>
>>
>> On Sat, May 16, 2020 at 11:43 PM Huang Chiming
>> wrote:
>>
>> Thanks for clarifying!
>>
>>
>>
>> Best Wishes,
>>
>> Chiming HUANG
>>
>>
>>
>>
>>
>> On Sat, May 16, 2020 at 11:40 PM Shawn Weeks
>> wrote:
>>
>> Starting in Hive 3.x all internal tables are transactional by default.
>> Unless you need the buckets you should be able to just say create table and
>> drop everything after partitioned by.
>>
>>
>>
>> Thanks
>>
>> Shawn
>>
>>
>>
>> *From: *Huang Chiming
>> *Reply-To: *"user@hive.apache.org"
>> *Date: *Saturday, May 16, 2020 at 10:32 AM
>> *To: *"user@hive.apache.org"
>> *Subject: *create transactional table issue
>>
>>
>>
>> Hi guys,
>>
>>
>>
>> I use hive 3.1.2 connecting to standalone metastore and execute ddl in
>> beeline/hive-cli failed, the error message is really weird to me… Anyone
>> ever encounter the same issue?
>>
>>
>>
>> ```
>>
>> create table test.dllm ( b string )
>>
>> partitioned by (a int)
>>
>> clustered by (b) into 2 buckets
>>
>> stored as orc tblproperties('transactional'='true');
>>
>>
>>
>> FAILED: Execution Error, return code 1 from
>> org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must
>> be stored using an ACID compliant format (such as ORC): test.dllm)
>>
>> ```
>>
>>
>>
>>
>>
>> Best Wishes,
>>
>> Chiming HUANG
>>
>>
Re: create transactional table issue
In Hive 3.1.1 the thread owner table creation works fine
hive --version
Hive 3.1.1
0: jdbc:hive2://rhes75:10099/default> create table test.dllm ( b string )
partitioned by (a int) clustered by (b) into 2 buckets stored as orc
tblproperties('transactional'='true')
. . . . . . . . . . . . . . . . . . > No rows affected (0.246 seconds)
HTH
[image: image.png]
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
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 Sun, 17 May 2020 at 17:30, Shawn Weeks wrote:
> The Hive Streaming API has changed in Hive 3.x and that might have
> something to do with it I’m not sure as I’ve never used Flume. Are you
> using a vendor distribution of Hive 3.x or are you rolling your own?
>
>
>
> What does “show create table your_table;” show? It is possible to turn off
> acid if your setting Hive up manually.
>
>
>
> Thanks
>
>
>
> *From: *Huang Chiming
> *Reply-To: *"user@hive.apache.org"
> *Date: *Saturday, May 16, 2020 at 11:09 AM
> *To: *"user@hive.apache.org"
> *Subject: *Re: create transactional table issue
>
>
>
> Hi @Shawn,
>
>
>
> Can tblproperties('transactional'='true') be omitted starting from 3.x?
>
>
>
> I just create a hive table without tblproperties('transactional'='true')
> and ingest data from flume hive sink (
> https://flume.apache.org/releases/content/1.9.0/FlumeUserGuide.html#hive-sink),
> flume throws an exception like `Caused by:
> org.apache.hive.hcatalog.streaming.InvalidTable: Invalid table db:test,
> table:dllm: is not an Acid table`.
>
>
>
> How can I create a hive ACID table? I'd like to ingest stream data from
> flume to hive.
>
>
>
> Thank you for help!
>
>
>
> Best Wishes,
>
> Chiming HUANG
>
>
>
>
>
> On Sat, May 16, 2020 at 11:43 PM Huang Chiming wrote:
>
> Thanks for clarifying!
>
>
>
> Best Wishes,
>
> Chiming HUANG
>
>
>
>
>
> On Sat, May 16, 2020 at 11:40 PM Shawn Weeks
> wrote:
>
> Starting in Hive 3.x all internal tables are transactional by default.
> Unless you need the buckets you should be able to just say create table and
> drop everything after partitioned by.
>
>
>
> Thanks
>
> Shawn
>
>
>
> *From: *Huang Chiming
> *Reply-To: *"user@hive.apache.org"
> *Date: *Saturday, May 16, 2020 at 10:32 AM
> *To: *"user@hive.apache.org"
> *Subject: *create transactional table issue
>
>
>
> Hi guys,
>
>
>
> I use hive 3.1.2 connecting to standalone metastore and execute ddl in
> beeline/hive-cli failed, the error message is really weird to me… Anyone
> ever encounter the same issue?
>
>
>
> ```
>
> create table test.dllm ( b string )
>
> partitioned by (a int)
>
> clustered by (b) into 2 buckets
>
> stored as orc tblproperties('transactional'='true');
>
>
>
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must
> be stored using an ACID compliant format (such as ORC): test.dllm)
>
> ```
>
>
>
>
>
> Best Wishes,
>
> Chiming HUANG
>
>
Re: create transactional table issue
The Hive Streaming API has changed in Hive 3.x and that might have something to
do with it I’m not sure as I’ve never used Flume. Are you using a vendor
distribution of Hive 3.x or are you rolling your own?
What does “show create table your_table;” show? It is possible to turn off acid
if your setting Hive up manually.
Thanks
From: Huang Chiming
Reply-To: "user@hive.apache.org"
Date: Saturday, May 16, 2020 at 11:09 AM
To: "user@hive.apache.org"
Subject: Re: create transactional table issue
Hi @Shawn,
Can tblproperties('transactional'='true') be omitted starting from 3.x?
I just create a hive table without tblproperties('transactional'='true') and
ingest data from flume hive sink
(https://flume.apache.org/releases/content/1.9.0/FlumeUserGuide.html#hive-sink),
flume throws an exception like `Caused by:
org.apache.hive.hcatalog.streaming.InvalidTable: Invalid table db:test,
table:dllm: is not an Acid table`.
How can I create a hive ACID table? I'd like to ingest stream data from flume
to hive.
Thank you for help!
Best Wishes,
Chiming HUANG
On Sat, May 16, 2020 at 11:43 PM Huang Chiming
mailto:nanoh...@gmail.com>> wrote:
Thanks for clarifying!
Best Wishes,
Chiming HUANG
On Sat, May 16, 2020 at 11:40 PM Shawn Weeks
mailto:swe...@weeksconsulting.us>> wrote:
Starting in Hive 3.x all internal tables are transactional by default. Unless
you need the buckets you should be able to just say create table and drop
everything after partitioned by.
Thanks
Shawn
From: Huang Chiming mailto:nanoh...@gmail.com>>
Reply-To: "user@hive.apache.org<mailto:user@hive.apache.org>"
mailto:user@hive.apache.org>>
Date: Saturday, May 16, 2020 at 10:32 AM
To: "user@hive.apache.org<mailto:user@hive.apache.org>"
mailto:user@hive.apache.org>>
Subject: create transactional table issue
Hi guys,
I use hive 3.1.2 connecting to standalone metastore and execute ddl in
beeline/hive-cli failed, the error message is really weird to me… Anyone ever
encounter the same issue?
```
create table test.dllm ( b string )
partitioned by (a int)
clustered by (b) into 2 buckets
stored as orc tblproperties('transactional'='true');
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must be
stored using an ACID compliant format (such as ORC): test.dllm)
```
Best Wishes,
Chiming HUANG
Re: create transactional table issue
Hi @Shawn,
Can tblproperties('transactional'='true') be omitted starting from 3.x?
I just create a hive table without tblproperties('transactional'='true')
and ingest data from flume hive sink (
https://flume.apache.org/releases/content/1.9.0/FlumeUserGuide.html#hive-sink),
flume throws an exception like `Caused by:
org.apache.hive.hcatalog.streaming.InvalidTable: Invalid table db:test,
table:dllm: is not an Acid table`.
How can I create a hive ACID table? I'd like to ingest stream data from
flume to hive.
Thank you for help!
Best Wishes,
Chiming HUANG
On Sat, May 16, 2020 at 11:43 PM Huang Chiming wrote:
> Thanks for clarifying!
>
> Best Wishes,
> Chiming HUANG
>
>
> On Sat, May 16, 2020 at 11:40 PM Shawn Weeks
> wrote:
>
>> Starting in Hive 3.x all internal tables are transactional by default.
>> Unless you need the buckets you should be able to just say create table and
>> drop everything after partitioned by.
>>
>>
>>
>> Thanks
>>
>> Shawn
>>
>>
>>
>> *From: *Huang Chiming
>> *Reply-To: *"user@hive.apache.org"
>> *Date: *Saturday, May 16, 2020 at 10:32 AM
>> *To: *"user@hive.apache.org"
>> *Subject: *create transactional table issue
>>
>>
>>
>> Hi guys,
>>
>>
>>
>> I use hive 3.1.2 connecting to standalone metastore and execute ddl in
>> beeline/hive-cli failed, the error message is really weird to me… Anyone
>> ever encounter the same issue?
>>
>>
>>
>> ```
>>
>> create table test.dllm ( b string )
>>
>> partitioned by (a int)
>>
>> clustered by (b) into 2 buckets
>>
>> stored as orc tblproperties('transactional'='true');
>>
>>
>>
>> FAILED: Execution Error, return code 1 from
>> org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must
>> be stored using an ACID compliant format (such as ORC): test.dllm)
>>
>> ```
>>
>>
>>
>>
>>
>> Best Wishes,
>>
>> Chiming HUANG
>>
>
Re: create transactional table issue
Thanks for clarifying!
Best Wishes,
Chiming HUANG
On Sat, May 16, 2020 at 11:40 PM Shawn Weeks
wrote:
> Starting in Hive 3.x all internal tables are transactional by default.
> Unless you need the buckets you should be able to just say create table and
> drop everything after partitioned by.
>
>
>
> Thanks
>
> Shawn
>
>
>
> *From: *Huang Chiming
> *Reply-To: *"user@hive.apache.org"
> *Date: *Saturday, May 16, 2020 at 10:32 AM
> *To: *"user@hive.apache.org"
> *Subject: *create transactional table issue
>
>
>
> Hi guys,
>
>
>
> I use hive 3.1.2 connecting to standalone metastore and execute ddl in
> beeline/hive-cli failed, the error message is really weird to me… Anyone
> ever encounter the same issue?
>
>
>
> ```
>
> create table test.dllm ( b string )
>
> partitioned by (a int)
>
> clustered by (b) into 2 buckets
>
> stored as orc tblproperties('transactional'='true');
>
>
>
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must
> be stored using an ACID compliant format (such as ORC): test.dllm)
>
> ```
>
>
>
>
>
> Best Wishes,
>
> Chiming HUANG
>
Re: create transactional table issue
Starting in Hive 3.x all internal tables are transactional by default. Unless
you need the buckets you should be able to just say create table and drop
everything after partitioned by.
Thanks
Shawn
From: Huang Chiming
Reply-To: "user@hive.apache.org"
Date: Saturday, May 16, 2020 at 10:32 AM
To: "user@hive.apache.org"
Subject: create transactional table issue
Hi guys,
I use hive 3.1.2 connecting to standalone metastore and execute ddl in
beeline/hive-cli failed, the error message is really weird to me… Anyone ever
encounter the same issue?
```
create table test.dllm ( b string )
partitioned by (a int)
clustered by (b) into 2 buckets
stored as orc tblproperties('transactional'='true');
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:The table must be
stored using an ACID compliant format (such as ORC): test.dllm)
```
Best Wishes,
Chiming HUANG
