2022-02-05大数据学习日志——Hadoop离线阶段——Hive SQL DDL

学习目标

#掌握HQL DDL建表语句
	理解Hive SerDe机制、分隔符语法
	掌握内外部表、分区表、分桶表创建使用
#理解HQL DDL其他语句
	修改、删除

内容大纲

#1、HQL DDL 数据定义语言 针对表的
 核心：建表语句   直接决定了表和文件之间能否映射成功
 	数据类型
 	SerDe序列化机制
 	分隔符语法
 	内部表、外部表
 	数据存储路径
 	分区表
 	分桶表
  alter修改表

---

01_Apache Hive DDL 概念与语法树介绍

蓝色字体是建表语法的关键字，用于指定某些功能。
[ ]中括号的语法表示可选。
|表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。

---

02_Apache Hive DDL 建表语句 表存在忽略异常

IF NOT EXISTS

• 建表的时候，如果表名已经存在，默认会报错，通过IF NOT EXISTS关键字可以忽略异常。

  --第一次创建表
  0: jdbc:hive2://node1:10000> create table t_1(id int,name string,age int);
  --再次执行
  0: jdbc:hive2://node1:10000> create table t_1(id int,name string,age int);
  Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. AlreadyExistsException(message:Table t_1 already exists) (state=08S01,code=1)
  --Error while processing statement 执行期间的错误 往往就是逻辑错误
  
  --加上if not exists忽略异常
  0: jdbc:hive2://node1:10000> create table if not exists t_1(id int,name string,age int);
  
  0: jdbc:hive2://node1:10000> creatf table t_1(id int,name string,age int);
  Error: Error while compiling statement: FAILED: ParseException line 1:0 cannot recognize input near 'creatf' 'table' 't_1' (state=42000,code=40000)
  0: jdbc:hive2://node1:10000> 
  --Error while compiling statement 编译期间的错误  SQL语法问题
  
  hivesql -->编译--->执行
  

---

03 Apache Hive DDL 建表语句 数据类型

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

• Hive除了支持SQL类型之外，还支持java数据类型；

• Hive除了支持基础数据类型之外，还支持复合类型（array数组 map映射）；

  • 针对复合类型的数据 要想直接从文件中解析成功 还必须配合分隔符指定的语法。

• Hive中大小写不敏感；

• 在建表的时候，最好表的字段类型要和文件中的类型保持一致，

  • 如果不一致，Hive会尝试进行类型隐式转换，不保证转换成功，如果不成功，显示null;

• 栗子

  --创建数据库并切换使用
  create database if not exists itheima;
  use itheima;
  
  --建表
  create table t_archer(
        id int comment "ID",
        name string comment "英雄名称",
        hp_max int comment "最大生命",
        mp_max int comment "最大法力",
        attack_max int comment "最高物攻",
        defense_max int comment "最大物防",
        attack_range string comment "攻击范围",
        role_main string comment "主要定位",
        role_assist string comment "次要定位"
  ) comment "王者荣耀射手信息"
  row format delimited
  fields terminated by "\t";
  
  --查看表数据 查看表的元数据信息
  select * from t_archer;
  desc formatted t_archer;
  
  --上传文件到表对应的HDFS目录下
  [root@node1 hivedata]# hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer
  
  [root@node1 hiedata]# pwd
  /root/hivedata
  

---

04_Apache Hive DDL 建表语句 SerDe机制、分隔符指定语法

• 机制：SerDe（Serializer and Deserializer）序列化机制

• Hive使用SerDe机制读写HDFS上文件

• 读文件

  • HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object

    #1、使用InputFileFormat（默认实现TextInputFormat）读取hdfs上文件
        一行一行读取数据，返回<k，v>键值对类型
    #2、返回<key, value>，其中数据存储在value中
    
    #3、使用Deserializer反序列化动作读取value  解析成为对象（Row object）
        默认的序列化类LazysimpleSerDe
    

• 写文件

  • Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files

• 分隔符指定语法

  • 语法格式

    ROW FORMAT DELIMITED | SERDE
    
    ROW FORMAT DELIMITED  表示使用LazySimpleSerDe类进行序列化解析数据
     
    ROW FORMAT SERDE      表示使用其他SerDe类进行序列化解析数据
    

  • ROW FORMAT DELIMITED具体的子语法

    row format delimited
    [fields terminated by char]  #指定字段之间的分隔符
    [collection items terminated by char]  #指定集合元素之间的分隔符
    [map keys terminated by char]         #指定map类型kv之间的分隔符
    [lines terminated by char]            #指定换行符
    

  • 课堂练习sql

    create table t_hot_skin_1(
    id int,
    name string,
    win_rate int,
    skin_price string
    )
    row format delimited
    fields terminated by ',';
    
    
    create table t_hot_skin_2(
    id int,
    name string,
    win_rate int,
    skin_price map<string,int>
    )
    row format delimited
    fields terminated by ','
    collection items terminated by '-'
    map keys terminated by ':';
    

---

05_Apache Hive DDL 建表语句 默认分隔符

• 默认分隔符

  • Hive在建表的时候，如果没有row format语法，则该表使用\001默认分隔符进行字段分割；
  • 如果此时文件中的数据字段之间的分隔符也是\001 ，那么就可以直接映射成功。
  • 针对默认分隔符，其是一个不可见分隔符，在代码层面是\001表示
  • 在vim编辑器中，连续输入ctrl+v 、ctrl+a；
  • 在实际工作中，Hive最喜欢的就是\001分隔符，在清洗数据的时候，有意识的把数据之间的分隔符指定为\001;

• 栗子

  --建表
  create table t_hot_hero_skin_price(
        id int,
        name string,
        win_rate int,
        skin_price map<string,int>
  )
  row format delimited
  fields terminated by ',' --字段之间分隔符
  collection items terminated by '-'  --集合元素之间分隔符
  map keys terminated by ':'; --集合元素kv之间分隔符;
  
  --上传数据
  hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/itheima.db/t_hot_hero_skin_price 
  
  select *
  from t_hot_hero_skin_price;
  
  
  --有点想法： 就把map数据类型当成字符串映射如何？
  create table t_hot_hero_skin_price_str(
        id int,
        name string,
        win_rate int,
        skin_price string
  )
  row format delimited
  fields terminated by ',';
  
  hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/itheima.db/t_hot_hero_skin_price_str 
  
  --结论 
  	·1、不管使用map还是使用string来定义数据 都能解析映射成功
  	 2、区别在于使用的过程中 一个是针对map类型数据处理  一个是针对string类型的数据处理
  	 
  	 
  select skin_price from t_hot_hero_skin_price;	 
  select skin_price from t_hot_hero_skin_price_str;
  
  
  select skin_price["至尊宝"] from t_hot_hero_skin_price limit 1;	 
  select skin_price["至尊宝"] from t_hot_hero_skin_price_str limit 1; --语法错误