Examples with DoubleValue org.knime.core.data.DoubleValue used on opensource projects

Example 21 with DoubleValue

use of org.knime.core.data.DoubleValue in project knime-core by knime.

the class DBWriterImpl method writeData.

/**
 * {@inheritDoc}
 * @deprecated
 */
@Deprecated
@Override
public String writeData(final String table, final RowInput input, final long rowCount, final boolean appendData, final ExecutionMonitor exec, final Map<String, String> sqlTypes, final CredentialsProvider cp, final int batchSize, final boolean insertNullForMissingCols, final boolean failOnError) throws Exception {
    final DatabaseConnectionSettings conSettings = getDatabaseConnectionSettings();
    // final Connection conn = conSettings.createConnection(cp);
    return conSettings.execute(cp, conn -> {
        exec.setMessage("Waiting for free database connection...");
        final StringBuilder columnNamesForInsertStatement = new StringBuilder("(");
        // synchronized (conSettings.syncConnection(conn)) {
        exec.setMessage("Start writing rows in database...");
        DataTableSpec spec = input.getDataTableSpec();
        // mapping from spec columns to database columns
        final int[] mapping;
        // append data to existing table
        if (appendData) {
            if (conSettings.getUtility().tableExists(conn, table)) {
                String query = conSettings.getUtility().getStatementManipulator().forMetadataOnly("SELECT * FROM " + table);
                try (ResultSet rs = conn.createStatement().executeQuery(query)) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    final Map<String, Integer> columnNames = new LinkedHashMap<String, Integer>();
                    for (int i = 0; i < spec.getNumColumns(); i++) {
                        String colName = replaceColumnName(spec.getColumnSpec(i).getName());
                        columnNames.put(colName.toLowerCase(), i);
                    }
                    // sanity check to lock if all input columns are in db
                    ArrayList<String> columnNotInSpec = new ArrayList<String>(columnNames.keySet());
                    for (int i = 0; i < rsmd.getColumnCount(); i++) {
                        String dbColName = replaceColumnName(rsmd.getColumnName(i + 1));
                        if (columnNames.containsKey(dbColName.toLowerCase())) {
                            columnNotInSpec.remove(dbColName.toLowerCase());
                            columnNamesForInsertStatement.append(dbColName).append(',');
                        } else if (insertNullForMissingCols) {
                            // append the column name of a missing column only if the insert null for missing
                            // column option is enabled
                            columnNamesForInsertStatement.append(dbColName).append(',');
                        }
                    }
                    if (rsmd.getColumnCount() > 0) {
                        columnNamesForInsertStatement.deleteCharAt(columnNamesForInsertStatement.length() - 1);
                    }
                    columnNamesForInsertStatement.append(')');
                    if (columnNotInSpec.size() > 0) {
                        throw new RuntimeException("No. of columns in input" + " table > in database; not existing columns: " + columnNotInSpec.toString());
                    }
                    mapping = new int[rsmd.getColumnCount()];
                    for (int i = 0; i < mapping.length; i++) {
                        String name = replaceColumnName(rsmd.getColumnName(i + 1)).toLowerCase();
                        if (!columnNames.containsKey(name)) {
                            mapping[i] = -1;
                            continue;
                        }
                        mapping[i] = columnNames.get(name);
                        DataColumnSpec cspec = spec.getColumnSpec(mapping[i]);
                        int type = rsmd.getColumnType(i + 1);
                        switch(type) {
                            // check all boolean compatible types
                            case Types.BIT:
                            case Types.BOOLEAN:
                                // types must be compatible to BooleanValue
                                if (!cspec.getType().isCompatible(BooleanValue.class)) {
                                    throw new RuntimeException("Column \"" + name + "\" of type \"" + cspec.getType() + "\" from input does not match type " + "\"" + rsmd.getColumnTypeName(i + 1) + "\" in database at position " + i);
                                }
                                break;
                            // check all int compatible types
                            case Types.TINYINT:
                            case Types.SMALLINT:
                            case Types.INTEGER:
                                // types must be compatible to IntValue
                                if (!cspec.getType().isCompatible(IntValue.class)) {
                                    throw new RuntimeException("Column \"" + name + "\" of type \"" + cspec.getType() + "\" from input does not match type " + "\"" + rsmd.getColumnTypeName(i + 1) + "\" in database at position " + i);
                                }
                                break;
                            case Types.BIGINT:
                                // types must also be compatible to LongValue
                                if (!cspec.getType().isCompatible(LongValue.class)) {
                                    throw new RuntimeException("Column \"" + name + "\" of type \"" + cspec.getType() + "\" from input does not match type " + "\"" + rsmd.getColumnTypeName(i + 1) + "\" in database at position " + i);
                                }
                                break;
                            // check all double compatible types
                            case Types.FLOAT:
                            case Types.DOUBLE:
                            case Types.NUMERIC:
                            case Types.DECIMAL:
                            case Types.REAL:
                                // types must also be compatible to DoubleValue
                                if (!cspec.getType().isCompatible(DoubleValue.class)) {
                                    throw new RuntimeException("Column \"" + name + "\" of type \"" + cspec.getType() + "\" from input does not match type " + "\"" + rsmd.getColumnTypeName(i + 1) + "\" in database at position " + i);
                                }
                                break;
                            // check for date-and-time compatible types
                            case Types.DATE:
                            case Types.TIME:
                            case Types.TIMESTAMP:
                                // types must also be compatible to DataValue
                                if (!cspec.getType().isCompatible(DateAndTimeValue.class)) {
                                    throw new RuntimeException("Column \"" + name + "\" of type \"" + cspec.getType() + "\" from input does not match type " + "\"" + rsmd.getColumnTypeName(i + 1) + "\" in database at position " + i);
                                }
                                break;
                            // check for blob compatible types
                            case Types.BLOB:
                            case Types.BINARY:
                            case Types.LONGVARBINARY:
                                // types must also be compatible to DataValue
                                if (!cspec.getType().isCompatible(BinaryObjectDataValue.class)) {
                                    throw new RuntimeException("Column \"" + name + "\" of type \"" + cspec.getType() + "\" from input does not match type " + "\"" + rsmd.getColumnTypeName(i + 1) + "\" in database at position " + i);
                                }
                                break;
                        }
                    }
                }
            } else {
                LOGGER.info("Table \"" + table + "\" does not exist in database, " + "will create new table.");
                // and create new table
                final String query = "CREATE TABLE " + table + " " + createTableStmt(spec, sqlTypes, columnNamesForInsertStatement);
                LOGGER.debug("Executing SQL statement as execute: " + query);
                try (Statement statement = conn.createStatement()) {
                    statement.execute(query);
                }
                if (!conn.getAutoCommit()) {
                    conn.commit();
                }
                mapping = new int[spec.getNumColumns()];
                for (int k = 0; k < mapping.length; k++) {
                    mapping[k] = k;
                }
            }
        } else {
            LOGGER.debug("Append not enabled. Table " + table + " will be dropped if exists.");
            mapping = new int[spec.getNumColumns()];
            for (int k = 0; k < mapping.length; k++) {
                mapping[k] = k;
            }
            Statement statement = null;
            try {
                statement = conn.createStatement();
                // remove existing table (if any)
                final String query = "DROP TABLE " + table;
                LOGGER.debug("Executing SQL statement as execute: " + query);
                statement.execute(query);
            } catch (Throwable t) {
                if (statement == null) {
                    throw new SQLException("Could not create SQL statement," + " reason: " + t.getMessage(), t);
                }
                LOGGER.info("Exception droping table \"" + table + "\": " + t.getMessage() + ". Will create new table.");
            } finally {
                if (!conn.getAutoCommit()) {
                    conn.commit();
                }
            }
            // and create new table
            final String query = "CREATE TABLE " + table + " " + createTableStmt(spec, sqlTypes, columnNamesForInsertStatement);
            LOGGER.debug("Executing SQL statement as execute: " + query);
            statement.execute(query);
            statement.close();
            if (!conn.getAutoCommit()) {
                conn.commit();
            }
        }
        // this is a (temporary) workaround for bug #5802: if there is a DataValue column in the input table
        // we need to use the SQL type for creating the insert statements.
        Map<Integer, Integer> columnTypes = null;
        for (DataColumnSpec cs : spec) {
            if (cs.getType().getPreferredValueClass() == DataValue.class) {
                columnTypes = getColumnTypes(conn, table);
                break;
            }
        }
        final String insertStamtement = createInsertStatment(table, columnNamesForInsertStatement.toString(), mapping, insertNullForMissingCols);
        // problems writing more than 13 columns. the prepare statement
        // ensures that we can set the columns directly row-by-row, the
        // database will handle the commit
        long cnt = 1;
        long errorCnt = 0;
        long allErrors = 0;
        // count number of rows added to current batch
        int curBatchSize = 0;
        LOGGER.debug("Executing SQL statement as prepareStatement: " + insertStamtement);
        final PreparedStatement stmt = conn.prepareStatement(insertStamtement);
        // remember auto-commit flag
        final boolean autoCommit = conn.getAutoCommit();
        DatabaseConnectionSettings.setAutoCommit(conn, false);
        try {
            final TimeZone timezone = conSettings.getTimeZone();
            // get the first row
            DataRow row;
            DataRow nextRow = input.poll();
            // iterate over all incoming data rows
            while (nextRow != null) {
                row = nextRow;
                cnt++;
                exec.checkCanceled();
                if (rowCount > 0) {
                    exec.setProgress(1.0 * cnt / rowCount, "Row " + "#" + cnt);
                } else {
                    exec.setProgress("Writing Row#" + cnt);
                }
                int dbIdx = 1;
                for (int i = 0; i < mapping.length; i++) {
                    if (mapping[i] < 0) {
                        if (insertNullForMissingCols) {
                            // insert only null if the insert null for missing col option is enabled
                            stmt.setNull(dbIdx++, Types.NULL);
                        }
                    } else {
                        final DataColumnSpec cspec = spec.getColumnSpec(mapping[i]);
                        final DataCell cell = row.getCell(mapping[i]);
                        fillStatement(stmt, dbIdx++, cspec, cell, timezone, columnTypes);
                    }
                }
                // if batch mode
                if (batchSize > 1) {
                    // a new row will be added
                    stmt.addBatch();
                }
                // get one more input row to check if 'row' is the last one
                nextRow = input.poll();
                curBatchSize++;
                // if batch size equals number of row in batch or input table at end
                if ((curBatchSize == batchSize) || nextRow == null) {
                    curBatchSize = 0;
                    try {
                        // write batch
                        if (batchSize > 1) {
                            stmt.executeBatch();
                        } else {
                            // or write single row
                            stmt.execute();
                        }
                    } catch (Throwable t) {
                        final String errorMsg;
                        if (batchSize > 1) {
                            errorMsg = "Error while adding rows #" + (cnt - batchSize) + " - #" + cnt + ", reason: " + t.getMessage();
                        } else {
                            errorMsg = "Error while adding row #" + cnt + " (" + row.getKey() + "), reason: " + t.getMessage();
                        }
                        // introduced in KNIME 3.3.2
                        if (failOnError) {
                            try {
                                // rollback all changes
                                conn.rollback();
                                LOGGER.debug("Rollback complete transaction with auto commit=" + autoCommit);
                            } catch (Throwable ex) {
                                LOGGER.info("Failed rollback after db exception with auto commit=" + autoCommit + ". Rollback error: " + ex.getMessage(), ex);
                            }
                            throw new Exception(errorMsg, t);
                        }
                        // anyway.
                        if (!conn.getAutoCommit()) {
                            conn.commit();
                        }
                        allErrors++;
                        if (errorCnt > -1) {
                            exec.setMessage(errorMsg);
                            if (errorCnt++ < 10) {
                                LOGGER.warn(errorMsg);
                            } else {
                                errorCnt = -1;
                                LOGGER.warn(errorMsg + " - more errors...", t);
                            }
                        }
                    } finally {
                        // clear batch if in batch mode
                        if (batchSize > 1) {
                            stmt.clearBatch();
                        }
                    }
                }
            }
            if (!conn.getAutoCommit()) {
                conn.commit();
            }
            if (allErrors == 0) {
                return null;
            } else {
                return "Errors \"" + allErrors + "\" writing " + (cnt - 1) + " rows.";
            }
        } finally {
            DatabaseConnectionSettings.setAutoCommit(conn, autoCommit);
            stmt.close();
        }
    });
}

Example 22 with DoubleValue

use of org.knime.core.data.DoubleValue in project knime-core by knime.

the class DoubleBarRenderer method setValue.

/**
 *     /** Sets the value according to the column domain's min/max. If the
 * object is not instance of DoubleValue, the cell is painted red.
 * @param value The value to be rendered.
 * @see javax.swing.table.DefaultTableCellRenderer#setValue(Object)
 */
@Override
protected void setValue(final Object value) {
    double d = 0;
    if (value instanceof DoubleValue) {
        DoubleValue cell = (DoubleValue) value;
        double val = cell.getDoubleValue();
        DataColumnSpec spec = getColSpec();
        double min = Double.POSITIVE_INFINITY;
        double max = Double.NEGATIVE_INFINITY;
        if (spec != null) {
            DataColumnDomain domain = spec.getDomain();
            DataCell lower = domain.getLowerBound();
            DataCell upper = domain.getUpperBound();
            if (lower instanceof DoubleValue) {
                min = ((DoubleValue) lower).getDoubleValue();
            }
            if (upper instanceof DoubleValue) {
                max = ((DoubleValue) upper).getDoubleValue();
            }
        }
        if (min >= max) {
            min = 0.0;
            max = 1.0;
        }
        d = (float) ((val - min) / (max - min));
        setToolTipText(Double.toString(val));
        setIconValue(d);
        setTextInternal(null);
    } else {
        setToolTipText("Missing Value");
        setIcon(null);
        setTextInternal(DataType.getMissingCell().toString());
    }
}

Example 23 with DoubleValue

use of org.knime.core.data.DoubleValue in project knime-core by knime.

the class AbstractTrainingRowBuilder method build.

@Override
public T build(final DataRow row, final int id) {
    int nonZeroFeatures = 1;
    int accumulatedIdx = 1;
    // the intercept feature is always present
    m_nonZeroIndices[0] = 0;
    m_nonZeroValues[0] = 1.0F;
    for (int i = 0; i < m_featureCellIndices.size(); i++) {
        // get cell from row
        Integer cellIdx = m_featureCellIndices.get(i);
        DataCell cell = row.getCell(cellIdx);
        DataType cellType = cell.getType();
        // handle cell according to cell type
        if (cellType.isCompatible(NominalValue.class)) {
            // handle nominal cells
            List<DataCell> nominalDomainValues = m_nominalDomainValues.get(cellIdx);
            int oneHotIdx = nominalDomainValues.indexOf(cell);
            if (oneHotIdx == -1) {
                throw new IllegalStateException("DataCell \"" + cell.toString() + "\" is not in the DataColumnDomain. Please apply a " + "Domain Calculator on the columns with nominal values.");
            } else if (oneHotIdx > 0) {
                m_nonZeroIndices[nonZeroFeatures] = accumulatedIdx + oneHotIdx - 1;
                m_nonZeroValues[nonZeroFeatures] = 1.0F;
                nonZeroFeatures++;
            }
            accumulatedIdx += nominalDomainValues.size() - 1;
        } else if (m_vectorLengths.containsKey(cellIdx)) {
            // handle vector cells
            if (cellType.isCompatible(BitVectorValue.class)) {
                BitVectorValue bv = (BitVectorValue) cell;
                for (long s = bv.nextSetBit(0L); s >= 0; s = bv.nextSetBit(s + 1)) {
                    m_nonZeroIndices[nonZeroFeatures] = (int) (accumulatedIdx + s);
                    m_nonZeroValues[nonZeroFeatures++] = 1.0F;
                }
            } else if (cellType.isCompatible(ByteVectorValue.class)) {
                ByteVectorValue bv = (ByteVectorValue) cell;
                for (long s = bv.nextCountIndex(0L); s >= 0; s = bv.nextCountIndex(s + 1)) {
                    m_nonZeroIndices[nonZeroFeatures] = (int) (accumulatedIdx + s);
                    m_nonZeroValues[nonZeroFeatures++] = bv.get(s);
                }
            // uncomment once DoubleVectors can be used with PMML
            // } else if (cellType.isCompatible(DoubleVectorValue.class)) {
            // // DoubleVectorValue also implements CollectionDataValue but
            // // as it then first boxes its values into DataCells, it is much more
            // // efficient to access its values via the DoubleVectorValue interface
            // DoubleVectorValue dv = (DoubleVectorValue)cell;
            // for (int s = 0; s < dv.getLength(); s++) {
            // float val = (float)dv.getValue(s);
            // if (!MathUtils.equals(val, 0.0)) {
            // m_nonZeroIndices[nonZeroFeatures] = accumulatedIdx + s;
            // m_nonZeroValues[nonZeroFeatures++] = val;
            // }
            // }
            // uncomment once double lists become compatible with PMML
            // } else if (cellType.isCollectionType() && cellType.getCollectionElementType().isCompatible(DoubleValue.class)) {
            // CollectionDataValue cv = (CollectionDataValue)cell;
            // int s = 0;
            // for (DataCell c : cv) {
            // // we already checked above that cv contains DoubleValues
            // DoubleValue dv = (DoubleValue)c;
            // double val = dv.getDoubleValue();
            // if (!MathUtils.equals(val, 0.0)) {
            // m_nonZeroIndices[nonZeroFeatures] = accumulatedIdx + s;
            // m_nonZeroValues[nonZeroFeatures] = (float)val;
            // }
            // s++;
            // }
            } else {
                // should never be thrown because we check the compatibility in the constructor
                throw new IllegalStateException("DataCell \"" + cell.toString() + "\" is of an unknown vector/collections type.");
            }
            accumulatedIdx += m_vectorLengths.get(cellIdx);
        } else if (cellType.isCompatible(DoubleValue.class)) {
            // handle numerical cells
            double val = ((DoubleValue) cell).getDoubleValue();
            if (!MathUtils.equals(val, 0.0)) {
                m_nonZeroIndices[nonZeroFeatures] = accumulatedIdx;
                m_nonZeroValues[nonZeroFeatures++] = (float) val;
            }
            accumulatedIdx++;
        } else {
            // a different DataCell of incompatible type.
            throw new IllegalStateException("The DataCell \"" + cell.toString() + "\" is of incompatible type \"" + cellType.toPrettyString() + "\".");
        }
    }
    int[] nonZero = Arrays.copyOf(m_nonZeroIndices, nonZeroFeatures);
    float[] values = Arrays.copyOf(m_nonZeroValues, nonZeroFeatures);
    return createTrainingRow(row, nonZero, values, id);
}

Example 24 with DoubleValue

use of org.knime.core.data.DoubleValue in project knime-core by knime.

the class CrosstabStatisticsCalculator method fisherExactPValue.

/**
 * Compute the p-value of the Fisher's exact test.
 */
private static double fisherExactPValue(final BufferedDataTable freqTable, final int rowIndex, final int colIndex, final int freqIndex, final Collection<DataCell> rowVars, final Collection<DataCell> colVars) {
    if (rowVars.size() != 2 || colVars.size() != 2) {
        return Double.NaN;
    }
    List<DataCell> rowVarList = new ArrayList<DataCell>();
    rowVarList.addAll(rowVars);
    List<DataCell> colVarList = new ArrayList<DataCell>();
    colVarList.addAll(colVars);
    // The column of freqTable with index freqIndex holds the data of the
    // contingency table. Fill crosstab with this data.
    int[][] crosstab = new int[2][2];
    for (DataRow row : freqTable) {
        DataCell cell = row.getCell(freqIndex);
        int intFreq = 0;
        if (!cell.isMissing()) {
            Double v = ((DoubleValue) cell).getDoubleValue();
            double freq = v;
            intFreq = (int) freq;
            // test if freq is a nonnegative integer
            if (freq != intFreq || intFreq < 0) {
                return Double.NaN;
            }
        } else {
            intFreq = 0;
        }
        int i = rowVarList.indexOf(row.getCell(rowIndex));
        int k = colVarList.indexOf(row.getCell(colIndex));
        crosstab[i][k] = intFreq;
    }
    double[] x = exactPValue(crosstab);
    return x[FISHERS_TWO_TAILED];
}

Example 25 with DoubleValue

use of org.knime.core.data.DoubleValue in project knime-core by knime.

the class BinningUtil method checkDomainRange.

/**
 * Checks if the given cell is in the domain range of the given
 * {@link DataColumnSpec}. If the cell is missing the method returns
 * <code>true</code>.
 * @param cell the cell to check
 * @param spec the {@link DataColumnSpec} with the domain
 * @return <code>true</code> if the cell is missing or the value is between
 * the upper and lower bound specified by the domain of the given column
 * specification
 */
public static boolean checkDomainRange(final DataCell cell, final DataColumnSpec spec) {
    if (!cell.isMissing()) {
        if (!cell.getType().isCompatible(DoubleValue.class)) {
            throw new IllegalStateException("X value is not a valid number");
        }
        final DataColumnDomain domain = spec.getDomain();
        final DataCell lowerBoundCell = domain.getLowerBound();
        if (lowerBoundCell == null || lowerBoundCell.isMissing() || !lowerBoundCell.getType().isCompatible(DoubleValue.class)) {
            throw new IllegalArgumentException("The lower bound of the binning column domain " + "should be defined");
        }
        final double lowerBound = ((DoubleValue) lowerBoundCell).getDoubleValue();
        final DataCell upperBoundCell = domain.getUpperBound();
        if (upperBoundCell == null || upperBoundCell.isMissing() || !upperBoundCell.getType().isCompatible(DoubleValue.class)) {
            throw new IllegalArgumentException("The upper bound of the binning column domain " + "should be defined");
        }
        final double upperBound = ((DoubleValue) upperBoundCell).getDoubleValue();
        if (((DoubleValue) cell).getDoubleValue() < lowerBound || ((DoubleValue) cell).getDoubleValue() > upperBound) {
            return false;
        }
    }
    return true;
}