立即登录 免费注册

超硬核！只需一篇万字博客带你掌握Hive❤️【小白还不赶紧收藏起来】

闲聊 闲聊 1278 人阅读 | 0 人回复

<
创做不容易，各人面个赞再走嘛！

目录



保举珍藏的Hive言语年夜齐

必需要看的媒介

上次写了一篇引见Hive的专客，很快乐上了综开热榜.
而此次，自己正在持续肝了好几天的情况下，完成了引见Hive言语的详细教程。珍藏本篇文章，也便意味着您具有了一份超等完美的Hive言语册本，讲得十分十分的详细，期望各人能渐渐看，看完以后必定会播种巨多，也能够协助后绝分离Hive停止更下真个操纵。快开教了，本门生最初的假期工夫皆用正在写专客上了，期望各人可以三连撑持一下，先感谢啦！
1、进门需知

1 创立数据库

1.1 创立数据库

  1. create database if not exists csdn_test;
复造代码
其中if not exists能够没有写，但假如曾经存正在了csdn_test那个数据库便会报错。
1.2 查察数据库

  1. show databases;
复造代码
1.3 删除数据库

删除空数据库 drop database 数据库名；
  1. drop database csdn_test;
复造代码
为避免删除的数据库没有存正在发作报错，最好接纳if exists判定数据库能否存正在。
  1. drop database if exists csdn_test;
复造代码
假如数据库没有为空，但又念删除怎样办？正在最初减个cascade。
  1. drop database csdn_test cascade;
复造代码
1.4 进进数据库

  1. use csdn_test;
复造代码
2 Hive数据范例

2.1 数字类

范例少度备注TINYINT1字节有标记整型SMALLINT2字节有标记整型INT4字节有标记整型BIGINT8字节有标记整型FLOAT4字节有标记单粗度浮面数DOUBLE8字节有标记单粗度浮面数DECIMAL–可带小数的准确数字字符串1字节能够存8个0/1。
2.2 日期工夫类

范例少度备注TIMESTAMP–工夫戳，内乱容格局：yyyy-mm-dd hh:mm:ss[.f…]DATE–日期，内乱容格局：YYYY- MM- DDINTERVAL––那里的工夫戳能够了解为离详细指定的某个工夫面相好的工夫（准确到秒）。
2.3 字符串类

范例少度备注STRING–字符串VARCHAR字符数范畴1 - 65535少度没有定字符串CHAR最年夜的字符数：255少度牢固字符串2.4 Misc类

范例少度备注BOOLEAN–布我范例 TRUE/FALSEBINARY–字节序列2.5 复开类

范例少度备注ARRAY–包罗同范例元素的数组，索引从0开端 ARRAYMAP–字典 MAPSTRUCT–构造体 STRUCTUNIONTYPE–连合体UNIONTYPE那些的连合体需求道一下，MAP范例便像Python中的DICT字典范数据，皆是一个枢纽字key对应一个枢纽值value；UNIONTYPE 范例的数据能够寄存任何数据，包罗字符串类、数组型战MAP型等。前面会有例子详细分析。
3 Hive建表

首先需求分析的是，Hive建表的时分能够挑选成立内乱部表大概是内部表。而内乱部表战内部表的区分次要以下。


  • 从数据办理去看，内乱部表次要是由Hive本身办理，而内部表需求由HDFS办理；
  • 从数据存储的地位去看，内乱部表数据是正在/user/hive/warehouse，内部表则是能够经由过程LOCATION枢纽词去指定存储地位，否则默许也是战内乱部表存储地位不异；
  • 从删除表去看，删除内乱部表时能够间接删除元数据及存储数据，而删除内部表仅仅会删除元数据；
  • 从修正表去看，内乱部表的修正会间接同步给元数据，而内部表需求停止建复操纵（MSCK REPAIR TABLE table_name;）
3.1 间接建表法

先给各人列一个建表的语句格局：
  1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  2. [(col_name data_type [COMMENT col_comment], ...)]
  3. [COMMENT table_comment]
  4. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  5. [CLUSTERED BY (col_name, col_name, ...)
  6. [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  7. [ROW FORMAT row_format]
  8. [STORED AS file_format]
  9. [LOCATION hdfs_path]
复造代码
看着有面治？不妨，我们待会会一个个提到。
留意上面语句中假如已存正在不异名字的表，会报错。
  1. create table test_Create_Table(id int,name string);
复造代码
  1. --检察当前库表名
  2. show tables;
  3. --检察建表疑息
  4. show create table test_Create_Table;
  5. --检察表构造
  6. desc test_Create_Table;
复造代码
战创立数据库一样，IF NOT EXIST能够疏忽同名表的非常标题问题。
  1. create table if not exists test_Create_Table(id int,name string);
复造代码
EXTERNAL ，可让用户创立一个内部表，正在建表的同时指定一个指背实践数据的途径（LOCATION）
  1. --没有指定途径，同内乱表途径分歧
  2. create external table test_External_Table(id int,name string);
复造代码
  1. --指定途径创立内部表（请求为新建途径)
  2. create external table test_External_Location(id int, name string) location &#39;/new_dir&#39;;
复造代码
LIKE，能够复造表的构造，但留意并出有复造表中的数据。觉得很简单了解：创立一个像…的表。
  1. create table test_like like test_create_table;
复造代码
COMMENT，便是给表大概字段增加描摹。
  1. create table table_Comment(id int comment &#39;编号&#39;,name string comment &#39;姓名&#39;) comment &#39;员工疑息表&#39;;
复造代码
PARTITIONED BY，用于指定分区，后文会详细解说分区战分桶。
ROW FORMAT，那里是指止数据的格局，后文也会特地解说。
STORED AS，假如文件数据是杂文本，可使用 STORED AS TEXTFILE。假如数据需求紧缩，利用 STORED AS SEQUENCE 。
LOCATION，指定表正在HDFS的存储途径。
CLUSTERED，表示的是根据某列散类，例如正在插进数据中有两项“张三，数教”战“张三，英语”。如果CLUSTERED BY name，则只会有一项，“张三，(数教，英语)”，那个机造也是为了放慢查询的操纵。
3.2 查询建表法

  1. create table NewTableBySelect as select * from test_create_table;
复造代码
需求留意的是，那里select 当选与的列名会做为新表的列名（所以凡是是要与别号），会改动表的属性、构造，好比只能是内乱部表、分辨别桶也出了；别的，select 当选与的列名会做为新表的列名（所以凡是是要与别号），会改动表的属性、构造，好比只能是内乱部表、分辨别桶也出了。同时，目的表没有许可利用内部表，会报错创立的表存储格局会酿成默许的格局 TEXTFILE ，不外能够指定表的存储格局，止战列的分开符等。
3.3 like建表法

那里正在3.1 引见like的时分道过了。
归正大致上建表便是那几种办法，各人要先把握好。
4 分开符

4.1 字段分开符

语法：fields terminated by &#39;\t&#39; （hive 默许的字段分开符为ascii码的掌握符 \001 ctrl + V+A）
  1. --设置字段分开符
  2. create table test_delimit(id int, name string) row format delimited fields terminated by &#39;^B&#39;;
  3. --检察字段分开符
  4. desc formatted test_delimit;
复造代码
我们能够运转看一下。
145212uht88mmmhh8mfkti.jpg

能够看到，那里的分开符疑息呈现了我们设置好的^B。固然，您念设置啥皆能够。
4.2 array 范例成员分开符

语法：collection items terminated by &#39;,&#39;
数组分分开符普通皆是设置为逗号。
我们运转个例子看看结果。
已知"C:\Users\24721\Desktop\"目录下的data1.txt文件内乱容为：
  1. 123|华为Mate10|1235,345
  2. 456|华为Mate30|89,635
  3. 789|小米5|452,63
  4. 1235|小米6|785,36
  5. 4562|OPPO Findx|7875,3563
复造代码
如今我们要做的便是成立一个表，而且把那个文件中的数据一般导进到该表中，详细完成代码以下。
  1. --指定 |为字段分开符 ，为数组分开符
  2. create table sales_info(
  3. sku_id string comment &#39;商品id&#39;,
  4. sku_name string comment &#39;商品称号&#39;,
  5. id_array array<string> comment &#39;商品相干id列表&#39;)
  6. row format delimited fields terminated by &#39;|&#39;
  7. collection items terminated by &#39;,&#39; ;
  9. --拆载当地文件到 sales_info中
  10. load data local inpath "C:\Users\24721\Desktop\data1.txt" overwrite into table sales_info;
  12. --经由过程查询语句检察数据
  13. select * from sales_info;
复造代码
结果以下：
145212oxf3oi2i6ohfchsp.png

4.3 map：Key战Value之间的分开符

语法：map keys terminated by &#39;:&#39;
我们正在举一个例子：已知"C:\Users\24721\Desktop\"下的data2.txt中的内乱容以下：
  1. 123|华为Mate10|id:1111,token:2222,user_name:zhangsan1
  2. 456|华为Mate30|id:1113,token:2224,user_name:zhangsan3
  3. 789|小米5|id:1114,token:2225,user_name:zhangsan4
  4. 1235|小米6|id:1115,token:2226,user_name:zhangsan5
  5. 4562|OPPO Findx|id:1116,token:2227,user_name:zhangsan6
复造代码
如今我们要做的便是成立一个表，而且把那个文件中的数据导进到该表中，详细完成代码以下。
  1. --创立露有map范例的数据表
  2. create table mapkeys(
  3. sku_id string comment &#39;商品id&#39;,
  4. sku_name string comment &#39;商品称号&#39;,
  5. state_map map<string,string> comment &#39;商品形态疑息&#39;)
  6. row format delimited
  7. fields terminated by &#39;|&#39;
  8. map keys terminated by &#39;:&#39;;
  10. --将当地data2.txt数据减载到mapkeys表
  11. load data local inpath "C:\Users\24721\Desktop\data2.txt" overwrite into table mapkeys;
  13. --检察表内乱数据能否取文件分歧
  14. select * from mapkeys;
复造代码
运转结果以下：
145212jesqaaceapseqpes.png

如图，建表插进数据胜利。
但各人认真看的话能够发明，state_map字段中的只要一个key（“id”），剩下齐被看成value。那很较着没有是我们终极念要的。那怎样办，我们能够减上一个collection items terminated by &#39;,&#39;。
详细以下：
  1. --创立露有map范例的数据表
  2. create table mapkeys2(
  3. sku_id string comment &#39;商品id&#39;,
  4. sku_name string comment &#39;商品称号&#39;,
  5. state_map map<string,string> comment &#39;商品形态疑息&#39;)
  6. row format delimited
  7. fields terminated by &#39;|&#39;
  8. collection items terminated by &#39;,&#39;
  9. map keys terminated by &#39;:&#39;;
  11. --将当地data2.txt数据减载到mapkeys表
  12. load data local inpath "C:\Users\24721\Desktop\data2.txt" overwrite into table mapkeys2;
  14. --检察表内乱数据能否取文件分歧
  15. select * from mapkeys2;
复造代码
145213vr29va31r2ngnk92.png

此时的结果才是我们念要的。
4.4 止分开符

语法：lines terminated by &#39;\n&#39;。
建表时得放正在最初，不外那个建表时普通出有人写上来，由于今朝默许"\n"同时也只撑持"\n"。
4.5 利用多字符做为分开符



  • 利用MultiDelimitSerDe的办法去完成
  1. --创立多分开符表
  2. CREATE TABLE test_MultiDelimit(id int, name string ,tel string)
  3. ROW FORMAT SERDE &#39;org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe&#39; WITH SERDEPROPERTIES ("field.delim"="##")
  4. STORED AS TEXTFILE;
  6. --检察表
  7. desc formatted test_MultiDelimit;
复造代码
运转结果以下：
145213xqog8uqltz36qqq1.jpg



  • 利用RegexSerDe的办法完成，但留意RegexSerDe仅撑持字符串范例的，不克不及有其他范例。
  1. CREATE TABLE test1(id string, name string ,tel string)
  2. ROW FORMAT SERDE &#39;org.apache.hadoop.hive.contrib.serde2.RegexSerDe&#39;
  3. WITH SERDEPROPERTIES ("input.regex" = "^(.*)\\#\\#(.*)$")
  4. STORED AS TEXTFILE;
复造代码
5 分区表创立

那部门是我正在上一篇专客中的Hive数据模子便提到了的，能够道着部门是Hive较为特征的部门。
5.1 利用分区表的意义

利用分区表的意义大致以下：利用分区手艺，避免hive齐表扫描，汲引查询服从；同时可以削减数据冗余进而前进特定(指定分区)查询阐发的服从。
留意，正在逻辑上分区表取已分区表出有区分，正在物理上分区表会将数据根据分区键的列值存储正在表目录的子目录中，目录名为“分区键=键值”。您能够把成立分区设想成我建了个文件夹，把一些类似（大概道您念要的范例）的数据寄存到文件夹中。
分区表那么好用，所以查询时只管操纵分区字段。假如没有利用分区字段，便会局部扫描。
5.2 分区表范例

分区表范例：静态分区战静态分区。区分正在于前者是我们脚动指定的，后者是经由过程数据去判定分区的。
5.3 成立分区

静态分区战静态分区的建表语句是一样的。
留意：PARTITIONED BY ()括号中指定的分区名不克不及跟表中的字段名一样。
  1. -- 创立分区表 PARTITIONED BY (分区字段名 分区字段范例)
  2. create table test_partition1(
  3. sku_id string comment &#39;商品id&#39;,
  4. sku_name string comment &#39;商品称号&#39;)
  5. PARTITIONED BY (sku_class string);
  7. --成立分区表以后，此时出无数据，也出有分区，需求成立分区
  8. --创立分区
  9. alter table test_partition1 add partition(sku_class=&#39;xiaomi&#39;) ;
  11. --检察表示有分区
  12. show partitions test_partition1;
复造代码
145213j1uinn55f7c5gu59.png

此时分区test_partition1和建好。
固然，我们能够经由过程多字段分区，详细以下。
  1. -- 创立分区表 PARTITIONED BY (分区字段名 分区字段范例,分区字段名2 分区字段范例2) 多字段分区
  2. create table test_partition_mul(
  3. sku_id string comment &#39;商品id&#39;,
  4. sku_name string comment &#39;商品称号&#39;)
  5. PARTITIONED BY (sku_class string,sku_lable string);
  7. --增加分区
  8. alter table test_partition_mul add IF NOT EXISTS
  9. partition(sku_class=&#39;xiaomi&#39;,sku_lable=&#39;dianzi&#39;);
  11. --检察现有分区
  12. show partitions test_partition_mul;
复造代码
145213rhrkeylqseyzznky.png

此时分区test_partition_mul和建好。
往静态分区增加数据的语句以下：
  1. insert into table test_partition_mul
  2. partition(sku_class=&#39;xiaomi&#39;,sku_lable=&#39;dianzi&#39;) values(&#39;001&#39;,&#39;xiaomi1&#39;);
  4. insert into table test_partition_mul
  5. partition(sku_class=&#39;xiaomi&#39;,sku_lable=&#39;dianzi&#39;) select sku_id,sku_name from sales_info;
复造代码
不外利用insert插进数据的速率常常会偏偏缓，各人能够接纳load data的办法减载当地文件到分区表中。
  1. load data local inpath &#39;当地文件途径&#39; into table test_partition partition (sku_class=&#39;xiaomi&#39;,sku_lable=&#39;dianzi&#39;);
复造代码
静态分区插进数据的办法以下：
  1. insert into table test_partition_mul partition(sku_class,sku_lable) values (&#39;001&#39;,&#39;xiaomi2&#39;,&#39;xiaomi&#39;,&#39;dianzi&#39;);
复造代码
5.4 删除分区 drop

语法：alter table 表名 drop partition(分区字段名=与值);
  1. alter table test_partition1 drop partition(sku_class=&#39;xiaomi&#39;);
  3. --检察分区：
  4. show partitions test_partition1;
复造代码
145214fzgqueifg6uxuxoo.png

此时分析删除胜利。
6 分桶表创立

为何要有分桶手艺？分桶是啥意义，有啥感化？分区战分桶的区分有哪些？
那便给您逐个解问。

  • 当单个分区大概表中的数据量愈来愈年夜的时分，分区不克不及更细粒天别离数据时，能够接纳分桶手艺停止更细粒度的别离战办理；
  • 分桶的本质实在便是对分桶的字段做了hash，然后寄存到对应的文件中；
  • 分桶能够前进join查询服从，便利停止抽样；
至于分桶战分区的区分呢，次要有一下几个处所：

  • 分区利用的是表中字段，而分桶利用的是表内乱字段（也便是道分区时分区名不克不及与表字段名，而分桶是得指定表字段名吗）；
  • 分桶是更细粒度的别离、办理数据，更多用去做数据抽样、JOIN操纵；
  • 分桶随机朋分数据库，而分区长短随机朋分数据库；
  • 分桶是对应差别的文件（细粒度），而分区是对应差别的文件夹（细粒度）；
  • 一般表（内部表、内乱部表）、分区表那三个皆是对应HDFS上的目录，同表对应的是目录里的文件。
假如到那出听懂，不妨，接着往下看。
语法：create table 表名(字段1 范例1，字段2，范例2 ) clustered by(表内乱字段) sorted by(表内乱字段) into 分桶数 buckets
  1. --创立分桶表
  2. create table test_buckets(
  3. sku_id string comment &#39;商品id&#39;,
  4. sku_name string comment &#39;商品称号&#39;)
  5. clustered by(sku_id) into 3 buckets;
复造代码
但记得先设置主动分桶开闭：
  1. set hive.enforce.bucketing=true;
复造代码
增加数据到分桶表：
  1. insert into test_buckets select sku_id,sku_name from sales_info;
复造代码
7 非hive情况下施行hql

固然内乱容很简朴，可是仍是得引见一下。
怎样施行一堆号令，能够将hql语句放到一个文件中，接纳hive -f "文件途径"便可，留意那个是正在非hive情况下运转。
而hive -e "hql语句"则是间接施行hive语句。
2、Hive操纵言语

1 减载数据

1.1 从当地拆载数据

那个实在正在前里也曾经提到了。
一般表：load data local inpath &#39;数据文件途径&#39; [overwrite] into table 表名 ;
其中overwrite表示笼盖本无数据，出有的话则表示增加数据。
  1. load data local inpath &#39;E:hadoop/datas/payments.txt&#39; into table payments;
复造代码
分区表：load data local inpath &#39;数据文件途径&#39; [overwrite] into table 表名 partition (分区字段=值);
  1. load data local inpath &#39;E:hadoop/datas/product_category_level1.txt&#39; into table product_category partition(level=1);
复造代码
分桶表：load data local inpath &#39;数据文件途径&#39; [overwrite] into table 表名;
  1. --开启分桶功用
  2. set hive.enforce.bucketing=true;
  4. -- 疏忽失落宁静查抄
  5. hive.strict.checks.bucketing=false;
  7. load data local inpath &#39;E:hadoop/datas/shop.txt&#39; into table shops;
复造代码
1.2 从HDFS减载数据


  • cmd上传到HDFS文件体系中。
    语法：hdfs dfs -put 当地文件途径 hdfs途径
  1. hdfs dfs -put "C:\Users\24721\Desktop\product_info.txt" "/datas"
复造代码

  • 文件数据减载到Hive表中。
    语法：load data inpath &#39;hdfs数据文件途径&#39; into table 表名;
  1. load data inpath &#39;/datas/product_info.txt&#39; into table product_info;
复造代码
同理分区表要指定分区，分桶表取一般表减载数据一样。
2 插进数据

2.1 一般表

语法：insert into 表名 values(值)大概insert overwrite 表名 values(值)。
2.2 分区表

静态分区：
  1. insert into test_partition1 partition(sku_class="xiaomi") values(1,&#39;sku_new&#39;);
复造代码
静态分区：
  1. insert into test_partition1 partition(sku_class) values(1,&#39;sku_new&#39;,&#39;苹果&#39;);
复造代码
2.3 分桶表

语法：insert into 分桶表表名
  1. insert into test_buckets values(1,&#39;sku_new&#39;);
复造代码
3 导出数据

3.1 导出到当地文件体系

语法：INSERT OVERWRITE LOCAL DIRECTORY &#39;文件夹途径&#39; ROW FORMAT DELIMITED FIELDS TERMINATED by &#39;字段分开符&#39; 查询语句;
PS: 会重写指定文件夹 （必然要警觉笼盖失落有效的文件），有新建文件夹功用， 导出的文件皆名皆为000000_0。别的，默许分开符是用体系指定的，战本人建表语句指定的出有干系。
  1. INSERT OVERWRITE LOCAL DIRECTORY &#39;E:/hadoop/datas/test_output&#39; ROW FORMAT DELIMITED FIELDS TERMINATED by &#39;\t&#39; select * from sales_info;
复造代码
3.2 导出到HDFS

语法：INSERT OVERWRITE DIRECTORY &#39;文件夹途径&#39; 查询语句;
取导出到当地文件体系比拟，少了个local。
4 删除表

4.1 删除局部数据

语法：truncate table 表名
利用truncate仅可删除内乱部表数据，不成删除表构造。
（便相等于您有个屋子，如今只是把内里的工具皆搬走，可是屋子的架构啥的皆出变）。
  1. truncate table byselect;
复造代码
利用cmd删除内部表数据(hdfs dfs -rm -r 内部表途径):
  1. hdfs dfs -rm -r /datas/test_Exteranl/*
复造代码
4.2 删除表部门数据

有partition表

  • 删除详细partition
    语法：alter table table_name drop partition(partiton_name=&#39;value&#39;))
  1. --检察数据
  2. select * from test_partition1;
  3. show partitions test_partition1;
  5. --删除指定分区
  6. alter table test_partition1 drop partition(sku_class = &#39;xiaomi&#39;);
复造代码

  • 删除partition内乱的部门疑息(INSERT OVERWRITE TABLE）
  1. INSERT OVERWRITE TABLE test_partition_mul
  2. PARTITION(sku_class=&#39;xiaomi&#39;,sku_lable=&#39;dianzi&#39;)
  3. SELECT sku_id,sku_name FROM test_partition_mul
  4. WHERE sku_id=&#39;1235&#39;;
复造代码
从头把对应的partition疑息写一遍，经由过程WHERE 去限制需求留下的疑息，出有留下的疑息便被删除。
无partiton表
语法：INSERT OVERWRITE TABLE 表名 SELECT * FROM 表名 WHERE 前提;。
  1. --插进测试数据
  2. insert into sales_info(sku_id,sku_name) values(1,&#39;a&#39;);
  4. --删除没有为2的数据
  5. insert overwrite table sales_info(sku_id,sku_name) select sku_id,sku_name from sales_info where sku_id=&#39;2&#39;;
复造代码
删除全部表
利用drop可删除全部表（drop table 表名）
  1. drop table test_external;
复造代码
3、Hive查询言语

1 内乱置运算符

1.1 干系运算符

运算符操纵描摹A = B局部根本范例假如表达A即是表达B，结果TRUE ，不然FALSE。A != B局部根本范例假如A没有即是表达式B表达返回TRUE ，不然FALSE。A < B局部根本范例假如表达式A小于表达式B为TRUE，不然FALSE。A  B局部根本范例假如表达式A年夜于表达式B为TRUE，不然FALSE。A >= B局部根本范例假如表达式A年夜于或即是表达式B为TRUE，不然FALSE。A [NOT] BETWEEN B AND C根本数据范例假如A，B大概C任一为NULL，则结果为NULL。假如A的值年夜于即是B并且小于或即是C，则结果为TRUE，反之为FALSE。假如利用NOT枢纽字则可到达相反的结果。A IS [NOT] NULL局部范例假如A即是NULL，则返回TRUE，反之返回FALSE, NOT 恰好相反。A IN(数值1, 数值2)局部范例假如A存正在指定的数据中，则返回TRUE，反之返回FALSE。A [NOT] LIKE B字符串假如A取B婚配的话，则返回TRUE；反之返回FALSE。%代表随便多个字符，_代表一个字符 % _。A RLIKE B字符串假如A或B为NULL；假如A任何子字符串婚配Java正则表达式B；不然FALSE。A REGEXP B字符串同等于RLIKE。1.2 算术运算符

运算符操纵描摹A + B局部数字范例A减B的结果A - B局部数字范例A加来B的结果A / B局部数字范例A除以B的结果A % B局部数字范例A除以B.发生的余数1.3 逻辑运算符

运算符操纵描摹A AND Bboolean假如A战B皆是TRUE，不然FALSE。A && Bboolean相似于 A AND B.A OR BbooleanTRUE，假如A或B或二者皆是TRUE，不然FALSE。A || Bboolean相似于 A OR B.NOT AbooleanTRUE，假如A是FALSE，不然FALSE。!Aboolean相似于 NOT A.1.4 庞大的运算符

运算符操纵描摹A[n]A是一个数组，n是一个int它返回数组A的第n个元素，第一个元素的索引0。M[key]M 是一个 Map key 的范例为K它返回对应于映照中枢纽字的值。S.xS 是一个构造它返回S的s字段2 内乱置函数

2.1 数教函数

返回范例署名描摹BIGINT DOUBLEround(double a) round(double a, int d)返回double范例的整数值部门 （遵照四舍五进） 返回指定粗度d的double范例BIGINTfloor(double a)返回即是大概小于该double变量的最年夜的整数BIGINTceil(double a)返回即是大概年夜于该double变量的最小的整数DOUBLErand() rand(int seed)返回一个0到1范畴内乱的随机数。假如指定种子seed，则会比及一个不变的随机数序列.DOUBLEpow(double a, double p)返回a的p次幂DOUBLEsqrt(double a)返回a的仄圆根DOUBLE INTabs(double a) abs(int a)返回数值a的尽对值2.2 日期函数

返回范例署名描摹STRINGfrom_unixtime(bigint unixtime[, string format])转化UNIX工夫戳（从1970-01-01 00:00:00 UTC到指按时间的秒数） 到当前时区的工夫格局BIGINTunix_timestamp() unix_timestamp(string date) unix_timestamp(string date, string pattern)得到当前时区的UNIX工夫戳 转换格局为"yyyy-MM-dd HH:mm:ss"的日期到UNIX工夫戳。假如转化失利，则返回0 转换pattern格局的日期到UNIX工夫戳。假如转化失利，则返回0STRINGto_date(string timestamp)返回日期工夫字段中的日期部门INTyear(string date) month (string date) day (string date)别离返回日期中的年 月 天INThour (string date) minute (string date) second (string date)别离返回日期中的时 分 秒INTweekofyear (string date)返回日期正在昔时的第几周INTdatediff(string enddate, string startdate)返回完毕日期加来开端日期的天数 日期有格局请求 yyyy-mm-dd hh:MM:ss 或 yyyy-mm-ddSTRINGdate_add(string startdate, int days)返回开端日期startdate增长days天后的日期 add_months(string startdate, int months)STRINGdate_sub (string startdate, int days)返回开端日期startdate削减days天后的日期2.3 前提判定函数

返回范例署名描摹Tif(boolean testCondition, T valueTrue, T valueFalseOrNull)当前提testCondition为TRUE时，返回valueTrue；不然返回valueFalseOrNullTcoalesce(T v1, T v2, …)返回参数中的第一个非空值；假如局部值皆为NULL，那末返回NULLTCASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END假如a即是b，那末返回c；假如a即是d，那末返回e；不然返回fTCASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END假如a为TRUE,则返回b；假如c为TRUE，则返回 d；不然返回e2.4 字符串函数

返回范例署名描摹INTlength(string A)返回字符串A的少度STRINGreverse(string A)返回字符串A的反转结果STRINGconcat(string A, string B…)返回输进字符勾通接后的结果，撑持随便个输进字符串STRINGconcat_ws(string SEP, string A, string B…)返回输进字符勾通接后的结果，SEP表示各个字符串间的分开符STRINGsubstr(string A, int start),substring(string A, int start) substr(string A, int start, int len),substring(string A, int start, int len)返回字符串A从start地位到末端的字符串 返回字符串A从start地位开端，少度为len的字符串STRINGupper(string A) ucase(string A)返回字符串A的年夜写格局STRINGlower(string A) lcase(string A)返回字符串A的小写格局STRINGtrim(string A) ltrim(string A) rtrim(string A)来除字符串双方的空格 除字符串右边的空格 来除字符串右侧的空格STRINGregexp_replace(string A, string B, string C)将字符串A中的契合java正则表达式B的部门改换为CSTRINGregexp_extract(string subject, string pattern, int index)将字符串subject根据pattern正则表达式的划定规矩拆分，返回index指定的字符STRINGparse_url(string urlString, string partToExtract [, string keyToExtract])返回URL中指定的部门。partToExtract的有用值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.STRINGget_json_object(string json_string, string path)剖析json的字符串json_string,返回path指定的内乱容。假如输进的json字符串无效，那末返回NULLSTRINGspace(int n)返回少度为n的空格字符串STRINGrepeat(string str, int n)返回反复n次后的str字符串STRINGlpad(string str, int len, string pad) rpad(string str, int len, string pad)将str停止用pad停止左补足到len位 将str 停止用pad停止左补足到len位ARRAYsplit(string str, string pat)根据pat字符串朋分str，会返回朋分后的字符串数组INTfind_in_set(string str, string strList) find_in_set(‘ab’,‘aa,ab,ac’)返回str正在strlist第一次呈现的地位，strlist是用逗号朋分的字符串。假如出有找该str字符，则返回0INTinstr(string str, string substr) instr(“abcde”,“ab”)返回substr正在str中第一次呈现的地位，已呈现则返回0（假如参数为NULL则返回NULL；地位从1开端）2.5 统计函数

返回范例署名描摹INTcount(*), count(expr), count(DISTINCT expr[, expr_.])count(*)统计检索出的止的个数，包罗NULL值的止；count(expr)返回指定字段的非空值的个数；count(DISTINCTexpr[, expr_.])返回指定字段的差别的非空值的个数DOUBLEsum(col), sum(DISTINCT col)sum(col)统计结果集合col的相减的结果；sum(DISTINCT col)统计结果中col差别值相减的结果DOUBLEavg(col), avg(DISTINCT col)avg(col)统计结果集合col的平均值；avg(DISTINCT col)统计结果中col差别值相减的平均值DOUBLEmin(col) max(col)统计结果集合col字段的最小值 统计结果集合col字段的最年夜值DOUBLEvar_pop(col) var_samp (col)统计结果集合col非空汇合的整体圆好 统计结果集合col非空汇合的样本变量DOUBLEstddev_pop(col) stddev_samp (col)统计结果集合col非空汇合的整体尺度好 统计结果集合col非空汇合的样本尺度好DOUBLEpercentile(BIGINT col, p)供精确的第p个百分位数，p必需介于0战1之间，可是col字段今朝只撑持整数，没有撑持浮面数范例ARRAYpercentile(BIGINT col, array(p1 [, p2]…))功用战上述相似，以后前面能够输进多个百分位数，返回范例也为array，其中为对应的百分位数2.6 复开范例构建会见函数

返回范例署名描摹MAPmap (key1, value1, key2, value2, …)按照输进的key战value对构建map范例STRUCTstruct(val1, val2, val3, …)按照输进的参数构建构造体struct范例ARRAYarray(val1, val2, …)按照输进的参数构建数组array范例…A[n]返回数组A中的第n个变量值。数组的肇端下标为0。…M[key]返回map范例M中，key值为指定值的value值…S.x返回构造体S中的x字段INTsize(Map) size(Array)返回map范例的少度 返回array范例的少度…explode（maparray)Arraycollect_set ( col)对col止变列并 来重，Arraycollect_list ( col)对col止变列并 没有来重，Arraymap_keys(map)与map范例的局部KeyArraymap_values(map)与map范例的局部valueTarray_contains(array,obj)判定指定的obj 能否村正在数组中3 Select 语句构造

语法以下：
  1. SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  2. FROM table_reference
  3. [WHERE where_condition]
  4. [GROUP BY col_list]
  5. [HAVING having_condition]
  6. [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]][ORDER BY col_list]
  7. [LIMIT number];
复造代码
hive语句的施行挨次：
from -->where --> select --> group by -->散开函数–> having --> order by -->limit


  • 齐表查询 （select * from 表名）
  1. select * from sales_info;
复造代码


  • 挑选特定列查询(select col1,col2,… from 表名 )
  1. select sku_id from sales_info;
复造代码


  • 列重定名（select col1 [as] ,… from 表名）
  1. select sku_id as id from sales_info;
复造代码


  • 返回数组的第一个元素(索引从0开端)
  1. select id_array[0] from sales_info;
复造代码


  • 查察数组中的每一个元素
  1. select explode(id_array) from sales_info;
复造代码


  • 数组各元素分止显现，并要有取只对应的sku_id,sku_name
  1. select sku_id,sku_name, id_list from sales_info lateral view explode(id_array) ids as id_list;
  2. --lateral view explode(数组字段) 假造表名 as 假造表字段
复造代码


  • 把test_hv 表 以sku_id 分组 id_list 转一列显现
  1. select sku_id,collect_set(id_list) from test_HV group by sku_id; --set 汇合来重
  2. select sku_id,collect_list(id_list) from test_HV group by sku_id;--list 列表没有来重
复造代码


  • 数组转取字符串交换
  1. select *， split(concat_ws(&#39;,&#39;,id_array),&#39;,&#39;) from sales_info;
复造代码


  • 返回Map中key 为 id 的值
  1. select state_map[&#39;id&#39;] from mapkeys1
复造代码


  • 把map中Key ，value 分两列显现
  1. select explode(state_map) from mapkeys1
复造代码


  • map各元素分止显现，并要有取只对应的sku_id,sku_name
  1. select sku_id,sku_name, infokey,infovalue from mapkeys1 lateral view explode(state_map) infos as infokey,infovalue;
复造代码
– 查察局部map中的value
  1. select map_values(state_map) from mapkeys1;
复造代码


  • map中没有存正在指定Key 时 显现 “无”
  1. select state_map[&#39;id&#39;],if( state_map[&#39;user&#39;] is null , &#39;无&#39;,state_map[&#39;user&#39;]) from mapkeys1;
复造代码


  • 返回构造体中为age元素值
  1. select basic_info.age from test_student;
复造代码
3.1 Java正则

指代内乱容的标记：


  • \w 指代字符（下划线，数字 ，字母）， \W指代非字符（也便是战\w反过去）
  • \d 指代数字（0-9）， \D指代非数字
  • . 指代随便字符
  • \s 指代各类空缺， \S同上。
  • [内乱容] 包罗指定内乱容，如 [A-Za-z]
  • [^内乱容] 没有包罗指定的内乱容，如 [^\d]指除数字之外的任何字符，那里同等于\D。
指代个数的标记：
标记代表个数*0-n+1-n?0-1{n}n{n,}最少n{，n}最多n{n,m}n-m举例弥补：
标记含义^字符开首$字符末端\d{11}11个数字[a-zA-Z0-9]{8,}有最少8个字母大概数字（并且是持续的）^A.*以A开首的任何字符.*8$以8末端的任何字符[A], AA,A3.2 Select——Where

利用WHERE 子句，将没有满意前提的止过滤失落。where 后是 干系运算 战 逻辑运算的差别组开。
– Sale_info表中Sku_Name 包罗 字母A的
  1. select * from sales_info where sku_name like &#39;%A%&#39;;
复造代码
–Sale_info表中 id_array 有id露有8的
  1. select * from sales_info where concat_ws("",id_array) like &#39;%8%&#39;;
  2. select * from sales_info where concat_ws("",id_array) RLIKE &#39;[8]&#39;;
  3. select * from sales_info where concat_ws("",id_array) Regexp &#39;8&#39;;
复造代码
–mapkeys1 表中 state_map 中key 露有id的
  1. select * from mapkeys1 where array_contains(map_keys(state_map),"id");
复造代码
–mapkeys1 表中 state_map user_name 中包罗zhang的
  1. select * from mapkeys1 where state_map["user_name"] like "%zhang%";
复造代码
–mapkeys1 表中姓张的而且名字只要两个字的
  1. select * from mapkeys1 where state_map["user_name"] like "张_";
  2. select * from mapkeys1 where state_map["user_name"] Rlike "^张.";&#39;^张.&#39;
  3. select * from mapkeys1 where state_map["user_name"] Rlike "^张.{1}";
复造代码
–mapkeys1 表中 state_map字段的 values 包罗 A 的（没有辨别巨细写）
  1. select * from mapkeys1 where upper(concat_ws(&#39;&#39;,map_values(state_map))) like &#39;%A%&#39;;
复造代码
3.3 Select——Group By & 散开函数

having取where差别面
wherehavingwhere针对表中的列阐扬感化，查询数据having针对查询结果中的列阐扬感化，挑选数据不克不及写分组函数可使用分组函数没有受限定只用于group by分组统计语句–统计test_partition1 表中各 sku_class 的个数
  1. select sku_class,count(*) from test_partition1 group by sku_class;
复造代码
–统计test_student 表basic_info 各age的人数 并显现年夜于1 的年齿组
  1. select basic_info.age, count(*) renshu from test_student
  2. group by basic_info.age
  3. having renshu > 1;
复造代码
–mapkeys1 以map列中指定key 对应的值停止分组 并统计记载条数
  1. select state_map["id"],sum(sku_id),count(1) from mapkeys1 group by state_map["id"];
复造代码
–统计test_partition1 表中各分区记载而且找到年夜于1的分区
  1. select sku_class,count(*) from test_partition1
  2. group by sku_class
  3. having count(*) > 1;
复造代码
3.4 Select——Order By

 order bysort bydistribute bycluster by感化order by会对输进做齐局排序sort by 是零丁正在各自的reduce中停止排序掌握map 中的输出正在 reduce中是怎样停止别离的缺陷只要一个Reduce，当输进范围较年夜时，耗损较少的计较工夫不克不及包管齐局有序只是分，出有排序只能做降序

  • order by (齐局排序asc ,desc)
–单列排序
  1. select * from sales_info order by sku_id;
复造代码
–多列排序
  1. select * from order_data1 order by quantity desc , sales desc limit 20
  3. --limit 能够停止区间与数的 语法 limit n ,m 成果： 从n+1 开端，与m笔记录
复造代码
–别号排序
  1. select order_id ,sales as jine from order_data1 order by jine limit 10;
复造代码
–默许的降序，默许是的NULLS FIRST
  1. select * from sales_info order by sku_id nulls last;
复造代码
4 表联系关系

那里便是主要常识面啦，熟习MySQL的朋友该当会明白不论是一样平常利用仍是口试发问城市常常呈现表联系关系的相干常识，所以请各人必然当真浏览。
我们先道一下Hive join的限定。实在很较着的一面是，Hive撑持相似 mysql 的年夜部门Join 操纵，可是留意只撑持等值毗连，其实不撑持没有等毗连。缘故原由是Hive语句终极是要转换为MapReduce 法式去施行的，可是 MapReduce法式很易完成这类没有等判定的毗连方法。
别的，正在on前面的表达式中，是没有撑持or的。
好，接着进进重面。
假如数据存储正在多个表中，如何用单条SELECT语句检索出数据？
谜底是利用联合。简朴天道，联合是一种机造，用去正在一条SELECT语句中联系关系表，因而称之为联合。利用特别的语法，能够联合多个表返回一组输出，联合正在运转时联系关系表中准确的止。
4.1 内乱部链接

  1. SELECT vend_name, prod_name, prod_price
  2. FROM vendors INNER JOIN products
  3.   ON vendors.vend_id = products.vend_id;
复造代码
4.2 联合多个表

  1. SELECT prod_name, vend_name, prod_price, quantity
  2. FROM orderitems, products, vendors
  3. WHERE products.vend_id = vendors.vend_id
  4.   AND orderitems.prod_id = products.prod_id
  5.   AND order_num = 123;
复造代码
此种联系关系处理是相等耗费资本的。
4.3 创立初级联合

经由过程AS枢纽字利用别号，两面益处：膨胀SQL语句；许可正在单浮薄SELECT语句中屡次利用不异的表。
  1. SELECT p1.prod_id, p1.prod_name
  2. FROM products AS p1, products AS p2
  3. WHERE p1.vend_id = p2.vend_id
  4.   AND p2.prod_id = &#39;DTNTR&#39;
复造代码
如上代码中利用两个不异的表别离做为p1, p2，WHERE当选与p2中产品ID为DTNTR的列，此时p2.vend_id便是DTNTR的消费商；p1.vend_id = p2.vend_id 此时意味正在p1拔取DTNTR的消费商。
4.4 内部联合 OUTER JOIN

那个实在也是考面。
SQL中的联系关系/查询的方法一共有4种，别离是INNER JOIN（内乱毗连，INNER能够省略）、LEFT OUTER JOIN（左毗连，OUTER能够省略）RIGHT OUTER JOIN（左毗连，OUTER能够省略）和FULL JOIN（齐毗连）。
毗连方法含义INNER JOIN只保留两张表中完整婚配的结果散。LEFT JOIN返回左表局部的止，而左表中出有婚配的记载则会表示为null。RIGHT JOIN返回左表局部的止，而左表中出有婚配的记载则会表示为null。FULL JOIN正在两张表停止毗连查询时，返回左表战左表中局部出有婚配的止。值得留意的是，MySQL中是没有撑持齐毗连的，不外hive是撑持的。
  1. SELECT id, name  FROM t1
  2. full JOIN t2 ON t1.id = t2.id
  4. -- 来失落反复值
  5. SELECT distinct id, name  FROM t1
  6. full JOIN t2 ON t1.id = t2.id
复造代码
4.5 组开查询 UNION

UNION对两个结果散停止并散操纵，没有包罗反复止，同时停止默许划定规矩的排序。
假如道上述提到的各类毗连方法归纳综合为一种情势的话，那便是表取表之间阁下毗连，而UNION则是高低毗连。
UNION的利用很简朴。所需做的只是给出每条SELECT语句，正在各条语句之间放上枢纽字UNION。
  1. select distinct customer_id from test_join_order
  2. union
  3. select distinct customer_id from user_info;
  5. --disctinct 利用取可其实不影响终极成果， union 有来重功用
  6. select customer_id from test_join_order
  7. union
  8. select customer_id from user_info;
复造代码
UNION战UNION ALL的区分
简朴来说，便是利用UNION会删除反复的记载，而UNION ALL则没有会;UNION会停止排序，而UNION ALL 没有会；从服从上看，UNION ALL施行服从下于UNION，假如出有来重的需供，便选用UNION ALL。
4.6 与交散 Intersect

Intersect 对两个结果散停止交散操纵，没有包罗反复止，同时停止默许划定规矩的排序。
  1. select customer_id from test_join_order
  2. Intersect
  3. select customer_id from user_info;
复造代码
4.7 供好散 Minus

Minus 对两个结果散停止好操纵(第一个加来第两个），没有包罗反复止，同时停止默许划定规矩的排序。
  1. select customer_id from test_join_order Minus select customer_id from user_info
复造代码
5 利用视图



  • 视图是一个实表，一个逻辑观点，能够逾越多张表。表是物理观点，数据放正在表中，视图是实表，操纵视图战操纵表是一样的，所谓实，是指视图下其实不存与数据。
  • 视图是成立正在已有表的根底上，视图好以成立的那些表称为基表。
  • 视图也能够成立再已有的视图上。
  • 视图能够简化庞大的查询。
5.1 创立视图

语法：
  1. CREATE VIEW [IF NOT EXISTS] [db_name.]view_name -- 视图称号
  2.         [(column_name [COMMENT column_comment], ...) ] --列名
  3.         [COMMENT view_comment] --视图正文
  4.         [TBLPROPERTIES (property_name = property_value, ...)] --分外疑息
  5.         AS SELECT ...;
复造代码
–没有指定列创立视图
  1. create view test_view as
  2. select a.order_id, a.order_date,b.customer_id,b.age,b.sex from join_order as a
  3. inner join user_info as b on a.customer_id = b.customer_id
  4. where year=2018 and month=1;
复造代码
–指定列名
  1. create view test_view_liename(id,order_date,customer_id,age,sex) as
  2. select a.order_id, a.order_date,b.customer_id,b.age,b.sex from join_order as a
  3. inner join user_info as b on a.customer_id = b.customer_id
  4. where year=2018 and month=1;
复造代码
–以视图创立视图
  1. create view test_fu_view as
  2. select * from test_view_liename;
复造代码
5.2 查察视图

–查察视图能否创立胜利
  1. show tables;
复造代码
–查察test_view视图构造
  1. desc test_view;
复造代码
–查察视图的详细疑息
  1. desc formatted test_view;
复造代码
5.3 删除视图

语法：DROP VIEW [IF EXISTS] [db_name.]view_name;
–删除test_view 视图
  1. drop view test_view;
复造代码
删除视图时，假如被删除的视图被其他视图所援用，删除时没有会收回正告，可是援用该视图的其他视图曾经生效，需求停止重修大概删除。
5.4 修正视图

语法：ALTER VIEW [db_name.]view_name AS new_select;
其中：


  • 正在修正指定列的视图后，指定的列名生效。
  • 当基表的数据记载删删时，视图也会死变革。
  • 当基表删除视图援用的列后，视图会生效。
  • 当基表增加列后，视图仍是原本的列，对新列没有做援用。
  • 当基表或基视图被删除后，此视图生效。
  1. alter view test_fu_view as
  2. select a.order_id,b.customer_id from join_order as a
  3. inner join user_info as b on a.customer_id = b.customer_id
  4. where year=2018 and month=1;
复造代码
5.5 视图总结


  • 视图是只读的，不克不及用做 LOAD / INSERT / ALTER 的目的；
  • 正在创立视图时分视图便曾经牢固，对基表的增长列操纵将没有会反应正在视图，删除视图援用的列，图会生效；
  • 删除基表其实不会删除视图，需求脚动删除视图；
  • 视图能够包罗 ORDER BY 战 LIMIT 子句。假如援用视图的查询语句也包罗那类子句，其施行劣先级低于视图对应字句
  • 创立视图时，假如已供给列名，则将从 SELECT 语句中主动派死列名；
  • 创立视图时，假如 SELECT 语句中包罗其他表达式，例如 x + y，则列称号将以C0，C1 等情势生成。
6 窗心函数

那个很较着，又是一个重头戏。也是一样平常使用战口试发问常常会呈现的常识面，实期望各人看过我之前写过的那篇专客，虽然说MySQL战Hive有面面区分，但只需您了解了MySQL，那借担忧没有懂Hive？
语法：阐发函数(如：sum(), max(), row_number()...) + 窗口儿句(over函数)。
6.1 窗口儿句

那里先讲下一些窗口儿句，前面去详讲窗心函数中的初级函数。
窗口儿句备注PRECEDING往前 n preceding 从当前止背前n止FOLLOWING此后 n following 从当前止背后n止CURRENT ROW当前止UNBOUNDED出发点UNBOUNDED PRECEDING表示该窗心最前里的止（出发点）UNBOUNDED FOLLOWING表示该窗心最初里的止（尽头）举例如下：阐发overData表中每三天（前一天，当前天，后一天）的销售额开计（保留两位小数面）
  1. select order_date,sales,quantity, round(sum(sales) over(order by order_date rows
  2. between 1 preceding and 1 FOLLOWING ),2) sumday3 from overData;
复造代码
接下去，我也是间接把我那篇专客中窗心函数的部门办了过去，照用哈哈。


免责声明：假如进犯了您的权益，请联络站少，我们会实时删除侵权内乱容，感谢协作！
1、本网站属于个人的非赢利性网站，转载的文章遵循原作者的版权声明，如果原文没有版权声明，按照目前互联网开放的原则，我们将在不通知作者的情况下，转载文章;如果原文明确注明“禁止转载”，我们一定不会转载。如果我们转载的文章不符合作者的版权声明或者作者不想让我们转载您的文章的话，请发帖留言提供原创证明，我们将积极配合您！
2、本网站转载文章仅为传播更多信息之目的，凡在本网站出现的信息，均仅供参考。本网站将尽力确保所提供信息的准确性及可靠性，但不保证信息的正确性和完整性，且不对因信息的不正确或遗漏导致的任何损失或损害承担责任。
3、任何透过本网站网页而链接及得到的资讯、产品及服务，本网站概不负责，亦不负任何法律责任。
4、本网站所刊发、转载的文章，其版权均归原作者所有，如其他媒体、网站或个人从本网下载使用，请在转载有关文章时务必尊重该文章的著作权，保留本网注明的“稿件来源”，并自负版权等法律责任。
回复

使用道具 举报

玩网女帝*ψ▔ LV2
发布了 9 篇主题 · 获得积分 91
 
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于作者
玩网女帝*ψ▔ LV2
累计积分 91
发表主题数 9
热门标签
  • Archiver| 手机版| 小黑屋|
Powered by Discuz! X3.4 Copyright © 2001-2020, Tencent Cloud.( 鲁ICP备2022033771号-1 )|Sitemap