Quick tip: Connecting DBeaver to SingleStoreDB

Abstract

There are many ways to interact with SingleStoreDB Cloud, ranging from the built-in web-based SQL Editor to a locally installed MySQL client. Another option is to use a popular database tool called DBeaver. We can set up a connection from DBeaver to SingleStoreDB Cloud in a few minutes. In this article, we'll see how.

Introduction

For this article, we'll use the Community Edition of DBeaver installed in a Linux environment.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use Iris Demo Group as our Workspace Group Name and iris-demo as our Workspace Name. We'll make a note of our password and host name.

Connect to SingleStoreDB Cloud

With a fresh copy of DBeaver installed and launched, we'll be presented with a Connect to a database wizard. From the list of database products shown, we'll select MariaDB and then click Next.

On the Connection settings page, we just need to modify the following:

1. Server Host: Replace localhost with the host name from SingleStoreDB Cloud.
2. Username: Replace root with admin.
3. Password: Enter the password created in SingleStoreDB Cloud.

Next, we'll click Test Connection …

If there is a message that the MariaDB driver files are missing, we'll download them using the Download button.

We should see a message that the connection was successful.

Next, we'll click Finish.

Create a database and table

From the top menu bar in DBeaver, we'll select SQL Editor > Open SQL console.

We'll now type in some SQL commands and execute them. First, let's create a database:

CREATE DATABASE IF NOT EXISTS iris_demo;

Next, let's create a table:

USE iris_demo;

DROP TABLE IF EXISTS iris;
CREATE TABLE IF NOT EXISTS iris (
    sepal_length FLOAT,
    sepal_width FLOAT,
    petal_length FLOAT,
    petal_width FLOAT,
    species VARCHAR(20)
);

Load CSV data into a table

We can quickly populate our database table using an external file. We'll use a CSV file containing the Iris flower data set.

If we look in the left-hand navigation pane, we should see the following type of structure:

⌄ Connection String
  ⌄ Databases
    ⌄ iris_demo
      ⌄ Tables
        ⌄ iris

If the iris_demo database is not visible in the list, we can Refresh (F5) the view.

Right-clicking on the iris table, there will be an option to Import Data. Selecting this option will start an import wizard that will walk us through the steps to load the CSV data. In a few seconds, we'll have the data loaded. We can confirm that the data were correctly loaded by running a few queries on the iris table, as follows:

USE iris_demo;

SELECT COUNT(*) FROM iris;

SELECT * FROM iris LIMIT 5;

Summary

DBeaver is a popular database tool, and we can quickly connect this to our SingleStoreDB Cloud environment. The tool also provides a data import wizard that allows us to easily populate a database table.