每日一问 hive如何插入数据

使用Hive数据库，从数据库连接开始就遇到问题。

Hive数据库跟MYSQL不一样，需要使用DBeaver工具使用。打开DBeaver，添加Hive的IP地址之后，输入token，点击编辑驱动。

 

 

 

这是使用Hive工具的配置内容，接下来才是重头戏，如何创建表单，插入表单，删除表单以及查询表单。

创建表单：

CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)

CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS

STORED AS ORC

TBLPROPERTIES ("transactional"="true");

-- 单值分区表（Unique Value Partition）

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

-- 范围分区表（Range Partition）

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;

-- 创建非分区ORC表。

DROP TABLE IF EXISTS ta;

CREATE TABLE ta (name STRING, age INT)

CLUSTERED BY (age) INTO 2 BUCKETS

STORED AS ORC TBLPROPERTIES ("transactional"="true");

-- 创建非分区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");

 

（增删改）语法：

• 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, ...)② 

 

举例

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