Hive dynamic partitioning and building tables, inserting data operations

1. Definition

Dynamic partitioning means that you do not need to add different INSERT statements for different partitions , partitions are not deterministic and need to be fetched from the data .

Set hive.exec.dynamic.partition=true;//using dynamic partitioning

(This statement can be viewed: set hive.exec.dynamic.partition;)

Set hive.exec.dynamic.partition.mode=nonstrict;//Unrestricted mode

If the pattern is strict, you must have a static partition and put it at the front.

SET hive.exec.max.dynamic.partitions.pernode=10000; Maximum number of dynamic partitions generated per node

Set hive.exec.max.dynamic.partitions=100000;, generate the maximum number of dynamic partitions, if the number of automatic partitions is greater than this parameter, will be an error

Set hive.exec.max.created.files=150000; The maximum number of files a task can create

Set dfs.datanode.max.xcievers=8192;//limit the number of open files at one time set dfs.datanode.max.xcievers=8192;//limit the maximum number of open files at one time

2. There is no essential difference between creating a static partition table and a dynamic partition table on the HQL statement, the main difference being the hive.exec.dynamic.partition.mode setting . 

Cases:

CREATE TABLE Order (

Namestring,

ID STRING

)

Partitioned by (Monthstring,info string)

ROW FORMAT delimited fields TERMINATED by ' \ t ';

3. Dynamic partition Table Insert data

Strict mode, need to have a static partition, and put on the front

Insert overwritetable Order (month= ' 2016-06 ', info)

Select Name,id,info (or substr (describtion,2,10) as info) from order;

info (or substr (describtion,2,10) as info):

The field info for a dynamic partition can be either a field in a table or an (alias) alias.

actual operation:

To create a partitioned table:

Creat Tabletest_info (

Name String,

ID string

)

Partitioned by (year String,month String,daystring,info string)

To insert data into a partitioned table:

Set mapred.reduce.tasks = 10;

Set Mapred.job.priority=very_high;

Insert Overwrite table test_infopartition (year= ", month=", "day=", info)

Select DeviceID as name,uuid as Id,ip asinfo from Ad_install limit 100;

when inserting data to Test_info, the Ad_install field does not allow dislocation, otherwise the data is wrong, but the name can be inconsistent.

Insert Result:

Under/usr/deployer/warehouse/tmp.db/test_info will appear year=2016 folder, under which there will be month=06 folder, under which there will be day=13 folder below the following image:

To clear the table's data:

TRUNCATE TABLE test_info;

To view the statement that created the table:

Show CREATE TABLE Test_info;

To delete a table:

drop table Test_info;

Create the database, specify the database storage path, default to Hive's Metastore path:

Create database if not exists Lin
Location '/tmp/data ';

To view the database:

Describe database Lin;

Hive The difference between creating an external table and an inner table is the location of the table folder, the external table can be set, and the internal table can delete the table when it is deleted, and the external table can only delete the data and the folder cannot be deleted .