You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "linghengqian (via GitHub)" <gi...@apache.org> on 2024/11/24 11:23:14 UTC
[PR] Adds documentation for ClickHouse support [shardingsphere]
linghengqian opened a new pull request, #33779:
URL: https://github.com/apache/shardingsphere/pull/33779
For #29052.
Changes proposed in this pull request:
- Adds documentation for ClickHouse support.
- TL;DR, no developer would like to see a long discussion about ClickHouse in the documentation of GraalVM Native Image integration. It is necessary to separate the independent documentation of ClickHouse.
---
Before committing this PR, I'm sure that I have checked the following options:
- [x] My code follows the [code of conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/) of this project.
- [x] I have self-reviewed the commit code.
- [x] I have (or in comment I request) added corresponding labels for the pull request.
- [x] I have passed maven check locally : `./mvnw clean install -B -T1C -Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
- [x] I have made corresponding changes to the documentation.
- [x] I have added corresponding unit tests for my changes.
- [x] I have updated the Release Notes of the current development version. For more details, see [Update Release Note](https://shardingsphere.apache.org/community/en/involved/contribute/contributor/)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Adds documentation for ClickHouse support [shardingsphere]
Posted by "linghengqian (via GitHub)" <gi...@apache.org>.
linghengqian commented on code in PR #33779:
URL: https://github.com/apache/shardingsphere/pull/33779#discussion_r1855470469
##########
docs/document/content/user-manual/shardingsphere-jdbc/optional-plugins/clickhouse/_index.en.md:
##########
@@ -0,0 +1,222 @@
++++
+title = "ClickHouse"
+weight = 6
++++
+
+## Background Information
+
+ShardingSphere does not provide support for `driverClassName` of `com.clickhouse.jdbc.ClickHouseDriver` by default.
+ShardingSphere's support for ClickHouse JDBC Driver is in the optional module.
+
+## Prerequisites
+
+To use a `jdbcUrl` like `jdbc:ch://localhost:8123/demo_ds_0` for the data node in the ShardingSphere configuration file,
+the possible Maven dependencies are as follows,
+
+```xml
+<dependencies>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-jdbc</artifactId>
+ <version>${shardingsphere.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-parser-sql-clickhouse</artifactId>
+ <version>${shardingsphere.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>com.clickhouse</groupId>
+ <artifactId>clickhouse-jdbc</artifactId>
+ <classifier>http</classifier>
+ <version>0.6.3</version>
+ </dependency>
+</dependencies>
+```
+
+## Configuration example
+
+### Start ClickHouse
+
+Write a Docker Compose file to start ClickHouse.
+
+```yaml
+services:
+ clickhouse-server:
+ image: clickhouse/clickhouse-server:24.10.2.80
+ ports:
+ - "8123:8123"
+```
+
+### Create business tables
+
+Use a third-party tool to create a business database and business table in ClickHouse.
+Taking DBeaver Community as an example, if you use Ubuntu 22.04.4, you can quickly install it through Snapcraft.
+
+```shell
+sudo apt update && sudo apt upgrade -y
+sudo snap install dbeaver-ce
+snap run dbeaver-ce
+```
+
+In DBeaver Community, use `jdbcUrl` of `jdbc:ch://localhost:8123/default`, `username` of `default` to connect to ClickHouse,
+and leave `password` blank.
+Execute the following SQL,
+
+```sql
+-- noinspection SqlNoDataSourceInspectionForFile
+CREATE DATABASE demo_ds_0;
+CREATE DATABASE demo_ds_1;
+CREATE DATABASE demo_ds_2;
+```
+
+Use `jdbcUrl` of `jdbc:ch://localhost:8123/demo_ds_0`,
+`jdbc:ch://localhost:8123/demo_ds_1` and `jdbc:ch://localhost:8123/demo_ds_2`
+to connect to ClickHouse and execute the following SQL.
+
+```sql
+-- noinspection SqlNoDataSourceInspectionForFile
+create table IF NOT EXISTS t_order (
+ order_id Int64 NOT NULL DEFAULT rand(),
+ order_type Int32,
+ user_id Int32 NOT NULL,
+ address_id Int64 NOT NULL,
+ status String
+) engine = MergeTree
+ primary key (order_id)
+ order by (order_id);
+
+TRUNCATE TABLE t_order;
+```
+
+### Create ShardingSphere data source in business project
+
+After the business project introduces the dependencies involved in `prerequisites`,
+write the ShardingSphere data source configuration file `demo.yaml` on the classpath of the business project.
+
+```yaml
+dataSources:
+ ds_0:
+ dataSourceClassName: com.zaxxer.hikari.HikariDataSource
+ driverClassName: com.clickhouse.jdbc.ClickHouseDriver
+ jdbcUrl: jdbc:ch://localhost:8123/demo_ds_0
+ ds_1:
+ dataSourceClassName: com.zaxxer.hikari.HikariDataSource
+ driverClassName: com.clickhouse.jdbc.ClickHouseDriver
+ jdbcUrl: jdbc:ch://localhost:8123/demo_ds_1
+ ds_2:
+ dataSourceClassName: com.zaxxer.hikari.HikariDataSource
+ driverClassName: com.clickhouse.jdbc.ClickHouseDriver
+ jdbcUrl: jdbc:ch://localhost:8123/demo_ds_2
+rules:
+- !SHARDING
+ tables:
+ t_order:
+ actualDataNodes:
+ keyGenerateStrategy:
+ column: order_id
+ keyGeneratorName: snowflake
+ defaultDatabaseStrategy:
+ standard:
+ shardingColumn: user_id
+ shardingAlgorithmName: inline
+ shardingAlgorithms:
+ inline:
+ type: INLINE
+ props:
+ algorithm-expression: ds_${user_id % 2}
+ keyGenerators:
+ snowflake:
+ type: SNOWFLAKE
+```
+
+### Enjoy integration
+
+Create a ShardingSphere data source to enjoy integration,
+
+```java
+import com.zaxxer.hikari.HikariConfig;
+import com.zaxxer.hikari.HikariDataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+public class ExampleUtils {
+ void test() throws SQLException {
+ HikariConfig config = new HikariConfig();
+ config.setJdbcUrl("jdbc:shardingsphere:classpath:demo.yaml");
+ config.setDriverClassName("org.apache.shardingsphere.driver.ShardingSphereDriver");
+ try (HikariDataSource dataSource = new HikariDataSource(config);
+ Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("INSERT INTO t_order (user_id, order_type, address_id, status) VALUES (1, 1, 1, 'INSERT_TEST')");
+ statement.executeQuery("SELECT * FROM t_order");
+ statement.execute("alter table t_order delete where order_id=1");
+ }
+ }
+}
+```
+
+## Usage Limitations
+
+### SQL Limitations
+
+ShardingSphere JDBC DataSource does not yet support the execution of ClickHouse's `CREATE TABLE` statement and `TRUNCATE TABLE` statement.
+Users should consider submitting a PR containing unit tests for ShardingSphere.
+
+### Key Generate restrictions
+
+The column type corresponding to the Key Generate function of ClickHouse itself is `UUID`,
+and `UUID` is received as `java.util.UUID` in ClickHouse JDBC Driver,
+refer to https://github.com/ClickHouse/ClickHouse/issues/56228 .
+The column type corresponding to the Key Generate SPI implementation of ShardingSphere's `SNOWFLAKE` is `UInt64`,
+which is received as `java.lang.Long` in ShardingSphere JDBC Driver.
+
+When configuring ShardingSphere to connect to ClickHouse,
+if ShardingSphere is also configured to use the Key Generate SPI implementation of `SNOWFLAKE`,
+the column type in the ClickHouse real database used by ShardingSphere's Key Generate function should not be set to `UUID`.
+
+Because `com.clickhouse.jdbc.ClickHouseConnection#prepareStatement(String, int)` of `com.clickhouse:clickhouse-jdbc:0.6.3:http`
+Maven module intentionally throws an exception when `autoGeneratedKeys` is `java.sql.Statement.RETURN_GENERATED_KEYS`,
+to prevent ShardingSphere from proxying `com.clickhouse.jdbc.internal.ClickHouseConnectionImpl` normally,
+therefore, if users need to obtain the Key generated by ShardingSphere from the JDBC business code,
+they need to set `autoGeneratedKeys` to `java.sql.Statement.NO_GENERATED_KEYS`.
Review Comment:
- I opened https://github.com/ClickHouse/clickhouse-java/issues/1968 for this. Related issues already exist from a few years ago.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Adds documentation for ClickHouse support [shardingsphere]
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu merged PR #33779:
URL: https://github.com/apache/shardingsphere/pull/33779
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org