Hive - 多种表类型的CURD测试-526互联

关于torc、textfile、orc、es、hyperdrive表的CURD测试

TORC（支持事务的orc表）测试

-- torc测试
-- =======
CREATE TABLE default.torc_test(
    id int,
    name string
) 
clustered BY (id) INTO 3 buckets 
stored AS orc 
tblproperties("transactional"="true");

-- INSERT 测试(支持)
INSERT INTO default.torc_test values(1,'manager');
INSERT INTO default.torc_test values(2,'manager');
INSERT INTO default.torc_test values(3,'zookeeper'),(4,'hdfs');

-- UPDATE 测试(支持)
UPDATE TABLE default.torc_test SET name='transwarp' WHERE id=1;

-- TRUNCATE 测试(支持)
TRUNCATE TABLE default.torc_test;

-- DELETE 测试(支持)
DELETE FROM default.torc_test WHERE id=2;

-- select 查询(支持)
SELECT * FROM default.torc_test;
SELECT * FROM default.torc_test WHERE id = 1;

-- 删除表(支持)
DROP TABLE IF EXISTS default.torc_test;

TORC（分区表）测试

-- torc(分区表)测试
-- =======
CREATE TABLE default.torc_partition_test(
    id int,
    name string
) 
PARTITIONED by (level string) 
clustered BY (id) INTO 3 buckets 
stored AS orc tblproperties("transactional"="true");

-- INSERT 测试(支持)
INSERT INTO default.torc_partition_test partition(level="aaa") values(1,'manager');
INSERT INTO default.torc_partition_test partition(level="aaa") values(2,'manager');
INSERT INTO default.torc_partition_test partition(level="bbb") values(3,'zookeeper'),(4,'hdfs');

-- UPDATE 测试(支持)
UPDATE TABLE default.torc_partition_test SET name='transwarp' WHERE id=1;

-- TRUNCATE 测试(支持)
TRUNCATE TABLE default.torc_partition_test;

-- DELETE 测试(支持)
DELETE FROM default.torc_partition_test WHERE id=2;

-- select 查询(支持)
SELECT * FROM default.torc_partition_test;
SELECT * FROM default.torc_partition_test WHERE id = 1;

TEXTFILE 表测试

-- text表测试
-- ========
CREATE TABLE default.text_test(id int,name string);
-- 默认创建的表是text表,不加关键字external则为内部表,且text表不支持事务操作
INSERT INTO default.text_test SELECT * FROM default.torc_test;
-- 不支持如下insert方式(不支持事务操作)
-- INSERT INTO default.text_test values(1,'manager');
-- UPDATE 测试(测试不通过,不支持事务操作)
-- UPDATE TABLE default.text_test SET name="hdfs1" WHERE id = 4;
-- DELETE 测试(测试不通过,不支持事务操作)
-- DELETE FROM default.text_test WHERE id=4;
-- TRUNCATE 测试(支持)
TRUNCATE TABLE default.text_test;
-- SELECT 查询(支持)
SELECT * FROM default.text_test;
-- 删除表(支持)
DROP TABLE IF EXISTS default.text_test;

ORC 表测试

-- orc表测试
-- ========
CREATE TABLE orc_test(id int,name string) stored AS orc;
-- 测试添加数据(支持)
INSERT INTO default.orc_test SELECT * FROM default.torc_test;
-- 不支持如下insert方式(不支持事务操作)
-- INSERT INTO orc_test values(1,'manager');
-- UPDATE 测试(测试不通过,不支持事务操作)
-- UPDATE TABLE default.orc_test SET name="hdfs1" WHERE id = 4;
-- DELETE 测试(测试不通过,不支持事务操作)
-- DELETE FROM default.orc_test WHERE id=4;
-- TRUNCATE 测试(支持)
TRUNCATE TABLE default.orc_test;
-- select 测试(支持)
SELECT * FROM orc_test;
-- 删除表(支持)
DROP TABLE IF EXISTS default.orc_test;

ES（ElasticSearch表）测试

-- es
-- =========
create table es_test (id string,name string) STORED AS ES WITH SHARD NUMBER 3 REPLICATION 1;
-- INSERT 测试(支持)
insert into default.es_test select * from  default.torc_test;
insert into default.es_test values (2,'manager');
-- UPDATE 测试(支持)
UPDATE TABLE default.es_test SET name="hdfs1" WHERE id=4;
-- DELETE 测试(支持)
DELETE FROM  default.es_test WHERE id=1;
-- TRUNCATE 测试(支持)
TRUNCATE TABLE default.es_test;
-- SELECT 测试(支持)
SELECT * FROM default.es_test;
-- 删除表(支持)
DROP TABLE IF EXISTS default.es_test;

hyperdrive 表测试

-- hyperdrive
-- ==========
create table default.hyper_test(id int,name string) STORED AS HYPERDRIVE;
-- INSERT 测试(支持)
insert into default.hyper_test select 8180,"manager" from default.es_test;
insert into default.hyper_test select * from default.es_test;
insert into default.hyper_test VALUES (10000,'quark2');
-- UPDATE 测试(支持)
UPDATE TABLE default.hyper_test SET name="hive" WHERE id=10000;
-- DELETE 测试(支持)
DELETE FROM default.hyper_test WHERE id=1;
-- TRUNCATE 测试(支持)
TRUNCATE TABLE default.hyper_test;
-- SELECT 测试(支持)
SELECT * FROM default.hyper_test;