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.
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:
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:
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 firstname.lastname@example.org
Connect to CrateDB
Select PostgreSQL and you should see this:
You probably want to leave the hostname and port number at their default values.
crate for the dbuser field.
Leave the password and database fields blank.
Now, select Connect.
You will see a screen like this:
doc schema button, and you should see your
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.
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.