使用 JDBC 将应用程序连接到 ClickHouse

最新推荐文章于 2024-05-02 18:20:34 发布

Java页大数据

最新推荐文章于 2024-05-02 18:20:34 发布

阅读量822

点赞数

分类专栏： work 文章标签： clickhouse java jvm

本文链接：https://blog.csdn.net/liguanhaoyonghu/article/details/126614671

版权

work 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

使用 JDBC 将应用程序连接到 ClickHouse

本质上是用jdbc jar包来连接clickhouse

[root@only ck]# mkdir -p /data/ck/hello-clickhouse-java-app
[root@only ck]# ls
ck-jdbc-bridge  hello-clickhouse-java-app

[root@only hello-clickhouse-java-app]# cd /data/ck/hello-clickhouse-java-app
[root@only hello-clickhouse-java-app]# mkdir -p /data/ck/hello-clickhouse-java-app/lib
[root@only hello-clickhouse-java-app]# ls
lib 
[root@only hello-clickhouse-java-app]# cd lib/
[root@only hello-clickhouse-java-app]# wget -P lib https://repo1.maven.org/maven2/com/clickhouse/clickhouse-jdbc/0.3.2-patch7/clickhouse-jdbc-0.3.2-patch7-shaded.jar
[root@only lib]# ll
总用量 2916
-rw-r--r--. 1 root root 2983288 8月  23 23:26 clickhouse-jdbc-0.3.2-patch7-shaded.jar

切回jdbc主目录，新建源码路径和java类文件

[root@only hello-clickhouse-java-app]# pwd
/data/ck/hello-clickhouse-java-app
[root@only hello-clickhouse-java-app]# mkdir -p /data/ck/hello-clickhouse-java-app/src
[root@only hello-clickhouse-java-app]# ls
lib  src
[root@only hello-clickhouse-java-app]# mkdir -p src/main/java/helloclickhouse
[root@only hello-clickhouse-java-app]# ls src/main/java/helloclickhouse
[root@only hello-clickhouse-java-app]# touch src/main/java/helloclickhouse/HelloClickHouse.java
[root@only hello-clickhouse-java-app]# cd src/main/java/helloclickhouse/
[root@only hello-clickhouse-java-app]# touch src/main/java/helloclickhouse/HelloClickHouse.java
[root@only hello-clickhouse-java-app]# ls
HelloClickHouse.java

往新建的java文件添加内容

import com.clickhouse.jdbc.*;
import java.sql.*;
import java.util.*;

public class HelloClickHouse {
    public static void main(String[] args) throws Exception {

        String url = "jdbc:ch://only:8123";
        Properties properties = new Properties();
        ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
        try (Connection connection = dataSource.getConnection("default", "javaAndBigdata");
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select * from system.tables limit 10")) {
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columns = resultSetMetaData.getColumnCount();
            while (resultSet.next()) {
                for (int c = 1; c <= columns; c++) {
                    System.out.print(resultSetMetaData.getColumnName(c) + ":" + resultSet.getString(c) + (c < columns ? ", " : "\n"));
                }
            }
        }
    }
}

[root@only hello-clickhouse-java-app]# cd /data/ck/hello-clickhouse-java-app
[root@only hello-clickhouse-java-app]# pwd
/data/ck/hello-clickhouse-java-app
[root@only hello-clickhouse-java-app]# ls
lib  src
[root@only hello-clickhouse-java-app]# java -cp lib/clickhouse-jdbc-0.3.2-patch7-shaded.jar src/main/java/helloclickhouse/HelloClickHouse.java
错误: 找不到或无法加载主类 src.main.java.helloclickhouse.HelloClickHouse.java
#官方提供的jar只允许使用java17以上的版本执行！

切换到java17

[root@only hello-clickhouse-java-app]# /opt/jdk-17.0.4/bin/java -version
java version "17.0.4" 2022-07-19 LTS
Java(TM) SE Runtime Environment (build 17.0.4+11-LTS-179)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.4+11-LTS-179, mixed mode, sharing)

使用java17环境执行程序

[root@only hello-clickhouse-java-app]# /opt/jdk-17.0.4/bin/java -cp lib/clickhouse-jdbc-0.3.2-patch7-shaded.jar src/main/java/helloclickhouse/HelloClickHouse.java

# 下面为返回结果
	1. database:INFORMATION_SCHEMA, name:COLUMNS, uuid:00000000-0000-0000-0000-000000000000, engine:View, is_temporary:0, data_paths:[], metadata_path:, metadata_modification_time:1970-01-01 08:00:00, dependencies_database:[], dependencies_table:[], create_table_query:ATTACH VIEW COLUMNS (`table_catalog` String, `table_schema` String, `table_name` String, `column_name` String, `ordinal_position` UInt64, `column_default` String, `is_nullable` UInt8, `data_type` String, `character_maximum_length` Nullable(UInt64), `character_octet_length` Nullable(UInt64), `numeric_precision` Nullable(UInt64), `numeric_precision_radix` Nullable(UInt64), `numeric_scale` Nullable(UInt64), `datetime_precision` Nullable(UInt64), `character_set_catalog` Nullable(String), `character_set_schema` Nullable(String), `character_set_name` Nullable(String), `collation_catalog` Nullable(String), `collation_schema` Nullable(String), `collation_name` Nullable(String), `domain_catalog` Nullable(String), `domain_schema` Nullable(String), `domain_name` Nullable(String), `column_comment` String, `column_type` String, `TABLE_CATALOG` String ALIAS table_catalog, `TABLE_SCHEMA` String ALIAS table_schema, `TABLE_NAME` String ALIAS table_name, `COLUMN_NAME` String ALIAS column_name, `ORDINAL_POSITION` UInt64 ALIAS ordinal_position, `COLUMN_DEFAULT` String ALIAS column_default, `IS_NULLABLE` UInt8 ALIAS is_nullable, `DATA_TYPE` String ALIAS data_type, `CHARACTER_MAXIMUM_LENGTH` Nullable(UInt64) ALIAS character_maximum_length, `CHARACTER_OCTET_LENGTH` Nullable(UInt64) ALIAS character_octet_length, `NUMERIC_PRECISION` Nullable(UInt64) ALIAS numeric_precision, `NUMERIC_PRECISION_RADIX` Nullable(UInt64) ALIAS numeric_precision_radix, `NUMERIC_SCALE` Nullable(UInt64) ALIAS numeric_scale, `DATETIME_PRECISION` Nullable(UInt64) ALIAS datetime_precision, `CHARACTER_SET_CATALOG` Nullable(String) ALIAS character_set_catalog, `CHARACTER_SET_SCHEMA` Nullable(String) ALIAS character_set_schema, `CHARACTER_SET_NAME` Nullable(String) ALIAS character_set_name, `COLLATION_CATALOG` Nullable(String) ALIAS collation_catalog, `COLLATION_SCHEMA` Nullable(String) ALIAS collation_schema, `COLLATION_NAME` Nullable(String) ALIAS collation_name, `DOMAIN_CATALOG` Nullable(String) ALIAS domain_catalog, `DOMAIN_SCHEMA` Nullable(String) ALIAS domain_schema, `DOMAIN_NAME` Nullable(String) ALIAS domain_name, `COLUMN_COMMENT` String ALIAS column_comment, `COLUMN_TYPE` String ALIAS column_type) AS SELECT database AS table_catalog, database AS table_schema, table AS table_name, name AS column_name, position AS ordinal_position, default_expression AS column_default, type LIKE 'Nullable(%)' AS is_nullable, type AS data_type, character_octet_length AS character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale, datetime_precision, NULL AS character_set_catalog, NULL AS character_set_schema, NULL AS character_set_name, NULL AS collation_catalog, NULL AS collation_schema, NULL AS collation_name, NULL AS domain_catalog, NULL AS domain_schema, NULL AS domain_name, comment AS column_comment, type AS column_type FROM system.columns, engine_full:, as_select:SELECT database AS table_catalog, database AS table_schema, table AS table_name, name AS column_name, position AS ordinal_position, default_expression AS column_default, type LIKE 'Nullable(%)' AS is_nullable, type AS data_type, character_octet_length AS character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale, datetime_precision, NULL AS character_set_catalog, NULL AS character_set_schema, NULL AS character_set_name, NULL AS collation_catalog, NULL AS collation_schema, NULL AS collation_name, NULL AS domain_catalog, NULL AS domain_schema, NULL AS domain_name, comment AS column_comment, type AS column_type FROM system.columns, partition_key:, sorting_key:, primary_key:, sampling_key:, storage_policy:, total_rows:null, total_bytes:null, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:0, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	2. database:INFORMATION_SCHEMA, name:SCHEMATA, uuid:00000000-0000-0000-0000-000000000000, engine:View, is_temporary:0, data_paths:[], metadata_path:, metadata_modification_time:1970-01-01 08:00:00, dependencies_database:[], dependencies_table:[], create_table_query:ATTACH VIEW SCHEMATA (`catalog_name` String, `schema_name` String, `schema_owner` String, `default_character_set_catalog` Nullable(String), `default_character_set_schema` Nullable(String), `default_character_set_name` Nullable(String), `sql_path` Nullable(String), `CATALOG_NAME` String ALIAS catalog_name, `SCHEMA_NAME` String ALIAS schema_name, `SCHEMA_OWNER` String ALIAS schema_owner, `DEFAULT_CHARACTER_SET_CATALOG` Nullable(String) ALIAS default_character_set_catalog, `DEFAULT_CHARACTER_SET_SCHEMA` Nullable(String) ALIAS default_character_set_schema, `DEFAULT_CHARACTER_SET_NAME` Nullable(String) ALIAS default_character_set_name, `SQL_PATH` Nullable(String) ALIAS sql_path) AS SELECT name AS catalog_name, name AS schema_name, 'default' AS schema_owner, NULL AS default_character_set_catalog, NULL AS default_character_set_schema, NULL AS default_character_set_name, NULL AS sql_path FROM system.databases, engine_full:, as_select:SELECT name AS catalog_name, name AS schema_name, 'default' AS schema_owner, NULL AS default_character_set_catalog, NULL AS default_character_set_schema, NULL AS default_character_set_name, NULL AS sql_path FROM system.databases, partition_key:, sorting_key:, primary_key:, sampling_key:, storage_policy:, total_rows:null, total_bytes:null, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:0, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	3. database:INFORMATION_SCHEMA, name:TABLES, uuid:00000000-0000-0000-0000-000000000000, engine:View, is_temporary:0, data_paths:[], metadata_path:, metadata_modification_time:1970-01-01 08:00:00, dependencies_database:[], dependencies_table:[], create_table_query:ATTACH VIEW TABLES (`table_catalog` String, `table_schema` String, `table_name` String, `table_type` Enum8('BASE TABLE' = 1, 'VIEW' = 2, 'FOREIGN TABLE' = 3, 'LOCAL TEMPORARY' = 4, 'SYSTEM VIEW' = 5), `TABLE_CATALOG` String ALIAS table_catalog, `TABLE_SCHEMA` String ALIAS table_schema, `TABLE_NAME` String ALIAS table_name, `TABLE_TYPE` Enum8('BASE TABLE' = 1, 'VIEW' = 2, 'FOREIGN TABLE' = 3, 'LOCAL TEMPORARY' = 4, 'SYSTEM VIEW' = 5) ALIAS table_type) AS SELECT database AS table_catalog, database AS table_schema, name AS table_name, multiIf(is_temporary, 4, engine LIKE '%View', 2, engine LIKE 'System%', 5, has_own_data = 0, 3, 1) AS table_type FROM system.tables, engine_full:, as_select:SELECT database AS table_catalog, database AS table_schema, name AS table_name, multiIf(is_temporary, 4, engine LIKE '%View', 2, engine LIKE 'System%', 5, has_own_data = 0, 3, 1) AS table_type FROM system.tables, partition_key:, sorting_key:, primary_key:, sampling_key:, storage_policy:, total_rows:null, total_bytes:null, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:0, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	4. database:INFORMATION_SCHEMA, name:VIEWS, uuid:00000000-0000-0000-0000-000000000000, engine:View, is_temporary:0, data_paths:[], metadata_path:, metadata_modification_time:1970-01-01 08:00:00, dependencies_database:[], dependencies_table:[], create_table_query:ATTACH VIEW VIEWS (`table_catalog` String, `table_schema` String, `table_name` String, `view_definition` String, `check_option` String, `is_updatable` Enum8('NO' = 0, 'YES' = 1), `is_insertable_into` Enum8('NO' = 0, 'YES' = 1), `is_trigger_updatable` Enum8('NO' = 0, 'YES' = 1), `is_trigger_deletable` Enum8('NO' = 0, 'YES' = 1), `is_trigger_insertable_into` Enum8('NO' = 0, 'YES' = 1), `TABLE_CATALOG` String ALIAS table_catalog, `TABLE_SCHEMA` String ALIAS table_schema, `TABLE_NAME` String ALIAS table_name, `VIEW_DEFINITION` String ALIAS view_definition, `CHECK_OPTION` String ALIAS check_option, `IS_UPDATABLE` Enum8('NO' = 0, 'YES' = 1) ALIAS is_updatable, `IS_INSERTABLE_INTO` Enum8('NO' = 0, 'YES' = 1) ALIAS is_insertable_into, `IS_TRIGGER_UPDATABLE` Enum8('NO' = 0, 'YES' = 1) ALIAS is_trigger_updatable, `IS_TRIGGER_DELETABLE` Enum8('NO' = 0, 'YES' = 1) ALIAS is_trigger_deletable, `IS_TRIGGER_INSERTABLE_INTO` Enum8('NO' = 0, 'YES' = 1) ALIAS is_trigger_insertable_into) AS SELECT database AS table_catalog, database AS table_schema, name AS table_name, as_select AS view_definition, 'NONE' AS check_option, 0 AS is_updatable, engine = 'MaterializedView' AS is_insertable_into, 0 AS is_trigger_updatable, 0 AS is_trigger_deletable, 0 AS is_trigger_insertable_into FROM system.tables WHERE engine LIKE '%View', engine_full:, as_select:SELECT database AS table_catalog, database AS table_schema, name AS table_name, as_select AS view_definition, 'NONE' AS check_option, 0 AS is_updatable, engine = 'MaterializedView' AS is_insertable_into, 0 AS is_trigger_updatable, 0 AS is_trigger_deletable, 0 AS is_trigger_insertable_into FROM system.tables WHERE engine LIKE '%View', partition_key:, sorting_key:, primary_key:, sampling_key:, storage_policy:, total_rows:null, total_bytes:null, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:0, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	5. database:atomicDatabase, name:testUUID_exchange, uuid:b110b25c-24ad-45b6-b788-6db084dc5ecb, engine:MergeTree, is_temporary:0, data_paths:[/var/lib/clickhouse/store/b11/b110b25c-24ad-45b6-b788-6db084dc5ecb/], metadata_path:/var/lib/clickhouse/store/d32/d3259a83-0b56-41df-b3af-7dba79e74b09/testUUID_exchange.sql, metadata_modification_time:2022-07-10 15:26:37, dependencies_database:[], dependencies_table:[], create_table_query:CREATE TABLE atomicDatabase.testUUID_exchange (`id` UInt64) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192, engine_full:MergeTree ORDER BY id SETTINGS index_granularity = 8192, as_select:, partition_key:, sorting_key:id, primary_key:id, sampling_key:, storage_policy:default, total_rows:0, total_bytes:0, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:1, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	6. database:atomicDatabase, name:testUUID_rename1, uuid:2e674a1d-ea56-4eeb-81b9-e8a4c4767f31, engine:MergeTree, is_temporary:0, data_paths:[/var/lib/clickhouse/store/2e6/2e674a1d-ea56-4eeb-81b9-e8a4c4767f31/], metadata_path:/var/lib/clickhouse/store/d32/d3259a83-0b56-41df-b3af-7dba79e74b09/testUUID_rename1.sql, metadata_modification_time:2022-07-05 23:30:29, dependencies_database:[], dependencies_table:[], create_table_query:CREATE TABLE atomicDatabase.testUUID_rename1 (`id` Int64) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192, engine_full:MergeTree ORDER BY id SETTINGS index_granularity = 8192, as_select:, partition_key:, sorting_key:id, primary_key:id, sampling_key:, storage_policy:default, total_rows:6, total_bytes:217, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:1, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	7. database:default, name:.inner.test_agg_view, uuid:00000000-0000-0000-0000-000000000000, engine:AggregatingMergeTree, is_temporary:0, data_paths:[/var/lib/clickhouse/data/default/%2Einner%2Etest_agg_view/], metadata_path:/var/lib/clickhouse/metadata/default/%2Einner%2Etest_agg_view.sql, metadata_modification_time:2022-07-13 23:51:49, dependencies_database:[], dependencies_table:[], create_table_query:CREATE TABLE default.`.inner.test_agg_view` (`id` UInt64, `maxMoney` AggregateFunction(max, UInt64)) ENGINE = AggregatingMergeTree ORDER BY id SETTINGS index_granularity = 8192, engine_full:AggregatingMergeTree ORDER BY id SETTINGS index_granularity = 8192, as_select:, partition_key:, sorting_key:id, primary_key:id, sampling_key:, storage_policy:default, total_rows:5, total_bytes:208, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:1, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	8. database:default, name:VersionedCollapsingMergeTreeTable, uuid:00000000-0000-0000-0000-000000000000, engine:VersionedCollapsingMergeTree, is_temporary:0, data_paths:[/var/lib/clickhouse/data/default/VersionedCollapsingMergeTreeTable/], metadata_path:/var/lib/clickhouse/metadata/default/VersionedCollapsingMergeTreeTable.sql, metadata_modification_time:2022-08-14 00:48:06, dependencies_database:[], dependencies_table:[], create_table_query:CREATE TABLE default.VersionedCollapsingMergeTreeTable (`id` UInt64, `name` String, `age` Int64, `sign` Int8, `version` Int64) ENGINE = VersionedCollapsingMergeTree(sign, version) ORDER BY id SETTINGS index_granularity = 8192, engine_full:VersionedCollapsingMergeTree(sign, version) ORDER BY id SETTINGS index_granularity = 8192, as_select:, partition_key:, sorting_key:id, version, primary_key:id, sampling_key:, storage_policy:default, total_rows:1, total_bytes:350, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:1, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	9. database:default, name:aggMergeFun, uuid:00000000-0000-0000-0000-000000000000, engine:MergeTree, is_temporary:0, data_paths:[/var/lib/clickhouse/data/default/aggMergeFun/], metadata_path:/var/lib/clickhouse/metadata/default/aggMergeFun.sql, metadata_modification_time:2022-07-13 23:25:58, dependencies_database:[default], dependencies_table:[test_agg_view], create_table_query:CREATE TABLE default.aggMergeFun (`id` UInt64, `money` UInt64) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192, engine_full:MergeTree ORDER BY id SETTINGS index_granularity = 8192, as_select:, partition_key:, sorting_key:id, primary_key:id, sampling_key:, storage_policy:default, total_rows:45, total_bytes:312, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:1, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]
	10. database:default, name:aggMergeFunTar, uuid:00000000-0000-0000-0000-000000000000, engine:AggregatingMergeTree, is_temporary:0, data_paths:[/var/lib/clickhouse/data/default/aggMergeFunTar/], metadata_path:/var/lib/clickhouse/metadata/default/aggMergeFunTar.sql, metadata_modification_time:2022-07-13 23:31:28, dependencies_database:[], dependencies_table:[], create_table_query:CREATE TABLE default.aggMergeFunTar (`id` UInt64, `money` UInt64) ENGINE = AggregatingMergeTree ORDER BY id SETTINGS index_granularity = 8192, engine_full:AggregatingMergeTree ORDER BY id SETTINGS index_granularity = 8192, as_select:, partition_key:, sorting_key:id, primary_key:id, sampling_key:, storage_policy:default, total_rows:0, total_bytes:0, lifetime_rows:null, lifetime_bytes:null, comment:, has_own_data:1, loading_dependencies_database:[], loading_dependencies_table:[], loading_dependent_database:[], loading_dependent_table:[]