526互联

Hive学习3（数据库操作DML、join、hive函数）

发布时间 2023-09-23 20:02:24作者: 201812

1.对数据库操作

数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database、table等。
DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。

1.1创建数据库

create database 库名字;

可以在webui查看在/user/hive/warehouse/目录下

 1.2切换到指定的数据库进行操作

到哪个数据库中，则所有操作默认都在这个数据库中进行。

use 库名;

1.3删除数据库

前提要求：该数据库中没有表，即该数据库为空。

drop database 库名;

 2.导入数据

2.1创建表导入数据

先创建表，注意session选择的地方，点击执行在itheima下面创建一张表

 在webui页面，可以进行暴力上传文件，注意：一定要进入到表里面上传文件

查询看是否成功

select * from 表名 limit 10;

 

 2.2分隔符

上面的例子中，分隔符是\t，如果不写默认是\001

3.show语法

show databases;

show tables；

show tables in itheima；-- 显示itheima库下面的表

desc formatted 表名字; -- 显示表的信息

 

4.处理中文乱码问题

要在mysql中进行配置

现在node1上输入命令：mysql -u root -p

黑马配置好的sql密码为hadoop

 切换到hive3

 执行下面的语句

alter table hive3.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table hive3.TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table hive3.PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table hive3.PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
alter table hive3.INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

执行完成后：ctrl+d结束

我们这个时候去查看还是乱码，原因是之前的表已经写死了，所以需要删除表再创建这时候就没有乱码了。

 删除后再创建表执行查询：

 5.SQL-DML

5.1Load加载

hive服务所在的本地

 

02-Hive SQL-DML-Load加载数据操作_哔哩哔哩_bilibili

 

5.2insert插入

1.一条条数据上传不推荐使用，慢。

2.通常是 insert+select 组合使用

如再原有的students表中挑出num和name，插入到新的students_insert表中

-- insert+select使用
create table students_insert(sno int,sname string);
insert into table students_insert select num,name from students;
-- 查看结果
select * from students_insert limit 10;

5.3select

执行顺序：from>where>group>having>order>select

5.3.1select去重，select查询数据库

-- select
-- 查询students表10 条数据
select * from students limit 10;
-- 查询具体的num、name字段
select name,dept  from students limit 10;


-- 去重
select distinct dept from students;
-- 注意这样子写是整体重复会去重，就是dept和name这两个字段都重复才会去掉
-- a AAA和a AAA会被去掉，而a AAA和a BBB不会被认为是重复
select distinct dept,name from students;


-- 查询当前的数据库名称
select current_database();

5.3.2select---where

-- select where
select * from students where sex = '女';
-- 可以指定长度:例查询name中长度大于3的信息
select * from students where length(name) > 3;
select * from students where age >=19 and age <=23;
select * from students where age in(18,19,20);

5.3.3select聚合

在where中不可以直接用聚合，可以先用having+聚合获得结果

-- 聚合函数，再where中不可以使用
--AVG、COUNT(column):不包括NULL、COUNT(*)、MAX、MIN、SUM
select count(num) as student_num from students;
-- 去重
select count(distinct num) as student_num from students;
-- SUM和COUNT
select sum(age) as total_age from students;

5.3.4GROUP BY

 通常和聚合函数一起使用

-- GROUP BY
-- 根据院系分组，统计院系为MA有多少个学生
select count(num) from students where dept = "MA" group by dept;
select dept, count(num) from students where dept = "MA" group by dept;

5.3.5Having

--having
-- 找出院系人数大于6人,列出院系和人数
select dept,count(num) as total_student from students group by dept having total_student > 6 ;

5.3.6order by

默认是升序

-- order by
--默认升序asc
select * from students order by age;
--规定降序
select * from students order by age desc,num asc ;

5.3.7limit

这里需要注意的是行偏移量

-- limit
-- 注意：行是从0开始,从第二行开始读取三行
select * from students;
select * from students limit 2,3;

 6.JOIN

13-Hive SQL Join关联查询_哔哩哔哩_bilibili

7.hive函数

hive内置函数：

-- hive内置函数
show functions; -- 查看函数
-- 查看函数使用方法
describe function extended 函数名字
官方文档：LanguageManual UDF - Apache Hive - Apache Software Foundation
------------String Functions 字符串函数------------
select length("itcast");
select reverse("itcast");

select concat("angela","baby");
--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));

--字符串截取函数：substr(str, pos[, len]) 或者  substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);
--分割字符串函数: split(str, regex)
--split针对字符串数据进行切割  返回是数组array  可以通过数组的下标取内部的元素 注意下标从0开始的
select split('apache hive', ' ');
select split('apache hive', ' ')[0];
select split('apache hive', ' ')[1];


----------- Date Functions 日期函数 -----------------
--获取当前日期: current_date
select current_date();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');

--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);


----Mathematical Functions 数学函数-------------
--取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);


-----Conditional Functions 条件函数------------------
--使用之前课程创建好的student表数据
select * from student limit 3;

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

--空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;