Java读取Teradata结果集写入Avro丢失行问题排查求助

Hey there, let's tackle this tricky random row loss issue—nothing's more frustrating than seeing data vanish without a clear pattern, especially when you can print every row just fine. Let's break down the most likely culprits and how to fix them:

1. Missing DataFileWriter Flush/Close Calls #

Avro's DataFileWriter uses an internal buffer to batch writes for performance. If you forget to close the writer after processing all rows, the last batch of data might never get flushed to the file. Even if you call flush() during the loop, close() is still required to finalize the file's metadata and ensure all buffered data is written.

Fix Example: #

// Initialize your Avro writer
DataFileWriter<GenericRecord> writer = new DataFileWriter<>(new GenericDatumWriter<>(schema));
writer.create(schema, outSt);

// Process ResultSet
while (rs.next()) {
    // Build your Avro record and append
    GenericRecord record = buildAvroRecordFromResultSet(rs, schema);
    writer.append(record);
    // Optional: Flush periodically if dealing with extremely large datasets
    // if (rs.getRow() % 1000 == 0) writer.flush();
}

// Critical: Close the writer after all rows are processed
writer.close();
outSt.close(); // Don't forget to close your output stream too!

2. Accidental ResultSet Cursor Misalignment #

Since you can print every row but lose some during Avro writing, double-check that your code isn't moving the ResultSet cursor twice per row (once for printing, once for writing) or skipping rows accidentally.

Common Mistake to Avoid: #

// ❌ Wrong: Moves cursor twice per iteration
while (rs.next()) {
    System.out.println("Printing row: " + rs.getString("id"));
    if (rs.next()) { // Oh no! This skips every other row when writing
        writer.append(buildRecord(rs));
    }
}

// ✅ Correct: Single rs.next() per row
int rowCount = 0;
while (rs.next()) {
    rowCount++;
    System.out.println("Processing row " + rowCount + ": " + rs.getString("id"));
    writer.append(buildRecord(rs));
}

3. Swallowed Exceptions During Data Conversion #

You mentioned converting all types to String—if a field conversion throws an exception (e.g., a NULL value handling issue, or a Teradata-specific type that getString() struggles with) and your code swallows it without logging, that row will silently fail to write.

Fix: Add Error Logging for Conversion #

private GenericRecord buildAvroRecordFromResultSet(ResultSet rs, Schema schema) throws SQLException {
    GenericRecord record = new GenericData.Record(schema);
    for (Field field : schema.getFields()) {
        String fieldName = field.name();
        try {
            // Handle NULLs explicitly if needed
            String value = rs.getString(fieldName);
            record.put(fieldName, value);
        } catch (SQLException e) {
            // Log the failure so you know which row/field is causing issues
            System.err.printf("Failed to convert field '%s' in row %d: %s%n", 
                fieldName, rs.getRow(), e.getMessage());
            // Optionally set a default value instead of skipping the entire row
            record.put(fieldName, null);
        }
    }
    return record;
}

4. Teradata JDBC Fetch Size Configuration #

Teradata's JDBC driver uses a default fetch size that might cause partial data retrieval if you're dealing with large result sets. Setting an appropriate fetch size can prevent unexpected row truncation.

Fix: Set Fetch Size on Your Statement #

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(5000); // Adjust based on your dataset size (larger = fewer roundtrips)
ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");

Quick Debugging Tip #

Add a row counter to both your print statement and Avro write logic, then compare the final counts. If the print count is higher than the Avro write count, you'll know exactly how many rows are missing—and can trace back to where they're being skipped.

内容的提问来源于stack exchange，提问作者Ambar Raghuvanshi