Examples with FieldItem com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem used on opensource projects

Example 11 with FieldItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem in project canal by alibaba.

the class SqlParser method visitOnCondition.

/**
 * 解析on条件
 *
 * @param expr sql expr
 * @param tableItem 表对象
 */
private static void visitOnCondition(SQLExpr expr, TableItem tableItem) {
    if (!(expr instanceof SQLBinaryOpExpr)) {
        throw new UnsupportedOperationException();
    }
    SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) expr;
    if (sqlBinaryOpExpr.getOperator() == BooleanAnd) {
        visitOnCondition(sqlBinaryOpExpr.getLeft(), tableItem);
        visitOnCondition(sqlBinaryOpExpr.getRight(), tableItem);
    } else if (sqlBinaryOpExpr.getOperator() == Equality) {
        FieldItem leftFieldItem = new FieldItem();
        visitColumn(sqlBinaryOpExpr.getLeft(), leftFieldItem);
        if (leftFieldItem.getColumnItems().size() != 1 || leftFieldItem.isMethod() || leftFieldItem.isBinaryOp()) {
            throw new UnsupportedOperationException("Unsupported for complex of on-condition");
        }
        FieldItem rightFieldItem = new FieldItem();
        visitColumn(sqlBinaryOpExpr.getRight(), rightFieldItem);
        if (rightFieldItem.getColumnItems().size() != 1 || rightFieldItem.isMethod() || rightFieldItem.isBinaryOp()) {
            throw new UnsupportedOperationException("Unsupported for complex of on-condition");
        }
        tableItem.getRelationFields().add(new RelationFieldsPair(leftFieldItem, rightFieldItem));
    } else {
        throw new UnsupportedOperationException("Unsupported for complex of on-condition");
    }
}

Example 12 with FieldItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem in project canal by alibaba.

the class SqlParser method parse.

/**
 * 解析sql
 *
 * @param sql sql
 * @return 视图对象
 */
public static SchemaItem parse(String sql) {
    try {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();
        MySqlSelectQueryBlock sqlSelectQueryBlock = (MySqlSelectQueryBlock) statement.getSelect().getQuery();
        SchemaItem schemaItem = new SchemaItem();
        schemaItem.setSql(SQLUtils.toMySqlString(sqlSelectQueryBlock));
        SQLTableSource sqlTableSource = sqlSelectQueryBlock.getFrom();
        List<TableItem> tableItems = new ArrayList<>();
        SqlParser.visitSelectTable(schemaItem, sqlTableSource, tableItems, null);
        tableItems.forEach(tableItem -> schemaItem.getAliasTableItems().put(tableItem.getAlias(), tableItem));
        List<FieldItem> fieldItems = collectSelectQueryFields(sqlSelectQueryBlock);
        fieldItems.forEach(fieldItem -> schemaItem.getSelectFields().put(fieldItem.getFieldName(), fieldItem));
        schemaItem.init();
        if (schemaItem.getAliasTableItems().isEmpty() || schemaItem.getSelectFields().isEmpty()) {
            throw new ParserException("Parse sql error");
        }
        return schemaItem;
    } catch (Exception e) {
        throw new ParserException();
    }
}

Example 13 with FieldItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem in project canal by alibaba.

the class ES7xTemplate method putRelationData.

private void putRelationData(ESMapping mapping, SchemaItem schemaItem, Map<String, Object> dmlData, Map<String, Object> esFieldData) {
    // 添加父子文档关联信息
    if (!mapping.getRelations().isEmpty()) {
        mapping.getRelations().forEach((relationField, relationMapping) -> {
            Map<String, Object> relations = new HashMap<>();
            relations.put("name", relationMapping.getName());
            if (StringUtils.isNotEmpty(relationMapping.getParent())) {
                FieldItem parentFieldItem = schemaItem.getSelectFields().get(relationMapping.getParent());
                String columnName = parentFieldItem.getColumnItems().iterator().next().getColumnName();
                Object parentVal = getValFromData(mapping, dmlData, parentFieldItem.getFieldName(), columnName);
                if (parentVal != null) {
                    relations.put("parent", parentVal.toString());
                    esFieldData.put("$parent_routing", parentVal.toString());
                }
            }
            esFieldData.put(relationField, relations);
        });
    }
}

Example 14 with FieldItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem in project canal by alibaba.

the class ESEtlService method executeSqlImport.

protected boolean executeSqlImport(DataSource ds, String sql, List<Object> values, AdapterConfig.AdapterMapping adapterMapping, AtomicLong impCount, List<String> errMsg) {
    try {
        ESMapping mapping = (ESMapping) adapterMapping;
        Util.sqlRS(ds, sql, values, rs -> {
            int count = 0;
            try {
                ESBulkRequest esBulkRequest = this.esConnection.new ES6xBulkRequest();
                long batchBegin = System.currentTimeMillis();
                while (rs.next()) {
                    Map<String, Object> esFieldData = new LinkedHashMap<>();
                    Object idVal = null;
                    for (FieldItem fieldItem : mapping.getSchemaItem().getSelectFields().values()) {
                        String fieldName = fieldItem.getFieldName();
                        if (mapping.getSkips().contains(fieldName)) {
                            continue;
                        }
                        // 如果是主键字段则不插入
                        if (fieldItem.getFieldName().equals(mapping.get_id())) {
                            idVal = esTemplate.getValFromRS(mapping, rs, fieldName, fieldName);
                        } else {
                            Object val = esTemplate.getValFromRS(mapping, rs, fieldName, fieldName);
                            esFieldData.put(Util.cleanColumn(fieldName), val);
                        }
                    }
                    if (!mapping.getRelations().isEmpty()) {
                        mapping.getRelations().forEach((relationField, relationMapping) -> {
                            Map<String, Object> relations = new HashMap<>();
                            relations.put("name", relationMapping.getName());
                            if (StringUtils.isNotEmpty(relationMapping.getParent())) {
                                FieldItem parentFieldItem = mapping.getSchemaItem().getSelectFields().get(relationMapping.getParent());
                                Object parentVal;
                                try {
                                    parentVal = esTemplate.getValFromRS(mapping, rs, parentFieldItem.getFieldName(), parentFieldItem.getFieldName());
                                } catch (SQLException e) {
                                    throw new RuntimeException(e);
                                }
                                if (parentVal != null) {
                                    relations.put("parent", parentVal.toString());
                                    esFieldData.put("$parent_routing", parentVal.toString());
                                }
                            }
                            esFieldData.put(Util.cleanColumn(relationField), relations);
                        });
                    }
                    if (idVal != null) {
                        String parentVal = (String) esFieldData.remove("$parent_routing");
                        if (mapping.isUpsert()) {
                            ESUpdateRequest esUpdateRequest = this.esConnection.new ES6xUpdateRequest(mapping.get_index(), mapping.get_type(), idVal.toString()).setDoc(esFieldData).setDocAsUpsert(true);
                            if (StringUtils.isNotEmpty(parentVal)) {
                                esUpdateRequest.setRouting(parentVal);
                            }
                            esBulkRequest.add(esUpdateRequest);
                        } else {
                            ESIndexRequest esIndexRequest = this.esConnection.new ES6xIndexRequest(mapping.get_index(), mapping.get_type(), idVal.toString()).setSource(esFieldData);
                            if (StringUtils.isNotEmpty(parentVal)) {
                                esIndexRequest.setRouting(parentVal);
                            }
                            esBulkRequest.add(esIndexRequest);
                        }
                    } else {
                        idVal = esFieldData.get(mapping.getPk());
                        ESSearchRequest esSearchRequest = this.esConnection.new ESSearchRequest(mapping.get_index(), mapping.get_type()).setQuery(QueryBuilders.termQuery(mapping.getPk(), idVal)).size(10000);
                        SearchResponse response = esSearchRequest.getResponse();
                        for (SearchHit hit : response.getHits()) {
                            ESUpdateRequest esUpdateRequest = this.esConnection.new ES6xUpdateRequest(mapping.get_index(), mapping.get_type(), hit.getId()).setDoc(esFieldData);
                            esBulkRequest.add(esUpdateRequest);
                        }
                    }
                    if (esBulkRequest.numberOfActions() % mapping.getCommitBatch() == 0 && esBulkRequest.numberOfActions() > 0) {
                        long esBatchBegin = System.currentTimeMillis();
                        ESBulkResponse rp = esBulkRequest.bulk();
                        if (rp.hasFailures()) {
                            rp.processFailBulkResponse("全量数据 etl 异常 ");
                        }
                        if (logger.isTraceEnabled()) {
                            logger.trace("全量数据批量导入批次耗时: {}, es执行时间: {}, 批次大小: {}, index; {}", (System.currentTimeMillis() - batchBegin), (System.currentTimeMillis() - esBatchBegin), esBulkRequest.numberOfActions(), mapping.get_index());
                        }
                        batchBegin = System.currentTimeMillis();
                        esBulkRequest.resetBulk();
                    }
                    count++;
                    impCount.incrementAndGet();
                }
                if (esBulkRequest.numberOfActions() > 0) {
                    long esBatchBegin = System.currentTimeMillis();
                    ESBulkResponse rp = esBulkRequest.bulk();
                    if (rp.hasFailures()) {
                        rp.processFailBulkResponse("全量数据 etl 异常 ");
                    }
                    if (logger.isTraceEnabled()) {
                        logger.trace("全量数据批量导入最后批次耗时: {}, es执行时间: {}, 批次大小: {}, index; {}", (System.currentTimeMillis() - batchBegin), (System.currentTimeMillis() - esBatchBegin), esBulkRequest.numberOfActions(), mapping.get_index());
                    }
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                errMsg.add(mapping.get_index() + " etl failed! ==>" + e.getMessage());
                throw new RuntimeException(e);
            }
            return count;
        });
        return true;
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return false;
    }
}

Example 15 with FieldItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem in project canal by alibaba.

the class ESSyncService method subTableSimpleFieldOperation.

/**
 * 关联子查询, 主表简单字段operation
 *
 * @param config es配置
 * @param dml dml信息
 * @param data 单行dml数据
 * @param old 单行old数据
 * @param tableItem 当前表配置
 */
private void subTableSimpleFieldOperation(ESSyncConfig config, Dml dml, Map<String, Object> data, Map<String, Object> old, TableItem tableItem) {
    ESMapping mapping = config.getEsMapping();
    MySqlSelectQueryBlock queryBlock = SqlParser.parseSQLSelectQueryBlock(tableItem.getSubQuerySql());
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ").append(SqlParser.parse4SQLSelectItem(queryBlock)).append(" FROM ").append(SqlParser.parse4FromTableSource(queryBlock));
    String whereSql = SqlParser.parse4WhereItem(queryBlock);
    if (whereSql != null) {
        sql.append(" WHERE ").append(whereSql);
    } else {
        sql.append(" WHERE 1=1 ");
    }
    List<Object> values = new ArrayList<>();
    for (FieldItem fkFieldItem : tableItem.getRelationTableFields().keySet()) {
        String columnName = fkFieldItem.getColumn().getColumnName();
        Object value = esTemplate.getValFromData(mapping, data, fkFieldItem.getFieldName(), columnName);
        sql.append(" AND ").append(columnName).append("=? ");
        values.add(value);
    }
    String groupSql = SqlParser.parse4GroupBy(queryBlock);
    if (groupSql != null) {
        sql.append(groupSql);
    }
    DataSource ds = DatasourceConfig.DATA_SOURCES.get(config.getDataSourceKey());
    if (logger.isTraceEnabled()) {
        logger.trace("Join table update es index by query sql, destination:{}, table: {}, index: {}, sql: {}", config.getDestination(), dml.getTable(), mapping.get_index(), sql.toString().replace("\n", " "));
    }
    Util.sqlRS(ds, sql.toString(), values, 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.getColumn().getColumnName());
                                            esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), val);
                                            break out;
                                        }
                                    }
                            }
                        }
                    } else {
                        Object val = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getColumn().getColumnName());
                        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()) {
                        if (fieldItem.getColumnItems().size() == 1) {
                            Object value = esTemplate.getValFromRS(mapping, rs, 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 query 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;
    });
}