Re: adding the column to hive partition

[Chaoyu Tang]

Is not it right that the values for the new column of existing partition
should be null?

On Thu, Aug 10, 2017 at 9:53 PM, mallik new  wrote:

> Hello All,
>
>  how to add the column to already partitioned table in hive, after i have
> added the column using cascade, the new column is showing null values, how
> to eliminate nulls and how this column pick the values.
>
>
>  looking for your support.
>
> thanks,
> Mallik.
>

adding the column to hive partition

[mallik new]

Hello All,

 how to add the column to already partitioned table in hive, after i have
added the column using cascade, the new column is showing null values, how
to eliminate nulls and how this column pick the values.


 looking for your support.

thanks,
Mallik.

[jira] [Created] (HIVE-5907) after adding new column to hive partition table,the new columns'value will be null be for reload all partitions

[alex kim (JIRA)]

alex kim created HIVE-5907:
--

 Summary: after adding new column to hive partition table,the new 
columns'value will be null be for reload all partitions
 Key: HIVE-5907
 URL: https://issues.apache.org/jira/browse/HIVE-5907
 Project: Hive
  Issue Type: Bug
Reporter: alex kim
Priority: Critical


when add new columns on external partition table ,query new column will get 
NULL value

0: jdbc:hive2://localhost:1 create external table 
test_alex1_replacereloadpart (id string,myname string) partitioned by(dt 
string);
No rows affected (0.395 seconds)
0: jdbc:hive2://localhost:1  alter table test_alex1_replacereloadpart add 
partition (dt='2013-11-28') location '2013-11-28';
No rows affected (0.339 seconds)
0: jdbc:hive2://localhost:1 insert overwrite table 
test_alex1_replacereloadpart partition(dt='2013-11-28') select id,myname from 
test_alex1;
+-+-+
| id  | myname  |
+-+-+
+-+-+
No rows selected (24.073 seconds)
0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+-+
|id | myname  | 
dt  |
+---+-+-+
| 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   | 
2013-11-28  |
| 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   | 
2013-11-28  |
| 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   | 
2013-11-28  |
| 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   | 
2013-11-28  |
| 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   | 
2013-11-28  |
| 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   | 
2013-11-28  |
| 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   | 
2013-11-28  |
| 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   | 
2013-11-28  |
| 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   | 
2013-11-28  |
| 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   | 
2013-11-28  |
+---+-+-+
0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart 
replace columns (id string,myname string,bee string);
No rows affected (0.202 seconds)

i delete old data from hive partition dir ,and add new data into the same dir

0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+--+-+
|id | myname  | 
bee  | dt  |
+---+-+--+-+
| 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   | 
 | 2013-11-28  |
| 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   | 
 | 2013-11-28  |
| 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   | 
 | 2013-11-28  |
| 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   | 
 | 2013-11-28  |
| 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   | 
 | 2013-11-28  |
| 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   | 
 | 2013-11-28  |
| 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   | 
 | 2013-11-28  |
| 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   | 
 | 2013-11-28  |
| 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   | 
 | 2013-11-28  |
| 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   | 
 | 2013-11-28  |
+---+-+--+-+

after reload the partition ,the new data appear

0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart drop 
partition (dt='2013-11-28');
No rows affected (0.286 seconds)

0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart add 
partition (dt='2013-11-28') location '2013-11-28';
No rows affected (0.551 seconds)
0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+--+-+
|id | myname  | 
bee  | dt

[jira] [Created] (HIVE-5907) after adding new column to hive partition table,the new columns'value will be null be for reload all partitions

[alex kim (JIRA)]

alex kim created HIVE-5907:
--

 Summary: after adding new column to hive partition table,the new 
columns'value will be null be for reload all partitions
 Key: HIVE-5907
 URL: https://issues.apache.org/jira/browse/HIVE-5907
 Project: Hive
  Issue Type: Bug
Reporter: alex kim
Priority: Critical


when add new columns on external partition table ,query new column will get 
NULL value

0: jdbc:hive2://localhost:1 create external table 
test_alex1_replacereloadpart (id string,myname string) partitioned by(dt 
string);
No rows affected (0.395 seconds)
0: jdbc:hive2://localhost:1  alter table test_alex1_replacereloadpart add 
partition (dt='2013-11-28') location '2013-11-28';
No rows affected (0.339 seconds)
0: jdbc:hive2://localhost:1 insert overwrite table 
test_alex1_replacereloadpart partition(dt='2013-11-28') select id,myname from 
test_alex1;
+-+-+
| id  | myname  |
+-+-+
+-+-+
No rows selected (24.073 seconds)
0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+-+
|id | myname  | 
dt  |
+---+-+-+
| 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   | 
2013-11-28  |
| 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   | 
2013-11-28  |
| 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   | 
2013-11-28  |
| 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   | 
2013-11-28  |
| 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   | 
2013-11-28  |
| 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   | 
2013-11-28  |
| 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   | 
2013-11-28  |
| 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   | 
2013-11-28  |
| 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   | 
2013-11-28  |
| 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   | 
2013-11-28  |
+---+-+-+
0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart 
replace columns (id string,myname string,bee string);
No rows affected (0.202 seconds)

i delete old data from hive partition dir ,and add new data into the same dir

0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+--+-+
|id | myname  | 
bee  | dt  |
+---+-+--+-+
| 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   | 
 | 2013-11-28  |
| 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   | 
 | 2013-11-28  |
| 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   | 
 | 2013-11-28  |
| 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   | 
 | 2013-11-28  |
| 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   | 
 | 2013-11-28  |
| 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   | 
 | 2013-11-28  |
| 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   | 
 | 2013-11-28  |
| 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   | 
 | 2013-11-28  |
| 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   | 
 | 2013-11-28  |
| 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   | 
 | 2013-11-28  |
+---+-+--+-+

after reload the partition ,the new data appear

0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart drop 
partition (dt='2013-11-28');
No rows affected (0.286 seconds)

0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart add 
partition (dt='2013-11-28') location '2013-11-28';
No rows affected (0.551 seconds)
0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+--+-+
|id | myname  | 
bee  | dt

[jira] [Updated] (HIVE-5907) after adding new column to hive partition table,the new columns'value will be null be for reload all partitions

[Xuefu Zhang (JIRA)]

 
partition (dt='2013-11-28') location '2013-11-28';
No rows affected (0.551 seconds)
0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+--+-+
|id | myname  | 
bee  | dt  |
+---+-+--+-+
| 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   | 
10   | 2013-11-28  |
| 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   | 
9| 2013-11-28  |
| 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   | 
8| 2013-11-28  |
| 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   | 
7| 2013-11-28  |
| 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   | 
6| 2013-11-28  |
| 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   | 
5| 2013-11-28  |
| 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   | 
4| 2013-11-28  |
| 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   | 
3| 2013-11-28  |
| 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   | 
2| 2013-11-28  |
| 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   | 
1| 2013-11-28  |
+---+-+--+-+



 after adding new column to hive partition table,the new columns'value will be 
 null be for reload all partitions
 ---

 Key: HIVE-5907
 URL: https://issues.apache.org/jira/browse/HIVE-5907
 Project: Hive
  Issue Type: Bug
Reporter: alex kim
Priority: Critical

 when add new columns on external partition table ,query new column will get 
 NULL value
 {code}
 0: jdbc:hive2://localhost:1 create external table 
 test_alex1_replacereloadpart (id string,myname string) partitioned by(dt 
 string);
 No rows affected (0.395 seconds)
 0: jdbc:hive2://localhost:1  alter table test_alex1_replacereloadpart 
 add partition (dt='2013-11-28') location '2013-11-28';
 No rows affected (0.339 seconds)
 0: jdbc:hive2://localhost:1 insert overwrite table 
 test_alex1_replacereloadpart partition(dt='2013-11-28') select id,myname from 
 test_alex1;
 +-+-+
 | id  | myname  |
 +-+-+
 +-+-+
 No rows selected (24.073 seconds)
 0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
 +---+-+-+
 |id | myname  
 | dt  |
 +---+-+-+
 | 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   
 | 2013-11-28  |
 | 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   
 | 2013-11-28  |
 | 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   
 | 2013-11-28  |
 | 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   
 | 2013-11-28  |
 | 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   
 | 2013-11-28  |
 | 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   
 | 2013-11-28  |
 | 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   
 | 2013-11-28  |
 | 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   
 | 2013-11-28  |
 | 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   
 | 2013-11-28  |
 | 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   
 | 2013-11-28  |
 +---+-+-+
 0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart 
 replace columns (id string,myname string,bee string);
 No rows affected (0.202 seconds)
 i delete old data from hive partition dir ,and add new data into the same dir
 0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
 +---+-+--+-+
 |id | myname  
 | bee  | dt  |
 +---+-+--+-+
 | 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   
 |  | 2013-11-28  |
 | 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   
 |  | 2013-11-28

[jira] [Updated] (HIVE-5907) after adding new column to hive partition table,the new columns'value will be null be for reload all partitions

[Xuefu Zhang (JIRA)]

 test_alex1_replacereloadpart add 
partition (dt='2013-11-28') location '2013-11-28';
No rows affected (0.551 seconds)
0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
+---+-+--+-+
|id | myname  | 
bee  | dt  |
+---+-+--+-+
| 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   | 
10   | 2013-11-28  |
| 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   | 
9| 2013-11-28  |
| 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   | 
8| 2013-11-28  |
| 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   | 
7| 2013-11-28  |
| 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   | 
6| 2013-11-28  |
| 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   | 
5| 2013-11-28  |
| 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   | 
4| 2013-11-28  |
| 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   | 
3| 2013-11-28  |
| 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   | 
2| 2013-11-28  |
| 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   | 
1| 2013-11-28  |
+---+-+--+-+



 after adding new column to hive partition table,the new columns'value will be 
 null be for reload all partitions
 ---

 Key: HIVE-5907
 URL: https://issues.apache.org/jira/browse/HIVE-5907
 Project: Hive
  Issue Type: Bug
Reporter: alex kim
Priority: Critical

 when add new columns on external partition table ,query new column will get 
 NULL value
 {code}
 0: jdbc:hive2://localhost:1 create external table 
 test_alex1_replacereloadpart (id string,myname string) partitioned by(dt 
 string);
 No rows affected (0.395 seconds)
 0: jdbc:hive2://localhost:1  alter table test_alex1_replacereloadpart 
 add partition (dt='2013-11-28') location '2013-11-28';
 No rows affected (0.339 seconds)
 0: jdbc:hive2://localhost:1 insert overwrite table 
 test_alex1_replacereloadpart partition(dt='2013-11-28') select id,myname from 
 test_alex1;
 +-+-+
 | id  | myname  |
 +-+-+
 +-+-+
 No rows selected (24.073 seconds)
 0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
 +---+-+-+
 |id | myname  
 | dt  |
 +---+-+-+
 | 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   
 | 2013-11-28  |
 | 20501B55B0C64273A5316C5078442569E3D87D24CA4E42D9A200E391417A7994  | 1   
 | 2013-11-28  |
 | 20501B55B0C64273A5316C5078442569814086424B2942919C97DF1D2D2C1F55  | 1   
 | 2013-11-28  |
 | 205018A818FB43A38CC29FF701E47D8B205018A818FB43A38CC29FF701E47D8B  | 1   
 | 2013-11-28  |
 | 205016D04EDA435CB578D059F931221EB9F06E42815243A486B611B96366DD01  | 1   
 | 2013-11-28  |
 | 205016D04EDA435CB578D059F931221E205016D04EDA435CB578D059F931221E  | 1   
 | 2013-11-28  |
 | 20500D24895641DBBBDBE71640E57887EA06A0AEB4324117B8125FC815297B36  | 1   
 | 2013-11-28  |
 | 205006E8A3EB4CA1997D947D89C5FD1B205006E8A3EB4CA1997D947D89C5FD1B  | 1   
 | 2013-11-28  |
 | 2050054E19B44C4D992D97C1661A26C32050054E19B44C4D992D97C1661A26C3  | 1   
 | 2013-11-28  |
 | 204fa32a43ef4aefac3b391562c5a25b7149D57B47E74C6F8C22CC30292FADF1  | 1   
 | 2013-11-28  |
 +---+-+-+
 0: jdbc:hive2://localhost:1 alter table test_alex1_replacereloadpart 
 replace columns (id string,myname string,bee string);
 No rows affected (0.202 seconds)
 {code}
 i delete old data from hive partition dir ,and add new data into the same dir
 {code}
 0: jdbc:hive2://localhost:1 select * from test_alex1_replacereloadpart;
 +---+-+--+-+
 |id | myname  
 | bee  | dt  |
 +---+-+--+-+
 | 20501B55B0C64273A5316C5078442569E425CC318ADC454CA163D72503489436  | 1   
 |  | 2013-11-28