在hive中造数据
1 . 创建表单
-
分区表
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...) CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
-
单值分区表
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...) PARTITIONED BY (<partition_key> <data_type>) CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");
-
范围分区表
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...) PARTITIONED BY RANGE(<partition_key1> <data_type>, <partition_key2> <data_type>, ...) ( PARTITION [<partition_name_1>] VALUE LESS THAN(<key1_bound_value1>, <key2_bound_value1>, ...), PARTITION [partition_name_2] VALUE LESS THAN(key1_bound_value2, key2_bound_value2, ...), ... ) CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true"); SET transaction.type=inceptor;
2.建表实例
-
创建非分区ORC表
DROP TABLE IF EXISTS tg; CREATE TABLE tg (name STRING, gpa DOUBLE) CLUSTERED BY (name) INTO 4 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");
-
创建单值分区ORC表
DROP TABLE IF EXISTS test; CREATE TABLE test (a INT, b STRING, c DOUBLE) PARTITIONED BY (date STRING) CLUSTERED BY (c) INTO 8 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");
-
创建范围分区ORC表
DROP TABLE IF EXISTS t5; CREATE TABLE t5(id INT, value INT) PARTITIONED BY RANGE(amount INT) ( PARTITION less1 VALUES LESS THAN (1), PARTITION less10 VALUES LESS THAN (10), PARTITION less100 VALUES LESS THAN (100) ) CLUSTERED BY (id) INTO 5 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");
3. 重点来了
语法:
(增删改)语法:
• INSERT INTO … VALUES
• UPDATE
• DELETE
• MERGE INTO
-- 非分区表
INSERT INTO table_name VALUES (value, value, ...)
-- 单值分区表
INSERT INTO table_name PARTITION (partition_key = value) VALUES (value, value, ...)
-- 范围分区表
INSERT INTO table_name VALUES (value, value, ...) ①
INSERT INTO table_name PARTITION partition_name VALUES (value, value, ...)②
4. 最终实例
SET transaction.type=inceptor;
-- 向非分区表单条插入
INSERT INTO ta VALUES ('Zhang San', 18);
-- 向单值分区表单条插入
INSERT INTO test PARTITION (date = '20150612')
VALUES (1, 'a', 1.0);
-- 向范围分区表单条插入
INSERT INTO t5 VALUES(0,5,99);
INSERT INTO t5 PARTITION less10 VALUES(0,5,9);
标签:...,中造,INSERT,INTO,hive,分区表,VALUES,value,数据
From: https://www.cnblogs.com/atao-BigData/p/16823793.html