Materialize: Separate sources, formats, and envelopes

[rjnn]

E.g. support tb sources. CDC sources are something prospects have asked for independent of them being kafka-ified.

[rjnn]

More broadly, we need to separate a few concerns. There are three broad concerns, which I term 'source', format', and 'envelope'

1) sources are either Kafka topic or Kinesis stream or file.
2) Format is either 'raw', json, protobuf, or avro.
3) Envelope (#1575) is debezium, append-only, or kafka-log-compaction.

cc @umanwizard who I had an offline conversation about this earlier.

We should support every combination of the three concerns, with the exception of raw newline deliminated string formats, which can only be interpreted as an append-only envelope.

[rjnn]

@cuongdo this is high priority and should be cleaned up before the public beta.

[rjnn]

cc @sploiselle this is what we were talking about earlier. This has a fairly high docs impact, so you should probably be involved in the conversation from the getgo, and you will most likely have very good feedback in the design phase as to what is most intuitive/easiest to explain.

[umanwizard]

We were originally planning on just having the format parsing be a relational function and having everything compose nicely using SQL. But upon discussion, it seems this is hard, since Materialize doesn't currently support record-valued functions, so there is no good way to write a query like the hypothetical create view foo as select parse_regex(my_str_column, 'a|(s)|(d|f)') from blah

Someday we will support record-valued functions, but in the meantime, we will still need hardcoded special syntax to specify the format and envelope

[jamii]

This isn't a problem at all for table-valued functions. Plenty of the existing ones return multiple columns. Something like select parsed.* from foo, parse_regex(foo.bar, "...") as parsed would work fine.

[umanwizard]

I'm not sure what I was confusing myself about before...

[jamii]

Table functions are a pretty recent addition to sql and they don't really fit in well with the existing mental model eg https://github.com/MaterializeInc/materialize/issues/1546. It's not you that's confused, it's the table functions :D

[benesch]

Outcome from our meeting today:

Syntax

CREATE SOURCE [ IF NOT EXISTS ] src FROM 'type://path' [ WITH (key = <value>, ...) ]
    [ FORMAT <format spec> ]
    [ ENVELOPE { NONE | DEBEZIUM } ]

format spec ::=
      AVRO USING <avro schema spec>
    | PROTOBUF USING <schema spec>
    | REGEX [ '<regex>' ]
    | CSV WITH <n> COLUMNS [ DELIMITED BY '<char>' ]
    | JSON
    | TEXT

avro schema spec ::= CONFLUENT SCHEMA REGISTRY <url> | <schema spec>

schema spec ::= SCHEMA FILE <path> | SCHEMA <inline schema>

Examples

CREATE SOURCE debezium_kafka_src FROM 'kafka://localhost:9092/topic'
    FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://localhost:8081'
    ENVELOPE DEBEZIUM;

CREATE SOURCE append_kafka_src FROM 'kafka://localhost:9092/topic'
    FORMAT AVRO USING SCHEMA '{ "type": "record", ... }';

CREATE SOURCE append_kafka_src FROM 'kafka://localhost:9092/topic'
    FORMAT AVRO USING SCHEMA FILE 'path/to/file.csv';

CREATE SOURCE csv_file_src FROM 'file:///path.csv'
    FORMAT CSV WITH 3 COLUMNS;

CREATE SOURCE regex_src FROM 'file:///path.txt'
    FORMAT REGEX '(?P<col1>.*) | (?P<col2>.*)';

CREATE SOURCE funky_file_src FROM 'file://funk.txt' WITH (delimiter = '\0')
    FORMAT JSON;

Anyone have lingering complaints about this syntax?

[ruchirK]

For protobuf sources we will need a message name argument as well

On Tue, Jan 28, 2020, 11:07 PM Nikhil Benesch [e-mail]
wrote:

Outcome from our meeting today:

Syntax

CREATE SOURCE [ IF NOT EXISTS ] src FROM 'type://path' [ WITH (key = , ...) ]
[ FORMAT ]
[ ENVELOPE { NONE | DEBEZIUM } ]

format spec ::=
AVRO USING
| PROTOBUF USING
| REGEX [ '' ]
| CSV WITH COLUMNS [ DELIMITED BY '' ]
| JSON
| TEXT

avro schema spec ::= CONFLUENT SCHEMA REGISTRY |

schema spec ::= SCHEMA FILE | SCHEMA

Examples

```sql
CREATE SOURCE debezium_kafka_src FROM 'kafka://localhost:9092/topic'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://localhost:8081'
ENVELOPE DEBEZIUM;

CREATE SOURCE append_kafka_src FROM 'kafka://localhost:9092/topic'
FORMAT AVRO USING SCHEMA '{ "type": "record", ... }';

CREATE SOURCE append_kafka_src FROM 'kafka://localhost:9092/topic'
FORMAT AVRO USING SCHEMA FILE 'path/to/file.csv';

CREATE SOURCE csv_file_src FROM 'file:///path.csv'
FORMAT CSV WITH 3 COLUMNS;

CREATE SOURCE regex_src FROM 'file:///path.txt'
FORMAT REGEX '(?P.) | (?P.)';

CREATE SOURCE funky_file_src FROM 'file://funk.txt' WITH (delimiter = '\0')
FORMAT JSON;

Anyone have lingering complaints about this syntax?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/MaterializeInc/materialize/issues/1579?email_source=notifications&email_token=AA6UHJCLRFB4W63E666CV3TRAD6H3A5CNFSM4KHXVVJKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKF3SKI#issuecomment-579582249,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AA6UHJGFFKHL4OI7RVL3AL3RAD6H3ANCNFSM4KHXVVJA
.

[jamii]

Is this desugaring to the new _extract functions or is it the only way to create sources?

Can I do FORMAT JUST BYTES I'LL DEAL WITH IT LATER?

[benesch]

Oops, here are the notes I wrote up in advance of the meeting, if anyone wants additional context: https://gist.github.com/benesch/3478ebe7257f5ae398a54940c1070867

@jamii it's just sugar, except for the envelope bit, because we don't have a way to express that outside of a CREATE SOURCE statement presently. The idea is that CREATE SOURCE src FROM 'uri' would work just as you say. I'm down to have an explicit FORMAT BYTES, though; that's very in keeping with SQL's design.

[umanwizard]

for Protobuf we can do PROTOBUF MESSAGE <name> USING <schema-spec> , if nobody objects

[umanwizard]

Also -- should the default envelope be NONE, or should it depend on the path? (Kafka vs. other)

(My vote is for default NONE)

[sploiselle]

Does NONE mean "append only?"

[umanwizard]

Also, @benesch , in | REGEX [ '<regex>' ] , why is the regex parameter optional? Is that a mistake?

[umanwizard]

1819 implements the part of this that we need in order for the 0.1 release to go out (baking the syntax).

There is still more to do, so I'm not closing this, but changing the milestone

[rjnn]

We should file separate issues for each of the remaining tasks (e.g. supporting Debezium envelopes for files, etc), and then close this one out. It's difficult to follow as-is, and the title work has been done. @umanwizard

[rjnn]

@awang there's a little bit of a re-assess the situation required here - I think it just requires some fresh eyes. @sploiselle can help you with any diagnosis that might be required as to what is and is not supported.

[awang]

also looks related to: https://github.com/MaterializeInc/materialize/issues/2537

[sploiselle]

This is done and documented so I'm closing.