# How to insert data into hive table with partition

**URL:** <https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681>\
**Category:** KNIME Analytics Platform\
**Created:** [December 15, 2022, 9:32am UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681 "2022-12-15T09:32:28Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![hhkim](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/dfb087/32.png) [@hhkim](https://forum.knime.com/u/hhkim)\
**Post date:** [December 15, 2022, 9:32am UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/1 "2022-12-15T09:32:28Z")

</div>

Hello.

I was wondering if I can insert data into hive table by using this sql query:

INSERT OVERWRITE TABLE testdb.test\_table PARTITION (base\_date = ‘202212’)

I have used “DB Connection Table Writer” node but it can’t be used with PARTIOTION option.

I think there is no node that can insert connected data into table with PARTITION option.

I’d would appreciate it if you could tell me how I can use PARTITION option.

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [December 15, 2022, 3:57pm UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/2 "2022-12-15T15:57:22Z")

</div>

@hhkim You can use the „DB Table creator“ with additional settings for partitions and then load data into the newly created table. The DB Loader would need a temporary folder in the HDFS file system.

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/9/c/9c7ed1936a24443d42df991185d19dd053488370.jpeg)

You can try and adapt this example here. Please note the partition would be the last column and is being omitted i the create on purpose since it will be set in the additional options.

> **[s\_601 - Sparkling predictions and encoded labels - "the poor man's ML Ops" (on...](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_bigdata_h2o_automl_spark_46/s_601_spark_label_encoder~BwPL1ZF0AHxei8Q6)**
>
> Sparkling predictions and encoded labels - "the poor man's ML Ops" (on a Big Data System) Use Big Data Technologies like Spark to ge a robust and scalable data…

Another option would be to load the data into a temporary table and then use an SQL Executor command to insert the data.

> **[School of Hive - with KNIME's local Big Data environment (SQL for Big Data) –...](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_hive_school_of~1q-JwD0cwmuEkWpy)**
>
> School of Hive - with KNIME's local Big Data environment (SQL for Big Data) Demonstrates a collection of Hive functions using KNIME's local Big Data environmen…

Here is a complete „self-healing“ reporting system with Hive that could demonstrate additional functions:

> **[Hive/Big Data - a simple "self-healing" (automated) ETL or analytics system on...](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_bigdata_hive_self_healing_jobs~ci0WL-BCJ09fW7wS)**
>
> Hive/Big Data - a simple "self-healing" (automated) ETL or analytics system on a big data cluster The scenario: you have a table on a big data system with dail…

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [December 18, 2022, 7:49pm UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/3 "2022-12-18T19:49:31Z")

</div>

@hhkim I created an example workflow that would demonstrate the use of Hive tables and partitions with the KNIME DB nodes.

If you want to do an OVERWRITE of a specific partition the easiest way to do so would be to first explicitly drop the relevant partition (there is a node doing that in the example).

> **[Create a Big Data Hive/Parquet table with a partition based on an existing...](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_bigdata_hive_partitions/m_001_hive_partitions~cJEMxIZg9QIZsMfi)**
>
> Create a Big Data Hive/Parquet table with a partition based on an existing KNIME table and add more partitions later You can create a Hive table with Parquet f…

please download the [whole workflow group](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_bigdata_hive_partitions~kotdKu7AOmLrlEbG/) to get a complete example:

---

<div class="post-metadata">

**Author:** ![hhkim](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/dfb087/32.png) [@hhkim](https://forum.knime.com/u/hhkim)\
**Post date:** [December 19, 2022, 5:25am UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/4 "2022-12-19T05:25:35Z")

</div>

Thank you for your reply.

I think DB Table Creator or DB Reader node can use my local PC resource.

So I just want insert selected DB data into DB table directly by using “INSERT OVERWRITE with PARTITION” query.

“INSERT OVERWRITE with PARTITION” query can use when the table that I want to select already exists.  
But when the table that I want to select comes from DB node, I don’t know how to do.

I hope my explanation is enough so you can understand what I mean.

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [December 19, 2022, 7:19pm UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/5 "2022-12-19T19:19:57Z")

</div>

@hhkim there ware two ways to add a partition to an existing one:

You can use the [DB Connection Table Writer](https://hub.knime.com/knime/extensions/org.knime.features.database/latest/org.knime.database.node.connector.tablewriter.DBConnectionTableWriterNodeFactory/) and just make sure your partitions are in the same order (at the end) of the file - like in this version the column “education”. Here it would append the data (all educations=Masters) if you would execute it more than once:

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/e/3/e35ac6c25891939292c4bf0ae13c19af5836195b.png)

Or you could use the syntax you already have mentioned - “INSERT OVERWRITE” where you mention the partition column. Again: the partitions have to be the last columns (and have to have the same order as in the target file).

```auto
INSERT OVERWRITE TABLE `default`.`data_train`
PARTITION (`education`)
SELECT *
FROM `default`.`data_education_masters`

```

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/f/a/fa030003407fc93a4d45a1bdf5f27091e3991678.png)

You would add the partition in the form of an additional folder or parquet files within the folder (on the HDFS):

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/9/5/9556088012b3d06ae19fac7ea925174341ff6495.png)

The settings for the target table “data\_train” have been set in the additional options of the [DB Table Creator node](https://hub.knime.com/knime/extensions/org.knime.features.database/latest/org.knime.database.node.utility.tablecreator.DBTableCreateNodeFactory2/)

```auto
STORED AS PARQUET
PARTITIONED BY (education STRING)
TBLPROPERTIES ( 'parquet.compression'='snappy'
  , 'external.table.purge'='true'
  , 'transactional'='false'
  , 'discover.partitions' = 'false'
  )

```

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/d/d/dd870af40e0eb4309b31f0c1e02041b04d642594.png)

> **[Create a Big Data Hive/Parquet table with a partition based on an existing...](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_bigdata_hive_partitions/m_001_hive_partitions~cJEMxIZg9QIZsMfi)**
>
> Create a Big Data Hive/Parquet table with a partition based on an existing KNIME table and add more partitions later You can create a Hive table with Parquet f…

---

<div class="post-metadata">

**Author:** ![hhkim](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/dfb087/32.png) [@hhkim](https://forum.knime.com/u/hhkim)\
**Post date:** [December 26, 2022, 6:51am UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/6 "2022-12-26T06:51:28Z")

</div>

Thank you for your answer.

Summarizing your answer, I understood that I can insert the data through the “DB SQL Executor” node when the table I want select exists, and I can insert the data through the “DB Connection Table Writer” node when the table I select does not exist.

It was helpful to me 🙂

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [January 2, 2023, 6:52am UTC](https://forum.knime.com/t/how-to-insert-data-into-hive-table-with-partition/55681/7 "2023-01-02T06:52:11Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
