Implement setClientInfo

[piotrp]

Feature request: implement java.sql.Connection#setClientInfo (and ClickHouseDatabaseMetaData#getClientInfoProperties)

That would allow to pass some useful metadata to database, eg. frontend user that initiated current query, which would be then accessible in query_log. Or customized User-Agent from various parts of backend application without the need to create separate connection pools.

From a quick glance these properties would be useful:

• http_referer
• http_forwarded_for
• client_name (i.e. HTTP User Agent)
• log_comment

[zhicwu]

Thanks @piotrp for the suggestion, let me see if I can put it into the next release.

Actually most of the properties are designed for clients using different protocols. This driver uses http protocol so the user agent header will only reflect on http_user_agent but not client_name, because the latter is reserved for native clients. On the other hand, log_comment is protocol irrelevant so it can be used on both sides.

I think we can start with below properties according to https://clickhouse.tech/docs/en/operations/system-tables/query_log/.	Protocol	Property	Type	Description
*	log_comment	String	Log comment. It can be set to arbitrary string no longer than max_query_size. An empty string if it is not defined.
Native	os_user?	String	Operating system username who runs clickhouse-client.
Native	client_hostname	String	Hostname of the client machine where the clickhouse-client or another TCP client is run.
Native	client_name	String	The clickhouse-client or another TCP client name.
Native	client_revision	UInt32	Revision of the clickhouse-client or another TCP client.
Native	client_version_major	UInt32	Major version of the clickhouse-client or another TCP client.
Native	client_version_minor	UInt32	Minor version of the clickhouse-client or another TCP client.
Native	client_version_patch	UInt32	Patch component of the clickhouse-client or another TCP client version.
HTTP	http_user_agent	String	HTTP header UserAgent passed in the HTTP query.
HTTP	http_referer	String	HTTP header Referer passed in the HTTP query (contains an absolute or partial address of the page making the query).
HTTP	forwarded_for	String	HTTP header X-Forwarded-For passed in the HTTP query.

Note: property name in JDBC driver will be different as multiple protocols will be supported.

[den-crane]

BTW, "client_name (i.e. HTTP User Agent)" has been implemented -- setClientName

       ClickHouseProperties clickHouseProperties = new ClickHouseProperties(new Properties());
        clickHouseProperties.setUser("default");
        clickHouseProperties.setPassword("***");
        clickHouseProperties.setClientName("my_client_xxx");
        ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/default", clickHouseProperties);
        ClickHouseConnection connection = dataSource.getConnection();
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("select http_user_agent from system.processes");
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        rs.close();

[zhicwu]

In the new JDBC driver, which currently only supports http and grpc two protocols:

• client_name(mapped to http_user_agent when using http protocol) is similar as before

  Properties props = new Properties();
  props.setProperty("client_name", "Test Client");
  Connection conn = DriverManager.getConnection("jdbc:ch://127.0.0.1/system", props);
  // override as needed
  conn.setClientInfo("ApplicationName", "New Client");
  props.setProperty("ApplicationName", "New Client 1.0");
  conn.setClientInfo(props); 

• http_referer and forwarded_for can be set as custom http headers, as they're only available when using http

  Properties props = new Properties();
  props.setProperty("custom_http_headers", "Referer=https://example.com/, X-Forwarded-For=1.2.3.4 \\,5.6.7.8");
  Connection conn = DriverManager.getConnection("jdbc:ch://127.0.0.1/system", props);
  // override as needed
  conn.setClientInfo("CustomHttpHeaders", "User-Agent: New Client, Referer=https://www.example.com/, X-Forwarded-For=0.0.0.0");

• log_comment can only be set as part of the query

  Properties props = new Properties();
  props.setProperty("log_leading_comment", "true");
  Connection conn = DriverManager.getConnection("jdbc:ch://127.0.0.1/system", props);
  Statement stmt = conn.createStatement();
  stmt.execute(
   "-- step 1: drop table if it exists\n"
   + "drop table if exists tmp1;"
   + "/* step 2: create table*/"
   + "create table tmp1(s String) engine=Memory;"
   + "system flush logs; "
   + "select distinct log_comment, query from system.query_log where log_comment != '' order by event_time desc limit 10");
  
  ResultSet rs = stmt.getResultSet();
  rs.next();
  rs.getString(1); // step 1: drop table if it exists
  rs.getString(2); // drop table if exists tmp1;
  rs.next();
  rs.getString(1); // step 2: create table
  rs.getString(2); // create table tmp1(s String) engine=Memory;

  Note: this does not work in tools like SQuirreL SQL Client, as it will remove leading comment from query.

[piotrp]

ApplicationName (ClickHouseConnection.PROP_APPLICATION_NAME) can't be changed on a connection when using JDBC driver 0.7.0. I noticed this after updating from 0.3.2-patch11 - driver always sends the original client_name that was present when connection was created.

When I have a Connection with client_name set to Abc and run the following code:

ds.connection.use { connection ->
    println("Original: " + connection.getClientInfo(ClickHouseConnection.PROP_APPLICATION_NAME))
    connection.setClientInfo(ClickHouseConnection.PROP_APPLICATION_NAME, "Custom")
    println("Customized: " + connection.getClientInfo(ClickHouseConnection.PROP_APPLICATION_NAME))

    connection.createStatement().use { stmt ->
        val ua =
            stmt.executeQuery("SELECT http_user_agent FROM system.processes WHERE query LIKE 'SELECT http_user_agent FROM system.processes%'")
                .use { rs ->
                    rs.next()
                    rs.getString(1)
                }
        println("Value sent do DB: $ua")
    }
}

I get the following output:

Original: Abc
Customized: Custom
Value sent do DB: Abc ClickHouse-JavaClient/0.7.0 (OpenJDK 64-Bit Server VM/Temurin-17.0.10+7; Apache-HttpClient/5.2.5)