Hive Dynamic Partitioning Insert experiment

experimental purposes
1. Verify that the partition table is dynamically partitioned into the function
2. Verify that you can use load for dynamic partition insertion

Experiment Steps
1. Write the following 4 rows of data in the local file/home/grid/a.txt:
Aaa,us,ca
Aaa,us,cb
Bbb,ca,bb
Bbb,ca,bc

2. Create a non-partitioned table and load data

CREATE TABLE T1 (name string, cty string, st STRING) ROW FORMAT delimited FIELDS terminated by ', ';
LOAD DATA local inpath '/home/grid/a.txt ' into TABLE T1;  
SELECT * from T1;  

Execute the command and result as shown in Figure 1.

Figure 1 3. Create an external partitioned table and load data dynamically

CREATE EXTERNAL TABLE T2 (name string) partitioned by (country string, state string);  

Set hive.exec.dynamic.partition=true;
Set hive.exec.dynamic.partition.mode=nonstrict;
Set hive.exec.max.dynamic.partitions.pernode=1000;
INSERT into TABLE T2 PARTITION (country, state) SELECT name, Cty, St. from T1;
INSERT into TABLE T2 PARTITION (country, state) SELECT name, Cty, St. from T1;

SELECT * from T2;  

Execute the command and result as shown in Figure 2.

As you can see in Figure 2, 8 data are loaded into the internal partition table, and the directory is created dynamically.

4. Edit the a.txt so that it has the following 4 rows of data, and then execute the following command.
Aaa,us,cd
Aaa,us,ce
Ccc,cb,bb
Ccc,cb,bc

LOAD DATA local inpath '/home/grid/a.txt ' OVERWRITE into TABLE t1;
INSERT OVERWRITE TABLE T2 PARTITION (country, state) SELECT name, Cty, St. from T1;
SELECT * from T2;  
dfs-ls/user/hive/warehouse/test.db/t2/; 
Dfs-ls/user/hive/warehouse/test.db/t2/country=us;

Execute the command and result as shown in Figure 3.

As you can see in Figure 3, there are now 12 data in the table, and the overwrite does not overwrite the original partition, but it appends 4 data and creates a new partitioned directory dynamically.
Execute the following command again:

INSERT OVERWRITE TABLE T2 PARTITION (country, state) SELECT name, Cty, St. from T1;
SELECT * from T2;  
dfs-ls/user/hive/warehouse/test.db/t2/; 
Dfs-ls/user/hive/warehouse/test.db/t2/country=us;

Execute the command and result as shown in Figure 4.

As you can see in Figure 4, the table still has 12 data, and the partition directory is unchanged.

On the dynamic partition insert, the internal and external parts of the table behave the same, experimental Conlio.

5. Use load to do dynamic partition insert

LOAD DATA local inpath '/home/grid/a.txt ' to TABLE T2 PARTITION (country, state);

Execute the command and result as shown in Figure 5.

As you can see in Figure 5, the load command does not support dynamic partition insertions.

Summary:
1. Overwrite does not delete existing partitioned directories, only appends new partitions and overwrites unpartitioned data for existing partitions.
2. Cannot use load for dynamic partition insertion