hivesql学习笔记

数据库介绍

这是本人的个人学习笔记,如有错误,请多指正。

数据库简介

其实数据库就是存放数据的地方,有了数据库之后我们可以直接查找数据。 Excel也可以存放数据,但是相比之下excel无法多人协作,存放的数据量较少,所以数据库就是能大量存放数据的可以多人协作的一个存放数据的系统。

关系型数据库

关系型数据库是由多个表组成的,每个表都是有行和列组成的。关系型数据库中存放着一系列的表,只不过这些表之间是有联系的。 关系型数据库=多张表+表之间的联系 所以对于关系型数据库,只需要掌握多张表中每个表的结构与各表之间的关系就可以了。 表的结构是指每张表长什么样子。表的结构也主要分为三个部分,表名,行,列。 alt 各表之间的关系 alt 以学生表,成绩表这两张为例 alt 在这两张表中,相同颜色代表同一个学生的信息。这两张表通过学号联系在一起。比如我们想知道学生表中学生号为0001的同学的成绩,就需要在成绩表中查找学号为0001的数据。对应的就找到了三门课的成绩。 关系就是数据能够对应的匹配,在关系型数据库中叫做联结,对应英文名称为join。

关系数据库管理系统

管理数据库的计算机软件叫做数据库管理系统,常见的有mysql,hive等等。

什么是sql

关系型数据库通过关系数据库管理系统来实现,人们通过SQL在关系数据库管理系统中查找数据。可以理解为python编译为c然后改变内存的过程。

hive中的基本数据类型

数据类型 长度 例子
TINYINT 1byte有符号整数 20
SMALINT 2byte有符号整数 20
INT 4byte有符号整数 20
BIGINT 8byte有符号整数 20
BOOLEAN 布尔类型 true
FLOAT 单精度浮点数 3.14159
DOUBLE 双精度浮点数 3.14159
STRING 字符序列,单双引号都可 'Hello' "WORLD"

hivesql语法学习

对表本身的操作

如何建表

'''
CREATE TABLE IF NOT EXISTS tmp_dev.test(
name STRING COMMENT 'country name',
GDP FLOAT COMMENT 'gdp of each country'
)
PARTITIONED BY (state STRING)
'''

这是一个典型的建表命令 其中第一行 'CREATE TABLE IF NOT EXISTS tmp_dev.test'意思是在tmp_dev数据库下建名为test的表,如果表已经存在的话,建立相同名称的表会报错,加上'IF NOT EXISTS'可以避免报错。括号内部分则是表的具体内容。其中每个用逗号分隔的语句表示表的某一列的情况。 以'name STRING COMMENT 'country name''为例,其中name就是列名,STRING意思是此列数据的数据类型为STRING,COMMENT关键字后的字符串代表对此列的一个备注。在这里也就是说这列描述的是国家名称。 PARTITIONED BY (state STRING)即创建的是一个分区表,后续会介绍分区表的概念。

分区表

数据分区的一般概念存在已久。其可以有多种形式,但是通常使用分区来水平分散压力,将数据从物理上转移到和使用最频繁的用户更近的地方,以及实现其他目的。 Hive中有分区表的概念。我们可以看到分区表具有重要的性能优势,而且分区表还可以将数据以一种符合逻辑的方式进行组织,比如分层存储。以下面sql代码为例。

'''
CREATE TABLE employees 
(name STRING,
salary  FLOAT,subordinates ARRAY <STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT <street:STRING,city:STRING, state:STRING, zip:INT>)
PARTITIONED BY (country STRING, state STRING)
'''

在这个表中,数据以country和state作为分区,每个country和state的组合都会有一张表。这些表会被储存在能够反映分区结构的子目录中,比如: .../employees/country=CA/state=AB
.../employees/country=CA/state=BC
.../employees/country=US/state=AC

分区字段(这个例子中就是country和state)一旦创建好,表现得就和普通的字段一样。

修改表

重命名

可以使用以下这个语句可以将表log_messages重命名为logmsgs:

'''
ALTER TABLE log_messages RENAME TO logmsgs;
'''
增加表分区
'''
ALTER TABLE log_messages ADD IF NOT EXISTSPARTITION (year = 2011, month = 1, day = 1) LOCATION '/logs/2011/01/01'PARTITION (year = 2011, month = 1, day = 2) LOCATION '/logs/2011/01/02'PARTITION (year = 2011, month = 1, day = 3) LOCATION '/logs/2011/01/03'...;
'''

查询语句

SELECT...FROM

SELECT是SQL中的射影算子。FROM子句标识了从哪个表、视图或嵌套查询中选择记录。 对于一个给定的记录,SELECT指定了要保存的列以及输出函数需要调用的一个或多个列(例如,像count(*)这样的聚合函数)。 例如我们想要从表格employee中读取全部信息,代码如下:

'''
SELECT * FROM employees;
'''

比如我们想要从表格中读取所有人的name,那么代码如下:

'''
SELECT salary FROM employees;
'''
使用列值来计算
'''
SELECT upper(name), salary, deductions["Federal Taxes"],round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
'''

结果如下:

name salary deductions d1
JOHN DOE 100000.0 0.2 80000
MARY SMITH 80000.0 0.2 64000
TODD JONES 70000.0 0.15 59500
BILL KING 60000.0 0.15 51000

注意round(salary * (1 - deductions["Federal Taxes"]))这句,相当于每列中的对应的元素按照这个表达式来进行运算。

算术运算符

Hive支持以下算术运算符:

运算符 类型 描述
A+B 数值 A和B相加
A-B 数值 A减去B
A*B 数值 A乘以B
A/B 数值 A除以B
A%B 数值 A除以B的余数
A&B 数值 A与B按位取与
A B 数值
A^B 数值 A与B按位取亦或
~A 数值 A按位取反
LIMIT语句

典型的查询语句会返回多行数据,可以使用LIMIT语句来限制返回的行数,当查询的表格过于庞大时,直接查询可能会导致集群崩溃,新手建议使用LIMIT语句进行限制。

'''
SELECT * FROM employees LIMIT 100;
'''

此语句只会返回表中的前100行。

列别名

当我们从表中查询某列时,我们可以重命名这个新查询到的列。在进行嵌套SELECT时,比较方便使用。同时在方便我们在打印表头时能够快速理解每列的数据的含义。

在这里主要使用AS关键字。比如:

'''
SELECT upper(name), salary, deductions["Federal Taxes"],round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
'''
嵌套SELECT语句

对于嵌套语句,使用别名是非常有用的

'''
SELECT e.name, e.salary_minus_fed_taxes FROM  
(SELECT upper(name), salary,deductions["Federal Taxes"] as fed_taxes,round(salary * (1 - deductions["Federal Taxes"])) as salary_ minus_fed_taxes FROM employees)e  
WHERE e.salary_minus_fed_taxes > 70000;
'''

在这里'SELECT upper(name), salary,deductions["Federal Taxes"] as fed_taxes,round(salary * (1 - deductions["Federal Taxes"])) as salary_ minus_fed_taxes FROM employees'此层读取的表格被重命名为别名e。在这个语句外面嵌套查询了name和salary_minus_fed_taxes两个字段,同时约束后者的值要大于70,000。

CASE … WHEN … THEN 句式

CASE … WHEN … THEN 句式用于处理单个列的查询结果。例如:

'''
SELECT name, salary,  
CASE WHEN salary  50000.0 THEN 'low' WHEN salary>= 50000.0 AND salary < 70000.0 THEN 'middle'  WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high' ELSE 'very high' END  AS bracket  
FROM employees;
'''

WHERE语句

SELECT语句用于选取字段,WHERE语句用于过滤条件,两者结合使用可以查找到符合过滤条件的记录。我们刚才就使用了下面这个例子来限制查询的结果必须是美国的加利福尼亚州的:

'''
SELECT * FROM employees  
WHERE country = 'US' AND state = 'CA';
'''

GROUP BY语句

GROUP BY 语句通常会和聚合函数一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作。 如下这个查询语句按照苹果公司股票(股票代码APPL)的年份对股票记录进行分组,然后计算每年的平均收盘价:

'''
SELECT year(ymd), avg(price_close) FROM stocks 
WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'   
GROUP BY year(ymd);
'''

ORDER BY 和 SORT BY

Hive中ORDER BY语句和其他的SQL方言中的定义是一样的。其会对查询结果集执行一个全局排序。这也就是说会有一个所有的数据都通过一个reducer进行处理的过程。对于大数据集,这个过程可能会消耗太过漫长的时间来执行。

Hive增加了一个可供选择的方式,也就是SORT BY,其只会在每个reducer中对数据进行排序,也就是执行一个局部排序过程。这可以保证每个reducer的输出数据都是有序的(但并非全局有序)。这样可以提高后面进行的全局排序的效率。

对于这两种情况,语法区别仅仅是,一个关键字是ORDER,另一个关键字是SORT。用户可以指定任意期望进行排序的字段,并可以在字段后面加上ASC关键字(默认的),表示按升序排序,或加DESC关键字,表示按降序排序。 下面是一个使用ORDER BY的例子:

'''
SELECT s.ymd, s.symbol, s.price_closeFROM stocks sORDER BY s.ymd ASC, s.symbol DESC;
'''

下面是一个类似的例子,不过使用的是SORT BY:

'''
SELECT s.ymd, s.symbol, s.price_closeFROM stocks sSORT BY s.ymd ASC, s.symbol DESC;
'''

JOIN语句

JOIN介绍

https://www.runoob.com/sql/sql-join.html

INNER JOIN

https://www.runoob.com/sql/sql-join-inner.html

LEFT JOIN

https://www.runoob.com/sql/sql-join-left.html

RIGHT JOIN

https://www.runoob.com/sql/sql-join-right.html

FULL JOIN

https://www.runoob.com/sql/sql-join-full.html

行转列和列转行

在Hive sql应用中会遇到“行转列”和“列转行”的场景,下面介绍其基本使用语法。

行转列:

关键字:collect_set() / collect_list()、concat_ws()

  1. collect_set()/collect_list(): collect_set( )函数只接受基本数据类型,作用是对参数字段进行去重汇总,返回array类型字段; collect_list()函数和collect_set( )作用一样,只是前者不去重,后者去重。

  2. concat_ws(): concat_ws (separator,字符串A/字段名A,字符串B/字段名B…)是concat的特殊形式,第一个参数是分隔符,分隔符会放到要连接的字符串之间,分隔符可以是字符串,也可以是其他参数。如果分隔符为NULL,则结果为NULL,函数会忽略任何分隔符参数后的 NULL值。但是concat_ws( )不会忽略任何空字符串。(然而会忽略所有的 NULL),如果参数为string类型数组则合并数组内字符串。 例:concat_ws( ',', [ 'a ', 'b'])–> 'a,b'

行转列基本语法:

select 分类字段,concat_ws(',',collect_set(合并字段)) as 别名 from table_name group 分类字段;

原始数据

name gender times
张三
李四
王五
赵六

先用collect_set将列拼接在一起,然后再通过concat_ws进行展开拼接

'''
SELECT a.gender_times,
   concat_ws(';',collect_set(a.name)) name
FROM
  (SELECT name,
      concat(gender, '_','times') gender_times
   FROM ***fo) t
GROUP BY t.gender_times;
'''

查询结果

gender_times name
男_唐 张三;李四
男_明 王五;赵六

列转行

关键字:explode()、split()和LATERAL VIEW

  1. split():

将一个字符串按照指定字符分割,结果为一个array;

  1. explode():

将一列复杂的array或者map拆分为多行,它的参数必须为map或array;

  1. lateral view:

lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateralview再把结果组合,产生一个支持别名表的虚拟表。

列转行基本语法:

select 字段,字段别名 from table_name lateral view explode(split(拆分字段,分隔符))表别名 as 字段别名;

原始数据 province | city| :-: | :-: | 河南 | 郑州市,开封市,洛阳市| 河北 | 石家庄市,保定市| 湖南 | 长沙市,岳阳市,常德市| -- addr为表名

'''

SELECT province,
       city_n
FROM addr LATERAL VIEW explode (split(city,',')) addr_tmp AS city_n;

'''

-- 查询结果

province city
河南 郑州市
河南 开封市
河南 洛阳市
河北 石家庄市
河北 保定市
湖南 长沙市
湖南 岳阳市
湖南 常德市

常见聚合函数

可参考 https://www.runoob.com/sql/sql-function.html

全部评论
感谢网友1314的笔记
点赞 回复 分享
发布于 2021-12-24 14:44

相关推荐

存一千万就可以进大厂实习
石圪节公社发型师:有存一千万的实力还实习个嘚,直接躺平
点赞 评论 收藏
分享
面试官人很好,态度和蔼可亲,没答出来时也会引导你去思考。由于是晚上面的,导致我白天一天都有点紧张,面的时候状态也不是很好,正常可能面试官提问完应该思考几秒再答,而我就像抢答一样一口气把所有会的都说出来,这样就导致逻辑比较混乱,东一句西一句的。首先是自我介绍,先把会的技术大致讲一下,由于我八股背的多所以着重讲了一下,Java,go,jvm,MySQL,Redis,计网,操作系统这些,然后一小部分闲聊,然后先问了一下项目,面试官问我这个项目是否落实之类的,直接坦言说是写的练手的,包括之前也写过IM通讯,外卖之类的。然后面试官就把提问的重点放在了八股上。先问了Java:类加载器(答:3种+自定义类加载器、tomcat、原因+双亲委派+好处)JVM参数(答:xmx,xms,newsize这些,问我是如何设定的,我回答是把内存分一半给堆,再把堆分一半给新生代,这方面确实不太了解)然后问了一下并发相关的:线程池(答:线程池的7个参数(忘了线程工厂和阻塞时间了),3个重要参数,还有线程如何启用,为什么要设计最大线程数之类的,提到Java栈默认分配1MB运行时不可以更改)AQS(答:先讲clh是自旋锁+list,然后是AQS在这个基础上做的两个优化,然后举了一下reentrantlock根据state如何获取资源)CAS(答:使用三个字段,aba问题,然后将通常搭配自旋锁实现,面试官问通常会自旋多少次,这个不太了解,答的100,然后问100次大概多少秒,回答微秒级,然后面试官讲了一下怎么做资源可能没用完,意识到可能还需要进行阻塞操作)然后考虑一下Linux命令(top,ps,如何使用管道符过滤线程和使用Linux启动线程没答出来)然后问Redis:持久化机制(答:三种aof,rdb,混合,aof的三个参数刷盘策略,rdb以快照保存,使用bgsave会使用子线程来保存不会阻塞,而aof虽然会阻塞但是只在写完数据后追加一条命令,不会太影响,然后是他俩的优缺点,还有混合是怎么保存数据的)集群模式(答:三种,主从复制到缺点再到哨兵机制,正常使用三个哨兵互相监督,主节点挂了投票选主哨兵然后选主节点,然后额外讲一下脑裂的问题,主节点进行数据更新然后把命令写入aof来同步从节点,最后cluster集群,如何实现,使用16383个哈希槽(艹答成16384了),先根据哈希码取余,再根据节点数取余决定放在哪个节点上,然后问了一下我会怎么选集群模式,首先是cluster的问题,会让管道操作之类的失效,然后哨兵会导致整个集群结构变得复杂,使用小项目可能会考虑哨兵,大的考虑cluster,然后考了一下cluster如果一个节点挂了怎么办,根据节点数重新取余然后数据转移,面试官说这么转移比较慢,有没有别的办法,我隐约记得使用一个类似环形数组的方式,想不起来了)然后考了一下MySQL的b+树(这方面的知识点太多了,导致我什么都想讲逻辑就比较乱,讲了一下聚簇索引,树的叶子节点对应着一张页16KB,MySQL有一个区的概念,把这些页放在同一个区中,这样叶子节点的双向链表遍历时速度更快,然后b+树的扇出比较大(非常二,说成扇度之类的,面试官以为说的是扇区)这样层数就比较小,一行1kb数据的话3层可以放心2000w数据)其他的暂时想不起来了算法是lru,面试官问要不要提示,我说写个,然后写了10分钟左右,说大概写好了,但是面试官指出了2个小错误,第一个马上就改回来了,第二个一直没看出来(大脑这时候已经停止工作了)反问:问学习建议,说根据实际的项目进行深入,考虑应该怎么做,还问了一下组里面是做Java的吗?面试官说他是做go的,组里什么语言都有,语言影响不大,连忙补充了一句我对go的底层有深入源码的学习)结束。总体感觉答得不太好,没有太体现出深度,细节也不够全面。
下一个更好呗:佬,我投完云智一直没消息,多久约的一面啊
查看14道真题和解析
点赞 评论 收藏
分享
05-11 11:48
河南大学 Java
程序员牛肉:我是26届的双非。目前有两段实习经历,大三上去的美团,现在来字节了,做的是国际电商的营销业务。希望我的经历对你有用。 1.好好做你的CSDN,最好是直接转微信公众号。因为这本质上是一个很好的展示自己技术热情的证据。我当时也是烂大街项目(网盘+鱼皮的一个项目)+零实习去面试美团,但是当时我的CSDN阅读量超百万,微信公众号阅读量40万。面试的时候面试官就告诉我说觉得我对技术挺有激情的。可以看看我主页的美团面试面经。 因此花点时间好好做这个知识分享,最好是单拉出来搞一个板块。各大公司都极其看中知识落地的能力。 可以看看我的简历对于博客的描述。这个帖子里面有:https://www.nowcoder.com/discuss/745348200596324352?sourceSSR=users 2.实习经历有一些东西删除了,目前看来你的产出其实很少。有些内容其实很扯淡,最好不要保留。有一些点你可能觉得很牛逼,但是面试官眼里是减分的。 你还能负责数据库表的设计?这个公司得垃圾成啥样子,才能让一个实习生介入数据库表的设计,不要写这种东西。 一个公司的财务审批系统应该是很稳定的吧?为什么你去了才有RBAC权限设计?那这个公司之前是怎么处理权限分离的?这些东西看着都有点扯淡了。 还有就是使用Redis实现轻量级的消息队列?那为什么这一块不使用专业的MQ呢?为什么要使用redis,这些一定要清楚, 就目前看来,其实你的这个实习技术还不错。不要太焦虑。就是有一些内容有点虚了。可以考虑从PR中再投一点产出
点赞 评论 收藏
分享
评论
2
1
分享

创作者周榜

更多
牛客网
牛客企业服务