[大数据]Hive-基本操作

DDL 数据定义

查询数据库

显示数据库

show databases

过滤显示查询的数据库

show databases like 'db_hive*'

显示数据库信息

desc database db_hive

显示数据库详细信息

desc databases extended db_hive

切换当前数据库

use db_hive

描述数据库的属性信息

alter database db_hive set dbproperties('createauth'='czs')

删除数据库

if exists避免要删除的空数据库不存在错误

drop database if exists db_hive

cascade强制删除数据不为空的数据库

drop database db_hive cascade

创建数据库

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选项来忽略这个异常

EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径LOCATION在删除表的时候内部表的元数据和数据会被一起删除而外部表只删除元数据不删除数据

COMMENT：为表和列添加注释

PARTITIONED BY：创建分区表

CLUSTERED BY：创建分桶表

SORTED BY：不常用，对桶中的一个或多个列另外排序

SerDe是Serialize/Deserilize的简称， hive使用SerDe进行行对象的序列与反序列化用户在建表的时候可以自定义SerDe或者使用自带的 SerDe。如果没有指定ROW FORMAT或者ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据

STORED AS指定存储文件类型：常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列 式存储格式文件）如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

LOCATION：指定表在HDFS上的存储位置

AS：后跟查询语句，根据查询结果创建表

LIKE： 允许用户复制现有的表结构，但是不复制数据

if not exists避免要创建的数据库已存在错误

create database db_hive  if not exists db_hive

指定数据库存放位置创建数据库

create database db_hive location '/db_hive.db'

内部表

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表Hive会或多或少地控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下

创建普通表

create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

根据查询结果创建表

create table if not exists student2 as select id, name from student;

根据已存在的表结构创建表

create table if not exists student3 like student;

外部表

因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这 份数据，不过描述表的元数据信息会被删除掉，外部表删除后，hdfs 中的数据还在，但是 metadata 中 dept 的元数据已被删除

创建外部表

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

管理表与外部表的互相转换

修改内部表为外部表

alter table student set tblproperties('EXTERNAL'='TRUE');

修改外部表为内部表

alter table student set tblproperties('EXTERNAL'='FALSE');

表操作

修改表

alter table student rename to stu

删除表

drop table dept;

增加列信息

alter table dept add columns(deptdesc string);

修改列信息

 alter table dept change column deptdesc desc string;

替换列信息

alter table dept replace columns(deptno string, dnamestring, loc string);

DML 数据操作

load-向表中装载数据

load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];

load data:表示加载数据

local:表示从本地加载数据到hive表；否则HDFS加载数据到 hive 表

inpath:表示加载数据的路径

overwrite:表示覆盖表中已有数据，否则表示追加

into table:表示加载到哪张表

student:表示具体的表

partition:表示上传到指定分区

加载本地文件到hive

load data local inpath '/hive/datas/student.txt' into table default.student;

加载hdfs数据到hive

load data inpath '/user/hive/student.txt' into table default.student;

insert-通过查询语句向表中插入数据

• insert into：以追加数据的方式插入到表或分区，原有数据不会删除
• insert overwrite：会覆盖表中已存在的数据

insert into table student values(1,'xyx'),(2,'czs');
insert overwrite table student select id, name from student where name='czs';

as select-查询语句中创建表并加载数据

create table if not exists student as select id, name from student;

import-导入数据到hive表

import table student from '/hive/export/student';

insert-导出数据

insert overwrite local directory '/hive/data/export/student' select * from student;

truncate-清除表中数据

truncate table student;

查询操作

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

常用函数

select count(*) cnt from emp;
select max(sal) max_sal from emp;
select min(sal) min_sal from emp;
select sum(sal) sum_sal from emp;
select avg(sal) avg_sal from emp;

Join语句

内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

左外连接

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

满外连接

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字 段没有符合条件的值的话，那么就使用 NULL 值替代。

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

常用内置函数

空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL则NVL 函数返回default_value的值否则返回value的值，如果两个参数都为 NULL 则返回 NULL。

select comm,nvl(comm, -1) from emp;

CASE WHEN THEN ELSE END

select
 dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;