CODEWITHSUNDEEP
sqlstoragewav
ERJAN
ERJAN

Reputation: 24500

what type of sql db can store sound or array data in a column?

I need to attach, create a new column in a sql db. It should store the numeric numpy representation of a user's voice - numpy array or an image - spectrogram. numpy array looks like this:

array([[  2.891e-07,   2.548e-03, ...,   8.116e-09,   5.633e-09],
       [  1.986e-07,   1.162e-02, ...,   9.332e-08,   6.716e-09],
       ...,
       [  3.668e-09,   2.029e-08, ...,   3.208e-09,   2.864e-09],
       [  2.561e-10,   2.096e-09, ...,   7.543e-10,   6.101e-10]])

I know sql can only store text data, wonder if it s possible to attach or represent shortly the array in a column.

ID |    date     | voice
1    01-01-20      array([[  2.891e-07,   2.548e-03...
2    01-02-20      array([[  2.891e-07,

Upvotes: 1

Views: 1113

Answers (1)

aLuViAn
aLuViAn

Reputation: 362

This depends on your database engine. I give you the answers to 3 of the most common databases I've worked with :

1. Oracle :

You can use BLOB datatype to store the binary files themselves. Also, if you want to store the array instead of binary file, you can use CLOB datatype as well. Similar to BLOB, CLOB holds big data, but in character format, so its suitable to hold big texts, such as arrays you've mentioned.

Oracle Large Objects

2. MySQL :

The same datatype, BLOB can be used in MySQL as well. TEXT (and its derivatives such as LARGETEXT) datatype can be used to hold the big array.

MySQL Large Objects

3. PostgreSQL :

PgSQL does not have BLOB, but another datatype, called BYTEA can store binary objects. Also TEXT datatype can store large strings in PgSQL.

PostgreSQL Large Objects

NOTE : Working with binary data in databases requires a mid-tier application to act as a 'translator' between client and server. Get more info in the reference links above.

Also, about the "Shortened Version" of the data in a column, you can insert the first 10 characters (or any arbitrary number that works for you) inside the table. This has different techniques and procedures, depending on your database engine.

Hope this has answered your question.

Upvotes: 1

Related Questions