字节云Bytehouse的使用

一，java连接Bytehouse并进行增删改查

maven：

<dependency>
    <groupId>com.bytedance.bytehouse</groupId>
    <artifactId>driver-java</artifactId>
    <version>1.1.24</version>
</dependency>

代码：

import com.bytedance.bytehouse.jdbc.ByteHouseDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Properties;

public class SimpleQuery {
    public static void main(String[] args) throws Exception {
        String url = String.format("jdbc:bytehouse://bytehouse-cn-beijing.volces.com:19000/?user=bytehouse&password=XXXX");
        Properties properties = new Properties();
        properties.setProperty("secure", "true");
        properties.setProperty("vw", "bigdata");

        DataSource dataSource = new ByteHouseDataSource(url, properties);

        try (Connection connection = dataSource.getConnection()) {
//            createDatabase(connection);
//            createTable(connection);
//            insertTable(connection);
//            insertBatch(connection);
            selectTable(connection);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void createDatabase(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("CREATE DATABASE IF NOT EXISTS inventory");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void createTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(
                    "CREATE TABLE IF NOT EXISTS inventory.orders\n" +
                            "(" +
                            "    OrderID String," +
                            "    OrderName String," +
                            "    OrderPriority Int8" +
                            ")" +
                            "    engine = CnchMergeTree()" +
                            "    partition by OrderID" +
                            "    order by OrderID"
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            stmt.executeUpdate(
                    "INSERT INTO inventory.orders VALUES ('54895','Apple',12)"
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertBatch(Connection connection) {
        String insertQuery = "INSERT INTO inventory.orders (OrderID, OrderName, OrderPriority) VALUES (?,'Apple',?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertQuery)) {
            int insertBatchSize = 10;

            for (int i = 0; i < insertBatchSize; i++) {
                pstmt.setString(1, "ID" + i);
                pstmt.setInt(2, i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void selectTable(Connection connection) {
        String selectQuery = "SELECT * FROM inventory.orders";
        try (Statement stmt = connection.createStatement()) {
            String selectSql = "SELECT * FROM inventory.orders";
            ResultSet rs = stmt.executeQuery(selectSql);
            while (rs.next()) {
                String OrderID = rs.getString("OrderID");
                System.out.println("OrderID: "+OrderID);


            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

二，api方式操作Bytehouse

package com;

import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import com.alibaba.fastjson.JSONObject;

public class BytehouseTest {
    public static void main(String[] args) {
        // 设置POST请求的URL
        String url = "https://bytemart-cn-beijing.bytehouse.volcbiz.com/v1/sql/sync";

        // 构建POST请求参数
        String day="20231107";
        String sql_list ="";


        JSONObject jsonObject = new JSONObject();
        jsonObject.put("query",sql_list);
        jsonObject.put("mode","ansi_sql");
        jsonObject.put("vw","bigdata");
        jsonObject.put("secure","true");
        // 发送POST请求
        long start = System.currentTimeMillis();
        HttpResponse response = HttpRequest.post(url)
                .header("Accept","application/json")
                .header("Authorization","Bearer ")
                .body(jsonObject.toString())
                .execute();

        // 获取响应结果
        String result = response.body();
        System.out.println("Response: " + result);
        long end = System.currentTimeMillis();
        System.out.println(end-start);

        
    }
}

三，python操作Bytehouse

def add_to_bytehouse_dict(dict):
    HOST="bytehouse-cn-beijing.volces.com"
    PORT="19000"
    API_KEY=""
    # 配置数据库连接信息
    DATABASE="dwd"
    client = Client.from_url('bytehouse://{}:{}/?user=bytehouse&password={}&database={}&secure=true'.format(HOST, PORT, API_KEY, DATABASE))
    client.execute("INSERT INTO ods.ods_trigger_sc  VALUES", [
        [dict['vehicle_id'], dict['scid'], dict['start_time'], dict['path'], dict['plat'], dict['project'],dict['day'],dict['isdelete']
            , dict['pt_date'], dict['pt_hour'],dict['pt_vehicleseries'],dict['pt_plat']
        ]])
    return