大数据学习笔记-2020-10-02--hive基础（数据类型与hql语句基础

最新推荐文章于 2022-10-09 17:19:32 发布
最新推荐文章于 2022-10-09 17:19:32 发布
阅读量256 收藏 1
点赞数
分类专栏： 大数据学习笔记 文章标签： hdfs mapreduce hadoop hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/airleaya/article/details/108906136
版权

hive的数据类型

  • hive中的基本数据类型

    Hive数据类型Java数据类型长度例子
    TINYINTbyte1byte有符号整数20
    SMALINTshort2byte有符号整数20
    INTint4byte有符号整数20
    BIGINTlong8byte有符号整数20
    BOOLEANboolean布尔类型，true或者falseTRUE FALSE
    FLOATfloat单精度浮点数3.14159
    DOUBLEdouble双精度浮点数3.14159
    STRINGstring字符系列。可以指定字符集。可以使用单引号或者双引号。‘now is the time’ “for all good men”
    TIMESTAMP时间类型
    BINARY字节数组

    hive中的string类型相当于数据库中的varchar类型，是一个可变的字符串，不过它不能声明其可存储的最大值，理论上可存储2GB的字符数。

  • 集合数据类型

    数据类型描述语法示例
    STRUCT和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。struct()
    MAPMAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素map()
    ARRAY数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。Array()

    • ARRAY和MAP与Java中的Array和Map类似

    • 而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

  • hive中类型的转化

    hive中的原子数据类型是可以进行隐试转换的，类似java中的类型转换，例如表达式使用int类型，tinyint会自动转换为int类型，但是hive不自动进行反向转化，如int不会自动转换为tinyint，如果有必要，可以使用cast操作。

    1. 隐式类型转换规则如下

      1. 1任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

      2. 2所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

      3. 3TINYINT、SMALLINT、INT都可以转换为FLOAT。

      4. 4BOOLEAN类型不可以转换为任何其它的类型。

    2. 可以使用CAST操作显示进行数据类型转换

      • 例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

hive的DDL（数据定义）

  • 创建数据库：

    • create database db_hive;

  • 创建数据库并避免报数据库已存在异常：

    • create database if not exists db_hive;

  • 查看数据库：

    • show databases;

  • 过滤数据库名：

    • show databases like 'db_hive*';

  • 显示数据库详细信息（详细信息：

    • desc database db_hive;
    • desc database extended db_hive;

  • 切换数据库：

    • use db_hive;

  • 删除数据库（判断存在、强制删除：

    • drop database db_hive;
    • drop database if exists db_hive;
    • 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]

    • 字段解释：

      • create table：创建一个指定名字的表，同样可以通过if not exists关键字忽略表已存在异常
      • external：创建外部表的关键字，如不加则创建内部表（管理表）
      • comment：为表或列添加注释
      • partitioned by：创建分区表
      • clustered by：创建分桶表
      • sorted by：不常用，见后续学习笔记
      • row format：指定SerDe，即Serialize/Deserilize的简称，目的是用于序列化和反序列化。
      • stored as：指定存储文件类型
      • location：指定存储在hdfs上的路径
      • like允许用户负责现有的表的结构

内部表（管理表）

​ 所有默认创建的表都是所谓的内部表，也叫管理表。因为这种表会（或多或少地）控制着数据的生命周期。

​ 内部表的数据会被储存在hive指定的目录中，当我们将之删除的时候，其对应的数据也会被全部删除。

  • 案例实操

    普通创建表：

    hive (mydb)> create table if not exists student2(
           > id int, name string
           > )
           > row format delimited fields terminated by '\t'
           > stored as textfile;

    此表即是内部表

外部表

​ 外部表的数据不会被hive所完全管理。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

  • 内部表和外部表的使用场景

    每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。-

DML 数据操作

数据导入

  • 向表中装载数据

    hive> load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

    • load data:表示加载数据

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

    • inpath:表示加载数据的路径

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

    • into table:表示加载到哪张表

    • student:表示具体的表

    • partition:表示上传到指定分区

    导入实操：

    ​ 创建表：

    create table if not exists test1(
id int,
id2 string)
row format delimited
fields terminated by ',';

    数据：

    -- /opt/module/datas/test1.txt
12,a1
13,ssd
23,ewfw
23,21wq
34,wefw
213,fieldse
3422,ee

    导入并查看数据：

    hive (mydb)> load data local inpath
           > '/opt/module/datas/test1.txt'
           > into table test1;
Loading data to table mydb.test1
Table mydb.test1 stats: [numFiles=1, totalSize=57]
OK
Time taken: 1.139 seconds


hive (mydb)> select * from test1;
OK
test1.id	test1.id2
12	a1
13	ssd
23	ewfw
23	21wq
34	wefw
213	fieldse
3422	ee

    成功。

  • 通过查询语句插入数据

    创建表test2，并将表test1中数据插入test2

    insert into test2
select * from test1;

  • 通过查询语句在创建表时插入数据：

    create table if not exists test3
as select * from test2;

  • 创建表的时候通过location指定加载数据的路径

    -- 注意这里的数据路径是HDFS上的路径
create table if not exists test4(
id int,
id2 string)
row format delimited
fields terminated by ','
location '/datas/test1';

  • import到hive表中

数据导出

  • 通过insert导出

    insert overwrite local directory 
'/opt/module/datas/test1.insertport'
select * from test1;

    查看导出的数据：

    [kgg@hadoop202 datas]$ ls test1.insertport/
000000_0
[kgg@hadoop202 datas]$ cat test1.insertport/000000_0 
12a1
13ssd
23ewfw
2321wq
34wefw
213fieldse
3422ee

    发现数据没有格式化

    格式化导出：

    insert overwrite local directory 
'/opt/module/datas/test1.insertport.format'
row format delimited 
fields terminated by '\t'
select * from test1;

    查看导出的数据：

    [kgg@hadoop202 datas]$ ls
test1.insertport  test1.insertport.format  test1.txt
[kgg@hadoop202 datas]$ ls test1.insertport.format/
000000_0
[kgg@hadoop202 datas]$ cat test1.insertport.format/000000_0 
12	a1
13	ssd
23	ewfw
23	21wq
34	wefw
213	fieldse
3422	ee

    成功。

  • 通过HDFS shell命令直接导出到本地

    • 能这样做的原因在于hive的数据是直接存储在HDFS中的，所以直接在对应的路径下载该数据即可
    -- hive中可以直接使用hdfs shell命令
dfs -get 
/user/hive/warehouse/mydb.db/test1/test1.txt
/opt/module/datas/test1.HDFS;

    查看数据

    [kgg@hadoop202 datas]$ ls
test1.HDFS  test1.insertport  test1.insertport.format  test1.txt
[kgg@hadoop202 datas]$ cat test1.HDFS 
12,a1
13,ssd
23,ewfw
23,21wq
34,wefw
213,fieldse
3422,ee

    成功。

  • 通过hive shell导出

    • 原理在于，hive提供了 -f -e两个语句，可以不进入hive shell页面执行hive语句

    执行并查询

    # 注意，因为执行时没有use database，所以表前要带上数据库的库名
# hive -e 'select * from mydb.test1;' > /opt/module/datas/test1.hiveshelll
[kgg@hadoop202 datas]$ /opt/module/hive/bin/hive -e 'select * from mydb.test1;' > /opt/module/datas/test1.hiveshelll

Logging initialized using configuration in file:/opt/module/hive/conf/hive-log4j.properties
OK
Time taken: 1.036 seconds, Fetched: 7 row(s)
[kgg@hadoop202 datas]$ cat test1.hiveshelll 
test1.id	test1.id2
12	a1
13	ssd
23	ewfw
23	21wq
34	wefw
213	fieldse
3422	ee

    成功。

  • export导出到HDFS上

    export table test1 to 
'/datas/test1.export';

    查看数据

    hive (default)> dfs -cat /datas/test1.export/data/test1.txt;
12,a1
13,ssd
23,ewfw
23,21wq
34,wefw
213,fieldse
3422,ee

    成功。

  • 其他插件导出，比如通过sqoop导出

清除表中的数据（Truncate）

++++++++++++++++++++++++++++++++++++++

  • 注意：truncate，只能删除管理表（内部表）的数据，不能删除外部表中的数据。

    truncate table test1;

    查看效果

    hive (mydb)> select * from test1;
OK
test1.id	test1.id2
12	a1
13	ssd
23	ewfw
23	21wq
34	wefw
213	fieldse
3422	ee
Time taken: 0.447 seconds, Fetched: 7 row(s)


hive (mydb)> truncate table test1;
OK
Time taken: 0.121 seconds


hive (mydb)> select * from test1;
OK
test1.id	test1.id2
Time taken: 0.048 seconds

    表中的数据都被删除了，成功。

基本查询（select…from）

  • 全表和特定列查询

    select * from test1;

    select id from test1;

  • 列别名

    • 重命名一个列

    • 便于计算

    • 紧跟列名，也可以加入关键字’AS’

    • 实例：

      hive (mydb)> select id intID,id2 as stringID from test1;
OK
intid	stringid
12	a1
13	ssd
23	ewfw
23	21wq
34	wefw
213	fieldse
3422	ee
Time taken: 0.049 seconds, Fetched: 7 row(s)

  • 算术运算符

    运算符描述
    A+BA和B 相加
    A-BA减去B
    A*BA和B 相乘
    A/BA除以B
    A%BA对B取余
    A&BA和B按位取与
    A|BA和B按位取或
    A^BA和B按位取异或
    ~AA按位取反

    案例，查询所有id和id对7取余：

    hive (mydb)> select id,id%7 id_7 from test1;
OK
id	id_7
12	5
13	6
23	2
23	2
34	6
213	3
3422	6
Time taken: 0.049 seconds, Fetched: 7 row(s)

  • 常用函数

    • count 计数

      hive (mydb)> select count(1) lineCount from test1;
OK
linecount
7
Time taken: 14.821 seconds, Fetched: 1 row(s)

    • max 最大

      hive (mydb)> select max(id) id_max from test1;
OK
id_max
3422
Time taken: 1.207 seconds, Fetched: 1 row(s)

    • min 最小

      hive (mydb)> select min(id) id_min from test1;
OK
id_min
12
Time taken: 1.176 seconds, Fetched: 1 row(s)

    • sum 求和

      hive (mydb)> select sum(id) id_sum from test1;
OK
id_sum
3740
Time taken: 1.15 seconds, Fetched: 1 row(s)

    • avg 平均值

      hive (mydb)> select avg(id) id_avg from test1;
OK
id_avg
534.2857142857143
Time taken: 1.146 seconds, Fetched: 1 row(s)

  • limit语句

    • 有些时候查询会返回很多的数据，而limit可以限制返回的行数

      hive (mydb)> select * from test1 limit 3;
OK
test1.id	test1.id2
12	a1
13	ssd
23	ewfw
Time taken: 0.04 seconds, Fetched: 3 row(s)

where语句

使用where子句可以过滤不满足条件的行，where子句跟在from子句后面

案例:

hive (mydb)> select * from test1 where id<30;
OK
test1.id	test1.id2
12	a1
13	ssd
23	ewfw
23	21wq
Time taken: 0.451 seconds, Fetched: 4 row(s)

  • 比较运算符

    下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

操作符支持的数据类型描述
A=B基本数据类型如果A等于B则返回TRUE，反之返回FALSE
A<=>B基本数据类型如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B基本数据类型A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B基本数据类型A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B基本数据类型A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B基本数据类型A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B基本数据类型A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C基本数据类型如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL所有数据类型如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL所有数据类型如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)所有数据类型使用 IN运算显示列表中的值
A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP BSTRING 类型B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

  • 实例：

    • id等于12
    hive (mydb)> select * from test1 where id=12;
OK
test1.id	test1.id2
12	a1
Time taken: 0.427 seconds, Fetched: 1 row(s)
    • id在50到50000之间
    hive (mydb)> select * from test1 where id between 50 and 50000;
OK
test1.id	test1.id2
213	fieldse
3422	ee
Time taken: 0.423 seconds, Fetched: 2 row(s)
    • id为12或23
    hive (mydb)> select * from test1 where id in (12,23);
OK
test1.id	test1.id2
12	a1
23	ewfw
23	21wq
Time taken: 0.064 seconds, Fetched: 3 row(s)

  • 逻辑运算符

操作符含义
AND逻辑并
OR逻辑或
NOT逻辑否

分组

  • group by语句

    • group by语句通常与聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作

    先给test1表里加了点数据：

    hive (mydb)> select * from test1;
OK
test1.id	test1.id2
12	a1
13	ssd
23	ewfw
23	21wq
34	wefw
213	fieldse
3422	ee
3422	wqe
123	ww
23	ee
2131	wefw
33	a1
23	few
12	wer
12	we
13	weew
13	wewe
34	ewr
Time taken: 0.038 seconds, Fetched: 18 row(s)

    测试：

    hive (mydb)> select id,count(*) id_count
           > from test1
           > group by id;
OK
id	id_count
12	3
13	3
23	4
33	1
34	2
123	1
213	1
2131	1
3422	2
Time taken: 1.184 seconds, Fetched: 9 row(s)

  • having语句

    1. having与where不同点
      1. where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
      2. where后面不能写分组函数，而having后面可以使用分组函数。
      3. having只用于group by分组统计语句。

    实例：

    求所有id2为a1或ee的数据的id的和

    hive (mydb)> select id2,sum(id) id_sum 
           > from test1 group by id2 
           > having id2='a1' or id2='ee';
OK
id2	id_sum
a1	45
ee	3445
Time taken: 1.2 seconds, Fetched: 2 row(s)

join语句

  • 等值join：

    Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

  • 不想编数据，不练join了

  • join和sql语句的join差不多

  • 注意在join中不支持or

排序

  • 全局排序 order by

    • 使用order by子句排序

      • asc：升序
      • desc：降序

    • order by子句只会将会使得全部任务只由一个reduce完成，应当尽量不使用。

    • order by子句在select语句的结尾

      实例：按id降序：

      hive (mydb)> select * from test1 order by id desc;
OK
test1.id	test1.id2
3422	ee
3422	wqe
2131	wefw
213	fieldse
123	ww
34	ewr
34	wefw
33	a1
23	few
23	ee
23	21wq
23	ewfw
13	weew
13	wewe
13	ssd
12	wer
12	we
12	a1
Time taken: 1.188 seconds, Fetched: 18 row(s)

  • 每个mapreduce内部排序（sort by）

    • 对每个reducer内部进行排序，对全局结果集来说不是排序

  • 分区排序（distribute by）

    • 类似mr中的partition，进行分区，结合sort by使用
    • distribute by语句要写在sort by之前

  • cluster by

    • 当distribute by和sort by的字段相同的时候，可以使用cluster by
    • 但是cluster by只能时升序排序

分桶及抽样查询

  • 分桶表数据存储

    • 分区针对的是文件路径，分桶针对的是数据文件。
    • 分区提供一个隔离数据和优化查询的遍历方式。不过并非所有的数据都可以形成合理的分区，特别时之前提到的要确定和食的划分大小这个疑虑。
    • 分桶是将数据分解成更容易管理的若干部分的另一个技术。

  • 建立分桶表：

    数据：

    /opt/module/datas/student.txt
1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

  • 创建表并导入数据

    hive (mydb)> create table stu_buck
           > (id int,name string)
           > clustered by(id)
           > into 4 buckets
           > row format delimited 
           > fields terminated by '\t';
OK
Time taken: 0.105 seconds
-- 查看stu_buck表的情况
hive (mydb)> desc formatted stu_buck;
OK
...              	 
Num Buckets:        	4                   	 
Bucket Columns:     	[id]                	 
...

hive (mydb)> load data local inpath '/opt/module/datas/student.txt' into table stu_buck;
Loading data to table mydb.stu_buck
Table mydb.stu_buck stats: [numFiles=1, totalSize=151]
OK
Time taken: 0.568 seconds

    去HDFS的web端查看数据，发现没有分桶存储，依旧是整块的数据：

在这里插入图片描述

  • 创建分桶表的时候通过子查询的方式导入数据

    • 先创建一个普通的表stu并导入数据

      hive (mydb)> create table stu(id int,name string)
           > row format delimited fields terminated by '\t';
OK
Time taken: 0.07 seconds
hive (mydb)> load data local inpath '/opt/module/datas/student.txt' into table stu;
Loading data to table mydb.stu
Table mydb.stu stats: [numFiles=1, totalSize=151]
OK
Time taken: 0.239 seconds
hive (mydb)> truncate table stu_buck;-- 清除stu_buck表中的数据
OK
Time taken: 0.085 seconds
hive (mydb)> insert into table stu_buck
           > select id, name from stu;
OK
Time taken: 1.923 seconds
-- 可以看到数据导入成功了
hive (mydb)> select * from stu_buck;
OK
stu_buck.id	stu_buck.name
1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16
Time taken: 0.048 seconds, Fetched: 16 row(s)

在这里插入图片描述

发现还是没有分桶，继续阅读学习文档才发现还要设置一个属性：

```sql
-- 设置分桶
hive (mydb)> set hive.enforce.bucketing = true;
-- 设置reduce数量为自动
hive (mydb)> set mapreduce.job.reduces=-1;
-- 再执行通过查询插入数据
hive (mydb)> insert into table stu_buck
           > select id, name from stu;
OK
id	name
Time taken: 14.366 seconds
-- 查看数据
hive (mydb)> select * from stu_buck;
stu_buck.id	stu_buck.name
1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16
Time taken: 0.051 seconds, Fetched: 32 row(s)
```

终于成功分桶了：

在这里插入图片描述

**注意** 这里我在最后一次操作前由于一些问题退出了一次hive，导致后来最后这一步的操作一直不能成功分桶，原来是因为在hive中通过set设置的属性是一次性的，每次退出都会导致这些属性失效。重新设置了属性后才成功。

  • 分桶抽样查询

    • 对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

    • tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。

      • y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。

      • x表示从第几个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

      • x的值必须小于等于y的值。

其他查询函数

  • 空字段赋值：

    • NVL：同sql语句中的ifnull函数，使用的格式为：NVL( string1, replace_with)。
    • 其中当string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

  • case when 语句

    • 实例，查询学生中各个部门中的男女个数

    • 数据：

      hive (mydb)> select * from stu;
OK
stu.id	stu.name	stu.sex	stu.part
1001	ss1	m	a1
1002	ss2	m	a2
1003	ss3	f	a3
1004	ss4	f	a3
1005	ss5	m	a2
1006	ss6	f	a3
1007	ss7	m	a1
1008	ss8	f	a2
1009	ss9	m	a1
1010	ss10	m	a3
1011	ss11	f	a2
1012	ss12	f	a1
1013	ss13	f	a3
1014	ss14	m	a3
1015	ss15	m	a2
1016	ss16	m	a1
Time taken: 0.038 seconds, Fetched: 16 row(s)

      case when子句查询:

      hive (mydb)> select 
           > part,
           > sum(case sex when 'm' then 1 else 0 end) male,
           > sum(case sex when 'f' then 1 else 0 end) female
           > from stu 
           > group by part;
OK
part	male	female
a1	4	1
a2	3	2
a3	2	4
Time taken: 15.167 seconds, Fetched: 3 row(s)

      成功。

  • 行转列

    • 相关函数：

      • CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
      • CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
      • COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

    • 实例，将上例中部门相同的学生查询并在同一行输出：

      hive (mydb)> select part,concat_ws('|',collect_set(name)) students
           > from stu 
           > group by part;
OK
part	students
a1	ss1|ss7|ss9|ss12|ss16
a2	ss2|ss5|ss8|ss11|ss15
a3	ss3|ss4|ss6|ss10|ss13|ss14
Time taken: 15.989 seconds, Fetched: 3 row(s)

      成功。

  • 列转行

    • 相关函数：

      • EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
      • LATERAL VIEW关键字：
        • 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
        • 解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

    • 实例：

      --数据
hive (mydb)> select * from hmx;
OK
hmx.name	hmx.labels
蕾米莉亚	["红魔馆","吸血鬼","操纵命运","领袖般的魅力","可怕","高傲","姐姐"]
芙兰朵露	["恶魔之妹","吸血鬼","魔法少女","红魔馆","妹妹","破坏一切"]
咲夜	["侍奉吸血鬼","女仆","操纵时间","女仆长","红魔馆","人类","潇洒","完美"]
美铃	["红魔馆","妖怪","门卫","中国","武术","中国风","睡觉"]
帕秋莉	["红魔馆","魔女","不动的大图书馆","魔法使","红魔馆的头脑","足不出户"]
小恶魔	["恶魔","属魔","图书馆","三无","无立绘","无符卡","无对白"]
Time taken: 0.034 seconds, Fetched: 6 row(s)

      需求：将角色标签展开并与名字一一对应

      hive (mydb)> select name,label from hmx
           > lateral view explode(labels) t as label;
OK
name	label
蕾米莉亚	红魔馆
蕾米莉亚	吸血鬼
蕾米莉亚	操纵命运
蕾米莉亚	领袖般的魅力
蕾米莉亚	可怕
蕾米莉亚	高傲
蕾米莉亚	姐姐
芙兰朵露	恶魔之妹
芙兰朵露	吸血鬼
芙兰朵露	魔法少女
芙兰朵露	红魔馆
芙兰朵露	妹妹
芙兰朵露	破坏一切
咲夜	侍奉吸血鬼
咲夜	女仆
咲夜	操纵时间
咲夜	女仆长
咲夜	红魔馆
咲夜	人类
咲夜	潇洒
咲夜	完美
美铃	红魔馆
美铃	妖怪
美铃	门卫
美铃	中国
美铃	武术
美铃	中国风
美铃	睡觉
帕秋莉	红魔馆
帕秋莉	魔女
帕秋莉	不动的大图书馆
帕秋莉	魔法使
帕秋莉	红魔馆的头脑
帕秋莉	足不出户
小恶魔	恶魔
小恶魔	属魔
小恶魔	图书馆
小恶魔	三无
小恶魔	无立绘
小恶魔	无符卡
小恶魔	无对白
Time taken: 0.043 seconds, Fetched: 41 row(s)

      成功。

  • 窗口函数

    • 相关函数说明：
      • OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
        • CURRENT ROW：当前行
        • n PRECEDING：往前n行数据
        • n FOLLOWING：往后n行数据
        • UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
      • LAG(col,n)：往前第n行数据
      • LEAD(col,n)：往后第n行数据
      • NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
        • 注意：n必须为int类型。
airleaya
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive学习笔记之八——关于concat的between取值范围问题
NLP与推荐算法
06-21 1271
For Video Recommendation in Deep learning QQ Group 277356808 For Visual in deep learning QQ Group629530787 I'm here waiting for you wanna have a date with someone in Beijing please join in the QQ Group737813700 在拉取日志数据时发现阶段性拉取的数据比后来拉取的整天数...
hive 查询语法
weixin_44017567的博客
03-30 263
查询 [WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0) SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE wh...
Hive 查询
weixin_34293141的博客
06-18 222
6. Hive 查询 6.1 基本查询（Select ... From) 6.1.1 常用函数 求总行数(count): select count(*) AS cnt from emp; 求工资的最大值(max)：select max(sal) max_sal from emp; 求工资的最小值(min): select min(sal) min_sal from emp; 求工资的总和(sum...
Hive-查询
liu_1221的博客
09-26 550
目录 基本查询（Select…From） 全表和特定列查询 列别名 算术运算符 常用函数 Limit语句 Where语句 比较运算符（Between/In/ Is Null） Like和RLike 逻辑运算符（And/Or/Not） 分组 Group By语句 Having语句 Join语句 等值Join 表的别名 内连接 左外连接 右外连接 满外连接 ...
hive的between-and问题
Sin_Geek成长の迹
09-19 2万+
背景 hive版本1.2.1 存储格式parquet 筛选字段为STRING类型的日期，如’20190918’时出现不包含头尾的现象。 实验 先分别创建parquet格式T1表与textfile格式的T2表， CREATE TABLE IF NOT EXISTS TEMP.t1 ( a STRING ) Partitioned by (BDW_Data_Dt STRING) Stored ...
Hadoop_Hive_Project:NYU CSCI-GA.3033-003的课程项目
04-29
1. **数据仓库工具**：Hive提供了将结构化数据映射到HDFS上的能力，使得用户可以通过SQL-like语言HQL对大数据进行查询和分析，降低了大数据处理的门槛。 2. **元数据管理**：Hive维护了关于数据表、列和分区等元数据...
大数据hive笔记.zip
10-05
它提供了一种基于Hadoop的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供SQL（HQL，Hive Query Language）查询功能，使得用户能够通过SQL语句对海量数据进行分析。Hive的设计目标是将复杂的...
apache-hive-1.2.1-bin.tar.gz.zip
05-11
3. **HQL（Hive Query Language）**：Hive 提供的 SQL 风格的查询语言，允许用户编写复杂的查询语句来处理大数据。 4. **编译器和优化器**：将 HQL 转换为 MapReduce 作业，同时进行查询优化，如消除冗余操作、选择...
hive学习笔记精华版
10-08
【Hive学习笔记精华版】 Hive是大数据处理领域中的一款重要工具，它基于Hadoop构建，主要用于数据仓库和数据分析。作为一个离线处理系统，Hive可以将结构化的数据文件映射成数据库表，允许用户使用类似SQL的查询...
hive搭建及使用入门简介（内含PPT、各种表创建sql及hive搭建使用笔记）
03-08
“各种表创建SQL”这部分将详细讲解CREATE TABLE语句的各种用法，包括创建基本表、分区表、分桶表，以及指定列属性（如是否可为空、默认值、数据类型等）。此外，还有ALTER TABLE语句用于修改已存在的表结构。 ...
Hive 不同类型的数值型数据和对应的范围
最新发布
houzhizhen的专栏
10-09 466
Hive 不同类型的数值型数据和对应的范围
between and 双闭合区间
germmy-神一样的开发
03-05 1245
假设查询一段时间是2012-03-05的，我以前用如下语句：   where time between '2012-03-05 00:00:00' and '2012-03-05 23:59:59'   但后来总是发生错误。   仔细研究后发现，这句话不仅仅将3月5日当天的数据，还将time='2012-03-06 00:00:00'的数据带出来了。   也就是说，sql serv...
Hive数据类型
惊鸿的博客
04-11 1万+
Hive数据类型支持的类型原始数据类型注意DATE类型整数类型小数、Boolean、二进制文本类型时间类型类型转换隐式转换显示转换（CAST函数）复杂数据类型详解ARRAY和MAPSTRUCTUNION实例 支持的类型 hive支持两种数据类型：原始数据类型、复杂数据类型 原始数据类型 原始数据类型包括数据型，布尔型，字符串型，具体如下表： 类型 描述 示例 TINYINT（tinyint） 一个字节（8位）有符号整数， -128~127 1 SMALLINT（smallint）
hive-分析函数
智慧与美貌并存
06-28 2087
分析函数是什么？ 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。   分析函数和聚合函数的不同之处是什么？ 普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。   分析函数的形式 分析
hive之窗口函数理解与实践
热门推荐
xiepeifeng的专栏
01-13 4万+
rt_data表存放了当天每半小时的店铺销售数据 ------------------------------------------------------------ 1.统计截止到当前时间段的店铺累计销售金额 select shop_id, stat_date, ordamt, sum(ordamt) over(partition by shop_id order by stat_da
sql 中使用 between and 查询日期时左右闭合的问题
wang037195的博客
06-20 6223
问题： sql 中使用 between and 查询日期时左右闭合的问题 用 select * from TABLE where date between '2009-1-22' And '2009-1-22' ，结果查不到 原因： 短日期类型默认Time为00:00:00，所以当使用between作限制条件时，就相当于 between '2009-1-22 00:00:00' and...
sql语句between and是闭区间的证明SQL
tgyman的博客
01-17 6483
sql语句between and 是闭区间的证明语句
阿里B2B-ICBU Hive学习笔记：数据仓库与Hadoop详解
Hive学习笔记是阿里巴巴B2B-ICBU数据应用部门针对Hive这一大数据处理框架的详细学习资料。Hive作为基于Hadoop的数据仓库基础设施，为大规模数据的存储、查询和分析提供了便利。它设计了一种类SQL的语言——HiveQL，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值