hive表基本语法

hive表基本语法

青少年是一个美好而又是一去不可再得的时期 是将来一切光明和幸福的开端

1.ROW FORMAT用法

1）创建一个表

CREATE TABLE IF NOT EXISTS filetest.total_score

(

student_id string COMMENT "学生编号",

score int COMMENT "总分"

)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"

STORED AS TEXTFILE;

2）上传一下数据

load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score;

3）查看内容

select * from filetest.total_score;

ROW FORMAT用于指定Hive表中数据的存储格式 通常在创建表时使用ROW FORMAT具体指定了数据在Hive表中的行格式 包括字段之间的分隔符 行之间的分隔符等

常见的字段分隔符有逗号 制表符（制表符：Tab键 （\t））等 行分隔符一般为换行符

注意:如果数据和分隔符不匹配 则原始数据中的所有数据会变成新表中的一列数据

2.LOCATION用法

在Hive中 LOCATION关键字用于指定Hive表的存储位置 通常情况下 当你创建一个Hive表时 表的数据会存储在HDFS（Hadoop分布式文件系统）上的默认位置 但是你也可以使用LOCATION关键字来指定表的具体存储路径 使得表的数据可以存储在你指定的任意路径下 这为管理和组织数据提供了更大的灵活性

1）我们先创建一个路径

dfs -ls /;

dfs -mkdir /testDatabase/total_score;

2）创建表

CREATE TABLE IF NOT EXISTS filetest.total_score3

(

student_id string COMMENT "学生编号",

score int COMMENT "总分"

)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"

STORED AS TEXTFILE

LOCATION "/testDatabase/total_score";

3）上传数据

load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score3;

4）查看表信息

查看表的字段信息

desc total_score3;

查看表的详细信息

desc formatted total_score3;

5）删除表

drop table total_score3;

删除表的时候 HDFS上的文件也会删除

那么 如果有重要信息 我们不想轻易删除怎么办？

3.EXTERNAL用法 （外部表）

1）我们先创建一个路径

dfs -ls /;

dfs -mkdir /testDatabase/total_score;

2）创建表

CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score3

(

student_id string COMMENT "学生编号",

score int COMMENT "总分"

)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"

STORED AS TEXTFILE

LOCATION "/testDatabase/total_score";

3）上传数据

load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score3;

4）删除表

drop table total_score3;

注:

外部表与普通内部表的区别在于:外部表删除表时 只能删除hive中的元数据 我们可以看到表被删除了 但是数据依然存在 不会删除对应的数据 并且和表数据的存储位置无关

4.STORED AS 用法：设置数据存储格式

1）使用方法一：

CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score4

(

student_id string COMMENT "学生编号",

score int COMMENT "总分"

)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"

STORED AS ORC;

--load data local inpath "/usr/local/soft/hive-3.1.2/data/total_score.txt" into table filetest.total_score4;--

注意:如果数据存储格式为压缩格式 那么就不能直接将文本数据加载至表中

insert into table filetest.total_score4 select * from filetest.total_score;

2）使用方法二：

通过查询语句的结果 去创建一个新表

create table if not exists filetest.total_score5 STORED AS ORC AS select student_id,score from filetest.total_score;

5.TBLPROPERTIES 用法

1）创建表

CREATE EXTERNAL TABLE IF NOT EXISTS filetest.total_score6

(

student_id string COMMENT "学生编号",

score int COMMENT "总分"

)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"

STORED AS TEXTFILE

TBLPROPERTIES("author"="jhw","application"="学生表 保存学生的总分 如有错请联系：jhw");

2）查看表信息

查看表的字段信息

desc total_score6;

查看表的详细信息

desc formatted filetest.total_score6;

6.PARTITIONED BY 分区

--将学生信息表中不同性别的学生进行分区保存

1）创建表

CREATE TABLE IF NOT EXISTS filetest.partition_student

(

id STRING COMMENT "学生ID",

name STRING COMMENT "学生姓名",

age int COMMENT "年龄”,

clazz STRING COMMENT “班级"

) PARTITIONED BY (gender STRING COMMENT "性别分区")

STORED AS TEXTFILE;

2）查看表结构

3）插入数据

开启动态分区支持 通过动态分区进行数据插入

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nostrict;

set hive.exec.max.dynamic.partitions.pernode=1000;

insert into table filetest.partition_student PARTITION(gender="男") select id,name,age,clazz FROM filetest.students where gender="男" limit 10;

注意：动态分区默认把最后一个字段设置为分区字段

静态分区：

insert into table filetest.partition_student PARTITION(gender="男") select "1500100002","吕金鹏",24,"文科六班";​​

select * from students limit 10;

7.扩展：向分区中插入数据时出错

我们打开MySQL 修改一下编码就可以啦

1. 打开数据库

mysql -u root -p123456

2.use hive

3.输入以下命令

修改hive database 编码​alter database hive default character set utf8;​修改 table 编码​alter table PARTITIONS default character set utf8;​alter table PARTITION_KEY_VALS default character set utf8;​alter table SDS default character set utf8;​修改 字段 编码​alter table PARTITIONS modify column PART_name varchar(190) character set utf8;​alter table PARTITION_KEY_VALS modify column PART_KEY_VAL varchar(256) character set utf8;​alter table SDS modify column LOCATION varchar(4000) character set utf8;