Kotlin中常规数据类型与ClickHouse JDBC类型映射及问题求助

一、类型映射的最佳实践 #

你的原类型映射存在两个关键问题：

1. 浮点/定点类型混淆：将float/float64映射为Decimal32(2)/Decimal64(2)是错误的——ClickHouse中Float32/Float64是原生浮点类型，适合非精确数值场景；Decimal是定点数类型，用于需要精确计算的场景，二者不能混用。
2. 覆盖范围不足：未涵盖ClickHouse JDBC支持的UInt系列、UUID、Array、Map等常用类型。

以下是符合ClickHouse最佳实践的完整类型映射表：

val dataTypes = mapOf(
    // 整数类型
    "int" to "Int32",
    "int8" to "Int8",
    "int16" to "Int16",
    "int64" to "Int64",
    "uint8" to "UInt8",
    "uint16" to "UInt16",
    "uint32" to "UInt32",
    "uint64" to "UInt64",
    // 浮点与定点数
    "float" to "Float32",
    "float64" to "Float64",
    "decimal32" to "Decimal32(2)", // 精度可按需调整
    "decimal64" to "Decimal64(4)",
    "decimal128" to "Decimal128(8)",
    // 布尔与字符串
    "boolean" to "Bool",
    "string" to "String",
    "fixed_string" to "FixedString(255)", // 需指定固定长度
    "uuid" to "UUID",
    // 日期时间
    "date" to "Date",
    "date32" to "Date32",
    "datetime" to "DateTime",
    "datetime64" to "DateTime64(3)", // 毫秒精度
    // 复杂类型示例（可按需扩展）
    "array_int" to "Array(Int32)",
    "map_string_int" to "Map(String, Int32)",
    "ipv4" to "IPv4",
    "ipv6" to "IPv6"
)

二、Increment生成器的UUID生成修复 #

你当前的逻辑存在类型不兼容问题：给Int32列设置DEFAULT generateUUIDv4()会触发ClickHouse类型错误，因为UUID是字符串类型，与整数类型不匹配。要实现每次生成唯一UUID的需求，需调整逻辑：

1. 将使用increment生成器的列类型指定为uuid（对应映射表中的UUID类型）。
2. 移除对int类型的强制限制，改为校验列类型是否为uuid。
3. 若希望插入时自动生成UUID，设置DEFAULT generateUUIDv4()即可，此时INSERT语句无需传入该列的值；若需在代码中生成，可使用java.util.UUID.randomUUID()生成字符串。

修正后的CREATE TABLE核心逻辑：

for (column in columns) {
    val dataType = dataTypes[column.dataType] ?: error("Unsupported data type: ${column.dataType}")
    writer.write("  ${column.name} $dataType")
    if (column.generator == "increment") {
        if (column.dataType != "uuid") {
            throw IllegalArgumentException("'increment' generator for UUID requires 'uuid' data type. Column '${column.name}' uses '${column.dataType}'.")
        }
        writer.write(" DEFAULT generateUUIDv4()")
        primaryKey = column.name
    }
    writer.write(",\n")
}

三、插入值的正确转换规范 #

针对不同ClickHouse类型，插入值的转换需遵循以下规则，避免类型错误或精度丢失：

• 整数/布尔：直接传入数值或布尔值（ClickHouse支持true/false作为Bool值，无需转1/0）。
• Decimal类型：建议传入字符串形式的数值（如"123.45"），避免浮点精度丢失；若使用数值类型，需确保小数位数匹配。
• 日期时间：可直接传入ISO格式字符串（如"2024-05-20"/"2024-05-20 14:30:00"），或使用ClickHouse的toDate/toDateTime函数。
• UUID：传入带引号的UUID字符串（如"'${UUID.randomUUID()}'"）。
• 复杂类型：Array需写成[val1, val2, ...]格式，Map写成{'key1': val1, 'key2': val2}格式。

修正后的INSERT值转换逻辑：

val value = when (dataTypes[column.dataType]) {
    // 整数类型
    "Int8", "Int16", "Int32", "Int64",
    "UInt8", "UInt16", "UInt32", "UInt64" -> row.fields[index]
    // 浮点类型
    "Float32", "Float64" -> row.fields[index]
    // Decimal类型（用字符串避免精度问题）
    "Decimal32(2)", "Decimal64(4)", "Decimal128(8)" -> "\"${row.fields[index]}\""
    // 布尔类型
    "Bool" -> row.fields[index].lowercase()
    // 字符串类型（转义单引号）
    "String", "FixedString(255)" -> "'${row.fields[index].replace("'", "\\'")}'"
    // UUID（自增列用DEFAULT，无需传值）
    "UUID" -> if (column.generator == "increment") "DEFAULT" else "'${row.fields[index]}'"
    // 日期时间类型
    "Date", "Date32" -> "'${row.fields[index]}'"
    "DateTime", "DateTime64(3)" -> "'${row.fields[index]}'"
    // 复杂类型示例
    "Array(Int32)" -> "[${row.fields[index].split(",").joinToString(", ")}]"
    "Map(String, Int32)" -> "{${row.fields[index].split(",").joinToString(", ") { entry ->
        val (k, v) = entry.split(":")
        "'$k': $v"
    }}}"
    else -> error("Unknown data type: ${column.dataType}")
}

四、完整优化后的函数代码 #

override fun getGeneratedFileClickhouse(file: File, rows: Int, columns: List<ColumnSettings>, tableName: String): InputStreamResource {
    val dummyTable = getGeneratedList(rows, columns)
    val columnNames = columns.joinToString(", ") { it.name }
    val dataTypes = mapOf(
        // 整数类型
        "int" to "Int32",
        "int8" to "Int8",
        "int16" to "Int16",
        "int64" to "Int64",
        "uint8" to "UInt8",
        "uint16" to "UInt16",
        "uint32" to "UInt32",
        "uint64" to "UInt64",
        // 浮点与定点数
        "float" to "Float32",
        "float64" to "Float64",
        "decimal32" to "Decimal32(2)",
        "decimal64" to "Decimal64(4)",
        "decimal128" to "Decimal128(8)",
        // 布尔与字符串
        "boolean" to "Bool",
        "string" to "String",
        "fixed_string" to "FixedString(255)",
        "uuid" to "UUID",
        // 日期时间
        "date" to "Date",
        "date32" to "Date32",
        "datetime" to "DateTime",
        "datetime64" to "DateTime64(3)",
        // 复杂类型示例
        "array_int" to "Array(Int32)",
        "map_string_int" to "Map(String, Int32)",
        "ipv4" to "IPv4",
        "ipv6" to "IPv6"
    )

    try {
        FileWriter(file).use { writer -> // 使用use自动关闭资源，避免内存泄漏
            // 生成CREATE TABLE语句
            writer.write("CREATE TABLE $tableName (\n")
            var primaryKey = ""
            for (column in columns) {
                val dataType = dataTypes[column.dataType] ?: error("Unsupported data type: ${column.dataType}")
                writer.write("  ${column.name} $dataType")
                if (column.generator == "increment") {
                    if (column.dataType != "uuid") {
                        throw IllegalArgumentException("'increment' generator for UUID requires 'uuid' data type. Column '${column.name}' uses '${column.dataType}'.")
                    }
                    writer.write(" DEFAULT generateUUIDv4()")
                    primaryKey = column.name
                }
                writer.write(",\n")
            }

            // 处理主键与MergeTree引擎（ClickHouse要求MergeTree必须有ORDER BY）
            if (primaryKey.isNotEmpty()) {
                writer.write("  PRIMARY KEY ($primaryKey)\n")
                writer.write(") ENGINE = MergeTree ORDER BY $primaryKey;\n\n")
            } else {
                writer.write(") ENGINE = MergeTree() ORDER BY tuple();\n\n")
            }

            // 生成INSERT INTO语句（过滤自增UUID列，无需传入值）
            for ((indexRow, row) in dummyTable.withIndex()) {
                if (indexRow == 0) continue // 跳过表头

                val insertColumns = columns.filter { it.generator != "increment" }
                val insertColumnNames = insertColumns.joinToString(", ") { it.name }
                val insertValues = insertColumns.mapIndexed { idx, column ->
                    val originalIndex = columns.indexOf(column)
                    val fieldValue = row.fields[originalIndex]
                    when (dataTypes[column.dataType]) {
                        // 整数
                        "Int8", "Int16", "Int32", "Int64",
                        "UInt8", "UInt16", "UInt32", "UInt64" -> fieldValue
                        // 浮点
                        "Float32", "Float64" -> fieldValue
                        // Decimal
                        "Decimal32(2)", "Decimal64(4)", "Decimal128(8)" -> "\"$fieldValue\""
                        // 布尔
                        "Bool" -> fieldValue.lowercase()
                        // 字符串
                        "String", "FixedString(255)" -> "'${fieldValue.replace("'", "\\'")}'"
                        // 日期时间
                        "Date", "Date32" -> "'$fieldValue'"
                        "DateTime", "DateTime64(3)" -> "'$fieldValue'"
                        // UUID（非自增）
                        "UUID" -> "'$fieldValue'"
                        // 复杂类型
                        "Array(Int32)" -> "[${fieldValue.split(",").joinToString(", ")}]"
                        "Map(String, Int32)" -> "{${fieldValue.split(",").joinToString(", ") { entry ->
                            val (k, v) = entry.split(":")
                            "'$k': $v"
                        }}}"
                        else -> error("Unknown data type: ${column.dataType}")
                    }
                }.joinToString(", ")

                writer.write("INSERT INTO $tableName ($insertColumnNames) VALUES ($insertValues);\n")
            }
        }
    } catch (e: IOException) {
        throw RuntimeException("Failed to generate ClickHouse file", e) // 抛出异常让上层处理，而非仅打印
    }

    return InputStreamResource(FileInputStream(file))
}

内容的提问来源于stack exchange，提问作者chumbo79