The Guide for Time Series Data Projects is out.

Download now
Skip to content
Blog

Use CrateDB With DataGrip, an Advanced Database IDE

This blog post has originally been posted on September 25th, 2018 by Naomi Rose and has been updated by Rafaela Sant'ana.

DataGrip is a cross-platform database IDE (Integrated Development Environment) that is tailored to suit the specific needs of professional SQL developers.

With the help of the PostgreSQL JDBC Driver, you can use DataGrip with CrateDB.

In this post, I will show you how to get set up with CrateDB and DataGrip as a desktop client for macOS, but these instructions should be adaptable for Linux or Windows.

Installing CrateDB

In this tutorial, I use the Ad-hoc method to install CrateDB, following this step-by-step tutorial. I download CrateDB (version 4.6.1) and unpack it. In the terminal, I navigate to the unpacked CrateDB root folder with the command
cd /crate-4.6.1

and run a single-node instance from CrateDB with
./bin/crate

Other installing options (e.g. Docker) are described in the CrateDB Installation Tutorial.

Open http://localhost:4200 in your browser to connect to the CrateDB Admin UI:

CrateDB AdminUI
Getting some data

For this tutorial, I’ll use the famous Iris Dataset. It was used in R.A. Fisher's classic 1936 paper and includes data about three iris species with 50 samples each as well as some properties about each flower.

Firstly, the dataset must be retrieved. Download the Iris dataset CSV file and save it to a folder on your machine. You can now make a table for our dataset from the CrateDB AdminUI.

In the AdminUI, navigate to the console on the left side and create a table with the same column names as in the Iris CSV file we just retrieved.

  • Attention! To import csv data to CrateDB, the csv’s column names must be exactly the same as in the newly created CrateDB table. CrateDB is case-sensitive when attributes are declared between quotes, else not. For this reason, it is important to create the table on CrateDB with camelcase column names between quotes to import the csv data successfully.
CREATE TABLE iris (
  "Id" INT,
  "SepalLengthCm" DOUBLE,
  "SepalWidthCm" DOUBLE,
  "PetalLengthCm" DOUBLE,
  "PetalWidthCm" DOUBLE,
  "Species" TEXT
);

CREATE TABLE CrateDB AdminUI

Then, copy the CSV file into the iris table by passing the <pathname> to where the file was saved in your machine (between single quotes).
COPY iris FROM '<pathname>/Iris.csv';

COPY iris FROM CrateDB AdminUI

The iris table can now be found in the tables section from the AdminUI. You can visualize the data by clicking on QUERY TABLE.
CrateDB AdminUI Table Schema

 

SELECT Statement CrateDB AdminUI

Everything is set to now connect to CrateDB from DataGrip.

Get DataGrip running

Head on over to the DataGrip page and select Download to start your trial.

I'm using macOS, so once the download is completed, I dragged the application icon to my Applications folder.

When you open DataGrip for the first time, you will be prompted to import settings, and then you will be prompted to activate the software. For now, you can select Evaluate for free and then Evaluate.

After being prompted to configure some initial settings, you should see something like this:

CrateDB & Datagrip: After Datagrip Software Activation
Installing the PostgreSQL-JDBC Driver

First, download the latest PostgreSQL-JDBC jar file.

Now, select the Database tab from the left-hand side. You should see a side-panel open up, like this:

CrateDB & Datagrip: Sidepanel

Then, select the + icon, then Driver, like so:

CrateDB & Datagrip: Select + and Driver

The following screen should come up:
CrateDB & Datagrip: Select the Driver

Click on the + icon on the top left-hand side to create a new User Driver:

CrateDB & Datagrip: Create a new User Driver

From here:

  1. Name the driver CrateDB
  2. Use the + icon for the Driver Files section to add the PostgreSQL-JDBC .jar file you previously downloaded
  3. Select the appropriate class name from the Class dropdown menu, which should be prepopulated

You should end up with something that looks like this:
CrateDB & Datagrip: Select the appropriate Class

Select OK.

Connecting to CrateDB

Now, you can add a CrateDB data source:

CrateDB & Datagrip: Add CrateDB as a data source

On the screen that comes up, configure the data source like so:

Name: CrateDB

User: crate

URL: jdbc:postgresql://localhost:5432/

13-Datagrip

Select OK.

Then, right-click on CrateDB and navigate to New → Query Console

Datagrip & CrateDB: Query Console

And write a simple query statement, for example:
SELECT * FROM iris;

CrateDB & Datagrip: select * from iris;

Click on the green run button to run the statement, and an output window will pop up, displaying the iris table we created:

Datagrip & CrateDB: Output window

And from here, you can continue to explore the features of DataGrip.

Wrap Up

If you're looking for a fully-featured database IDE that works with CrateDB, DataGrip provides the goods. Explore your database, write queries using a powerful query interface, run queries, import and export data, and much more.

Don't forget to check out the other clients that work with CrateDB.