:bug: Source Amplitude: Failed to apply RFC3339 pattern on date-time string

[bazarnov]

With given connection:
Source Amplitude <> Destination S3 (CSV, no compression, root level flattening),
The issue with date-time parsing comes up:

Failed to apply RFC3339 pattern on 2022-05-16 19:13:09.369000

The example date-time string which causes issue: 2022-05-16 19:13:09.369000
This is the usual RFC3339 format, however destination s3 handles it incorrectly.

More info in this issue: #13057

[bazarnov]

@alexandr-shegeda Please take a look, sounds like minor bug.

[eliyarson]

We are experiencing the same bug here with BQ destination.
It's generating a massive amount of logs which is throttling the MinIO deployment.

[agiletich]

Same with postgres (connectors/destination/postgres )

[alexandertsukanov]

During the investigation, I found that the current bug was not related to either Python or Java Connectors.
The code which triggers the issue is located in airbyte-workers:

airbyte/airbyte-workers/src/main/java/io/airbyte/workers/general/DefaultReplicationWorker.java

Line 309 in 3dcda7a

recordSchemaValidator.validateSchema(messageOptional.get().getRecord());

It looks like io/airbyte/validation/json/JsonSchemaValidator.java uses a library that has a regexp pattern for date-time nodes.

The dependeny tree as follows:
airbyte-workers -> airbyte-json-validation -> com.networknt:json-schema-validator:1.0.42

The library is:

airbyte/airbyte-json-validation/build.gradle

Line 6 in 9d1cd42

implementation 'com.networknt:json-schema-validator:1.0.42'

And the line where the JsonSchemaFactory instance is created:

airbyte/airbyte-json-validation/src/main/java/io/airbyte/validation/json/JsonSchemaValidator.java

Line 33 in 9d1cd42

this.jsonSchemaFactory = JsonSchemaFactory.getInstance(SpecVersion.VersionFlag.V7);

The regexp used by the com.networknt:json-schema-validator:1.0.42 is:

private static final Pattern RFC3339_PATTERN = Pattern.compile(
            "^(\\d{4})-(\\d{2})-(\\d{2})" // yyyy-MM-dd
                    + "([Tt](\\d{2}):(\\d{2}):(\\d{2})(\\.\\d+)?)?" // 'T'HH:mm:ss.milliseconds
                    + "([Zz]|([+-])(\\d{2}):?(\\d{2}))?");

And the validation code looks as follows:

 // Validate the format
        if (!matcher.matches()) {
            logger.error("Failed to apply RFC3339 pattern on " + string);
            return false;
        }

This validation doesn't affect the Sync. I was able successfully to sync the next sources and destinations:

• amplitude -> s3
• amplitude -> bq
• amplitude -> MySQL

The only thing annoying here is this ERROR message which introduces a misunderstanding from the client's point of view.

The library provides the way to modify JSON meta schema for our needs:
https://github.com/networknt/json-schema-validator/blob/master/doc/validators.md. I belive we can replace the constructor with smth like:

public JsonSchemaValidator() {
    this.schemaValidatorsConfig = new SchemaValidatorsConfig();
    String URI = "https://json-schema.org/draft/2019-09/schema";
    JsonSchemaFactory.getInstance(SpecVersion.VersionFlag.V7);
    final JsonMetaSchema overrideEmailValidatorMetaSchema = new JsonMetaSchema.Builder(URI)
        .idKeyword("$id")
        .addFormat(
            new PatternFormat("date-time", "^(\\d{4})-(\\d{2})-(\\d{2}) ((\\d{2}):(\\d{2}):(\\d{2})(\\.\\d+)?)?([Zz]|([+-])(\\d{2}):?(\\d{2}))?"))
        .build();
    this.jsonSchemaFactory = new JsonSchemaFactory.Builder()
        .addMetaSchema(overrideEmailValidatorMetaSchema)
        .build();
  }

@grishick what team is responsible for the airbyte-workers module? Could you address the issue, please? Thanks, in advance.

CC: @bazarnov, @alexandr-shegeda

[bazarnov]

In addition to what's already said above:

• we've also tested the latest connector version of source-amplitude v0.1.7 with base-normalization - and it works as expected. No issues occurred, all target table is populated correctly, and data-time columns are full of expected data, but in Airbyte UI > logs we keep seeing this error about Failed to apply RFC3339 pattern on...

Could we try to adopt the existing pattern:

private static final Pattern RFC3339_PATTERN = Pattern.compile(
            "^(\\d{4})-(\\d{2})-(\\d{2})" // yyyy-MM-dd
                    + "([Tt](\\d{2}):(\\d{2}):(\\d{2})(\\.\\d+)?)?" // 'T'HH:mm:ss.milliseconds
                    + "([Zz]|([+-])(\\d{2}):?(\\d{2}))?");

to validate the 2022-05-16 19:13:09.369000 format date-time strings as well? Is this going to be a big change?

[pedroslopez]

The example date-time string which causes issue: 2022-05-16 19:13:09.369000
This is the usual RFC3339 format, however destination s3 handles it incorrectly.

@bazarnov It seems like date-times without a timezone offset are not valid in RFC3339, so I don't think updating the regex would be appropriate. See https://datatracker.ietf.org/doc/html/rfc3339#section-5.6 for the definitions, specifically:

date-time       = full-date "T" full-time
full-time       = partial-time time-offset
time-offset     = "Z" / time-numoffset

While there's a note that the "T" can be replaced by a space character, time-offset should still be specified. I believe this is the main reason why the Amplitude datetimes are coming back as invalid (no offset).

#13351 will make it so that the logs aren't spammed with these messages, but Amplitude should still be addressed.

[grishick]

Looks like there are three issues here

1. we need to update the pattern in JsonSchemaValidator to allow a space as well as a T between date and time
2. unnecessary error logging is being already taken care of in #13351
3. potentially, there is a bug in Amplitude source connector or Amplitude API wrt missing time offset

@alexandertsukanov since you already found the place in the code where to update the regex - go ahead and make the change and add me as reviewer to the PR.

As far as I can see from briefly perusing Amplitude docs, we may need to fix how we represent Amplitude schemas:

• annotations API has "date" field in YYYY-MM-DD format, but our schema says it's date-time in annotations.json
• sessions API has date field, which is also YYYY-MM-DD, but we have "format": "date-time" in average_session_length.json
• we use export API for reading events from Amplitude. This API returns date/time in ISO-8601 format rather than RFC3339 and in events.json we represent it as "format": "date-time", which is validates it against RFC3339

@pedroslopez could you please open a bug for Amplitude connector to represent date and ISO-8601 fields as dates rather than date-time? The solution may be just to remove "format": "date-time" from the schema.

[alovew]

@grishick I don't think we want to do number 1: we need to update the pattern in JsonSchemaValidator to allow a space as well as a T between date and time

Like @pedroslopez said, this is actually a validation error and so I think the correct solution is to fix the connectors instead of changing how we are doing validation.

[grishick]

@grishick I don't think we want to do number 1: we need to update the pattern in JsonSchemaValidator to allow a space as well as a T between date and time

Like @pedroslopez said, this is actually a validation error and so I think the correct solution is to fix the connectors instead of changing how we are doing validation.

Good point. We don't need to change schema validation until we see another RFC3339 field with a space instead of T.