Working with SQL
This section requires a Practicus AI cloud node. Please visit the introduction to cloud nodes section of this tutorial to learn more.
Using SQL with a SQL database
This is quite straightforward, if the database you connect to support SQL, you can simply start by authoring a SQL statement.
Practicus AI cloud nodes come with a simple music artists database.
- Open Explore tab
- Select a Cloud Node upper right of screen (start new or reuse existing)
- Select SQLite on the left menu
- Click Run Query
You will see the result of a sample SQL, feel free to experiment and rerun the SQL
As you will see later in this tutorial, Practicus AI also allows you to run SQL on the result of your previous SQL, as many times as you need. On a SQL database you would need to use temporary tables to do so, which is a relatively advanced topic.
Using SQL on any data
Practicus AI allows you to use SQL without a SQL database. Let's demonstrate using an Excel file. You can do the same on other non-SQL data, such as S3.
- Open Explore tab
- Select Local Files
- Load Home > practicus > samples > data > node_types.xlsx
- Click on SQL Query button
Since SQL is an advanced feature, it will require a cloud node to run. You will be asked if you would like to quickly upload to a cloud node. Click Yes, select a cloud node, and now your Excel file will be on the cloud.
Click on SQL Query button again. This time the SQL query editor will be displayed.
- Type the below SQL
SELECT "Name", "Total price", "Bang for buck" FROM "node_types" WHERE "Total price" < 5 ORDER BY "Bang for buck" DESC
Tip: double-clicking on a column name adds its name to the query editor, so you can write SQL faster. If you select some text before double-clicking, your selected text is replaced with the column name.
- Click on Test SQL button
Note: Your first SQL on a particular cloud node (cold run) will take a little longer to run. Subsequent SQL queries will run instantly.
- (Optional) Experiment further with the SQL, click
- When ready, click Apply SQL button
You will get the result of the SQL back in the worksheet.
(Optional) Visualize SQL result
- Click Analyze > Graph
- Select Bang for buck for X and Total Price for Y
- Click ok
You will see the estimated total cost (Practicus AI license cost + Cloud infrastructure cost), and how much cloud capacity value you would expect to get (bang for buck) visualized.
Note: If you have Practicus AI Enterprise license, your software is already paid for. So this graph would not make any sense. This is only meaningful for the professional pay-as-you-go license type.