First use

Once CrateDB is installed and running, you can start to interact with the database for the first time. Follow this tutorial for a brief guided tour.

Table of contents

Admin UI

CrateDB ships with a web administration user interface (UI), or admin UI for short.

The CrateDB admin UI runs on every CrateDB node, and you can use it to inspect and interact with the whole CrateDB cluster in a number of ways.

We will use the admin UI throughout this section.

Access the admin UI in your browser using a URL like this:

http://localhost:4200/

If CrateDB is not running locally, replace localhost with the hostname CrateDB is running on.

You should see something like this:

_images/admin-ui.png

Import some tweets

In the admin UI, select the Help tab (question mark icon) from the left-hand navigation menu.

You should see something like this:

_images/admin-ui-help.png

Select IMPORT TWEETS FOR TESTING and follow the instructions to authenticate your Twitter account.

Tip

Don’t worry. This isn’t going to post anything on your behalf. All this does is import a selection of recent public tweets on Twitter.

Leave this running and stop it when you have enough tweets. Aim for at least few thousand.

When you’re done, select the Tables icon from the left-hand navigation.

You should see something like this:

_images/admin-ui-tweets.png

Query your tweets

CrateDB uses regular SQL for queries.

One of the easiest ways to query CrateDB is with the query console in the CrateDB admin UI.

Open up the admin UI.

Select the Console tab from the left-hand navigation menu.

You should see something like this:

_images/admin-ui-console.png

In the previous document, we imported some tweets from the public Twitter timeline.

If you want know more about the tweets table, select the Tables tab from the left-hand navigation menu. This screen will show you the table schema.

For an example query, why don’t we filter those tweets so that we’re only looking at tweets from people with more than 100 followers. We can do that with a query like so:

SELECT *
  FROM tweets
 WHERE account_user['followers_count'] > 100
 LIMIT 100;

The tweets table has a column named account_user. The account_user is an object, and can be queried into by specifying object attributes. In the query above, we’re matching rows where the followers_count attribute of this object is more than 100.

Type this query into the admin UI query console. Then select EXECUTE QUERY.

You should see something like this:

_images/admin-ui-results.png

CrateDB’s SQL support includes distributed aggregation, scalar functions and geospatial support for more complex queries and data analysis. CrateDB also comes with fulltext search.

See also

Consult the CrateDB query reference for documentation on the full range of query capabilities.

Crash

The CrateDB shell (aka Crash) is a command-line shell.

First, you must install Crash.

Then, you can start the shell like so:

sh$ crash

This will automatically connect to CrateDB running on localhost.

From here, you can execute queries against CrateDB by typing them and hitting Enter.

Let’s try something new.

Type out the following query:

  SELECT account_user['followers_count']
    FROM tweets
ORDER BY account_user['followers_count'] DESC
   LIMIT 10;

As you type, you may notice that Crash comes with autocompletion.

Here, we’re ordering by follower count from highest to lowest, and then selecting the top 10 values.

Hit Enter.

You should see something like this:

+---------------------------------+
| account_user['followers_count'] |
+---------------------------------+
|                         1416583 |
|                         1076527 |
|                         1025614 |
|                          886577 |
|                          854054 |
|                          818439 |
|                          795778 |
|                          761014 |
|                          740071 |
|                          673962 |
+---------------------------------+
SELECT 10 rows in set (0.003 sec)

The CrateDB HTTP endpoint

CrateDB provides a HTTP endpoint.

Tip

If you’re not familiar with HTTP APIs, feel free to skip this section.

Let’s run another query:

SELECT COUNT(*) FROM tweets

This time, all we want to know is how many tweets we have.

Using HTTPie, you can run this query against CrateDB listening on localhost:4200, like so:

sh$ http localhost:4200/_sql stmt="SELECT COUNT(*) FROM tweets"

CrateDB will respond using JSON. You should see something like this:

HTTP/1.1 200 OK
content-length: 71
content-type: application/json; charset=UTF-8

{
    "cols": [
        "count(*)"
    ],
    "duration": 11.847271,
    "rowcount": 1,
    "rows": [
        [
            3879
        ]
    ]
}

In this example, we have a single row with a single column: 3879.

Next steps

Start building with CrateDB clients and tools.

See also

Generate time series data (tutorials for multiple languages)