HIVE 总结 三 Hive DDL数据定义

HIVE 总结 三 Hive DDL数据定义

本篇总结hive的数据定义,也就是表,库相关的操作命令

关于hive的介绍,安装在前面两篇文章里面

1.建库

1. 语法

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

2. 创建数据库,数据在HDFS上默认的存储路径是由hive-site.xml配置文件中 hive.metastore.warehouse.dir 属性配置的

    <property>
           <name>hive.metastore.warehouse.dir</name>
           <value>/user/hive/warehouse</value>
       </property>
   
       <!-- 指定hiveserver2连接的端口号 -->
       <property>
           <name>hive.server2.thrift.port</name>
           <value>10000</value>
       </property>
      <!-- 指定hiveserver2连接的host -->
       <property>
           <name>hive.server2.thrift.bind.host</name>
           <value>hadoop102</value>
     </property>
   
   

3. 创建一个hive库

   create database hive_db;

   避免库已存在的错误,建议加上if not exists

   create database if not exists hive_db;

2.库的查询,修改,删除

1. 显示数据库

   show databases;
   

2. 查看数据详情

   desc database hive_db;
   

#可以查看到设置数据库的属性参数 parameters
desc database extended hive_db;

3. 修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter databse hive_db set dbproperties('createTime'='2020.7.31');

4. 删除数据库

   删除空数据库

   drop database hive_db2
   

   

   建议添加if exists 判断是否存在

   drop database if exists hive_db2 
   

   

   如果数据库不为空,可以使用cascade 强制删除

    drop database hive_db2 cascade;
   

3.建表

1. 建表语法

   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]
   [TBLPROPERTIES (property_name=property_value, ...)]
   [AS select_statement]
   [LIKE table_name]
   

2. 解释

   （1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

   （2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

   （3）COMMENT：为表和列添加注释。

   （4）PARTITIONED BY创建分区表

   （5）CLUSTERED BY创建分桶表

   （6）SORTED BY不常用，对桶中的一个或多个列另外排序

   （7）ROW FORMAT

   DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

   ​ [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

   用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

   SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。

   （8）STORED AS指定存储文件类型

   常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

   如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

   （9）LOCATION ：指定表在HDFS上的存储位置。

   （10）AS：后跟查询语句，根据查询结果创建表。

   （11）LIKE允许用户复制现有的表结构，但是不复制数据。

4.表的修改,删除,清除

1. 创建表的方式

   1. 普通创建

      create table if not exists student2(
      
      id int ,
      
      name string
      
      )
      
      row format delimited fields terminated by '\t'
      
      location '/user/hive/warehouse/student2'
      

   2. 根据查询结构创建表

      create table if not exists student3 as select id,name from student2; 
      

      使用as,会拷贝响应列的数据

      使用as 后面select的字段必须存在于student2中

   3. 使用like根据表结构创建表

      create table if not exists student4 like student3;
      

      使用like复制表结构到新的表,但是不会影响以及复制任何数据,student4只有student3的表结构,没有数据.

2. 查询表类型

   desc formatted student2; 
   

   

3. 删除表,查看表数据文件是否还存在于hdfs中

   drop table student2;
   

   

   删除此表后再HDFS上面的文件同样被删除

4. 修改表

   1. 重命名表

      ALTER TABLE table_name RENAME TO new_table_name
      alter table student3 rename to student5;
      

   2. 增删改列信息

      列的数据精度只能小改大,不能大改小

      ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
      
      -- 举个栗子
      alter table dept replace columns(deptno string, dname
       string, loc string);
       alter table dept change column deptdesc desc string;
      

5. 清除,删除表

   删除表:

   drop table dept;
   

   清除表:

    truncate table student;
   

5.内部表(管理表)和外部表的区别

1. 内部表概述

   默认创建的表都是所谓的管理表，有时也被称为内部表。

   因为这种表，Hive会（或多或少地）控制着数据的生命周期

   Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。

   当我们删除一个管理表时，Hive也会删除这个表中数据。

   管理表不适合和其他工具共享数据。

2. 外部表概述

   因为表是外部表，所以Hive并非认为其完全拥有这份数据。

   删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

3. 各自使用场景

   每天将收集到的网站日志定期流入HDFS文本文件。

   在外部表（原始日志表）的基础上做大量的统计分析，

   用到的中间表、结果表使用内部表存储，

   数据通过SELECT+INSERT进入内部表。

4. 简要概述区别

   内部表(管理表):hive完全掌控表中数据的生命周期,可以完全删除表元数据和表数据

   外部表:hive不能完全掌控表中数据的声明周期,只能删除表的元数据,删除表不能删除表内数据

5. 关键字

   建表时添加external 关键字

   create external table if not exists dept(
   deptno int,
   dname string,
   loc int
   )
   row format delimited fields terminated by '\t'
   location '/company/dept';
   

6. 内部表与外部表互换

   查看表类型

   desc formatted student3;
   

   

   内部表

   -- 改为内部表
   alter table student3 set tblproperties('EXTERNAL'='FALSE');
   -- 改为外部表
   alter table student3 set tblproperties('EXTERNAL'='true');
   

`

6. 内部表与外部表互换

   查看表类型

   desc formatted student3;
   

   [外链图片转存中…(img-FmqLKRbg-1596199343867)]

   内部表

   -- 改为内部表
   alter table student3 set tblproperties('EXTERNAL'='FALSE');
   -- 改为外部表
   alter table student3 set tblproperties('EXTERNAL'='true');