Hive详细讲解-底子语法快速入门

1.DDL数据库相关操作

• HiveSQL分为两部分DDL数据界说，DML数据操作
  
• 下述代码块中所有[]圈起来的操作都表示可选的操作
  

1.1创建数据库

1. CREATE DATABASE [IF NOT EXISTS] database_name
2. [COMMENT database_comment]
3. [LOCATION hdfs_path]
4. [WITH DBPROPERTIES (property_name=property_value, ...)];

复制代码

• COMMENT表示注释
  
• Location表示HDFS路径
  
• With dbproperties表示可以向数据库添加附加参数
  
• 案例如下所示：
  

注：若不指定路径，其默认路径为${hive.metastore.warehouse.dir}/database_name.db

1.2指定路径下创建数据库

1.3添加额外信息创建with dbproperties

1. create database db_hive2
2. with dbproperties ('name'='junchao');

复制代码

1.4查看数据库 结合like模糊查询

like查询可以使用通配符
*表示匹配任意个字符
|表示或的关系

2.查看某一个数据库的相关信息

2.1.如何查看数据库信息，extended可选

desc和describe结果相同
只有extended加入之后数据库才会展示parameters

2.2修改数据库

3.Hive基本数据类型

• 所有数据类型均小写
  
• string和varchar的区别，varchar（2000）需要指定长度例如2000不能超过65535，string不需要
  
• decimal表示小数10进度准度decimal(总位数，小数位数)
  
• 像decimal和varchar等需要传参的
  

4.复杂数据类型

• 建表建立一个字段的名称：array 表示该字段为数组类型，元素为string
  
• map和java同理，但是请注意小写；
  
• struct表示结构体，和JavaBean雷同。理解界说和取值即可。
  

5.类型转换

• 类型转换情景：
  
  
  
  • int类型数据和bigint类型数据。
    
  • 往一张表insert数据-类型不匹配可以转换
    
  
  

5.1类型隐士转换

• 仅学习规则即可
  
  
  
  
• 转换为参与运算的两个类型的最小转换类型
  

• 详细逻辑如上图所示，此中，横轴从左到右类型逐渐变大。
  

5.2类型显式转换

• cast函数做体现类型转换。
  
• string转int数据如下所示
  

• 固然注意事项，你这个字符必须是能转化为int类型的数据。
  

6.建表逻辑*

• row format关键字，可选，表示对每行数据进行序列化和反序列化
  
• stored as：指定文件格式，textfile(默认)、sequence file、orc file、parquet file.和底层的文件格式相关
  
• partitioned by
  

   指定hive的分区字段
  分区指的是hive一张表的数据按照你指定的分区字段将数据存储到不同的字段中。
  

• clustered by
  

   只当分桶表
  将hive一张表的数据分散的存储到多个文件中。
  Hive的读流程：

Hive的写流程：

   hive中任意一张表的建表语句都需要inputformat、outputformat、serializer
  

• 若不配置，则默认配置序列化反序列化器
  

• stored as后面紧跟inputformat、outputformat
  
• row format重点：
  

1. ROW FORAMT DELIMITED
2. [FIELDS TERMINATED BY char]
3. [COLLECTION ITEMS TERMINATED BY char]
4. [MAP KEYS TERMINATED BY char]
5. [LINES TERMINATED BY char]
6. [NULL DEFINED AS char]

复制代码

• 1.delimited只要声明，hive就会默认对每行数据进行序列化和反序列化。
  
• delimited关键字可选一些分隔符
  

null值的存储格式；
配置null defined as ;默认值是\N

• 2.serde序列化方式
  

与上述delimited序列化方式不同，serde可以指定特定的序列化方式如json格式。也可以自界说序列化格式。

1. row format serde serdename

复制代码

• 3.stored as 跟文件格式，hive底层可以直接进行映射例如textinputformat
  

   当前章节仅了解即可， 后续详细讲解stored as等
  7.CTAS建表

• 用户使用select查询结果进行建表
  

1. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
2. [COMMENT table_comment]
3. [ROW FORMAT row_format]
4. [STORED AS file_format]
5. [LOCATION hdfs_path]
6. [TBLPROPERTIES (property_name=property_value, ...)]
7. [AS select_statement]

复制代码

  此处create 后面关键字仅有temporary
  先前的可以指定external外部表等这是CTAS限定；
  8.create table like建表

• 用户可以在一张已经存在的表上进行复刻，但是与先前的区别是此建表是没有数据的
  

1. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
2. [LIKE exist_table_name]
3. [ROW FORMAT row_format]
4. [STORED AS file_format]
5. [LOCATION hdfs_path]
6. [TBLPROPERTIES (property_name=property_value, ...)]

复制代码

9.创建表应用案例

• 创建一张表
  

• 向hdfs中直接CLI方式传入数据
  

• 按然后直接查询该表就能看到数据
  

   这个表创建时没有声明外部表，external
  那么默认表示是内部表
  

• 删除表之后
  

• 创建外部表测试案例
  

• 删除之后查看hdfs
  

   仍然存在
  

• 练习2，serde和复杂数据类型
  
• 首先创建一个json格式文件
  

• 关于json格式文件存储格式，hive已经想好了，使用hive界说的JSON serde即可
  
• json一级字段格式要求和表的字段一致
  
• json的值和表的字段值要保持一致，可能使用到复杂数据类型。
  

   

• 一级字段要保持一致
  
• 结构体写法
  

1. struct<name:string>

复制代码

• row format格式要求json
  

1. row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'

复制代码

• 注意一下复杂数据类型即可
  
• myhadoop.json的文件格式肯定要是压平之后的文件格式。否则报错
  
• 别的服务器可以向hdfs上传任何后缀的文件，hive都能够辨认到，只要文件格式和当前界说保持一致即可
  

  

• 练习3，复杂数据类型取值
  

• 练习4CTAS建表语句
  

• 练习5CTL建表语句
  

10.查看表语法

1.展示所有表

2.查看某一个表的详细信息

• formatted结果更全
  

3.表的重定名 rename to

4.修改表的列信息

• 增长列
  
• 更新列
  
• 替换列
  

   请你注意：
  

• 三种列的修改仅仅对表的元数据信息进行修改，而hdfs源文件不会进行修改，若有修改需求请你手动修改
  
• 新增列新增的column仅会追加到末端
  

  

• 新增列
  

• 替换列
  

   替换列指的是将所有字段重新赋值，也就是所有column进行重新替换。
  

• 更新列
  

   经验小结：
  所有sql语句后面table 后面大多跟表明，可能和语法有关
  上述修改表字段类型时，参考隐式转换，string式不能直接转int
  

    如果非要string转int，可以使用hive提供的强转

1. set hive.metastore.disallow.incompatible.col.type.changes=false;

复制代码

   

  11.DML

• DML（data manipulate language）
  
• DML学习章节没有数据库删除语句，原因式hive是海量数据的分析盘算引擎，很少对数据进行删除操作。
  

1.Load

• 将文件导入表中，此功能和put一样
  

   local指的是当前客户端向hdfs上 传文件
  命令行客户端那么表示命令行当前节点
  hiveserver2表示hiverserver2所在节点
  

可以多次into操作，表示追加

将hdfs路径下数据load

overwrite方式load数据

   load从客户端load 文件时copy方式
  hdfs load，是hdfs文件移动方式
  2.Insert

• 平常插入语句
  

   into可以替换为overwrite
  与load不同
  load若进行覆盖写，那么直接overwrite into 或into
  而insert 要么into 要么overwrite
  

• 用户点名数据插入到表中
  

   此处注意的点是：values后面一个括号表示 要插入的一行数据
  

• 将查询结果插入到目标路径
  

   此处必须用overwrite而不能用into
  

   local表示当地否则是hdfs路径
  row format声明行格式或声明每一行序列化反序列化
  stored as声明文件格式
  3.export

4.import

   我的临时实现不了，先暂定
  12.Hive查询语句

• 基本语法
  

   

• 默认ALL模式除非单独加入distinct
  
• select from where groupby having order by limit
  
• where是对每一行数据进行过滤
  
• having是对分组数据进行过滤
  

  

12.1关系运算函数

• where，having后面紧跟过滤条件，不可制止会跟关系运算函数
  

   

• between and关键字表示区间判断[a,b]，同理between前面可以加入not达到相反的结果
  
• 判空操作用is null ，is not null，你就记着，null是string类型，以是is很合理。
  
• in 关键字和一个集合做判断，若在集合中，那么返回true
  
• like关键字模糊匹配，此处模糊和show后面的like表示不一样，show 后面的like可以用*表示全部，此处平替为了%
  
• 注意一下正则匹配rlike regexp
  

  

12.2聚合函数*

• 将多行数据进行聚合到一行进行盘算如count
  
• 语法
  

• 查询总行数
  

   

• count(*)统计总行数不会忽略null
  
• count(1)统计非Null总行数
  

  COUNT(*) 会对所有列进行查抄（即使不会读取实际列值），以确保行的存在性。
  COUNT(1) 只会查抄一列的存在性或用虚拟列来替换。
  性能上count(1)略微高效
  

• Count（id）此用法和count(1)一样，id可能为空因此不会统计在内
  

  

• SQL跑MR执行过程
  

• 查询所有员工的薪水最大值
  

   

• 盘算最大值，max聚合函数要放到select后面而不是过滤条件处
  
• min聚合函数与count相同
  
• 均匀值avg()同
  

  12.3 group by

• 案例需求，我如今想要做更为复杂的统计 ，此处肯定不能count
  

   固然你直接将job进行分组后还是要统计行数的
  因此需要和count(*)一起使用。
  

   老师精讲：分组聚合做的第一部将group by应用到用户指定的字段例如job，然后使用你指定的聚合函数对每一组进行统计等。
  

   细节：
  hive语法规定，你使用了分组聚合那么就不能在select查询分组以外的数据了
  

   

• 你能选的字段，分组字段和聚合函数
  

  

• 对分组聚合函数进行过滤
  

   having专门对group by进行过滤。
  13.Join语句

• 多张表进行横向拼接成一个宽表
  
• join操作会生成一张虚拟表，此表包括join表的所有字段。固然deptno两张表都有，因此虚拟表都会存在
  

• 盘算dept表中每一个loc有多少个员工
  

   直接在join完成的虚拟表进行分组聚合操作即可
  

• Join毗连类型
  

   等值毗连，非等值毗连（非等值毗连不常用 ）
    DG快捷键，格式化当前sql代码；
  ctrl shift enter
  

• Join毗连
  

   内毗连
  外毗连
  左外，右外毗连，端外毗连，多表毗连，笛卡尔毗连，联合
  

• 内毗连
  

   1.默认情况下单写一个join表示内毗连。
    2.只有进行毗连的两个表中都存在与毗连条件相匹配的数据才会被保存下来。

1. select
2.     e.empno,
3.     e.ename,
4.     d.deptno
5. from emp e
6. join dept d
7. on e.deptno = d.deptno;

复制代码

内毗连实现的结果就是二者毗连条件的公共字段被返回

• 左外毗连
  

1. select
2.     e.empno,
3.     e.ename,
4.     d.deptno
5. from emp e
6. left join dept d
7. on e.deptno = d.deptno;

复制代码

  左外毗连
  

• 行返回的是左表的全部行
  
• 列返回的是左右量表的全部列
  
• 关于毗连条件d表能够匹配上e表就直接返回，如果没有匹配上返回null
  

  

• 由外毗连和左外毗连相同
  

   返回的行是右表的所有行，列是所有列
  注意：若单join默认就是内毗连，left join，right join，full join默认就是外毗连

• 满外毗连
  
• full join 或 full outer join
  

返回所有行所有列，没匹配上返回null

• 笛卡尔积（慎用）
  
• ab两个表粉笔m，n行数据，进行笛卡尔积操作，a表的每一行作为k与b表的每一行进行join关联
  

• 出现笛卡尔积的情况
  
  
  
  • 内毗连不写毗连条件
    
  • 毗连条件无效 例如on 1=1；
    
  • 特别写法
    
  1. select * from emp,location

  复制代码
  
  

14.Union

• Join将两个表的数据进行横向的拼接，而Union是将两个表的数据进行纵向拼接
  

• 上下两表union的前置要求
  
  
  
  • 两个表的字段个数相同
    
  • 每个字段的类型也要前后对应
    
  
  
• union毗连两个查询语句
  

• Union注意事项
  
  
  
  • 1.毗连的必须是select查询语句，不能表union表
    
  • 2.字段名不一致不影响union，只要字段个数，字段类型对应即可。字段名不一致，最终返回结果的字段名取第一个表界说的字段名；
    
  • 3.union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。
    
  
  

15.Order by排序

• 按照工资升序排序 order by 默认升序
  

1. -- 15.按照工资升序排序
2. select * from emp
3. order by sal;

复制代码

• 降序的话使用desc
  

1. -- 15.按照工资升序排序
2. select * from emp
3. order by sal desc;

复制代码

• 注意：生产环境Order by是高危操作，可以看一下底层执行计划
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。