How to INSERT a single scan Table multiple times in Hive

Editor to share with you how many times in Hive INSERT single scan table, I hope you will learn something after reading this article, let's discuss it together!

Multiple INSERT single scan table

By default, Hive performs multiple table scans. Therefore, if you want to perform multiple operations in a hive table, it is recommended that you use a scan and use that scan to perform multiple operations.

For example, the data from one table is queried many times and loaded into another table. As in the following example, table my_table is a partition table with the partition field dt. If you need to query two specific partition date data in the table, and load the record into two different tables.

INSERT INTO temp_table_20201115 SELECT * FROM my_table WHERE dt = '2020-11-15'

INSERT INTO temp_table_20201116 SELECT * FROM my_table WHERE dt = '2020-11-16'

In the above query, Hive will scan the table twice. To avoid this, we can use the following ways:

FROM my_table

INSERT INTO temp_table_20201115 SELECT * WHERE dt = '2020-11-15'

INSERT INTO temp_table_20201116 SELECT * WHERE dt = '2020-11-16'

This ensures that only one scan is performed on the my_ table table, which greatly reduces the time and resources required for execution.

After reading this article, I believe you have a certain understanding of "how to scan multiple INSERT tables in Hive". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!