在hive中造数据

在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);