桥下の民 - Hive
type: channel
link: https://wordplay.work/index.php/category/Hive
items: Hive 生产调优
items: Hive 文件格式和压缩
items: Hive 分区表和分桶表
items: Hive DML(Data Manipulation Language)数据操作
items: Hive DDL(Data Definition Language)数据定义
items: Hive入门&安装

Hive 生产调优
type: item
date: 2022-03-16T01:08:00+00:00
description: 一、计算资源配置 计算环境为 Hive on MR。计算资源的调整主要包括 Yarn 和 MR。 1.1、Yarn资源配置 修改$HADOOP_HOME/etc/hadoop/yarn-site.xml文件。 需要调整的 Yarn 参数均与 CPU、内存等资源有关，核心配置参数如下。 （1）yarn.nodemanager.resource.memory-mb 该参数的含义是，一个 NodeManager 节点分配给 Container 使用的内存。该参数的配置，取决于 NodeManager 所在节点的总内存容量和该节点运行的其他服务的数量。 考虑上述因素，此处可将该参数设置为64G，如下： <property> <name>yarn.nodemanager.resource.memory-mb</name> <value>65536</value> </property> （2）yarn.nodemanager.resource.cpu-vcores 该参数的含义是，一个 NodeManager 节点分配给 Container 使用的 CPU 核数。该参数的配置，同样取决于 NodeManager 所在节点的总 CPU 核数和该节点运行的其他服务。 考虑上述因素，此处可将该参数设置为16。 <property> <name>yarn.nodemanager.resource.cpu-vcores</name> <value>16</value> </property> （3）yarn.scheduler.maximum-allocation-mb 该参数的含义是，单个 Container 能够使用的最大内存。推荐配置如下： <property> <name>yarn.scheduler.maximum-allocation-mb</name> <value>16384</value> </property> （4）yarn.scheduler.minimum-allocation-mb 该参数的含义是，单个 Container 能够使用的最小内存，推荐配置如下： <property> <name>yarn.scheduler.minimum-allocation-mb</name> <value>512</value> </property> 1.2、MapReduce资源配置 MapReduce 资源配置主要包括 Map Task 的内存和 CPU核数，以及 Reduce Task 的内存和 CPU核数。核心配置参数如下： 1）mapreduce.map.memory.mb 该参数的含义是，单个 Map Task 申请的 container 容器内存大小，其默认值为1024。该值不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。 该参数需要根据不同的计算任务单独进行配置，在 hive 中，可直接使用如下方式为每个SQL语句单独进行配置： set mapreduce.map.memory.mb=2048; 2）mapreduce.map.cpu.vcores 该参数的含义是，单个 Map Task 申请的 container 容器 cpu核数，其默认值为1。该值一般无需调整。 3）mapreduce.reduce.memory.mb 该参数的含义是，单个 Reduce Task 申请的 container 容器内存大小，其默认值为 1024。该值同样不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。 该参数需要根据不同的计算任务单独进行配置，在hive中，可直接使用如下方式为每个SQL语句单独进行配置： set mapreduce.reduce.memory.mb=2048; 4）mapreduce.reduce.cpu.vcores 该参数的含义是，单个 Reduce Task 申请的 container 容器 cpu核数，其默认值为1。该值一般无需调整。 二、 测试用表 2.1、订单表(2000w条数据) 1）表结构 2）建表语句 hive (default)> drop table if exists order_detail; create table order_detail( id string comment '订单id', user_id string comment '用户id', product_id string comment '商品id', province_id st…

Hive 文件格式和压缩
type: item
date: 2022-03-15T01:59:00+00:00
description: 一、 Hadoop压缩概述 压缩格式 算法 文件扩展名 是否可切分 DEFLATE DEFLATE .deflate 否 Gzip DEFLATE .gz 否 bzip2 bzip2 .bz2 是 LZO LZO .lzo 是 Snappy Snappy .snappy 否 为了支持多种压缩/解压缩算法，Hadoop 引入了编码/解码器，如下表所示： Hadoop 查看支持压缩的方式hadoop checknative。 Hadoop 在 driver 端设置压缩。 压缩格式 对应的编码/解码器 DEFLATE org.apache.hadoop.io.compress.DefaultCodec gzip org.apache.hadoop.io.compress.GzipCodec bzip2 org.apache.hadoop.io.compress.BZip2Codec LZO com.hadoop.compression.lzo.LzopCodec Snappy org.apache.hadoop.io.compress.SnappyCodec 压缩性能的比较： 压缩算法 原始文件大小 压缩文件大小 压缩速度 解压速度 gzip 8.3GB 1.8GB 17.5MB/s 58MB/s bzip2 8.3GB 1.1GB 2.4MB/s 9.5MB/s LZO 8.3GB 2.9GB 49.3MB/s 74.6MB/s 二、Hive文件格式 为 Hive 表中的数据选择一个合适的文件格式，对提高查询性能的提高是十分有益的。Hive 表数据的存储格式，可以选择 text file、orc、parquet、sequence file等。 2.1、Text File 文本文件是 Hive 默认使用的文件格式，文本文件中的一行内容，就对应 Hive 表中的一行记录。 可通过以下建表语句指定文件格式为文本文件: create table textfile_table (column_specs) stored as textfile; 2.2、ORC 1）文件格式 ORC（Optimized Row Columnar） file format 是 Hive 0.11 版里引入的一种列式存储的文件格式。ORC 文件能够提高 Hive 读写数据和处理数据的性能。 与列式存储相对的是行式存储，下图是两者的对比： 如图所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。 （1）行存储的特点 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。 （2）列存储的特点 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。 前文提到的text file和sequence file都是基于行存储的，orc和parquet是基于列式存储的。 ORC 文件的具体结构如下图所示： 每个 ORC 文件由 Header、Body 和 Tail 三部分组成。 Header 内容为 ORC，用于表示文件类型。 Body 由1个或多个 stripe 组成，每个 stripe 一般为 HDFS 的块大小，每一个 stripe 包含多条记录，这些记录按照列进行独立存储，每个 stripe 里有三部分组成，分别是 Index Data，Row Data，Stripe Footer。 Index Data：一个轻量级的 index，默认是为各列每隔 1W 行做一个索引。每个索引会记录第 n 万行的位置，和最近一万行的最大值和最小值等信息。 Row Data：存的是具体的数据，按列进行存储，并对每个列进行编码，分成多个 Stream 来存储。 Stripe Footer：存放的是各个 Stream 的位置以及各 column 的编码信息。 Tail 由 File Footer 和 PostScript 组成。File Footer 中保存了各 Stripe 的其实位置、索引长度、数据长度等信息，各 Column 的统计信息等；PostScript 记录了整个文件的压缩类型以及 File Footer 的长度信息等。 在读取 ORC 文件时，会先从最后一个字节读取 PostScript 长度，进而读取到 PostScript，从里面解析到 File Footer 长度，进而读取 FileFooter，从中解析到各个 Stripe 信息，再读各个 Stripe，即从后往前读。 2）建表语句 create table orc_table (column_spe…

Hive 分区表和分桶表
type: item
date: 2022-03-15T01:36:00+00:00
description: 一、分区表和分桶表 Hive 中的分区就是把一张大表的数据按照业务需要分散的存储到多个目录，每个目录就称为该表的一个分区。在查询时通过 where 子句中的表达式选择查询所需要的分区，这样的查询效率会提高很多。 1.1、分区表基本语法 1、创建分区表 hive (default)> create table dept_partition ( deptno int, --部门编号 dname string, --部门名称 loc string --部门位置 ) partitioned by (day string) row format delimited fields terminated by '\t'; 2、分区表读写数据 1）写数据 （1）load 1、数据准备 在/opt/module/hive/datas/路径上创建文件dept_20220401.log，并输入如下内容。 [atguigu@hadoop102 datas]$ vim dept_20220401.log 10 行政部 1700 20 财务部 1800 2、装载语句 hive (default)> load data local inpath '/opt/module/hive/datas/dept_20220401.log' into table dept_partition partition(day='20220401'); （2）insert 将day='20220401'分区的数据插入到day='20220402'分区，可执行如下装载语句 hive (default)> insert overwrite table dept_partition partition (day = '20220402') select deptno, dname, loc from dept_partition where day = '20220401'; 2）读数据 查询分区表数据时，可以将分区字段看作表的伪列，可像使用其他字段一样使用分区字段。 select deptno, dname, loc ,day from dept_partition where day = '20220401'; 3、分区表基本操作 1）查看所有分区信息 hive> show partitions dept_partition; 2）增加分区 （1）创建单个分区 hive (default)> alter table dept_partition add partition(day='20220403'); （2）同时创建多个分区（分区之间不能有逗号） hive (default)> alter table dept_partition add partition(day='20220404') partition(day='20220405'); 3）删除分区 （1）删除单个分区 hive (default)> alter table dept_partition drop partition (day='20220403'); （2）同时删除多个分区（分区之间必须有逗号） hive (default)> alter table dept_partition drop partition (day='20220404'), partition(day='20220405'); 4）修复分区 Hive 将分区表的所有分区信息都保存在了元数据中，只有元数据与 HDFS 上的分区路径一致时，分区表才能正常读写数据。若用户手动创建/删除分区路径，Hive都是感知不到的，这样就会导致 Hive 的元数据和 HDFS 的分区路径不一致。再比如，若分区表为外部表，用户执行drop partition命令后，分区元数据会被删除，而 HDFS 的分区路径不会被删除，同样会导致 Hive 的元数据和 HDFS 的分区路径不一致。 若出现元数据和 HDFS 路径不一致的情况，可通过如下几种手段进行修复。 （1）add partition 若手动创建 HDFS 的分区路径，Hive 无法识别，可通过add partition命令增加分区元数据信息，从而使元数据和分区路径保持一致。 （2）drop partition 若手动删除HDFS的分区路径，Hive无法识别，可通过drop partition命令删除分区元数据信息，从而使元数据和分区路径保持一致。 （3）msck 若分区元数据和 HDFS 的分区路径不一致，还可使用msck命令进行修复，以下是该命令的用法说明。 hive (default)> msck repair table table_name [add/drop/sync partitio…

Hive DML(Data Manipulation Language)数据操作
type: item
date: 2022-03-14T14:58:00+00:00
description: 一、Load Load 语句可将文件导入到 Hive 表中。 1）语法 hive> LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]; 关键字说明： （1）local：表示从本地加载数据到 Hive 表；否则从 HDFS 加载数据到 Hive 表。 （2）overwrite：表示覆盖表中已有数据，否则表示追加。 （3）partition：表示上传到指定分区，若目标是分区表，需指定分区。 2）实操示例 （0）创建一张表 hive (default)> create table student( id int, name string ) row format delimited fields terminated by '\t'; （1）加载本地文件到 hive hive (default)> load data local inpath '/opt/module/datas/student.txt' into table student; （2）加载 HDFS 文件到 hive 中 1、上传文件到 HDFS [atguigu@hadoop102 ~]$ hadoop fs -put /opt/module/datas/student.txt /user/atguigu 2、加载 HDFS 上数据，导入完成后去 HDFS 上查看文件是否还存在 hive (default)> load data inpath '/user/atguigu/student.txt' into table student; （3）加载数据覆盖表中已有的数据 1、上传文件到 HDFS hive (default)> hdfs -put /opt/module/datas/student.txt /user/atguigu; 2、加载数据覆盖表中已有的数据 hive (default)> load data inpath '/user/atguigu/student.txt' overwrite into table student; 二、Insert 2.1、将查询结果插入表中 1）语法 INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement; 关键字说明： （1）INTO：将结果追加到目标表 （2）OVERWRITE：用结果覆盖原有数据 2）示例 （1）新建一张表 hive (default)> create table student1( id int, name string ) row format delimited fields terminated by '\t'; （2）根据查询结果插入数据 hive (default)> insert overwrite table student1 select id, name from student; 2.2、将给定Values插入表中 1）语法 INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...] 2）示例 hive (default)> insert into table student1 values(1,'wangwu'),(2,'zhaoliu'); 2.3、将查询结果写入目标路径 1）语法 INSERT OVERWRITE [LOCAL] DIRECTORY directory [ROW FORMAT row_format] [STORED AS file_format] select_statement; 2）示例 insert overwrite local directory '/opt/module/datas/student' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' select id,name from student; 三、Export&Import Export 导出语句可将表的数据和元数据信息一并到处的 HDFS 路径，Import 可将 Export 导出的内容导入 Hive，表的数据和元数据信息都会恢复。Export 和 Import 可用于两个 H…

Hive DDL(Data Definition Language)数据定义
type: item
date: 2022-03-14T14:44:00+00:00
description: 一、数据库（database） 1.1、创建数据库 1）语法 CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; 2）示例 （1）创建一个数据库，不指定路径 hive (default)> create database db_hive1; 注：若不指定路径，其默认路径为：${hive.metastore.warehouse.dir}/database_name.db （2）创建一个数据库，指定路径 hive (default)> create database db_hive2 location '/db_hive2'; （2）创建一个数据库，带有dbproperties hive (default)> create database db_hive3 with dbproperties('create_date'='2022-11-18'); 1.2、查询数据库 1）展示所有数据库 （1）语法 SHOW DATABASES [LIKE 'identifier_with_wildcards']; 注：like通配表达式说明：*表示任意个任意字符，|表示或的关系。 （2）示例 hive> show databases like 'db_hive*'; OK db_hive_1 db_hive_2 2）查看数据库信息 （1）语法 DESCRIBE DATABASE [EXTENDED] db_name; （2）示例 1、查看基本信息 hive> desc database db_hive3; OK db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db atguigu USER 2、查看更多信息 hive> desc database extended db_hive3; OK db_name comment location owner_name owner_type parameters db_hive3 hdfs://hadoop102:8020/user/hive/warehouse/db_hive3.db atguigu USER {create_date=2022-11-18} 1.3、修改数据库 用户可以使用alter database命令修改数据库某些信息，其中能够修改的信息包括dbproperties、location、owner user。需要注意的是：修改数据库 location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。 1）语法 --修改dbproperties ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...); --修改location ALTER DATABASE database_name SET LOCATION hdfs_path; --修改owner user ALTER DATABASE database_name SET OWNER USER user_name; 2）示例 （1）修改 dbproperties hive> ALTER DATABASE db_hive3 SET DBPROPERTIES ('create_date'='2022-11-20'); 1.4 删除数据库 1）语法 DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE]; 注：RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。 CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。 2）示例 （1）删除空数据库 hive> drop database db_hive2; （2）删除非空数据库 hive> drop database db_hive3 cascade; 1.5、切换当前数据库 1）语法 USE database_name; 二、表（table） 2.1、创建表 2.1.1、语法 1）普通建表 （1）完整语法 CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name data_type [COMMENT col_comment], ...)] [C…

Hive入门&安装
type: item
date: 2022-03-14T09:21:00+00:00
description: 一、Hive入门 1.1、关于Hive 1）Hive简介 Hive 是由Facebook 开源，基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。 那为什么会有Hive呢？它是为了解决什么问题而诞生的呢？ 下面通过一个案例，来快速了解一下Hive。 例如：需求，统计单词出现个数。 （1）在 Hadoop 中我们用 MapReduce 程序实现的，当时需要写Mapper、Reducer和Driver三个类，并实现对应逻辑，相对繁琐。 （2）如果通过Hive SQL实现，一行就搞定了，简单方便，容易理解。 select count(*) from test group by id; 2）Hive本质 Hive 是一个 Hadoop 客户端，用于将HQL（Hive SQL）转化成MapReduce程序。 （1）Hive 中每张表的数据存储在 HDFS。 （2）Hive 分析数据底层的实现是 MapReduce（也可配置为Spark或者Tez）。 （3）执行程序运行在 Yarn 上。 1.2、Hive架构原理 1.2.1、用户接口：Client CLI（command-line interface）、JDBC/ODBC。 说明：JDBC 和 ODBC 的区别。 （1）JDBC 的移植性比 ODBC 好；（通常情况下，安装完 ODBC 驱动程序之后，还需要经过确定的配置才能够应用。而不相同的配置在不相同数据库服务器之间不能够通用。所以，安装一次就需要再配置一次。JDBC 只需要选取适当的 JDBC 数据库驱动程序，就不需要额外的配置。在安装过程中，JDBC 数据库驱动程序会自己完成有关的配置。） （2）两者使用的语言不同，JDBC 在 Java 编程时使用，ODBC 一般在 C/C++ 编程时使用。 1.2.2、元数据：Metastore 元数据包括：数据库（默认是default）、表名、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。 默认存储在自带的derby数据库中，由于 derby 数据库只支持单客户端访问，生产环境中为了多人开发，推荐使用 MySQL 存储 Metastore。 1.2.3、驱动器：Driver （1）解析器（SQLParser）：将SQL字符串转换成抽象语法树（AST） （2）语义分析（Semantic Analyzer）：将AST进一步划分为QeuryBlock （3）逻辑计划生成器（Logical Plan Gen）：将语法树生成逻辑计划 （4）逻辑优化器（Logical Optimizer）：对逻辑计划进行优化 （5）物理计划生成器（Physical Plan Gen）：根据优化后的逻辑计划生成物理计划 （6）物理优化器（Physical Optimizer）：对物理计划进行优化 （7）执行器（Execution）：执行该计划，得到查询结果并返回给客户端 抽象语法树： 逻辑计划与物理计划： 1.2.4、Hadoop 使用 HDFS 进行存储，可以选择 MapReduce/Tez/Spark 进行计算。 二、Hive安装 2.1、Hive安装地址 1）Hive官网地址 http://hive.apache.org/ 2）文档查看地址 https://cwiki.apache.org/confluence/display/Hive/GettingStarted 3）下载地址 http://archive.apache.org/dist/hive/ 4）github地址 https://github.com/apache/hive 2.2、Hive安装部署 2.2.1、安装Hive 1）把apache-hive-3.1.3-bin.tar.gz上传到Linux的/opt/software目录下 2）解压apache-hive-3.1.3-bin.tar.gz到/opt/module/目录下面 tar -zxvf /opt/software/apache-hive-3.1.3-bin.tar.gz -C /opt/module/ 3）修改apache-hive-3.1.3-bin.tar.gz的名称为hive [atguigu@hadoop102 software]$ mv /opt/module/apache-hive-3.1.3-bin/ /opt/module/hive 4）修改/etc/profile.d/my_env.sh，添加环境变量 sudo vim /etc/profile.d/my_env.sh （1）添加内容 #HIVE_HOME export HIVE_HOME=/opt/module/hive export PAT…