Use CrateDB and Franchise for a Notebook Interface to Your Data

Franchise is a lightweight but powerful SQL tool with a notebook interface that exists as a free to use web application with no signup. It's also an open source tool, so you can contribute to it on GitHub.

Franchise lets you tabulate and visualize your queries in a number of different ways. It also lets you compare your queries side-by-side.

And when you're done mashing up your data, you can download the notebook, which includes all the queries, results, and visualizations. These notebooks can then be shared with friends.

Fortunately, because Franchise supports PostgreSQL, it works CrateDB. In this post we'll show you how to get set up with CrateDB and Franchise.

Install CrateDB

If you don't already have CrateDB running locally, it's very easy to get set up.

Run this command:

bash -c "$(curl -L try.crate.io)"

If you wanna get up-and-running a different way, check out the download page for more options.

Get Some Data

If you’re playing around with a fresh CrateDB install, chances are you don’t have any data. So head on over to the Help screen in the Admin UI:

http://localhost:4200/#/help

You should see something like this:

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

Don’t worry. This isn’t going to post anything on your behalf. It doesn’t even look at your tweets. All this does is import a bunch of recent public tweets on Twitter.

Once you’re done, select the Tables icon from the left hand navigation, and then select the tweets table. You should end up here:

http://localhost:4200/#/tables/doc/tweets

Which should look like this:

Get the Franchise Client Running

You need Node.js.

If you're running Mac OS X and you have Homebrew installed, you can run:

$ brew install node

Otherwise, head over to the Node.js download page.

Once Node.js is installed, you can run the Franchise client, like so:

$ npx franchise-client@0.2.7

Connect to CrateDB

Open the Franchise cloud app.

Select PostgreSQL and you should see this:

You probably want to leave the hostname and port number at their default values.

Fill in crate for the dbuser field.

Leave the password and database fields blank.

Now, select Connect.

You will see a screen like this:

Select doc schema button, and you should see your tweets table.

Select the tweets table.

Franchise has read the table schema and generated a query for you:

Franchise doesn't have a CrateDB connector yet, so we're using the PostgreSQL connector. The two query languages are similar enough in most cases that PostgreSQL clients will work. But sometimes we need to make a few adjustments.

Want to help us add a CrateDB connector? We'll pay you! See the end of this blog post for more details.

In this case, Franchise has correctly expanded the account_user object field into a list of object property fields. But the syntax is slightly wrong and will result in an error.

Here's the troublesome part of the generated query:

    "account_user", 
    "account_user['created_at']", 
    "account_user['description']", 
    "account_user['followers_count']", 
    "account_user['friends_count']", 
    "account_user['id']", 
    "account_user['location']", 
    "account_user['statuses_count']", 
    "account_user['verified']", 

We can drop the account_user field itself, because represents the whole object. And it's the object properties we're interested in.

We also need to drop those " characters around the object property columns.

The fixed query is:

SELECT
    account_user['created_at'], 
    account_user['description'], 
    account_user['followers_count'], 
    account_user['friends_count'], 
    account_user['id'], 
    account_user['location'], 
    account_user['statuses_count'], 
    account_user['verified'], 
    "created_at", 
    "id", 
    "retweeted", 
    "source", 
    "text"
FROM "doc"."tweets"
LIMIT 1000

Paste that into the query editor and run it by selecting the green play icon in the bottom right of the query editor.

You should see something like this:

This is the table view.

You can switch to the card view by selecting the Card View icon on the right hand side of the results pane.

Wrap Up

Franchise is a great new web-based notebook tool that works with CrateDB.

We’d love to see an official CrateDB connector. And fortunately, Franchise is an open source project accepting contributions. So. If you’re a developer, and you’d like to help us achieve this, get in touch so we can pay you.