# Hive-demo **Repository Path**: CandyPop/hive-demo ## Basic Information - **Project Name**: Hive-demo - **Description**: No description available - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2022-04-04 - **Last Updated**: 2022-10-08 ## Categories & Tags **Categories**: Uncategorized **Tags**: hive ## README #### Hive 本质上，Hive是基于Hadoop的一个**仓库工具**，可以将结构化的数据文件映射成一张表，并提供类似Sql查询功能。 将HQL转化为MapReduce程序 * Hive 处理的数据存储在 HDFS * Hive 分析数据底层的实现是 MapReduce * 执行程序运行在 Yarn 上 Hadoop有集群，Hive没有，他只是Hadoop数据访问客户端 ##### 优点 （1）操作接口采用类 SQL 语法，提供快速开发的能力（简单、容易上手）。 （2）避免了去写 MapReduce，减少开发人员的学习成本。 （3）Hive 的执行延迟比较高，因此 Hive 常用于数据分析，对实时性要求不高的场合。 （4）Hive 优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较 高。 （5）Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。 ##### 缺点 1）**Hive** **的** **HQL** **表达能力有限** （1）迭代式算法无法表达 （2）数据挖掘方面不擅长，由于 MapReduce 数据处理流程的限制，效率更高的算法却 无法实现。 **2**）**Hive** **的效率比较低** （1）Hive 自动生成的 MapReduce 作业，通常情况下不够智能化 （2）Hive 调优比较困难，粒度较粗 ##### Hive架构 ![1649063403863](./img/1649063403863.png) **1**）用户接口：**Client** CLI（command-line interface）、JDBC/ODBC(jdbc 访问 hive)、WEBUI（浏览器访问 hive） 元数据：**Metastore** 元数据包括：表名、表所属的数据库（默认是 default）、表的拥有者、列/分区字段、 表的类型（是否是外部表）、表的数据所在目录等； 默认存储在自带的 derby 数据库中，推荐使用 MySQL 存储 Metastore **3**）**Hadoop** 使用 HDFS 进行存储，使用 MapReduce 进行计算。 **4**）驱动器：**Driver** （1）解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第 三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存在、SQL 语义是否有误。 （2）编译器（Physical Plan）：将 AST 编译生成逻辑执行计划。 （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。 （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来 说，就是 MR/Spark。 ![1649063861019](./img/1649063861019.png) Hive 通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的 Driver， 结合元数据(MetaStore)，将这些指令翻译成 MapReduce，提交到 Hadoop 中执行，最后，将 执行返回的结果输出到用户交互接口。 ``` http://hive.apache.org/ ``` 下载完成后，配置环境变量 ``` vim /etc/profile #HIVE_HOME export HIVE_HOME= 安装目录 export PATH=$PATH:$HIVE_HOME/bin #export PATH=$PATH:$HIVE_HOME/sbin ``` 解决日志jar包冲突，可做可不做 ``` mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak ``` 初始化元数据，由于hive默认使用apache的derby数据库。所以这里初始化一下，到安装目录 ``` bin/schematool -dbType derby -initSchema ``` ##### 启动Hive 但是之前，需要先启动hadoop的hdfs服务 可以当做一个jdbc连接器 ``` bin/hive ``` ![QQ图片20220404175709](./img/QQ图片20220404175709.png) 日志默认放在/tmp/当前登录者目录下![QQ图片20220404175833](./img/QQ图片20220404175833.png)hive的用户名 然后我们尝试去登陆这个hive，来创建一张表来写点数据 ![QQ图片20220404181131](./img/QQ图片20220404181131.png) ![QQ图片20220404181135](./img/QQ图片20220404181135.png) ![QQ图片20220404181138](./img/QQ图片20220404181138.png) 可以看到，插入数据相当于启动了一个MapReduce的任务，去执行插入数据。 ##### 存储方式由Derby修改为MySql 为何要修改metadb的方式，主要是因为derby的元数据不好看，还一个问题就是derby是单用户的，如果你同时启用两个用户去使用，就会报错。所以我们需要替换成mysql ``` 将mysql连接驱动包，放到hive的lib目录下，同时在conf目录下创建一个hive-site.xml 文件，将配置信息粘贴进去。 ``` ```xml javax.jdo.option.ConnectionURL jdbc:mysql://hadoop102:3306/metastore?useSSL=false javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName root javax.jdo.option.ConnectionPassword 000000 hive.metastore.schema.verification false hive.metastore.event.db.notification.api.auth false hive.metastore.warehouse.dir /user/hive/warehouse ``` 然后初始化 ``` 在此之前，需要在mysql创建好，之前，我们定义好的库名 schematool -initSchema -dbType mysql -verbose ``` ![QQ图片20220404200730](./img/QQ图片20220404200730.png) 切换成功。![QQ图片20220404201500](./img/QQ图片20220404201500.png) ![QQ图片20220404201502](./img/QQ图片20220404201502.png) 只要元数据存在，且hdfs有数据，hive就可以查询到。 你甚至不通过hive的insert方法，直接使用put指令只要按照我们之前配置的路径，就可以查询到 ``` /user/hive/warehouse/test ``` ##### 使用元数据方式连接Hive 因为Hive本身是一个客户端，并不是一个服务端，所以需要有一个可以支持提供服务的Hive，来让第三方能够连接，这样我们才可以随意的使用Hive的功能。 ```xml hive.metastore.uris thrift://hadoop102:9083 ``` 请注意，一旦这个配置上去了，你通过bin/hive就无法使用语句，因为服务并没有启动，这个时候，你需要启动hive的metastore服务。 ``` hive --service metastore ``` 这样可以正常连接。 ##### 使用JDBC连接 ```xml hive.server2.thrift.bind.host hadoop102 hive.server2.thrift.port 10000 ``` 请注意，你想要使用jdbc连接，需要先启动metasore服务，然后启动hiveserver2服务 ``` bin/hive --service hiveserver2 ``` 然后连接，这里多等待一会，你也可以监听/tmp/登陆hive用户/hive.log 来看初始化进度 ``` bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu ``` 默认没有密码。 他的流程是 ``` client--hive的jdbc-->hiveserver2---->metasore--mysql的jdbc驱动-->hdfs ``` 一个用于启动hive服务的脚本 ```bash #!/bin/bash HIVE_LOG_DIR=$HIVE_HOME/logs if [ ! -d $HIVE_LOG_DIR ] then mkdir -p $HIVE_LOG_DIR fi#检查进程是否运行正常，参数 1 为进程名，参数 2 为进程端口 function check_process() { pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}') ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut - d '/' -f 1) echo $pid [[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1 } function hive_start() { metapid=$(check_process HiveMetastore 9083) cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &" [ -z "$metapid" ] && eval $cmd || echo "Metastroe 服务已启动" server2pid=$(check_process HiveServer2 10000) cmd="nohup hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &" [ -z "$server2pid" ] && eval $cmd || echo "HiveServer2 服务已启动" } function hive_stop() { metapid=$(check_process HiveMetastore 9083) [ "$metapid" ] && kill $metapid || echo "Metastore 服务未启动" server2pid=$(check_process HiveServer2 10000) [ "$server2pid" ] && kill $server2pid || echo "HiveServer2 服务未启动" } case $1 in "start") hive_start ;; "stop") hive_stop ;; "restart") hive_stop sleep 2 hive_start ;; "status") check_process HiveMetastore 9083 >/dev/null && echo "Metastore 服务运行 正常" || echo "Metastore 服务运行异常" check_process HiveServer2 10000 >/dev/null && echo "HiveServer2 服务运 行正常" || echo "HiveServer2 服务运行异常" ;; *) echo Invalid Args! echo 'Usage: '$(basename $0)' start|stop|restart|status' ;; esac ``` 启动的话 ``` chmod +x $HIVE_HOME/bin/hiveservices.sh // 添加执行权限 sh hiveserver.sh start ``` 一些其他的参数 ``` [atguigu@hadoop102 hive]$ bin/hive -help usage: hive -d,--define Variable subsitution to apply to hive commands. e.g. -d A=B or --define A=B --database Specify the database to use -e SQL from command line -f SQL from files -H,--help Print help information --hiveconf Use value for given property --hivevar Variable subsitution to apply to hive commands. e.g. --hivevar A=B -i Initialization SQL file -S,--silent Silent mode in interactive shell -v,--verbose Verbose mode (echo executed SQL to the console) ``` **1****）“-e”不进入 **hive** **的交互窗口执行** **sql** **语句** [atguigu@hadoop102 hive]$ bin/hive -e "select id from student;" **2****）“-f”执行脚本中 **sql** **语句** （1）在/opt/module/hive/下创建 datas 目录并在 datas 目录下创建 hivef.sql 文件 ``` [root@hadoop102 datas]$ touch hivef.sql ``` （2）文件中写入正确的 sql 语句 select *from student; （3）执行文件中的 sql 语句 ``` [root@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql ``` （4）执行文件中的 sql 语句并将结果写入文件中 ``` [root@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql > /opt/module/datas/hive_result.txt ``` ###### 打印当前库和表信息 ```xml hive.cli.print.header true hive.cli.print.current.db true ``` ![1659343419831](./img/1659343419831.png) set语法的参数修改 ![1659344009512](./img/1659344009512.png) **1****）查看当前所有的配置信息** hive>set; **2****）参数的配置三种方式** （1）配置文件方式 默认配置文件：hive-default.xml 用户自定义配置文件：hive-site.xml 注意：用户自定义配置会覆盖默认配置。另外，Hive 也会读入 Hadoop 的配置，因为 Hive 是作为 Hadoop 的客户端启动的，Hive 的配置会覆盖 Hadoop 的配置。配置文件的设定对本 机启动的所有 Hive 进程都有效。 （2）命令行参数方式 启动 Hive 时，可以在命令行添加-hiveconf param=value 来设定参数。 例如： ``` [root@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10; ``` 注意：仅对本次 hive 启动有效 查看参数设置： ``` hive (default)> set mapred.reduce.tasks; ``` （3）参数声明方式 可以在 HQL 中使用 SET 关键字设定参数 例如： ``` hive (default)> set mapred.reduce.tasks=100; ``` 注意：仅对本次 hive 启动有效。 查看参数设置 ``` hive (default)> set mapred.reduce.tasks; ``` 上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系 统级的参数，例如 log4j 相关的设定，必须用前两种方式设定，因为那些参数的读取在会话 建立以前已经完成了。 基于以上的用法，我们可以将配置信息，和sql语句写到在一个文件，然后执行外部sql来初始化自定义的hive连接。 **注意** 你通过set进行参数设置的时候，里面的参数必须是`hive-default.xml.template`里面有的参数，该文件和`hive-site.xml`在同一级别 同时，-f这种执行外部命令的，一般可以用定时任务来执行，你用程序生成好了脚本，然后定时去执行 ##### 基本数据类型 ![1659367467302](./img/1659367467302.png) ![1659367496058](./img/1659367496058.png) 有这么一个数据 ```json { "name": "songsong", "friends": ["bingbing" , "lili"] , //列表 Array, "children": { //键值 Map, "xiao song": 18 , "xiaoxiao song": 19 } "address": { //结构 Struct, "street": "hui long guan", "city": "beijing" } } ``` 如果你想要导入这样一个结构的数据，你可以创建一个这样的文件，并写入这些内容 ``` songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing ``` 我们可以看到，这些用逗号隔开的，代表的是不同的字段，字段的值之间还有不同的分隔符区别他们，Array是`_`，Map是`：`，不同行的数据用换行符 ``` row format delimited fields terminated by ',' -- 列分隔符 collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号) map keys terminated by ':' -- MAP 中的 key 与 value 的分隔符 lines terminated by '\n'; -- 行分隔符 ``` 创建表 ```sql create table test3( name string, friends array, children map, address struct ) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' lines terminated by '\n'; //行的定义 ``` 你可以在hive中直接输入命令插入数据 ``` load data local inpath '/opt/module/hive/datas/test.txt' into table test; ``` 当然，你也可以直接在hadoop中在指定的表空间下放入test.txt资源文件，前提是你这个数据格式一定要符合你刚刚创建的test表，然后是通过这种hadoop的put方式的数据，虽然可以在hive中查询到，但是对应test表使用count命令可能会数据不准确，因为数据的数量和行数是记录在hive的元数据里的(metastore)中的，当你不通过hive来插入数据的时候，元数据也同样不会更新。也许你也会注意到，当你使用count命令的时候，hive也不会启动hadoop的mapreduce命令开启任务开索取数据，而是直接返回结果。 当时，如果你的数量和行数不一致的话，那么就会去尝试重新请求hadoop的md任务了。 ``` hive (default)> select friends[1],children['xiao song'],address.city from test where name="songsong"; OK _c0 _c1 city lili 18 beijing Time taken: 0.076 seconds, Fetched: 1 row(s) ``` 获取上面数据的，方式，Array字段类型可以直接使用索引，Map类型不用get，而是中括弧，里面写key的值，而struts类型，可以像是javabean一样，直接点出数据。 ##### Hive的基本数据转换 Hive 的原子数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如某表达式 使用 INT 类型，TINYINT 会自动转换为 INT 类型，但是 Hive 不会进行反向转化，例如，某表 达式使用 TINYINT 类型，INT 不会自动转换为 TINYINT 类型，它会返回错误，除非使用 CAST 操作。 ###### 隐身类型转换规则如下 （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。 （2）所有整数类型、FLOAT 和 **STRING** 类型都可以隐式地转换成 DOUBLE。 （3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。 （4）BOOLEAN 类型不可以转换为任何其它的类型。 ###### 可以使用CAST操作显示进行数据类型转换 例如 CAST('1' AS INT)将把字符串'1' 转换成整数 1；如果强制类型转换失败，如执行 CAST('X' AS INT)，表达式返回空值 NULL。 ``` select '1'+2, cast('1'as int) + 2; ``` ##### DDL 数据定义 ###### 创建数据库 ``` CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; ``` 创建一个数据库，数据库在HDFS上的默认存储路径是 `/user/hive/warehouse/*.db` ![1659405944641](./img/1659405944641.png) 避免已经存在的数据库 ``` hive (default)> create database if not exists db_hive; ``` 指定位置 ``` create database db_hive2 location '/tmp/db_hive2.db'; ``` 这个位置，也是指定在hadoop的hdf目录下 ![1659406375279](./img/1659406375279.png) ###### 显示数据 ```sql show databases; ``` ###### 过滤显示查询的数据库 ``` show databases like 'db_hive*'; ``` ###### 显示数据库信息 ``` desc database db_hive; desc database extended db_hive2; // extended的操作还可以看到对该数据的所有历史操作 ``` ![1659407401150](./img/1659407401150.png) ###### 切换数据库 ``` use db_hive2 ``` ###### 修改数据库 ``` alter database db_hive2 set dbproperties('createtime'='20220802'); ``` 在hive中查看修改结果 ![1659409849722](./img/1659409849722.png) ###### 删除数据库 ```sql drop database db_hive2; -- 删除空的数据库 drop database if exists db_hive2; -- 如果不存在，就不执行删除操作 drop database db_hive2 cascade; -- 即便有数据，也强制删除 ``` ##### 创建表 ###### 建表语句 ```sql CREATE [EXTERNAL] -- hive中的表有内外之分 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] -- 指定表的在hdfs系统里的位置 [TBLPROPERTIES (property_name=property_value, ...)] -- 额外属性 [AS select_statement] --可以用其它表查询出来的sql语句作为插入语句，插入到创建到新表中 ``` ###### 字段解释说明 （1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常； 用户可以用 IF NOT EXISTS 选项来忽略这个异常。 （2）EXTERNAL 关键字可以让用户创建一个外部表，**在建表的同时可以指定一个指向实** **际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外** 部表只删除元数据，不删除数据。 （3）COMMENT：为表和列添加注释。 （4）PARTITIONED BY 创建分区表 （5）CLUSTERED BY 创建分桶表 （6）SORTED BY 不常用，对桶中的一个或多个列另外排序 （7）ROW FORMAT 定义行的格式 ```sql 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, ...)] -- 属于row format格式的定义，有字段的分隔符，集合的分隔符，map的分隔符，每一行数据的分割符 ``` 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需 要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表 的具体的列的数据。 SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。 （8）STORED AS 指定存储文件类型 （9）LOCATION ：指定表在 HDFS 上的存储位置。 （10）AS：后跟查询语句，根据查询结果创建表。 （11）LIKE 允许用户复制现有的表结构，但是不复制数据。 ###### 管理表 默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会（或多或 少地）控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 **当我们删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据** ![1659452640329](./img/1659452640329.png) ```sql create EXTERNAL table test4; ``` ![1659452701712](./img/1659452701712.png) ###### 外部表 因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这 份数据(hdfs)，不过描述表的**元数据信息**会被删除掉。 ###### 如何区别 如果你的资源是其它也要使用，也就是共享的，进行外部表的操作可以保护数据，一般来说，我们是 创建外部表，如果你是用什么关系数据，例如建立两个数据的关联关系，可以考虑用管理表，因为删除也不会对数据有什么影响 ###### 内外部表如何切换 （1）查询表的类型 ``` hive (default)> desc formatted student2; Table Type: MANAGED_TABLE ``` （2）修改内部表 student2 为外部表 ``` alter table student2 set tblproperties('EXTERNAL'='TRUE'); ``` （3）查询表的类型 ``` hive (default)> desc formatted student2; Table Type: EXTERNAL_TABLE ``` （4）修改外部表 student2 为内部表 ``` alter table student2 set tblproperties('EXTERNAL'='FALSE'); ``` （5）查询表的类型 ``` hive (default)> desc formatted student2; Table Type: MANAGED_TABLE ``` **注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！** ###### 建表指定分隔符 当我们创建一张表的时候，并插入一条语句，也就意味着在对应的hdfs中的表目录下也有一个文件记录了数据，我们可以在线预览或者下载下来看看这些内容。 ![1659453798893](./img/1659453798893.png) 可以看到有奇怪的分隔符，所以我们可以在定义表的时候这样写格式 ```sql create table test6(id int,name string) row format delimited fields terminated by ',' ``` ![1659453990655](./img/1659453990655.png) 这样，你就可以自己定义数据，自己读取了 ##### 修改表 ###### 重命名 ``` alter table test1 rename to test8 ``` 新表名在当前库中不能存在 ###### 更新/修改/替换列信息 ```sql ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] alter table test1 change id id2 string; ``` （2）增加和替换列 ```sql ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) -- 直接新增一个类型为string的name2字段 alter table test2 add columns (name2 string) -- 请注意这个replace是从表的维度替换，即会将表中的所有字段都替换成 columns括号内的内容 alter table test2 replace colums(name2 string) ``` **注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)， REPLACE 则是表示替换表中所有字段。** ###### 删除列 很可惜，并没有提供删除列的方式，但是你可以使用替换的方式将原本两个列替换成一个列，也达到了删除的效果 ![1659456649814](./img/1659456649814.png) 这里需要说明的是，你这里的替换，其实也是对元数据进行操作，即便你将表字段进行了替换，他的数据在hdfs也是不改变的，也是可以展示的，如果你的hdfs里的数据多于的表字段，就会少显示，反之亦然。 ###### 删除表 ```sql drop table dept; ``` 如果是管理表(内部表)会将hdfs信息和元数据一起删除，如果是外部表只会删除元数据，hdfs会保留。 ###### 向表中装载数据(Load) ``` hive> load data [local] inpath '数据的 path' [overwrite] into table student [partition (partcol1=val1,…)]; ``` （1）load data:表示加载数据 （2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表 （3）inpath:表示加载数据的路径 （4）overwrite:表示覆盖表中已有数据，否则表示追加 （5）into table:表示加载到哪张表 （6）student:表示具体的表 （7）partition:表示上传到指定分区 ![1659510751181](./img/1659510751181.png) 之前我们说过直接通过put命令将文件上传到hadoop的hdfs中的时候，hive的元数据是无法感知到的，也就是numFiles和numRows这两个属性都是不会变化的。而当你使用hive的insert命令的时候，这两个值会同时变化。 但是对于load命令而言，其实是hive直接将文件传到了hdfs中，所以hive是知道文件的变化的，这种情况，只会变化numFiles，而不会变化numRows。 而对于load的这种情况，当你尝试使用count的命令，去查询行数据的时候，由于numFiles和numRows不相同，hive会发起mr任务，将正确的结果返回，不过对于元数据还是不会修改。 对于local模式进行数据的加载，如果在是在hdfs中进行加载 ``` load data inpath '/student.txt' into table student; ``` 那就只是一个剪切操作，而且速度极快，但是实际对于hadoop而言，他也只是修改了文件的指向元数据而已。本身的数据存放在hadoop固定的目录没有变化。 而load local需要将文件拷贝到hdfs中，再进行剪切。 ###### 插入数据 对于大数据场景，你不太可能一条一条插入数据，所以对于insert into的指令而言 更多的是使用其他表的查询结果，进行数据的插入。 ```sql insert into student select * from student1 ``` 覆盖操作 ```sql insert overwrite table student ``` ###### 多表多分区插入 ```sql from student -- 从 student表里查数据 insert into table student1 -- 插入到student1 select id,name insert into table student2 -- 插入到student2 select id,name -- 请注意，只有一个分号，说明这是一条语句,而且一定插入的是两张不同的表 ``` ###### 创建表的同时插入数据 ```sql create table if not exists student3 as select id, name from student; ``` ###### **创建表时通过** **Location** **指定加载数据路径** ```sql hive (default)> dfs -mkdir /student; hive (default)> dfs -put /opt/module/datas/student.txt /student; ``` 指定位置 ```sql hive (default)> create external table if not exists student5( id int, name string ) row format delimited fields terminated by '\t' location '/student'; ``` ###### 导出数据 将查询结果导出到**本地**8000000021053623 ```sql insert overwrite local directory '/opt/module/hive/data/export/student' select * from student; -- 将 student 的查询结果，导出到本地指定的目录下 ``` 但是有的时候，导出的表数据的分割，会按照原始的格式分割，看起来不太友好，所以你可以在导出的时候，指定格式，这样就和之前创建的表一样。拥有比较合适的格式 ```sql insert overwrite local directory '/opt/module/hive/data/export/student1' -- 指定格式 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student; ``` 导到hdfs路径 ```sql insert overwrite directory '/user/student' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student; -- 会在 /user/student 的hdfs路径上创建 ``` 当然，你可以直接从hadoop导出 ``` dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt; ``` ###### 清空表的数据 注意：Truncate只能删除管理表，不能删除外部表中的数据 ```sql truncate table student; ``` ##### 查询 查询中的比较运算符 ![1659622431456](./img/1659622431456.png) 由于hql语法和mysql 的sql别无他样，所以这里也不会做很多多余的语法介绍，这里会记录一些对于hive的一些语法的补充 ###### JOIN ![1659879121895](./img/1659879121895.png) 对于join，一共有七种，内连接，左外连接，左连接，右外连接，右连接，满连接。 **内连接**可以省略inner，直接写join，他只会匹配两个结果的交集，也就是只会匹配两个表共有的东西。 而**左外，右外连接**则会以相对应的左右表作为关联，他的一切都会显示出来，如果匹配不到就会显示null **左右连接**和相对应的外连接的区别，则是在where后面增加了条件，也就是将**独有的部分**展示出来。 在语句中体现的就是 ```sql where 连接的key is null ``` 也就是匹配不上数据，该表独有的数据展示出来。 **满连接**你可以看成是全部的展示，无论是独有的，还是共有的都会展示，是并集关系。 那么，如果不依靠满连接的`full join`语法，是否还有其他的实现方法，按照图上而言，是否是左边外连接和右边连接，或者左连接和右外连接的语法的结合呢。 对于满连接的右边的图像，是否也是左连接和右连接的union all 语法呢 ###### Union 与 Union All 区别在于Union 在整合数据的时候，会去重，对于使用场景，如果对结果不介意的话，使用Union All，如果需求有明确规定，使用Union，因为去重也是一种性能的消耗。 ###### 笛卡尔集 对于连接语句，以下情况，容易出现笛卡尔集 （1）省略连接条件 （2）连接条件无效 （3）所有表中的所有行互相连接 由于笛卡尔集是无意义的结果集，所以请一定避免。 ###### 排序 **Order By** 我们在语句中用的很多，因为他可以对结果集进行排序，我们可以使用逗号隔开，并且还可以指定他们的排序规则，默认升序(ASC)，当然你也可以指定降序(DESC) 不过，对于大数据而言，上亿的数据，你如果这样排序，效率是很差的。所以是很少使用的。 **Sort By** 学习过Spark或者Hadoop我们知道，对于大量数据的情况话，貌似这些组件都给我们一个一致的解决方案。那就是分区处理，将数据分摊给不同的分区，让他让分区自行处理，最后将结果汇总起来返回，来提高效率。 sortBy的使用你需要在hive中设置你的reducer数量。 ``` hive (default)> set mapreduce.job.reduces=3; ``` 这样设置，将会以为这，之后你使用sortby的命令，他会将数据按照一定的分区算法，均摊给三个分区，进行排序 ``` select * from emp sort by deptno desc; ``` 当然，这个结果可能看起来有点奇怪。 ![1659880499262](./img/1659880499262.png) 你可以将数据输出到文件中 ```sql insert overwrite local directory './sortBy' row format delimited fields terminated by ',' select * from emp sort by deptno desc; ``` ![1659880709936](./img/1659880709936.png) ![1659880749274](./img/1659880749274.png) 可以很明显的看到，这其实在区域内是有序的，只不过汇总的时候，看起来像是无序的 **Distribute By**，分区的存在其实是和sortBy一起使用的。Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为 了进行后续的聚集操作。**distribute by** 子句可以做这件事。**distribute by** 类似 MR 中 partition （自定义分区），进行分区，结合 sort by 使用。 对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。 （1）先按照部门编号分区，再按照员工编号降序排序。 ``` hive (default)> set mapreduce.job.reduces=4; hive (default)> insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distribute by deptno sort by empno desc; ``` ![1659881284549](./img/1659881284549.png) ![1659881329309](./img/1659881329309.png) 可以看到分区的内容，由于我们分区是分区key的hash码与reduce取模，所以你看编号是20模上reducer 4，结果是0，所以在0号分区 注意： ➢ distribute by 的分区规则是根据**分区字段的 hash 码与 reduce 的个数进行模除后**， 余数相同的分到一个区。 ➢ Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。 **Cluster By** 当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。 cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。**但是排序只能是升序 排序，不能指定排序规则为 ASC 或者 DESC。** 但是你可以用distribute by 和sort by 来指定降序或者排序 以下写法等价 ``` hive (default)> select * from emp cluster by deptno; hive (default)> select * from emp distribute by deptno sort by deptno; ``` 注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一 个分区里面去。 ###### 分区表 分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所 有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据 集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率 会提高很多。 例如把每天的数据都会抽成一个目录来做，提高效率。**也就是把数据分开放** 创建一张分区表。 ```sql hive (default)> create table dept_partition( deptno int, dname string, loc string) -- 分区的条件。这里的day和本身表的字段是不一致的。 partitioned by (day string) row format delimited fields terminated by '\t'; ``` 同时我们准备三份数据。 dept_20200401.log ``` 10 ACCOUNTING 1700 20 RESEARCH 1800 ``` dept_20200402.log ``` 30 SALES 1900 40 OPERATIONS 1700 ``` dept_20200403.log ``` 50 TEST 2000 60 DEV 1900 ``` 这三份数据，名字并不固定。然后插入数据，你要指定分区 ```sql hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401'); hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402'); hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403'); ``` 会在我们定义好的hdfs的目录下看到额外的目录 ![1660114809348](./img/1660114809348.png) 然后我们点击进去，可以看到我们的数据 ![1660114846096](./img/1660114846096.png) 这样我们就将我们的数据按照自己规定的格式分别放在不同的目录下了。当我们使用查询的时候查询全部表的时候。 ![1660115095967](./img/1660115095967.png) 可以看到，也可以将数据全部查询查出来，而我们之前定义的分区字段，也成为了表的一个字段。你甚至可以直接使用这个字段来查询。 ![1660115203270](./img/1660115203270.png) 但是你用分片字段day来查询，会更高效。由于你的deptno是写在文件里，如果你不扫描文件，是无法知道内容的，而直接指定分区名字，可以知道找到分区下的文件。所以，即便上面两种结果是一致，效率也是有差别的。 ![1660115496187](./img/1660115496187.png) 元数据也会存储这样的信息。 **添加分区** 可以添加一个或者多个分区，请注意，partition之间用空格隔开 ```sql alter table dept_partition add partition(day='20220810') partition('20220811') ``` **删除分区** 多个，这里使用的是逗号。 ```sql alter table dept_partition drop partition (day='20200404'), partition(day='20200405'); ``` **查看有多少分区** ```sql show partitions dept_par ``` ![1660117582050](./img/1660117582050.png) **查看分区表的结构** ```sql desc formatted dept_partition ``` ![1660117724631](./img/1660117724631.png) ###### 二级分区 之前我们按照一天分区，如果一天的数据还是太大怎么办，可不可以按小时再分区呢 ```sql create table dept_partition2( deptno int, dname string, loc string ) -- 两个分区字段 partitioned by (day string, hour string) row format delimited fields terminated by '\t'; ``` 插入数据，之前是一个字段，现在我们需要用两个字段 ```sql load data local inpath '/数据所在的目录' into table dept_partition2 partition(day='2022-08-10',hour='11') ``` ###### HDFS和元数据 回到之前的话题，表的创建和本身数据的创建先后顺序真的固定吗 答案是**肯定的**，你可以通过hadoop创建和分区相同的目录，然后再创建表，这样是查询不到的。 ``` hadoop fs -mkdir /user/hive/warehouse/一段目录 ``` 上传文件 ``` hadoop fs -put dept1.txt /user/hive/warehouse/一段目录 ``` 和普通的创建表不同，你可以理解分区的数据存储和普通的表是不一样的，他们缺少了**元数据**，分区的更多的信息，是存储在元数据里。 那么这样的方式，我们就真的束手无策了吗，当然不是的。可以通过三种方式，去修复这种后面添加的分区信息，让我们可以查询到。 **第一种方式** 此方式也许会出现，你在hdfs中创建了目录，然后你通过hive去查询这张分区表，查询不到。可以执行这个命令 ```sql msck repair table dept_partition2; ``` 执行完后，他还会根据这张表的分区元数据和现在的hdfs的表空间下的信息，对元数据进行补充。从而达到修复的目的。 **第二种方式** 我们可以直接添加这个分区，也可以达到插入数据的目的 ```sql alter table dept_partition2 add partition(day='201709',hour='14'); ``` **第三种方式** 你可以load数据到分区中，也会添加元数据信息。 ```sql load data loacl inpath '/..' into table dept_partitions partition(day='20200401',hour='11'); ``` **一个问题引发的思考** 我们使用一个分区表，插入数据的使用会指定分区的键。然后hive会通过mr任务会创建对应的hdfs路径。如果我们不指定路径，就这么插入会如何呢。 答案是不确定，有的时候会成功有的时候不会成功。 原因在于，语句是可以通过的，因为本身的加载表数据的语法是正确的，当sql语句被打包成mr任务的时候，其实提交给yarn来分配资源，由于，你并没有指定规则，yarn会随机挑选一个节点来进行数据的加载，有可能会选择到你存放了数据的节点，也可能找不到。 当找到了，也就可以插入了。不过你没有指定分区，所以会给你一个默认的名字。 ![1660141872585](./img/1660141872585.png) ###### 动态分区 之前我们讨论了分区和二级分区，这些都是写死的，如果我的数据是动态的，需要根据某个规则路由到某个位置怎么办呢。关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据 插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过， 使用 Hive 的动态分区，需要进行相应的配置。 动态分区功能默认是true，开始 ``` hive.exec.dynamic.partition=true ``` 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为 静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。） ``` hive.exec.dynamic.partition.mode=nonstrict ``` 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000 ``` hive.exec.max.dynamic.partitions=1000 ``` 在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际 的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就 需要设置成大于 365，如果使用默认值 100，则会报错。 ``` hive.exec.max.dynamic.partitions.pernode=100 ``` 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000，这个参数的意义可能是代表了在路由数据的时候，所产生的文件个数。 ``` hive.exec.max.created.files=100000 ``` 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false ``` hive.error.on.empty.partition=false ``` 我们创建一个动态分区的表，格式还是一样的。 ```sql create table dept_partition3(id int,name string) partitioned by (loc int) row format delimited fields terminated by '\t'; -- 以上和创建普通分区并无区别，但是在插入的时候，有所区别 -- 一定要设置这句话，否则会报错 set hive.exec.dynamic.partition.mode = nonstrict; -- 这个是静态的写法，如果你不写70，就会插入到默认的分区中，也就是之前的，DEFAULT_HIVE_PARTITION 分区 insert into table dept_partition3 partition(loc='70') select deptno,dname from dept; -- 插入，请注意，之前的partition中是写了值的，但是这里是没有的 insert into table dept_partition3 partition(loc) select deptno,dname, -- 动态分区，这个动态值一定要写在语句的最后 loc from dept; -- 如果你含有二级分区，这个顺序也遵守放后面就行了 insert into table dept_partition3 partition(day,hour) select deptno,dname,day,hour from dept; ``` ![1660143182184](./img/1660143182184.png) 会根据你的内容，动态创建。 **Hive3.0动态分区新特性** ```sql -- 3.0 hive的动态分区插入，支持简写，你可以不加partition insert into table dept_partition3 -- 但是分区字段还是不能省，且必须写在最后面。 select deptno,dname,loc from dept; -- 同时之前我们是设置了严格模式为非严格模式，在3.0hive中，你可以直接这样写，而不是一定要非严格模式，他也不会报错。底层其实也是他自动帮你设置了一遍非严格模式。总体来说可以帮你节约一些事件。 ``` ###### 分桶表 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围 划分。 分桶是将数据集分解成更容易管理的若干部分的另一个技术。 **分区针对的是数据的存储路径；分桶针对的是数据文件。** ```sql create table stu_buck(id int, name string) -- 和分区表不同，分区表的路由字段不能与表的字段重名，但是分桶一定要在字段中选择 clustered by(id) into 4 buckets row format delimited fields terminated by '\t'; ``` 查看表结构 ``` hive (default)> desc formatted stu_buck; Num Buckets: 4 ``` 当你插入数据时候 ```sql hive (default)> load data inpath '/student.txt' into table stu_buck; ``` ![1660146102358](./img/1660146102358.png) 一份数据，被拆分成了四个小文件。 ![1660146176183](./img/1660146176183.png) 分桶规则 ``` 根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中 ``` **分桶表操作需要注意的事项** （1）reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个 数设置为大于等于分桶表的桶数 （2）从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题 （3）不要使用本地模式 **insert** **方式将数据导入分桶表** ``` hive(default)>insert into table stu_buck select * from student_insert; ``` ###### 抽样查询 对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。 ```sql hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id); ``` ```sql buck x out of y -- x 一定不能大于 y -- y的含义在于将表的数据分成多少份，x表示从第几份开始进行抽取，上面的例子就是分成4份，从第1份开始抽样 ``` ##### 系统函数 ###### 查看现有的函数 ```sql show functions; ``` ###### 显示自带的函数用法 ``` desc function [function_name]; ``` ###### 详细显示自带的函数用法 ``` desc function extended [function_name]; ``` ![1660292312156](./img/1660292312156.png) 不同的系统自带的函数，是被定义的聚合函数，不同种类的聚合所代表的意义也不同。 ``` UDF：一进一出 UDAF：多进一出 UDTF：一进多出 ``` 一、多指的是输入数据的**行数**。 ###### 常用函数说明 **NVL** NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如 果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。 如果员工comm为NULL，则用-1代替 ```sql select comm,nvl(comm, -1) from emp; comm _c1 NULL -1.0 300.0 300.0 500.0 500.0 NULL -1.0 1400.0 1400.0 NULL -1.0 NULL -1.0 NULL -1.0 NULL -1.0 0.0 0.0 NULL -1.0 NULL -1.0 NULL -1.0 NULL -1.0 ``` 如果员工的 comm 为 NULL，则用领导id 代替 ```sql select comm, nvl(comm,mgr) from emp; comm _c1 NULL 7902.0 300.0 300.0 500.0 500.0 NULL 7839.0 1400.0 1400.0 NULL 7839.0 NULL 7839.0 NULL 7566.0 NULL NULL 0.0 0.0 NULL 7788.0 NULL 7698.0 NULL 7566.0 NULL 7782.0 ``` **CASE WHEN THEN ELSE END** 求出不同部门男女各多少人。 select dept_id, sum(case sex when '男' then 1 else 0 end) male_count, sum(case sex when '女' then 1 else 0 end) female_count from emp_sex group by dept_id; -- if 函数用于只有一个分支的问题 select dept_id, sum(if(sex='男',1,0)) male_count, sum(if(sex='女',1,0)) female_count from emp_sex group by dept_id; ###### 行转列 将一行单独的数据合并到一列中去 相关函数介绍 * CONCAT（string A/col，string B/col...） * 返回输入字符串连接后的结果，支持任意个输入字 符串; * CONCAT_WS（separator,str1,str2,...）: * 它是一个特殊形式的 CONCAT()。第一个参数剩余参 数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将 为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接 的字符串之间; `注意: CONCAT_WS must be "string or array` * COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重 汇总，产生 Array 类型字段。 把星座和血型一样的人归类到一起。希望查询出来的结果和下面一样。 ``` 射手座,A 大海|凤姐 白羊座,A 孙悟空|猪八戒 白羊座,B 宋宋|苍老师 ``` 目前准备的数据 ``` 孙悟空 白羊座 A 大海 射手座 A 宋宋 白羊座 B 猪八戒 白羊座 A 凤姐 射手座 A 苍老师 白羊座 B ``` 创建hive表并导入数据 ```sql create table person_info( name string, constellation string, blood_type string) row format delimited fields terminated by "\t"; load data local inpath "/opt/module/hive/data/person_info.txt" into table person_info; ``` 按照需求查询数据 ```sql select t1.c_b, CONCAT_WS("|",collect_set(t.name)) -- 将结果全部聚合起来，和聚合函数一样的效果 from ( select name,CONCAT_WS(',',constellation,blood_type) c_b from person_info ) t1 GROUP BY t1_c_b ``` ###### 列转行 将一列含有多个信息的数据，拆分出来成为新的一列 你希望展示的数据 ``` 《疑犯追踪》 悬疑 《疑犯追踪》 动作 《疑犯追踪》 科幻 《疑犯追踪》 剧情 《Lie to me》 悬疑 《Lie to me》 警匪 《Lie to me》 动作 《Lie to me》 心理 《Lie to me》 剧情 《战狼 2》 战争 《战狼 2》 动作 《战狼 2》 灾难 ``` 你的数据 ``` 《疑犯追踪》 悬疑,动作,科幻,剧情 《Lie to me》悬疑,警匪,动作,心理,剧情 《战狼 2》 战争,动作,灾难 ``` EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。 LATERAL VIEW 侧写表 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。 ```sql create table movie_info( movie string, category string) row format delimited fields terminated by "\t"; load data local inpath "/opt/module/data/movie.txt" into table movie_info; ``` 写法 ```sql SELECT movie, category_name FROM movie_info -- 展示字段，在这里指定 这里单独写侧写表，然后category_name产生关联 lateral VIEW explode(split(category,",")) movie_info_tmp AS category_name; --如果你explode的字段需要和原表进行关联，也就是还需要原表的字段，可以用上面的方式 --但是如果你只需要炸裂的后的展示，那么你这样写也可以。 select explode(split(category,",")) from move_info ``` ![1660571269404](./img/1660571269404.png) ###### 窗口函数 OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。开窗函数，用法和Groupby是差不多的，是对某个一个数据集进行分组。 以下这些函数是写在over里面的，对分组的内容进行限定。 * CURRENT ROW：当前行 * n PRECEDING：往前 n 行数据 * n FOLLOWING：往后 n 行数据 * UNBOUNDED：起点 * UNBOUNDED PRECEDING 表示从前面的起点 * UNBOUNDED FOLLOWING 表示到后面的终点 * LAG(col,n,default_val)：往前第 n 行数据 * LEAD(col,n, default_val)：往后第 n 行数据 * NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对 于每一行，NTILE 返回此行所属的组的编号。**注意：n 必须为 int 类型。** 准备的数据，字段分别是 name，orderdate，cost ``` jack,2017-01-01,10 tony,2017-01-02,15 jack,2017-02-03,23 tony,2017-01-04,29 jack,2017-01-05,46 jack,2017-04-06,42 tony,2017-01-07,50 jack,2017-01-08,55 mart,2017-04-08,62 mart,2017-04-09,68 neil,2017-05-10,12 mart,2017-04-11,75 neil,2017-06-12,80 mart,2017-04-13,94 ``` 对应的表 ```sql create table business( name string, orderdate string, cost int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; load data local inpath "/opt/module/data/business.txt" into table business; ``` 查询在 2017 年 4 月份购买过的顾客及总人数 为了展示over函数的意义。我们可以把这个问题拆分开来，一个是展示所有的用户名 ```sql select DISTINCT(name) from business where substring(orderdate,0,7) = '2017-04' mart jack -- 然后展示总人数 select count(DISTINCT(name)) from business where substring(orderdate,0,7)='2017-04' 2 -- 另一种写法，展示总人数 select count(*) from ( select name from business where substring(orderdate,0,7) = '2017-04' group by name )t1 -- 如果我们这样写,代表的是每个人在四月份产生多少次消费记录，和我们想要的不符合 select name,count(*) from business where substring(orderdate,0,7)='2017-04' group by name mart 4 jack 1 -- 要求是，查能插四月份的总人数，且带人名的内容 -- 最终写法 select name,count(*) over() from business where substring(orderdate,0,7)='2017-04' group by name name count_window_0 mart 2 jack 2 -- 解释说明，over() 是在group by 函数后生效。如果不加over，我们查询的结果是 4 1，目前 over() 是加在count后面，意义在于，在count(*) 函数后面做了开窗操作，你可以理解成，是独立于原本的查询的另一个查询窗口，只是放到这里来展示的，由于你的over的括号中并没有写什么，他就是对整个表的开窗，也就是名字个数的总计。 -- 如果把开窗函数不和groupby组合起来看 select name,count(*) over() from business where substring(orderdate,0,7)='2017-04' jack 2 tony 2 jack 2 tony 2 jack 2 jack 2 tony 2 jack 2 mart 2 mart 2 neil 2 mart 2 neil 2 mart 2 ``` groupby与over的区别，groupby相当于数据的聚合，他将相同类别的数据，放到同一个组中，以上面的含有gourpby的案例，数据的数量是缩小了，因为被放到同一个组了。但是over开窗操作，他会对每一条数据进行一个组，也就会说，即便你jack人名有5条，over也会创建5个分组，将他们放进去，有多少条数据就会开几次窗口。由于你的over中并没有写什么条件，对于他而言，是将没有任何过滤的结果集放进去该窗口进行统计。所以总是2个总数。 **查询顾客的购买明细及月购买总额** ```sql -- 所有人的所有日期的总共消费总数 select name,orderdate,cost, sum(cost) over() from business ; ``` ![1660637792712](./img/1660637792712.png) ```sql select name,orderdate,cost, sum(cost) over(partition by name) from business ``` ![1660637973192](./img/1660637973192.png) ```sql select name,orderdate,cost, sum(cost) over(partition by name,month(orderdate)) from business -- 每个用户在每个月的消费记录，和总月份消费总额 ``` ![1660638879754](./img/1660638879754.png) ```sql select name,orderdate,cost,sum(cost) over(parition by month(orderdate)) from business -- 每个月的用户消费情况，和每个月总用户消耗的总数 ``` ![1660639630326](./img/1660639630326.png) parition by 可以限定开窗的大小。 **将每个顾客的cost按照日期进行累加** ![1660640551639](./img/1660640551639.png) ```sql -- 有多种实现 select name,orderdate,cost, sum(cost) over() as sample1,--所有行相加 sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加 sum(cost) over(partition by name order by orderdate) -- 如果orderby不写开窗里的界定，即标识这默认是从起始行到当前行，下面截图 as sample3,--按 name分组，组内数据累加 sum(cost) over(partition by name order by orderdate -- rows 一定要跟在orderby的后面，后面标识row在起始行到当前行，这个范围做一个开窗 rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 from business; ``` 未指定，后面可以默认范围。 ![1660699505499](./img/1660699505499.png) 更复杂的根据名字累加操作，每三个数据进行一次累加 ![1660699744201](./img/1660699744201.png) 这样的开窗操作如何完成。 ```sql select name,orderdate,cost,sum(cost) over(parition by name order by orderdate row between 1 PROCEDING and 1 FOLLOWING -- 每次的计算，只针对自己这行 与 前一行和后一行 进行合计 ) from business; -- 结合上面的语法，还有两个之前见过的比较特殊的语法 UNBOUNDED：起点 -- 没有边界的向前行就是起点 * UNBOUNDED PRECEDING 表示从前面的起点 -- 没有边界的向后就是重点 * UNBOUNDED FOLLOWING 表示到后面的终点 ``` **注意** 对于开窗中使用orderby对所开窗结果进行累加的操作，如果你所排序的含有相同的数值。 ```sql select id,sum(id) over(order by id) from business; -- 结果 -- 预想的结果 1 1 2 3 3 6 3 9 4 13 5 18 -- 实际的结果 1 1 2 3 3 9 -- 由于排序结果是一致的 3 9 4 13 5 18 ``` 而对于这两条相同的排序而言，他们所开的窗是相同的，所以结果也是相同的。 ![1660700987762](./img/1660700987762.png) 所以在之后的开发中，请注意，orderby尽量去找不相同的数据，不然会出现累计的结果一致，或者你可以orderby，两个以上的值，来保证开的窗结果唯一。 **查询每个客户上次的购买时间** ```sql * LAG(col,n,default_val)：往前第 n 行数据 * LEAD(col,n, default_val)：往后第 n 行数据 select name,orderdate lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) from busniess select name,orderdate -- 如果拿不到，例如是第一行，他的前一条就是没数据的，无法取到，可以直接将当前行的orderdate放进去 lag(orderdate,1,orderdate) over(partition by name order by orderdate) from busniess ``` 这里对这个做出解释，找到上一次其实就是对日期进行排序且每次都取上一条数据，排除第一条的特殊情况，我们展示的这个字段总是拿的上一个，所以我们使用lag函数，第一个参数是字段名，第二个是取多少，第三个是默认值，非必填。 ![1660701737143](./img/1660701737143.png) ![1660702157687](./img/1660702157687.png) 这样的场景类似于，如果你有ABC三个网页，你想要A跳转到B的次数，或者B跳到C的次数的话，你需要如何做 ``` jack A jack B jack C 你想看跳转其实这个不好分组，所以如果我们可以将值转化成 jack A-B Jack B-C jack C-A 这样的话，你可以按照A-B分组，统计数量，而你需要上一条的结果作为拼接。 ``` **查询前20%时间的订单信息** 意味着订单时间排序，每次对当前行取前20%数量的订单 ```sql NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对 。 需要计算前20%，我们可以将时间分别放到5个组中，然后取第一个组就行了。 select name,orderdate,cost, -- ntile 必须和over一起使用，无法单独使用 ntile(5) over(order by orderdate) from busniess; ``` ![1660704094480](./img/1660704094480.png) ```sql select name,orderdate,cost from ( select name,orderdate,cost, -- ntile 必须和over一起使用，无法单独使用 ntile(5) over(order by orderdate) groupId from busniess; ) t1 -- 只取第一个组的 where groupId = 1 ``` ###### Rank * RANK() 排序相同时会重复，总数不会变 * ```sql 1 1 3 -- 虽然展示排序，但是已经用了2个排序名额，所以从3开始。 ``` * DENSE_RANK() 排序相同时会重复，总数会减少 * ```sql 1 1 2 -- 虽然是相同排序，但是接下来的排序还是会按照之前的规律看起来，排序的总数是减少的 ``` * ROW_NUMBER() 会根据顺序计算 * ```sql 1 2 3 -- 行号的含义，无论如何都累加，和相同排序无关 ``` ```sql select * ,rank() over(order by score) from socres; ``` ![1660718046810](./img/1660718046810.png) 总数是不会变的，所以6完了直接是8 ```sql select *,dense_rank() over(order by score) from scores; ``` ![1660722608359](./img/1660722608359.png) 排序还是有保持顺序，但是总数减少了。 ```sql select *,row_number() over(order by score) from scores ``` 只是代表行号，不会重复，总数也不会变化 ![1660722917145](./img/1660722917145.png) ```sql -- 只取各科前三名 select name,subject,score from ( -- 各科的排行 select *, rank() over(partition by subject order by score desc) rk from score) t1 where rk<=3 ``` ![1660724812169](./img/1660724812169.png) ###### 常用的函数 ```sql -- 常用时间函数 -- 返回一个时间戳 select unix_timestamp() -- 指定时间返回一个时间戳 select unix_timestamp('2020-10-28','yyyy-MM-dd') -- from_unixtime: 将时间戳转为日期格式 select from_unixtime(1603843200) select from_unixtime(1603843200,'yyyy-MM-dd') -- current_timestamp select current_timestamp -- 输出:2020-10-28 00:00:00.000 精确到毫秒 -- current_date select current_date -- 2020-10-28 -- 提取日期 select to_date('2020-10-28 12:12:12') -- 2020-10-28 -- year：获取年 select year('2020-10-28 12:12:12') -- month：获取月 select month('2020-10-28 12:12:12') -- day：获取日 select day('2020-10-28 12:12:12') -- hour：获取时 select hour('2020-10-28 12:12:12') -- minute：获取分 select minute('2020-10-28 12:12:12') -- second：获取秒 select second('2020-10-28 12:12:12') -- dayofmonth: 当前时间是一个月中的第几天 select dayofmonth('2020-10-28 12:12:12') -- months_between:两个日期间的月份 前面减后面 select months_between('2020-04-01','2020-10-28') -- add_months:日期加减月 select add_months('2020-10-28',3) select add_months('2020-10-28',-3) -- datediff 计算两个天数的相差 select datediff('2020-10-12','2020-10-28') -- date_add:日期加天数 前面减后面 select date_add('2020-10-28',3) -- date_sub:日期减天数 select date_sub('2020-10-28',-3) -- last-day:日期当月的最后一天 select last_day('2020-10-28') -- date_format:格式化日期 日期，时间戳，字符串 select date_format('2020-10-28 12:12:12','yyyy-MM') -- 常用数学函数 -- 四舍五入，只取整数位 select round(3.14) 3 select round(3.54) 4 -- 向下取整 select floor(3.14) 3 select floor(3.54) 3 -- 向上取整 select ceil(3.14) 4 select ceil(3.54) 4 -- 常用的函数 --大写 select upper() --小写 select lower() --长度 select length() --前后去空格 select trim() -- 向左补齐，到指定长度 select lpad('agagag',7,'x') xagagag -- 向右补齐，到指定长度 select rpad('agagag',7,'x') agagagx -- 需要说明的是，如果第二个参数，小于本身指定的长度，那么起到截取字符串到指定长度的效果 -- 如果你写的是2，那么agagag这个无论是lpad还是rpad都是aga，因为已经到指定的长度了，他不会再有其他补充操作。 -- regexp_replace 正则替换 select regexp_replace('2020/10/25','/','-') 2020-10-25 ``` ![1660835442298](./img/1660835442298.png) ```sql -- 以上操作，假设一张表存在数组字段和map字段和struct字段 -- 集合操作 -- size 集合中元素的个数 select size(firends) from test3 -- map_keys:返回map中的key select map_keys(children) from test3 -- map_values:返回map中的value select map_keys(children) from test3 -- array_contains:判断array中是否包含某个元素 select array_contains(firends,"xixi") from test3 -- sort_array:将array中的元素排序 select sort_array(friends) from test3 ``` ###### 自定义UDF函数 看源码 当你编写好自己的UDF函数的时候，进行打包 ![1660872625038](./img/1660872625038.png) 打包完成后，拖拽到linux的文件目录下。这边我们统一放到hive的lib目录下。 ![1660874726541](./img/1660874726541.png) 放到hive的lib下有一个好处，你每次重启的时候，hive都会扫描hive下面的所有的jar包，包括你自己的新加的。 但是你需要重启hive客户端才可以生效，如果你不希望重新，也想直接使用，可以通过以下命令重新添加项目 ``` add jar /opt/module/xxxx.jar ``` 创建函数和现有的jar产生关联 ```sql -- 也可以不写temporyary临时的 create temporary function my_len as "com.pop.hive.MyUDF" ``` ![1660875390565](./img/1660875390565.png) ![1660875607054](./img/1660875607054.png) ![1660875846642](./img/1660875846642.png) ###### 自定义UDTF函数 看源码 直接创建函数来使用 ```sql create temporay function my_split as "com.pop.hive.MyUDTF"; ``` ![1660878917659](./img/1660878917659.png)