Clickhouse的Map类型数据及JDBC插入

Clickhouse的Map类型数据及JDBC插入

1.Map类型数据结构简介

需要在21.3+版本才支持

https://repo.yandex.ru/clickhouse/rpm/testing/x86_64/ 下载新版本的CK安装包

https://github.com/ClickHouse/ClickHouse/pull/15806 中描述了添加Map类型的issue

而且该功能为实验性功能，需要设置参数开启该功能

SET allow_experimental_map_type=1;
-- 建表语句
CREATE TABLE test_map (  id String,  users Map(String,  UInt8)) ENGINE = MergeTree ORDER BY (id, users);
-- 插入数据
INSERT INTO test_map VALUES ('1',{'nauu':20 , 'boness': 30});
INSERT INTO test_map VALUES ('2',{'jack':35 , 'lili':22, 'realinsight':6});
-- 直接查询
SELECT id, usersFROM test_map;
-- 通过key进行查询
SELECT id, users, users['nauu']FROM test_mapWHERE (users['boness']) = 30;

Map 类型相关函数：

• mapKeys 获取所有 Map 的 Keys
• mapValues 获取所有 Map 的 Values
• mapContains 判断是否包含了某个 Key

SELECT id, mapKeys(users) AS keys, mapValues(users) AS vals, mapContains(users, 'realinsight') AS hasKey FROM test_map;

注意，Clickhouse的Map类型的key只能为Integer、String或者UUID。

另外，当前版本的Clickhouse查询Map类型的数据时并不会走索引，因此在大数据场景下查询性能会受到影响。

2.利用JDBC插入Map类型数据

运行环境

clickhouse: 21.7.3.14
clickhouse-jdbc: 0.3.1

准备含有Map结构的Clickhouse表

CREATE TABLE dbtest.user_test
(
    `uid` Int64,
    `uname` String,
    `props` Map(String, String)
)
ENGINE = MergeTree
ORDER BY uid
SETTINGS index_granularity = 8192;

相关测试代码如下

public class ClickHouseJdbcTest {

    private static Connection conn;

    @Before
    public void init() throws ClassNotFoundException, SQLException {
        Class.forName(ClickHouseDriver.class.getCanonicalName());
        String url = "jdbc:clickhouse://ckhost:8123/dbtest";
        Properties prop = new Properties();
        prop.setProperty("username", ClickHouseConst.CLICKHOUSE_USERNAME);
        prop.setProperty("password", ClickHouseConst.CLICKHOUSE_PASSWORD);
        conn = DriverManager.getConnection(url, prop);
    }

    @After
    public void stop() throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    @Test
    public void testInsertMapObj() throws SQLException {
        String sql = "insert into dbtest.user_test values (?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 1005);
        ps.setString(2, "tianqi");

        Map<Object, Object> objMap = new HashMap<>();
        objMap.put("k3", "v3");

        ps.setObject(3, objMap);

        ps.addBatch();
        ps.executeBatch();

        ps.close();
    }

}

---

需要注意以下几点：

① 老版本的clickhouse-jdbc（例如0.2.4）插入Map失败，报如下错误

Code: 26, e.displayText() = DB::ParsingException: Cannot parse quoted string: expected opening quote ''', got xxx

使用0.3.1版本的clickhouse-jdbc可以正常插入

② 如果插入单条数据时，直接用下面代码不报错，但是批量执行时会报错

PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 1005);
ps.setString(2, "tianqi");
ps.setObject(3, "{'K3':'V3'}");
ps.execute();
conn.commit();

---

参考资料：

https://cloud.tencent.com/developer/article/1786452

https://github.com/ClickHouse/clickhouse-jdbc/issues/566