How to connect MATLAB on windows OS to Clickhouse database server on a cloud Linux virtual machine?

Hi,

My computations are stored in a Clickhouse databse server on a Linux (Ubuntu) virtual machine (VM). I would like to access the data from the Clickhouse server on the VM to MATLAB in my local machine on windows OS, so that I can process the data locally.

I tried to explore the database tool box, but since I am new to this, I haven't been able to connect to the database on the VM.

I would appreciate pointers from the experts.

Thanks in advance.

Answer 1

[Tejas]

Hello Usman,

It seems you are interested in finding a way to connect a ClickHouse database to MATLAB. To achieve this, a suitable JDBC driver for the ClickHouse database is needed. Once the driver is installed, connect it to MATLAB using the “javaaddpath” command. For more detailed instructions on this command, please refer to the following documentation: https://www.mathworks.com/help/matlab/ref/javaaddpath.html .

Now, create a data source (which acts like a connection object), using ‘databaseConnectionOptions’ command. It is worth noting that the ClickHouse Database is not directly supported by the MATLAB Database Toolbox, so set the ‘vendor’ property to 'other'. For more information on this, please consult the following documentation link: https://www.mathworks.com/help/database/ug/databaseconnectionoptions.html#:~:text=vendor%20%E2%80%94%20Database%20vendor%0A%22Microsoft,vector%20or%20a%20string%20scalar.

Next, configure the options of data source by providing:

• Path to the JDBC driver
• Username
• Password
• Database name
• IP address of the virtual machine.

Here is an example that demonstrates how this is done: https://www.mathworks.com/help/database/ug/databaseconnectionoptions.html.

Once the connection is established, ‘fetch’ command can be used to access data from the database. For guidance on how this is accomplished, here is a link to the documentation: https://www.mathworks.com/help/database/ug/database.odbc.connection.fetch.html.

Hope it helps!

Answer 2

[Usman Tahir Virk]

Thanks for your help and providing detail steps, Tejas.

However, I am still stuck with the step#1, i.e., the JDBC driver. I downloaded the clickhouse-jdbc-0.6.0-patch5-all.jar file using the following link.

Releases · ClickHouse/clickhouse-java (github.com)

However, this doesn't seem to work. Please see the snippets below. In the first one, I have used Microsoft MySQL Server option, as the fields were more easy for me to understand and provide input. For the second one, I don't understand what should I put in the highlighted field.

Any further pointers? Thanks again.