零基础班第十五课 - Hive DDL详解

最新推荐文章于 2022-11-22 21:24:24 发布

zhikanjiani

最新推荐文章于 2022-11-22 21:24:24 发布

阅读量3.8k

点赞数

分类专栏：零基础班Hive课程

本文链接：https://blog.csdn.net/zhikanjiani/article/details/89075266

版权

零基础班Hive课程专栏收录该内容

6 篇文章 1 订阅

订阅专栏

第一章：上次课回顾

1.1 Hive部署过程中出现的坑

第二章：Hive DDL语句

2.1 图解Hive结构
2.2 创建数据库

第三章：Hive的元数据

3.1 Hive创建表的定义
3.2 正式开始创建Hive表
3.3 表结构创建好后导入数据到表中

第四章：创建表的其它用法

4.1 只拷贝表结构，不拷贝表数据
4.2 既拷贝表结构，又拷贝表数据
4.3 YARN中没有健康节点导致的问题

第五章：修改表相关的信息

5.1 修改表名
5.2 Hive中的内部表和外部表
5.3 Hive官网中对内部表和外部表的描述

第六章：本次课程涉及到的面试题

第一章：上次课回顾

https://blog.csdn.net/zhikanjiani/article/details/89066923

一句话总结Hive：

（Hive基于Hadoop之上的数据仓库，数据是存储在hdfs，元数据是在MySQL或关系型数据库中。）

1.1 Hive部署过程中出现的坑

1、找不到Hadoop-HOME

在这里插入图片描述
需要自行配置环境变量vi ~/.bash_profile
export HADOOP_HOME=/home/hadoop/app/hadoop-2.6.0-cdh5.7.0
export PATH= $HADOOP_HOME/bin:$ HADOOP_HOME/sbin:$PATH
source ~/.bash_profile

2、核对cat hive-site.xml信息

在hive的conf目录下hive-site.xml打印出来需要参数核对：
注意：ConnectionURL配置的是元数据库的地址
hive-site.xml 元数据相关的配置

在这里插入图片描述

3、权限问题

eg：配在Hadoop001这台机器，比如MySQL远程访问这台机器要刷新权限，grant all privileges 然后再flush privileges

4、前提：hdfs启动

提示IP+端口被拒绝 ==>
解决：hive启动前需要先把hdfs启动起来。

5、查看hive.log

以 .template结尾的都是模板文件，不会正式生效，需要使用的时候使用cp命令拷贝一份使用：

1、进入hive配置目录：
 cd /home/hadoop/app/hive-1.1.0-cdh5.7.0/conf 			

2、复制一份配置文件：
 cp hive-log4j.properties.template hive-log4j.properties		

3、	查看这个文件
 cat hive-log4j.properties		

4、找到hive.log.dir，其中${java.io.tmpdir}对应的是/tmp（是根目录下的tmp目录），${user.name}对应的是当前的用户hadoop
 发现hive.log.dir = ${java.io.tmpdir}/${user.name}			//对应的目录为/tmp/hadoop，这个目录下有hive.log.

在这里插入图片描述

对应的文件路径cd /tmp/hadoop
其下存在有hive.log，tail -100f hive.log
打印出最近100行的日志。

hive.log中打印出的日志如下所示：注意一个参数：hive.execution.engine=mr.

1.6、注意一个参数

hive.execution.engine=mr //更换执行引擎的参数。

1.7、经典异常信息：

在这里插入图片描述

An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes**

上次课主要是快速的了解Hive，搭建了环境，运行了案例，要知道MySQL是单点的，单点存在的问题就是数据没了就是没了，解决办法：采用主备机制。

第二章：Hive DDL

任何语法相关直接去到Hive官网：

hive.apache.org—>HIVE wiki
https://cwiki.apache.org/confluence/display/Hive

在这里插入图片描述
MySQL中：
DDL：与创建相关的东西
DML：与查询相关操作

Hive是一个类SQL的引擎，所以语法是类似的。

任何Hive语法相关的一定要到Hive的官网上去查询。

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

2.1 图解Hive结构

在Hive中：DB（数据库）、TABLE（表）、PARTITION（分区）都是一个HDFS上的一个目录或文件夹。

图解：
在一个数据库下面是有很多表的，在每一个表下面又有很多分区；上图上的任何一个层次都是对应于HDFS上的一个目录或者是文件夹。

涉及的DDL操作有如下所示：CREATE 、DROP 、TRUNCATE、ALTER、MSCK、DESCRIBE
此处只讲三类：databases；table；function；

2.2 创建数据库

2.2.1 数据库建表语句

Create Database建表语句：

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

建表语句中一定要记住的话，语法解读：

Hive中没有中小括号标识的必须存在，小括号中(|)带竖线 | 的表示需要二选一，[]中的内容表示可有可无， [LOCATION hdfs_path]不改就是默认路径。

为什么需要有location，location指向的是hdfs上的path，我们说了hive创建数据库就会在hdfs上存在一个目录，创建数据库一定要有一个hdfs目录与之相对应。

2.2.2 开始创建数据库

1、create database G6;

[LOCATION hdfs_path]，创建数据库必然有一个hdfs目录与之相对应，sql语句底层默认是有一个目录的，如果不改就是使用默认的。

2、desc database G6;

能够知道我们创建的数据库在hdfs上的存储路径 ==> hdfs://hadoop001:9000/user/hive/warehouse/g6.db
/user/hive/warehouse，是默认的hive存储在hdfs上的目录。

这个值是在哪儿体现的呢？

依次进入如下页面：hive.apache.org–> hive wiki–> 在user documentation下找到Hive Configuration Properties；

解析：

我们hadoop使用的是9000端口
hdfs://hadoop001:9000 是在$HADOOP_HOME/etc/hadoop/core-site.xml文件下

网址如下：
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

3、g6.db是怎么来的？ g6是我们新建数据库的名称，g6.db是命名规范。
hdfs dfs -ls /user/hive/warehouse/g6.db //在hdfs上可以查找到该目录
在这里插入图片描述

2.2.3 修改Hive数据库默认存储路径的两种方式

（1）、set hive.metastore.warehouse.dir；
set + key表示取值。
在这里插入图片描述
set key=value 设置值
局部设置：单session

（2）、进入到hive-site.xml 配置文件，
以key-value的方式配置进去如下图，修改全局变量。

 <property>
        <name>javax.jdo.option.ConnectionURL</name>
         <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8</value>
         </property>

思考问题？

如果把全局改掉，那是不是会影响别人的作业；PK哥对自己团队要求：改变参数运行完后自己再改回去.

1、自行指定Hive创建的数据库表的位置：

create database hive2 location ‘/d6_hive/directory’ //创建成功后使用desc database hive2查看路径：

2、查看hive2表的信息：

show database hive2; hdfs://hadoop004:9000/d6/hive/directory hadoop USER

3、去hdfs上进行验证是否有这个目录：

hdfs dfs -ls /d6_hive/directory

在这里插入图片描述

第三章：Hive的元数据

进入到mysql数据库下找到在hive-site.xml中配置的元数据库信息：
cd $HIVE_HOME/bin 目录，启动hive；
进入到mysql数据库查看信息：

新开窗口，登录MySQL数据库，
mysql -uroot -p960210;
show databases;
use hive;
show tables;
select * from dbs  \G;

1、 default数据库默认存储的hdfs路径就是/user/hive/warehouse；
2、我们自己创建的g6数据库会在/user/hive/warehouse下，g6.db这是它的命名规范；
3、第三个g666数据库是我们自己指定了数据库在hdfs上的存放目录是/usr/directory。
在这里插入图片描述

小结：

我们看到的命令行下所有的信息都是去元数据中找出来的，所以要有一个元数据的概念；MySQL存在单点的问题，MySQL挂了之后就是扯犊子了。

3.1、hive创建表的定义

在Hive官网中对创建表的定义，定义很多，我们开始提炼：

1、CREATE		 [TEMPORARY] 		[EXTERNAL] 	TABLE		[IF  NOT  EXISTS]    [db_name.]	table_name
第一行解析：CREATE TABLE table_name	必须的三个字段：创建+表+表名

2、列的名字+数据类型：后续会涉及
[( col_name		data_type
data_type
  : primitive_type		基础类型
  | array_type			数组类型
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)

3、每一行记录的格式，在大数据中用到的是两列：行式、列式：两大分隔符：行与行	字段与字段之间的分隔符
[ROW_FORMAT	row_format]
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

（1）、data type：数据类型

（2）、[ROW_FORMAT row_format]row format：引出行式、列式概念

（3）、row_format中用到的是两大分割符：（hdfs上的文件如果想要作用上schema那我们必然要知道文本中的分割符）我们要知道行与行、字段与字段之间的分割符；

行与行之间的分割符默认的是回车，字段与字段之间的分割符可用是逗号，tab键分割。hive默认的列分隔符：\001

（4）、最准确的说file格式分为：行式、列式

（5）、数值类型：int、 bigint（int长度不够时）、 float 、 double、 decimal(对于金钱来说更精确)、关于时间类型：若泽公司生产上不用，他们用string字符串类型.
布尔类型数值类型
true/false 0/1

常用数值类型	时间类型（PK生产都不用）	布尔类型
int/bigint	字符串(String)	true/false==>(0,1)，可以提升性能
float	生产上时间类型转换废老鼻子劲了	-
double	-	-
decimal(银行对钱的精度)	-	-

布尔类型存储true/false，相比较直接存储为0/1，哪个更方便；就把一个字段（true、false）修改成为(1、0) 后整体性能提升了很多倍。

3.2、正式开始创建Hive表

3.2、hive下进行员工信息表创建

解析：员工编号：Int类型；员工名字：String类型；工作：string类型；部门编号：string类型；雇佣日期使用String类型（Why? 不要使用date，定义的时候是个date类型，但是数据进来后转换失败该怎么办呢）；工资：double类型；奖金：double类型；部门编号：int类型

字段名称	数据类型
员工编号	Int
员工名字	String
工作	String
部门编号	String
雇佣日期	String
工资	double
奖金	double
部门编号	Int

建表语句如下：

create table emp(
empnu int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';				

字段与字段间的分隔符使用tab键制表符，用"\t"表示。

此时的FileFormat还不需要设置，文本格式是默认的。

如何操作能够使得我们知道当前使用的数据库：

hive客户端下set hive.cli.print.current.db=true，配置路径如下：
配置路径：cd /home/hadoop/app/hive-1.1.0-cdh5.7.0/conf 下进入hive.xml文件中，把以下代码加入进去，下次重新启动进行生效，重启hive后能够显示当前使用的数据库是哪个。

<property>
                        <name>hive.cli.print.current.db</name>
                        <value>true</value>
         </property>

PK哥演示的时候报错了：

在这里插入图片描述
前台看得出来P个东西，需要进入到/tmp/hadoop/hive.log下打印近200行的日志。

在这里插入图片描述

3.3、表结构创建好后导入数据到表中

语法：load data [local] inpath ‘filepath’ [overwrite] into table tablename [partition]

注意：

local：指的是从本地（linux）加载数据;
filepath指的是自己选择的路径“/home/hadoop/data/emp.txt”：指定路径的话在本地找（eg：/home/hadoop/data这样的本地）；没有指定local的话，会去fs.default.name路径下去找（也就是去hdfs上的路径上去找）。

开始测试：

1、load data local inpath ‘/home/hadoop/data/emp.txt’ overwrite into table emp;
2、select * from emp;

我们看到原来emp表中数据为空，使用load加载后表中已有数据。

小结：

至此，我们已经把文件转换成了一张表了，后面只要使用SQL进行操作，和关系型数据库类似，可以使用SQL统计文件信息，相比MapReduce就省力不知道多少了。
在这里插入图片描述

到/tmp/hadoop目录下查看近100行日志tail -100f /tmp/hadoop/hive.log

第四章：创建表的其它用法

第一种：like只拷贝表结构，不拷贝表数据(跑MapReduce)

语法定义：

CREATE	[TEMPORARY]	[EXTERNAL]	TABLE		[IF  NOT  EXISTS]	[db_name]table_name
LIKE	existing_table_or_view_name
[LOCATION		hdfs_path]

==>	语法简化
CREATE	TABLE	emp2
LIKE	existing_table_or_view_name

create table 新表表名 like 已经创建好的表的表名
create table emp2 like emp;
show tables, 查看有emp2这个表
select * from emp2是没有数据的，只拷贝了表结构，不拷贝表内容

第二种：既拷贝表结构又拷贝表数据(跑MapReduce)

语法：

create table emp3 as select * from emp; //意思是查询到所有emp中的数据放到emp3表中
上面这个语句会跑一个mapreduce作业，我们进入到对应YARN的UI界面上：http://192.168.137.138:8088/cluster，查看作业情况。

YARN中没有健康节点导致的问题

问题：PK哥演示出现的问题：

可能会遇到的问题如下：健康的节点（active node）没有了，
原因：磁盘空间使用率超过90%，进行清理。

如何查看磁盘使用率：

df -Th
df -h
使用df --help查看命令帮助，df -T：–print-type print file system type
df -h：–human–readable print sizes in human readable format(e.g., 1k 234M 2G)
df -H：–si likewise, but use powers of 1000 not 1024.

在这里插入图片描述

可以自行测试下：当磁盘空间大于90%，YARN节点必然是不健康的。
在这里插入图片描述

回顾：

创建表的方式：自己创建表；拷贝人家的表结构；拷贝人家的数据。

第五章：修改表相关的信息

5.1 修改表名

承接第四章：

创建表此时我们已经知道了三种方式，按照第四章中的操作后，我们已经有了三张表。

可以修改的信息有很多：Alter Table/Partition/Column

第一步：修改表名

语法：

alter table emp3 rename to new_emp3;

修改表名的时候会一并修改元数据信息：

查看表信息的几种方式：

第一种：desc table emp3; //这个只能查到一个简单的字段信息

第二种：desc extended emp3; //查看详细信息

第三种：desc formatted emp3; //推荐使用的查看格式

此时去到hdfs上查看到emp3的表的名字：

Location：hdfs://hadoop002:8020/user/hive/warehouse/d6_hive.db/emp3

开始修改名字：

alter table emp3 rename to new_emp3;

show tables查看名字是否改掉：

new_emp3
Location：hdfs://hadoop002:8020/user/hive/warehouse/d6_hive.db/new_emp3 hdfs上的元数据信息也被改掉了。

5.2 Hive中的内部表和外部表

使用desc formatted emp3; 查看到的是表的详细信息；我们注意到其中一行：table type: managed_type （内部表），除了内部表以外还有external table （外部表）。
Hive中默认的是内部表，如果我们需要外部表，则在创建表时加入external。

内部表创建测试：

1、语法如下：

create table emp_managed as select * from emp;
数据肯定是没有问题的，select * form emp_managed; 数据都是正常被加载进来的。

2、此时进入MySQL，在ruoze_g6数据库下有很多张表，有一张TBLS表；

select * from TBLS \G;
注意到我们的emp_managed表，在MySQL中它是一个内部表。

3、此时我们在Hive上使用drop命令（drop table emp_managed）删除emp_managed表，删除完后在Hive上肯定是没有这个表格了；进入到MySQL中查看，select * from TBLS \G; 发现也没有emp_managed的记录了。

小结：

删除内部表：数据+元数据删除

外部表创建测试：

建表语句：

create external table emp_external (empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)row format delimited fields terminated by '\t'
location 'g6/external';

外部表创建的时候external关键字要加上，不区分external的大小写.
创建外部表的时候location指定到HDFS上的某个地方.

表格创建好后开始查询：

1、select * from emp_external;

此时数据为空

2、hadoop fs -ls /g6/external
此目录下数据暂时为空

3、hadoop fs -put emp.txt /g6/external

把emp.txt的数据上传到/g6/external这个目录

4、select * from emp_external;
此时再去Hive中查询数据，发现已经能查询的到数据了。

5 、drop table emp_external;

此时再删除Hive下的表，去到MySQL的ruoze_g6/TBLS表中查看，我们发现已经没有该表的记录了，但是在/g6/external中这份文件还保留着。

小结：

删除外部表：数据不删（hdfs上的数据），元数据删除（MySQL中的元数据信息删除）。

5.3 Hive官网中对内部表和外部表的描述

Hive官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

Managed and External Tables
By default, Hive creates managed tables, where files, metadata and statistics are managed by internal Hive process. For details on the differences between managed and external table see Managed vs External Tables.

Managed tables：

A managed table is shorted under the hive.metastore.warehouse.dir path property, by default in a folder path（文件夹路径） similar to /user/hive/warehouse/databasename.db/tablename/. The default location can be overridden by the location property during table creation, If a managed table or partition is dropped（如果一个内部表或者分区被删除）, the data and metadata associated with that table or partition are deleted（数据和meta相关的数据都会被删除）. If the PURGE option is not specified, the data is moved to a trash folder for a defined duration.
Use managed tables when Hive managed the lifecycle of the table, or when generating temporary tables.

External tables：

An external table describes the metadata/schema on external files（描述的是外部文件的meta信息）. External table files can be accessed and managed by process outside of Hive. External tables can access data stored in sources such as Azure Storage Volumes (ASV) or remote HDFS locations. If the structure or partitioning of an external table is changed, an MSCK REPAIR TABLE table_name statement can be used to refresh metadata information.
Use external tables when files are already present or in remote locations（使用外部表，文件一直会存在一个路径下）, and the files should remain even if the tables is dropped（即使表删除，文件一直保留着的）.

第六章：本次课程涉及到的面试题

1、drop和truncate的区别？

Answer：truncate:只删除数据文件，保存在MySQL中的meta store文件不会被删除；
drop:数据文件和MySQL中的元数据信息都会被删除

https://blog.csdn.net/weixin_34112181/article/details/92048840

2、Hive内部表和外部表的区别?

https://blog.csdn.net/zhikanjiani/article/details/94492610

zhikanjiani

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
零基础班第十五课 - Hive DDL详解

一、hive回顾1.1基于Hadoop之上的数据仓库，数据实在hdfs，元数据是在MySQL这些关系型数据库中。hive部署过程中出现的坑：1、找不到Hadoop-HOME，需要自行配置环境变量。echo $HADOOP_HOME1.2、cat hive-ste.xml打印出来需要核对参数1.3、权限问题，现在配在Hadoop001这台机器，比如MySQL远程访问这台机器要刷新权限1...
复制链接

扫一扫