hive常用sql语句有哪些

这篇文章主要讲解了“hive常用sql语句有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“hive常用sql语句有哪些”吧！

hive常用sql

1.数据类型

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

2.类型转换

2.1 隐式类型转换规则如下

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。

（4）BOOLEAN类型不可以转换为任何其它的类型。

2.2 可以使用CAST操作显示进行数据类型转换

例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。

0: jdbc:hive2://hadoop102:10000> select '1'+2, cast('1'as int) + 2;
+------+------+--+
| _c0  | _c1  |
+------+------+--+
| 3.0  | 3    |
+------+------+--+

3.DDL数据定义

3.1 创建数据库

hive (default)> create database db_hive;
hive (default)> create database if not exists db_hive; --避免创建已存在的报错
hive (default)> create database db_hive2 location '/db_hive2.db';--创建并指定在hdfs的存储位置

3.2 查询数据库

hive> show databases;
hive> show databases like 'db_hive*'; --模糊查询
hive> desc database db_hive; --显示数据库信息
hive> desc database extended db_hive; --显示数据库详细信息

3.3 切换数据库

hive (default)> use db_hive;

3.4 修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');
--查看修改结果
hive> desc database extended db_hive;
db_name comment location        owner_name      owner_type      parameters
db_hive         hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db    atguigu USER    {createtime=20170830}

3.5 删除数据库

hive>drop database db_hive2;
hive> drop database if exists db_hive2; --避免删除的数据库不存在
hive> drop database db_hive cascade; --避免数据库不为空报错

3.6 创建表

--基本语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
--创建内部表
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';
--根据查询结果创建表
create table if not exists student3 as select id, name from student;
--根据已经存在的表创建表
create table if not exists student4 like student;
--查询表的类型(外部表还是内部表)
hive (default)> desc formatted student2;
--创建外部表(上传数据)
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;

hive (default)> create external table stu_external(
id int, 
name string) 
row format delimited fields terminated by '\t' 
location '/student';
 drop table stu_external; --删除外部表,只删除元数据不会删除数据。

3.7 管理表(内部表)与外部表的交换

hive (default)> desc formatted student2;  --查看表的类型
Table Type:             MANAGED_TABLE

alter table student2 set tblproperties('EXTERNAL'='TRUE'); --将内部表修改为外部表

hive (default)> desc formatted student2;  --查看表的类型
Table Type:             EXTERNAL_TABLE

alter table student2 set tblproperties('EXTERNAL'='FALSE'); --将外部表改成内部表

hive (default)> desc formatted student2;--查看表的类型
Table Type:             MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

3.8 分区表

--引入分区表
/user/hive/warehouse/log_partition/20170702/20170702.log
/user/hive/warehouse/log_partition/20170703/20170703.log
/user/hive/warehouse/log_partition/20170704/20170704.log
--创建分区表 注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
hive (default)> create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
--加载数据到分区表中 注意：分区表加载数据时，必须指定分区
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707’);
--查询分区表数据
hive (default)> select * from dept_partition where month='201709';
hive (default)> select * from dept_partition where month='201709'
              union
              select * from dept_partition where month='201708'
              union
              select * from dept_partition where month='201707';

--增加分区
hive (default)> alter table dept_partition add partition(month='201706') ;
--同时创建多个分区
hive (default)> alter table dept_partition add partition(month='201705') partition(month='201704');
--删除分区
hive (default)> alter table dept_partition drop partition (month='201704');
hive (default)> alter table dept_partition drop partition (month='201705'), partition (month='201706');
--查看分区表有多少分区
hive> show partitions dept_partition;
--查看分区表结构
hive> desc formatted dept_partition;

3.9 两级分区表

--创建二级分区表
hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';
--正常加载数据
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table
 default.dept_partition2 partition(month='201709', day='13');
--查询分区数据
hive (default)> select * from dept_partition2 where month='201709' and day='13';

3.10 分区关联修复

方式一:上传数据后修复

--上传数据
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
--查询不到刚上传的数据
hive (default)> select * from dept_partition2 where month='201709' and day='12';
--执行修复命令
hive> msck repair table dept_partition2;
--再次查询数据
hive (default)> select * from dept_partition2 where month='201709' and day='12';

方式二:上传数据后添加分区

--上传数据
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=11;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;
--执行添加分区
hive (default)> alter table dept_partition2 add partition(month='201709',day='11');
--查询数据
hive (default)> select * from dept_partition2 where month='201709' and day='11';

方式三:创建文件夹后load数据到分区

--创建目录
hive (default)> dfs -mkdir -p  /user/hive/warehouse/dept_partition2/month=201709/day=10;
--上传数据
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
-- 查询数据
hive (default)> select * from dept_partition2 where month='201709' and day='10';

3.11 修改表

hive (default)> alter table dept_partition2 rename to dept_partition3; --重命名
hive (default)> alter table dept_partition add columns(deptdesc string); --添加列
hive (default)> alter table dept_partition change column deptdesc desc int; --更新列
hive (default)> alter table dept_partition replace columns(deptno string, dname
 string, loc string); --替换列

4.DML操作

4.1 数据导入

load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

insert into table  student partition(month='201709') values(1,'wangwu'),(2,’zhaoliu’);

insert overwrite table student partition(month='201708') select id, name from student where month='201709';

insert into：以追加数据的方式插入到表或分区，原有数据不会删除

insert overwrite：会覆盖表或分区中已存在的数据

注意：insert不支持插入部分字段

hive (default)> from student
              insert overwrite table student partition(month='201707')
              select id, name where month='201709'
              insert overwrite table student partition(month='201706')
              select id, name where month='201709';

hive (default)> import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

4.2 数据导出

--将查询结果导出到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;
--将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/datas/export/student1'
           ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;
--将查询结果导出到hdfs上
insert overwrite directory '/user/atguigu/student2'
             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
             select * from student;

感谢各位的阅读，以上就是“hive常用sql语句有哪些”的内容了，经过本文的学习后，相信大家对hive常用sql语句有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！