Multi-value attributes and MariaDB


I have a TileDB array, initialized in python, which has a 3-value fixed-length attribute: dtype(“float32, float32, float32”)

I have connected this array with a MariaDB server to run SQL queries, but when I am * selecting the table, only the first of the three values is shown.

Is there a way to fetch all values, as tuple/list or in different columns sequentially?


Hello @vkaramanis ! Thank you for posting about this. Today MariaDB doesn’t support fixed size (greater than 1 value) or var length numeric types. There is no “array” or other type which we can convert this value to natively in MariaDB. There are a few options that we could consider and would like your feedback on based on your intended use case.

Option 1:
We can encode the multi-value attribute in a csv or json for a string representation. This could be useful for printing out of the results but will incur overhead in the conversion and depending on what you are looking to do with the values might cause you to parse the data back into a vector/array after the query.

Option 2:
We can report these fields are blobs, and return the values as a raw sequence of bytes (12 bytes in your case) where at an application you could cast each blob to the appropriate datatype, i.e a numpy array of dtype=[np.float, np.float, np.float].

Option 3:
For fixed-length attributes we can report each values as a separate column in MariaDB. i.e attribute_1_value_1, attribute_1_value_2, attribute_1_value_3. This semantics would let you use the values in MariaDB and perform operations but it will only work for fixed size values where the number of generated fields is known.

Please let us know your use case and thoughts on which implementation would best align with your goals and we’ll make the changes and issue a new release in the next week.

Hi @seth ! Thanks a lot for the reply and the willingness to help. :slight_smile:

I think option 2 is most appropriate as it seems to be the most performant and general, so I can make any casting or column separation in a later stage.

We will start working on option 2. @Dimitris_Staratzis will do the implementation and provide you with a status update next week.

Hi @vkaramanis,
We have implemented a working prototype that retrieves the data correctly as a blob. We could not yet cast this data as a float or any other datatype in MariaDB itself but the bytes are correct and they work as expected if manipulated outside MariaDB.

Example of casting we tried: CAST(SUBSTRING(attribute, 1, 4) AS FLOAT)

I will come back early next week on this.

1 Like

Hi @vkaramanis, we have released TileDB-MariaDB 0.22.2. All fixed-length multi-value attributes will now be treated as blobs and will return a sequence of bytes.


Thanks very much guys!!

I will test it today and get back to you if anything pops up.

1 Like