您可以通过开源的 MySQL JDBC 驱动程序连接到 ByteHouse 云数仓版。

数据库用户功能为 Beta 功能，如需使用数据库用户连接，请联系提交工单或联系 ByteHouse 团队获取白名单权限。

1. MySQL JDBC Driver 版本： 8.3.0 及以上版本；
2. OpenJDK 版本： 8 及以上版本；
3. 拥有 ByteHouse 账号。

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.3.0</version>
</dependency>

连接信息

通过 MySQL JDBC Driver 连接 ByteHouse 的字符串信息如下，其中 {HOST} 、{USER}、需要的验证密钥 {API_KEY} 或 {PASSWORD} 信息请参考获取 ByteHouse 连接信息获取，{Port} 默认值为 3306。

jdbc:mysql://{HOST}:{Port}/{DATABASE_NAME}

数据插入与查询

使用 IAM 用户

您可以参考下面的代码示例来进行数据插入与查询，注意替换连接信息，详见上文连接信息。

import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

import java.sql.*;

public class TestMySQLConnection {

    private Connection connection;

    @BeforeClass
    private void beforeClass() throws SQLException, ClassNotFoundException {

        String host = "{HOST}";
        int port = 3306;
        String user = "bytehouse";
        String password = "{API_KEY}";//API_key密钥
        String database = "{DATABASE_NAME}"; //数据库名称

        String url = String.format("jdbc:mysql://%s:%d/%s", host, port, database);

        Class.forName(com.mysql.cj.jdbc.Driver.class.getName());
        connection = DriverManager.getConnection(url, user, password);
    }


    @AfterClass
    public void tearDown() throws SQLException {
        // 关闭数据库连接
        if (connection != null) {
            connection.close();
        }
    }

    @Test
    public void testShowDatabases() {
        String sql = "show databases;";
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println(toCSV(resultSet));
            statement.close();
        } catch (SQLException e) {
            Assert.fail(String.format("Failed to execute: %s:%s", sql, e.getMessage()));
        }
    }

    private String toCSV(ResultSet resultSet) throws SQLException {
        StringBuilder builder = new StringBuilder();

        // 获取列名
        for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
            builder.append(resultSet.getMetaData().getColumnLabel(i));
            if (i < resultSet.getMetaData().getColumnCount()) {
                builder.append(",");
            } else {
                builder.append('\n');
            }
        }

        // 获取数据
        int columnCount = resultSet.getMetaData().getColumnCount();
        while (resultSet.next()) {
            for (int colIndex = 1; colIndex <= columnCount; colIndex++) {
                Object result = resultSet.getObject(colIndex);
                if (null == result) {
                    builder.append("\\N");
                } else {
                    builder.append(resultSet.getString(colIndex));
                }
                if (colIndex < resultSet.getMetaData().getColumnCount()) {
                    builder.append(",");
                }
            }
            builder.append('\n');
        }
        return builder.toString();
    }
}

使用数据库用户

您可以参考下面的代码示例来进行数据插入与查询，注意替换连接信息，详见上文连接信息。

import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

import java.sql.*;

public class TestMySQLConnection {

    private Connection connection;

    @BeforeClass
    private void beforeClass() throws SQLException, ClassNotFoundException {

        String host = "{HOST}";
        int port = 3306;
        String user = "{accountID_or_accountName}::{username}[::{envID}] ";
        String password = "{PASSWORD}";//数据库账号密钥
        String database = "{DATABASE_NAME}"; //数据库名称

        String url = String.format("jdbc:mysql://%s:%d/%s", host, port, database);

        Class.forName(com.mysql.cj.jdbc.Driver.class.getName());
        connection = DriverManager.getConnection(url, user, password);
    }


    @AfterClass
    public void tearDown() throws SQLException {
        // 关闭数据库连接
        if (connection != null) {
            connection.close();
        }
    }

    @Test
    public void testShowDatabases() {
        String sql = "show databases;";
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println(toCSV(resultSet));
            statement.close();
        } catch (SQLException e) {
            Assert.fail(String.format("Failed to execute: %s:%s", sql, e.getMessage()));
        }
    }

    private String toCSV(ResultSet resultSet) throws SQLException {
        StringBuilder builder = new StringBuilder();

        // 获取列名
        for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
            builder.append(resultSet.getMetaData().getColumnLabel(i));
            if (i < resultSet.getMetaData().getColumnCount()) {
                builder.append(",");
            } else {
                builder.append('\n');
            }
        }

        // 获取数据
        int columnCount = resultSet.getMetaData().getColumnCount();
        while (resultSet.next()) {
            for (int colIndex = 1; colIndex <= columnCount; colIndex++) {
                Object result = resultSet.getObject(colIndex);
                if (null == result) {
                    builder.append("\\N");
                } else {
                    builder.append(resultSet.getString(colIndex));
                }
                if (colIndex < resultSet.getMetaData().getColumnCount()) {
                    builder.append(",");
                }
            }
            builder.append('\n');
        }
        return builder.toString();
    }
}