A short tutorial for Querying data from QuestDB in a Jupyter Notebook.
To get started, you'll need a few things installed and set up. This should be quick.
QuestDB: To install Questdb you can see Installation for complete instructions in case you want to use Docker, or brew on macOS, but the easiest way is to download the binaries and run it directly. Instructions for that are in the official documentation.
Jupyter Notebooks: These are interactive Python environments that will help you run a complete version of this tutorial interactively. To run it, you should:
- make sure you are running Python 3.x and not Python 2.7. If you're in doubt,
python --versionwill tell you.
- install Jupyter Notebooks with
pip3 install --upgrade ipython jupytermake sure that the libraries we use in this tutorial are also installed with
pip3 install requests urlib matplotlib pandas
- clone this repository
git clone https://github.com/davidgs/QuestNotebook) in the repository directory run Jupyter Notebook
That will get you right back to a page that looks eerily similar to this that is interactive, allowing you to run the code and interact with the database yourself.
If you get errors like
ModuleNotFoundError: No module named 'requests' for any
of the libraries you installed above, double-check to make sure that you are
actually using Python 3.x
jupytper --path will let you know if Jupyter is
using 2.7 or 3.x
Create a database
We will need someplace to store our data, so let's create a test database where we can put some random data.
We will create a simple table with 5 columns, one of which is a timestamp.
The Create operation in QuestDB appends records to the bottom of a table. If the table has a designated new record, time stamps must be superior or equal to the latest timestamp. Attempts to add a timestamp in middle of a table will result in a timestamp out of order error.
cust_id is the customer identifier. It uniquely identifies a customer.
balance_ccy balance currency. We use char in this example, but in general for
text entries we would use SYMBOL to avoid storing text against each record to
save space and increase database performance.
balance is the current balance for customer and currency tuple.
inactive is used to flag deleted records.
timestamp timestamp in microseconds of the record. Note that if you receive
the timestamp data as a string, it could also be inserted using
This should return a
200 status the first time you run it. If you run it more
than once, subsequent runs will return 400 because the database already exists.
Generate some data
Since we have a new setup, we should add some data to QuestDB so that we can have something to query.
We will add some random data, for now. You can re-run this section as many times
as you want to add 100 entries at a time, or simply change the
add as many datapoints as you wish.
Query data from QuestDB
Now that we have data available, let's try querying some of it to see what we get back!
Read the content into pandas dataframe
So you'll notice that the returned data is just a massive CSV string. If you'd rather have JSON data, then you would change the endpoint to http://localhost:9000/exec ... But since we're going to use Pandas to frame our data, we'll stick with CSV.
We are also telling pandas to parse the
timestamp field as a date.
This is important since we're dealing with Time Series data.
Narrow the search
That's just getting us all the data, but let's narrow the search using some SQL clauses.
Let's look for a specific
cust_id and only balances of that customer that are
We are also only interested in times the customer was active
Since this is SQL, you can make this query as simple, or as complex, as you'd like.
Since all of the data was generated randomly, this exact query may return no
results, so you may have to adjust the
cust_id below until you get results
Note: The query string must be URL-encoded before it is sent.
Plot the data
We will use matplotlib to plot the data
From that query we should get a nice little plot of our data, like this:
Now we will clean everything up for the next time.
You can now stop your QuestDB instance, if you'd like, or leave it running and find some great uses for it!