Support dots in column name

[Kronos3]

Is your feature request related to a problem?

I'm looking at potentially migrating to QuestDB from InfluxDB though I'm having a problem when trying to create columns with .. Dots are normally supported by InfluxDB (and the official line protocol syntax spec so I expected them to be supported here.

They are very useful since I am logging structured data and 'flattening' it to shove into a single row

For example, if I have an object in the form:

{
  "key1": 0.1,
  "key2": "stringValue",
  "key3": { "nested": [0, 1, 2] } 
}

This will be serialized into line protocol with something like

table key1=0.1,key2="stringValue",key3.nested[0]=0,key3.nested[1]=1,key3.nested[2]=2 TIMESTAMP

This allows me to reconstitute the object from a query without attaching additional metadata to describe to objects structure.

Describe the solution you'd like.

I'd like QuestDB to accept . as a valid column character. I know this character is explicitly not allowed at the moment #412, #2216 (comment) but it's unclear to me why that is necessary as there shouldn't be ambiguity in the line protocol syntax.

Describe alternatives you've considered.

Simply renaming the . to another special character that is supported like & seems to work though this is not optimal seeing as my schema will change and therefore other points that expect serialized nested paths to be named a certain way.

Full Name:

Andrei Tumbar

Affiliation:

NASA Jet Propulsion Laboratory

Additional context

Error for reference:

{"code":"invalid","message":"failed to parse line protocol:errors encountered on line(s):\nerror in line 1: table: TABLE; invalid column name: value.ARRAY_NAME[0]","line":1,"errorId":"21a903fbc762-7"}%                                                                                         

[nwoolmer]

Hi @Kronos3 ,

Thanks for the report!

Referring to this documentation: https://questdb.io/docs/reference/sql/create-table/#column-name

We don't support . in column names due to naming restrictions on the column itself. Its true that ILP would allow it, but we use the column names to name files on the filesystem, and not all filesystems allow arbitrary dots in the filename.

This could be potentially fixed by mangling the column name and storing a mapping of an alias to the column.

For example, on disk, we store a_b_c and then store a pointer to it referenced by a.b.c.

Could you share how much of a pain point this is? Is it workable or likely to prevent adoption?

[Kronos3]

Hi @nwoolmer, thanks for your reply. I see your point with mangling the name however it becomes a pain point mainly because it adds an extra transformation step in the frontend which may not always be viable. For example, in my use case we are pushing thousands of telemetry channel values, each with their own table and set of column names. If someone is exploring the schema without the transformation (i.e. with default tools like Grafana), they will be working with the mangled name rather than the true name.

If the dot restriction is just due to filesystem limitations, maybe it could be enabled through a configuration flag? Also pretty much all modern filesystems do allow dots just potentially not names ending in . so maybe relaxing the validation to allow inner-dots could be another solution?

[bluestreak01]

adding . notation is a non-trivial effort on our side, what's needed is:

• disambiguate SQL parser and add a bunch of tests to ensure . is not confused with table name prefix
• disambiguate storage engine, where . is used to separate name and version

We will come back to this as as soon as we can.

you can also store JSON and don't take it apart. Grafana dashboards can query materialized views that are created from the json

[tayler-king]

Annoyingly I did not see this mentioned in the documentation, I have several tables in production with dots in the name. I'm now finding out that certain queries aren't supported due to this.

This issue needs to account for existing tables that already include dots in the name.

[nwoolmer]

Dots in column names are disallowed. Table names do allow them, but they are not always well supported due to ambiguity with join prefixes and other issues.

Most users will choose a different delimeter like _.

[tayler-king]

Yes apologies I misread the issue name. Unfortunately the issue persists with dots in table names breaking queries unfortunately.