问题描述
从 Hive 表插入到 Hive 表时，加载的记录比实际记录多.任何人都可以帮助解决 Hive 的这种奇怪行为吗?
While inserting from Hive table to HIve table, It is loading more records that actual records. Can anyone help in this weird behaviour of Hive ?
我的查询看起来像这样:
My query would be looking like this:
insert overwrite table_a
select col1,col2,col3,... from table_b;
我的 table_b 包含 6405465 条记录.
My table_b consists of 6405465 records.
从 table_b 插入到 table_a 后，我发现 table_a 中的总记录数为 6406565.
After inserting from table_b to table_a, i found total records in table_a are 6406565.
有人可以帮忙吗?
推荐答案
If hive.compute.query.using.stats=true; 那么优化器正在使用统计信息进行查询计算而不是查询表数据.这要快得多，因为 Metastore 是一个像 MySQL 一样的快速数据库，并且不需要 map-reduce.但是，如果表是不使用 INSERT OVERWRITE 加载的，或者负责统计自动收集的配置参数 hive.stats.autogather 被设置为 false，那么统计可能不是新鲜的(过时的).加载文件或使用第三方工具后，统计数据也不会新鲜.这是因为从未分析过文件，Metastore 中的统计数据并不新鲜，如果您放入了新文件，则没有人知道数据是如何更改的.同样在 sqoop 加载等之后.因此，在加载后使用ANALYZE TABLE ... COMPUTE STATISTICS"收集表或分区的统计信息是一个好习惯.
If hive.compute.query.using.stats=true; then optimizer is using statistics for query calculation instead of querying table data. This is much faster because metastore is a fast database like MySQL and does not require map-reduce. But statistics can be not fresh (stale) if the table was loaded not using INSERT OVERWRITE or configuration parameter hive.stats.autogather responsible for statistics auto gathering was set to false. Also statistics will be not fresh after loading files or after using third-party tools. It's because files was never analyzed, statistics in metastore is not fresh, if you have put new files, nobody knows about how the data was changed. Also after sqoop loading, etc. So, it's a good practice to gather statistics for table or partition after loading using 'ANALYZE TABLE ... COMPUTE STATISTICS'.
如果无法自动收集统计信息(适用于 INSERT OVERWRITE)或通过运行 ANALYZE 语句，那么最好关闭 hive.compute.query.using.stats范围.Hive 将查询数据而不是使用统计信息.
In case it's impossible to gather statistics automatically (works for INSERT OVERWRITE) or by running ANALYZE statement then better to switch off hive.compute.query.using.stats parameter. Hive will query data instead of using statistics.
参考:https://cwiki.apache.org/confluence/display/Hive/StatsDev#StatsDev-StatisticsinHive
这篇关于在 Hive 中加载比实际更多的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！