Contents
This topic describes how to configure a Query Table using the options in the Properties view for the Query Table data construct.
To learn about Query Tables in general, see the following pages:
A Query Table data construct is always associated with one or more Query operators, which are described in Using the Query Operator.
The Query Table data construct does not have input and output ports, and the flow of tuples in your application does not typically flow through a Query Table. Instead, each Query Table data construct must be associated with at least one Query operator. Use the Query operator to read from, write to, delete from, or update your Query Table.
To draw an association arc between a Query Table data construct and a Query operator:
-
Select the gray square on the bottom port of the Query operator, and hold down the mouse button.
-
Drag the mouse pointer to the gray square at the top of the Query Table data construct.
-
Release the mouse button.
If you mark a Query Table as shared, then outer modules can run queries on the shared Query Table in an inner module. In this case, you draw an association arc between a Query operator and the Module Reference, both in the outer module.
At the top of the Properties view for a Query Table data construct, there is an Affected Components link. Click this link to display a popup window that lists the Query operators in the current module that are associated with the selected Query Table. Click anywhere outside the popup to close the popup.
The following example shows an Affected Components
popup window that shows the four Query operators associated with the Nasdaq100Table Query Table in the Query.sbapp sample, which is a member of the operator sample group shipped with StreamBase.
|
Name: Use this field to specify or change the component's name, which must be unique in the application. The name must contain only alphabetic characters, numbers, and underscores, and no hyphens or other special characters. The first character must be alphabetic or an underscore.
Description: Optionally enter text to briefly describe the component's purpose and function. In the EventFlow canvas, you can see the description by pressing Ctrl while the component's tooltip is displayed.
Use the Table Settings tab to specify this Query Table's type and behavior. The following image shows the tab with all default values:
|
This tab's settings are described in the following table.
Note
Use the Table Settings tab to select a previously-defined table schema, which replaces both schema and index settings for the entire table.
You can also use the Schema tab to select a previously-defined named schema, which replaces the schema definition for that tab only.
| Property | Default | Description | |
|---|---|---|---|
| Definition | <Private Table Schema> |
With the default setting, the Schema, Primary Index, and Secondary Indices
tabs of this Query Table's Properties view are active. Use those tabs to
define the schema and indices for this Query Table. In this case, those
definitions are local to this Query Table and to the containing module.
You can instead use the Definition drop-down list to specify the name of a previously-defined table schema. The Definition drop-down list is empty unless you have defined or imported at least one table schema for the current module. When you select the name of a table schema, the Schema and both Index tabs become dimmed and unavailable, and the entire definition of this Query Table is taken from the specified table schema.
The Definition drop-down list only shows table schema names if the containing module defines or imports at least one table schema. Table schemas are discussed further in Using Table Schemas. |
|
| Type | In memory |
Specifies the persistence of this Query Table. The options are:
|
|
| Truncate on startup | (dimmed) |
This setting applies only to On disk Query
Tables.
If set to
If set to
|
|
| Access Level | Private |
Controls the visibility of this Query Table across application modules. Use
this setting to mark the data in this Query Table as accessible from outside
the module that defines it.
The options for Access Level are:
|
|
| Enable Table Replication | No |
Selecting Yes causes the servers in a
high-availability design pattern to maintain the state of the tables on each
server so that either can become the leader in case of failover to the other.
When using this option, you must also configure the parameters in the
|
The EventFlow canvas icons for Shared and Placeholder Query Tables are overlaid with a decoration to distinguish them from concrete, private Query Tables. Shared tables have a green dot overlay, while Placeholder tables have a blue triangle overlay.
|
|
|
If you specified the name of a table schema in the Definition field of the Table Settings tab, then the Schema tab is dimmed and unavailable. In this case, the specified table schema is responsible for providing the table's schema.
Note
Use the Schema tab to select a previously-defined named schema, which replaces the schema definition for this tab only.
By contrast, use the Table Settings tab to select a previously-defined table schema, which replaces both schema and index settings for the entire table.
If the Definition field of the Table Settings tab
specifies <Private Table Schema>, then use the
Schema tab as described in this section to define the schema for this Query Table.
-
Use the optional Schema Description field at the bottom of the Schema tab to document your schema.
-
Use the drop-down list at the top of the Schema tab to select the schema type. Select
<Private Schema>or the name of a previously-defined named schema from the list. (The drop-down list is empty unless you have defined or imported at least one named schema for the current module.)- Private Schema
-
Populate the schema fields using one of these methods:
-
Define the schema's fields manually, using the
button to add a row
for each schema field. You must enter values for the Field Name and
Type cells; the Description cell is optional. For example:
Field Name Type Description symbol string Stock symbol quantity int Number of shares Field names must follow the StreamBase identifier naming rules. The data type must be one of the supported StreamBase data types, including, for tuple fields, the identifier of a named schema and, for override fields, the data type name of a defined capture field.
-
Add and extend a parent schema. Use the
button's Add Parent
Schema option to select a parent schema, then optionally add
local fields that extend the parent schema. If the parent schema
includes a capture field used as an abstract placeholder, you can
override that field with an identically-named concrete field.
-
Copy an existing schema whose fields are appropriate for this component. The existing schema can be from a system stream, or from any named or unnamed schema defined in the current module or in another application in your workspace. To reuse an existing schema, click the
button. (You may be
prompted to save the current module before continuing.)
In the Copy Schema dialog, select the schema of interest as described in Copying Schemas. Click when ready, and the selected schema fields are loaded into the schema grid. Remember that this is a local copy and any changes you make here do not affect the original schema that you copied.
Use the , , and buttons to edit and order your schema fields.
-
- Named Schema
-
Use the drop-down list to select the name of a named schema previously defined in or imported into this module. The drop-down list is empty unless you have defined or imported at least one named schema for the current module.
When you select a named schema, its fields are loaded into the schema grid, overriding any schema fields already present. Once you import a named schema, the schema grid is dimmed and can no longer be edited. To restore the ability to edit the schema grid, re-select
Private Schemafrom the drop-down list.
If you specified the name of a table schema in the Definition field of the Table Settings tab, then the Primary Index tab is dimmed and unavailable. In this case, the specified table schema is responsible for providing the primary index field or fields.
If the Definition field specifies <Private Table Schema>, then use the Primary Index tab as
described in this section to define the primary index field for this Query Table.
Query Tables must have a primary index to be valid. Use the Primary Index tab to specify a field or fields that will be used to look up values in the Query Table. In general, specify the field or fields that the associated Query operators will use most often. For example, in a table that stores stock trade information, the field that holds the stock symbol is likely to be queried often.
In the Available Fields list, double-click each field that you want to add to the index (or select a field and click the right arrow button).
Use the Index Type control to select how keys are indexed for table read operations:
- Unordered, no ranges (hash):
-
Keys are unsorted, and they are evenly distributed (hashed) across the index. A hash index is used for accessing keys based on equality, and are generally best for doing simple lookups.
- Ordered, with ranges (btree)
-
Keys are sorted. A btree index is used when output ordering and range queries are desired. Note that the sort depends on the order of the fields in the index keys.
The relative performance of hash and btree methods varies depending on many factors, including the distribution of keys in your dataset. If you are in doubt, try both methods to determine which produces the fastest lookup times for associated Query operators. Also remember that StreamBase Studio allows you to specify a key range and sort order in an associated Query operator when you use a btree ordered index, but not when using hashed access.
If you specified the name of a table schema in the Definition field of the Table Settings tab, then the Secondary Indices tab is dimmed and unavailable. In this case, the specified table schema is responsible for providing any secondary index fields.
If the Definition field specifies <Private Table Schema>, then use the Secondary Indices tab as
described in this section to define one or more secondary index fields for this Query
Table.
Secondary indices specify a field or set of fields that are used to look up values in the Query Table. Secondary indices are optional, but if used, no secondary index can be the same as the primary index.
For each secondary index:
-
Click to display the Edit Secondary Index dialog.
-
In the Available Fields list, double-click each field that you want to add to the index.
-
Click the button.
Fields are indexed using the btree method by default. Just as with primary indexes, you can also use the hash method. To change the index type, select Ordered or Unordered at the bottom of the Edit Secondary Index dialog. You can intermix hash and btree indexes in the same Query Table. For example, the primary index could be hash and a secondary index could be btree.
Secondary indices make write and delete operations slower, because each secondary index in the Query Table must be updated with every change to the Query Table.
Use the Initial Contents tab to specify the starting point contents of this Query Table at module startup. The options are:
- Be empty
-
The default option is to not load the Query Table at module start time. Select this option if you want a clean slate table at module start time, or if the table is loaded by upstream operators such as a CSV File Reader adapter.
- Load initial rows from a resource file, CSV
-
Select a resource file in CSV format, which must already exist on this module's resource search path. The initial loader's CSV reading capabilities are a strict subset of the features available in the CSV File Reader adapter and the Feed Simulation CSV Data File Option. The initial loader CSV file has the following restrictions:
-
Do not use a header row.
-
You must use the comma as field separator.
-
The file's columns must exactly match the schema of the Query Table in number and data type for each field.
-
The
nullkeyword is not accepted, but you can specify empty fields with a pair of adjacent quotes:"" -
Date format strings are not interpreted as timestamp fields. Specify the exact literal time instead.
The
operatorssample group includes a CSV file,ResourceFiles/NASDAQ100.csv, in the correct format for use as an initial table loader. Use this file with theQuery.sbappmodule, replacing the CSV File Reader adapter currently in place. You can also establish a project reference from your own test project folder to thesample_operatorproject, then use this file to load Query Tables in your own modules. -
- Load initial rows from a resource file, JSON
-
Select a file from this module's resource search path, containing one or more JSON objects, each on its own row. JSON objects are defined on json.org as a set of comma-separated field-value pairs, with pairs separated by a colon, and with the entire object surrounded by braces. Strings in JSON are surrounded by double quotes. There is no header row in JSON format.
For example, following are the first two rows of the
NASDAQ100.csvfile expressed in JSON format, with truncated Description fields for readability. The two lines are shown here on four lines for publication clarity:{ Name:"Activision Blizzard, Inc",Symbol:"ATVI",Price:12.71,Color:"red", Description:"Activision Blizzard ..."} { Name:"Adobe Systems Incorporated",Symbol:"ADBE",Price:28.43,Color:"yellow", Description:"Founded in 1982, ..."} - Load initial rows from the text below, CSV or JSON
-
Select this option and type one or more rows of data in either CSV or JSON format, using the same rules as for CSV and JSON files described above.
If you provide a CSV or JSON loader file whose fields fail to match the table's schema, or if you type mismatched rows in the Initial Contents tab, the typechecking error is shown in associated Query operators, not in the Properties view for the Query Table itself.