根据JSON数据自动生成ClickHouse的Insert语句

如果你还不知道怎么建表请移步 根据JsonSchema生成阿里云ClickHouse建表语句

这里就直接贴代码了，建表的规则，字段规则都在上方链接

    private static final String SCHEMA_TYPE = "type";
    private static final String SCHEMA_TYPE_OBJECT = "object";
    private static final String SCHEMA_TYPE_ARRAY = "array";
    private static final String SCHEMA_TYPE_NULL = "null";
    private static final String SCHEMA_TYPE_INTEGER = "integer";
    private static final String SCHEMA_TYPE_NUMBER = "number";
    private static final String SCHEMA_PROPERTIES = "properties";
    private static final String SCHEMA_ITEMS = "items";
    private static final String SCHEMA_REQUIRED = "required";
    private static final Map<String, String> typeConversion = new HashMap<>(16);
    static {
        typeConversion.put("string", " String ");
        typeConversion.put("integer", " Int64 ");
        typeConversion.put("boolean", " String ");
        typeConversion.put("number", " Decimal64(8) ");
    }


    /**
     * 根据JSON数据生成clickHouse表的Insert语句
     *
     * 1、根据JSON的所有节点生成对应的节点Insert语句
     *
     * @param source 数据源
     * @param scene 数据源场景
     * @param json json数据
     * @param schema jsonSchema
     * @return SQL列表
     */
    public static List<String> json2InsertSql(String source, String scene, String json, String schema) {
        JSONObject schemaNode = JSON.parseObject(schema);
        String nodeType = schemaNode.getString(SCHEMA_TYPE);
        String rootTableName = source + "_" + scene;
        if (SCHEMA_TYPE_OBJECT.equals(nodeType)) {
            return mergeInsertSql(jsonObj2InsertSql(rootTableName, null, null, schemaNode.getJSONObject(SCHEMA_PROPERTIES), JSON.parseObject(json)));
        } else if (SCHEMA_TYPE_ARRAY.equals(nodeType)) {
            return mergeInsertSql(jsonArray2InsertSql(rootTableName, null, null, schemaNode.getJSONObject(SCHEMA_ITEMS), JSON.parseArray(json)));
        }
        throw new BusinessErrException("Unable to resolve JSONSchema！");
    }

    /**
     * 单挑插入的SQL组装为为批量插入
     * @param sqlList
     * @return
     */
    private static List<String> mergeInsertSql(List<String> sqlList) {
        List<String> result = new ArrayList<>();
        Map<String, List<String>> resultMap = new HashMap<>();
        sqlList.forEach(sql -> {
            String v = sql.substring(sql.indexOf("(", sql.indexOf(")")));
            String k = sql.substring(0, sql.indexOf("(", sql.indexOf(")")));
            List<String> values = resultMap.get(k);
            if (CollectionUtils.isEmpty(values)) {
                values = new ArrayList<>();
            }
            values.add(v);
            resultMap.put(k, values);
        });
        resultMap.forEach((filed, valueList) -> {
            StringBuilder sql = new StringBuilder(filed);
            valueList.forEach(value -> {
                sql.append(value.replace(";", ","));
            });
            String substring = sql.substring(0, sql.length() - 1);
            result.add(substring + ";");
        });
        return result;
    }


    /**
     * JSON对象数据生成 clickHouse的Insert语句
     * @param tableName 指定表名
     * @param parentIdName 父节点主键名
     * @param parentId 父节点主键ID
     * @param properties jsonSchema中的字段表
     * @param jsonData 数据
     * @return sql列表
     */
    private static List<String> jsonObj2InsertSql(String tableName, String parentIdName, String parentId, JSONObject properties, JSONObject jsonData) {
        if (properties == null) {
            return new ArrayList<>();
        }
        String tableKey = tableName + "_jzx_id";
        List<String> result = new ArrayList<>();
        String id = GuidUtils.random();
        Set<String> keySet = properties.keySet();
        for (String key : keySet) {
            JSONObject typeObj = properties.getJSONObject(key);
            String type = typeObj.getString(SCHEMA_TYPE);
            if (StringUtils.isEmpty(type)) {
                continue;
            }
            switch (type) {
                case SCHEMA_TYPE_OBJECT:
                    result.addAll(jsonObj2InsertSql(tableName + "_" + key, tableKey, id, typeObj.getJSONObject(SCHEMA_PROPERTIES), jsonData.getJSONObject(key)));
                    continue;
                case SCHEMA_TYPE_ARRAY:
                    result.addAll(jsonArray2InsertSql(tableName + "_" + key, tableKey, id, typeObj.getJSONObject(SCHEMA_ITEMS), jsonData.getJSONArray(key)));
                    continue;
                case SCHEMA_TYPE_NULL:
            }
        }
        String insertValuesSql = getInsertValuesSql(jsonData, properties, parentIdName, parentId, id);
        insertValuesSql = insertValuesSql.substring(0, insertValuesSql.length() - 1);
        StringBuilder sql = new StringBuilder("insert into ")
                .append(tableName)
                .append(getInsertFiledSql(properties, tableKey, parentIdName))
                .append(" values ")
                .append(insertValuesSql)
                .append(";");
        result.add(sql.toString());
        return result;
    }

    /**
     * JSON数组数据生成 clickHouse的Insert语句
     * @param tableName 指定表名
     * @param parentIdName 父节点主键名
     * @param parentId 父节点主键ID
     * @param items jsonSchema中的Items
     * @param jsonArrayData 数据表
     * @return Sql语句表
     */
    private static List<String> jsonArray2InsertSql(String tableName, String parentIdName, String parentId, JSONObject items, JSONArray jsonArrayData) {
        if (items == null || CollectionUtils.isEmpty(jsonArrayData)) {
            return new ArrayList<>();
        }
        List<String> result = new ArrayList<>();
        String type = items.getString(SCHEMA_TYPE);
        if (StringUtils.isEmpty(type)) {
            return new ArrayList<>();
        }
        switch (type) {
            case SCHEMA_TYPE_ARRAY:
                for (int i = 0; i < jsonArrayData.size(); i++) {
                    JSONArray jsonArray = jsonArrayData.getJSONArray(i);
                    result.addAll(jsonArray2InsertSql(tableName, parentIdName, parentId, items.getJSONObject(SCHEMA_ITEMS), jsonArray));
                }
                break;
            case SCHEMA_TYPE_OBJECT:
                for (int i = 0; i < jsonArrayData.size(); i++) {
                    JSONObject jsonObject = jsonArrayData.getJSONObject(i);
                    result.addAll(jsonObj2InsertSql(tableName, parentIdName, parentId, items.getJSONObject(SCHEMA_PROPERTIES), jsonObject));
                }
                break;
            case SCHEMA_TYPE_NULL:
        }
        return result;

    }

    /**
     * 生成insert语句的values
     * @param jsonData JSON数据
     * @param properties jsonSchema字段表
     * @param parentIdName 父节点主键名称
     * @param parentId 父节点主键ID
     * @param id 指定该节点主键ID
     * @return
     */
    private static String getInsertValuesSql(JSONObject jsonData, JSONObject properties, String parentIdName, String parentId, String id) {
        Set<String> keySet = properties.keySet();
        StringBuilder valuesSql = new StringBuilder(" ( '").append(id).append("', ");
        if (StringUtils.isNotEmpty(parentIdName)) {
            valuesSql.append("'").append(parentId).append("', ");
        }

        for (String filed : keySet) {
            JSONObject typeObj = properties.getJSONObject(filed);
            String type = typeObj.getString(SCHEMA_TYPE);
            if (StringUtils.isEmpty(type) || SCHEMA_TYPE_OBJECT.equals(type) || SCHEMA_TYPE_ARRAY.equals(type) || SCHEMA_TYPE_NULL.equals(type)) {
                continue;
            }
            if (SCHEMA_TYPE_INTEGER.equals(type) || SCHEMA_TYPE_NUMBER.equals(type)) {
                String value = jsonData.getString(filed);
                valuesSql.append(value == null ? 0 : value).append(", ");
            } else {
                String value = jsonData.getString(filed);
                valuesSql.append("'").append(value == null ? "" : value).append("'").append(", ");
            }
        }
        valuesSql.append("'").append(DateUtils.getDateStr("yyyy-MM-dd HH:mm:ss")).append("'),");

        return valuesSql.toString();
    }

    /**
     * 生成insert语句的字段表
     * @param filedList jsonSchema中的字段表
     * @param tableKey 表主键名
     * @param parentIdName 父级节点主键名
     * @return
     */
    private static String getInsertFiledSql(JSONObject filedList, String tableKey, String parentIdName) {
        Set<String> keySet = filedList.keySet();
        StringBuilder fieldSql = new StringBuilder(" ( ").append(tableKey).append(", ");
        if (StringUtils.isNotEmpty(parentIdName)) {
            fieldSql.append(parentIdName).append(", ");
        }
        for (String filed : keySet) {
            JSONObject typeObj = filedList.getJSONObject(filed);
            String type = typeObj.getString(SCHEMA_TYPE);
            if (StringUtils.isEmpty(type) || SCHEMA_TYPE_OBJECT.equals(type) || SCHEMA_TYPE_ARRAY.equals(type) || SCHEMA_TYPE_NULL.equals(type)) {
                continue;
            }
            fieldSql.append(filed).append(", ");
        }
        fieldSql.append(" jzx_create_date) ");
        return fieldSql.toString();
    }