Column name prefixes

Support, Troubleshooting, & Help
1

When create a table with an id XYZ its name in the API is something like abcde_XYZ. When I create a column in that table named plugh, its name in the API is something like cdefg_plugh. Just observationally, it seems to be [a-z]{5}_.

I’m wondering if that behavior is consistent, predictable, and will never change. I’m working in node red, and I need to turn those names back into something meaningful. At present, I’m doing this with a regex. I just follow the tulip request with:

let keyre = /^[a-zA-Z0-9]{5}_(.*)$/
let oldPayload = msg.payload
Object.keys(oldPayload).forEach(  oldKey => {
    let m = oldKey.match( keyre )
    if (m && len(m) > 0) {
        /* the regex matched
        let newKey = m[1]   /* is this 1?  or is that the whole thing? I can't remember - print it out */
        newPayload[newKey] = oldPayload [oldKey]
    } else {
       /* didn't match */
       newPayload [oldKey] = oldPayload [oldKey]
    }
})

Experimentally this works. I’m making some changes to the node red library (expect PR soon) and am contemplating just doing this fix-up in there rather than externally.

Can Tulip define the behavior? And, any thoughts on what I’m doing here?

–Chris

2

Hey @wz2b,

So the underlying reason we add this string to the beginning of the table field name is so you can name two columns in the same table, the same thing. If we didn’t add this unique string, you could end up with two line fields in your table (say one is an integer and another is text), and in this case it would be impossible for the API to understand where your data should go.

The good - These don’t ever change, many customers have these unique ids hard coded in external systems with zero issues. Important to note - If you rename the column in your table, the column in the api won’t change. This is an intentional decission if it is hard coded in some other system, but something to be aware of, because suddenly the asfhsf_line field in the api is no longer the line field in your table (because you renamed it in the table).

Does this make sense?
Pete

3

It does make sense. In my case, though, I just want to strip that prefix out, understanding that it may cause a collision. But as long as I understand the rules, I can avoid problems.

Considering your motivation, I’m glad you chose to do it this way. If it were a GUID it would be unreadable (by human eyes), this way you can still look at it and tell what the field is. But my question, still, is will the format ever change: is ([a-zA-Z0-9]{5}_) a durable regex? I get that my tables/columns are guaranteed to never change, I’m just trying to figure out if I can make a dynamic map back function, rather than having to have a static mapping table

If you rename the column in your table

Can you actually do that, or only change the Label?

4

Hey @wz2b,

Thanks for the patience in me getting back to you, had to talk to our data teams to understand if we expect this architecture to change. My key takeaways were two-fold:

  1. There is no intention to change this architecture because many existing customers rely on the assumption it won’t change. Any outward facing data models would only ever change if there was an absolute need to change them, and that would only apply going forward (so legacy tables wouldn’t be impacted). Additionally, this change would be well communicated to anyone who may be impacted. This would be akin to the same level of change if we just totally removed some key function from the app builder and broke a bunch of customer apps. As far as I know, this kind of change has never happened, and there is no intention of making changes like this going forward.
  2. Your Regex is correct to match the 5 character unique Id.

In regard to renaming fields-
You are effectively just changing what the field looks like in the table ui, and trigger ui, the underlying name in the database where the data is stored isn’t changing, which is why it doesn’t change when the API returns it.

Pete.

5

@wz2b -

Had some further discussion with our data engineering teams. All of what I shared yesterday is accurate - a change to this architecture is very unlikely.
Having said that…

The better approach to get from table column ID’s to a human readable column tag would be to use the /tables/{tableId} endpoint. This endpoint will return a list of columns, including their unique ID (which the other endpoints return) and their human readable tag in the format below:

{
      "name": "ahvbb_model_number",
      "label": "Model Number",
      "description": "",
      "dataType": {
        "type": "string"
      },
      "hidden": false,
      "unique": false
    }

From here you would have something that is more scalable if we ever decided to change that unique id structure (again incredibly unlikely, but if).

Pete

6

Yeah.

I think the main thing here is that I need to treat table and column “name” as opaque strings. The fact that they do have a user-defined name as part of them is a great convenience, but if they were just a GUID then I would be forced to do what you said, and I would probably be fine with that - it’s pretty common in fact. For example, you generally refer to a bucket in InfluxDB by its bucket-id which is some kind of random string - in many places you are required to refer to a bucket by its ID.

I’m a little worried about using the labels here just because the goal is to get this data somewhere else (like a local TSDB or historian). The label truly is a user defined, mutable description that could have spaces, apostrophes, and all sorts of things in it, which increases complexity if you’re trying to use it as a “key” to the column.

OK. I’m pretty well set on this discussion for now, I know what to do.