一、新建表：
create table [表英文名]
(
[字段1] STRING COMMENT '[字段1]'
,[字段2] STRING COMMENT '[字段2]'
,[字段3] DECIMAL(18,7) COMMENT '[字段3]'
,[字段4] INT COMMENT '[字段4]'
,[字段5] DATE COMMENT '[字段5]'
,[字段6] SMALLINT COMMENT '[字段6]'
)
COMMENT '[表中文名]'
PARTITIONED BY
(
[分区字段] STRING COMMENT '[分区字段]'
)
STORED AS Orc
;
--举例如下：
--1、分区表的建表：
create table TEST_TABLE
(
Field1 STRING COMMENT '字段1'
,Field2 STRING COMMENT '字段2'
,Field3 DECIMAL(18,7) COMMENT '字段3'
,Field4 INT COMMENT '字段4'
,Field5 DATE COMMENT '字段5'
,Field6 SMALLINT COMMENT '字段6'
)
COMMENT '测试表'
PARTITIONED BY
(
PRT_DT STRING COMMENT '分区日期'
)
STORED AS Orc
;
--DESC TEST_TABLE;
--2、非分区表的建表：
create table TEST_TABLE_NO
(
Field1 STRING COMMENT '字段1'
,Field2 STRING COMMENT '字段2'
,Field3 DECIMAL(18,7) COMMENT '字段3'
,Field4 INT COMMENT '字段4'
,Field5 DATE COMMENT '字段5'
,Field6 SMALLINT COMMENT '字段6'
)
COMMENT '测试表NO'
STORED AS Orc
;
--DESC TEST_TABLE_NO;
二、删除表：
--Drop table [表名]
DROP TABLE TEST_TABLE;
DROP TABLE TEST_TABLE_NO;
三、插入数据：
--INSERT INTO [表名] PARTITION(字段1,字段2) VALUES (100,'51N');
--1、分区表插入数据
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-18')VALUES ('1','2',3,4,'2021-02-05',6);
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-18')VALUES ('11','22',33,44,'2021-02-05',66);
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-19')VALUES ('1','2',3,4,'2021-02-05',6);
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-19')VALUES ('11','22',33,44,'2021-02-05',66);
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-19')VALUES ('\N','2',3,4,'2021-02-05',6); --HIVE表中默认将NULL存为\N
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-19')VALUES ('','2',3,4,'2021-02-05',6);
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-19')VALUES ('1','2',3.33333333,4,'2021-02-05',6);
--2、非分区表插入数据
INSERT INTO TABLE TEST_TABLE_NO VALUES ('1','2',3,4,'2021-02-05',6);
INSERT INTO TABLE TEST_TABLE_NO VALUES ('11','22',33,44,'2021-02-05',66);
INSERT INTO TABLE TEST_TABLE_NO VALUES ('1','2',3,4,'2021-02-05',6);
INSERT INTO TABLE TEST_TABLE_NO VALUES ('11','22',33,44,'2021-02-05',66);
***能插入部分（指定）字段数据吗？***
--以下是两种方式插入部分（指定）字段数据，其余字段可设置为默认值
--1、静态分区方式，从另一个表插入数据
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT = '2021-02-20') SELECT * FROM TEST_TABLE_NO;
--2、动态分区方式，从另一个表插入数据
set hive.stats.autogather=true;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT INTO TABLE TEST_TABLE PARTITION (PRT_DT)
SELECT
Field1
,Field2
,Field3
,Field4
,Field5
,Field6
,'2020-02-01'
FROM TEST_TABLE_NO;
--SELECT * FROM TEST_TABLE;
--SELECT * FROM TEST_TABLE_NO;
四、删除数据：
--DELETE FROM [表名] WHERE [字段名]>100
--以上执行语句报错，提示HiveSQLException:SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.
***hive无update delete操作，有ALTER TABLE DROD和INSERT OVERWRITE TABLE操作***
--1.1 分区表删除具体分区
ALTER TABLE TEST_TABLE DROP IF EXISTS PARTITION (PRT_DT = '2020-02-01');
--1.2 分区表删除具体分区的部分数据
INSERT OVERWRITE TABLE TEST_TABLE PARTITION(PRT_DT='2021-02-19')
SELECT
Field1
,Field2
,Field3
,Field4
,Field5
,Field6
FROM TEST_TABLE
WHERE Field1 is NULL; --#WHERE后的条件是需要保留的数据的查询结果
--2、非分区表删除数据
INSERT OVERWRITE TABLE TEST_TABLE_NO SELECT * FROM TEST_TABLE_NO WHERE Field3 > 3; --#WHERE后的条件是需要保留的数据的查询结果,即删除Field3小于及等于3的数据
五、更新数据：
--UPDATE [表名] SET [字段1] = '200',[字段2] = '5T' WHERE [字段3] = 33;
***hive无 update delete操作，有ALTER TABLE和INSERT OVERWRITE TABLE操作***
虽然hive可以通过配置hive-site.xml，来增加Update、Delete支持，但是效率不太高，以下是他人的总结：
•1、Hive可以通过修改参数达到修改和删除数据的效果，但是速度远远没有传统关系型数据库快。
•2、通过ORC的每个task只输出单个文件和自带索引的特性，以及数据的分桶操作，可以将要修改的数据锁定在一个很小的文件块，因此可以做到相对便捷的文件修改操作。
因此数据的分桶操作非常重要，通常一些表单信息都会根据具体的表单id进行删除与修改，因此推荐使用表单ID作为分桶字段。
•3、频繁的update和delete操作已经违背了hive的初衷。不到万不得已的情况，还是使用增量添加的方式最好。
六、修改字段：
--ALTER TABLE [表名] CHANGE [字段名] STRING
ALTER TABLE TEST_TABLE CHANGE Field1 Field1111 STRING;
ALTER TABLE TEST_TABLE CHANGE Field1111 Field1111 decimal(18,2);
DESC TEST_TABLE; --Field1111的字段类型可以从STRING改为decimal(18,2)
SELECT * FROM TEST_TABLE; --但是解析数据库结果集会出现异常：ORC不支持STRING转换为decimal(18,2)
ALTER TABLE TEST_TABLE CHANGE Field1111 Field1 STRING;
ALTER TABLE TEST_TABLE CHANGE Field3 Field3333 DECIMAL(18,2);
ALTER TABLE TEST_TABLE CHANGE Field3333 Field3 DECIMAL(18,7);
七、新增字段：
--ALTER TABLE [表名] ADD [字段名] STRING
ALTER TABLE TEST_TABLE ADD COLUMNS (field7 INT COMMENT '字段7');
ALTER TABLE TEST_TABLE ADD COLUMNS (
field8 INT COMMENT '字段8'
,field9 INT COMMENT '字段9'
,field10 STRING COMMENT '字段10'
);
八、删除字段：
--ALTER TABLE [表名] REPLACE COLUMNS ([字段名] [字段类型])
ALTER TABLE TEST_TABLE_NO REPLACE COLUMNS(Field1 STRING, Field2 STRING); --删除字段(使用新schema替换原有的)
ALTER TABLE TEST_TABLE_NO REPLACE COLUMNS(Field1 STRING, Field2 STRING, Field3 DECIMAL(18,7), Field4 INT, Field5 DATE, Field6 SMALLINT); --原有Field3~Field6数据又会补充回来
ALTER TABLE TEST_TABLE REPLACE COLUMNS(Field1 STRING, Field2 STRING, Field3 DECIMAL(18,7), Field4 INT, Field5 DATE, Field6 SMALLINT);
九、重命名表：
--ALTER TABLE [原表名] RENAME TO [新表名]
ALTER TABLE TEST_TABLE RENAME TO TEST_TABLE2;
拓展：
Hive中支持的分区类型有两种：
•静态分区（static partition）
•动态分区（dynamic partition）
两者的区别：
主要在于静态分区需要手动指定，而动态分区是基于查询参数的位置去推断分区的名称，从而建立分区。
总的来说就是，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能确定。
