数据库操作
创建数据库
create database 库名;
create database if not exitst 库名; -创建之前先判定数据库是否已经存在,如果不存在才创建
create database 库名 location 'hdfs路径'; -创建数据库并指定它在hdfs上的存储位置
使用数据库
use database 库名;
检察数据库具体信息
desc database 库名;
删除数据库
drop database 库名; -只能删除空数据库,如果数据库内里有数据表,就会报错
drop database 库名 cascade; -逼迫删除数据库,包含数据库内里的表一起删除
数据表操作
创建数据库表
create [external] table [if not exitst] 表名(
列名 数据类型 [comment colcommrnt],...)
EXTERNAL,创建外部表 PARTITIONED BY, 分区表 CLUSTERED BY,分桶表 STORED AS,存储格式 LOCATION,存储位置
底子建表语法
只管建表语法比力复杂,现在我们暂时未打仗到分区、分桶等概念。 以是,创建一个简单的数据库表可以有如下SQL:
create table 表名(
列名 数据类型,
列名2 数据类型);
--创建一样简单的表
- create table test(
- id int,
- name string,
- gender string
- );
复制代码 数据类型
Hive中支持的数据类型还是比力多的 其中使用比力多的类型有:
简单类型
- 布尔类型: boolean(只有true和flase两个值)
- 数字类型: float(单精度小数)int(整数)double(双精度小数)
- 字符串类型: char(固定长度字符串) varchar(变长字符串) string(没有限定的字符串)
- 日期时间类型: date(年月日时分秒毫秒)在工作中不会有人用date类型,hive都是用string存储时间
复杂类型
- 数组类型: array<简单类型> 雷同于python内里的列表。
- 如果要存储一个全部都是整数的数组 array<int>
- 映射类型: map<基本类型, 基本类型> 是一个键值对格式,雷同与python内里字典
- 要存储的是科目标分数,例如数学多少分 语文多少分 map<string, int>
删除表
drop table 表名;
- #删除刚刚创建的test表
- drop table test;
复制代码 表分类
hive中可以创建的表的类型有:
内部表和外部表
create table 创建的表格都是内部表,通过 create external table 创建的表格,就是外部表。
内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定(默认:/user/hive/warehouse),用drop table语句删除内部表会直接删除元数据(metadata)及存储数据,因此内部表不得当和其他工具共享数据。
外部表是指表数据可以在任何位置,通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的,而是可以随意暂时链接到外部数据上的。 以是,在删除外部表的时候, 仅仅是删除元数据(表的信息),不会删除数据自己。
内部表和外部表的区别
- 内部表可以用truncate table清空,也可以用drop table删除
- 外部表是不能通过truncate table执行清空操作的;
- 外部表通过drop table删除,只会删除mysql中表格的元数据,但是无法删除hdfs内里表格的文件夹以及数据文件的;
因此在数据堆栈中,大数据的表格或者业务数据的表格,一样平常都会使用外部表进行创建和存储。
创建内部表
创建一个t1表
- create table t1(
- id int,
- name string,
- mobile char(11),
- hobby array<string>,
- score map<string,double>
- );
复制代码 往表格中插入数据:
1.只插入简单类型的字段数据
inset into 表(列名) values(值);
- insert into t1(id,name,mobile) values(101,'curry','18878984708');
- insert into t1(id,name,mobile) values(102,'kobe','12343547896');
复制代码 hive数据库所有的sql操作都是高耽误的,如果操作的数据量小于128M,那么我们可以去启动本地设置的开关,加快数据的操作
本地模式开关:set hive.exec.mode.local.auto=true;
2.插入的数据包括了复杂类型
insert into 表(列名) select 查询;
- insert into t1(id,name,mobile,hobby,score)
- select 103,'james','15567589087',array('sing','rap'),
- map('math',cast(98.5 as double),'chinese',cast(99 as double),'english',cast(92.5 as double));
复制代码 hive数据库中,每一次的 insert 操作,在表格的文件夹内里,都会天生一个新的当前的数据文件。
表格的分隔符
- 表格字段之间的默认分隔符是 ^A,通过键盘的 ctrl + V + A 输出的,在数据库内里的编码格式是 \001
- 数组和映射类型,元素之间的分隔符是 ^B,通过键盘的 ctrl + V + B 输出的,在数据库内里的编码格式是 \002
- 映射类型内里,关键字和值之间,分隔符是 ^C,通过键盘的 ctrl + V + C 输出的,在数据库内里的编码格式是 \003
在创建表格的同时,强调分隔符的类型:
- create table t2(
- id int,
- name string,
- mobile char(11),
- hobby array<string>,
- score map<string,int>
- ) row format delimited fields terminated by ',' --字段分隔符
- collection items terminated by '/' --元素分隔符
- map keys terminated by '-'; --键值对分隔符
复制代码 插入数据
- insert into t2
- select 101,'aa','18899997777',array('唱歌','跳舞'),map('语文',88,'数学',91);
复制代码 将数据文件导入到hive表格中
在hive中比力少用 insert into 添加数据,我们一样平常都是用文件的方式来导入数据。
- create table t3(
- id int,
- name string,
- age int
- )row format delimited fields terminated by ',';
复制代码 1.直接通过hadoop fs -put命令,从linux上传数据文件到hdfs对应的表格文件夹中:
- hadoop fs -put /usr/t3.txt /user/hive/warehouse/myhive.db/t3
复制代码 2.通过sql语句,进行数据的导入,在hive数据库内里运行的:
load data local inpath 'linux文件的位置和名字' into table 表名;
- load data local inpath '/usr/t3.txt' into table t3;
复制代码 3.将数据文件同一的上传到hdfs的其他的文件夹中,例如 /datas,然后再从hdfs的 /datas 内里加载到表格中
load data inpath 'hdfs文件的位置和名字' into table 表名;
- hadoop fs -put /usr/t3.txt /datas/ --先把文件上传到hdfs
复制代码- load data inpath '/datas/t3.txt' into table t3;
复制代码 注:t3.txt文件的分隔符和t3表格的分隔符一定要同等,如果不同等,读取到的数据都是NULL
创建外部表
- create external table t4_ex(
- id int,
- name string,
- age int
- );
- insert into t4_ex values(101,'aa',18);
复制代码
内部表和外部表的转换
将内部表转换成外部表:
alter table 内表 set tblproperties ('EXTERNAL'='TRUE');
- alter table t3 set tblproperties('EXTERNAL'='TRUE');
复制代码 将外部表转换成内部表:
alter table 外表 set tblproperties ('EXTERNAL'='FALSE');
- alter table t3 set tblproperties ('EXTERNAL'='FALSE');
复制代码 创建表格的时候,可以指定表格的文件夹在哪里
内部表和外部表都可以指定表格的文件夹的位置:location
- create table t5(
- id int,
- name string,
- age int
- )location '/user/hive/warehouse/bigdata.db/t4_ex';
复制代码 也可以用location让多个表格来共享同一个文件夹的数据:
- create table t6(
- id int,
- name string,
- age int
- )location '/user/hive/warehouse/bigdata.db/t4_ex';
复制代码 这种建表的方式是不会在hdfs上天生自己的表格文件夹的。
创建暂时表
暂时表的关键字:temporary table
- create temporary table t6_tmp(
- id int,
- name string,
- age int
- );
复制代码
- 暂时表在当前的数据库中,不会在hdfs中创建表格的文件夹。
- 插入到暂时表的数据可以被正常的使用。
- 如果退出登录重新进入hive数据库,那么暂时表被删除掉了。
- 暂时表的数据被存放在内存中的,如果退出内存就被清空了,暂时表也被删除了。
创建分区表
分区表的目标:是为了加快表格查询和筛选数据的服从,是在where的阶段起到加快作用的
创建一个分区表:
create table 表名(
列名 数据类型
) partitioned by (表格中没有的新字段 数据类型);
- create table stu_part(
- stuid int,
- stuname string,
- age int
- ) partitioned by (class string)
- row format delimited fields terminated by ',';
复制代码 父子分区的结构:在hive内里语法没有限定分区必须是几层的结构,但项目中一样平常最多就是两层。
create table 表名(
列名 数据类型
) partitioned by (表格中没有的新字段1 数据类型, 新字段2 数据类型);
将不同的数据文件,导入到表格不同的分区中:
load data local inpath 'linux文件的位置和名字' into table 表名 partition (分区字段=分区值);
- load data local inpath '/root/s2.dat' into table stu_part partition (class='002');
复制代码 检察表格有哪些分区:show partitions
- show partitions stu_part;
复制代码 删除分区:drop partition
- alter table stu_part drop partition (class='002');
复制代码 直接创建一个空分区:add partition
- alter table stu_part add partition (class='003');
复制代码 a. 静态分区的导入方式
通过上面的load data的方法进行文件数据的导入,需要自己指定一个分区来导入文件的内容为静态分区导入。
现在有一个现成的表格,要将表格的内容导入到分区中
- -- 创建一个普通的表格emp
- create table emp(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) row format delimited fields terminated by ',';
- -- 导入表格数据
- load data local inpath '/root/emp.txt' into table emp;
复制代码 接着创建一个emp的分区表,按照部门去创建分区:
- -- 创建一个部门分区的emp表
- create table emp_dept_part(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float
- ) partitioned by (deptno int)
- row format delimited fields terminated by ',';
复制代码 最后读取emp表的数据,按照部门,导入到emp_dept_part中不同的分区中:
- insert overwrite table 表名; 覆盖写入数据
- insert into table 表名; 追加写入数据
- insert overwrite table emp_dept_part partition (deptno=10)
- select empno,ename,job,mgr,hiredate,sal,comm
- from emp
- where deptno=10;
- insert overwrite table emp_dept_part partition (deptno=20)
- select empno,ename,job,mgr,hiredate,sal,comm
- from emp
- where deptno=20;
复制代码 b. 动态分区的导入方式
只能通过 insert overwrite / into table 来导入数据。不能通过load data导入数据
动态导入是根据select内里字段的内容,来主动的判定数据应该放在哪个分区中。
- --写入数据之前,动态分区还需要两个开关
- -- 动态分区的开关
- set hive.exec.dynamic.partition=true;
- -- 打开动态分区中的非严格模式
- set hive.exec.dynamic.partition.mode=nonstrict;
- -- 导入数据
- insert overwrite table emp_dept_part partition (deptno)
- select empno,ename,job,mgr,hiredate,sal,comm,
- deptno
- from emp;
复制代码 再举一个例子:
- -- 创建一个emp的表格,用工资等级来分区
- create table emp_sal_part(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) partitioned by (grade string)
- row format delimited fields terminated by ',';
- -- 导入emp的数据
- insert overwrite table emp_sal_part partition (grade)
- select emp.*,
- case when sal<2000 then 'C'
- when sal<3000 then 'B'
- else 'A'
- end grade
- from emp;
复制代码 动态分区和静态分区的区别
- 动态分区只有insert导入数据的方式,静态可以通过insert或者load data来导入数据
- 动态分区在导入的时候不需要指定分区的值,通过select语句的字段来主动分区;静态分区需要自己指定分区值来导入数据
- 动态分区在导入的时候需要打开动态开关以及非严酷模式的开关
- 在分区确定和数据量一样的情况下,静态分区导入的速度比动态更快一点,但是动态导入更加方便
创建分桶表
分区是当表格很大的时候,为了加快where的服从,并且进行更加精细化的数据的管理,例如每天分区,或者每个地域分区,每个种别分区等。
分桶表是在数据分组统计的时候(group by),或者是在进行表格团结查询的时候,进行查询加快使用的表格结构。
分桶的操作是根据哈希算法,将分桶字段转换成数字除以分桶的数目根据余数来进行分桶划分的。
create table 表名(
列名 数据类型
)clustered by (表格已经存在的字段) into 分桶数目 buckets;
- --创建一个分桶表
- create table emp_clu(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) clustered by (empno) into 4 buckets
- row format delimited fields terminated by ',';
复制代码
- 通过数据文件来导入数据到分桶表格中(分桶表的数据导入不能使用本地模式的)
- load data local inpath '/root/emp.txt' into table emp_clu;
复制代码
- insert overwrite table emp_clu select * from emp;
复制代码 分区表和分桶表区别
- 分区是用的新字段,分桶是已有字段
- 分区是where加快,分桶是group by 和Join加快
- 分区是划分不同的文件夹存储的,分桶是划分不同的文件来存储的
- 分区是自己指定的规则,分桶是使用哈希算法
为什么分桶可以加快group by的聚合盘算的速度:
如果有一个表格,只有一个文件,文件是128M,盘算的时候将这个文件当成一个整体,使用一个map和一个reduce进行数据统计的;
分桶就是将这个128M的文件拆分成多个文件来存储,例如拆分成四个桶文件,那么盘算的时候就会有4个reduce参与数据的盘算和整合,那么数据的盘算速度就会变快。
为什么分桶可以加快join的表连接的速度:
用于加快join的分桶,两个表格的分桶的数目,必须是相同的数目或者是成倍数关系的数目。
通过哈希的余数关系,镌汰join中笛卡尔积数目标效果。
一个表格的分桶数目是怎么决定的:
表格的总数据量大小/(blocksize*2)
如果有个表总数目是4个GB,那么 4096/256=16 桶
创建外部的分区表
- create external table user_ext_part(
- id int,
- name string,
- age int
- ) partitioned by (y int)
- row format delimited fields terminated by ','
- location '/datas';
复制代码 datas文件夹的结构如下:
直接创建表格,指定了对应的目录,也读取不到数据,由于表格是个分区表,只有表格的结构,没有分区的结构。以是我们需要去新建分区,让分区的目录和分区的值一一对应。
- alter table user_ext_part add partition (y=2020) location '/datas/20240701';
- alter table user_ext_part add partition (y=2021) location '/datas/20240702';
- alter table user_ext_part add partition (y=2022) location '/datas/20240703';
复制代码 表格的存储方式以及对应的压缩格式
检察表格建表的基本情况:show create table 表名;
hive表格默认存储格式是textfile,存储的内容和格式其实就是一个有行和列构成的文本文档,textfile以行为单位存储的表格(行存表)
textfile比力得当中小型表格(几百万以上的数据,就是大型的表格)。
textfile就是可以直接通过load data的方式导入数据。
- create table 表名(
- 列名 数据类型
- );
- -- 也可以强调表格的存储类型
- create table 表名(
- 列名 数据类型
- )stored as textfile;
复制代码 对textfile表格的格式进行数据的压缩操作:
set hive.exec.compress.output=true;
set mapred.output.compress=true;
默认的压缩格式:deflate
除了默认的压缩格式,也可以支持 gzip 或者 bzip2 两种压缩
parquet也叫做拼接文件。
在重复数据特别多的表格中,parquet可以节省很多的存储空间。
不能使用load data直接导入文本的数据。
- create table emp_parquet(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- )stored as parquet;
- insert overwrite table emp_parquet select * from emp;
复制代码 parquet格式的压缩类型叫做SNAPPY:
- create table emp_parquet_ys(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) stored as parquet
- tblproperties ('parquet.compression'='SNAPPY');
- insert overwrite table emp_parquet_ys select * from emp;
复制代码
sequencefile叫做序列文件格式,占用的空间比textfile要稍大,查询速度比textfile要稍快。数据在数据库的内部是使用键值对的方式来存储的。
sequencefile不能直接通过load data导入数据。
- create table emp_seq(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) stored as sequencefile;
- insert overwrite table emp_seq select * from emp;
复制代码 sequencefile的压缩格式叫做BLOCK。默认的存储叫做RECORD。
set mapred.output.compression.type=BLOCK;
- create table emp_seq_ys(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) stored as sequencefile;
- insert overwrite table emp_seq_ys
- select * from emp;
复制代码
中小型表格使用textfile,大型的表格就使用orc。
orc是一种数据列存的格式,查询的速度是最快的,表格自带压缩功能的,压缩格式叫做zlib,每一行数据单独压缩
- create table emp_orc(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) stored as orc;
- insert overwrite table emp_orc
- select * from emp;
复制代码 表格默认是不支持数据的更新update和删除delete的。
1.一个普通的表格,现在需要修改和删除数据:用insert overwrite table来覆盖
- -- 修改SMITH工资为1000
- insert overwrite table emp
- select empno,ename,job,mgr,hiredate,1000,comm,deptno from emp where ename='SMITH'
- union all
- select * from emp where ename!='SMITH';
- -- 删除SMITH的数据
- insert overwrite table emp
- select * from emp where ename!='SMITH';
复制代码 2.如果一个表格一定有修改或者删除的场景,那么就创建成orc类型的表格
- -- 先打开数据库支持事务的开关
- set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
- set hive.support.concurrency=true;
- create table emp_orc_2(
- empno int,
- ename string,
- job string,
- mgr int,
- hiredate string,
- sal float,
- comm float,
- deptno int
- ) stored as orc
- tblproperties('transactional'='true');
- insert overwrite table emp_orc_2
- select * from emp;
- update emp_orc_2 set sal=5000 where ename='ALLEN';
- delete from emp_orc_2 where ename='WARD';
复制代码 DDL语句
1.创建表格
- create [external] [terporary] table 表名(
- 列名 数据类型
- ) partition by (分区字段 数据类型)
- clustered by (分桶字段) into 分桶数量 buckets by '字段'
- row format delimited fields terminated by '字段分隔符'
- collection items terminated by '数组元素分隔符'
- map keys terminated by '字典键值对分隔符'
- location '创建文件夹的位置'
- stored as 存储类型
- tblproperties ('属性名'='值');
复制代码 2.复制表格
- --将查询的结果复制成一个表格
- create table 新表 as select 查询;
- --直接复制一个表格的结构为一个新的表(只有表结构没有数据)
- create table 新表 like 要复制的表;
复制代码 3.修改表格的结构
- --修改字段的结构
- 1.新增字段
- alter table 表名 add columns(列名 数据类型);
- 给t10表新增gender字段和addr字段
- alter table t10 add columns(gender char(3), addr string);
- 2.修改字段的名字和数据类型
- alter table 表名 change 字段 新字段 新数据类型;
- alter table t10 change mobile mobile char(11);
- alter table t10 change id userid string;
- 3.不能删除表格的字段
- 4.重命名表格
- alter table 表名 rename to 新表名;
- alter table t10 rename to test10;
- --修改表格的分区信息
- 1.新增分区
- alter table 分区表 add partition(分区字段=分区值) [location 'hdfs路径'];
- 2.删除分区
- alter table 分区表 drop partition(分区字段=分区值);
- 3.清空分区
- alter table 分区表 truncate partition(分区字段=分区值);
复制代码 4.清空整个表格
truncate table 表名;
5.删除表格
drop table 表名;
DML
新增、修改和删除数据。
其中update和delete是不能直接使用的,只能在打开了事件属性的orc表格中可以用。
1.在简单类型中插入一行数据
- insert into 表名(列名) values(值);
2.在复杂类型中插入一行数据
- insert into 表名(列名) select array(),map() ...;
3.将一个表格的数据覆盖写入到另一个表格中
- insert overwrite table 表名 [partition (分区字段=分区值)] select 查询;
4.将一个表格的数据追加写入到另一个表格中
- insert into table 表名 [partition (分区字段=分区值)] select 查询;
5.查询一次表格,对数据进行拆分分别写入到N个不同的表格中,进行分表的操作
- create table emp_a like emp;
- create table emp_b like emp;
- create table emp_c like emp;
- from emp
- insert overwrite table emp_a select * where empno%3=0
- insert overwrite table emp_b select * where empno%3=1
- insert overwrite table emp_c select * where empno%3=2;
复制代码 DQL
select 字段 4
from 表名 1
where 筛选 2
group by 分组 3
having 聚合筛选 5
order by 排序 6
limit 分页; 7
- select deptno,sum(sal) s from emp group by deptno having s>2000;
复制代码
limit 开始行号,连续的取值范围
limit 4,5 从第5行开始,连续取5行数据(开始行号是从0开始的)
limit 3 相称于 limit 0,3 开始值是0,我们可以省略
- --查询前三行数据
- select * from emp limit 3;
- --查询第3行到第5行
- select * form emp limit 2,3;
复制代码
hive中排序除了order by之外,还有 sort by,还有 cluster by,还有 distribute by。
--order by排序
不管表格有多大的数据量,都是将表格当成一个整体,放在一个reduce进程中进行排序的。order by排序很慢。
select * from 表 order by 列名 desc|asc;
--sort by排序
是在reduce中进行排序的,有几个reduce,就会在几个不同的范围内分别排序。当reduce等于1的时候,效果和order by 是一样的。
怎样设置reduce的数目:
set mapred.reduce.tasks; 这个开关的默认值是-1,表示没有逼迫限定,reduce的数目是跟着分桶或者文件的多少来决定的。
select * from 表 sort by 列 desc|asc;
--distribute by排序
distribute by 其实就是在查询的过程中,对表格进行分桶的含义。
distribute by 是不能单独运行的,只能和 sort by 结合使用。
select * from 表 distribute by 分桶字段 sort by 排序字段 desc|asc;
-- cluster by排序
其实就是
select * from 表 distribute by 分桶字段 sort by 按照分桶字段进行排序 asc;
select * from 表 cluster by 列;
- select * from emp distribute by sal sort by sal;
- select * from emp cluster by sal;
- 两句话是一样的效果
复制代码 多表查询
1.子查询嵌套
select * from (select deptno,sum(sal) s from emp group by deptno) t;
在hive中,嵌套的子查询,必须要有个别名。
2.多表团结查询
- cross join 笛卡尔积:显示两个表所有大概的连接情况
- inner join 内连接:查询出两个表格之间共有的数据部门
- left join 外连接之左连接:查询出两个表格的共有数据,然后显示出左边表格独有的数据。
- right join 外连接之右连接:查询出两个表格的共有数据,然后显示出右边表格独有的数据。
- full join 外连接之全连接:查询出两个表格的共有数据,然后分别显示左表和右表独有的数据。
在Hive中,多了一个左半连接的操作:在hive2.x的版本中,不能使用子查询嵌套的语法,用left semi join对嵌套进行语法的替换操作。
select * from a left semi join b on a.xx=b.xx; 这里的*号,代表的不是a和b表的所有字段,只代表a表的所有的字段。
- -- 查询工资比MILLER要高的员工信息
- select * from emp where sal>(select sal from emp where ename='MILLER');
- select * from emp a left semi join emp b on a.ename!='MILLER' and b.ename='MILLER'
- and a.sal>b.sal;
- 从查询的效率上讲,左半连接比嵌套查询更好一点。
复制代码
函数
聚合函数
- max() min() sum() avg() count()
单行函数
1. 数字类型
abs() 求绝对值
round() 四舍五入保留小数精度;如果没有小数精度,只是保留整数的部门
- round(1.234545,3) 效果为1.235
- round(1.2345) 效果为1
floor() 查询离数字最近的小于它的整数,向下取整
ceil() 查询离数字最近的大于它的整数,向上取整
power() 数字的幂运算
随机函数:rand() 取随机的一个数字,范围是0-1
取余数,模运算:%
中位数的盘算:先对数组进行排序,取中间的值 percentile(列名, 0.5)
- create table score(
- stuid int,
- score int
- )row format delimited fields terminated by ',';
- 1,90
- 2,88
- 3,79
- 4,100
- 5,92
- select percentile(score,0.5) from score;
复制代码 2.字符串类型
substr() 连续截取字符串
- substr(字符串,开始位置,连续长度) 开始位置是负数,表示从后往前数
- substr(字符串,开始位置) 没有连续长度会取值到字符串的最后一位
length() 检察字符串的长度
replace() 字符串的替换
- replace(字符串, '旧数据', '新数据') 用新字符替换旧字符
regexp_replace()
translate() 用一串新的数据依次替换前面的一串旧数据
instr()
upper()
lower()
initcap()
lpad()/rpad()
trim()/ltrim()/rtrim()
concat() 拼接字符串。oracle内里只能拼接两个字段,hive中拼接的字段没有数目标限定,hive没有||的拼接符号了
- select concat(empno,ename,job,mgr) from emp;
复制代码 concat_ws() 拼接的字段只能是字符串类型
- select concat_ws(',',ename,job,hiredate) from emp;
- select concat_ws(',',ename,job,hiredate,cast(sal as string)) from emp;
复制代码
- concat_ws()不仅可以拼接多个字段,也可以拼接数组的类型
- select concat_ws('-',array('aa','bb','cc'));
复制代码 split(字符串, '切割符号') 拆分字符串,将一个字符串切割成一个数组
- select split('aaa-bbb-ccc-ddd','-');
复制代码 collect_list() 将一个列的数据,合并成一个数组字段
- select collect_list(job) from emp;
复制代码
collect_set() 将一个列的数据,合并成一个数组字段并对数据进行去重的处置惩罚
- select collect_set(job) from emp;
复制代码
辨认json数据的格式,并且对关键字的值进行提取:get_json_object(json字符串, 提取的路径描述)
get_json_object() 函数不管查询出来的数据样式是什么,类型都是字符串。
- create table test11(
- userid int,
- userinfo string
- );
- 1^A{"username":"aa","age":18,"gender":"male"}
- 2^A{"username":"bb","age":17,"gender":"female"}
- 3^A{"username":"cc","age":19,"gender":"male"}
- 4^A{"username":"dd","age":17,"gender":"female"}
- select userid,
- get_json_object(userinfo, '$.username')
- from test11;
复制代码- create table test12(
- id int,
- fruits string
- )row format delimited fields terminated by '/';
- 1/{"dt":"20200101","datas":[{"name":"apple","price":1.8},{"name":"pear","price":2.1}]}
- 2/{"dt":"20200102","datas":[{"name":"apple","price":1.9},{"name":"pear","price":2.0}]}
- 查询每一天的所有的水果的price信息
- select get_json_object(fruits, '$.datas[*].price') from test12;
复制代码 3.时间日期类型
add_months()
months_between()
last_day()
next_day()
查询当前的年月日信息: current_date
检察当前的年月日时分秒毫秒信息: current_timestamp
- select current_timestamp;
复制代码 检察当前的时间戳数据,累计的秒数时间: unix_timestamp()
转换时间戳提取出时间的维度: from_unixtime()
- select from_unixtime(1738574831,'yyyy-MM-dd HH:mm:ss');
复制代码 进行日期天数的偏移: date_add()
- select date_add('2024-8-2',5);
- select date_add('2024-8-2',-5);
复制代码 查询两个日期之间的天数隔断: datediff()
- select datediff('2024-8-2','2024-7-28');
复制代码 提取日期格式内里的时间维度:year() month() day() hour() minute() second()
- select minute(current_timestamp);
- select year(current_timestamp);
复制代码 练习:使用时间函数,查询距离 2024-8-2 最近的上一个星期二的日期。
- select date_add(next_day('2024-8-2','tue'),-7);
复制代码 4.数据类型转换
cast(值 as 新的类型)
- select cast(1.666 as string);
- select cast(1.666 as int);
- select cast('2024-8-1' as date);
复制代码 5.数组类型
将多个数组合并转换成一个数组: array(值1,值2,值3..)
案例
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |