Hive_Day01
文章目录
- Hive_Day01
- Hive的简介
- 什么是Hive
- Hive框架的解释
- metastore元数据信息包含哪些
- mysql中存储元数据信息的常见的几张表
- 客户端向hive中输入一条sql语句是如何转换为mapreduce任务的(Driver)
- 为什么使用hive
- hive特点
- hive与传统数据库对比
- hive搭建
- hive架构以及四种启动方式
- hive cli和beeline cli的区别
- hive的基本操作
- hive的基本数据类型
- hive表的存储格式
- 对于hive的存储格式和压缩方法的总结
- 五种基本创建表的方式
- 简单和复杂用户信息表的创建
- 显示表
- 向表中添加数据的四种方式
- hive的一些额外操作(类似mysql)
- 内部表和外部表的区别
- hive导出数据
Hive的简介
Hive本质是将SQL转换为MapReduce的任务进行运算,底层由HDFS来提供数据存储,说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具,甚至更近一步说hive就是一个MapReduce客户端。
什么是Hive
1、hive是数据仓库建模的工具之一。
2、可以向hive传入一条交互式的sql,在海量数据中查询分析得到结果的这样一个平台
Hive框架的解释
-
HDFS上存储的是结构化文件,所谓结构化文件就是每行的列数相同,并且列与列之间的分隔符是固定的
-
mysql的一些表中存储的是HDFS上结构化文件的元数据信息
-
hive通过读取MySQL上的元数据信息,进而得到HDFS上的结构化文件数据,将文件中的数据映射成一张表
-
如下图所示
metastore元数据信息包含哪些
- 表名
- 表所属的数据库
- 表的拥有者
- 列/分区字段
- 表的类型(内部表、外部表)
- 表的数据所在的目录
- 等等
元数据信息一般需要借助于其他的数据载体(数据库),主要用于存放数据库的建表语句等信息,推荐使用Mysql数据库存放数据
mysql中存储元数据信息的常见的几张表
Hive元数据库中一些重要的表结构及用途,方便Impala、SparkSQL、Hive等组件访问元数据库的理解。
-
Hive数据库相关的元数据表(DBS、DATABASE_PARAMS)
DBS:该表存储Hive中所有数据库的基本信息。
DATABASE_PARAMS:该表存储数据库的相关参数。
-
Hive表和视图相关的元数据表
主要有TBLS、TABLE_PARAMS、TBL_PRIVS,这三张表通过TBL_ID关联。
TBLS:该表中存储Hive表,视图,索引表的基本信息。
TABLE_PARAMS:该表存储表/视图的属性信息。
TBL_PRIVS:该表存储表/视图的授权信息。 -
Hive文件存储信息相关的元数据表
主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS,由于HDFS支持的文件格式很多,而建Hive表时候也可以指定各种文件格式,Hive在将HQL解析成MapReduce时候,需要知道去哪里,使用哪种格式去读写HDFS文件,而这些信息就保存在这几张表中。
SDS:该表保存文件存储的基本信息,如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的SD_ID与该表关联,可以获取Hive表的存储信息。
SD_PARAMS: 该表存储Hive存储的属性信息。
SERDES:该表存储序列化使用的类信息。
SERDE_PARAMS:该表存储序列化的一些属性、格式信息,比如:行、列分隔符。 -
主要涉及COLUMNS_V2:该表存储表对应的字段信息。
-
存储Hive版本的元数据表(VERSION),该表比较简单,但很重要,如果这个表出现问题,根本进不来Hive-Cli。比如该表不存在,当启动Hive-Cli的时候,就会报错“Table ‘hive.version’ doesn’t exist”
客户端向hive中输入一条sql语句是如何转换为mapreduce任务的(Driver)
元数据存储在数据库中,默认存在自带的derby数据库(单用户局限性)中,推荐使用Mysql进行存储。
1) 解析器(SQL Parser):将SQL字符串转换成抽象语法树AST,这一步一般都用第三方工具库完 成,比如ANTLR;对AST进行语法分析,比如表是否存在、字段是否存在、SQL语义是否有误。
2) 编译器(Physical Plan):将AST编译生成逻辑执行计划。
3) 优化器(Query Optimizer):对逻辑执行计划进行优化。
4) 执行器(Execution):把逻辑执行计划转换成可以运行的物理计划。对于Hive来说,就是 MR/Spark。
抽象语法树(Abstract Syntax Tree,AST)是源代码语法结构的一种树形表示方法。它将源代码解析为树状结构,每个节点代表代码的一个语法元素,如表达式、语句或声明。抽象语法树(Abstract Syntax Tree,AST)是源代码语法结构的一种树形表示方法。它将源代码解析为树状结构,每个节点代表代码的一个语法元素,如表达式、语句或声明。
为什么使用hive
如果直接使用hadoop的话,人员学习成本太高,项目要求周期太短,MapReduce实现复杂查询逻辑开发难度太大。如果使用hive的话,可以操作接口采用类SQL语法,提高开发能力,免去了写MapReduce,减少开发人员学习成本,功能扩展很方便(比如:开窗函数)。
hive特点
优点:
- 可扩展性:Hive可以自由的扩展集群的规模,一般情况下不需要重启服务
- 延伸性:Hive支持自定义函数,用户可以根据自己的需求来实现自己的函数
- 容错:即使节点出现错误,SQL仍然可以完成执行
- 简单、容易上手:操作接口采用类sql语法,提供快速开发的能力。避免了去写MapReduce,减少开发人员的学习成本
缺点:
- 迭代式算法无法表达 (反复调用,mr之间独立,只有一个map一个reduce,反复开关)
- Hive 优势在于处理大数据,对于处理小数据没有优势,因为 Hive 的执行延迟比较高。(不断地开关JVM虚拟机)
- Hive的延迟性比较高,因此Hive常用于数据分析,适用于对实时性要求不高的场合
- Hive 自动生成的 MapReduce 作业,通常情况下不够智能化
- Hive 调优比较困难,粒度较粗 (hql根据模板转成mapreduce,不能像自己编写mapreduce一样精细,无法控制在map处理数据还是在reduce处理数据)
hive与传统数据库对比
hive搭建
1、上传解压配置环境变量
# 1、解压
tar -xvf apache-hive-3.1.2-bin.tar.gz.gz
# 2、重名名
mv apache-hive-3.1.2-bin hive-3.1.2
# 3、配置环境变量
vim /etc/profile
# 4、在最后增加配置
export HIVE_HOME=/usr/local/soft/hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin
# 5、使环境变量剩下
source /etc/profile
2、修改配置文件
1、进入hive配置文件所在目录
cd /usr/local/soft/hive-3.1.2/conf
2、创建hive-site.xml配置文件
vim hive-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master:3306/hive?useSSL=false&createDatabaseIfNotExist=true&characterEncoding=utf8&useUnicode=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://master:9083</value>
</property>
<property>
<name>hive.server2.enable.doAs </name>
<value>false</value>
</property>
<property>
<name>hive.querylog.location</name>
<value/>
</property>
(同上)
<property>
<name>hive.exec.local.scratchdir</name>
<value/>
</property>
(同上)
<property>
<name>hive.downloaded.resources.dir</name>
<value/>
</property>
</configuration>
注意3215行的<descirption></descirption>直接删掉就行
3、创建log4j.properties配置文件
# 将日志级别改成WARN,避免执行sql出现很多日志
log4j.rootLogger=WARN,CA
log4j.appender.CA=org.apache.log4j.ConsoleAppender
log4j.appender.CA.layout=org.apache.log4j.PatternLayout
log4j.appender.CA.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n%
3、上传mysql驱动
# 将nysql驱动包上传到hive的lib目录下
mysql-connector-java-8.0.29.jar
4、初始化hive元数据库
# 2、初始化hive的元数据(表结构)到mysql中
schematool -dbType mysql -initSchema
5、启动hive元数据服务
hive --service metastore
6、进入hive命令行
# 进入hive
hive
# 测试
# 1、创建表
CREATE EXTERNAL TABLE IF NOT EXISTS student(
id string ,
`name` string ,
age string ,
gender string ,
clazz string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/data/student';
# 2、执行sql查询数据
select clazz,count(1) as num from student group by clazz;
解决 log4j 兼容性问题
警告信息,不影响使用
LF4J: Class path contains multiple SLF4J bindings.
cd /usr/local/soft/hive-3.1.2/lib
# hive 与 Hadoop 在运行时会出现 log4j 兼容性问题,这是因为 hive 的 log4j 版本与 Hadoop 的产生了冲突,我们这里将 hive 的 log4j 设置为备份。
mv log4j-slf4j-impl-2.10.0.jar log4j-slf4j-impl-2.10.0.jar.bak
hive架构以及四种启动方式
1)第一种交互方式
shell交互Hive,用命令hive启动一个hive的shell命令行,在命令行中输入sql或者命令来和Hive交互。
服务端启动metastore服务(后台启动):nohup hive --service metastore >/dev/null &
进入命令:hive
退出命令行:quit;
2)第二种交互方式
Hive启动为一个服务器,对外提供服务,其他机器可以通过客户端通过协议连接到服务器,来完成访问操作,这是生产环境用法最多的
服务端启动hiveserver2服务:
nohup hive --service metastore >/dev/null &
nohup hiveserver2 >/dev/null &
需要稍等一下,启动服务需要时间:
进入命令:1)先执行: beeline ,再执行: !connect jdbc:hive2://master:10000
2)或者直接执行: beeline -u jdbc:hive2://master:10000 -n root
退出命令行:!exit
3)第三种交互方式
使用 –e 参数来直接执行hql的语句
bin/hive -e "show databases;"
使用 –f 参数通过指定文本文件来执行hql的语句
特点:执行完sql后,回到linux命令行。
vim hive.sql
use myhive;
select * from test;
hive -f hive.sql
hive cli和beeline cli的区别
hive的基本操作
创建一个数据库
1)创建一个数据库,数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。
create database testdb;
2)避免要创建的数据库已经存在错误,增加if not exists判断。(标准写法)
create database if not exists testdb;
创建一个数据库并指定存储的位置
create database if not exists dept location '/testdb.db';
修改数据库
数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。
一般只修改一个数据库的创建时间
alter database dept set dbproperties('createtime'='20220531');
数据库的详细信息
1)显示数据库(show)
show databases;
2)可以通过like进行过滤
show databases like 't*';
3)查看详情(desc)
desc database testdb;
4)切换数据库(use)
use testdb;
删除数据库(将删除的目录移动到回收站中)
1)最简写法
drop database testdb;
2)如果删除的数据库不存在,最好使用if exists判断数据库是否存在。否则会报错:FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
drop database if exists testdb;
3)如果数据库不为空,使用cascade命令进行强制删除。报错信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
drop database if exists testdb cascade;
hive的基本数据类型
类型 | Java数据类型 | 描述 |
---|---|---|
TINYINT | byte | 8位有符号整型。取值范围:-128~127。 |
SMALLINT | short | 16位有符号整型。取值范围:-32768~32767。 |
INT | int | 32位有符号整型。取值范围:-2 31 ~2 31 -1。 |
BIGINT | long | 64位有符号整型。取值范围:-2 63 +1~2 63 -1。 |
BINARY | 二进制数据类型,目前长度限制为8MB。 | |
FLOAT | float | 32位二进制浮点型。 |
DOUBLE | double | 64位二进制浮点型。 |
DECIMAL(precision,scale) | 10进制精确数字类型。precision:表示最多可以表示多少位的数字。取值范围:1 <= precision <= 38。scale:表示小数部分的位数。取值范围: 0 <= scale <= 38。如果不指定以上两个参数,则默认为decimal(10,0)。 | |
VARCHAR(n) | 变长字符类型,n为长度。取值范围:1~65535。 | |
CHAR(n) | 固定长度字符类型,n为长度。最大取值255。长度不足则会填充空格,但空格不参与比较。 | |
STRING | string | 字符串类型,目前长度限制为8MB。 |
DATE | 日期类型,格式为yyyy-mm-dd 。取值范围:0000-01-01~9999-12-31。 | |
DATETIME | 日期时间类型。取值范围:0000-01-01 00:00:00.000~9999-12-31 23.59:59.999,精确到毫秒。 | |
TIMESTAMP | 与时区无关的时间戳类型。取值范围:0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999,精确到纳秒。说明 对于部分时区相关的函数,例如cast( as string),要求TIMESTAMP按照与当前时区相符的方式来展现。 | |
BOOLEAN | boolean | BOOLEAN类型。取值:True、False。 |
ARRAY | array<int>``array<struct<a:int, b:string>> | array(1, 2, 3)``array(array(1, 2), array(3, 4)) |
---|---|---|
MAP | map<string, string>``map<smallint, array<string>> | map(“k1”, “v1”, “k2”, “v2”)``map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’)) |
STRUCT | struct<x:int, y:int>struct<field1:bigint, field2:array<int>, field3:map<int, int>> named_struct(‘x’, 1, ‘y’, 2) named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200)) |
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似,而STRUCT与C语言中的Struct类似,它封装了一个命名字段集合,复杂数据类型允许任意层次的嵌套。还有一个uniontype< 所有类型,所有类型… > 。
数组:array< 所有类型 >;
Map < 基本数据类型,所有数据类型 >;
struct < 名:所有类型[注释] >;
uniontype< 所有类型,所有类型… >
hive表的存储格式
Hive的存储格式:
Hive没有专门的数据文件格式,常见的有以下几种:
TEXTFILE
SEQUENCEFILE
AVRO
RCFILE
ORCFILE
PARQUET
TextFile:
TEXTFILE 即正常的文本格式,是Hive默认文件存储格式,因为大多数情况下源数据文件都是以text文件格式保存(便于查看验数和防止乱码)。此种格式的表文件在HDFS上是明文,可用hadoop fs -cat命令查看,从HDFS上get下来后也可以直接读取。
TEXTFILE 存储文件默认每一行就是一条记录,可以指定任意的分隔符进行字段间的分割。但这个格式无压缩,需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用,使用这种方式,Hive不会对数据进行切分,从而无法对数据进行并行操作。
一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式,其他事实表和维度表都不建议使用。
RCFile:
Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能。通常写操作比较慢,比非列形式的文件格式需要更多的内存空间和计算量。 RCFile是一种行列存储相结合的存储方式。首先,其将数据按行分块,保证同一个record在一个块上,避免读一个记录需要读取多个block。其次,块数据`列式存储`,有利于数据压缩和快速的列存取。
ORCFile:
Hive从0.11版本开始提供了ORC的文件格式,ORC文件不仅仅是一种列式文件存储格式,最重要的是有着很高的压缩比,并且对于MapReduce来说是可切分(Split)的。因此,在Hive中使用ORC作为表的文件存储格式,不仅可以很大程度的节省HDFS存储资源,而且对数据的查询和处理性能有着非常大的提升,因为ORC较其他文件格式压缩比高,查询任务的输入数据量减少,使用的Task也就减少了。ORC能很大程度的节省存储和计算资源,但它在读写时候需要消耗额外的CPU资源来压缩和解压缩,当然这部分的CPU消耗是非常少的。
Parquet:
通常我们使用关系数据库存储结构化数据,而关系数据库中使用数据模型都是扁平式的,遇到诸如List、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下,通常数据的来源是服务端的埋点数据,很可能需要把程序中的某些对象内容作为输出的一部分,而每一个对象都可能是嵌套的,所以如果能够原生的支持这种数据,这样在查询的时候就不需要额外的解析便能获得想要的结果。Parquet的灵感来自于2010年Google发表的Dremel论文,文中介绍了一种支持嵌套结构的存储格式,并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式,它是语言、平台无关的,并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势:支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.
SEQUENCEFILE:
SequenceFile是Hadoop API 提供的一种二进制文件,它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile,不过它的key为空,使用value 存放实际的值, 这样是为了避免MR 在运行map 阶段的排序过程。SequenceFile支持三种压缩选择:NONE, RECORD, BLOCK。 Record压缩率低,一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好,有API,但除此之外平平无奇,实际生产中不会使用。
AVRO:
Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑,若要读取大量数据时,Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的,所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式,如Pig 、Hive、Flume、Sqoop和Hcatalog。
Hive的四大常用存储格式存储效率及执行速度对比
结论:ORCFILE存储文件读操作效率最高
耗时比较:ORC<Parquet<RC<Text
结论:ORCFILE存储文件占用空间少,压缩效率高
占用空间:ORC<Parquet<RC<Text
对于hive的存储格式和压缩方法的总结
常见的存储格式有哪些
- TestFile(系统默认)
- RCFile
- ORCFile
- AVRO
- Parquet
- SequenceFile
其中TestFile(系统默认),RCFile,ORCFile,Parquet这四种存储格式是最为常见的存储格式
对于这四种常见数据类型的介绍
-
TextFile(行存储)
它是正常的文本格式,是Hive默认文件存储格式, TEXTFILE 存储文件默认每一行就是一条记录,此种格式的表文件在HDFS上是明文,可用hadoop fs -cat命令查看,从HDFS上get下来后也可以直接读取。可以指定任意的分隔符进行字段间的分割。但这个格式无压缩,需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用,使用这种方式,Hive不会对数据进行切分,从而无法对数据进行并行操作。
-
RCFile(列存储)
Record Columnar的缩写。是Hadoop中第一个列文件格式 RCFile是一种行列存储相结合的存储方式。首先,其将数据按行分块,通过横向切分数据并将其分布在多个数据块中,可以支持并行读取和处理,提高了读取性能。其次,块数据列式存储,有利于数据压缩和快速的列存取。
-
ORCFile(列存储)
ORCFile在列式存储的基础上,采用更为复杂的压缩算法和索引结构,以提高读取性能和查询效率。
-
Parquet(列式存储)
parquet相较于orc的仅有优势:支持嵌套结构
-
SequenceFile(行存储)
二进制的行存储文件
压缩方法
-
zlib:压缩率⽐较⾼、但是压缩速度⼀般
-
snappy:压缩率⽐zlib格式要低 ,压缩速度快
-
gzip:压缩率⽐较⾼,⽽且压缩速度也⽐较快
五种基本创建表的方式
创建表
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)
创建内部表时,会将数据移动到数据仓库指向的路径(默认位置);
创建外部表时,仅记录数据所在的路径,不对数据的位置做任何改变。在
删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
- COMMENT:
为表和列添加注释。
- PARTITIONED BY
创建分区表
- CLUSTERED BY
创建分桶表
- SORTED BY
不常用
- 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确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称,目的是用于序列化和反序列化。
- STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。
如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
- LOCATION :
指定表在HDFS上的存储位置。
- LIKE
允许用户复制现有的表结构,但是不复制数据。
建表1:全部使用默认建表方式
create table students
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选,指定列分隔符
建表2:指定location (这种方式也比较常用)
create table students2
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; // 指定Hive表的数据的存储位置,一般在数据已经上传到HDFS,想要直接使用,会指定Location,通常Locaion会跟外部表一起使用,内部表一般使用默认的location
建表3:指定存储格式
create table students3
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; // 指定储存格式为rcfile,inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat,如果不指定,默认为textfile,注意:除textfile以外,其他的存储格式的数据都不能直接加载,需要使用从表加载的方式。
建表4:create table xxxx as select_statement(SQL语句) (这种方式比较常用)
create table students4 as select * from students2;
建表5:create table xxxx like table_name 只想建表,不需要加载数据
create table students5 like students;
简单和复杂用户信息表的创建
简单用户信息表创建:
create table t_user( id int, uname string, pwd string, gender string, age int ) row format delimited fields terminated by ',' lines terminated by '\n';
1,admin,123456,男,18 2,zhangsan,abc123,男,23 3,lisi,654321,女,16
复杂人员信息表创建:
create table IF NOT EXISTS t_person( name string, friends array<string>, children map<string,int>, address struct<street:string ,city:string> ) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' lines terminated by '\n';
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,beng bu_anhui yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,he fei_anhui
显示表
show tables;
show tables like 'u';
desc t_person;
desc formatted t_person; // 查看更加详细的表信息
向表中添加数据的四种方式
1、使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'
2、使用 load data inpath
下列命令需要在hive shell里执行
// 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下,注意是 移动、移动、移动
load data inpath '/input1/students.txt' into table students;
// 清空表
truncate table students;
// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
load data local inpath '/usr/local/soft/data/students.txt' into table students;
// overwrite 覆盖加载
load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;
3、create table xxx as SQL语句
4、insert into table xxxx SQL语句 (没有as)
// 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失
insert into table students2 select * from students;
// 覆盖插入 把into 换成 overwrite
insert overwrite table students2 select * from students;
hive的一些额外操作(类似mysql)
修改列
查询表结构
desc students2;
添加列
alter table students2 add columns (education string);
查询表结构
desc students2;
更新列
alter table stduents2 change education educationnew string;
删除表
drop table students2;
内部表和外部表的区别
Hive内外部表
面试题:内部表和外部表的区别?如何创建外部表?工作中使用外部表
hive内部表
当创建好表的时候,HDFS会在当前表所属的库中创建一个文件夹
当设置表路径的时候,如果直接指向一个已有的路径,可以直接去使用文件夹中的数据
当load数据的时候,就会将数据文件存放到表对应的文件夹中
而且数据一旦被load,就不能被修改
我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS
当我们删除表的时候,表对应的文件夹会被删除,同时数据也会被删除
默认建表的类型就是内部表
// 内部表
create table students_internal
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input2';
hive> dfs -put /usr/local/soft/data/students.txt /input2/;
Hive外部表
外部表说明
外部表因为是指定其他的hdfs路径的数据加载到表中来,所以hive会认为自己不完全独占这份数据
删除hive表的时候,数据仍然保存在hdfs中,不会删除。
// 外部表
create external table students_external
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input3';
hive> dfs -put /usr/local/soft/data/students.txt /input3/;
删除表测试一下:
hive> drop table students_internal;
Moved: 'hdfs://master:9000/input2' to trash at: hdfs://master:9000/user/root/.Trash/Current
OK
Time taken: 0.474 seconds
hive> drop table students_external;
OK
Time taken: 0.09 seconds
hive>
一般在公司中,使用外部表多一点,因为数据可以需要被多个程序使用,避免误删,通常外部表会结合location一起使用
外部表还可以将其他数据源中的数据 映射到 hive中,比如说:hbase,ElasticSearch…
设计外部表的初衷就是 让 表的元数据 与 数据 解耦
hive导出数据
Hive导出数据
将表中的数据备份
- 将查询结果存放到本地
//创建存放数据的目录
mkdir -p /usr/local/soft/shujia
//导出查询结果的数据(导出到Node01上)
insert overwrite local directory '/usr/local/soft/shujia/person_data' select * from t_person;
- 按照指定的方式将数据输出到本地
-- 创建存放数据的目录
mkdir -p /usr/local/soft/shujia
-- 导出查询结果的数据
insert overwrite local directory '/usr/local/soft/shujia/person'
ROW FORMAT DELIMITED fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
select * from t_person;
- 将查询结果输出到HDFS
-- 创建存放数据的目录
hdfs dfs -mkdir -p /shujia/bigdata17/copy
-- 导出查询结果的数据
insert overwrite local directory '/usr/local/soft/shujia/students_data2' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * from students
- 直接使用HDFS命令保存表对应的文件夹
// 创建存放数据的目录
hdfs dfs -mkdir -p /shujia/bigdata17/person
// 使用HDFS命令拷贝文件到其他目录
hdfs dfs -cp /hive/warehouse/t_person/* /shujia/bigdata17/person
-
将表结构和数据同时备份
将数据导出到HDFS
//创建存放数据的目录 hdfs dfs -mkdir -p /shujia/bigdata17/copy //导出查询结果的数据 export table t_person to '/shujia/bigdata17/copy';
删除表结构
drop table t_person;
恢复表结构和数据
import from '/shujia/bigdata17';
注意:时间不同步,会导致导入导出失败