关于HiveQL的常用语法总结(一)——DDL

利用Hadoop平台做用户画像也有一些时日了，用到Hive的时候是非常多的，今年年初就想总结下Hive中常用的语法情况，因为各种各样的原因，一直拖到现在，惭愧…总算可以稍作总结下了，这个主题我想分为四个部分进行叙述：DDL——DML——hive常用函数——其它技巧(hive优化技巧)。
本系列文章的定位是：总结hive的常用知识点和实战经验，不涉及艰深的大数据理论架构等问题。
好啦，直接进入主题吧。

1、数据库语言简介

数据库语言有四大功能：DDL,DML,DCL,TCL。
Data Definition Language，数据库定义语言。用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
Data Manipulation Language，数据操纵语言。数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
Data Control Language，数据库控制语言。 授权，角色控制等。
Transaction Control Language，事务控制语言。设置保存点，回滚。

2、DDL语法

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 EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加描述
•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 确定表的具体的列的数据。

•STORED AS

        SEQUENCEFILE

        | TEXTFILE

        | RCFILE    

        | INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname
   如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

基于以上的语句，可以在hive中建立各类表：
在此，需要先了解下hive支持的数据类型：基础类型和复杂类型。

复杂类型包括ARRAY,MAP,STRUCT,UNION，这些复杂类型是由基础类型组成的。

ARRAY：ARRAY类型是由一系列相同数据类型的元素组成，这些元素可以通过下标来访问。比如有一个ARRAY类型的变量fruits，它是由['apple','orange','mango']组成，那么我们可以通过fruits[1]来访问元素orange，因为ARRAY类型的下标是从0开始的；
　　MAP：MAP包含key->value键值对，可以通过key来访问元素。比如”userlist”是一个map类型，其中username是key，password是value；那么我们可以通过userlist['username']来得到这个用户对应的password；
　　STRUCT：STRUCT可以包含不同数据类型的元素。这些元素可以通过”点语法”的方式来得到所需要的元素，比如user是一个STRUCT类型，那么可以通过user.address得到这个用户的地址。
　　UNION: UNIONTYPE，他是从Hive 0.7.0开始支持的。

1.1 简单表

hive>CREATE TABLE test(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t

1.2 创建外部表
EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,

     page_url STRING, referrer_url STRING,

     ip STRING COMMENT 'IP Address of the User',

     country STRING COMMENT 'country of origination')

 COMMENT 'This is the staging page view table'

 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'

 STORED AS TEXTFILE

 LOCATION '<hdfs_location>';

创建外部表

1.3 创建分区表
有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序，这样可以为特定应用提高性能。不过个人觉得分桶表分区表如果真的深究下去还是挺深的，以后有机会再写吧，在此只是分享下分区表常见使用方法。

--分区表
CREATE TABLE par_table(viewTime INT, userid BIGINT,

     page_url STRING, referrer_url STRING,

     ip STRING COMMENT 'IP Address of the User')

 COMMENT 'This is the page view table'

 PARTITIONED BY(date STRING, pos STRING)

ROW FORMAT DELIMITED ‘\t’

   FIELDS TERMINATED BY '\n'

STORED AS SEQUENCEFILE;

为方便理解，引用了[wisgood博主的一个观点]，值得参考。(http://blog.csdn.net/wisgood/article/details/17186591)
分区表实际是一个文件夹，表名即文件夹名。每个分区，实际是表名这个文件夹下面的不同文件。分区可以根据时间、地点等等进行划分。比如，每天一个分区，等于每天存每天的数据；或者每个城市，存放每个城市的数据。每次查询数据的时候，只要写下类似 where pt=2010_08_23这样的条件即可查询指定时间得数据。

当然还有10个小技巧可以分享下：

1. 创建表并创建索引字段ds

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

2.显示所有表

hive> SHOW TABLES;
hive>show databases;
hive>show partitions ;
hive>show functions;
hive>describe extended table_name dot col_name;

3.按正条件（正则表达式）显示表

SHOW TABLES '.*s';

4.为表添加列

hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

5.为表修改或删除列

ALTER TABLE name DROP [COLUMN] column_name  --删除既存的指定列
hive> ALTER TABLE employee DROP name;
ALTER TABLE name CHANGE column_name new_name new_type --修改指定列
hive> ALTER TABLE employee CHANGE name ename String;
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...]) --替换指定列
hive> ALTER TABLE employee REPLACE COLUMNS (eid INT empid Int,ename STRING name String);

6.更改表名

ALTER TABLE events RENAME TO 3koobecaf;

7.删除表

hive>DROP TABLE IF EXISTS employee;

8.查看表信息

hive>desc tabel_name; -- 显示表中的字段信息
hive>show partitions table_name;--查看表的分区信息，如不是分区表，则报错

9.增加/删除分区

alter table 表v_log_ods add partition (dt='2016-09-12');--增加分区
alter table v_log_ods drop partition(dt='2015-05-31');--删除分区

10.创建数据库

hive>CREATE DATABASE name;

更多信息可以参看