Querying Tables

In the DolphinDB web interface, users can query a table in two ways: Query Builder and Script Editor. After the query, users can preview and directly export the query result in CSV format.

  • Query Builder enables users to select columns from a table and set query conditions. Based on users' settings, it executes query conditions and dynamically generates the corresponding SQL scripts.

  • Script Editor enables users to query a table directly through the SQL scripts.

Note: This function is only supported in a standalone mode, or on the data node or compute node in a cluster.

Create Query

Click the icon on the right of the table name to open the query window.

Figure 1. Figure 1. Query Window Entry

The query window is shown in the following figure.

Figure 2. Figure 2. Query Window

Click the tab shown in the upper left corner to switch to the corresponding query pattern.

Figure 3. Figure 3. Query Patterns

Query Builder

Query Builder consists of two sections — Select Columns and Set Query Conditions.

Figure 4. Figure 4. Query Builder Interface

Select Columns

Select Columns section consists of two selection boxes and two operation buttons.

To add columns for queries, users can select one or multiple columns in the box of unselected columns on the left, then click Add Column; to remove columns for queries, users can select one or multiple columns in the box of selected columns on the right, then click Remove Column.

Figure 5. Figure 5. Select Columns Section

Set Query Conditions

In this section, users can set conditions on partitioning columns and combine them with conditions on non-partitioning columns using the AND operator. Users can set one or multiple conditions. A query condition consists of three required items — column name, operator, and value.

Figure 6. Figure 6. Set Query Conditions Section

Partitioning Columns

If there is a partitioning column in the selected table, users should set at least 1 condition on the partitioning column. Conditions on partitioning columns are combined using the AND operator.

If there is no partitioning column in the selected table, the section of partitioning columns will not be displayed.

How to Set a Condition on a Partitioning Column

The following steps describe how to set a condition on a partitioning column.

  1. Select a partitioning column from the Select column dropdown, which contains all partitioning columns available for query in the selected table. Note that most column types are supported by Query Builder. For details, refer to the section "Supported Column Types".

  2. Select an operator from the Select operator dropdown, which contains all operators available for the selected partitioning column.

  3. Enter at least 1 value in the value box. For different operators, the value box may be a dropdown, a date table, or a manual input box. For details, refer to the section "Column Types and Operators".

Figure 7. Figure 7. How to Set a Query Condition

How to Add/Delete a Condition

To add a condition, click the icon, which is combined with other conditions using the AND operator. To delete a condition, click the icon.

Non-Partitioning Columns

In this section, users can set conditions on non-partitioning columns and combine them using the AND or OR operators. Non-partitioning columns are those available for query in the selected table, excluding partitioning columns.

Non-Partitioning Columns section consists of condition items and condition groups.

Users can create one or multiple condition groups and combine them using the OR operator.

A condition group contains one or multiple condition items, combined using the AND operator.

Figure 8. Figure 8. Non-Partitioning Columns section

To set conditions on non-partitioning columns or add/delete a condition item, follow the same steps as in the Partitioning Columns section.

Click Add Condition Group to add a condition group, which is combined with other condition groups using the OR operator.

Click the icon to delete a condition group.

Figure 9. Figure 9. How to Add/Delete a Condition Group

Column Types and Operators

Supported Column Types

Column Type Examples Category Range
BOOL 1b, 0b, true, false Logical 0~1
SHORT 122h Integral -2 15 +1~2 15 -1
INT 21 Integral -2 31 +1~2 31 -1
LONG 22l Integral -2 63 +1~2 63 -1
DATE 2013.06.13 Temporal
MONTH 2012.06M Temporal
TIME 13:30:10.008 Temporal
MINUTE 13:30m Temporal
SECOND 13:30:10 Temporal
DATETIME 2012.06.13 13:30:10 or 2012.06.13T13:30:10 Temporal [1901.12.13T20:45:53, 2038.01.19T03:14:07]
TIMESTAMP 2012.06.13 13:30:10.008 or 2012.06.13T13:30:10.008 Temporal
NANOTIME 13:30:10.008007006 Temporal
NANOTIMESTAMP 2012.06.13 13:30:10.008007006 or 2012.06.13T13:30:10.008007006 Temporal [1677.09.21T00:12:43.145224193, 2262.04.11T23:47:16.854775807]
FLOAT 2.1f Floating Sig. Fig. 06-09
DOUBLE 2.1 Floating Sig. Fig. 15-17
SYMBOL Literal
STRING "Hello" or 'Hello' or `Hello Literal
DATEHOUR 2012.06.13T13 Temporal
DECIMAL32(S) 3.1415926$DECIMAL32(3) Decimal (-1*10^(9-S), 1*10^(9-S))
DECIMAL64(S) 3.1415926$DECIMAL64(3), , 3.141P Decimal (-1*10^(18-S), 1*10^(18-S))
DECIMAL128(S) 3.1415926$DECIMAL128(3) Decimal (-1*10^(38-S), 1*10^(38-S))

Note:

  • Avoid using single quotes (') when querying columns of SYMBOL/STRING type, otherwise the query may fail.

  • Filtering NULL values of columns is currently not supported by Query Builder and can only be achieved by Script Editor.

For more information, refer to Data Types.

Supported Operators

Column Type Supported Operators
Temporal, numeric =, !=, >, <, >=, <=
SYMBOL, STRING like, not like, in, not in, =, !=
Others =, !=

Instructions for LIKE/NOT LIKE pattern:

The LIKE/NOT LIKE pattern must include the wildcard character %, which represents zero, one, or multiple characters of any type or length. Note that it is case-sensitive. For example:

  • 688% matches strings starting with "688", like "688101".

  • %SZ% matches strings that contain "SZ", such as "001SZ".

  • %6 matches strings ending with "6", like "abcd6".

For more information, refer to like.

Instructions for IN/NOT IN operators:

The value dropdown shows a subset of available values with a brief delay. Alternatively, enter one or more values manually in the field, pressing 'Enter' after each. For more information, refer to in.

For details about other operators, refer to Operator Summary.

View Generated Query

After column selection and condition setting, users can click View Generated Query to dynamically generate corresponding SQL scripts. To set the settings, users can click Reset.

Figure 10. Figure 10. View Generated Query

The generated SQL scripts are as follows. Click Go Back to return to the Query Builder page to modify settings. Click the icon to copy the SQL scripts.

Figure 11. Figure 11. Dynamically Generated SQL Scripts

Preview Data

Click Preview Data to preview the result.

Figure 12. Figure 12. Preview Data

The result will be previewed in table form, as shown below.

Note: The preview page can display up to 20 million rows of data. If the full dataset exceeds 20 million rows, a partial preview will be displayed.

Figure 13. Figure 13. Preview in Table Form

Export Data

Click Export Data and enter the file name in the pop-up window to export the result in CSV format.

Note: If the query returns no data or a dataset exceeding 500,000 rows, the export fails.

Figure 14. Figure 14. Export Data

After the export, an "Export successfully" prompt pops up. Users can view the exported file in the browser's download queue.

Figure 15. Figure 15. Successful Export Prompt

Script Editor

Users can also directly enter SQL scripts in the Script Editor tab to create a query. By default, an example script of the selected table will be automatically generated and displayed in the Script Editor tab.

After entering executable SQL scripts, click Preview Data > Export Data to export the result.

Figure 16. Figure 16. Script Editor Page