Hive的insert语句能够从查询语句中获取数据,并同时将数据Load到目标表中。现在假定有一个已有数据的表staged_employees(雇员信息全量表),所属国家cnty和所属州st是该表的两个属性,我们做个试验将该表中的数据查询出来插入到另一个表employees中。
如果把OVERWRITE关键字删掉,或者替换成INTO,则hive会追加而不是替代原分区或原表中的数据,这个特性在Hive v0.8.0之后才支持。
当数据已经存在于hdfs上但不是我们想要的格式的时候,当进行的计算需要分好多步骤有必要存储中间数据的时候,或者原数据没有分区、有很多无效列需要过滤的时候,可以使用insert..select句型来完成这一转换过程。
由于一个国家有很多个省份,如果想根据(国家country,地区partition)两个维度对数据进行分区的话,这条SQL语句的执行个数应该等于地区的数目,比如中国有23个省就要对该SQL语句执行23次。因此hive对这个SQL语句进行了改造,只需要扫描一次原表就可以生成不同的输出(多路输出)。比如下面的SQL语句扫描了一次原始数据表,但是同时生成了3个省份的结果数据:
通过缩进可以很清楚的看到,我们扫描了一次staged_employees表但是执行了3次不同的insert语句,这条大SQL语句是这么执行的:先通过from staged_employees表获取一条记录,然后执行每一个select子句,如果select子句验证通过则执行相应的insert语句。注意这里的三条select子句是完全独立执行的,并不是if .. then .. else的关系,这就意味着这3条select子句在某种情况下可能同时通过where检测。
通过这种结构,原始表的数据能被拆分到目标表的不同partition中去。
如果原表的一条记录满足于其中一个给定的select .. where .. 子句,则该记录将被写到目标表的固定分区中。其实更进一步,每条Insert语句能将数据写到不同的数据表中,不管这个表是否分区都一样。
于是,就像一个过滤器一样,原表的一些数据被写到了很多输出地址,而剩下的数据会被丢弃。
当然,你也可以混用Insert overwrite和insert into两种不同的方法写出数据。
向动态分区插入数据
但是问题还是没有解决,中国有23个省,那么我们就需要写23个insert into .. select ..where子句,这非常不现实。于是hive的一种叫做动态分区的特性就出现了,它能够根据select出来的参数自动推断将数据插入到那个分区中去。本文上面的两种SQL语句设定分区的方式都叫做静态分区插入。
将上一个SQL语句进行改动,会得到以下简洁的新SQL语句:
hive先获取select的最后两个位置的se.cnty和se.st参数值,然后将这两个值填写到Insert语句partition中的两个country和state变量中,即动态分区是通过位置来对应分区值的。原始表select出来的值和输出partition的值的关系仅仅是通过位置来确定的,和名字并没有关系,比如这里se.cnty和county的名称完全没有关系。
上面的这条SQL语句是对两个分区同时进行了动态设定,如果staged_employees表中有100个国家,每个国家有100个地区,那么该SQL语句自动对每个国家和地区建立相应的partition并插入数据,如果用手写的话不现实。
只要位置正确,你可以混用动态分区和静态分区值设定,比如下面这个例子,你可以静态指定一个country值,但是state值采用动态的方法设定:
注意:静态分区值必须在动态分区值的前面!
使用hive动态分区的参数设定
动态分区功能默认是关闭的,而当它是打开状态时,默认会工作在“strict”模式下,这种模式下要求至少指定一个静态分区的值。这样做是为了防止设计了大量partition的糟糕情况,举个例子你使用时间戳来进行分区,竟然每一秒钟都产生一个分区!还有其他的一些属性设定用来限制类似的情况出现,如下表所示:
名称 | 默认值 | 描述 |
hive.exec.dynamic.partition | false | 设置为true用于打开动态分区功能 |
hive.exec.dynamic.partition.mode | strict | 设置为nonstrict能够让所有的分区都动态被设定,否则的话至少需要指定一个分区值 |
hive.exec.max.dynamic.partitions.pernode | 100 | 能被每个mapper或者reducer创建的最大动态分区的数目,如果一个mappre或者reducer试图创建多余这个值的动态分区数目,会引发错误 |
hive.exec.max.dynamic.partitions | +1000 | 被一条带有动态分区的SQL语句所能创建的动态分区总量,如果超出限制会报出错误 |
hive.exec.max.created.files | 100000 | 全局能被创建文件数目的最大值,专门有一个hadoop计数器来跟踪该值,如果超出会报 |
举个例子,使用全动态分区的SQL语句序列如下所示,需要先设定一些必要的参数才可以:
总结
使用from .. insert.. select ..where结构能够从一个数据表中抽取数据,将结果插入到不同的表和分区中,而使用动态分区能够让hive根据select最末几个位置的值自动设定目标分区的值,使用动态分区需要设定一些hive运行参数。
相关推荐
其中一种常见的问题是,在使用Spark或MR引擎对Hive表进行数据插入后,通过Hive查询这些数据时,结果却显示为0条记录。具体表现为用户反馈在使用Tez引擎查询时可以得到正确结果,但在使用Spark-SQL查询同一张表时却...
此外,Hive旨在通过数据仓库技术从大量数据中快速获取有价值的信息。 3. **Hive的特点**: - **数据仓库模型**:Hive提供了合理且直观的数据组织方式,简化了复杂的数据运算。 - **非实时查询**:Hive不适合需要...
Hive提供了`LOAD DATA`命令来加载数据到表中。`LOAD DATA LOCAL INPATH`可以从本地文件系统加载数据,`LOAD DATA INPATH`则从HDFS加载。 8. **插入数据**: 使用`INSERT INTO`或`INSERT OVERWRITE`来向表中添加...
例如,可以使用`SELECT`语句从Hive表中提取数据,`JOIN`操作用于合并多个表的数据,`GROUP BY`和`HAVING`用于数据分组和过滤。 3. **元数据**:Hive MetaStore存储了关于Hive表的所有元数据,如表名、列名、分区...
当需要从多个表中提取数据时,合理设计JOIN操作可以显著提升查询效率。例如,通过将需要关联的表写为子查询,可以确保每次JOIN操作只涉及一张表,从而减少数据扫描量。同时,如果可能,应确保主表的JOIN键相同,这有...
- **元数据存储**:Hive 的元数据(如表结构、分区信息等)通常存储在一个关系型数据库中,如 MySQL 或 PostgreSQL,用于管理表的结构和位置信息。 - **计算模型**:Hive 将用户的查询语句转化为 MapReduce 作业执行...
静态分区在建表时指定分区值,动态分区则在插入数据时根据查询动态生成分区。 7. **Hive 元数据**: - Hive 的元数据(如表结构、分区信息等)存储在 metastore,通常是一个关系数据库中,如 MySQL 或 PostgreSQL...
随后,这些文件会被传递给MapReduce任务的各个节点,从而减少了从关系型数据库中获取元数据的需求。 **2.2 自定义MapReduce** Hive允许用户编写自定义的MapReduce程序来处理特定的数据处理需求。这种能力扩展了...
Hive主要应用于大数据处理领域,特别是那些基于Hadoop的数据处理任务,其设计目标是为大规模数据集提供便捷的数据提取、转化和加载(ETL)功能,以及数据分析。 ### Hive核心概念 1. **元数据(Metadata)**:Hive...
3. 将对象信息插入到相应的元数据表中。 4. 更新`SEQUENCE_TABLE`,增加ID值,以备下次使用。 **Hive与普通关系数据库的异同** 尽管Hive基于Hadoop和分布式文件系统,它的元数据组织方式与传统RDBMS类似。像...
3. **元数据获取**:MetaStore 是 Hive 的元数据存储库,包含关于表结构、分区、列类型等信息。 4. **编译与优化**:Compiler 根据元数据将 HiveQL 转换为抽象语法树(AST),进一步转化为逻辑查询计划。逻辑计划...
Hive是基于Hadoop的数据仓库工具,它允许用户使用类SQL的语言HQL(Hive Query Language)对大数据集进行查询、分析和管理。Hive设计的主要目标是为结构化的数据文件提供一个简化的数据查询方法,同时为数据分析人员...
- **解析函数**:用于从复杂数据类型中提取数据。 - **聚合函数**:如COUNT、SUM等。 - **开窗函数**:如LAG、LEAD等。 #### 三、Hive内置函数汇总 - **数学函数**:如ABS、RAND等。 - **集合函数**:如UNION、...
静态分区是在创建表时预先定义分区值,而动态分区则在插入数据时根据INSERT语句中的表达式动态确定分区。静态分区适合于分区值已知且不经常变化的情况,而动态分区适用于分区值在插入时不确定或频繁变化的场景。 4...
- **分区**:用于水平分割表中的数据,每个分区对应一个目录,通常基于日期或地区等字段进行分区,以加速查询速度。 - **桶**:用于垂直分割数据,常用于随机采样,减少join操作的数据量。 ### 安装配置 1. **...
它提供了类似SQL的查询语言——Hive SQL,使得用户能够方便地对Hadoop中的大规模数据进行提取、转换和加载(ETL)。Hive的特点包括: - **可伸缩性**:可以在Hadoop集群上动态增加节点以扩展处理能力。 - **可扩展...
5. **操作Hive**:Hive提供了类似SQL的查询语言——HQL,可以创建表、插入数据、查询数据、分区表、桶表等。例如,创建一个表: ```sql CREATE TABLE IF NOT EXISTS employees ( id INT, name STRING, age INT,...