Minor documentation nits

[candlerb]

Hello, I'm a newbie to Proton which looks awesome, and going through the docs I have a few minor nits to flag.

---

At https://docs.timeplus.com/glossary under "view"

By default, views don't take any extra computing or storage resources. They are expected to the SQL definition when they are queried

Should this be "expanded" not "expected"?

---

At https://www.timeplus.com/post/introducing-mutable-streams there is a formatting issue:

You can also run a streaming query with historical data backfill (e.g. select .. from my_stream where tp time>now()-14d )

I think that should be _tp_time

---

At https://docs.timeplus.com/eventtime

When it's configured, we may set trip_end as the (default) event time
...
This query uses trip_start, the default event time, to run the aggregation.

That's inconsistent: in the first sentence it said that trip_end was the default end time, and that agrees with the following clause:

If the passenger ends the trip on 00:01 at midnight, it will be included in the 00:00-00:59 time window.

But then it's also inconsistent with the following section:

In some cases, you as the analyst, may want to focus on how many passengers get in the taxi, instead of leaving the taxi, in each hour, then you can set trip_end as the event time for the query via tumble(taxi_data,trip_end,1h)

I think those should say trip_start.

---

At https://docs.timeplus.com/proton-ingest-api#push-data-to-timeplus

The above method should work very well for most system integration. However, the column names will be repeatedly mentioned in the requested body.

We also provide a more performant solution to only list the column names once.

The request body is this format:

But then it goes on to show exactly the same format, i.e. the original example was already in the form that doesn't repeat the column names (so we don't know what that was)

---

The list of functions appears to be incomplete. I notice for example that unhex() is mentioned in passing under ipv6_num_to_string, but it doesn't seem to be documented elsewhere (although hex is)

---

I also have a few questions:

• https://docs.timeplus.com/proton-create-stream#data-types
  • Is "string" type 8-bit clean (like a blob), or UTF-8 only, or some other limitation?
  • There's a separate list of types at https://docs.timeplus.com/datatypes which is different (e.g. it shows ipv4 and ipv6), perhaps this should be referenced?
  • How are timezones handled? Is it expected that the user will convert all date/times to UTC or to a consistent common local timezone? Is the system configured timezone involved at all? I note that select now(); gives me the local time.
• There's a mention of external log streams here, but I can't see any further info on how to configure them, and at https://docs.timeplus.com/proton-kafka it says only kafka and timeplus streams are available. Are these for system logs (newline-delimited files), or some other sort of log
• There doesn't seem to be any discussion of NULL handling

Experimentally, it looks like by default "NOT NULL" is handled as a constraint but ignored; Null values are just zero values, and Null can be inserted regardless of "NOT NULL" constraint:

create stream xxx (src4 ipv4);
create stream yyy (src4 ipv4 not null);
insert into xxx (src4) values (null);
insert into yyy (src4) values (null);
select * from table(xxx);
┌─src4────┬────────────────_tp_time─┐
│ 0.0.0.0 │ 2024-09-06 11:43:56.583 │
└─────────┴─────────────────────────┘
select * from table(yyy);
┌─src4────┬────────────────_tp_time─┐
│ 0.0.0.0 │ 2024-09-06 11:45:28.521 │
└─────────┴─────────────────────────┘

However, the avro data types section does mention nullable(T) as a proton data type, and that is accepted:

create stream zzz (src4 nullable(ipv4));
insert into zzz (src4) values (null);
select * from table(zzz);
┌─src4─┬────────────────_tp_time─┐
│ ᴺᵁᴸᴸ │ 2024-09-06 11:50:09.035 │
└──────┴─────────────────────────┘

Perhaps this ought to be mentioned at https://docs.timeplus.com/datatypes ?

How does the internal representation of NULL compare to a not-null value? For example, with ipv4 is there perhaps a fixed 5-byte structure regardless of whether the value is null or not, or do null values take up less space than not-null values?

(I'm thinking of a record which might use ipv4 or ipv6, whether I should have one column of each, and make them both nullable)

OK, that's enough for now! But I'm also interested if anyone has any example use cases of:

• Using Proton to process system logs
• Using Proton to process netflow data

Many thanks,

Brian.

[candlerb]

FWIW, I have been trying some more with time zones but have ended up with some confusion.

select to_time('6/9/2024 13:28','UTC');
┌─to_time('6/9/2024 13:28', 'UTC')─┐
│          2024-06-09 13:28:00.000 │
└──────────────────────────────────┘

select to_time('6/9/2024 13:28','Europe/London');
┌─to_time('6/9/2024 13:28', 'Europe/London')─┐
│                    2024-06-09 13:28:00.000 │
└────────────────────────────────────────────┘

select to_time('6/9/2024 13:28','Europe/London') = to_time('6/9/2024 13:28','UTC');
┌─equals(to_time('6/9/2024 13:28', 'Europe/London'), to_time('6/9/2024 13:28', 'UTC'))─┐
│ false                                                                                │
└──────────────────────────────────────────────────────────────────────────────────────┘

So from this, it looks like datetime values do know their timezone, but it's hidden when you display them.

I note that even the format_datetime function doesn't have a way to extract or display the timezone, so I'm not sure if it's really part of the value at all.

But if I insert the values into a table, then it behaves differently:

create stream foo (id int, t datetime);
insert into foo (id, t) values (1, to_time('6/9/2024 13:28','UTC')), (2, to_time('6/9/2024 13:28','Europe/London'));
select id,t from table(foo);
┌─id─┬───────────────────t─┐
│  1 │ 2024-06-09 14:28:00 │  <<< NOTE!
│  2 │ 2024-06-09 13:28:00 │
└────┴─────────────────────┘

The times have been converted. Either both were converted to UTC before being stored in the database, and then converted to local system time when retrieved or displayed; or, both were converted to local system time for storage, then displayed as-is.

It's very confusing when you compare to the following, which makes you think they are the same:

select to_time('6/9/2024 13:28','UTC') as t1, to_time('6/9/2024 13:28','Europe/London') as t2;
┌──────────────────────t1─┬──────────────────────t2─┐
│ 2024-06-09 13:28:00.000 │ 2024-06-09 13:28:00.000 │
└─────────────────────────┴─────────────────────────┘

except they're not:

select to_time('6/9/2024 13:28','UTC')::int as t1, to_time('6/9/2024 13:28','Europe/London')::int as t2
┌─────────t1─┬─────────t2─┐
│ 1717939680 │ 1717936080 │    << These are different!
└────────────┴────────────┘

I'm running this on a Mac. If I stop proton, change my timezone from London (UTC+1) to Bangkok (UTC+7), and restart proton, then:

select id,t from table(foo);
┌─id─┬───────────────────t─┐
│  1 │ 2024-06-09 20:28:00 │
│  2 │ 2024-06-09 19:28:00 │
└────┴─────────────────────┘

My best guess, then, is that times are stored in UTC (or as Unix time?), but are normally displayed in local time, except for the unexplained oddity around rendering of select to_time(...)

Anyway, this is something I definitely need to get a handle on, as it's pretty crucial to the operation of a time-based event logging database!

[jovezhong]

Hi @candlerb , thank you very much for trying Proton and sharing your suggestions for the docs.

To track those suggested edits and discuss item by item easier, do you mind creating a PR for https://github.com/timeplus-io/docs?

[jovezhong]

Thank you @candlerb for the detailed feedback. I really enjoy the discussion.

Yes, many of the data types and SQL functions we inherited from ClickHouse. In many cases ClickHouse docs are great, but sometimes can be overwhelming. We will keep enhancing the docs and don't want to push people to read ClickHouse docs first. So what you are suggesting make perfect senses.

I am not sure the string has to be UTF-8. From https://clickhouse.com/docs/en/sql-reference/data-types/string

ClickHouse does not have the concept of encodings. Strings can contain an arbitrary set of bytes, which are stored and output as-is. If you need to store texts, we recommend using UTF-8 encoding. At the very least, if your terminal uses UTF-8 (as recommended), you can read and write your values without making conversions. Similarly, certain functions for working with strings have separate variations that work under the assumption that the string contains a set of bytes representing a UTF-8 encoded text

If you need to process the string in JavaScript UDF, I would recommend using UTF-8 text. If the content is actually binary, maybe use base64 encoding first.

Let's discuss the datetime/timezone in the other ticket.

Happy to learn a bit more about your use case. If it's to send logs from vector to Timeplus Proton, you need to batch the data by yourself. say a single raw string column, with array of array and each item is a the log message. You can also send them to Kafka and Proton can read data with some batch configuration.

Have a nice weekend.

[candlerb]

I am not sure the string has to be UTF-8.

From the point of view of Clickhouse storage, that's correct. But I'm not sure of the position of Proton yet.

There are cases where strings are included as parts of JSON bodies: in the HTTP ingestion API for example, or Javascript UDFs. If you put random binary data inside double quotes, then that's technically not JSON, because JSON only allows valid UTF-8. But some systems do allow it.

This isn't just a theoretical problem, because I see this in real life (e.g. network devices which sometimes include some binary junk at the end of syslog messages). Either the logging system needs to be 8-bit clean to preserve these messages faithfully; or I need to preprocess them to substitute the problematic bytes. But if I don't consider this case, then it's possible that the API will barf when I try to store or process one of these messages, which can't be allowed to happen.

Maybe I can avoid this issue by not using the REST API and sticking to the SDK clients which I presume use the native API, and if that's based on Clickhouse's then it should be 8-bit clean. But I wonder if I will come across other edge cases where putting binary data in a string causes issues.

Happy to learn a bit more about your use case.

I have three related use cases all around network monitoring:

1. Syslog ingestion, searching, reporting (e.g. group by source IP, facility, severity), and being able to stream or retrieve stored logs into other systems e.g. for security analysis.
2. Netflow processing and visualization (e.g. top K source/destination addresses, ports; geo-mapping IP addresses)
3. Structured log ingestion (e.g. DHCP logs, RADIUS auth logs, ARP/NDP tables, bridge tables, DNS query logs), to be able to answer queries like "show me everything that you know about who was using IP address 1.2.3.4 at time T"

There are a bunch of existing tools which do this with differing levels of success. Grafana Loki is quite good for use case 1. For case 2 you could use filebeat+elasticsearch+kibana but that's a hefty stack to run and hard to scale for high volumes of netflow data; or nfdump/nfsen for a low resource storage of flow records but with limited querying capabilities; or there are many expensive commercial solutions. Case 3 I don't have a good solution for; Netdisco and/or prometheus+snmp_exporter and/or packetbeat+Elasticsearch gets some of the way there, but it's a real jumble of parts.

I'm interested in Proton and/or Clickhouse as the potential for a coherent solution which could cover all these well. Proton appears to have the more mature JSON support, and it could provide real-time views (such as "live-tailing" logs, which Loki can do too, but also live-tailing aggregations)

If it's to send logs from vector to Timeplus Proton, you need to batch the data by yourself. say a single raw string column, with array of array and each item is a the log message.

IMO batching belongs in a Vector sink, like the loki sink. Batching before Vector would lose the benefit of going through Vector in the first place, i.e. being able to keep logs in a general form and route them to different destinations. If I have to create "Proton-specific" messages then they might as well be sent directly to Proton.

I do think a log pipeline like Vector could still be useful, potentially for things like enriching logs with GeoIP information, which I'm not sure I could do efficiently inside Proton EDIT: I see that Clickhouse supports dictionaries of type ip_trie which would be perfect for this sort of lookup. Proton mentions dictionaries only in passing, in the dict_get function, and doesn't mention CREATE DICTIONARY in SQL commands. It does accept CREATE DICTIONARY, but I get hangs in dict_get - I'll raise that as an issue, but right now it's looking like Clickhouse is the more mature product for what I'm trying to do.

Cheers!

[jovezhong]

Hi Brian, Thank you for the detailed reply. Somehow I overlooked the message during the weekend. For the dict_get/dictionary, as we discussed in the other thread, dictionary is not officially supported and I have removed the dict_get/etc functions.

We do have customers to apply Timeplus in DDoS attack detection. The high level information is available at https://www.timeplus.com/ddos-detection You may join our slack and we can setup meetings to have more discussions

[chenziliang]

Hi @candlerb , thanks for raising the questions and doc errors which we will revise on our end. In general, Proton is usually a super set of ClickHouse since it extended ClickHouse with streaming processing capability, however there are indeed some ClickHouse features we haven't exposed to end users yet.

The timezone display issue can be mitigated via to_type_name(datetime_column) and also the requirement is tracked here: #835

For string, Proton is basically same as ClickHouse.

Your use cases are perfect ones for Proton. We can discuss in details regarding your requirements if you like to. I believe things could be largely simplified (instead of using X different tools to tackle your use cases, it is highly possible to tackle them by using only one with even better performance and resource efficiency)

[chenziliang]

Regarding dict support, usually for quite lots of use cases , MUTABLE STREAM in Timeplus Enterprise could be a replacement since it provides incremental data update, fast point / range query + streaming query processing capability