[client-v2] Underscore in column name is removed

[averemee-si]

Describe the bug

According to the documentation, underscore is a valid character in a column name:

Non-quoted identifiers must match the regex ^[a-zA-Z_][0-9a-zA-Z_]*$ and can not be equal to [keywords](https://clickhouse.com/docs/en/sql-reference/syntax#keywords). Examples: x, _1, X_y__Z123_.

But when I try to register an POJO for a table in the Java client-v2, an error occurs for any column that contains an underscore in the name: Exception in thread "main" java.lang.IllegalArgumentException: No serializer found for column 'ORA_ROW_ID'. Did you forget to register it? at com.clickhouse.client.api.Client.insert(Client.java:1178) at com.clickhouse.client.api.Client.insert(Client.java:1124) at solutions.a2.oracle.expimp.pipe.DtoTest.main(DtoTest.java:84)

Steps to reproduce

1. Create project with Java client-v2, i.e. for Gradle add dependency:

   def clickHouseVersion = "0.7.0"
   // https://mvnrepository.com/artifact/com.clickhouse/clickhouse-client
   implementation("com.clickhouse:client-v2:$clickHouseVersion")

2. Create table

   CREATE TABLE TEST4CH(
   ORA_ROW_ID String,
   OWNER String, 
   NAME String, 
   TYPE String,
   LINE UInt32,
   TEXT String, 
   ORIGIN_CON_ID Nullable(UInt32)) engine = MergeTree order by ();

3. Try to register POJO for this table and then insert to this table

Expected behaviour

Successful execution

Code example

        client.register(dynamicClass, client.getTableSchema("TEST4CH"));
        InsertSettings settings = new InsertSettings().compressClientRequest(true);
        try (InsertResponse response = client.insert("TEST4CH", list).get()) {
            System.out.println("rows written=" + response.getWrittenRows());
        } catch (InterruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ExecutionException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        client.close();

Error log

6:50:01,415 INFO main Client:build:886 - Using server timezone: UTC 06:50:01,712 INFO main Client:poolConnectionManager:201 - Connection reuse strategy: FIFO 06:50:01,745 INFO main Client::110 - client compression: true, server compression: true, http compression: false 06:50:01,746 INFO main Client::168 - Using new http client implementation 06:50:03,499 WARN main Client:register:1087 - No getter method found for column: orarowid 06:50:03,499 WARN main Client:register:1095 - No setter method found for column: orarowid 06:50:03,509 WARN main Client:register:1087 - No getter method found for column: originconid 06:50:03,509 WARN main Client:register:1095 - No setter method found for column: originconid Exception in thread "main" java.lang.IllegalArgumentException: No serializer found for column 'ORA_ROW_ID'. Did you forget to register it? at com.clickhouse.client.api.Client.insert(Client.java:1178) at com.clickhouse.client.api.Client.insert(Client.java:1124) at solutions.a2.oracle.expimp.pipe.DtoTest.main(DtoTest.java:84)

Configuration

Environment

• Client version: 0.7.0
• Language version: N/A
• OS: Linux

ClickHouse server

24.6.2

• CREATE TABLE statements for tables involved: CREATE TABLE TEST4CH( ORA_ROW_ID String, OWNER String, NAME String, TYPE String, LINE UInt32, TEXT String, ORIGIN_CON_ID Nullable(UInt32)) engine = MergeTree order by ();

[averemee-si]

Proposed PR - register(): make underscore legal in column name

[chernser]

@averemee-si thank you for reporting the issue! Would you please give us an example of your POJO? If it doesn't have getters please let us know.

Thank you!

[averemee-si]

Sergey,

I'm not using a static POJO because we need to build a bootstrapped Oracle database with thousands of tables, each table with up to five hundred columns, and each name with an underscore. Due to these limitations, we use dynamic construction of the POJO

1. You need to add ByteBuddy to project dependencies - below Gradle snippet

    def byteBuddyVersion = "1.15.3"
    // https://mvnrepository.com/artifact/net.bytebuddy/byte-buddy
    implementation("net.bytebuddy:byte-buddy:$byteBuddyVersion")

2. Java code snippet (of course this is an example - we plan to download data from Oracle Data dictionary

        DynamicType.Unloaded<?> dynamicType = new ByteBuddy()
                .subclass(Object.class, ConstructorStrategy.Default.DEFAULT_CONSTRUCTOR)
                .name("DEPT.SCOTT.PRD.ora")
                .defineProperty("ORA_ROW_ID", String.class)
                .defineProperty("OWNER", String.class)
                .defineProperty("NAME", String.class)
                .defineProperty("TYPE", String.class)
                .defineProperty("LINE", int.class)
                .defineProperty("TEXT", String.class)
                .defineProperty("ORIGIN_CON_ID", int.class)
                .make();

        Class<?> dynamicClass = dynamicType
                .load(dynamicType.getClass().getClassLoader(), ClassLoadingStrategy.Default.WRAPPER)
                .getLoaded();

        // Print list of getters/setters
        for (Method method : dynamicClass.getDeclaredMethods()) {
            System.out.println("++>" + method.getName() + "=" + Modifier.isPublic(method.getModifiers()));
        }

        Constructor<?> dynamicConstr = dynamicClass.getConstructor();
        Object dtoElement = dynamicConstr.newInstance();

        dynamicClass.getMethod("setORA_ROW_ID", String.class).invoke(dtoElement, "AAAXCiAADAAAb2TAAA");
        dynamicClass.getMethod("setOWNER", String.class).invoke(dtoElement, "SYS");
        dynamicClass.getMethod("setNAME", String.class).invoke(dtoElement, "STANDARD");
        dynamicClass.getMethod("setTYPE", String.class).invoke(dtoElement, "PACKAGE");
        dynamicClass.getMethod("setLINE", int.class).invoke(dtoElement, 1);
        dynamicClass.getMethod("setTEXT", String.class).invoke(dtoElement, "package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SED edit occurs!");
        dynamicClass.getMethod("setORIGIN_CON_ID", int.class).invoke(dtoElement, 1);

        List<Object> list = new ArrayList<>();
        list.add(dtoElement);

        PipePool pool = PipePool.get("ch-1", "clickhouse:XXXXX", "YYYYYYY", "ZZZZZZZZ");
        Client client = pool.getChClient();
        client.register(dynamicClass, client.getTableSchema("TEST4CH"));
        InsertSettings settings = new InsertSettings().compressClientRequest(true);
        try (InsertResponse response = client.insert("TEST4CH", list).get()) {
            System.out.println("rows written=" + response.getWrittenRows());
        } catch (InterruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ExecutionException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        client.close();

Thanks/ Заранее признателен!

Aleksei

[chernser]

@averemee-si Thank you for explaining! This is fine - I will think on the solution to make it more general. Something like settings where exact mapping or strategy can be passed. Hopefully this week I can provide the solution.

[chernser]

Good day, @averemee-si! Have you a chance to test the nightly build?

Thanks!

[averemee-si]

Hi Sergey,

Thanks for the update. I'll check it tomorrow.

Regards, Aleksei

[averemee-si]

Hi Sergey,

I started checking and realized that I can't download the nightly build. Please tell me where I can find it.

Thanks.

Regards, Aleksei

[chernser]

@averemee-si here is a gist with instructions https://gist.github.com/chernser/b4eacfde70093847449e3aef8e51ae8e

[averemee-si]

Thanks! Cool.

Tested with build 20241022-155731-21

Regards, Aleksei

[chernser]

@averemee-si Does it resolve the issue?

[averemee-si]

Yes, thanks. that's what Cool meant

Regards, Aleksei