Schema-registry: AvroConverter should use Avro 1.8 and its logical types

[rhauch]

Avro 1.8 introduces logical types for decimal, duration, and temporal values. The AvroConverter should use these when serializing to enable more precise deserialization in Avro (depending upon the language binding and capabilities).

[0x0ece]

Let me first say that I agree with you.

This said, I've tested avro 1.8.1 a while ago and using logical types was pretty hard within avro itself. For instance, given a schema (with logical types), I couldn't find a way to create another schema with the same logical types. I literally mean in Java, while cloning fields I was getting fields without logical types and even setting them explicitly wasn't working. So, I'm not sure this feature is really "production ready".

This said, I'm using logical types (say, for annotation purposes) and I look forward to more of them, and proper integrations, such as geo location (latitude/longitude), timestamps as you mentioned, currencies, maybe json fields for unstructured objects...

[ig-michaelpearce]

+1 we're using 1.8.1 in our systems, but keep hitting issues as it seems schema registry is still based on older avro version

[ewencp]

Agreed the update would be valuable. Fully making use of logical types would require updating:

1. The version number needs to be bumped.
2. AvroData.toConnectData would need to be able to use Connect's logical schema type field or Avro's to determine which logical converter to apply.
3. AvroData.fromConnectSchema/Data need to be updated to ensure they cover translating the logical types correctly, including things like parameters for the logical types and filling in both Connect logical type names and Avro logical type names.

Note that there still wouldn't be a 1:1 mapping between them since Connect has tended towards a smaller number of critical types rather than an exhaustive set.

[m1schka]

Do you already have a plan when this gets implemented?
We're struggeling with a timestamp not getting inserted as a timestamp when using the JDBC kafka connector but actually as a long

[56quarters]

@m1schka I'm not a Confluent dev but I believe timestamps should use the correct database types (timestamp/datetime/etc., not long) with version 3.2 of the JDBC connector. After updating to 3.2 of the JDBC sink, a timestamp_ms field in Avro ended up as a datetime2 field in SQLServer.

https://github.com/confluentinc/schema-registry/blob/8aef6caa167aa34dfdc4b7ee36c97ca132e86740/avro-converter/src/main/java/io/confluent/connect/avro/AvroData.java#L1242

[m1schka]

I have the exact same issue as described in #522 with postgres. I see the upgrade is getting shipped with the next minor version of the platform, which could solve the problem

[ewencp]

Fixed by https://github.com/confluentinc/schema-registry/pull/563

[tryggvit]

Has this been confirmed to be fixed?
Because I still have this problem with confluent-oss-4.1.1-2.11

I have tried making a timestamp field in postgresql and an avro-schema logical type:

"name": "observation_time",
"type": "string",
"logicalType": "timestamp-millis"

Sending a message:

$ kafka-avro-console-producer --broker-list localhost:9092 --topic mytopic --property value.schema='{"type":"record","name":"myrecord","fields":[{"name":"observation_time", "type": "string", "logicalType": "timestamp-millis"}]}'

{"observation_time": "2018-06-13 00:00:00"}

results in:

ERROR: column \"observation_time\" is of type timestamp without time zone but expression is of type character varying

or a text field being created if I set auto.create=true

and

"name": "observation_time",
"type": "long",
"logicalType": "timestamp-millis"

Sending:
{"observation_time": "2018-06-13 00:00:00"}

results in:

ERROR: column \"observation_time\" is of type timestamp without time zone but expression is of type bigint

or an int8 field being created if I set auto.create=true

[ewencp]

@tryggvit If you're using logicalType of timestamp-millis, you must use the long type, that is the only concrete type supported. But further, console producer isn't going to automatically parse to match the type. Console producer isn't really written to support this, so to try using logical types, you'd want to be writing actual code that creates the correct, matching type (e.g. Java Date class).

[tryggvit]

@ewencp Sorry to keep on about this, and about the confusion.

I am able to produce valid Avro messages using the long type and logicalType timestamp-millis (using confluent-kafka-python or kafka-avro-console-producer).

But the error is thrown by my PostgreSQL database when writing through the io.confluent.connect.jdbc.JdbcSinkConnector, consuming the Avro messages.

However, I have figured out what the problem was. The logicalType schema needed by the JDBC-connector is not according to the Avro specification:

This doesn't work:

{
    "name": "observation_time",
    "type": "long",
    "logicalType": "timestamp-millis"
}

This works:

{
    "name" : "observation_time",
    "type" : [ "null", {
        "type" : "long",
        "logicalType" : "timestamp-millis"
    }]
}