Pulsar: How to Date type -> AVRO -> Presto

[KannarFr]

Hi,

I checked https://github.com/apache/pulsar/blob/master/pulsar-client/src/test/java/org/apache/pulsar/client/impl/schema/AvroSchemaTest.java#L80.

First question, java.time.LocalDateTime is supported?

Second about this issue:

I've got a schema with timestamp value, I tried to encode it using the example provided via Joda. Here is the schema for this attribute:

      {
        "name": "timestamp",
        "type": {
          "type": "record",
          "name": "DateTime",
          "namespace": "org.joda.time",
          "fields": [
            {
              "name": "iMillis",
              "type": "long"
            },
            {
              "name": "iChronology",
              "type": {
                "type": "record",
                "name": "Chronology",
                "fields": []
              }
            }
          ]
        }
      },

How get a generic date type, not jvm one, to output it as timestamp type in presto?

For this I've got presto typed as timestamp.imillis | bigint | |.

[KannarFr]

@jerrypeng

[sijie]

@codelipenghui @congbobo184 can any of you help @KannarFr with his question? Also if the documentation is missing, it might be worth improving the documentation about this part.

[KannarFr]

poke @codelipenghui @congbobo184

[congbobo184]

@KannarFr First, AvroSchema only support org.joda.time. The second, you should use Avro logicalType like

    @Data
    private static class SchemaLogicalType{
        @org.apache.avro.reflect.AvroSchema("{\n" +
                "  \"type\": \"bytes\",\n" +
                "  \"logicalType\": \"decimal\",\n" +
                "  \"precision\": 4,\n" +
                "  \"scale\": 2\n" +
                "}")
        BigDecimal decimal;
        @org.apache.avro.reflect.AvroSchema("{\"type\":\"int\",\"logicalType\":\"date\"}")
        LocalDate date;
        @org.apache.avro.reflect.AvroSchema("{\"type\":\"long\",\"logicalType\":\"timestamp-millis\"}")
        DateTime timestampMillis;
        @org.apache.avro.reflect.AvroSchema("{\"type\":\"int\",\"logicalType\":\"time-millis\"}")
        LocalTime timeMillis;
        @org.apache.avro.reflect.AvroSchema("{\"type\":\"long\",\"logicalType\":\"timestamp-micros\"}")
        long timestampMicros;
        @org.apache.avro.reflect.AvroSchema("{\"type\":\"long\",\"logicalType\":\"time-micros\"}")
        long timeMicros;
    }

in your class

[KannarFr]

@congbobo184 I tried this but in presto side it's a bigint, not a timestamp.

[KannarFr]

Yes I saw this in AvroSchemaTest.java or something related but still the presto type issue.

[KannarFr]

(The second part of the issue described on top of this thread).

[codelipenghui]

@KannarFr Are you using the master branch to build a snapshot version? I test with master branch, it seems worked:

presto> select * from pulsar."public/default".foo limit 10;
 age |     timestampmillis     | __partition__ | __event_time__ |    __publish_time__     | __message_id__ | __sequence_id__ | __producer_name__ | __key__ | __properties__
-----+-------------------------+---------------+----------------+-------------------------+----------------+-----------------+-------------------+---------+----------------
   0 | 2019-09-10 20:22:04.249 |            -1 | NULL           | 2019-09-10 20:22:04.267 | (16,0,0)       |               0 | standalone-1-2    | NULL    | {}
   1 | 2019-09-10 20:22:04.298 |            -1 | NULL           | 2019-09-10 20:22:04.299 | (16,1,0)       |               1 | standalone-1-2    | NULL    | {}
   2 | 2019-09-10 20:22:04.302 |            -1 | NULL           | 2019-09-10 20:22:04.302 | (16,2,0)       |               2 | standalone-1-2    | NULL    | {}
   3 | 2019-09-10 20:22:04.304 |            -1 | NULL           | 2019-09-10 20:22:04.304 | (16,3,0)       |               3 | standalone-1-2    | NULL    | {}
   4 | 2019-09-10 20:22:04.307 |            -1 | NULL           | 2019-09-10 20:22:04.307 | (16,4,0)       |               4 | standalone-1-2    | NULL    | {}
   5 | 2019-09-10 20:22:04.310 |            -1 | NULL           | 2019-09-10 20:22:04.310 | (16,5,0)       |               5 | standalone-1-2    | NULL    | {}
   6 | 2019-09-10 20:22:04.313 |            -1 | NULL           | 2019-09-10 20:22:04.313 | (16,6,0)       |               6 | standalone-1-2    | NULL    | {}
   7 | 2019-09-10 20:22:04.316 |            -1 | NULL           | 2019-09-10 20:22:04.316 | (16,7,0)       |               7 | standalone-1-2    | NULL    | {}
   8 | 2019-09-10 20:22:04.318 |            -1 | NULL           | 2019-09-10 20:22:04.318 | (16,8,0)       |               8 | standalone-1-2    | NULL    | {}
   9 | 2019-09-10 20:22:04.321 |            -1 | NULL           | 2019-09-10 20:22:04.321 | (16,9,0)       |               9 | standalone-1-2    | NULL    | {}
(10 rows)

[KannarFr]

Ok, will try maybe I probably did something wrong. Thanks for your time, keep you updated.

[KannarFr]

@codelipenghui and about List<String> to presto array, do you have an example?

[codelipenghui]

@KannarFr Currently can not work well with list, i think we need add support for presto ARRAY type in pulsar SQL.

[KannarFr]

@congbobo184 @codelipenghui

I used joda, with the logical schema type. like

@AvroSchema("{\"type\":\"long\",\"logicalType\":\"timestamp-millis\"}")
public DateTime timestamp;
public String ownerid;

The output of show columns from topic

 timestamp.imillis    | bigint    |       |
 ownerid              | varchar   |       |

When I select this particular field I've got Column 'timestamp.imillis' cannot be resolved other fields (text, int) work well.

The select * works and outputs

| timestamp.imillis |                  ownerid                  |
+-------------------+-------------------------------------------+
|     1568128483098 | orga_858600a8-74f4-4d75-a8a3-f5b368be093c |
|     1568128480098 | orga_858600a8-74f4-4d75-a8a3-f5b368be093c |
|     1568128481098 | orga_858600a8-74f4-4d75-a8a3-f5b368be093c |
|     1568128482098 | orga_858600a8-74f4-4d75-a8a3-f5b368be093c |

[codelipenghui]

@KannarFr I see something different.

The output of show columns from topic

Only show 2 columns? i can't see the internal columns and timestamp.imillis looks weird, i don't know why add .imillis in the end for column name timestamp.

The select * works and outputs

Also no internal columns, do you trim them deliberately?

[KannarFr]

@codelipenghui I deliberately trim, the metadata columns are here and publishtime is typed timestamp.

For your example what is the schema if you schemas get on topic for field timestamp?

[KannarFr]

(I described mine un top of this thread.)

[codelipenghui]

@KannarFr This is my schema info(use pulsar-admin schemas get):

{
  "version": 0,
  "schemaInfo": {
    "name": "foo",
    "schema": {
      "type": "record",
      "name": "UserV1",
      "namespace": "org.apache.pulsar.issue.SchemaTests$",
      "fields": [
        {
          "name": "age",
          "type": "int"
        },
        {
          "name": "timestamp",
          "type": [
            "null",
            {
              "type": "long",
              "logicalType": "timestamp-millis"
            }
          ]
        }
      ]
    },
    "type": "AVRO",
    "properties": {
      "__alwaysAllowNull": "true"
    }
  }
}

[KannarFr]

@codelipenghui what can explain the diff with mine?

[sijie]

@KannarFr is your question answered?