使用Hive数据库,从数据库连接开始就遇到问题。
Hive数据库跟MYSQL不一样,需要使用DBeaver工具使用。打开DBeaver,添加Hive的IP地址之后,输入token,点击编辑驱动。
这是使用Hive工具的配置内容,接下来才是重头戏,如何创建表单,插入表单,删除表单以及查询表单。
创建表单:
CREATE TABLE
CLUSTERED BY (
STORED AS ORC
TBLPROPERTIES (“transactional”=”true”);
— 单值分区表(Unique Value Partition)
CREATE TABLE
PARTITIONED BY (
CLUSTERED BY (
STORED AS ORC
TBLPROPERTIES (“transactional”=”true”);
— 范围分区表(Range Partition)
CREATE TABLE
PARTITIONED BY RANGE(
PARTITION [
PARTITION [partition_name_2] VALUE LESS THAN(key1_bound_value2, key2_bound_value2, …),
…
)
CLUSTERED BY (
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);
Original: https://www.cnblogs.com/mm150144978/p/16554907.html
Author: 大海之星
Title: 每日一问 hive如何插入数据
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/562723/
转载文章受原作者版权保护。转载请注明原作者出处!