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      parametersdb_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 textfilelocation '/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_TABLEalter table student2 set tblproperties('EXTERNAL'='TRUE'); --将内部表修改为外部表hive (default)> desc formatted student2;  --查看表的类型Table Type:             EXTERNAL_TABLEalter 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语句有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！