Forem: Carlota Soto

Build a data ingestion pipeline using Kafka, Flink, and CrateDB

Carlota Soto — Mon, 10 May 2021 13:39:05 +0000

This tutorial explains how to build a data ingestion pipeline using three open-source tools: Apache Kafka, Flink, and CrateDB.

Kafka is the front line of the stack, used to queue messages received from (for example) IoT sensors and devices. CrateDB will query and store the data. And between CrateDB and Kafka, it lives Apache Flink, a data processing engine. These three tools are all distributed systems that provide elastic scaling, fault tolerance, high-throughput, and low-latency performance via parallel processing.

This tutorial uses Kafka 6.1.1, Apache Flink 1.12, and CrateDB 4.5.0 on a macOS system.

Preliminary notes

This guide references the example job published at https://github.com/crate/cratedb-flink-jobs. This example job brings together three software components: the Kafka connector for Flink, the JDBC connector for Flink, and the CrateDB JDBC driver. It uses a sample dataset including a subset of trip records completed in NYC taxis during 2017. Explore the repository for more insights into it.
This guide uses Docker with Docker Compose. Please, make sure you're using recent versions, like Docker 20.10.5 and Docker Compose 1.29.0, so the infrastructure sandbox can be invoked successfully.
Apart from Docker, this guide assumes you have Git, Homebrew, and Wget installed. If you don't have/don't want to install these components in your machine, you can always use alternatives, but the steps on this guide will follow more smoothly if you have them installed.
CrateDB uses a mmapfs directory by default to store its indices. In a real use case, the default operating system limits on mmap counts are likely to be too low, which may result in out of memory exceptions. If you have to, you can increase the limits by running the command sudo sysctl -w vm.max_map_count=262144. To set this value permanently, update the vm.max_map_count setting in /etc/sysctl.conf. You can verify it after rebooting by running sysctl vm.max_map_count.
This guide is built so you can run the complete suite of technologies locally. It does not address aspects like high-availability, fault-tolerance, or scalability.

Starting Kafka, Flink and CrateDB

The simplest possible way to setup and start all software components at once is to use Docker with Docker Compose. To do so, first set up a sandbox directory and navigate to it with your terminal:



# Set up a directory to host your containers
mkdir -p sandbox/kafka-flink-cratedb

# Navigate to it
cd ./sandbox/kafka-flink-cratedb/

Next, start Kafka, Flink and CrateDB through docker-compose:



# Clone the repository cratedb-examples
git clone https://github.com/crate/cratedb-examples

# Navigate to the kafka-flink directory 
cd cratedb-examples/spikes/kafka-flink

# Start the containers 
docker compose up

Once everything is ready, you will be able to access both Flink and CrateDB through their graphical user interfaces.

To access the CrateDB UI, open http://localhost:4200/ in your browser:

To access the Apache Flink UI, open http://localhost:8081/ in your browser:

Communicating with Kafka through Kafkacat

To communicate with Kafka, you can use Kafkacat, a command-line tool that allows to produce and consume Kafka messages using a very simple syntax. It also allows you to view the topics' metadata.

You can install Kafkacat through Homebrew. To do so, use:



brew install kafkacat

For some quick examples of how to use Kafkacat, take a look at the commands below. If you want to dive deeper, this post summarizes the basic Kafkacat parameters very well.



# List all the Kafka topics and partitions 
kafkacat -L -b localhost:9094

# Write a message
export MESSAGE="The quick brown fox jumps over the lazy dog"

# Publish message to the topic test
echo $MESSAGE | kafkacat -b localhost:9094 -P -t test

# Consume messages from the topic test
kafkacat -b localhost:9094 -C -t test -o end

Get some data

As we mentioned in the preliminary notes, in this guide we will be using a small subset (with 5000 records) of a public dataset that includes information about NYC taxi trips published by the NYC Taxi and Limousine commission.

To obtain the sample data, use:



# Acquire the NYC taxi dataset in JSON format
wget https://gist.github.com/kovrus/328ba1b041dfbd89e55967291ba6e074/raw/7818724cb64a5d283db7f815737c9e198a22bee4/nyc-yellow-taxi-2017.tar.gz

# Extract archive
tar -xvf nyc-yellow-taxi-2017.tar.gz

# Create a subset of the data (5000 records) for the purpose of this tutorial

cat nyc-yellow-taxi-2017.json | head -n 5000 > nyc-yellow-taxi-2017-subset.json

Create a Kafka topic

To create the Kafka topic that will eventually ingest the data we just downloaded, run the code below. It creates a topic called "rides".



docker run -it --network=scada-demo confluentinc/cp-kafka:6.1.1 \
    kafka-topics --bootstrap-server kafka-broker:9092 --create --replication-factor 1 --partitions 1 --topic rides

Invoking the Flink job

Before setting up the Flink job, we need to create a table in CrateDB to host the data. We will call it taxi_rides. Use this command:



docker run -it --network=scada-demo westonsteimel/httpie \
    http "cratedb:4200/_sql?pretty" stmt='CREATE TABLE "taxi_rides" ("payload" OBJECT(DYNAMIC))'

The table will show in the CrateDB UI:

Now, it's time to invoke the Flink job that subcribes to the topic rides. To do so, follow these steps:



# Acquire Flink job
VERSION=0.2
JARFILE="cratedb-flink-jobs-${VERSION}.jar"
wget https://github.com/crate/cratedb-flink-jobs/releases/download/${VERSION}/${JARFILE}

# Invoke Flink job
docker run -it --network=scada-demo --volume=$(pwd)/${JARFILE}:/${JARFILE} flink:1.12 \
    flink run --jobmanager=flink-jobmanager:8081 /${JARFILE} \
        --kafka.servers kafka-broker:9092 \
        --kafka.topic rides \
        --crate.hosts cratedb:5432 \
        --crate.table taxi_rides

The running job will show in the Flink UI:

Publish data to the Kafka topic

Now that we have everything set up, you are ready to publish the NYC taxi data into Kafka. Run:



# Subscribe to the topic to receive messages
docker run -it --network=scada-demo edenhill/kafkacat:1.6.0 kafkacat -b kafka-broker -C -t rides -o end

# Publish data to the Kafka topic
cat nyc-yellow-taxi-2017-subset.json | docker run -i --network=scada-demo confluentinc/cp-kafka:6.1.1 \
    kafka-console-producer --bootstrap-server kafka-broker:9092 --topic rides

You are all set! You can now query the data from the CrateDB UI. Keeping it simple:

Hope this was useful 🤙 See you around!

Questions? Ask the Crate.io engineers through our community page 💫

Exploring data with Jupyter Notebook, Pandas and CrateDB

Carlota Soto — Thu, 15 Apr 2021 15:07:20 +0000

In a previous post, I showed you how to pair CrateDB (an open-source database specialized in machine data) and Jupyter Notebook, an application that allows you to create and share documents containing live code. I also told you how to load two different sample datasets into CrateDB, one recording the position of the ISS and another containing information about taxi trips in NYC.

In this post, I'll play a little bit with the NYC taxi dataset using Pandas and Matplotlib. This will be a simple example, but you can take it from here and explore further what you can do with these tools.

Before continuing, make sure you take it from where we left it, i.e. with Jupyter Notebook running and the NYC taxi dataset loaded into CrateDB.

To start, let's import our libraries. We will be importing pandas and matplotlib. Open a new Jupyter Notebook and run the following code:

import pandas as pd

import matplotlib.pyplot as plt
from matplotlib import rcParams
plt.style.use ('ggplot')

If there's no error, you will see no return.

Now, let's query CrateDB from Jupyter. This is how you can do it:

limit = 1000000
sql_query = """
  SELECT total_amount, trip_distance FROM nyc_taxi_sample
LIMIT 1000000;
""".format(limit)
try:
    df_data = pd.read_sql(
    sql_query, 'crate://localhost:4200')
    display(df_data.head(5))
except Exception as error:
    print(error)

This query asks CrateDB for a table containing the price of each taxi trip and its distance, and with the help of pandas, we're defining a function called df_data with this result. Besides, we're asking to display a table with the first 5 values in our notebook.

If everything goes well, you will see such table showing up:

Next, let's see how our data looks like by building our first plot:

df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.show()

After running the command above, a price vs distance plot will display in your notebook:

We notice some interesting things; for example, there are some anomalies, most notably what appears to be negative trip distances. Datasets are rarely perfect... Especially those with millions of data points.

We can change our original query, so it only includes positive values in the return:

limit = 1000000
sql_query = """
  SELECT total_amount, trip_distance FROM nyc_taxi_sample
  WHERE total_amount> '0' AND trip_distance> '0'
LIMIT 1000000;
""".format(limit)
try:
    df_data = pd.read_sql(
    sql_query, 'crate://localhost:4200')
    display(df_data.head(5))
except Exception as error:
    print(error)

Again, if it's successful, you will see a table with the first five data points.

Let's plot the data again:

df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.show()

Now, let's zoom in a little, limiting our x axis to 75 and our y axis to 1000. You can do that like this:

df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.xlim([0,75])
plt.ylim([0,1000])
plt.show()

Let's zoom in even more:

df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.xlim([0,50])
plt.ylim([0,300])
plt.show()

Nice!

To finish with this little exploration, let's estimate the linear function describing the relationship both variables.

In order to do this, let's use SciPy:

import scipy.stats
slope, intercept, r, p, stderr = scipy.stats.linregress(df_data.trip_distance, df_data.total_amount)

Next, run the command below. It will return you a string with the equation of the regression line and the value of the Pearson correlation coefficient:

line = f'Regression line: y={intercept:.2f}+{slope:.2f}x, r={r:.2f}'
line

Something like this:

'Regression line: y=10.27+3.19x, r=0.75'

To end, if you want to show the regression line in a plot it together with our data, this is how you can do it:

fig, ax = plt.subplots()
ax.plot(df_data.trip_distance, df_data.total_amount, linewidth=0, marker='s', label='Data points')
ax.plot(df_data.trip_distance, intercept + slope * df_data.trip_distance, label=line)
ax.set_xlabel('trip_distance')
ax.set_ylabel('total_amount')
ax.legend(facecolor='white')
plt.show()

Hope this was fun!

Monitoring your database with Prometheus and Grafana

Carlota Soto — Mon, 12 Apr 2021 16:12:49 +0000

Prometheus is an open-source systems’ monitoring and alerting toolkit. It is very useful for monitoring machine metrics and service-oriented architectures, supporting multi-dimensional data collection and querying. If paired with Grafana, the open-source visualization tool that we introduced in this previous post, one can build very nice monitoring dashboards.

In this tutorial, we will show you:

How to set up CrateDB (the open source database we develop at Crate.io), Prometheus, and Grafana with docker-compose
Hoe to enable JMX monitoring in CrateDB
How to set up a monitoring dashboard in Grafana, giving you the option of importing a complete pre-built dashboard

We're using CrateDB 4.5.0, Prometheus 2.26.0 and Grafana 7.5.2.

Let's do it!

Starting Prometheus, Grafana and CrateDB with Docker (+ JMX monitoring)

To run our tools, we will be using Docker. We will build our containers by using docker-compose, which comes very handy to define and run multi-container Docker applications very quickly.

Docker-compose comes pre-installed with your Docker installation. If you don't have Docker installed, you can download it here — just follow the instructions that will pop-up.

Once your installation of Docker is complete, create a working directory and navigate there with your terminal. There, create a docker-compose.yml file with the following content:

version: "3.9"
services:
  cratedb:
    image: "crate"
    volumes:
      - ./crate-jmx-exporter-1.0.0.jar:/jmxdir/crate-jmx-exporter-1.0.0.jar
    ports:
      - "4200:4200"
      - "7071:7071"
    environment:
      CRATE_JAVA_OPTS: "-javaagent:/jmxdir/crate-jmx-exporter-1.0.0.jar=7071 -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.authenticate=false"
  prometheus:
    image: "prom/prometheus"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"
  grafana:
    image: "grafana/grafana"
    ports:
      - "3000:3000"

(You can create a .yml file through multiple methods. In macOS, you can open a terminal text editor like nano, saving the file with the .yml extension. You can also use a text editor.)

Now, let's move on to the next step. As we briefly mentioned in the introduction, to scrape CrateDB we're making use of the Java Management Extensions (JMX) and the CrateDB JMX monitoring feature.

To set it up, download the latest JMX monitoring .jar here. Click on /1.0.0; in this blogpost, I’m using the file called crate-jmx-exporter-1.0.0.jar. Then, move the .jar file into the working directory you created before.

Note: let's take a closer look at what's happening here. With the help of the .yml file we defined earlier, we are going to start three containers at the same time (CrateDB, Prometheus, and Grafana) and expose their relevant ports. Besides, we are including the JMXExporter.jar into the Docker container, by using the volumes directive. Then, we are using the -javaagent directive to enable the JMXExporter and configure it to use port 7071. The other arguments you see in the file are needed to fully enable JMX Monitoring.

One last configuration item. Before we can start Prometheus, we need a new configuration file for it. In order to do that, create a new .yml file named prometheus.yml in your working folder, and paste the following content in it:

global:
  scrape_interval: 15s
  scrape_timeout: 10s
  evaluation_interval: 15s
scrape_configs:
- job_name: prometheus
  honor_timestamps: true
  metrics_path: /metrics
  scheme: http
  static_configs:
  - targets:
    - cratedb:7071

Now, we are ready to start all the containers. Navigate to your working folder with the terminal and run the following command:

docker-compose up

Wait a few seconds for the process to end. When it finishes, you can access CrateDB, Prometheus, and Grafana.

👉 To connect to the CrateDB, open http://localhost:4200/ in your browser

👉 To connect to Prometheus, open http://localhost:9090/ in your browser

If you click on ”Status -> Targets”, you will see that CrateDB is already set up as an endpoint:

👉 To connect to Grafana, open http://localhost:3000/ in your browser

Note: if this is your first time using Grafana, first fill “admin” in both the username and the password field. You can define your credentials on the next screen.

Setting up a pre-built monitoring dashboard in Grafana

Now that we have all our tools ready, let’s set up a dashboard to monitor our cluster in Grafana using Prometheus as the data source.

In Grafana, go to “Configuration -> Data sources”:

Now, click on ”Add data source”:

Select ”Prometheus”:

A configuration page will show up. Fill up the following fields:

Name: Prometheus
Host URL: http://prometheus:9090/ You can leave all the other fields with the default configuration.

When you’re done, scroll to the end of the page, and click on “Save & Test”. If everything goes well, you’ll see a message saying “Data source is working”.

Now, let's set up our dashboard. On the left menu, click on "Create -> Dashboard":

You will see a screen like the one below. Click on "Add new panel". (Panels are the building blocks of Grafana's dashboards).

The configuration screen for your new panel will open up. Here, you can define all the elements of your panel, like its name, queries, type of visualization, and so on.

To learn about all the possibilities that Grafana offers, check out their documentation, and experiment by building your own panels.

In this tutorial, however, I am going to give you the option of importing a pre-built monitoring dashboard.

In order to do so, first download this JSON file.

Then, on the Grafana home page, click on “Create -> Import”:

Now, press on “Upload JSON file”, and select the JSON file you just downloaded. Then, click on “Import”.

Voilà! You now have a complete dashboard monitoring CrateDB in real-time 👏 Your panels show:

Queries per second
Average query duration over the last minute
Query error rate
GC rates
Number of shards
Circuit breaker memory in use
JMV memory in use

PS: This dashboard is similar to what we're using at Crate.io for monitoring real clusters in production. This is how it looks like for one of our customers:

Hope this was useful 😁

You can also find the Crate.io team at:

Github (We'd love a ⭐️ 🙏)
Twitter
Our community page

See you around 🐐

Pairing CrateDB with Jupyter Notebook

Carlota Soto — Thu, 08 Apr 2021 15:51:31 +0000

Jupyter Notebook is an open-source web application for creating documents that contain live code. It's a very nice tool for working on your data analysis projects, as it allows you to write lines of code, run them one at a time, make changes, and rerun them again. You can save segments of code, make them go back to a previous state, and share the notebooks with others.

In this post, will tell you how to pair CrateDB (an open-source, distributed SQL database) with a Jupyter Notebook.

Install Jupyter Notebook + the CrateDB SQL Alchemy driver

Jupyter can be easily installed through Anaconda, an open-source data science platform that includes many useful libraries. If you don't have Anaconda, you can install it here.

When you're done with the Anaconda installer, let's setup CrateDB in the Anaconda environment with the SQL Alchemy dialect. This will be useful if you want to use libraries like pandas in your Jupyter Notebook.

With your terminal, navigate to the bin directory inside your Anaconda files folder (it will be called something like "anaconda3"). There, run the command pip install -U "crate[sqlalchemy]".

In my case, the full command looked like this:

/anaconda3/bin/pip install -U "crate[sqlalchemy]"

Launch Jupyter Notebook

We'll launch Jupyter Notebook from the Anaconda Navigator. You can open it with this command:

anaconda-navigator

You may also see a desktop icon that you can double-click.

The Anaconda Navigator looks like this:

Now, launch "Jupyter Notebook".

It will open in your browser, at localhost:8888:

Install and launch CrateDB

Docker makes it very easy to get started with CrateDB. If you don't have Docker, you can install it here.

If you already have Docker running, create the directory where your CrateDB data will be stored. Then, navigate there with your terminal, and run:

docker run --rm --name=cratedb --publish=4200:4200 --volume="$(pwd)/var/lib/crate:/data" crate

Next, open http://localhost:4200/ in your browser. You will see the CrateDB Admin UI:

Load data into CrateDB

If you have your own dataset ready, you can load it to CrateDB using COPY FROM. However, having a fresh CrateDB install, you may need some sample data.. So I will show you how to download two different sample datasets: one containing data from the NYC cabs, and another with data about the position of the ISS. We will load this last dataset straight from our Jupyter Notebook, using Phython.

NYC taxi dataset

Open the CrateDB Admin UI. In the Console, paste the following code and click on “Execute query”. (This query creates a table where our data will live).

CREATE TABLE "nyc_taxi_sample" ( 
  "congestion_surcharge" REAL, 
  "dolocationid" INTEGER, 
  "extra" REAL, 
  "fare_amount" REAL, 
  "improvement_surcharge" REAL, 
  "mta_tax" REAL, 
  "passenger_count" INTEGER, 
  "payment_type" INTEGER, 
  "pickup_datetime" TIMESTAMP WITH TIME ZONE, 
  "pulocationid" INTEGER, 
  "ratecodeid" INTEGER, 
  "store_and_fwd_flag" TEXT, 
  "tip_amount" REAL, 
  "tolls_amount" REAL, 
  "total_amount" REAL, 
  "trip_distance" REAL, 
  "vendorid" INTEGER) 
WITH ("column_policy" = 'dynamic', "number_of_replicas" = '0', "refresh_interval" = 10000);`

To import the dataset, execute the query below. It can take up to 10 minutes for the data to be fully loaded. This dataset contains about 6 million records.

COPY "nyc_taxi_sample" FROM 'https://s3.amazonaws.com/crate.sampledata/nyc.yellowcab/yc.2019.07.gz' 
WITH (compression = 'gzip');

ISS dataset

Note: we have a tutorial in the CrateDB Docs explaining all the steps below in detail. Check it out if you like.

For importing this dataset, let's use a Jupyter Notebook.

First, start a new notebook. In the Jupyter Notebook UI (localhost:8888), click on "New -> Phyton 3" (in the right upper corner). This will open a tab with a blank notebook.

The first step is to import the requests library. Type the following command in your notebook, and run it by pressing "Run":

import requests

Now, let's read the current position of the ISS. Type this:

>>> response = requests.get("http://api.open-notify.org/iss-now.json")
>>> response.json()

Your return will be the postion of the ISS in latitude/longitude coordinates. Something like this:

Next, let's encapsulate this operation with a function that returns longitude and latitude as a WKT string:

>>> def position():
...     response = requests.get("http://api.open-notify.org/iss-now.json")
...     position = response.json()["iss_position"]
...     return f'POINT ({position["longitude"]} {position["latitude"]})'

When running this function, it will return your point string:

>>> position()

Now, let's import the crate client:

from crate import client

Next, connect to CrateDB:

connection = client.connect("localhost:4200")

And get a database cursor to execute queries:

cursor = connection.cursor()

Now, create a table in CrateDB to host the ISS coordinates:

>>> cursor.execute(
...     """CREATE TABLE iss (
...            timestamp TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP,
...            position GEO_POINT)"""
... )

You will see the table in the CrateDB Admin UI.

With the table in place, let's record the position of the ISS.

The following command calls the position function we defined earlier, insterting the result into the table we just created. Run it a few times to see several records.

>>> cursor.execute("INSERT INTO iss (position) VALUES (?)", [position()])

Now, SELECT that data back out of CrateDB:

>>> cursor.execute('SELECT * FROM iss ORDER BY timestamp DESC')

And fetch all the result rows at once:

>>> cursor.fetchall()

Now, automate the whole process. Paste and run the following code in your notebook:


import time

import requests
from crate import client


def position():
    response = requests.get("http://api.open-notify.org/iss-now.json")
    position = response.json()["iss_position"]
    return f'POINT ({position["longitude"]} {position["latitude"]})'


def insert():
    # New connection each time
    try:
        connection = client.connect("localhost:4200")
        print("CONNECT OK")
    except Exception as err:
        print("CONNECT ERROR: %s" % err)
        return
    cursor = connection.cursor()
    try:
        cursor.execute(
            "INSERT INTO iss (position) VALUES (?)", [position()],
        )
        print("INSERT OK")
    except Exception as err:
        print("INSERT ERROR: %s" % err)
        return


# Loop indefinitely
while True:
    insert()
    print("Sleeping for 10 seconds...")
    time.sleep(10)

Here, the script sleeps for 10 seconds after each sample. Accordingly, the time series data will have a resolution of 10 seconds. If you want to configure your script differently, feel free to do so!

You will see the script running, and the ISS data filling up into CrateDB.

Farewell to our Enterprise License

Carlota Soto — Tue, 23 Mar 2021 16:02:12 +0000

Starting with the release of CrateDB v4.5.0, in Crate.io we're saying farewell 👋 to our Enterprise License. Instead, we will be developing a single, open-source version of CrateDB licensed under Apache 2.0 from now on.

This means that CrateDB is (and will continue to be) completely free if you run it on your own premises.

You can download CrateDB v4.5 here, and also with Docker.

Why is Crate.io making this change?

CrateDB started out as an open-source project. Our database was built using open-source components from Elasticsearch, Lucene, and many others, and so it was a thrill for us to contribute back to the community.

However, in 2019 we decided to try an open-core approach, a model that was being widely adopted in the industry at the time. In theory, it allowed us to open new revenue streams while still offering an open-source variant to the community... But after operating this model for two years, we realized it was not for us.

To offer a feature-limited version of CrateDB as the open-source product faced us with continual contradictions in our development ethos, as we are open-source developers at heart. So we decided to go back to fully open source, leaving the open-core approach behind. This lets us focus all our efforts on a single, open-source version of CrateDB, available to anyone.

How will Crate.io build a successful business?

We know that the future is in the cloud. Daily, we see our customers' interests shift towards fully managed SaaS solutions, and we are confident this trend is here to stay. This new tech landscape gives companies like Crate.io the possibility to build a business without abandoning the open-source values.

For enterprise customers interested in an on-prem version of CrateDB with premium support, of course we will continue to offer it, including SLA’s and project and incident management. The same is true for partners that want to embed CrateDB into their stack, such as an IoT Platform or OEMs.

Is this decision ever going to change?

No, this decision is not going to change. (You can quote us).

Isn't Crate.io concerned about third parties (like cloud providers) potentially selling CrateDB as a service without contributing?

CrateDB wouldn’t be possible without the generosity of the open-source community. We celebrate third parties using our software for their own projects and commercial products, just as CrateDB has done with others.

When it comes to the possibility of other companies offering CrateDB as a service without significantly contributing back, we do not see this as a too significant problem. We are the originators and main contributors to the product, and we're confident that we will offer the best support and assistance to our users now and in the future.

I am a CrateDB Enterprise user. How does this change affect me?

You will not see any changes. Your commercial agreement with Crate.io is still valid, including our SLAs, technical support, hotfixes, incident management, roadmap discussions, on-demand training, rich onboarding, and regular check-ins, and consultative support.

I was already using the open-source version of CrateDB. How does this change affect me?

If you upgrade to CrateDB v4.5.0, you'll get the (previously called) Enterprise features:

Access to user management for multiple database users
Access to user privilege configuration
Database management with authentication
User-defined functions in JavaScript
Access to the following features in the CrateDB admin UI:
Access to the following system information functions:
Access to the hyperloglog_distinct aggregate function
Access to the following window functions:
- first_value(arg)
- last_value(arg)
- lag(arg [, offset [, default] ])
- lead(arg [, offset [, default] ]
- nth_value(arg, number)
- rank()
- dense_rank()
JMX monitoring

Apart from the former Enterprise features, what other changes are coming with CrateDB v4.5.0?

As well as the above features, CrateDB v4.5.0 comes with improvements in the following areas:

Performance optimization
PostgreSQL Tool Compatibility (added regclass data type, pg_tablespace table)
Better error messages
Added SQL support for "CREATE TABLE AS " statement

You can get CrateDB v4.5 here and also in Docker.

What is CrateDB? 🤔 FAQ (2)

Carlota Soto — Thu, 11 Mar 2021 15:11:56 +0000

👉 Can I store large data volumes in CrateDB?

CrateDB thrives in use cases in the range of hundreds of terabytes, being able to handle petabytes in some configurations.

👉 How do sharding and distribution work in CrateDB?

Every table in CrateDB is sharded, which means that data is divided and distributed across the nodes of a cluster. A unique internal identifier is assigned to each row; this identifier is used to rout the requests. The shard assignment never changes, even if the shard is moved from one node to another.

To assure consistency, an arbitrary number of replica shards can be configured per table, with every replica holding a fully synchronized copy of the primary shard. To make the cluster fault-tolerant, CrateDB automatically distributes replica shards across the cluster in a way that assures that there is always a complete copy of the data accessible, even if a certain number of failures occur.

Tables can also be partitioned. Partitions can be of different sizes, allowing a second dimension for scaling and moving data through the cluster.

👉 What is the average query response time of CrateDB?

This depends on the use case, but CrateDB aims for responses in the sub-second time frame.

Primary-key lookups work really fast on CrateDB. In complex cases with a high number of nodes, the right data optimization strategy can reduce delays considerably. And to increase performance with high cardinality data, we implemented approximate functions like HyperLogLog.

👉 How querying works in CrateDB?

Real-time databases often require all data to fit in the main memory, limiting the amount of data that can be managed. But to achieve real-time performance without data volume limitations, CrateDB implements memory-resident columnar field caches for each shard.

The caches tell the query engine whether there are rows on that shard that meet the query criteria and where the rows are located, all performed at in-memory speed.

Besides, CrateDB implements a query planner that decides which nodes are best suited to finalize the processing of aggregations and joins.

👉 Is CrateDB a good choice for use cases with frequent parallel queries?

Yes, CrateDB presents excellent performance with multiple concurrent users.

👉 Does CrateDB offer ACID transactions?

No, CrateDB does not focus on ACID. In order to prioritize data availability, CrateDB implements an eventual consistency model, allowing it to operate with high performance even in complex multi-node operations with parallel queries.

👉 How does eventual consistency work?

CrateDB is built for applications that require the handling of “fire-hoses” of data, i.e. high volumes of staple data that need to be queried intensively. In many of these applications, strong consistency is not a key requirement: efficiency and performance are.

This is the reason why CrateDB was built with an eventually consistent, non-blocking data insertion model. This eliminates the OLTP locking overhead traditionally found in other databases, leading to a significant improvement in performance.

To eventually assure consistency, CrateDB includes record versioning, optimistic concurrency control, and a table-level refresh frequency setting, which forces the cluster to become consistent after a certain number of milliseconds. Making this compromise maximizes data availability, resulting in the great response of CrateDB with challenging workloads.

Queries retrieving a specific row by its primary key are an exception. These are fully consistent, always receiving the most recent row (read-after-write consistency). All the other queries in CrateDB will return eventually consistent data.

👉 If CrateDB is eventual-consistent, how it assures atomicity and durability?

CrateDB implements WAL (write-ahead logging) to ensure transactional atomicity, data durability, and to reduce disk overhead.

WAL guarantees a few things:

Operations on rows (internally stored in CrateDB as JSON documents by the Lucene shards) are atomic. This means that a write operation on a row either succeeds as a whole, or has no effect at all.
Operations on rows are persisted to disk without having to issue a Lucene-Commit for every write operation. When the translog gets flushed, all data is written to the persistent index storage of Lucene, and the translog gets cleared.
In the case of an unclean shutdown of a shard, the transactions in the translog are replayed upon startup, to ensure that all executed operations are permanent.
The translog is also directly transferred when a newly allocated replica initializes itself from the primary shard.

👉 What about schemas?

Instead of implementing traditional relational schemas, which can be very painful to change, CrateDB’s schemas are very flexible:

CrateDB can handle INSERT statements including a column that wasn’t originally defined on the table. It can be configured to a) enforce the original schema by throwing an error, b) automatically update the schema by adding the new column, or c) just storing the plain JSON value.
Internally, every relational record in CrateDB is stored as a JSON document. They can be extended seamlessly, which gives CrateDB the flexibility to handle evolving data structures. JSON objects can be stored in “object” columns, which can have arbitrary numbers of attributes, nesting levels, and even arrays of objects.
As new attributes are added to objects, CrateDB can automatically add them to the schema, and they can be accessed by “object.attribute” in SQL queries.

This schema flexibility, combined with SQL, JSON & full-text search, is one of the main reasons why CrateDB customers choose to replace multiple databases (e.g., MySQL + Elasticsearch) with CrateDB.

👉 Does distribution work the same for reads and for writes?

With read operations, there is no difference between executing the operation on the primary shard or in any of the replicas.
However, write operations are handled differently than reads.

Write operations are synchronous over all the active replicas, with the following flow:

For the given operation, the affected primary shard and its active replicas are locked up in the cluster state. For this step to succeed, the primary shard and a quorum of the configured replicas need to be available.
The operation is then routed to the primary shard for execution.
If the operation succeeds on the primary, the operation gets executed on all replicas in parallel.
Once all replica operations finish, the result gets returned to the caller.
If any replica shard fails to write the data, or if it times out in step 4, the data it’s immediately considered as unavailable.

All these operations are managed internally by CrateDB. Data rebalancing, replication, and distribution are automatic.

👉 Does CrateDB support full-text search?

Yes, CrateDB integrates Lucene-powered full-text search. Check out our docs for more info.

👉 Is CrateDB compatible with Telegraf/Prometheus?

Yes. CrateDB supports the Postgres protocol, which makes it compatible with all the common data interfaces for Postgres.

👉 How can I access CrateDB?

We offer the following client interfaces:

PostgreSQL wire protocol
Web administrator user interface (Admin UI)
Crash, an interactive command line interface (CLI)
JDBC
DBAL
PDO
Python client
Plugin for Npgsql

There's nothing better than trying things by yourself! Download CrateDB or sign up for a CrateDB Cloud free trial. Experiment... And tell us what you think 😁

Apart from Dev.to, you can reach to the Crate.io team in:

Github (We'd love a ⭐️ 🙏)
Twitter
Our community page

See you around 🐐

Visualizing time series data with Grafana and CrateDB 📈

Carlota Soto — Tue, 09 Mar 2021 03:13:19 +0000

Grafana is an open-source tool that helps you build real-time dashboards and graphs. It is the perfect complement to CrateDB, which is purpose-built for monitoring large volumes of machine data in real-time.

In this tutorial, I will show you how to:

Install CrateDB on-prem / Sign up for CrateDB Cloud
Load a sample dataset
Install Grafana
Connect Grafana to CrateDB or CrateDB Cloud
Build your first Grafana dashboard
If you already have CrateDB or CrateDB Cloud installed with data loaded into it, jump to the "Connect Grafana to CrateDB" section to get straight to the point.

Let’s get started!

Installing CrateDB / Signing up for CrateDB Cloud

If you want to use CrateDB on-prem, an easy way to do so is with Docker. If you don't have Docker, you can download it here — just follow the instructions that will pop-up. For alternative install options, check out our docs.

Once you have Docker installed, create a directory where your data will be stored. Navigate there with your terminal, and run the following command:

docker run --rm --name=cratedb --publish=4200:4200 --publish=5432:5432 --volume="$(pwd)/var/lib/crate:/data" crate

Then, open http://localhost:4200/ in your browser. You will see the CrateDB Admin UI:

If you prefer to use CrateDB Cloud, you can sign up for free trial. For instructions on how to deploy a free-trial cluster, click here. You'll be done in a few minutes, no credit card required.

Loading a sample dataset

In this tutorial, I’ll use a sample dataset that includes data published by the NYC Taxi & Limousine Commission—a reduced-size version of the NYC dataset we've used in other posts. This is a great dataset to learn Grafana, as it includes a wide variety of information while loading fast into CrateDB.

The process of importing the dataset is equivalent for both CrateDB on-prem and CrateDB Cloud. If you're using the latter, you can access the CrateDB Admin UI by clicking on "Open cluster administration" in your cluster "Overview" page:

You will be asked the username and password associated with your cluster (the 24-character password). If you don't remember those credentials, you can check out your username (and change your password) by clicking on "Settings".

Once you introduce your credentials, the CrateDB Admin UI will pop-up:

The first step to importing the data is creating a table. In the CrateDB Console, type the following code, and click on "Execute query":

CREATE TABLE "nyc_taxi_sample" (
  "congestion_surcharge" REAL,
  "dolocationid" INTEGER,
  "extra" REAL,
  "fare_amount" REAL,
  "improvement_surcharge" REAL,
  "mta_tax" REAL,
  "passenger_count" INTEGER,
  "payment_type" INTEGER,
  "pickup_datetime" TIMESTAMP WITH TIME ZONE,
  "pulocationid" INTEGER,
  "ratecodeid" INTEGER,
  "store_and_fwd_flag" TEXT,
  "tip_amount" REAL,
  "tolls_amount" REAL,
  "total_amount" REAL,
  "trip_distance" REAL,
  "vendorid" INTEGER)
WITH ("column_policy" = 'dynamic', "number_of_replicas" = '0', "refresh_interval" = 10000);

Now, import the dataset. Execute this query:

COPY "nyc_taxi_sample" FROM 'https://s3.amazonaws.com/crate.sampledata/nyc.yellowcab/yc.2019.07.gz'
WITH (compression = 'gzip');

The data is now loading into CrateDB. Click on "Tables"; you will see the table you just created ("nyc_taxi_sample") getting filled. In my case, it took less than 10 minutes to get the 6 million records.

Before moving on to the next section, let's explore our table. Run this simple query:

SELECT * FROM nyc_taxi_sample
LIMIT 10;

Scroll to the right to get familiar with the columns in the table, which display information about the NYC taxi rides. Some examples:

pickup_datetime: this column shows the pickup date and time for each taxi ride recorded as a timestamp
trip_distance: the ride's distance in miles
passenger_count: the number of passengers present in each ride
total_amount: the price of the ride, in USD

Installing Grafana

To install Grafana, follow the instructions on their website. There are multiple options; in my case, I used the standalone MacOS installation.

curl -O https://dl.grafana.com/oss/release/grafana-7.4.3.darwin-amd64.tar.gz 
tar -zxvf grafana-7.4.3.darwin-amd64.tar.gz

If you’re following this method too, once you've copied Grafana to your preferred directory, install and start its local web server with the following command:

./bin/grafana-server web

Now, access Grafana by opening http://localhost:3000/ in your browser:

In the previous screen, enter "admin" in both username and password. The screen below will pop up next, where you can set up a new password.

You're now in!

Connecting Grafana to CrateDB or CrateDB Cloud

If you just installed Grafana, click on "Add your first data source".

If you don't see the previous screen, you can also go to "Configuration -> Data Sources" in the left menu.

Here, click on "Add data source":

Once you get to a page like this, look for PostgreSQL. Select it.

You will get to a configuration screen.

If you are using CrateDB on-prem, enter the following values:

Name: CrateDB
Host: localhost:5432
Database: doc
User: crate
Password: leave it empty
SSL mode: disable

Once you've filled it up, click on "Save and test". If everything goes well, you'll see “Database Connection OK”:

If you are using CrateDB Cloud, the configuration will be the following:

Name: CrateDB Cloud
Host: to get this information, go to the CrateDB Cloud Console. In the cluster "Overview" page, click on "Learn how to connect to the cluster".

A screen like this will display:

To create a Grafana connection, use the psql client. In this case:

my-cluster.aks1.eastus2.azure.cratedb.net

Database: doc
User and password: these are the credentials to access your CrateDB Cloud cluster (the 24-character password). Remember that they will show under "Settings" on the cluster "Overview" page
SSL mode: require

Once you have everything ready, your configuration page will look like this:

Click on "Save and test". If everything goes well, you'll see a "Database connection OK" message pop up.

Building a dashboard in Grafana

Now we're ready for the final step: let's build a dashboard in Grafana.

If you're new to Grafana, you'll see the screen below. Click on "Dashboards" in the menu on the left. Then, click on "Create your first dashboard":

If you don't see the previous option, you can also create a dashboard under "Create -> Dashboard" on the left menu.

Either way, you will get to a screen like the one below.

In Grafana, dashboards are composed of individual blocks called panels, to which you can assign different visualization types and individual queries. Click on "Add new panel".

In the next screen, we will define the query for your panel, the type of visualization (like graphs, stats, tables, or bar charts), and the time range. I won't get into all the options in the panel editor here, but I recommend you explore the menu on the right to start getting familiar with it. Also, check out the Grafana documentation.

We will start by defining our query. Click on "Edit SQL":

A console will open up where you can write your SQL code.

To build this panel, let's plot the number of rides per day in the first week of July 2019.

Paste this query in the SQL editor:

SELECT date_trunc('day', pickup_datetime) AS time,
COUNT(*) AS rides
FROM nyc_taxi_sample
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-07T23:59:59'
GROUP BY 1
ORDER BY 1;

Note: something important to know about the "Time series" format mode in Grafana is that your query needs to return a column called "time". Grafana will identify this as your time metric, so make sure the column has the proper datatype (any datatype representing an epoch time). In this query, we're labeling pickup_datetime as "time" for this reason.

This is how it looks like:

Now, set up the following configuration:

On the top of the panel, select the appropriate time range for your panel—in this case, from July 1st to July 7th, 2019:

Under "Settings" on the right, define the name of your panel.
Under "Visualization", select "Graph".
Under "Display", select "Bars".

Click on "Apply" on the right side of the screen. Once everything is ready, your graph will show up:

The number of rides per day fluctuates between 100k and 200k, with July 2nd being the busiest day of the week.

Click on the arrow on the top left of the screen to go back to your dashboard. It now contains its first panel:

Before wrapping up, let's add a couple more panels to it. For example, let's include the following information:

How many taxi rides took place during the week?
What was the average distance per ride per day?

To add more panels to your dashboard, click on "Add panel" at the top of the screen.

Now that you get the basics of panel building, I will go through these a bit quicker, describing only the key steps:

Panel 2: How many rides took place during the first week of July?

For building this panel, paste this query in the SQL editor:

SELECT COUNT(*) AS total_rides
FROM nyc_taxi_sample
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-07T23:59:59';

And under "Visualization", select "Stat" as your panel type. It will look like this:

Panel 3: What was the average distance per ride per day?

For building this panel, paste this query in the SQL editor:

SELECT
date_trunc('day', pickup_datetime) AS time,
COUNT(*) as rides,
SUM(trip_distance) as total_distance,
SUM(trip_distance) / COUNT(*) AS average_distance_per_ride
FROM nyc_taxi_sample
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-07T23:59:59'
GROUP BY time
ORDER BY 1;

With a little bit of formatting, and after selecting "average_distance_per_ride" at the bottom of the graph (it will show the three columns returned by the query above), this is how the panel looks like:

Our average distance per trip ranges around 3 miles on average.

Our dashboard now looks a little bit more complete! If you want to keep building it, check out this blogpost for inspiration, where we use SQL queries to explore the NYC taxi data. You can also customize the look of your dashboard by dragging and dropping the panels, making them larger... Go to the Grafana documentation for more.

Don't forget to click on the "Save dashboard" button before you go.

There's nothing better than trying things by yourself! Download CrateDB or sign up for a CrateDB Cloud free trial. Experiment... And tell us what you think 😁

Apart from Dev.to, you can reach to the Crate.io team in:

Github (We'd love a ⭐️ 🙏)
Twitter
Our community page

See you around 🐐

Querying time series data with SQL: examples

Carlota Soto — Mon, 01 Mar 2021 20:47:53 +0000

(This content is based on a blogpost originally published in Crate.io)

When signing up for CrateDB Cloud, you can import the NYC taxi dataset from the console with only a couple of clicks. We like this dataset because it includes information from tens of millions of taxi trips, with multiple data types—including time-series data.

In a previous post, I showed you how to load the NYC taxi dataset into CrateDB Cloud. Now, I will give you some SQL queries you can try. This post will be especially useful if you are (like me) still a beginner in the world of SQL. 🤓

Let's get into it.

Assuming that you already have the full dataset imported, you now have about 40 million data records in your cluster. Let's start by taking a look at our table:

SELECT * FROM nyc_taxi
LIMIT 10;

The result:

If you scroll to the right, you will see all the information in your table: fare amounts, passenger numbers, tips and tolls paid...

To play around with your data, try the following queries:

1. How many rides happened per day during July 2019?

The SQL query:

SELECT
  date_trunc('day', pickup_datetime) AS day,
  COUNT(*) AS rides
FROM nyc_taxi
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-31T23:59:59'
GROUP BY 1
ORDER BY 1 LIMIT 100;

The result:

2. What is the total distance traveled during July 2019, as well as the average distance per trip?

The SQL query:

SELECT
  date_trunc('day', pickup_datetime) AS DAY,
  COUNT(*) AS rides,
  SUM(trip_distance) AS total_distance,
  SUM(trip_distance) / COUNT(*) AS average_per_ride
FROM nyc_taxi
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-31T23:59:59'
GROUP BY DAY
ORDER BY 1 LIMIT 100;

The result:

3. What are the busiest hours of the day?

The SQL query:

SELECT
  EXTRACT(HOUR FROM pickup_datetime) AS HOUR,
  COUNT(*),
  SUM(trip_distance) AS total_distance
FROM nyc_taxi
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-31T23:59:59'
GROUP BY HOUR
ORDER BY COUNT(*) DESC LIMIT 100;

The result:

4. How many unique pickup locations there are in the entire dataset?

With this one, things start getting a little bit more interesting.

For this query, you would normally do something like this:

SELECT COUNT(DISTINCT(pulocationid)) FROM nyc_taxi;

However, the amount of data is too large. If you try this, CrateDB Cloud will immediately trip a circuit breaker: this query can't be performed reasonably, without having to do a ridiculous full scan of the table.

The solution is to use a function called HYPERLOGLOG:

SELECT HYPERLOGLOG_DISTINCT(pulocationid) FROM nyc_taxi;

The result:

This function uses an algorithm to estimate the number of data records, and it is fast even on very large datasets. You can use HYPERLOGLOG_DISTINCT() just like you would use COUNT(DISTINCT) in your queries.

5. Which are the most high-grossing location pairs in NYC for the entire year? For each pair, how much more it grosses if compared to the next best one?

To end, we get advanced for real: this is an example of how to use window functions.

In this case, the query uses the LAG() function:

SELECT pulocationid, dolocationid, cnt, total, next_highest_grossing, total - next_highest_grossing AS leads_by
FROM (
  SELECT
    pulocationid,
    dolocationid,
    COUNT(*) AS cnt,
    SUM(total_amount) AS total,
    LAG(sum(total_amount), 1) OVER (ORDER BY SUM(total_amount)) AS     next_highest_grossing
  FROM nyc_taxi
  WHERE pickup_datetime BETWEEN '2019-01-01T00:00:00' AND
  '2019-12-31T23:59:59'
  GROUP BY pulocationid, dolocationid
  ORDER BY total DESC
  LIMIT 100
) AS sub;

The result:

Hope this was useful! See you next time 👋

There's nothing better than trying things by yourself! Download CrateDB or sign up for a CrateDB Cloud free trial. Experiment... And tell us what you think 😁

Apart from Dev.to, you can reach to the Crate.io team in:

Github (We'd love a ⭐️ 🙏)
Twitter
Our community page

See you around 🐐

What is CrateDB? 🤔 FAQ (1)

Carlota Soto — Tue, 23 Feb 2021 01:57:51 +0000

👉 The basics

CrateDB is a distributed SQL database, purpose-built for querying huge volumes of machine data in real-time.

It is open-source (Apache 2)
It is natively distributed, with a shared-nothing architecture, automatic data rebalancing, and automatic table partitioning
It performs aggregations, JOINs and sub-selects
Its schemas are completely dynamic, being possible to add columns anytime without slowing performance or downtime
Written in Java

👉 Can I use CrateDB for time-series data?

Yes, CrateDB is actually an amazing choice for time series, especially if you have very high volumes: machine data is time-series data. CrateDB has excellent performance with real-time queries, together with group by’s or general roll-up queries over a huge data set without losing accuracy. The dynamic schemas of CrateDB are also great for time series.

👉 What we mean by "purpose-built for machine data"?

CrateDB was built with the features that are most important for handling challenging machine data use-cases, as industrial IoT. And its architecture was designed for this matter: for example, instead of focusing on strong consistency, CrateDB focuses on data availability and partition tolerance. Efficiency is another key pillar of CrateDB, which focuses on maximizing data availability. At the same time, the SQL access makes it easy for developers.

👉 What's the story?

Some years ago, our founders (in lovely Austria) were working on some of the biggest websites in Europe at that time. They soon got obsessed with data, being inspired to build a database that gave Elasticsearch the SQL access it lacked. The first iteration of CrateDB was soon born—winning the TechCrunch Disrupt Europe award.

👉 How far can I scale CrateDB?

CrateDB is built to make it easy to scale indefinitely. Sharding, replication, and data rebalancing are automatic—to grow the database capacity, just add more nodes to the cluster... Or choose the ease of CrateDB Cloud to increase capacity anytime.

👉 Why using CrateDB over other databases?

Our own users can answer this one better than me:

CrateDB’s unmatched concurrency capabilities and simple scaling made it the best solution for us. We tried other solutions, including MongoDB, but it was difficult and expensive to scale for our needs
(Waseem Javid Nasiri, Senior Developer – Roomonitor)

Postgres couldn’t keep up with the data we have; Datastax Enterprise had ingest scaling issues with spatial data; Cassandra didn’t have spatial query operations. CrateDB was the only database we found that could smoothly process data for our users. We fell in love with it immediately
(Kartik Venkatesh, CTO – Spatially Health)

We didn’t want to use a sharded and clustered MySQL database, since maintaining it would have been labor-intensive and use up our engineering resources. I started looking at CrateDB and was impressed by the quality of the code. Switching from MySQL to CrateDB took only a couple of days
(Jeff Nappi, Director of Engineering – ClearVoice)

CrateDB’s ability to query enormous amounts of data expands the realm of what’s possible with Clickdrive. We tried a few different SQL and NoSQL databases, and CrateDB offered the best combination of high performance, scalability, and ease-of-use
(Mark Sutheran, Founder - Clickdrive.io)

There's nothing better than trying things by yourself! Download CrateDB or sign up for a CrateDB Cloud free trial. Experiment... And tell us what you think 😁

Apart from Dev.to, you can reach to the Crate.io team in:

Github (We'd love a ⭐️ 🙏)
Twitter
Our community page

See you around 🐐