Hive语法详解

先看看Hive的官方介绍：Hive是一个数据仓库基础工具在Hadoop中用来处理结构化数据。它架构在Hadoop之上，总归为大数据，并使得查询和分析方便。并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。

废话不多说，下面从DDL,DML,DQL三种操作来介绍Hive语法规则。

1.DDL 操作

1.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]

• 如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
• EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（HDFS_LOCATION）。使用external建表和普通建表区别：前者存放元数据，删除后文件系统中的数据不会删除，后者会直接删除文件系统中的数据
• LIKE 允许用户复制现有的表结构（不带数据）
• COMMENT可以为表与字段增加描述
• PARTITIONED BY关键字设置分区字段
• CLUSTERED BY一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。
• SORTED BY对数据进行排序。这样可以为特定应用提高性能。
• ROW FORMAT
  关键字，是用来设置创建的表在加载数据的时候，支持的列分隔符。
  用户在建表的时候可以自定义 SerDe （Serialize/Deserilize的简称,目的是用于序列化和反序列化）或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

 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, ...)]

• STORED AS
  关键字是用来设置加载数据的数据类型。
  Hive本身支持的文件格式只有：TextFile，Sequence File。
  如果文件数据是纯文本，可以使用 [STORED AS TEXTFILE]。
  如果数据需要压缩，使用[STORED AS SEQUENCE] 。
  通常情况，只要不需要保存序列化的对象，我们默认采用[STORED AS TEXTFILE]

 SEQUENCEFILE| TEXTFILE| RCFILE    | INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname

建表示例：

//1.创建简单表hive> CREATE TABLE pokes (foo INT, bar STRING);//2.创建外部表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 TEXTFILELOCATION '<hdfs_location>';//3.建立分区表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 fields terminated by '\t'lines terminated by '\n'STORED AS SEQUENCEFILE;//4.建立bucket表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)CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETSROW FORMAT DELIMITED fields terminated by '\t'lines terminated by '\n'STORED AS SEQUENCEFILE;//5.创建表并创建索引字段dshive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);//6.复制一个空表CREATE TABLE empty_key_value_store LIKE key_value_store;//7.显示所有表hive> SHOW TABLES;hive> SHOW TABLES '.*s';

改表示例

#1.表添加一列：hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);#2.添加一列并增加列字段注释hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');#3.更改表名：hive> ALTER TABLE events RENAME TO 3koobecaf;#4.删除表中的数据（删除一个内部表的同时会同时删除表的元数据和数据。删除一个外部表，只删除元数据而保留数据）：hive> DROP TABLE pokes;#5.增加、删除分区#•增加ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...partition_spec:: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)#•删除ALTER TABLE table_name DROP partition_spec, partition_spec,...#6.重命名表ALTER TABLE table_name RENAME TO new_table_name #7.修改列的名字、类型、位置、注释：ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]#这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合#8.表添加一列：hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);#9.添加一列并增加列字段注释hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');#10.增加/更新列ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)  #• ADD是代表新增一字段，字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段。#11.增加表的元数据信息ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties::[property_name = property_value…..] #用户可以用这个命令向表中增加metadata#12.改变表文件格式与组织ALTER TABLE table_name SET FILEFORMAT file_formatALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS#这个命令修改了表的物理存储属性#13.创建／删除视图CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT...•增加视图•如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成•如果修改基本表的属性，视图中不会体现，无效查询将会失败•视图是只读的，不能用LOAD/INSERT/ALTER•DROP VIEW view_name•删除视图#14.创建数据库CREATE DATABASE name#15.显示命令•show tables;•show databases;•show partitions ;•show functions•describe extended table_name dot col_name