Hive语法--DDL、DML 详解＋实例

爱探险的小张

已于 2024-01-30 11:08:39 修改

阅读量834

点赞数 3

文章标签： hive hadoop 数据仓库

于 2023-07-15 20:45:44 首次发布

本文链接：https://blog.csdn.net/m0_63733763/article/details/131743777

版权

HQL数据定义语言（DDL）

数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。

Hive SQL（HQL）与SQL的语法大同小异，基本上是相通的，学过SQL的使用者可以快速上手Hive SQL。只需要特别要注意Hive特有的语法知识点，比如partition相关的DDL操作。

1. Hive建表语法树

蓝色字体是建表语法的关键字，用于指定某些功能。

[]中括号的语法表示可选。

|表示使用的时候，左右语法二选一。

建表语句中的语法顺序要和上述语法规则保持一致。

基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握DDL语法的重中之重。可以说建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。

1. Hive读写文件机制

想要完全掌握HQL的语法，就必须首先了解Hive的文件读写机制。

不同于Mysql等数据库，Hive 是建立在 Hadoop 生态系统之上的数据仓库工具。它使用分布式文件系统（如 HDFS）存储数据，并将数据划分为多个块进行处理。

在 Hive 中，数据通常以某种格式（如文本、Avro、Parquet 等）存储在文件系统中，而这些文件本身只是字节流。因此，掌握了Hive的文件读写机制，才能将文件系统中的数据，映射成为Hive中可以查询的数据结构。

而SerDe 在 Hive 中就是用于将数据在存储格式和内部数据结构之间进行转换的组件。SerDe 的作用就是将文件系统中的数据字节流解析成可以被 Hive 查询引擎理解的内部表示形式，或者将内部表示形式序列化为指定的输出格式。

2.1 SerDe与读写文件机制

SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。

Hive使用SerDe（和FileFormat）读取和写入行对象。

Hive读取文件机制：首先调用InputFormat（默认TextInputFormat），返回一条一条kv键值对记录（默认是一行一条记录）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔符切分为各个字段。

Hive写文件机制：将Row写入文件时，首先调用SerDe（默认LazySimpleSerDe）的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

2.2 SerDe相关语法 [ROW FORMAT]

在Hive的建表语句中，和SerDe相关的语法为：

[ROW FORMAT DELIMITED | SERDE serde_name ...]

其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。

如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

2.3 [ROW FORMAT DELIMITED]分隔符指定语法

LazySimpleSerDe是Hive默认的序列化方法，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

ROW FORMAT DELIMITED具体的子语法:

row format delimited

[fields terminated by char] #指定字段之间的分隔符

[collection items terminated by char] #指定集合元素之间的分隔符

[map keys terminated by char] #指定map类型kv之间的分隔符

[lines terminated by char] #指定换行符

为了帮助我们更好的理解分隔符指定语法，我们通过王者荣耀英雄数据建表过程来描述该语法。

--建表
create table t_hot_hero_skin_price(
     id int,
     name string,
     win_rate int,
     skin_price map<string,int>
)
row format delimited
fields terminated by ',' --字段之间分隔符
collection items terminated by '-' --集合元素之间分隔符
map keys terminated by ':'; --集合元素kv之间分隔符;

--上传数据
--hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/itheima.db/t_hot_hero_skin_price

select * from t_hot_hero_skin_price;

查询结果如下：

对照源数据格式：

以“,”作为字段之间分隔符，将源数据切分为四个字段。

以“-”作为集合元素之间分隔符，skin_price列，整体被识别为map数据。以“：”作为集合元素kv之间分隔符。

我们即可通过：select skin_price["至尊宝"] from t_hot_hero_skin_price limit 1;

获得第一行至尊宝皮肤的价格为888。

hive建表时如果没有row format语法。此时字段之间默认的分割符是'\001'，是一种特殊的字符。

在完成对Hive文件读写机制后，接下来将对HQL的建表语法进行详细解释并搭配实例进行演示：

演示部分将按照上述语法树结构顺序解释

1. 建表语法详解

3.1 [TEMPORARY] 临时表 [EXTERNAL] 外部表

参数解释如下：

TEMPORARY：可选参数，表示创建一个临时表。临时表只在当前会话中存在，会话结束后会被自动删除。

EXTERNAL：可选参数，表示创建一个外部表。外部表的数据存储在表外的位置，例如文件系统或其他数据库中。

使用这些参数可以根据需求来创建不同类型的表。例如：

create table name_1(name string,age int)：创建一个永久表name_1，数据存储在数据库内部，并持久保存。

create temporary table name_2(name string,age int):创建一个临时表name_2，数据只在当前会话中有效，会话结束后自动删除。

create external table name_3(name string,age int):创建一个外部表name_3，数据存储在表外的位置，例如文件系统或其他数据库中，不受数据库管理。

若要完全理解external 参数，我们引入内部表与外部表的概念。

3.1.1内部表与外部表介绍

在 Hive 中，内部表（Internal Table）和外部表（External Table）是两种不同的表概念。

内部表（Internal Table）是 Hive 默认创建的表类型。

——数据存储在 Hive 的默认存储位置（通常是 HDFS 或本地文件系统），并由 Hive完全管理表（元数据和数据）的生命周期。

——当删除内部表时，表的元数据和数据都将被删除。使用内部表时，对表的操作会直接修改或删除存储在默认位置上的数据。

——内部表适用于需要 Hive 管理数据的情况。Hive 自动管理其数据的生命周期，包括数据的添加、删除和清理。

外部表（External Table）：是指 Hive 不负责管理其数据的表。

——外部表的数据可以存储在任何位置，例如 HDFS、本地文件系统、HBase 或其他数据库中。创建外部表时，Hive 只会管理表的元数据，而不会移动、修改或删除存储在外部位置上的数据。

——当删除外部表时，只会删除表的元数据，而不会删除数据本身。

——外部表适用于不希望 Hive 管理数据的情况，或者需要与其他系统进行数据共享和交互的情况。外部表可以通过指定存储位置来引用数据，可以方便地与其他系统进行数据集成。

接下来通过建表实例介绍内部表与外部表的区别

3.1.2内部表、外部表实例

首先在Hive的iz1.db中创建内部与外部空白表，指定相同的字段与数据类型。

--创建内部表
create table student_inner(Sno int,Sname string,Sex string,Sage int,Sdept string)
row format delimited fields terminated by ',' ;
--通过external关键字创建外部表
create external table student_external(Sno int,Sname string,Sex string,Sage int,Sdept string)
row format delimited fields terminated by ',';
--注： row format delimited fields terminated by ','为后面内容，暂不解释。只需注意表的不同即可

--在linux系统上传数据文件到新建的内部表、外部表中
--hadoop fs -put students.txt /user/hive/warehouse/iz1.db/student_inner
--hadoop fs -put students.txt /user/hive/warehouse/iz1.db/student_external
分别查询发现两表内容没有任何区别，都能映射成功数据也都在HDFS上。

select * from student_inner;
select * from student_external;

--针对内部表、外部表进行drop删除操作
drop table student_inner; --内部表在删除的时候元数据和数据都会被删除
drop table student_external; --外部表在删除的时候只删除元数据而HDFS上的数据文件不会动

结果如上所示，内部表student_inner 已经被完全删除，但是student_external数据文件依旧还在，只是show tables;时，两表都不存在.

综上，我们不难发现无论内部表还是外部表，Hive都在Hive Metastore中管理表定义及其分区信息。删除内部表会从Metastore中删除表元数据，还会从HDFS中删除其所有数据/文件。

删除外部表，只会从Metastore中删除表的元数据，但保持HDFS位置中的实际数据不变。

在不确定内外表的情况下，可以通过命令desc formatted table_name;去查询表的元数据信息获取表的类型。

结果分类为：

MANAGED_TABLE 内部表、受控表

EXTERNAL_TABLE 外部表

3.2 [IF NOT EXISTS] 忽略建表存在异常

CREATE TABLE [IF NOT EXISTS] table_name

建表的时候，如果表名已经存在，默认会报错

Error while processing statement: 执行期间的错误往往就是逻辑错误

另：Error while compiling statement 编译期间的错误 SQL语法问题

通过IF NOT EXISTS关键字可以忽略异常。

3.3 [COMMENT ] 添加注释

在 Hive 中，可以利用COMMENT关键字来对表或表中的列进行注释。

具体解释如下：

COMMENT col_comment：可选项，对列进行注释或描述的内容。可以在表定义中添加对列的注释，以提供关于该列的额外信息和说明。

COMMENT table_comment：可选项，表示对表进行注释或描述的内容。

以下是一个示例，演示如何使用上述语法来注释表、列：

create table my_table (
id int,
name string comment 'This is the column for storing names.',
age int comment 'This column stores the age of individuals.'
)
comment 'This is a sample table for demonstration purposes.';

在上面的示例中，my_table 是表名，有三个列被定义：id、name 和 age。其中，name 和 age 列使用了注释来提供关于列的额外描述。我们同时可以为表 my_table 添加一条注释，用于描述这个表的用途或其他相关信息。

通过添加表级别的注释，可以让其他人更好地理解表的含义、用途或其他特定信息。这对于协作开发和维护大型数据仓库非常有用。

通过命令desc formatted table_name;可以查看列注释

在实际使用时，要根据需要选择合适的数据类型和提供适当的注释，以便更好地理解和使用表的列。

3.4[PARTITIONED BY] 分区表

create table table_name(col type,col2 type)

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。比如把一整年的数据根据月份划分12个月（12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

简单示例：

--分区表建表
create table t_all_hero_part(
  id int,
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
) partitioned by (role string)--注意哦这里是分区字段
row format delimited
fields terminated by "\t";

--查询分区表发现分区字段也显示出来了
select * from t_all_hero_part;

如上图需要注意：分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上，可以被我们select查询出来。

3.4.1分区表数据加载--静态分区

静态分区——分区的字段值是由用户在加载数据的时候手动指定的。

语法如下：

load data [local] inpath ' ' into table tablename partition(分区字段='分区值'...);

Local表示数据是位于本地文件系统还是HDFS文件系统。关于load语句后续详细展开讲解。

加载示例：

3.4.2分区表数据加载--动态分区

往hive分区表中插入加载数据时，如果需要创建的分区很多，则需要复制粘贴修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区。

动态分区指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert+select。插入的数据来自于后面的查询语句返回的结果。

查询返回的内容，其字段类型、顺序、个数要和待插入的表保持一致。

启用hive动态分区，需要在hive会话中设置两个参数：

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

第一个参数表示开启动态分区功能

第二个参数指定动态分区的模式。分为nonstick非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区。

案例演示：

--动态分区
--创建一张新的分区表 t_all_hero_part_dynamic
create table t_all_hero_part_dynamic(
   id int,
   name string,
   hp_max int,
   mp_max int,
   attack_max int,
   defense_max int,
   attack_range string,
   role_main string,
   role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";

--创建普通表
create table t_all_hero(
  id int,
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
)
row format delimited
fields terminated by "\t";

----上传文件到普通表
--hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt
-- /user/hive/warehouse/iz1.db/t_all_hero
select * from t_all_hero;

--动态分区nonstick非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
--执行动态分区插入分区表  --注意分区值并没有手动写死指定
insert into table t_all_hero_part_dynamic partition(role)
select tmp.*,tmp.role_main from t_all_hero tmp;

select * from t_all_hero_part_dynamic;

查询结果如下：已成功分区

查看HDFS文件路径，发现已经在该数据表目录下创建分区文件

在执行动态分区插入数据的时候，如果是严格模式strict，要求至少一个分区为静态分区怎模办？

partition(guojia="zhongguo",sheng) --第一个分区写死了（静态）符合严格模式。

partition(guojia,sheng) --两个分区都是动态确定的需要非严格模式

3.4.3分区表的注意事项

1.分区表的字段不能是表中已有的字段；分区的字段会显示在查询结果上；

2.分区的字段是虚拟的字段，出现在表所有字段的后面，其值来自于加载数据到表中的时候手动指定。

3.分区在底层的形式就是以文件夹管理不同的文件；不同文件夹就是表不同分区；文件夹的名字：

4.分区表是一种优化表，建表的时候可以不使用，但是，当==创建分区表之后，使用分区字段查询可以减少全表扫描，提高查询的效率==。

5.企业中常用的分区字段：地域维度：省，市、时间维度：day,month,year

3.4.5多重分区

通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：partitioned by(字段1 ，字段2....)

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。

案例演示：

--多重分区
--以国家、省创建分区表
create table t_user_double_p(id int,name string,country string) partitioned by(guojia string,sheng string)
   row format delimited fields terminated by ',';

--加载数据到多分区表中
load data local inpath '/root/hivedata/china_sh.txt' into table t_user_double_p
   partition(guojia="zhongguo",sheng="shanghai");

load data local inpath '/root/hivedata/china_sz.txt' into table t_user_double_p
   partition(guojia="zhongguo",sheng="shenzhen");

load data local inpath '/root/hivedata/usa_dezhou.txt' into table t_user_double_p
   partition(guojia="meiguo",sheng="dezhou");

--查询来自于中国深圳的用户有哪些？
select * from t_user_double_p where guojia="zhongguo"and sheng="shenzhen";

查询结果如上

3.5 [CLUSTERED BY ] 分桶表

分桶表也叫做桶表，源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。

--分桶表建表语句
CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name) SORTED BY (col_name asc |desc)
INTO N BUCKETS;

clustered by和sorted by是 Hive 中用于定义表的数据分桶和排序的语法关键字。

CLUSTERED BY 关键字用于指定基于哪些列对表进行数据分桶。

SORTED BY 在 CLUSTERED BY 的基础上指定了每个桶内的数据排序方式。

INTO N BUCKETS表示分为几桶（也就是几个部分）。

分桶的字段必须是表中已经存在的字段。

通过数据分桶，可以提高查询性能，减少数据的扫描范围。例如，可以选择将用户表按照用户ID进行分桶，这样相同用户ID的数据会被分配到同一个桶中。

3.5.1 分桶表的创建

--分桶表创建
CREATE TABLE t_usa_covid19_bucket(
     count_date string,
     county string,
     state string,
     fips int,
     cases int,
     deaths int)
clustered by (state) into 5 buckets; --分桶的字段一定要是表中已经存在的字段

--根据state州分为5桶每个桶内根据cases确诊病例数倒序排序
CREATE TABLE t_usa_covid19_bucket_sort(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int)
CLUSTERED BY(state)
sorted by (cases desc) INTO 5 BUCKETS;--指定每个分桶内部根据 cases倒序排序

--step2:把源数据加载到普通hive表中
CREATE TABLE t_usa_covid19(
      count_date string,
      county string,
      state string,
      fips int,
      cases int,
      deaths int)
row format delimited fields terminated by ",";

--将源数据上传到HDFS，t_usa_covid19表对应的路径下
--hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/iz1.db/t_usa_covid19

--step3:使用insert+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;
select * from t_usa_covid19_bucket;
insert into t_usa_covid19_bucket_sort select * from t_usa_covid19;
select * from t_usa_covid19_bucket_sort;

3.5.2 分桶表的优点

通过使用 CLUSTERED BY 和 SORTED BY，可以优化 Hive 表的数据存储方式，提高查询性能和效率。

和非分桶表相比，分桶表的使用好处有以下几点：

基于分桶字段查询时，减少全表扫描

--基于分桶字段state查询来自于New York州的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York) mod 5计算出分桶编号
--查询指定分桶里面的数据就可以找出结果此时是分桶扫描而不是全表扫描
select *
from t_usa_covid19_bucket where state="New York";

JOIN时可以提高MR程序效率，减少笛卡尔积数量

对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了分桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

分桶表数据进行抽样

当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

总结：

1.分桶表也是一种优化表，可以减少join查询时笛卡尔积的数量、提高抽样查询的效率。

2.分桶表的字段必须是表中已有的字段；

3.分桶表需要使用间接的方式才能把数据加载进入：insert+select

4.在join的时候，针对join的字段进行分桶，可以提高join的效率减少笛卡尔积数量。

3.6 [STORED AS file_format] 指定存储格式

STORED AS 是 Hive 中用于指定表的存储格式的语法。

在 Hive 中，可以将表的数据以不同的存储格式进行存储，例如文本格式（TextFile）、压缩格式（SequenceFile、ORC、Parquet 等）等。每种存储格式都有其自身的特点和适用场景。

STORED AS 关键字用于指定表的存储格式，后面跟随具体的文件格式参数。常见的文件格式参数包括：

STORED AS TEXTFILE：以文本格式存储，默认为行文本格式。

STORED AS SEQUENCEFILE：以序列化文件格式（SequenceFile）存储。

STORED AS RCFILE：以行列存储格式（RCFile）存储。

STORED AS ORC：以优化的列式存储格式（ORC）存储。

STORED AS PARQUET：以高性能列式存储格式（Parquet）存储。

STORED AS AVRO：以 Avro 格式存储。

示例：

--指定数据ORC存储格式
CREATE TABLE my_table (
col1 INT,
col2 STRING
)
STORED AS ORC;

创建了一个名为 my_table 的表，并将其数据以 ORC 格式进行存储。

通过使用 STORED AS，可以根据具体需求选择合适的文件格式来存储表的数据，在查询和分析时提供更高的性能和效率。不同的存储格式可能在数据压缩、列式存储、读写性能等方面有所差异，需要根据具体场景进行选择。

3.7 [LOCATION hdfs_path] 指定数据存储路径

LOCATION 是 Hive 中用于指定表数据存储路径的语法。

Hive表默认存储路径是由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定。默认值是：/user/hive/warehouse。

在该路径下，文件将根据所属的库、表，有规律的存储在对应的文件夹下。

通过 LOCATION 关键字可以手动指定数据的存储路径。

LOCATION 关键字后面跟随具体的 HDFS 路径参数，指定表数据的存储路径。HDFS 路径可以是绝对路径或相对路径。

下面是一个示例：

CREATE TABLE my_table (
col1 INT,
col2 STRING
)
LOCATION '/user/hive/warehouse/my_table';

以上语句创建了一个名为 my_table 的表，并将其数据存储在 HDFS 中的 /user/hive/warehouse/my_table 路径下。

通过使用 LOCATION，可以将表的数据存储在指定的路径下，而不是默认的 Hive 仓库目录中。这样可以更加灵活地管理表的数据存储位置，例如可以将数据存储在指定的分区目录下，或者直接引用已有的数据文件。注意，使用 LOCATION 指定的路径必须存在且可被 Hive 访问。

3.8 [TBLPROPERTIES ] 表属性

TBLPROPERTIES 是 Hive 中用于为表设置属性的语法。

在 Hive 中，可以通过 TBLPROPERTIES 关键字为表设置各种属性。属性是键值对形式的信息，用于描述表的特性、元数据或其他自定义信息。

TBLPROPERTIES 关键字后面跟随一组以逗号分隔的属性键值对，每对键值对之间使用等号连接。键值对中的键和值可以是字符串或数字。例如：TBLPROPERTIES ('key1'='value1', 'key2'='value2', ...)。

下面是一个示例：

--指定表属性
CREATE TABLE my_table (
col1 INT,
col2 STRING
)
TBLPROPERTIES ('comment'='This is my table', 'owner'='binjie09');

以上语句创建了一个名为 my_table 的表，并为表设置了两个属性，分别是 comment 和 owner。

通过使用 TBLPROPERTIES，可以根据需要向表添加任意数量和类型的属性。这些属性可以用于描述表的特定信息，例如表的注释、所有者、来源等。在查询和管理表时，可以使用这些属性来过滤、分类或标记表。还可以使用 SHOW TABLE EXTENDED 等命令来获取表的属性信息。

需要注意的是，不同的存储格式和引擎可能支持不同的表属性，具体的属性定义和用法取决于所使用的存储格式和引擎。

1. Hive DDL其他语法

因为Hive建表、加载数据及其方便高效；在实际的应用中，如果建表有问题，通常可以直接drop删除重新创建加载数据。时间成本极低。

4.1、Database 数据库 DDL操作

--ddl数据库操作
--创建数据库
create database if not exists itcast
comment "this is my first db"
with dbproperties ('createdBy'='Allen');
创建一个名为 "itcast" 的数据库。如果 "itcast" 数据库不存在，则会创建该数据库；如果已经存在，则不会执行任何操作。

数据库的注释被设置为 "this is my first db"。

此外，使用 WITH DBPROPERTIES 关键字和键值对的形式，可以为数据库设置属性。在示例中，为数据库设置了一个 "createdBy" 属性，其值为 "Allen"。

--描述数据库信息
describe database itcast;
describe database extended itcast;
desc database extended itcast;

--切换数据库
use default;
use itcast;
create table t_1(id int);

--删除数据库
--注意 CASCADE关键字慎重使用
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
drop database itcast cascade ;

删除名为 "itcast" 的数据库，同时使用了 CASCADE 关键字来指定级联删除。级联删除将会删除数据库下的所有表。

请注意，级联删除操作将不可逆转，并且会删除数据库中的所有表及其数据。在执行此操作之前，请确保您理解该操作的影响，并谨慎操作。

--更改数据库属性
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
--更改数据库所有者
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
--更改数据库位置
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

4.2、Table 表 DDL操作

--下面这两个需要特别记住
--查询指定表的元数据信息
desc formatted table_name;#返回名为 "table_name" 的表的详细信息

show create table table_name;#查看表的建表语句

--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
--移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

--4、更改表的文件存储格式该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table_name SET FILEFORMAT file_format;
--5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";

--6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is: b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is: c1 int, b int, a2 string.
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

--7、添加/替换列
--使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
--REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);

4.3、Partition分区 DDL操作

比较重要的是增加分区、删除分区操作

--1、增加分区
--step1: 创建表手动加载分区数据
drop table if exists t_user_province;
create table t_user_province (
   num int,
   name string,
   sex string,
   age int,
   dept string) partitioned by (province string);

load data local inpath '/root/hivedata/students.txt' into table t_user_province partition(province ="SH");

--step2:手动创建分区的文件夹且手动上传文件到分区中绕开了hive操作发现hive无法识别新分区
hadoop fs -mkdir /user/hive/warehouse/itheima.db/t_user_province/province=XM
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/t_user_province/province=XM

--step3：修改hive的分区，添加一个分区元数据
ALTER TABLE t_user_province ADD PARTITION (province='XM') location
'/user/hive/warehouse/itheima.db/t_user_province/province=XM';
----此外还支持一次添加多个分区
ALTER TABLE table_name ADD

PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';

--2、重命名分区
ALTER TABLE t_user_province PARTITION (province ="SH") RENAME TO PARTITION (province ="Shanghai");

--3、删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE; --清除直接删除数据不进垃圾桶有点像skipTrash

--4、修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

--5、修改分区
--更改分区文件存储格式
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format;
--更改分区位置
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";

Hive SQL中常见的show语法

Show相关的语句提供了一种查询Hive metastore的方法。可以帮助用户查询相关信息。

--1、显示所有数据库 SCHEMAS和DATABASES的用法功能一样
show databases;
show schemas;

--2、显示当前数据库所有表/视图/物化视图/分区/索引
show tables;
SHOW TABLES [IN database_name]; --指定某个数据库

--3、显示当前数据库下所有视图
Show Views;
SHOW VIEWS 'test_*'; -- show all views that start with "test_"
SHOW VIEWS FROM test1; -- show views from database test1
SHOW VIEWS [IN/FROM database_name];

--4、显示当前数据库下所有物化视图
SHOW MATERIALIZED VIEWS [IN/FROM database_name];

--5、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
show partitions table_name;

--6、显示表/分区的扩展信息
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name;
show table extended like student;

--7、显示表的属性信息
SHOW TBLPROPERTIES table_name;
show tblproperties student;

--8、显示表、视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);
show create table student;

--9、显示表中的所有列，包括分区列。
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];
show columns in student;

--10、显示当前支持的所有自定义和内置的函数
show functions;

--11、Describe desc
--查看表信息
desc extended table_name;
--查看表信息（格式化美观）
desc formatted table_name;
--查看数据库相关信息
describe database database_name;

HQL数据操纵语言（DML）

3.1 DML-Load加载数据

当在Hive中创建好表之后，默认就会在HDFS上创建一个与之对应的文件夹，默认路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse。

要想让hive的表和结构化的数据文件产生映射，就需要把文件移到到表对应的文件夹下面，当然，可以在建表的时候使用location语句指定数据文件的路径。但是不管路径在哪里，必须把数据文件移动到对应的路径下面。

最直接的方式就是使用hadoop fs –put等方式将数据移动到路径下面。

Hive官方推荐使用Load命令将数据加载到表中。

在将数据load加载到表中时，Hive不会进行任何转换。

加载操作是将数据文件移动到与Hive表对应的位置的纯复制/移动操作。

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE]--是否覆盖
INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

filepath表示的待移动数据的路径，可以引用一个文件（Hive将文件移动到表中），也可以是一个目录（Hive将把该目录中的所有文件移动到表中）。

相对路径，例如：project/data1

绝对路径，例如：/user/hive/project/data1

具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

LOCAL表示是从本地文件系统加载数据到Hive的表中。

注意，如果对HiveServer2服务运行此命令。这里的本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。

如果没有指定LOCAL关键字，如果filepath指向的是一个完整的URI，hive会直接使用这个URI。否则如果没有指定schema或者authority，Hive会使用在hadoop配置文件中定义的schema 和 authority，即参数fs.default.name指定的（不出意外，都是HDFS）。

OVERWRITE关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。 --覆盖

3.1.1加载数据案例演示

--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS 用于演示从HDFS加载数据到非分区表
create table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS_p 用于演示从HDFS加载数据到分区表
create table student_HDFS_p(num int,name string,sex string,age int,dept string) partitioned by(country string) row format delimited fields terminated by ',';

--step2:加载数据
-- 从本地加载数据数据位于HS2（node1）本地文件系统本质是hadoop fs -put上传操作
--Loading data to table itheima.student_local from file:/root/hivedata/students.txt
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;

--从HDFS加载数据数据位于HDFS文件系统根目录下本质是hadoop fs -mv 移动操作
--Loading data to table itheima.student_hdfs from hdfs://node1:8020/stu/students.txt
load data inpath '/stu/students.txt' into table student_hdfs;

--先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;

----从HDFS加载数据到分区表中并指定分区数据位于HDFS文件系统根目录下
--先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS_p partition(country ="China");

3.2 DML-Insert插入数据

在MySQL等RDBMS中，通常是insert+values的方式来向表插入数据，并且速度很快。这也是RDBMS中插入数据的核心方式。

如果把Hive也使用insert+values的方式插入数据，你会发现执行过程非常非常慢，因为Hive底层是使用MapReduce把数据写入HDFS的。

因此在Hive中我们通过将数据清洗成为结构化文件，再Load加载到表中。

但是并不意味着insert语法在Hive中没有使用地位了，通常在Hive中我们使用insert+select语句。即插入表的数据来自于后续select查询语句返回的结果。

3.2.1 insert + select

Hive中insert主要是结合select查询语句使用，将查询结果插入到表中

保证后面select查询语句返回的结果字段个数、类型、顺序和待插入表一致；

如果不一致，Hive会尝试帮你转换，但是不保证成功；

insert+select也是在数仓中ETL数据常见的操作。

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;

select * from student;

--step2：创建一张目标表只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert
select num,name from student;

select * from student_from_insert;

3.2.2 multiple inserts多重插入

multiple inserts可以翻译成为多次插入，多重插入，核心是：一次扫描，多次插入。其功能也体现出来了就是减少扫描的次数。

--普通插入：
insert into table test_insert1 select id from source_table;
insert into table test_insert2 select name from source_table;

--在上述需求中从同一张表扫描了2次分别插入不同的目标表中性能低下。

--多重插入：
from source_table
insert overwrite table test_insert1
select id
insert overwrite table test_insert2
select name;
--只需扫描一次表分别把不同字段插入到不同的表中减少扫描次数提高效率

3.2.3 dynamic partition insert动态分区插入

1、如果是在加载数据的时候人手动写死指定的叫做静态分区

load data local inpath '/root/hivedata/usa_dezhou.txt' into table t_user_double_p partition(guojia="meiguo",sheng="dezhou");

2、如果是通过insert+select 动态确定分区值的，叫做动态分区

insert table partition (分区字段) +select

create table student(num int,name string,sex string,age int,dept string)
row format delimited fields terminated by ',' ;
load data local inpath '/root/hivedata/students.txt' into table student;

--动态分区插入
--1、首先设置动态分区模式为非严格模式默认已经开启了动态分区功能
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
--2、当前库下已有一张表student
select * from student;
--3、创建分区表以sdept作为分区字段
create table student_partition(Sno int,Sname string,Sex string,Sage int) partitioned by(Sdept string);
--4、执行动态分区插入操作
insert into table student_partition partition(Sdept)
select num,name,sex,age,dept Sdept from student;
--其中，num,name,sex,age作为表的字段内容插入表中
--Sdept作为分区字段值

select *from student_partition;
show partitions student_partition;

3.2.4 insert + directory导出数据

Hive支持将select查询的结果导出成文件存放在文件系统中。语法格式如下：

--标准语法:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
SELECT ... FROM ...

--Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

--row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

注意，导出操作是一个OVERWRITE覆盖操作。慎重。

目录可以是完整的URI。如果未指定scheme或Authority，则Hive将使用hadoop配置变量fs.default.name中的方案和Authority，该变量指定Namenode URI。

如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录。

写入文件系统的数据被序列化为文本，列之间用^ A隔开，行之间用换行符隔开。如果任何列都不是原始类型，那么这些列将序列化为JSON格式。也可以在导出的时候指定分隔符换行符和文件格式。

--当前库下已有一张表student
select * from student;

--1、导出查询结果到HDFS指定目录下
insert overwrite directory '/tmp/hive_export/e1' select num,name,age from student limit 2; --默认导出数据字段之间的分隔符是\001

--2、导出时指定分隔符和文件存储格式
insert overwrite directory '/tmp/hive_export/e2' row format delimited fields terminated by ','
stored as orc
select * from student;

--3、导出数据到本地文件系统指定目录下
insert overwrite local directory '/root/hive_export/e1' select * from student;

爱探险的小张

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
2
评论
Hive语法--DDL、DML 详解＋实例

Hive SQL（HQL）与SQL的语法大同小异，基本上是相通的，学过SQL的使用者可以快速上手Hive SQL。只需要特别要注意Hive特有的语法知识点，比如partition相关的DDL操作。本文将通过一篇文章梳理HQL中的关键——建表语句，以及其他DDL和DML语句
复制链接

扫一扫