Hive SQL 初识 DDL学习笔记

​​​​​​

初识

数据仓库

数据仓库 是一个用于存储、分析、报告的数据系统

OLAP（联机分析处理）

数据仓库特征

面向主题：

集成性：主题相关的数据通常会分布在多个操作型系统中，彼此分散、独立、异构。需要集
成到数仓主题下

非易变性：也叫非易变性。数据仓库是分析数据的平台，而不是创造数据的平台

大量查询 删除和修改操作少

时变性：数据仓库的数据需要随着时间更新

数仓主流开发语言SQL

结构化查询语言（Structured Query Language）SQL

结构化数据：由二维表结构来逻辑表达和实现的数据

用于数据存储以及查询、更新和管理数据

SQL语法分类

数据定义语言（DDL）

DDL语法使我们有能力创建或删除表，以及数据库、索引等各种对象，但是不涉及表中具体数据操作
CREATE DATABASE - 创建新数据库
CREATE TABLE - 创建新表

数据操纵语言（DML）

DML语法是我们有能力针对表中的数据进行插入、更新、删除、查询操作
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT - 向数据库表中插入数据

Hive初识

Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言（HQL）

Hive核心是将HQL转化为MapReduce程序，然后将程序提交到Hadoop执行

Hive利用HDFS存储数据，利用MapReduce查询分析数据

数据库模型

Hive架构组件

用户接口

元数据存储

Driver驱动程序

 执行引擎

元数据

Hive Metadata

Hive Metadata即Hive的元数据

元数据：描述数据的数据

元数据存储在关系型数据库中，如hive自带的Derby  或第三方MySQL

Hive Metastore

Metastore元数据服务

Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通
过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据

操作

Metastore配置模式

 用远程模式来配置Hive Metastore，其他依赖hive的软件都可以通过Metastore访问hive

部署

安装前保证hadoop集群环境健康可以（启动集群后等会 等到HDFS安全模式关闭）

服务器基础环境

集群时间同步、防火墙关闭、主机Host映射、免密登录、JDK安装 Hadoop启动

Hadoop与Hive整合

添加相关属性，满足Hive在hadoop上运行

修改/export/server/hadoop-3.3.0/etc/hadoop中core-site.xml(插入)

<!-- 整合 hive -->
<property>
  <name>hadoop.proxyuser.root.hosts</name>
  <value>*</value>
</property>
<property>
  <name>hadoop.proxyuser.root.groups</name>
  <value>*</value>
</property>

Hadoop集群同步配置文件，重启

MySQL安装

一台机器安装

卸载Centos7自带的mariadb
 rpm -qa|grep mariadb

 rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps

 rpm -qa|grep mariadb 

安装mysql

 mkdir /export/software/mysql（存储mysql安装包）

 上传到此文件夹下

tar xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

yum -y install libaio

 rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm mysql-community-libs-5.7.29-1.el7.x86_64.rpm mysql-community-client-5.7.29-1.el7.x86_64.rpm mysql-community-server-5.7.29-1.el7.x86_64.rpm 

rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm mysql-community-libs-5.7.29-1.el7.x86_64.rpm mysql-community-client-5.7.29-1.el7.x86_64.rpm mysql-community-server-5.7.29-1.el7.x86_64.rpm 

安装成功

初始化

mysqld --initialize

更改所属组

 chown mysql:mysql /var/lib/mysql -R

启动mysql
 systemctl start mysqld.service

查看生成的临时root密码

cat /var/log/mysqld.log
  
  [Note] A temporary password is generated for root@localhost: o+TU+KDOm004

                                                                                                            （密码）

密码12位 注意位数

mysql -u root -p

输入密码

修改密码

alter user user() identified by "你的密码";（hadoop）

授权
 mysql> use mysql;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'hadoop' WITH GRANT OPTION;

刷新

 FLUSH PRIVILEGES

ctlr d结束推出myspl

设置为开机自启动服务

systemctl enable mysqld

查看是否已经设置自启动成功

systemctl list-unit-files | grep mysqld

mysql的启动和关闭 状态查看 
  systemctl stop mysqld
  systemctl status mysqld
  systemctl start mysqld

Hive安装

一台机器安装

 安装到/export/server下

 tar zxvf apache-hive-3.1.2-bin.tar.gz

解决Hive与Hadoop之间版本差异

cd /export/server/apache-hive-3.1.2-bin/
 rm -rf lib/guava-19.0.jar
 cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar ./lib/

 配置文件

cd /export/server/apache-hive-3.1.2-bin/conf

 mv hive-env.sh.template hive-env.sh

vim hive-env.sh

最后插入

export HADOOP_HOME=/export/server/hadoop-3.3.0
export HIVE_CONF_DIR=/export/server/apache-hive-3.1.2-bin/conf
export HIVE_AUX_JARS_PATH=/export/server/apache-hive-3.1.2-bin/lib

 vim hive-site.xml（密码修改）

    <configuration>
    <!-- 存储元数据mysql相关配置 -->
    <property>
    	<name>javax.jdo.option.ConnectionURL</name>
    	<value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
    </property>
    
    <property>
    	<name>javax.jdo.option.ConnectionDriverName</name>
    	<value>com.mysql.jdbc.Driver</value>
    </property>
    
    <property>
    	<name>javax.jdo.option.ConnectionUserName</name>
    	<value>root</value>
    </property>
    
    <property>
    	<name>javax.jdo.option.ConnectionPassword</name>
    	<value>hadoop</value>
    </property>
    
    <!-- H2S运行绑定host -->
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>node1</value>
    </property>
    
    <!-- 远程模式部署metastore metastore地址 -->
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://node1:9083</value>
    </property>
    
    <!-- 关闭元数据存储授权  --> 
    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
    </property>
    </configuration>

上传mysql jdbc驱动到hive安装包lib下

/export/server/apache-hive-3.1.2-bin/lib

上传

初始化元数据

cd /export/server/apache-hive-3.1.2-bin/

bin/schematool -initSchema -dbType mysql -verbos

在hdfs创建hive存储目录（如存在则不用操作）
  hadoop fs -mkdir /tmp
  hadoop fs -mkdir -p /user/hive/warehouse
  hadoop fs -chmod g+w /tmp
  hadoop fs -chmod g+w /user/hive/warehouse

metastore服务启动方式

前台启动 进程一直占据终端 ctrl+c结束进程关闭服务

/export/server/apache-hive-3.1.2-bin/bin/hive --service metastore

后台启动 输出日志在/root下nohup.out

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore&（两次回车）

（可能哪里有问题  复制上文 前台启动 的路径）

jps查看(Runjar)

kill -9 进程号

Hive客户端使用

Hive自带客户端-----

 老客户端：安装目录下$HIVE_HOME/bin/hive

新客户端：$HIVE_HOME/bin/beeline(经过两个)

hiveserver2 启动

远程模式下beeline通过 Thrift 连接到单独的HiveServer2服务上

只使用一代客户端

只需Metastore

二代：启动两个（启动HiveServer2之前必须先首先启动metastore）

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore&（metastore）（有错看上）
nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2&（有错看上）

客户端操作连接

(客户端与服务在一台机器上不直观)

scp -r /export/server/apache-hive-3.1.2-bin root@node3:/export/server/(拷贝到其他机器下)

node3

$HIVE_HOME/bin/hive（第一代）

node1

第二代

/export/server/apache-hive-3.1.2-bin/bin/beeline’

输入

! connect jdbc:hive2://node1:10000

root

密码直接回车

输入语法操作

Hive可视化客户端----DataGrip

DataGrip、Dbeaver、SQuirrel SQL Client等可以在Windows、MAC平台中通过JDBC连接HiveServer2的图形界面工具

DateGrip

（安装路径中文无空格）

指定文件存储位置

新建项目

右键·空白xuanze

选择你创建的文件夹  使得之后创建的文件存储到其中

创建sql文件

配置链接hive

改驱动

 

 -号删除

用资料里的

+号找

name：随便写（node1_hive）

Host:node1.itcast.cn(下面有个测试)

root   apply ok

Hive可视化工具--IntelliJ IDEA  新

驱动配置

Database

 

 -号删除驱动

添加驱动（Apache  2  资料软件里）

 连接数据库

 

 

 校验

出现勾  则成功

 写 

如 src下直接创建  File    （）.sql

hive.sql文件名

选择  执行右键   Execute

切换数据库

在编辑区展示结果

创建新的会话

Hive SQL语言（HQL）之 DDL

DDL数据定义语言     是·SQL中对数据库内部的对象结构进行创、删、改的操作语言

不涉及内部数据操作

读写文件机制SerDe  

desc formatted lll;

 lll是表名

 查看表的SerDe信息

数据库database与建库

使用beeline

在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的/user/hive/warehouse下
用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下

create database创建数据库

creat (Database|Schema)[IF NOT EXISTS] database_name

（例create database itcase（建itheima itcast)

comment "this is firse"

with dbproperties('createdBy'='Allen')）

[COMMENT database_comment（注解)]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

COMMENT：数据库的注释说明语句(给数据添加注释说明)

LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db（最好不要指定）

WITH DBPROPERTIES：用于指定一些数据库的属性配置

（句子后加上；）

 use database切换数据库

use database database名字;

（例use database itcase）

切换到数据库  之后操作默认在该数据库下操作

删除数据库drop

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

默认为空 数据库不为空无法删除

不为空：加上CASCADE参数

表与建表

一个表要有   表名   字段  字段之间分隔符

表包含带有数据的记录（行）

建表完整语法树    

蓝色是建表关键字   [ ]表示括号中的语法表示可选  |表示2选1  语法顺序要和语法树中的保持一致

建表语法

create table  [db.name(库名)].table_name

(col_name data_type[COMMENT col_comment（注释 也可以-- l类似#）],.....)

[COMMENT table_comment]
[ROW FORMAT DELIMITED …（指定分隔符）];

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

 [ ]中括号的语法表示可选
建表语句中的语法顺序要和语法树中顺序保持一致

 最低限度必须包括的语法为：CREATE TABLE table_name (col_name data_type);

不写库名默认当前

删除表

drop table 表名字；

分隔符指定语法 row format关键字   新

LazySimpleSerDe是Hive默认的，4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号

LazySimpleSerDe分隔符指定

 二选其一  如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据

如果文件格式特殊 可以使用 row format serde serde_name来指定Serde类来处理数据  支持用户自定义的SerDe类

          不指定分隔符 默认分隔符

建表时不指定分隔符

默认的分割符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的

vim 连续按下ctrl+v/ctrl +a即可输入‘’\001' 显示^A

文本编辑器显示        SOH

默认存储路径   location更改数据在HDFS上的存储路径

/uer/hive/warehouse/库/表

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

(一般别改)

location 指定数据在HDFS上存储路径

location '<hdfs_location>'

Hive内外部表

选择内外部表 

内部表

内部表（Internal table） 也被称为Hive拥有和管理的托管表（Managed tadle）

内部表被Hive完全控制   删除内部表删除数据以及表的元数据

          查看表的元数据信息

desc formatted t_team_ace_player（表名字）;

查看元数据描述信息

describe formatted t_team_ace_player;--那个是表名字

外部表

加上关键字external指定外部表

create external table student_ext(
   num int,
   name string,
   sex string,
   age int,
   dept string)
row format delimited
fields terminated by ','
location '/stu';

外部表中的数据 不是Hive拥有或管理的 只管理元数据的生命周期

删除外部表 自会删除元数据，不会删除实际数据（。。.txt） 在Hive外部仍然可以访问实际数据

外部表搭配location     不指定路径创建在默认路径下

分区表

用处：优化查询

现实中多个文件上传到一个表上   数据大查询慢  如下

六个文件上传到一个表上

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/itheima.db/t_all_hero

 查询 射手信息   只需查询archer.txt文件里的 于是便有了分区表

根据指定的字段对表进行分区

 语法

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";

 分区字段不能是表中已经存在的字段   （如上不能是id、name、hp_max、mp_max、attack_max、defense_max、attack_range、role_main、role_assist）

          分区表数据加载方式--静态分区-----------

静态分区是指分区的属性值是由用户在在加载数据的时候手动指定的

分区值是写死的

load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(role='cike');
load data local inpath '/root/hivedata/mage.txt' into table t_all_hero_part partition(role='fashi');
load data local inpath '/root/hivedata/support.txt' into table t_all_hero_part partition(role='fuzhu');
load data local inpath '/root/hivedata/tank.txt' into table t_all_hero_part partition(role='tanke');
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(role='zhanshi');

          分区表存储方式变化

点开表后

 不同分区对应不同文件夹 同一分区的数据存储在同一文件夹下

--非分区表 全表扫描过滤查询
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;
--分区表 先基于分区过滤 再查询
select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;

             多重分区表----------

partitioned by (p1 data_type1,p2 data_type2) 可以指定多个字段

 多重分区之下，分区是一种递进关系，是在前一个分区的基础上继续分区

注意分区字段的顺序 谁在前在后

-----多重分区表
--单分区表，按省份分区
create table t_user_province (id int, name string,age int) partitioned by (province string);
--双分区表，按省份和市分区
--分区字段之间是一种递进的关系 因此要注意分区字段的顺序 谁在前在后
create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);

           多重分区表的加载

--双分区表的数据加载 静态分区加载数据
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city
    partition(province='zhejiang',city='hangzhou');
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city
    partition(province='zhejiang',city='ningbo');
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city
    partition(province='shanghai',city='pudong');

如第一个在   province=zhejiang  下  创建了  city=hangzhou

           多重分区的查询

-双分区表的使用  使用分区进行过滤 减少全表扫描 提高查询效率
select * from t_user_province_city where  province= "zhejiang" and city ="hangzhou";

           分区表_动态分区插入------------

动态加载 指的是 分区的字段是基于查询结果自动推断出来的

核心语法  insert+select

            启用动态分区参数设置

#是否开启动态分区功能
set hive.exec.dynamic.partition=true;
#指定动态分区模式，分为nonstick非严格模式和strict严格模式
#strict严格模式要求至少一个分区为静态分区
set hive.exec.dynamic.partition.mode=nonstrict;

             语法  

要用两张表

一张表有全部数据    根据第一张表的筛选来确定第二张表

--创建一张新的分区表 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";

 先把数据存放在一张表中

这是   t_all_hero表

insert into table t_all_hero_part_dynamic partition(role) --注意这里 分区值并没有手动写死指定
select tmp.*,tmp.role_main from t_all_hero tmp;

 选择t_all_hero 表所有数据插入 dynamic    tmp.role 为pratitonned里的role

tmp.* 指的是所有字段 (tmp是固定的)     根据role_main的值进行分区

          分区表注意事项

分区表是优化手段  非必须

分区字段是虚拟字段   其数据并不存储在底层数据中

分桶表

概念

分桶表bucket    是一种用于优化查询而设计的语言 

分区表 ：不同文件夹

分桶表：文件分为若干部分   被拆分成若干个独立的小文件

实操

实操：（创建一张表 映射数据 上传文件）

 点击后按F4（show databases验证）

新建SQl文件（1、creat_table）

create table t_archer(
      id int comment "ID",
      name string comment "英雄名称",
      hp_max int comment "最大生命",
      mp_max int comment "最大法力",
      attack_max int comment "最高物攻",
      defense_max int comment "最大物防",
      attack_range string comment "攻击范围",
      role_main string comment "主要定位",
      role_assist string comment "次要定位"
) comment "王者荣耀射手信息"
row format delimited
fields terminated by "\t";

 下面的 comment是表的注释

 

选中运行

 建表之后要有数据

           1上传文件直接到网页/ 该表的位置

/user/hive/warehouse/itheima/t.archer

上传数据集

 

 看表看

            2上传文件到指定linux指定文件夹下     

例子：我把表中的数据   这个文件上传到 /root/hivedata中

hadoop fs -put archer.txt /user/hive/warehouse/itit2.db/t_archer
           （数据文件名  相对路径）

 上传数据文件

select *from t_archer;

 查看表内容

实例2    复杂数据类型  map类型

如下的文件   映射到表中    发现有：

则是map类型   类似于字典

1,孙悟空,53,西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688
2,鲁班七号,54,木偶奇遇记:288-福禄兄弟:288-黑桃队长:60-电玩小子:2288-星空梦想:0
3,后裔,53,精灵王:288-阿尔法小队:588-辉光之辰:888-黄金射手座:1688-如梦令:1314
4,铠,52,龙域领主:288-曙光守护者:1776
5,韩信,52,飞衡:1788-逐梦之影:888-白龙吟:1188-教廷特使:0-街头霸王:888

 指定 skin_rate为map类型    键是string   值是int

create table t_hot_hero_skin_price(
      id int,
      name string,
      win_rate int,
      skin_price map<string,int>
)

            分隔符 指定

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之间分隔符;

实例3 -默认分隔符的使用

写文件时优先考虑\001分隔符

create table t_team_ace_player(
                                  id int,
                                  team_name string,
                                  ace_player_name string
);

1成都AG超玩会一诺
2重庆QGhappyHurt
3DYG久诚
4上海EDG.M浪浪
5武汉eStarProCat
6RNG.M暴风锐
7RW侠渡劫
8TES滔搏迷神
9杭州LGD大鹅伪装
10南京Hero久竞清融

实例4 location 指定路径

题：把txt文件  上传到HDFS任意路径下，不能移动复制，并在Hive中建表映射成功该文件

create table t_team_ace_player_location(
                                           id int,
                                           team_name string,
                                           ace_player_name string)
    location '/user/hive/warehouse/itit2.db/'; --使用location关键字指定本张表数据在hdfs上的存储路径

select * from t_team_ace_player_location;

 创建一个表 可以直接指定路径（当是文件所在路径则可以直接show）

数据类型

指的是表中列的类型 使用最多字符串（string）与数字类型（int）

 原生数据类型（primitive data type）：数值类型、时间日期类型、字符串类型、杂项数据类型

复杂数据类型（complex data type）：array数组、map映射、struct结构、union联合体

array数组   map映射 struct结构

英文一般不分大小写    支持JAVA数据类型  

如果定义的数据类型和文件不一致，Hive会尝试隐式转换，但不一定成功

显式转换CAST         例子：CAST('100' as INT)会将100字符串转换为100整数值

如果强制转换失败  返回NULL

注释中文乱码

mysql -u root -p(登陆mysql)

use hive3；

执行文件中

删表 重新创建（运行上述代码）

常见的show语法

显示所有数据库

SCHEMAS和DATABASES的用法 功能一样
show databases;
show schemas;（database=schemas）

显示当前数据库所有表

show tables;
SHOW TABLES [IN database_name]; --指定某个数据库

(show tadles in 数据库名）