How Can I use spring Jdbc template or others batch insert data to hive table with a partition?

Question

As the title, I want to batch insert data to hive table by spring NamedParameterJbbcTemplate, it can work as follows:

SqlParameterSource[] batchParameterSources = SqlParameterSourceUtils.createBatch(batchValues);
template.batchUpdate("insert into table(a, b, c) values(:a, :b, :c)", batchParameterSources);

But, above sql can't assign the partition, use sql

"insert overwrite table partition(date = '2020-04-02') values (:a, :b, :c)"

spring will throw the SQLFeatureNotSupportException: Method not supported.

So how can I use spring jdbc template to batch insert data to a hive table with the special partition?

Looking forward your answer, Thanks very much!

Answer 1

I research the Spring tutorial and not find about batch insert to hive table with a special partition.

So I use the native hive driver.

You can code as follow:

try {
    Class.forName("org.apache.hive.jdbc.HiveDriver");
    Connection connection = DriverManager.getConnection("hiveserver", "username", "password");
    Statement statement = connection.createStatement();
    boolean result = statement.execute("insert overwrite table table_name partition(dt = 'date') values (valueA), (valueB), (valueC)");
    if (!result) {
        log.info("write data successfully");
    }
}

Note:

1. If you store some data, the method of statement.execute() will return false. Because storage will no results to return.
2. If your datas are too many, it's better to store batch, like

insert overwrite table_name partition(dt='date') values (valueA), (valueB)...
insert into table_name partition(dt='date') values (valueC), (valueD)...