这篇文章主要讲解了“hive常用sql语句有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“hive常用sql语句有哪些”吧！
hive常用sql
1.数据类型
| Hive数据类型 | Java数据类型 | 长度 | 例子 |
|---|---|---|---|
| TINYINT | byte | 1byte有符号整数 | 20 |
| SMALINT | short | 2byte有符号整数 | 20 |
| INT | int | 4byte有符号整数 | 20 |
| BIGINT | long | 8byte有符号整数 | 20 |
| BOOLEAN | boolean | 布尔类型，true或者false | TRUE FALSE |
| FLOAT | float | 单精度浮点数 | 3.14159 |
| DOUBLE | double | 双精度浮点数 | 3.14159 |
| STRING | string | 字符系列。可以指定字符集。可以使用单引号或者双引号。 | ‘now is the time’ “for all good men” |
| TIMESTAMP | 时间类型 | ||
| BINARY | 字节数组 |
| 数据类型 | 描述 | 语法示例 |
|---|---|---|
| STRUCT | 和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 | struct()例如struct<street:string, city:string> |
| MAP | MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素 | map()例如map<string, int> |
| ARRAY | 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。 | Array()例如array<string> |
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语句有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是本站，小编将为大家推送更多相关知识点的文章，欢迎关注！
