Hive DDL语法

浅浅的花香味﹌ 2024-04-18 16:51 65阅读 0赞

# DDL语法 #

## 1. 数据库操作 ##

1.  创建一个数据库会在HDFS上创建一个目录，Hive里数据库的概念类似于程序中的命名空间，用数据库来组织表，在大量Hive的情况下，用数据库来分开可以避免表名冲突。Hive默认的数据库是default。
2.  hive不能使用关键字、数字开始的字符串来作库表名，不区分大小写。

#### 1.1 创建数据库例子： ####

hive> create database [if not exists] db1;

\[\]中的可以不写，判断是否存在

#### 1.2 查看数据库定义： ####

`Describe` 命令来查看数据库定义，包括：数据库名称、数据库在HDFS目录、HDFS用户名称。

hive> desc database db1;
    OK
    db1		hdfs://cluster1/user/hive/warehouse/db1.db	root	USER	
    Time taken: 0.059 seconds, Fetched: 1 row(s)

db1是数据库名称。

hdfs://cluster1/user/hive/warehouse/db1.db 是db1库对应的存储数据的HDFS上的根目录。

#### 1.3 查看数据库列表: ####

hive> show databases;
    OK
    db1
    default
    Time taken: 0.036 seconds, Fetched: 2 row(s)

#### 1.4 删除数据库 ####

删除数据库时，如果库中存在数据表，是不能删除的，要先删除所有表，再删除数据库。添加上`cascade`后，就可以先自动删除所有表后，再删除数据库。（友情提示：慎用啊！）删除数据库后，HDFS上数据库对应的目录就被删除掉了。

hive> drop database if exists db1 cascade;

#### 1.5 切换当前数据库 ####

hive> use db1;

## 2. 表操作 ##

#### 2.1 创建表 ####

**内外部表区别：**  
1、默认创建内部表，创建外部表需要external。  
2、一般使用外部表(长期存在的表、数据量大的、不希望把数据块删除的数据)，临时表或者确定使用即可清空全部数据(数据库和元数据)则可以使用内部表。  
3、内部表删除时将会删除元数据和hdfs中表对应的目录，而外部表删除时只会删除元数据，hdfs中的数据目录保留。  
4、`alter table userinfo set tblproperties('EXTERNAL'='true');` \#\#\#内部表转外部表，true一定要大写;  
`alter table log3 set tblproperties('EXTERNAL'='false');` \#\#false大小写都没有关系  
**分区表：**  
静态分区：加载数据到指定分区的值。  
动态分区：数据未知，根据分区的值来确定需要创建的分区。  
混合分区：静态和动态都有。  
动态分区的属性：

set hive.exec.dynamic.partition=true #设定动态分区
    set hive.exec.dynamic.partition.mode=strict/nonstrict #设定严格模式与否
    set hive.exec.max.dynamic.partitions=1000 #设定最大分区数
    set hive.exec.max.dynamic.partitions.pernode=100 #设定单节点最大分区数

strict:严格模式必须至少一个静态分区  
nonstrict：可以所有的都为动态分区，但是建议尽量评估动态分区的数量。

**桶表：**

###### 2.1.1 创建普通表（内部表） ######

hive> create table if not exists userinfo  
            > (
            >   userid int,
            >   username string,
            >   cityid int,
            >   createtime date    
            > )
            > row format delimited fields terminated by '\t'
            > stored as textfile;
            OK
            Time taken: 2.133 seconds

以上例子是创建表的一种方式，如果表不存在，就创建表userinfo。row format delimited fields terminated by ‘\\t’ 是指定列之间的分隔符；默认的分隔符是  
^A 、  
\\u0001 、  
\\001、  
^B \\002、  
^C \\003  
stored as textfile是指定文件存储格式为textfile。  
创建表一般有几种方式：

*  create table 方式：以上例子中的方式。
 *  create table as select 方式：根据查询的结果自动创建表，并将查询结果数据插入新建的表中。
 *  create table like tablename1 方式：是克隆表，只复制tablename1表的结构。复制表和克隆表会在下面的Hive数据管理部分详细讲解。

###### 2.1.2 创建外部表 ######

外部表是没有被hive完全控制的表，当表删除后，数据不会被删除。

hive> create external table iislog_ext (
            >  ip string,
            >  logtime string    
            > )
            > ;

###### 2.1.3 创建分区表 ######

Hive 分区表有  
Hive查询一般是扫描整个目录，但是有时候我们关心的数据只是集中在某一部分数据上，比如我们一个Hive查询，往往是只是查询某一天的数据，这样的情况下，可以使用分区表来优化，一天是一个分区，查询时候，Hive只扫描指定天分区的数据。

普通表和分区表的区别在于：一个Hive表在HDFS上是有一个对应的目录来存储数据，普通表的数据直接存储在这个目录下，而分区表数据存储时，是再划分子目录来存储的。一个分区一个子目录。主要作用是来优化查询性能。（可创建多级分区）

–创建经销商操作日志表

create table user_action_log
        (
        companyId INT comment   '公司ID',
        userid INT comment   '销售ID',
        originalstring STRING comment   'url', 
        host STRING comment   'host',
        absolutepath STRING comment   '绝对路径',
        query STRING comment   '参数串',
        refurl STRING comment   '来源url',
        clientip STRING comment   '客户端Ip',
        cookiemd5 STRING comment   'cookiemd5',
        timestamp STRING comment   '访问时间戳'
        )
        partitioned by (dt string)
        row format delimited fields terminated by ','
        stored as textfile;

这个例子中，这个日志表以dt字段分区，dt是个虚拟的字段，dt下并不存储数据，而是用来分区的，实际数据存储时，dt字段值相同的数据存入同一个子目录中，插入数据或者导入数据时，同一天的数据dt字段赋值一样，这样就实现了数据按dt日期分区存储。

当Hive查询数据时，如果指定了dt筛选条件，那么只需要到对应的分区下去检索数据即可，大大提高了效率。所以对于分区表查询时，尽量添加上分区字段的筛选条件。

###### 2.1.4 创建桶表 ######

桶表也是一种用于优化查询而设计的表类型。创建通表时，指定桶的个数、分桶的依据字段，hive就可以自动将数据分桶存储。查询时只需要遍历一个桶里的数据，或者遍历部分桶，这样就提高了查询效率。举例：

\------创建订单表

create table user_leads
    (
    leads_id string,
    user_id string,
    user_id string,
    user_phone string,
    user_name string,
    create_time string
    )
    clustered by (user_id) sorted by(leads_id) into 10 buckets 
    row format delimited fields terminated by '\t' 
    stored as textfile;

对这个例子的说明：

*  clustered by是指根据user\_id的值进行哈希后模除分桶个数，根据得到的结果，确定这行数据分入哪个桶中，这样的分法，可以确保相同user\_id的数据放入同一个桶中。而经销商的订单数据，大部分是根据user\_id进行查询的。这样大部分情况下是只需要查询一个桶中的数据就可以了。
 *  sorted by 是指定桶中的数据以哪个字段进行排序，排序的好处是，在join操作时能获得很高的效率。
 *  into 10 buckets是指定一共分10个桶。

在HDFS上存储时，一个桶存入一个文件中，这样根据user\_id进行查询时，可以快速确定数据存在于哪个桶中，而只遍历一个桶可以提供查询效率。

— 分桶表读写过程

待完善。。。。。。

#### 2.2 查看有哪些表 ####

–查询库中表

show tables;

– 查询分区表

show  partitions tablename;
    --直接写到文件中
    show partitions tablename > result.txt; //覆盖写
    show partitions tablename >> result.txt; //追加写

`Show TABLES '*info';` \--可以用正则表达式筛选要列出的表

#### 2.3 查看表定义 ####

###### 2.3.1 查看简单定义： ######

describe userinfo;

###### 2.3.2 查看表详细信息： ######

describe formatted userinfo;

执行结果如下所示：

<table> 
 <thead> 
  <tr> 
   <th>备注</th> 
   <th>col_name</th> 
   <th>data_type</th> 
   <th>comment</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>列信息</td> 
   <td># col_name</td> 
   <td>data_type</td> 
   <td>comment</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>userid</td> 
   <td>int</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>username</td> 
   <td>string</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>cityid</td> 
   <td>int</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>createtime</td> 
   <td>date</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td># Detailed Table Information</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>所在库</td> 
   <td>Database:</td> 
   <td>user_db</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>所属HUE用户 Owner: admin NULL</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>表创建时间</td> 
   <td>CreateTime:</td> 
   <td>Tue Aug 16 06:05:14 PDT 2019</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>最后访问时间</td> 
   <td>LastAccessTime:</td> 
   <td>UNKNOWN</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Protect Mode:</td> 
   <td>None</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Retention:</td> 
   <td>0</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>表数据文件在HDFS上路径</td> 
   <td>Location:</td> 
   <td>hdfs://cluster1/user/hive/warehouse/db1.db/userinfo</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>表类型(内部表或者外部表)</td> 
   <td>Table Type:</td> 
   <td>MANAGED_TABLE</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>表分区信息</td> 
   <td>Table Parameters:</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>transient_lastDdlTime</td> 
   <td>1471352714</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td># Storage Information</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>序列化反序列化类</td> 
   <td>SerDe Library:</td> 
   <td>org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>mapreduce中的输入格式</td> 
   <td>InputFormat:</td> 
   <td>org.apache.hadoop.mapred.TextInputFormat</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>mapreduce中的输出格式</td> 
   <td>OutputFormat:</td> 
   <td>org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>压缩</td> 
   <td>Compressed:</td> 
   <td>No</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>总占用数据块个数</td> 
   <td>Num Buckets:</td> 
   <td>-1</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Bucket Columns:</td> 
   <td>[]</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Sort Columns:</td> 
   <td>[]</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Storage Desc Params:</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>field.delim</td> 
   <td>\t</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>serialization.format</td> 
   <td>\t</td> 
  </tr> 
  <tr> 
   <td>—</td> 
   <td>—</td> 
   <td>—</td> 
   <td>—</td> 
  </tr> 
 </tbody> 
</table>

#### 2.4 修改表 ####

常用的对表的修改操作有：修改表名、添加字段、删除字段、修改字段等

###### 2.4.1 修改表名 ######

–将表名从userinfo改为user\_info

alter table userinfo rename to user_info;

###### 2.4.2 添加字段 ######

—在user\_info表添加一个字段provinceid，int 类型

alter table user_info add columns (provinceid int );

###### 2.4.3 删除字段 ######

— 删除字段只能是覆盖重写，并不是真正的删除

replace columns
    alter table user_info replace columns(
     userid int,
     username string,
     createtime date    
    );

###### 2.4.4 修改字段 ######

alter table user_info 
    replace columns (
    userid int,
    username string,
    cityid int,
    joindate date,
    provinceid int
    );

修改字段，只是修改了Hive表的元数据信息（元数据信息一般是存储在MySql中），并不对存在于HDFS中的表数据做修改。  
并不是所有的Hive表都可以修改字段，只有使用了native SerDe (序列化反序列化类型)的表才能修改字段

#### 2.5 删除表 ####

–如果表存在，就删除。

drop table if exists user_info;

if exists 可以不写  
或者

truncate table tableName;

删除表中所有数据，而不破坏表结构  
或者

insert overwrite table A select id,name from A where id !=2;

依然是以覆盖的方式删除某一行数据

#### 2.6 关于分区表 ####

1、查看分区

show partitions 表名;

2、添加分区

alter table part1 add partition(dt='2019-09-10');
    alter table part1 add partition(dt='2019-09-13') partition(dt='2019-09-12');
    alter table part1 add partition(dt='2019-09-11') location  '/user/hive/warehouse/db1.db/part1/dt=2019-09-10';

3、分区名称修改

alter table part1 partition(dt='2019-09-10') rename to partition(dt='2019-09-14');

4、修改分区路径

alter table part1 partition(dt='2019-09-14') set location '/user/hive/warehouse/db1.db/part1/dt=2019-09-09';    --错误使用
    alter table part1 partition(dt='2019-09-14') set location 'hdfs://hadoo01:9000/user/hive/warehouse/db1.db/part1/dt=2019-09-09';  --绝对路径

5、删除分区

alter table part1 drop partition(dt='2019-09-14');
    alter table part1 drop partition(dt='2019-09-12'),partition(dt='2019-09-13');