Search in sources :

Example 1 with ESMapping

use of com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping in project canal by alibaba.

the class ESSyncService method wholeSqlOperation.

/**
 * 关联(子查询), 主表复杂字段operation, 全sql执行
 *
 * @param config es配置
 * @param dml dml信息
 * @param data 单行dml数据
 * @param tableItem 当前表配置
 */
private void wholeSqlOperation(ESSyncConfig config, Dml dml, Map<String, Object> data, Map<String, Object> old, TableItem tableItem) {
    ESMapping mapping = config.getEsMapping();
    // 防止最后出现groupby 导致sql解析异常
    String[] sqlSplit = mapping.getSql().split("GROUP\\ BY(?!(.*)ON)");
    String sqlNoWhere = sqlSplit[0];
    String sqlGroupBy = "";
    if (sqlSplit.length > 1) {
        sqlGroupBy = "GROUP BY " + sqlSplit[1];
    }
    StringBuilder sql = new StringBuilder(sqlNoWhere + " WHERE ");
    for (FieldItem fkFieldItem : tableItem.getRelationTableFields().keySet()) {
        String columnName = fkFieldItem.getColumn().getColumnName();
        Object value = esTemplate.getValFromData(mapping, data, fkFieldItem.getFieldName(), columnName);
        ESSyncUtil.appendCondition(sql, value, tableItem.getAlias(), columnName);
    }
    int len = sql.length();
    sql.delete(len - 5, len);
    sql.append(sqlGroupBy);
    DataSource ds = DatasourceConfig.DATA_SOURCES.get(config.getDataSourceKey());
    if (logger.isTraceEnabled()) {
        logger.trace("Join table update es index by query whole sql, destination:{}, table: {}, index: {}, sql: {}", config.getDestination(), dml.getTable(), mapping.get_index(), sql.toString().replace("\n", " "));
    }
    Util.sqlRS(ds, sql.toString(), rs -> {
        try {
            while (rs.next()) {
                Map<String, Object> esFieldData = new LinkedHashMap<>();
                for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                    if (old != null) {
                        // 从表子查询
                        out: for (FieldItem fieldItem1 : tableItem.getSubQueryFields()) {
                            for (ColumnItem columnItem0 : fieldItem.getColumnItems()) {
                                if (fieldItem1.getFieldName().equals(columnItem0.getColumnName()))
                                    for (ColumnItem columnItem : fieldItem1.getColumnItems()) {
                                        if (old.containsKey(columnItem.getColumnName())) {
                                            Object val = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getFieldName());
                                            esFieldData.put(fieldItem.getFieldName(), val);
                                            break out;
                                        }
                                    }
                            }
                        }
                        // 从表非子查询
                        for (FieldItem fieldItem1 : tableItem.getRelationSelectFieldItems()) {
                            if (fieldItem1.equals(fieldItem)) {
                                for (ColumnItem columnItem : fieldItem1.getColumnItems()) {
                                    if (old.containsKey(columnItem.getColumnName())) {
                                        Object val = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getFieldName());
                                        esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), val);
                                        break;
                                    }
                                }
                            }
                        }
                    } else {
                        Object val = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getFieldName());
                        esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), val);
                    }
                }
                Map<String, Object> paramsTmp = new LinkedHashMap<>();
                for (Map.Entry<FieldItem, List<FieldItem>> entry : tableItem.getRelationTableFields().entrySet()) {
                    for (FieldItem fieldItem : entry.getValue()) {
                        Object value = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getFieldName());
                        String fieldName = fieldItem.getFieldName();
                        // 判断是否是主键
                        if (fieldName.equals(mapping.get_id())) {
                            fieldName = "_id";
                        }
                        paramsTmp.put(fieldName, value);
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.trace("Join table update es index by query whole sql, destination:{}, table: {}, index: {}", config.getDestination(), dml.getTable(), mapping.get_index());
                }
                esTemplate.updateByQuery(config, paramsTmp, esFieldData);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return 0;
    });
}
Also used : ColumnItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.ColumnItem) DataSource(javax.sql.DataSource) LinkedHashMap(java.util.LinkedHashMap) ESMapping(com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping) ArrayList(java.util.ArrayList) List(java.util.List) FieldItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem) LinkedHashMap(java.util.LinkedHashMap) Map(java.util.Map)

Example 2 with ESMapping

use of com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping in project canal by alibaba.

the class ESSyncService method singleTableSimpleFiledUpdate.

/**
 * 单表简单字段update
 *
 * @param config es配置
 * @param dml dml信息
 * @param data 单行data数据
 * @param old 单行old数据
 */
private void singleTableSimpleFiledUpdate(ESSyncConfig config, Dml dml, Map<String, Object> data, Map<String, Object> old) {
    ESMapping mapping = config.getEsMapping();
    Map<String, Object> esFieldData = new LinkedHashMap<>();
    Object idVal = esTemplate.getESDataFromDmlData(mapping, data, old, esFieldData);
    if (logger.isTraceEnabled()) {
        logger.trace("Main table update to es index, destination:{}, table: {}, index: {}, id: {}", config.getDestination(), dml.getTable(), mapping.get_index(), idVal);
    }
    esTemplate.update(mapping, idVal, esFieldData);
}
Also used : ESMapping(com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping) LinkedHashMap(java.util.LinkedHashMap)

Example 3 with ESMapping

use of com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping in project canal by alibaba.

the class ESSyncService method joinTableSimpleFieldOperation.

/**
 * 关联表主表简单字段operation
 *
 * @param config es配置
 * @param dml dml信息
 * @param data 单行dml数据
 * @param tableItem 当前表配置
 */
private void joinTableSimpleFieldOperation(ESSyncConfig config, Dml dml, Map<String, Object> data, TableItem tableItem, Map<String, Object> esFieldData) {
    ESMapping mapping = config.getEsMapping();
    Map<String, Object> paramsTmp = new LinkedHashMap<>();
    for (Map.Entry<FieldItem, List<FieldItem>> entry : tableItem.getRelationTableFields().entrySet()) {
        for (FieldItem fieldItem : entry.getValue()) {
            if (fieldItem.getColumnItems().size() == 1) {
                Object value = esTemplate.getValFromData(mapping, data, fieldItem.getFieldName(), entry.getKey().getColumn().getColumnName());
                String fieldName = fieldItem.getFieldName();
                // 判断是否是主键
                if (fieldName.equals(mapping.get_id())) {
                    fieldName = "_id";
                }
                paramsTmp.put(fieldName, value);
            }
        }
    }
    if (logger.isDebugEnabled()) {
        logger.trace("Join table update es index by foreign key, destination:{}, table: {}, index: {}", config.getDestination(), dml.getTable(), mapping.get_index());
    }
    esTemplate.updateByQuery(config, paramsTmp, esFieldData);
}
Also used : ESMapping(com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping) ArrayList(java.util.ArrayList) List(java.util.List) FieldItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem) LinkedHashMap(java.util.LinkedHashMap) Map(java.util.Map) LinkedHashMap(java.util.LinkedHashMap)

Example 4 with ESMapping

use of com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping in project canal by alibaba.

the class ESSyncService method update.

/**
 * 更新操作dml
 *
 * @param config es配置
 * @param dml dml数据
 */
private void update(ESSyncConfig config, Dml dml) {
    List<Map<String, Object>> dataList = dml.getData();
    List<Map<String, Object>> oldList = dml.getOld();
    if (dataList == null || dataList.isEmpty() || oldList == null || oldList.isEmpty()) {
        return;
    }
    SchemaItem schemaItem = config.getEsMapping().getSchemaItem();
    int i = 0;
    for (Map<String, Object> data : dataList) {
        Map<String, Object> old = oldList.get(i);
        if (data == null || data.isEmpty() || old == null || old.isEmpty()) {
            continue;
        }
        if (schemaItem.getAliasTableItems().size() == 1 && schemaItem.isAllFieldsSimple()) {
            // ------单表 & 所有字段都为简单字段------
            singleTableSimpleFiledUpdate(config, dml, data, old);
        } else {
            // ------主表 查询sql来更新------
            if (schemaItem.getMainTable().getTableName().equalsIgnoreCase(dml.getTable())) {
                ESMapping mapping = config.getEsMapping();
                String idFieldName = mapping.get_id() == null ? mapping.getPk() : mapping.get_id();
                FieldItem idFieldItem = schemaItem.getSelectFields().get(idFieldName);
                boolean idFieldSimple = true;
                if (idFieldItem.isMethod() || idFieldItem.isBinaryOp()) {
                    idFieldSimple = false;
                }
                boolean allUpdateFieldSimple = true;
                out: for (FieldItem fieldItem : schemaItem.getSelectFields().values()) {
                    for (ColumnItem columnItem : fieldItem.getColumnItems()) {
                        if (old.containsKey(columnItem.getColumnName())) {
                            if (fieldItem.isMethod() || fieldItem.isBinaryOp()) {
                                allUpdateFieldSimple = false;
                                break out;
                            }
                        }
                    }
                }
                // 不支持主键更新!!
                // 判断是否有外键更新
                boolean fkChanged = false;
                for (TableItem tableItem : schemaItem.getAliasTableItems().values()) {
                    if (tableItem.isMain()) {
                        continue;
                    }
                    boolean changed = false;
                    for (List<FieldItem> fieldItems : tableItem.getRelationTableFields().values()) {
                        for (FieldItem fieldItem : fieldItems) {
                            if (old.containsKey(fieldItem.getColumn().getColumnName())) {
                                fkChanged = true;
                                changed = true;
                                break;
                            }
                        }
                    }
                    // 如果外键有修改,则更新所对应该表的所有查询条件数据
                    if (changed) {
                        for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                            fieldItem.getColumnItems().forEach(columnItem -> old.put(columnItem.getColumnName(), null));
                        }
                    }
                }
                // 判断主键和所更新的字段是否全为简单字段
                if (idFieldSimple && allUpdateFieldSimple && !fkChanged) {
                    singleTableSimpleFiledUpdate(config, dml, data, old);
                } else {
                    mainTableUpdate(config, dml, data, old);
                }
            }
            // 从表的操作
            for (TableItem tableItem : schemaItem.getAliasTableItems().values()) {
                if (tableItem.isMain()) {
                    continue;
                }
                if (!tableItem.getTableName().equals(dml.getTable())) {
                    continue;
                }
                // 关联条件出现在主表查询条件是否为简单字段
                boolean allFieldsSimple = true;
                for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                    if (fieldItem.isMethod() || fieldItem.isBinaryOp()) {
                        allFieldsSimple = false;
                        break;
                    }
                }
                // 所有查询字段均为简单字段
                if (allFieldsSimple) {
                    // 不是子查询
                    if (!tableItem.isSubQuery()) {
                        // ------关联表简单字段更新------
                        Map<String, Object> esFieldData = new LinkedHashMap<>();
                        for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                            if (old.containsKey(fieldItem.getColumn().getColumnName())) {
                                Object value = esTemplate.getValFromData(config.getEsMapping(), data, fieldItem.getFieldName(), fieldItem.getColumn().getColumnName());
                                esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), value);
                            }
                        }
                        joinTableSimpleFieldOperation(config, dml, data, tableItem, esFieldData);
                    } else {
                        // ------关联子表简单字段更新------
                        subTableSimpleFieldOperation(config, dml, data, old, tableItem);
                    }
                } else {
                    // ------关联子表复杂字段更新 执行全sql更新es------
                    wholeSqlOperation(config, dml, data, old, tableItem);
                }
            }
        }
        i++;
    }
}
Also used : SchemaItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem) ColumnItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.ColumnItem) TableItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem) LinkedHashMap(java.util.LinkedHashMap) ESMapping(com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping) FieldItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem) LinkedHashMap(java.util.LinkedHashMap) Map(java.util.Map)

Example 5 with ESMapping

use of com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping in project canal by alibaba.

the class ESEtlService method importData.

public EtlResult importData(List<String> params) {
    ESMapping mapping = config.getEsMapping();
    logger.info("start etl to import data to index: {}", mapping.get_index());
    String sql = mapping.getSql();
    return importData(sql, params);
}
Also used : ESMapping(com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping)

Aggregations

ESMapping (com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping)16 LinkedHashMap (java.util.LinkedHashMap)13 FieldItem (com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem)9 Map (java.util.Map)7 DataSource (javax.sql.DataSource)7 ColumnItem (com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.ColumnItem)5 ArrayList (java.util.ArrayList)5 List (java.util.List)5 SchemaItem (com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem)4 ESBulkRequest (com.alibaba.otter.canal.client.adapter.es.core.support.ESBulkRequest)4 ESBulkResponse (com.alibaba.otter.canal.client.adapter.es.core.support.ESBulkRequest.ESBulkResponse)4 ESIndexRequest (com.alibaba.otter.canal.client.adapter.es.core.support.ESBulkRequest.ESIndexRequest)4 ESUpdateRequest (com.alibaba.otter.canal.client.adapter.es.core.support.ESBulkRequest.ESUpdateRequest)4 SQLException (java.sql.SQLException)4 HashMap (java.util.HashMap)4 SearchResponse (org.elasticsearch.action.search.SearchResponse)4 SearchHit (org.elasticsearch.search.SearchHit)4 ESSyncConfig (com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig)2 TableItem (com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem)2 ESDeleteRequest (com.alibaba.otter.canal.client.adapter.es.core.support.ESBulkRequest.ESDeleteRequest)2