【Hive】hive基本操作 ddl dml

最新推荐文章于 2024-04-11 08:00:00 发布
最新推荐文章于 2024-04-11 08:00:00 发布
阅读量706 收藏 6
点赞数
分类专栏： Hadoop Hive 文章标签： hive hadoop ddl dml
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jiajane/article/details/103787366
版权

文章目录


注意：hive 不区分大小写

1. ddl 操作

1.1库操作

1.1.1 创建库

语法：

 CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
 [COMMENT database_comment]
 [LOCATION hdfs_path]
 [WITH DBPROPERTIES (property_name=property_value, ...)];

用法：

  1. 创建普通库

    hive> create database firstdb;

  2. 创建库的时候检查存与否

    hive> create databse if not exists seconddb;

  3. 创建库的时候带注释

    hive> create database if not exists thirdDb comment 'create my db named 3db';
OK
Time taken: 0.031 seconds
hive> desc database thirdDb;
OK
thirddb	create my db named 3db	hdfs://hdp01:9000/user/hive/warehouse/thirddb.db	hdp01	USER	
Time taken: 0.014 seconds, Fetched: 1 row(s)

  4. 创建带属性的库

    hive> create database if not exists fourthDb with dbproperties('a'='aaa','b'='bbb');

    但是不知道怎么查看库属性？有知道的请留言，多谢

1.1.2 查看库

  1. 查看有哪些数据库

    hive> show databases;
OK
default
firstdb
fourthdb
seconddb
thirddb
Time taken: 0.009 seconds, Fetched: 5 row(s)

  2. 显示数据库的详细属性信息
    语法：

    desc database [extended] dbname;

    示例：

    hive> desc database extended fourthdb;

  3. 查看正在使用哪个库

    hive> select current_database();
OK
default
Time taken: 0.843 seconds, Fetched: 1 row(s)

  4. 查看创建库的详细语句

    hive> show create database fourthdb;
OK
CREATE DATABASE `fourthdb`
LOCATION
  'hdfs://hdp01:9000/user/hive/warehouse/fourthdb.db'
WITH DBPROPERTIES (
  'a'='aaa', 
  'b'='bbb')
Time taken: 0.013 seconds, Fetched: 6 row(s)

1.1.3 切换库

hive> use fourthdb;

1.1.4 删除库

drop database dbname;
drop database if exists dbname;

默认情况下，hive 不允许删除包含表的数据库，有两种解决办法：

  1. 手动删除库下所有表，然后删除库

  2. 使用 cascade 关键字

    drop database if exists dbname cascade;

    默认情况下就是 restrict
    drop database if exists myhive ==== drop database if exists myhive restrict

1.2 表操作

1.2.1 创建表

1.2.1.0 建表语法及解释

建表语法：

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 EXISTS 选项来忽略这个异常。
EXTERNAL： 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的
路径（LOCATION），如果不存在，则会自动创建该目录。Hive 创建内部表时，会将数据
移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置
做任何改变。

在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删
除数据。（经典面试问题）

外部表和内部表的选择：

  1. 如果数据已经存储在 HDFS 上了，然后需要使用 Hive 去进行分析，并且该份数据还
    有可能要使用其他的计算引擎做计算之用，请使用外部表
  2. 如果一份数据仅仅只是使用 Hive 做统计分析，那么可以使用内部表
    不管使用内部表和外部表，表的数据存储路径都是可以通过 location 指定的！！！！！！

推荐方式：

  1. 创建内部表的时候，最好别指定 location，就存储在默认的仓库路径
  2. 如果要指定外部路径，那么请创建该表为外部表

PARTITIONED BY:
在 Hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。
有时候只需要扫描表中关心的一部分数据，因此建表时引入 partition 概念。
一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下
分区是以字段的形式在表结构中存在，通过 desc table 命令可以查看到字段存在，但是
该字段不存放实际的数据内容，仅仅是分区的表示。（即分区字段不能出现在表字段中）
分区建表分为 2 种：
一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录
一种是多分区，表文件夹下出现多文件夹嵌套模式

LIKE： 允许用户复制现有的表结构，但是不复制数据。
示例：create table tableA like tableB（创建一张 tableA 空表复制 tableB 的结构）

COMMENT： 可以为表与字段增加描述

ROW FORMAT：

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

fields terminated by 列
lines terminated by ‘\n’ 行 默认的行分割符\n
collection items terminated by 集合元素分割符
map keys terminated by map集合key-v 分隔符

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。
如果没有指定ROW FORMAT或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。
在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

STORED AS TEXTFILE | SEQUENCEFILE | RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE，默认也是 textFile 格式，可以通
过执行命令 set hive.default.fileformat，进行查看，如果数据需要压缩，使用 STORED AS
SEQUENCEFILE。

  1. 默认格式 TextFile，数据不做压缩，磁盘开销大，数据解析开销大。可结合 gzip、bzip2
    使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive 不会对数据进行切
    分，从而无法对数据进行并行操作
  2. SequenceFile 是 Hadoop API 提供的一种二进制文件支持，文件内容是以序列化的 kv
    对象来组织的，其具有使用方便、可分割、可压缩的特点。 SequenceFile 支持三种压缩
    选择：NONE，RECORD，BLOCK。Record 压缩率低，一般建议使用 BLOCK 压缩
  3. RCFILE 是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个
    record 在一个块上，避免读一个记录需要读取多个 block。其次，块数据列式存储，有利
    于数据压缩和快速的列存取。相比 TEXTFILE 和 SEQUENCEFILE，RCFILE 由于列式存储方式，
    数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次
    写入、多次读取，因此，整体来看，RCFILE 相比其余两种格式具有较明显的优势

CLUSTERED BY
对于每一个表（table）或者分区，Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表（或者分区）组织成桶（Bucket）有两个理由：

  1. 获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map Join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN操作就可以，可以大大较少 JOIN 的数据量。
  2. 使取样（Sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。（经典面试题）

SORTED BY 指定每一个桶中的排序字段及规则的
asc 升序 desc 降序
INTO num_buckets BUCKETS 指定分桶个数

LOCATION： 指定数据文件存放的 HDFS 目录，不管内部表还是外表，都可以指定。不指
定就在默认的仓库路径。
hive表在hdfs的存储路径：

  1. hive-default.xml中 /user/hive/warehouse
  2. hive-site.xml 配置了 /user/hadoop/hive
  3. Location指定

加载顺序 1. 2. 3,最后加载的最终生效
如果在这里不指定LOCATION 存储在hive-site.xml中配置的路径下
如果指定了 则覆盖hive-site.xml中的
hdfs_path 给定的是hdfs上的一个目录
最佳实践：
如果创建内部表请不要指定 location
如果创建表时要指定 location，请创建外部表。

1.2.1.1 创建内部表

创建一个库

hive> create database studentdb;
OK
Time taken: 0.037 seconds
hive> use studentdb;

创建表不指定存储位置，默认存储在/user/hive/warehouse/studentdb.db/stu_managed

create table if not exists stu_managed(
id string,
name string,
sex string,
age int,
dept string)
row format delimited
fields terminated by ','
stored as textfile;

创建表时，为表指定存储位置

create table if not exists stu_managed01(
id string,
name string,
sex string,
age int,
dept string)
row format delimited
fields terminated by ','
stored as textfile
location 'hdfs://hdp01:9000/user/hivetest/stu01';
1.2.1.2 创建外部表
create external table if not exists stu_external(
id int,
name string,
sex string,
age int,
dept string)
row format delimited
fields terminated by ','
stored as textfile;
1.2.1.3 创建分区表

选定分区字段dept

create external table if not exists stu_ptn(
id int,
name string,
sex string,
age int)
partitioned by (dept string)
row format delimited
fields terminated by ',';

注意dept不能出现在表字段中，否则报错，如下：

hive> create external table if not exists stu_ptn(
    > id int,
    > name string,
    > sex string,
    > age int,
    > dept string)   ## dept不能出现在这里
    > partitioned by (dept string)
    > row format delimited
    > fields terminated by ',';
FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns
1.2.1.4 创建分桶表
create table if not exists stu_buk(
id int,
name string,
sex string,
age int,
dept string)
clustered by (age) sorted by (id desc) into 3 buckets
row format delimited
fields terminated by ',';
1.2.1.5 表复制

使用 like 关键字拷贝表,
只能复制表结构（字段信息） 不能复制表中的数据的以及表的属性

  1. 不管老表是内部表还是外部表，new_table 都是内部表

    create table stu_ptn_copy2 like stu_ptn;

  2. 不管老表是内部表还是外部表，如果加 external 关键字，new_table 都是外部表

    create external table stu_ptn_copy like stu_ptn;

在这里插入图片描述

1.2.1.6 ctas建表

在实际情况中，表的输出结果可能太多，不适于显示在控制台上，这时候，将 Hive 的查询输出结果直接存在一个新的表中是非常方便的，我们称这种情况为 CTAS
将查询语句的结果存储位一张表

CREATE TABLE mytest AS SELECT name, age FROM test;

注意：CTAS 操作是原子的，因此如果 select 查询由于某种原因而失败，新表是不会创建的！

1.2.2 修改表

1.2.2.1 重命名表

有两种方法：

  1. 通过语句
    hive> alter table stu_ptn_copy2 rename to stu_ptn_rn1;
hive> show tables;
OK
stu_buk
stu_external
stu_managed
stu_managed01
stu_ptn
stu_ptn_copy
stu_ptn_rn1  ##改过来了哈
  2. 修改元数据表TBLS
    在这里插入图片描述
    回到hive查看表
    hive> show tables;
OK
stu_buk
stu_external
stu_managed
stu_managed01
stu_ptn
stu_ptn_copy
stu_ptn_rn2   ##已经重命名了
Time taken: 0.018 seconds, Fetched: 7 row(s)
1.2.2.2 修改表属性

语法：

ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties: (property_name = property_value, property_name = property_value, ... )

示例：

hive> alter table stu_ptn_rn2 set tblproperties('a'='aaa','b'='bbb');

注意：

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = 'my new students table');

不支持修改表名，和表的数据存储目录

1.2.2.3 修改 SerDe 信息

语法：

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH
SERDEPROPERTIES serde_properties];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES
serde_properties;
serde_properties:
 : (property_name = property_value, property_name = property_value, ... )

实例：
更改列分隔符

ALTER TABLE student SET SERDEPROPERTIES ('field.delim' = '-');
1.2.2.4 增加/删除/改变/替换列

  1. 增加列
    语法：

    ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])

    （注意：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)
    示例：

    hive> alter table stu_ptn_rn2 add columns(class string,address string);
OK
Time taken: 0.107 seconds
hive> desc stu_ptn_rn2;
OK
id                  	int                 	                    
name                	string              	                    
sex                 	string              	                    
age                 	int                 	                    
class               	string        ## 添加列      	                    
address             	string        ## 添加列      	                    
dept                	string        ## 该列是分区例，新列在此列前，原有列之后      	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
dept                	string

  2. 删除列
    不支持

  3. 改变列
    语法：

    ALTER TABLE name CHANGE c_name new_name new_type [FIRST|AFTER c_name]

    示例：

    # 修改列名
hive> alter table stu_ptn_rn2 change class grade string;
# 修改列类型
hive> alter table stu_ptn_rn2 change grade grade string;

  4. 替换列
    语法：

    ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

    注意：REPLACE 表示替换表中所有字段）
    示例：

    hive> alter table stu_ptn_rn2 replace columns
最低0.47元/天 解锁文章
时间的美景
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive之DML数据操作
01-07
Hive简介：https://blog.csdn.net/weixin_45102492/article/details/104418259 Hive安装部署 ： ...Hive之DDL数据定义： https://blog.csdn.net/weixin_45102492/article/details/91349323 Hive
Error: Error while compiling statement: FAILED: SemanticException [Error 10035]: Column repeated in
热门推荐
qq_33732952的博客
06-22 2万+
Error: Error while compiling statement: FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns (state=42000,code=10035) 错的解读：Error: Error while compiling statement 这里的 意思是...
HIVE元数据表结构
16岁工科狗想当歌手
04-10 519
hive
Hive分区partition详解
maenlai0086的博客
06-24 990
Hive分区更方便于数据管理，常见的有时间分区和业务分区。 下面我们来通过实例来理解Hive分区的原理。 一、单分区操作 1.创建分区表 create table t1( id int ,name string ,hobby array<string> ,add map<String,string> ) p...
Hive之深入了解元数据
weixin_45666566的博客
01-07 6522
元数据引言1、Hive 元数据2、Hive 元数据表2.1 Hive 库2.2 VERSION 表2.3 Hive数据库相关的元数据表2.3.1 DBS—数据库表2.3.2 DATABASE_PARAMS表—数据库参数表2.4 Hive数据表相关的元数据表2.4.1 TBLS表—数据表的元数据表2.4.2 TABLE_PARAMS表—数据表属性信息表2.4.3 TBL_PRIVS—授权表2.5 Hive文件存储信息相关的元数据表2.5.1 SDS表2.5.2 SD_PARAMS表2.5.3 SERDES表
hive元数据表tbls、parts、sds
王小禾
10-17 3518
hive原数据表hive_mysql_partitions、hive_mysql_partitions、hive_mysql_partitions关系解析
Hive DDL 之 表
01-07
创建表 语法： 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], …)...
Hive DML
01-07
Hive不支持update的操作。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。 load：加载数据到表 load data [local] inpath ‘filepath’ overwrite | into table 表名 [partition ...
Hive-工具篇_hive_
10-04
3. **Hive的DDL（Data Definition Language）** - **创建表**：定义表结构，包括表名、列名和列类型，还可以指定分区。 - **修改表**：可以添加、删除或修改列，也可以更改表的属性，如分区。 - **删除表**：Hive...
hive笔记-静态分区和动态分区
然然然星人的博客
01-07 2950
1、静态分区CREATE TABLE emp_dept_partition(empno int,ename string,job string,mgr int,hiredate string,sal double,comm double) PARTITIONED BY (deptno int)ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" ;备注：静态...
hive建表的时候字段和分区字段重复
mayday555525的博客
10-23 293
这个表是按照dt进行分区的，那么建表的时候表里就不该有dt这个字段。把建表语句的dt那一行删掉就好了。
（九）hive动态分区
潇洒人生的博客
03-13 727
动态分区 create table cost_dynamic_partition( input_time string, money double ) partitioned by (name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 动态分区 分区字段不能再 表中存在 得把name去掉 动态分...
Hive元数据信息对应MySQL数据库表
勇心在馨
02-01 528
一、VERSION（存储Hive版本的元数据表） 如果该表出现问题，根本进入不了Hive-Cli。 比如该表不存在，当启动Hive-Cli时候，就会报错”Table ‘hive.version’ doesn’t exist” 二、Hive数据库相关的元数据表（DBS、DATABASE_PARAMS） 1、DBS（存储Hive中所有数据库的基本信息） 2、DATABASE_PAR...
sqoop导出数据到hive：Column repeated in partitioning columns
m0_46241543的博客
11-30 90
因为我在用sqoop导出到hive时添加了一个分区的字段，而导出的表中刚好存在这个字段，和分区字段冲突，故报此错误。
hive外部分区表删除分区后，再overwrite 写入，数据并没有被覆盖
laidongxu666的博客
08-11 3121
如果重跑的文件数(500个）
Hive元数据信息表详解
菜鸟叔叔的博客
04-06 6275
hive元数据表的基本情况
Hive概述与基本操作
最新发布
weixin_52134189的博客
04-11 1241
Hive基本概念，hive架构，hive的数据库操作、表操作，内部表与外部表
Hive数据库基本操作优化及实验目的总结
在Hive中，可以通过一系列的基本操作来管理数据库、表和数据。 首先，我们可以通过HQL语句来创建数据库库，但是为了避免重复创建，我们可以在语句中添加if not exists来进行判断。这样可以避免创建已存在的数据库，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值