This guide demonstrates how to create reusable, parameterized queries using CrateDB's views and execute them via the CrateDB HTTP Endpoint. This method streamlines query management, making it easier for former Rockset users to transition to CrateDB.
(if you already have one, you can skip this step)
After you have set up a CrateDB Cloud instance, you can proceed by creating a table. This could be done using the HTTP endpoint, but in this example, we use the CrateDB Cloud Console.
Use this code, paste it into the console, and execute.
Now that the table is created, it is time to insert some rows. In this example, we use a publicly available weather dataset. Copy the code into the Console and execute it to load the 70k rows.
Let's assume a query where NULL readings are interpolated based on the previous and the next value.
This could also be executed using the HTTP endpoint.
In a shell, set the following environment variables.
Even though this works fine, you end up with a significant POST command. The best way to simplify things is by creating views.
Instead of including large SQL statements in the post of your command, you could simply create a view using the code.
Copy the code and execute it in the Console like this example:
With the view in place, the HTTP endpoint command looks like this.
Using views still gives you the power to use filters, for example. Let's assume you want to filter on location='Berlin' and interpolate_temperature > 10. You can still use the view.
Using views combined with the CrateDB HTTP endpoint, you can efficiently manage and execute complex queries, providing an accessible pathway for Rockset users to transition to CrateDB.