Hadoop Hive基础SQL语法(DDL 操作)_like关键字hadoop-CSDN博客

本文链接：https://blog.csdn.net/it_dx/article/details/52638850

介绍：
Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构
化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需
要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。而mapreduce开发人员可以把
己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。
它与关系型数据库的SQL 略有不同，但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机
online)事务处理，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。
HIVE的特点：可伸缩（在Hadoop的集群上动态的添加设备），可扩展，容错，输入格式的松散耦合。

1. DDL操作

1.1 DDL建表

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 。

1.1.2 创建简单表

CREATE TABLE pokes (foo INT, bar STRING);

1.1.3 创建外部表

CREATE EXTERNAL TABLE 表名(字段名 字段类型,字段名 字段类型 COMMENT '注释')COMMENT '表注释'ROW FORMAT DELIMITED FIELDS TERMINATED BY ','(在文件中保存的字段之间的分隔符) STORED AS TEXTFILE LOCATION 'HDFS绝对路径';

1.1.4 建分区表

CREATE EXTERNAL TABLE 表名(字段名 字段类型,字段名 字段类型 COMMENT '注释')COMMENT '表注释' PARTITIONED BY(分区字段名 分区字段类型, 分区字段名 分区字段类型)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'(在文件中保存的字段之间的分隔符)STORED AS SEQUENCEFILE;

1.1.5 建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 BUCKETS ROW FORMAT DELIMITED ‘\t’  FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

1.1.6 LIKE关键字复制一个空表

CREATE TABLE 表名 LIKE 被复制表名;

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

SHOW TABLE ‘正则表达式’

1.2 DDL修改表结构

1.2.1 表添加一列：

ALTER TABLE 表名 ADD COLUMNS (列名列类型);

1.2.2 添加一列并增加列字段注释

ALTER TABLE 表名 ADD COLUMNS (列名列类型 COMMENT ‘注释’);

1.2.3 更改表名：

ALTER TABLE 更改前表名 RENAME TO 更改后表名;

1.2.4 删除列：

ALTER TABLE person_in REPLACE COLUMNS（列名类型，列名类型）(需要保存的列);

1.2.5 增加、删除分区

•增加

ALTER TABLE 表名 ADD [IF NOT EXISTS] PARTITON [分区字段=值] [ LOCATION ‘location1’ ] PARTITON [分区字段=值] [ LOCATION ‘location2’ ] …

•删除

ALTER TABLE 表名 DROP PARTITON ([分区字段=值])…【注意要加括号】（删除多个分区，用‘，’分割）。

1.2.6 重命名表

ALTER TABLE 表名 RENAME TO 新表名；

1.2.7 修改列的名字、类型、位置、注释：

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名新列名列类型 [COMMENT ‘注释’] [FIRST|AFTER column_name]

　　这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

1.2.8 表添加一列：

ALTER TABLE 表名 ADD COLUMNS (列名列类型);

1.2.9 添加一列并增加列字段注释

ALTER TABLE 表名 ADD COLUMNS (列名列类型 COMMENT ’ 注释’);

1.3.0 增加/更新列

ALTER TABLE 表名 ADD|REPLACE COLUMNS (列名列类型 [COMMENT ‘注释’], …)

• ADD是代表新增一字段，字段位置在所有列后面(partition列前)
• REPLACE则是表示替换表中所有字段。

1.31 增加表的元数据信息

ALTER TABLE 表名 SET TBLPROPERTIES table_properties table_properties::[property_name = property_value…..]
•用户可以用这个命令向表中增加metadata

1.32 改变表文件格式与组织

ALTER TABLE 表名 SET FILEFORMAT 文件格式

•这个命令修改了表的物理存储属性

ALTER TABLE 表名 CLUSTERED BY(字段名) SORTED BY(字段名) INTO 桶号 BUCKETS

1.4 创建／删除视图

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

•删除视图