[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17421271#comment-17421271
]
hengtao tantai commented on HIVE-22098:
---
hi [~brahmareddy] i found this issus in non transactional
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: GuangMing Lu
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17405959#comment-17405959
]
Brahma Reddy Battula commented on HIVE-22098:
-
Hi [~luguangming] , [~zergtant] ,[~belugabehr] ,[~jithendhir92] and
[~kgyrtkirk].
is this data loss with managed-non transactional tables..?
are you guys using non-transactional tables with hive-3.x..?
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: GuangMing Lu
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17386060#comment-17386060
]
hengtao tantai commented on HIVE-22098:
---
hi all
I also recently encountered this problem with Hive 3.1.2.
As mentioned earlier, the new hash algorithm is used because of
bucketing_version=2, but the other bucketing_version uses the old algorithm.
Hive-21304 fixes this issue but needs to wait for HIVE 4.0.
You can modify bucketing_version to other numbers, like the one I used
{code:sql}
alter table [tablename] set tblproperties('bucketing_version'='1');{code}
will solve the problem temporarily.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: GuangMing Lu
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17216551#comment-17216551
]
Zoltan Haindrich commented on HIVE-22098:
-
I somehow missed this ticket - note that HIVE-21304 have fixed a few issues
with bucketing_version related stuff...so this might be fixed on master
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: GuangMing Lu
>Assignee: yongtaoliao
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17069176#comment-17069176
]
David Mollitor commented on HIVE-22098:
---
OK. I *thought* I was hitting this state from [HIVE-18983], but I did some
testing across different versions of HDP and found that {{CREATE TABLE LIKE}}
preserves the bucketing_version in some of them. My colleagues have corrected
me and instead pointed me at [HIVE-18735].
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: yongtaoliao
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17067919#comment-17067919
]
Sam An commented on HIVE-22098:
---
[~luguangming], hope the message finds you well. There are some production use
case for your solution. Would you like to complete this patch?
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17065237#comment-17065237
]
David Mollitor commented on HIVE-22098:
---
[~jithendhir92] Sorry for any confusion.
I am not saying that this issue cannot be reproduced by using the method you
have propose. I am saying that this issue is all the more important because
there are MULTIPLE ways to get the table into this state.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17063119#comment-17063119
]
JithendhiraKumar commented on HIVE-22098:
-
[~belugabehr] Thank you for looking into this issue.
In this case, the tables are not created using `CREATE TABLE LIKE`. They are
created with `CREATE TABLE` Commands. In my previous comment, I have provided
the DDLs and the sample query to reproduce the issue.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17061197#comment-17061197
]
David Mollitor commented on HIVE-22098:
---
I linked this case to [HIVE-18983], because when a table is created with
{{CREATE TABLE LIKE}}, then the bucket_version information is missing and it
later triggers this issue.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17042719#comment-17042719
]
JithendhiraKumar commented on HIVE-22098:
-
Attaching test_data.csv.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17042715#comment-17042715
]
JithendhiraKumar commented on HIVE-22098:
-
[~luguangming] has already mentioned the steps to reproduce *Scenario 1.*
Here are Steps To Reproduce *Scenario 2*: (test_data.csv can be found in the
attachments)
{code:java}
CREATE TABLE `join_test_1`(`id` string, `first` string, `email` string) ROW
FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH
SERDEPROPERTIES ('field.delim'=',', 'serialization.format'=',') STORED AS
INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' TBLPROPERTIES
('bucketing_version'='1');
LOAD DATA LOCAL INPATH '/uploads/test_data.csv' OVERWRITE INTO TABLE
join_test_1;
CREATE TABLE `join_test_2`(`id` string, `first` string, `email` string) ROW
FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH
SERDEPROPERTIES ('field.delim'=',', 'serialization.format'=',') STORED AS
INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' TBLPROPERTIES
('bucketing_version'='2');
LOAD DATA LOCAL INPATH '/uploads/test_data.csv' OVERWRITE INTO TABLE
join_test_2;
Query:
set mapred.reduce.tasks=2;
set hive.auto.convert.join=false;
SELECT * from (SELECT id from join_test_1) as tbl1 LEFT JOIN (SELECT id from
join_test_2) as tbl2 on tbl1.id = tbl2.id;
OutPut: (Wrong Results/Data Loss)
+--+--+
| tbl1.id | tbl2.id |
+--+--+
| 0| NULL |
| 2| NULL |
| 4| NULL |
| 6| NULL |
| 8| 8|
| 1| NULL |
| 3| NULL |
| 5| 5|
| 7| NULL |
| 9| NULL |
+--+--+
Expected Result:
+--+--+
| tbl1.id | tbl2.id |
+--+--+
| 1| 1|
| 3| 3|
| 7| 7|
| 8| 8|
| 9| 9|
| 0| 0|
| 2| 2|
| 4| 4|
| 5| 5|
| 6| 6|
+--+--+
{code}
.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0, 3.1.2
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Labels: data-loss, wrongresults
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and no of reducers is greater
> than 2, the result is incorrect (*data loss*).
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17042419#comment-17042419
]
JithendhiraKumar commented on HIVE-22098:
-
I backported HIVE-20164, HIVE-21167, HIVE-22429 in the same order on 3.1.2
and verified our query. Still, the results were wrong. It didn't solve the
issue.
[~rameshkumar] Any other leads would be greatly appreciated.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17042335#comment-17042335
]
Ramesh Kumar Thangarajan commented on HIVE-22098:
-
[~jithendhir92] I see. 3.1.2 misses another patch too. Can you try applying the
patch HIVE-20164.01-branch-3.patch in
https://issues.apache.org/jira/browse/HIVE-20164 and then apply
https://issues.apache.org/jira/browse/HIVE-22429 on top of it?
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Blocker
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17041987#comment-17041987
]
JithendhiraKumar commented on HIVE-22098:
-
[~rameshkumar]
Thank you for the suggestion. There were no insertions done to the old tables
after the migration. Anyway, I tried to backport the mentioned by you
https://issues.apache.org/jira/browse/HIVE-22429 , It didn't apply cleanly on
release-3.1.2.
But, when we tried the patch suggested by [~luguangming], it solved our issue,
the left join gave correct results for our use case, but this patch works only
for MR, it is not working for TEZ.
[~rameshkumar] Can you please suggest any other alternatives/workarounds if
available?
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17041296#comment-17041296
]
Ramesh Kumar Thangarajan commented on HIVE-22098:
-
[~jithendhir92] Do you know if there were any inserts done to the migrated
table after migration? If yes, then this probably might be related to
https://issues.apache.org/jira/browse/HIVE-22429. We can try backporting the
change to 3.1.2 and verify if it fixes the issue.
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17040749#comment-17040749
]
JithendhiraKumar commented on HIVE-22098:
-
Hi, we are stuck with similar issue, after upgrading from 2.3 to 3.1.2. The
tables which were created in Hive 2.3 has no bucketing version in its table
properties, but when new tables are created in Hive 3.1.2 they are created with
bucketing_version 2. When we do a left join between these old and new tables,
in a few cases the results are wrong.
[~djaiswal] [~jdere] Can you guys please review this patch?
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17035824#comment-17035824
]
Hive QA commented on HIVE-22098:
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12977341/HIVE-22098.1.patch
{color:red}ERROR:{color} -1 due to build exiting with an error
Test results:
https://builds.apache.org/job/PreCommit-HIVE-Build/20584/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/20584/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-20584/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Tests exited with: NonZeroExitCodeException
Command 'bash /data/hiveptest/working/scratch/source-prep.sh' failed with exit
status 1 and output '+ date '+%Y-%m-%d %T.%3N'
2020-02-13 00:34:22.360
+ [[ -n /usr/lib/jvm/java-8-openjdk-amd64 ]]
+ export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
+ JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
+ export
PATH=/usr/lib/jvm/java-8-openjdk-amd64/bin/:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
+
PATH=/usr/lib/jvm/java-8-openjdk-amd64/bin/:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
+ export 'ANT_OPTS=-Xmx1g -XX:MaxPermSize=256m '
+ ANT_OPTS='-Xmx1g -XX:MaxPermSize=256m '
+ export 'MAVEN_OPTS=-Xmx1g '
+ MAVEN_OPTS='-Xmx1g '
+ cd /data/hiveptest/working/
+ tee /data/hiveptest/logs/PreCommit-HIVE-Build-20584/source-prep.txt
+ [[ false == \t\r\u\e ]]
+ mkdir -p maven ivy
+ [[ git = \s\v\n ]]
+ [[ git = \g\i\t ]]
+ [[ -z master ]]
+ [[ -d apache-github-source-source ]]
+ [[ ! -d apache-github-source-source/.git ]]
+ [[ ! -d apache-github-source-source ]]
+ date '+%Y-%m-%d %T.%3N'
2020-02-13 00:34:22.362
+ cd apache-github-source-source
+ git fetch origin
+ git reset --hard HEAD
HEAD is now at fcfc71b HIVE-10362: Support Type check/conversion in dynamic
partition column(Karen Coppage, reviewed by Vineet Garg, Zoltan Haindrich)
+ git clean -f -d
Removing standalone-metastore/metastore-server/src/gen/
+ git checkout master
Already on 'master'
Your branch is up-to-date with 'origin/master'.
+ git reset --hard origin/master
HEAD is now at fcfc71b HIVE-10362: Support Type check/conversion in dynamic
partition column(Karen Coppage, reviewed by Vineet Garg, Zoltan Haindrich)
+ git merge --ff-only origin/master
Already up-to-date.
+ date '+%Y-%m-%d %T.%3N'
2020-02-13 00:34:23.508
+ rm -rf ../yetus_PreCommit-HIVE-Build-20584
+ mkdir ../yetus_PreCommit-HIVE-Build-20584
+ git gc
+ cp -R . ../yetus_PreCommit-HIVE-Build-20584
+ mkdir /data/hiveptest/logs/PreCommit-HIVE-Build-20584/yetus
+ patchCommandPath=/data/hiveptest/working/scratch/smart-apply-patch.sh
+ patchFilePath=/data/hiveptest/working/scratch/build.patch
+ [[ -f /data/hiveptest/working/scratch/build.patch ]]
+ chmod +x /data/hiveptest/working/scratch/smart-apply-patch.sh
+ /data/hiveptest/working/scratch/smart-apply-patch.sh
/data/hiveptest/working/scratch/build.patch
Trying to apply the patch with -p0
error: a/ql/src/java/org/apache/hadoop/hive/ql/exec/mr/ExecMapper.java: does
not exist in index
Trying to apply the patch with -p1
error: patch failed:
ql/src/java/org/apache/hadoop/hive/ql/exec/mr/ExecMapper.java:20
Falling back to three-way merge...
Applied patch to
'ql/src/java/org/apache/hadoop/hive/ql/exec/mr/ExecMapper.java' with conflicts.
Going to apply patch with: git apply -p1
error: patch failed:
ql/src/java/org/apache/hadoop/hive/ql/exec/mr/ExecMapper.java:20
Falling back to three-way merge...
Applied patch to
'ql/src/java/org/apache/hadoop/hive/ql/exec/mr/ExecMapper.java' with conflicts.
U ql/src/java/org/apache/hadoop/hive/ql/exec/mr/ExecMapper.java
+ result=1
+ '[' 1 -ne 0 ']'
+ rm -rf yetus_PreCommit-HIVE-Build-20584
+ exit 1
'
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12977341 - PreCommit-HIVE-Build
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=1703#comment-1703
]
JithendhiraKumar commented on HIVE-22098:
-
[~luguangming] Was there any progress on the patch being made available in
master?
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16912911#comment-16912911
]
LuGuangMing commented on HIVE-22098:
please check and verify this patch update,
https://issues.apache.org/jira/secure/attachment/12977341/HIVE-22098.1.patch
if ok, you can be merged it to master branch, thanks all commiters
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: HIVE-22098.1.patch, image-2019-08-12-18-45-15-771.png,
> join_test.sql, table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
[jira] [Commented] (HIVE-22098) Data loss occurs when multiple tables are join with different bucket_version
[
https://issues.apache.org/jira/browse/HIVE-22098?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16905063#comment-16905063
]
LuGuangMing commented on HIVE-22098:
Modifying the hashcode algorithm to get key is consistent, and using the
recommended getBucketHashCode to avoid such problems. To maintain a unified
hash algorithm, first of all, we need to ensure that the bucket Version of the
table is consistent.
!image-2019-08-12-18-45-15-771.png!
bucketVersion=-1(default) join bucketVersion=1, result consistent is 1 to
compatible old table hash algorithm.
bucketVersion=-1(default) join bucketVersion=2,result consistent is 2 to use
new hash algorithm.
bucketVersion=1 join bucketVersion=2, result consistent is 2 to use new hash
algorithm, for old table could be join with new table. HIVE-21167 . HIVE-18910
> Data loss occurs when multiple tables are join with different bucket_version
>
>
> Key: HIVE-22098
> URL: https://issues.apache.org/jira/browse/HIVE-22098
> Project: Hive
> Issue Type: Bug
> Components: Operators
>Affects Versions: 3.1.0
>Reporter: LuGuangMing
>Assignee: LuGuangMing
>Priority: Major
> Attachments: image-2019-08-12-18-45-15-771.png, join_test.sql,
> table_a_data.orc, table_b_data.orc, table_c_data.orc
>
>
> When different bucketVersion of tables do join and reducers number greater
> than 2, result is easy to lose data.
> *Scenario 1*: Three tables join. The temporary result data of table_a in the
> first table and table_b in the second table joins result is recorded as
> tmp_a_b, When it joins with the third table, the bucket_version=2 of the
> table created by default after hive-3.0.0, temporary data tmp_a_b initialized
> the bucketVerison=-1, and then ReduceSinkOperator Verketison=-1 is joined. In
> the init method, the hash algorithm of selecting join column is selected
> according to bucketVersion. If bucketVersion = 2 and is not an acid
> operation, it will acquired the new algorithm of hash. Otherwise, the old
> algorithm of hash is acquired. Because of the inconsistency of the algorithm
> of hash, the partition of data allocation caused are different. At stage of
> Reducer, Data with the same key can not be paired resulting in data loss.
> *Scenario 2*: create two test tables, create table
> table_bucketversion_1(col_1 string, col_2 string) TBLPROPERTIES
> ('bucketing_version'='1'); table_bucketversion_2(col_1 string, col_2 string)
> TBLPROPERTIES ('bucketing_version'='2');
> when use table_bucketversion_1 to join table_bucketversion_2, partial result
> data will be loss due to bucketVerison is different.
>
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
