创建表

语法：
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]

其中：

• CREATE TABLE： 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；可以用 IF NOT EXISTS 选项来忽略这个异常。
• EXTERNAL：创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）。
• COMMENT：为表和列添加注释。
• PARTITIONED BY创建分区表
• CLUSTERED BY创建分桶表
• SORTED BY不常用
• 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的简称，目的是用于序列化和反序列化。
• STORED AS指定存储文件类型
  常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）。如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
• LOCATION ：指定表在HDFS上的存储位置。

 有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。
 表名和列名不区分大小写，SerDe 和属性名区分大小写。表和列的注释是字符串。

Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。

管理表

• 创建表
  
  查看：
  
• 查看表的类型：

语法：desc formatted 表名;

• 根据查询结果创建表

语法：create table if not exists 目标表名 as select 字段列表 from 源表名;

查看：

• 复制表结构

语法：create table if not exists 目标表名 like 源表名;

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

查看：

外部表

• 假设有下面数据：
  10 ACCOUNTING NewYork
  20 RESEARCH Boston
  30 SALES Dallas
  40 OPERATIONS Chicago

• 创建表
  

• 查看表格式
  

• 导入数据
  

• 查看数据
  

管理表和外部表相互转换

• 修改表为管理表：

语法：alter table 表名 set tblproperties(“EXTERNAL”=“FALSE”);

• 修改表为外部表

语法：alter table 表名 set tblproperties(“EXTERNAL”=“true”);

分区表

简介

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。分区表实际上对应一个HDFS文件系统上的文件夹，该文件夹下是该分区的数据文件。

HIVE的分区通过在创建表时启用partition by实现，具体分区的标识是插入内容时指定的。当要查询某一分区的内容时可以采用where语句，通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

示例：

1. 准备数据
   在Linux的/usr/local/test目录下面创建如下三个文件
   • stu1.txt：
     zhangsan 13 male shiziBan
     lisi 14 female musicBan
     wanger 19 male musicBan
     mazi 15 male shiziBan
   • stu2.txt：
     qianwu 12 female wudaoBan
     zhaoliu 16 female shiziBan
     lisi 18 male wudaoBan
     xiangming 13 female shiziBan
   • stu3.txt：
     wangwei 18 female wudaoBan
     ligang 10 male musicBan

创建分区表

3. 导入数据
   

查看数据

增加分区

删除分区

查看分区个数

查看分区结构

二级分区

二级分区指是2个分区字段
示例：
第一步：创建二级分区表：

第二步：导入数据

第三步：查询数据

分区和数据关联的方式

方式一：先上传数据后恢复

1. 创建目录并上传文件
   
2. 修复之后才能查询到数据

方式二：先上传数据后添加分区

1. 创建目录、上传数据
   
2. 添加分区后查询
   

先创建文件夹后load数据到分区

1. 创建文件夹
   
2. load数据到分区
   
3. 查询
   

查看表

• 查看表名

  SHOW TABLES 表名;
  支持表名模糊匹配，比如：show tables ‘tb_s*’;

• 查看某表结构：

  DESCRIBE 表名;

  

• 查看表格式化数据

  desc formatted 表名;

  

• 查看某表的所有分区信息，如果没有就报错：

  SHOW PARTITIONS 表名;

• 查看分区内容

  SELECT 字段列表 FROM 表名 WHERE 分区名=值;

  

• 查看有限行内容，同Greenplum，用limit关键词

  SELECT 字段列表 FROM 表名 limit 3;

  

• 查看表分区定义

  DESCRIBE EXTENDED 表名 PARTITION (分区名=值);

  

修改表

• 重命名表

  ALTER TABLE 旧表名 RENAME TO 新表名

  
  重命名之后发现对应的文件夹的名字也发生了改变：
  

• 更新列语法

  ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 列数据类型 [COMMENT col_comment] [FIRST|AFTER column_name]

  

• 增加和替换列不服水土

  ALTER TABLE 表名 ADD|REPLACE COLUMNS (列名 数据类型 [COMMENT col_comment], …)

  注：

  • ADD是代表新增一字段，字段位置在所有列后面(partition列前)
  • REPLACE则是抛弃原来的表结构，将原表中的所有的列替换为指定的列。

示例：add

示例：replace

不管怎么replace，HDFS中数据是不会发生变化的。

删除表

在删除表时：内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

语法：drop table 表名