Query cells offer a powerful way to interact with the data. They support various query languages and seamlessly store the result in a variable for further processing. This document explores some of the advanced features and best practices for working with query cells.
Result Variable
When you execute a query in a query cell, the result is automatically stored in a variable. You can specify the name of this variable in the Store in input field located within the query cell interface. If a variable with the same name already exists in the notebook’s environment, the new query result will overwrite the existing variable’s content.
If you have multiple queries within a single query cell, it’s important to note that only the result of the final query will be stored in the specified variable.
Namespace Option
Query cells in Polypheny provide the option to set a default namespace for all queries executed within that specific cell. This feature is particularly useful for query languages that don’t support prefixing entity names with their corresponding namespaces.
Transaction Control
Queries executed within a single query cell are treated as one cohesive transaction. This ensures that if multiple queries are present in a single cell and one fails to execute correctly, all previously executed queries within that cell will be rolled back to maintain data integrity. Once the final query in the cell executes successfully, Polypheny automatically commits the transaction.
Variable Expansion in Queries
Polypheny allows you to integrate the values of variables that were previously defined in code cells into your SQL queries dynamically. You can accomplish this by enclosing the variable name in ${} brackets.
SELECT * FROM emps
WHERE salary > ${salary_var}
Activation Required:
Before you can make use of this feature, it must be activated. Navigate to Run > Expand Variables in the notebook toolbar to enable variable expansion.
Be cautious when using this feature. The variable values are directly inserted into the query without any sanitization.
Lock Cell Execution
To give you better control and to prevent accidental database modifications, query cells have the option for manual execution. This is particularly useful for queries that might alter the database state through Data Definition Language (DDL) or Data Manipulation Language (DML) commands.
To enable this, you’ll find a toggle button located beneath the standard run-button ▶. When this toggle is active, the query cell will only execute when you explicitly trigger it, preventing any auto-execution when the notebook is restarted.
Cell Execution Order
Keep in mind that the order in which you run the cells matters, especially if your queries depend on variables defined in code cells. For more consistent results, it is good practice to execute your notebook cells in the order in which they appear.
