Shulou(Shulou.com)06/01 Report--

This article will share with you about the common syntax of Hive partition tables. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Foreword:

If you know the partitioned table like the back of your hand, the syntax of the non-partitioned table is much simpler. You just need to remove the PARTITION-related fields.

1. Create a hive partition table

/ / No LOCATION specified

CREATE EXTERNAL TABLE

IF NOT EXISTS hive_partition_01 (

ROWKEY STRING

YEAR INT

MONTH INT

DAY INT

HOUR INT

MINUTE INT)

PARTITIONED BY (AGE INT)

ROW FORMAT DELIMITED FIELDS

TERMINATED BY', 'STORED AS TEXTFILE

Note: do not specify LOCATION, default is / user/hive/warehouse/*.db/table_name

/ / specify LOCATION

CREATE EXTERNAL TABLE

IF NOT EXISTS hive_partition_02 (

ROWKEY STRING

YEAR INT

MONTH INT

DAY INT

HOUR INT

MINUTE INT)

PARTITIONED BY (AGE INT)

ROW FORMAT DELIMITED FIELDS

TERMINATED BY','

STORED AS TEXTFILE

LOCATION 'hdfs://master:8020/user/hive/warehouse/hive_hbase.db/hive_partition_01'

Note: data cannot be found until partitions are added to the external partition table for the specified data path. You can execute the repair command on the hive command line: msck repair table tablename.

two。 Create Partition

ALTER TABLE hive_partition_01 ADD PARTITION (AGE= 1998)

Note: add directory age=1998 to HDFS after creating partition

3. Delete partition

ALTER TABLE hive_partition_01 DROP IF EXISTS PARTITION (age = 1998)

Note: HDFS partition data will not be deleted after the partition is deleted from the external table.

4. Load data into the HIVE table (four ways)

4.1 Import data from HDFS to Hive table

/ / load all data under a directory, only suitable for non-partitioned tables

LOAD DATA INPATH'/ emp.txt' OVERWRITE INTO TABLE hive_01

/ / INTO append write

LOAD DATA INPATH'/ emp.txt' INTO TABLE hive_partition_01 PARTITION (AGE=1998)

Note: data is loaded from HDFS, and the emp.txt file disappears after loading, which belongs to copy operation.

/ / OVERWRITE INTO overwrite

LOAD DATA INPATH'/ emp.txt' OVERWRITE INTO TABLE hive_partition_01 PARTITION (AGE=1998)

LOAD DATA INPATH'/ emp.txt' [OVERWRITE] INTO TABLE hive_partition_01 PARTITION (AGE=1999)

Note: execute this statement if the partition does not exist, it will be created automatically

4.2 Import data from the local file system into the Hive table

/ / INTO append write

LOAD DATA LOCAL INPATH'/ home/emp.txt' INTO TABLE hive_partition_01 PARTITION (AGE=1999)

/ / OVERWRITE INTO overwrite

LOAD DATA LOCAL INPATH'/ home/emp.txt' OVERWRITE INTO TABLE hive_partition_01 PARTITION (AGE=1999)

4.3 query data from other tables and import it into the Hive table

/ / INTO append write

Insert INTO table hive_partition_01 partition (AGE=1999) select rowkey, year, month, day, hour, minute from hive_partition_02

Note: the fields of hive_partition_02 table select must correspond to hive_partition_01 table. This statement performs the MapReduce task.

/ / OVERWRITE overwrite

Insert OVERWRITE table hive_partition_01 partition (AGE=1999) select rowkey, year, month, day, hour, minute from hive_partition_02

Note: all files under the HDFS partition directory will be overwritten.

4.4 when creating a table, query data from another table to the created table

Create table hive_partition_03 as select rowkey, year, month from hive_partition_01

Note: this statement will perform the MapReduce task, but select some fields

4.5 support for multi-table insertion

From hive_partition_01

> insert into table hive_partition_02

> partition (age=1998)

> select rowkey, year, month, day, hour, minute

> insert into table hive_partition_03

> select rowkey, year, month

Note: upload the same file to the HDFS directory under the same path, and the word copy will be added to the file name. As follows:

/ user/hive/warehouse/hive_hbase.db/hive_partition_01/age=1999/emp.txt

/ user/hive/warehouse/hive_hbase.db/hive_partition_01/age=1999/emp_copy_1.txt

Note: hive fields are not case sensitive

Thank you for reading! This is the end of this article on "what are the common grammars of Hive partition tables?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.