Format or style field in bytes

Feature Requests
1

I just want to know if there is a way to ask a question and in the result of the table to be able to give a style to a numeric column that are bytes, just as you have the style of percent, scientific or currency, to assign the bytes value so that it is automatically represented to KiloBytes, MegaBytes, Gigabytes and so on depending on the value.

In this case what I want to represent is a value within the Radius standard called the Accounting Input Octets field for example.

AcctInputs
AcctInputs1917×799 123 KB

I understand that I can use a custom column doing the direct conversion to Gigabytes but it would be a fixed value having values like 0.002Gb, but I lose dynamically that the graph will represent the value automatically if they are KB, GB... etc...
I just want to confirm if this can be done in a paid version in Metabase? or it is not available in any version?

1 Like
2

I would do that with custom columns and case statements

3

Hi @Luiggi , thanks for you reply...

Thank you very much for the suggestion, I was working on it but I can't figure out how you can graph or represent this data in a grouped way (dynamically).

I don't like the idea of converting them all to Gigabyte since there are sessions that only consume Bytes or Kilobytes, where a number like 0.0024Gb would be seen.

Now what worked to start with were two custom columns, one to give me what kind of Unit I'm working with:
-B
-KB
-mb
...

  • Up to Pb

As well as another customized column where it evaluates if it falls in Kb, Mb, Gb.... then I do the respective conversion.

But when I do this I have a session or user within the time line that reports that it consumed 1 Kb, then after 5min it reports a consumption of 5Mb, after 5min another 2Gb, in this case I cannot do a sum of Kb, Mb or Gb. since they are not equivalent to the same.

Excuse my despair but I come from ELK where you simply select what type of value the column had and say "Bytes" and automatically perform all the conversions, both in tables and when graphing.

But I see that Metabase only brings for Currency, percentage and scientific.

We continue to evaluate the tool.

4

I'd love to have this. I'm happy to make a PR for this if directions are given

5

We really need this feature as well!! I Would love to see it in the product. B

6

Not having this feature is quite problematic as showing bandwidth/storage/transmission rates is not really possible with Metabase. Either we end up with laughably large values like 1,234,567,890,123 bytes or Metabase tries to treat these as regular numbers and divide them into thousands/millions etc. which is incorrect for data measurements. Metabase team this seems like an easy addition, can I help do a PR perhaps if you point me to how to easily add measurement types?

7

Can you provide an example or mockup of what output you’d like to see?

Charts already have a compact value representation for axes, and you can stick a ‘B’ suffix on there to complete the illusion.

I can work on a custom expression to compact a column. The resultant value is text so you can’t plot it but it’ll make table displays nicer.

PostgreSQL has pg_size_pretty() that formats a data size into a compact string. I use it for size displays in dashboards.

8

As a workaround, here’s some pretty print custom expressions. I only went up to giga-, but you can add peta- easily enough. Replace the [COLUMN] reference with the column you want to format. To avoid too much loss of precision, and as there's no fixed-point formatting (i.e., printf('%0.2f')) functions in Metabase, it doesn't move up to the next prefix until there's more than 1 digit.

For decimal prefixes:

if(
  [COLUMN] > 10000000000,
  concat(
    floor([COLUMN] / 1000000000),
    " GiB"
  ),
  [COLUMN] > 10000000,
  concat(
    floor([COLUMN] / 1000000),
    " MiB"
  ),
  [COLUMN] > 10000,
  concat(
    floor([COLUMN] / 1000),
    " KiB"
  ),
  concat([COLUMN], " B")
)

For binary prefixes:

if(
  [COLUMN] > 10000000000,
  concat(
    floor([COLUMN] / power(2, 30)),
    " GB"
  ),
  [COLUMN] > 10000000,
  concat(
    floor([COLUMN] / power(2, 20)),
    " MB"
  ),
  [COLUMN] > 10000,
  concat(
    floor([COLUMN] / power(2, 10)),
    " KB"
  ),
  concat([COLUMN], " B")
)
9

Thanks @dwhitemv for the suggestion, but sadly this approach of generating a string value for the column doesn’t work in the way it should for the following reasons:

  • Ordering is not possible by size i.e. 20GiB is greater than 50MiB, but not when lexicographically ordered.

  • When users then export the data, they don’t get the raw byte values, like they do for normal numerical values. This means they no longer have the raw data and fidelity they would need to use this data.

  • As you have said too, the values can’t be plotted because they’re strings, not numbers.

All of this as a result makes it impractical to show bandwidth (we don’t deal with files but traffic instead) values to users in tables or graphs.

The intended behaviour is that, like how numerical values are automatically converted to human friendly numbers in the frontend display layer, which means graphs would be scaled correctly with suitable MiB, GiB, TiB, etc. suffixes, rolling over entries in the graphs would show human friendly numbers, data could be exported so that the underlying value is still available, and the columns could be used for sorting and charting graphs.

BTW. The alternative approach I have tried was to add a value for “Multiply by a number” which was 1/1024*1024 (9.5e-7), and then add a MiB suffix. But that approach doesn’t work perfectly either because a) users now cannot export the underlying raw data, b) at times I’ve seen Metabase do weird things like add a k in front of the MiB when it tries to humanise the values, but kMiB is not a thing, c) if a user is looking at data with high granularity, MiB is often too big and it should be in KiB or just bytes to be meaningful.

10

It feels like this would be implemented as a new semantic type in the table metadata. But aggressive compaction works for other types of numbers, too (think currency). Maybe it’s an option rather than its own semantic type?