Examples with ColumnNamePolicy org.knime.base.node.preproc.groupby.ColumnNamePolicy used on opensource projects

Example 1 with ColumnNamePolicy

use of org.knime.base.node.preproc.groupby.ColumnNamePolicy in project knime-core by knime.

the class MovingAggregationNodeModel method createTableFactory.

private MovingAggregationTableFactory createTableFactory(final FileStoreFactory fsf, final DataTableSpec spec) {
    final GlobalSettings globalSettings = new GlobalSettings(fsf, Collections.<String>emptyList(), m_maxUniqueVals.getIntValue(), m_valueDelimiter.getJavaUnescapedStringValue(), spec, 0);
    final ColumnNamePolicy colNamePolicy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    final MovingAggregationTableFactory tableFactory = new MovingAggregationTableFactory(spec, globalSettings, colNamePolicy, m_columnAggregators2Use, m_cumulativeComputing.getBooleanValue(), WindowType.getType(m_windowType.getStringValue()), m_winLength.getIntValue(), m_handleMissings.getBooleanValue(), m_removeAggregationCols.getBooleanValue(), m_removeRetainedCols.getBooleanValue());
    return tableFactory;
}

Example 2 with ColumnNamePolicy

use of org.knime.base.node.preproc.groupby.ColumnNamePolicy in project knime-core by knime.

the class CrosstabNodeModel method createGroupByTable.

/**
 * Create group-by table.
 * @param exec execution context
 * @param table input table to group
 * @param groupByCols column selected for group-by operation
 * @return table with group and aggregation columns
 * @throws CanceledExecutionException if the group-by table generation was
 *         canceled externally
 */
private final GroupByTable createGroupByTable(final ExecutionContext exec, final BufferedDataTable table, final List<String> groupByCols) throws CanceledExecutionException {
    final int maxUniqueVals = Integer.MAX_VALUE;
    final boolean enableHilite = m_settings.getEnableHiliting();
    final boolean retainOrder = false;
    final ColumnNamePolicy colNamePolicy = ColumnNamePolicy.AGGREGATION_METHOD_COLUMN_NAME;
    final GlobalSettings globalSettings = GlobalSettings.builder().setFileStoreFactory(FileStoreFactory.createWorkflowFileStoreFactory(exec)).setGroupColNames(groupByCols).setMaxUniqueValues(maxUniqueVals).setValueDelimiter(GlobalSettings.STANDARD_DELIMITER).setDataTableSpec(table.getDataTableSpec()).setNoOfRows(table.size()).setAggregationContext(AggregationContext.ROW_AGGREGATION).build();
    ColumnAggregator collAggregator = null;
    if (null != m_settings.getWeightColumn()) {
        final String weightColumn = m_settings.getWeightColumn();
        // the column aggregator for the weighting column
        final boolean inclMissing = false;
        final DataColumnSpec originalColSpec = table.getDataTableSpec().getColumnSpec(weightColumn);
        final OperatorColumnSettings opColSettings = new OperatorColumnSettings(inclMissing, originalColSpec);
        collAggregator = new ColumnAggregator(originalColSpec, new NonNegativeSumOperator(globalSettings, opColSettings), inclMissing);
    } else {
        // use any column, does not matter as long as it exists and
        // include missing is true;
        final boolean inclMissing = true;
        final DataColumnSpec originalColSpec = table.getDataTableSpec().getColumnSpec(groupByCols.get(0));
        final OperatorColumnSettings opColSettings = new OperatorColumnSettings(inclMissing, originalColSpec);
        collAggregator = new ColumnAggregator(originalColSpec, new CountOperator(globalSettings, opColSettings), inclMissing);
    }
    final GroupByTable resultTable = new BigGroupByTable(exec, table, groupByCols, new ColumnAggregator[] { collAggregator }, globalSettings, enableHilite, colNamePolicy, retainOrder);
    if (enableHilite) {
        setHiliteMapping(new DefaultHiLiteMapper(resultTable.getHiliteMapping()));
    }
    // check for skipped columns
    final String warningMsg = resultTable.getSkippedGroupsMessage(3, 3);
    if (warningMsg != null) {
        setWarningMessage(warningMsg);
    }
    return resultTable;
}

Example 3 with ColumnNamePolicy

use of org.knime.base.node.preproc.groupby.ColumnNamePolicy in project knime-core by knime.

the class DBGroupByNodeModel2 method createQuery.

/**
 * @param connection
 * @param query Query for the input table
 * @param manipulator Statement manipulator for the current database
 * @return SQL query that applies a group by to the input query
 */
private String createQuery(final DatabaseQueryConnectionSettings connection, final String query, final StatementManipulator manipulator) {
    final StringBuilder buf = new StringBuilder();
    final String[] queries = query.split(DBReader.SQL_QUERY_SEPARATOR);
    for (int i = 0; i < queries.length - 1; i++) {
        buf.append(queries[i]);
        buf.append(DBReader.SQL_QUERY_SEPARATOR);
    }
    final String selectQuery = queries[queries.length - 1];
    // Build identifier for input table
    String tableName = "table_" + System.identityHashCode(this);
    final StringBuilder columnBuf = new StringBuilder();
    final List<String> groupByCols = m_groupByCols.getIncludeList();
    // Add group by columns
    for (int i = 0; i < groupByCols.size(); i++) {
        columnBuf.append(manipulator.quoteIdentifier(groupByCols.get(i)));
        if (i + 1 < groupByCols.size() || m_aggregationFunction2Use.size() > 0 || m_addCountStar.getBooleanValue()) {
            columnBuf.append(", ");
        }
    }
    final ColumnNamePolicy columnNamePolicy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    if (m_addCountStar.getBooleanValue()) {
        columnBuf.append("COUNT(*) AS " + manipulator.quoteIdentifier(m_countStarColName.getStringValue()));
        if (!m_aggregationFunction2Use.isEmpty()) {
            columnBuf.append(", ");
        }
    }
    // Add aggregated columns
    for (int i = 0; i < m_aggregationFunction2Use.size(); i++) {
        final DBColumnAggregationFunctionRow row = m_aggregationFunction2Use.get(i);
        columnBuf.append(row.getSQLFragment(manipulator, tableName));
        columnBuf.append(" AS ");
        columnBuf.append(manipulator.quoteIdentifier(generateColumnName(columnNamePolicy, row)));
        if (i + 1 < m_aggregationFunction2Use.size()) {
            columnBuf.append(", ");
        }
    }
    // we add this hack since google big query requires the AS here but Oracle for example does not supports it
    final boolean appendAs = connection.getDriver().toLowerCase().contains("googlebigquery");
    buf.append("SELECT " + columnBuf.toString() + " FROM (" + selectQuery + ") ");
    if (appendAs) {
        buf.append("AS ");
    }
    buf.append(manipulator.quoteIdentifier(tableName));
    // build GROUP BY clause
    if (!groupByCols.isEmpty()) {
        buf.append(" GROUP BY ");
    }
    for (int i = 0; i < groupByCols.size(); i++) {
        buf.append(manipulator.quoteIdentifier(groupByCols.get(i)));
        if (i + 1 < groupByCols.size()) {
            buf.append(", ");
        }
    }
    return buf.toString();
}

Example 4 with ColumnNamePolicy

use of org.knime.base.node.preproc.groupby.ColumnNamePolicy in project knime-core by knime.

the class DBGroupByNodeModel2 method createOutSpec.

/**
 * @param inSpec Spec of the input table
 * @param manipulator
 * @param checkRetrieveMetadata
 * @return Spec of the output table
 * @throws InvalidSettingsException if settings do not match the input specification
 */
private DataTableSpec createOutSpec(final DataTableSpec inSpec, final DatabaseConnectionSettings settings, final String query, final StatementManipulator manipulator, final boolean ignoreExceptions) throws InvalidSettingsException {
    // Try get spec from database
    try {
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(settings, query);
        DBReader conn = querySettings.getUtility().getReader(querySettings);
        return conn.getDataTableSpec(getCredentialsProvider());
    } catch (SQLException e) {
        NodeLogger.getLogger(getClass()).info("Could not determine table spec from database, trying to guess now", e);
        if (!ignoreExceptions) {
            throw new InvalidSettingsException("Error in automatically build sql statement: " + e.getMessage());
        }
    // Otherwise guess spec
    }
    final List<DataColumnSpec> colSpecs = new ArrayList<>();
    // Add all group by columns
    for (String col : m_groupByCols.getIncludeList()) {
        final DataColumnSpec columnSpec = inSpec.getColumnSpec(col);
        if (columnSpec == null) {
            throw new InvalidSettingsException("Group column '" + col + "' not found in input table");
        }
        colSpecs.add(columnSpec);
    }
    if (m_addCountStar.getBooleanValue()) {
        colSpecs.add(new DataColumnSpecCreator(manipulator.getValidColumnName(m_countStarColName.getStringValue()), LongCell.TYPE).createSpec());
    }
    final ColumnNamePolicy columnNamePolicy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    // Add aggregated columns
    for (int i = 0; i < m_aggregationFunction2Use.size(); i++) {
        final DBColumnAggregationFunctionRow row = m_aggregationFunction2Use.get(i);
        final String col = row.getColumnSpec().getName();
        final String methodId = row.getFunction().getId();
        if (inSpec.getColumnSpec(col) == null) {
            throw new InvalidSettingsException("Column '" + col + "' for aggregation function " + row.getFunction().getLabel() + " does not exist");
        }
        final DatabaseUtility databaseUtility = settings.getUtility();
        final DBAggregationFunction function = databaseUtility.getAggregationFunction(methodId);
        // Get type of column after aggregation
        final DataType type = function.getType(inSpec.getColumnSpec(col).getType());
        colSpecs.add(new DataColumnSpecCreator(manipulator.getValidColumnName(generateColumnName(columnNamePolicy, row)), type).createSpec());
    }
    return new DataTableSpec(colSpecs.toArray(new DataColumnSpec[colSpecs.size()]));
}

Example 5 with ColumnNamePolicy

use of org.knime.base.node.preproc.groupby.ColumnNamePolicy in project knime-core by knime.

the class MovingAggregationNodeModel method validateSettings.

/**
 * {@inheritDoc}
 */
@Override
protected void validateSettings(final NodeSettingsRO settings) throws InvalidSettingsException {
    m_winLength.validateSettings(settings);
    m_windowType.validateSettings(settings);
    m_cumulativeComputing.validateSettings(settings);
    m_handleMissings.validateSettings(settings);
    m_removeRetainedCols.validateSettings(settings);
    m_maxUniqueVals.validateSettings(settings);
    m_valueDelimiter.validateSettings(settings);
    final List<ColumnAggregator> aggregators = ColumnAggregator.loadColumnAggregators(settings);
    final List<DataTypeAggregator> typeAggregators = new LinkedList<>();
    final List<PatternAggregator> regexAggregators = new LinkedList<>();
    try {
        regexAggregators.addAll(PatternAggregator.loadAggregators(settings, CFG_PATTERN_AGGREGATORS));
        typeAggregators.addAll(DataTypeAggregator.loadAggregators(settings, CFG_DATA_TYPE_AGGREGATORS));
    } catch (InvalidSettingsException e) {
    // introduced in 2.11
    }
    if (aggregators.isEmpty() && regexAggregators.isEmpty() && typeAggregators.isEmpty()) {
        throw new IllegalArgumentException("Please select at least one aggregation option");
    }
    final String policyLabel = ((SettingsModelString) m_columnNamePolicy.createCloneWithValidatedValue(settings)).getStringValue();
    final ColumnNamePolicy namePolicy = ColumnNamePolicy.getPolicy4Label(policyLabel);
    try {
        GroupByNodeModel.checkDuplicateAggregators(namePolicy, aggregators);
    } catch (IllegalArgumentException e) {
        throw new InvalidSettingsException(e.getMessage());
    }
    final boolean removeAggrCols = ((SettingsModelBoolean) m_removeAggregationCols.createCloneWithValidatedValue(settings)).getBooleanValue();
    if (ColumnNamePolicy.KEEP_ORIGINAL_NAME.equals(namePolicy) && !removeAggrCols) {
        throw new InvalidSettingsException("'" + ColumnNamePolicy.KEEP_ORIGINAL_NAME.getLabel() + "' option only valid if aggregation columns are filtered");
    }
}