You need to enable JavaScript to run this app.
导航
查询网关
最近更新时间:2023.04.28 18:13:20首次发布时间:2023.04.28 18:13:20
简介

ByteHouse企业版为用户提供网关组件,作为集群的负载均衡器和统一的查询入口。企业版查询网关支持社区 Clickhouse Client、Clickhouse Java JDBC、Clickhouse GO Driver 等多种方式接入,也可使用 DataGrip、DBeaver 工具连接。

企业版查询网关感知节点健康状态,将查询负载平均分发到健康的 ByteHouse 节点上,Client 端无需感知集群节点变化或扩缩容等运维操作。

网关实现的功能如下:

  • 通过round robin 技术实现请求在副本和节点间的均衡负载。

  • 支持检查节点健康情况,熔断故障节点,防止向不健康的节点发送请求。

  • 支持根据代理请求到多个 ClickHouse 集群。指定集群或者根据用户和查询自动判断路由的集群。

  • 支持不同模式,除了自动分配任意节点,也支持选择指定节点,或所有节点,取代 on cluster语法。

可通过 运维与权限管理 -> 集群管理 -> 集群列表 -> 点击集群查看详情,看到集群的私网网关地址与公网网关地址(若该集群绑定了外网 IP)。

命令支持
SQL StatementUsageTCP协议HTTP协议
DROPDATABASE
TABLE
CREATENORMAL
DATABASE
SELECTNORMAL
WITH CALUSE
INSERT INTONORMAL
INSERT INTO SELECT
ALTERCOLUMN
DELETE
UPDATE
SHOWCREATE TABLE
SHOW DATABASES
SHOW TABLES
GRANTPRIVILEGE
ROLE
EXPLAINAST
REVOKEPRIVILEGE
ATTACH
CHECK TABLE
DESCRIBE TABLE
DETACH
EXISTS
KILLQUERY
OPTIMIZE
RENAME
EXCHANGE
SET
SET ROLE
TRUNCATE
USE
网关功能

HTTP连接

curl --location --request POST '<gateway-address>:8123/?cluster=<cluster_name>&password=<password>&user=<user_name>&query_id=<query_id>&custom_gw_force_all_nodes=<true/false>&custom_gw_force\_ck_node=<node_ip>' \
--data-raw '<SQL>'

参数:

  • gateway-address:网关地址;

  • cluster:【可选】集群。若用户只有 1 个集群,可以不填;

  • user:ByteHouse 集群用户名;

  • password:ByteHouse 集群用户密码;

  • query_id:指定查询的 ID,建议使用业务标识作为前缀 + id,方便后续在查询历史中过滤业务下的所有查询,示例:“bi-xxxxx”;

  • custom_gw_force_all_nodes:【可选】将查询发送到全部节点,true/false(默认 false);和 custom_gw_force_ck_node 只能选择其一填写。

  • custom_gw_force_ck_node:【可选】将查询发送到指定节点,填写节点 的 IP。和 custom_gw_force_all_nodes 只能选择其一填写;

  • data-raw:发送到集群的 SQL 语句。

TCP 连接

以下以 clickhouse-client 举例:

clickhouse-client -h <gateway-address> -p 9000 --user <Username>-0-<cluster_name> --password <Password>

参数:

  • Gateway-address:网关地址;

  • Cluster:【可选】集群。若用户只有 1 个集群,可以不填;

  • user:ByteHouse 集群用户名;

  • Password:ByteHouse 集群用户密码;

建立连接后,可通过 set 命令设置以下参数:

  • custom_gw_force_all_nodes:【可选】将查询发送到全部节点,true/false(默认 false);和 custom_gw_force_ck_node 只能选择其一设置。

  • custom_gw_force_ck_node:【可选】将查询发送到指定节点,填写节点 的 IP。和 custom_gw_force_all_nodes 只能选择其一设置;

连接示例

下发到任意节点

根据负载均衡策略,选择最优节点分配。

HTTP 直连示例:

curl --location --request POST 'http://<gateway-address>:8123/?user=<user_name>&password=<password> \
--data-raw '<SQL>'

ClickHouse Client 连接示例:

clickhouse client --host <HOST>.bytehouse-ce.volces.com --user <USER> --password <PASSWORD>

JDBC 连接示例:

若使用clickhouse官网jdbc driver: https://github.com/ClickHouse/clickhouse-jdbc

ClickHouse JDBC Version:

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.1<</version>
    <classifier>http</classifier>
</dependency>

Java 示例:

package org.example;

import java.sql.*;

public class Main {

    static String jdbcUrl = "jdbc:clickhouse://<URL>:8123/default?custom_settings=custom_gw_force_ck_node=<node_ip>";
    static String user = "<USER>";
    static String password = "<PASSWORD>";

    public static void main(String[] args) throws ClassNotFoundException {
        try {
            Connection con = DriverManager.getConnection(jdbcUrl,user,password);
            Statement stmt = con.createStatement();
            ResultSet resultSet = stmt.executeQuery("select * from system.clusters");
            while (resultSet.next()) {
                System.out.println("host_name: " + resultSet.getString("host_name") + " is_local: " + resultSet.getInt("is_local"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Golang 示例:

Clickhouse Go Driver Version: github.com/ClickHouse/clickhouse-go/v2 v2.7.0

代码示例:

func TestSpecifyNode(t *testing.T) {
   ctx := context.Background()

   conn, err := clickhouse.Open(&clickhouse.Options{
      Addr: []string{"<URL>.bytehouse-ce.volces.com:9000"},

      Auth:     clickhouse.Auth{Database: "default", Username: "<USER>", Password: "<USER>"},
      Debug:    true,
   })
   if err != nil {
      fmt.Printf("error----->: %v", err)
      return
   }

   defer conn.Close()

   rows, err := conn.Query(ctx, "select host_name, is_local from system.clusters")
   if err != nil {
      fmt.Printf("error-----:%v", err)
      return
   }

   var result struct {
      Col1 string `ch:"host_name"`
      Col2 uint8  `ch:"is_local"`
   }

   for {
      if rows.Next() {
         err := rows.ScanStruct(&result)
         if err != nil {
            fmt.Printf("error-----:%v", err)
            return
         }
         fmt.Printf("----> %s %d\n", result.Col1, result.Col2)
      } else {
         break
      }
   }
}

下发到指定节点

HTTP 直连示例:

curl --location --request POST 'http://<gateway-address>:8123/?user=<user_name>&password=<password>&custom_gw_force_ck_node=<node_ip>' \
--data-raw '<SQL>'

ClickHouse Client 连接示例:

clickhouse client --host <HOST> --user <USER> --password <PASSWORD>                     

ByteHouse Gateway :) set custom_gw_force_ck_node='<node_ip>'

JDBC 连接示例:

若使用clickhouse官网jdbc driver: https://github.com/ClickHouse/clickhouse-jdbc

ClickHouse JDBC Version:

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.1<</version>
    <classifier>http</classifier>
</dependency>

Java 示例:

package org.example;

import java.sql.*;

public class Main {

    static String jdbcUrl = "jdbc:clickhouse://<URL>:8123/default?custom_settings=custom_gw_force_ck_node=<node_ip>";
    static String user = "<USER>";
    static String password = "<PASSWORD>";

    public static void main(String[] args) throws ClassNotFoundException {
        try {
            Connection con = DriverManager.getConnection(jdbcUrl,user,password);
            Statement stmt = con.createStatement();
            ResultSet resultSet = stmt.executeQuery("select * from system.clusters");
            while (resultSet.next()) {
                System.out.println("host_name: " + resultSet.getString("host_name") + " is_local: " + resultSet.getInt("is_local"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Golang 示例:

Clickhouse Go Driver Version: github.com/ClickHouse/clickhouse-go/v2 (v2.7.0)

代码示例:

func TestSpecifyNode(t *testing.T) {
   ctx := context.Background()

   conn, err := clickhouse.Open(&clickhouse.Options{
      Addr: []string{"<URL>.bytehouse-ce.volces.com:9000"},

      Auth:     clickhouse.Auth{Database: "default", Username: "<USER>", Password: "<USER>"},
      Debug:    true,
      Settings: map[string]interface{}{"custom_gw_force_ck_node": "<HOST>"},
   })
   if err != nil {
      fmt.Printf("error----->: %v", err)
      return
   }

   defer conn.Close()

   rows, err := conn.Query(ctx, "select host_name, is_local from system.clusters")
   if err != nil {
      fmt.Printf("error-----:%v", err)
      return
   }

   var result struct {
      Col1 string `ch:"host_name"`
      Col2 uint8  `ch:"is_local"`
   }

   for {
      if rows.Next() {
         err := rows.ScanStruct(&result)
         if err != nil {
            fmt.Printf("error-----:%v", err)
            return
         }
         fmt.Printf("----> %s %d\n", result.Col1, result.Col2)
      } else {
         break
      }
   }
}

下发到全部节点

HTTP 直连示例:

curl --location --request POST 'http://<gateway-address>:8123/?user=<user_name>&password=<password>&custom_gw_force_all_nodes=true \
--data-raw '<SQL>'

ClickHouse Client 示例:

clickhouse client --host <HOST>.bytehouse-ce.volces.com --user <USER> --password <PASSWORD>

ByteHouse Gateway :) set custom_gw_force_all_nodes=true
ByteHouse Gateway :) CREATE TABLE default.test(`id` Int64,`info` String COMMENT '1') ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192

CREATE TABLE default.test
(
    `id` Int64,
    `info` String COMMENT '1'
)
ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192

JDBC 连接示例:

若使用clickhouse官网jdbc driver: https://github.com/ClickHouse/clickhouse-jdbc

ClickHouse JDBC Version:

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.1<</version>
    <classifier>http</classifier>
</dependency>

Java 示例:

package org.example;

import java.sql.*;

public class Main {

    static String jdbcUrl = "jdbc:clickhouse://<URL>:8123/default?custom_settings=custom_gw_force_all_nodes=true";
    static String user = "<USER>";
    static String password = "<USER>";

    public static void main(String[] args) throws ClassNotFoundException {
        try {
            Connection con = DriverManager.getConnection(jdbcUrl,user,password);
            Statement stmt = con.createStatement();
            ResultSet resultSet = stmt.executeQuery("select * from system.clusters");
            while (resultSet.next()) {
                System.out.println("host_name: " + resultSet.getString("host_name") + " is_local: " + resultSet.getInt("is_local"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Golang 连接示例:

Clickhouse Go Driver Version: github.com/ClickHouse/clickhouse-go/v2 (v2.7.0)

func TestAllNodes(t *testing.T) {
   ctx := context.Background()

   conn, err := clickhouse.Open(&clickhouse.Options{
      Addr: []string{"<URL>.bytehouse-ce.volces.com:9000"},

      Auth:     clickhouse.Auth{Database: "default", Username: "<USER>", Password: "<USER>"},
      Debug:    true,
      Settings: map[string]interface{}{"custom_gw_force_all_nodes": true},
   })
   if err != nil {
      fmt.Printf("error----->: %v", err)
      return
   }
   
   defer conn.Close()
   
   err = conn.Exec(ctx, "CREATE TABLE default.test(`id` Int64,`info` String COMMENT '1') ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192")
   if err != nil {
      fmt.Printf("error----->: %v", err)
      return
   }
}

异步查询

(仅 HTTP 支持)过在Header中添加 X-Async-Query 即可使用。发送后不立刻获得结果,而是用 query_id 异步请求获得查询结果,常用于大查询场景。

例子:

curl --location --request POST 'http://<gateway-address>:8123/?user=<user_name>&password=<password>&query_id=<queryID>' \
--header 'X-Async-Query: 1' \
--data-raw 'show tables FORMAT JSON;'

Response:

  • 异步提交时与查询进行中,返回的 Header 中有以下信息:
    • Header:X-Async-Query: running
  • 查询完成后,会返回结果,同时 Header 中会有 X-Spend-Time 信息(单位:毫秒):
    • Header: X-Spend-Time: 100
功能特性限制

暂不支持数据加密 TLS 功能:

HTTPS & Clickhouse Client --secure