查看表的完整建表语句

当我们使用 create table test 可以创建一张表时，很多参数 hive 帮助我们配置成了默认参数。

如果想查看该表所有配置参数的完整建表语句，可以使用以下SQL：

  1. show create table test;

返回结果为：

  1. CREATE TABLE `test`(
  2.   `id` string)
  3. ROW FORMAT SERDE 
  4.   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
  5. STORED AS INPUTFORMAT 
  6.   'org.apache.hadoop.mapred.TextInputFormat'       -- MR操作时的InputFormat
  7. OUTPUTFORMAT 
  8.   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'   -- MR操作时的OutputFormat
  9. LOCATION
  10.   'hdfs://hadoop102:8020/user/hive/warehouse/test'   -- 该表在HDFS上的存储路径
  11. TBLPROPERTIES (
  12.   'bucketing_version'='2', 
  13.   'transient_lastDdlTime'='1660101789')    -- 最后一次DDL的时间等属性

类似的，其他DDL也可以进行show查看完整命令：

  1. show create database hive;

数据库操作

创建数据库

语法格式：

  1. -- IF NOT EXISTS 先判断是否已经存在，不存在时才创建
  2. create database [IF NOT EXISTS] database_name
  3. -- 注释
  4. [COMMENT database_comment]
  5. -- 指定该表在hdfs上的存储位置，默认路径为：/user/hive/warehouse/数据库名称.db/
  6. -- 在该数据库中创建表时，表的默认存储路径就是：/user/hive/warehouse/数据库名称.db/表名/
  7. [LOCATION hdfs_path]
  8. -- 创建时间、作者等其他属性
  9. [WITH DBPROPERTIES (property_name=property_value, ...)]

示例：

  1. create database if not exists db_hive;

查询数据库

显示所有数据库名称：

  1. show databases;

过滤显示查询的数据库名称：

  1. -- 查询以hive开头的数据库
  2. show databases like 'hive*';

查询指定数据库的信息：

  1. -- 查询hive数据库的信息
  2. desc database hive;

返回数据库的相关信息：

  1. db_name    comment    location    owner_name    owner_type    parameters
  2. hive        hdfs://hadoop102:8020/user/hive/warehouse/hive.db    tengyer    USER

查询指定数据库的详细信息：

  1. desc database extended hive;

这里查询的其实就是元数据库中的元数据信息，所以也可以直接在 Mysql 元数据库中进行查询：

  1. select * from DBS;

切换数据库

使用use在不同数据库之间进行切换：

  1. use hive;

修改数据库

用户可以使用 ALTER DATABSE 命令为某个数据库的 DBPROPERTIES 设置键值对属性值，来描述这个数据库的属性信息：

  1. alter database hive set dbproperties('createtime'='20200101');

查看修改结果：

  1. desc database extended hive;

删除数据库

删除空的数据库：

  1. drop database hive;

如果删除的数据库无法确定是否存在，最好加上 if exists 判断：

  1. drop database if exists hive;

如果数据库不为空，可以加上 cascade级联强制删除：

  1. drop database hive cascade;

表操作

创建表

建表语句

语法格式：

  1. -- EXTERNAL：外部表
  2. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  3. -- 字段 字段类型 字段注释
  4. [(col_name data_type [COMMENT col_comment], ...)]
  5. -- 表注释
  6. [COMMENT table_comment]
  7. -- 分区表
  8. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  9. -- 分桶表 指定分组字段、桶个数
  10. [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  11. -- 数据行格式：列分隔符、换行符等
  12. [ROW FORMAT row_format]
  13. -- 指定文件格式：文本文件、压缩文件等
  14. [STORED AS file_format]
  15. -- 表的存储位置
  16. [LOCATION hdfs_path]
  17. -- 表的其他属性
  18. [TBLPROPERTIES (property_name=property_value, ...)]
  19. -- 从另一张表查询结果创建表
  20. [AS select_statement]

管理表（内部表）

默认创建的表都是管理表，有时也称为内部表。我们平时创建（不带external关键字）的表都是内部表。

因为这种表，Hive会（或多或少）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir所定义的目录的子目录下。

当我们删除一个管理表时，Hive也会删除这个表中的数据。管理表不适合和其他工具共享数据。 即：Mysql元数据库中的元数据信息、Hdfs上的数据文件夹和数据文件都会被删除。

可以通过元数据库中的TBLS表的TBL_TYPE字段判断该表是否是管理表（MANAGED_TABLE）。

  1. create table test(id string);

外部表

当表创建时指定为external外部表时，Hive并非认为其完全拥有这份数据。

删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。也即：只删除 Mysql 元数据库中存储的元数据信息，不删除 hdfs 上的数据文件内容。

  1. create external table test4(id string);

例如：每天将收集到的网站日志，定期流入HDFS文本文件中，在Hive中创建外部表，可以做一些统计分析，将最后分析的结果使用Hive内部表进行存储，分析过程中，使用 select + insert 插入内部表中。

管理表和外部表的互相转换

查看表的类型：

  1. -- 查看表的信息，其中Table Type是表的类型
  2. desc formatted test4;

修改内部表为外部表：

  1. -- 将内部表test4转换为外部表,需要注意EXTERNAL大小写
  2. alter table test4 set tblproperties('EXTERNAL'='TRUE');

修改外部表为内部表：

  1. -- 将外部表转换为内部表
  2. alter table test4 set tblproperties('EXTERNAL'='FALSE');

指定分隔符等数据行格式

示例：

  1. create table test3(
  2.     name string,
  3.     friends array<string>,
  4.     children map<string,int>,
  5.     address struct<street:string, city:string>
  6. )
  7. -- 数据行的格式定义
  8. row format delimited 
  9. -- 字段间分隔符
  10. fields terminated by ','     
  11. -- 数组集合类型的多个元素之间分隔符
  12. collection items terminated by '_'   
  13. -- map的keyvalue之间分隔符
  14. map keys terminated by ':'    
  15. -- 换行符，默认的就是\n
  16. lines terminated by '\n';

修改表

重命名表

重命名表语法：

  1. ALTER TABLE table_name RENAME TO new_table_name;

修改字段信息

更新列：

  1. ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name
  2. column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替换列：

  1. -- ADD 新增一个字段，字段位置在所有列后面（partition列前）
  2. -- REPLACE 替换表中所有字段
  3. ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

删除表

删除表

内部表：删除表时，元数据和hdfs数据文件全都删除

外部表：只删除元数据

  1. drop table test;