Query Sample

Contents

Importing This Sample into StreamBase Studio
Sample Location
This Sample's Files
Running Query.sbapp in StreamBase Studio
Running Query.sbapp in Terminal Windows
How We Created the Query Sample
Related Topics

This sample contains several Query operators and an associated Query Table data construct that stores stock index data, and enables you to look up a symbol's name and stock description.

Importing This Sample into StreamBase Studio

In StreamBase Studio, import this sample with the following steps:

  • From the top menu, click File → Load StreamBase Sample.

  • Select operator from the Applications list.

  • Click OK.

StreamBase Studio creates a single project for the operator samples.

Sample Location

By default, the sample files are installed in:

  • On Windows: C:\Program Files\StreamBase Systems\StreamBase.n.m\sample\operator

  • On UNIX: /opt/streambase/sample/operator

When you load the sample into StreamBase Studio, Studio copies the sample project's files to your Studio workspace. StreamBase Systems recommends that you use the workspace copy of the sample, especially on UNIX, where you may not have write access to /opt/streambase. In the default installation, the path to this sample in your Studio workspace is: 

UNIX:       
  ~/streambase-studio-n.m-workspace/sample_operator
Windows XP:
  C:\Documents and Settings\username\My Documents\StreamBase Studio n.m Workspace\
      sample_operator
Windows Vista:
  C:\Users\username\Documents\StreamBase Studio n.m Workspace\
      sample_operator

This Sample's Files

The Query operator sample shares the same Studio project folder as the samples for other operators.

The sample_operator project directory includes a nasdaq100.tab data file that contains stock names, symbols, and descriptions.

Running Query.sbapp in StreamBase Studio

  1. In the Package Explorer, double-click to open the Query.sbapp application. Make sure the application is the currently active tab in the EventFlow Editor.

  2. Click the Run button. This opens the SB Test/Debug perspective and starts the application.

  3. In the Application Output view, select All in the Output stream control. No output is displayed at this point, but the dequeuer is prepared to receive output. This view eventually shows the output of the application.

  4. Open a StreamBase Command Prompt (on Windows) or terminal window on UNIX. In the window:

    1. Navigate to the directory where the sample is installed, or to your workspace copy of the sample, as described above.

    2. Load the stock entries in the nasdaq100.tab data file into the sample application's query table: 

      sbc enqueue -dtab Nasdaq100In < nasdaq100.tab

      Each stock entry has the stock symbol, company name, price, a color (arbitrarily assigned red, blue, or yellow), and company description. The color entry is an example of a custom characterization of the stock, and could be any custom field.

  5. In the Manual Input view, select SymbolStream in the Input stream control.

  6. Enter INTC in the Symbol field.

  7. Click Send Data. In the Application Output view, observe the symbol, name, price, color and description of the Intel stock. This stock has been successfully read from the table.

  8. Try another stock symbol that you know is not in the NASDAQ 100 list. For example, enter GE, and observe the message in the dequeue window: 

    Symbol=GE, Name="UNKNOWN", Price=null, Color=null, Description="Not a member of the Nasdaq100"

  9. In the Manual Input view, select the PriceStream input stream.

  10. Enter 10 in the MaxPrice and 5 in the MinPrice field.

  11. Click Send Data. In the Application Output view, observe the symbol, name, price, color, and description of five different stocks ranging in price from $8.94. to $5.92. Note that the stock entries are sent in descending order of the secondary key (Price,Color).

  12. In the Manual Input view, select the PriceColorStream input stream.

  13. Enter 10 in the MaxPrice and blue in the Color field.

  14. Click Send Data. In the Application Output view, observe the symbol, name, price, color, and description of four blue stocks ranging in price from $2.13. to $8.94. Note that the stock entries are sent in ascending order of the secondary key (Price,Color).

  15. When done, press F9 or click the Stop Running Application button.

Running Query.sbapp in Terminal Windows

This section describes how to run the sample in UNIX terminal windows or Windows command prompt windows. On Windows, be sure to use the StreamBase Command Prompt from the Start menu as described in the Test/Debug Guide, not the default command prompt.

  1. Open three terminal windows on UNIX, or three StreamBase Command Prompts on Windows. In each window, navigate to the directory where the sample is installed, or to your workspace copy of the sample, as described above.

  2. In window 1, start StreamBase Server running the Query sample application. Enter: 

    sbd Query.sbapp

    Look for output similar to the following: 

    notice[StreamBaseServer] listening on port 10000

  3. In window 2, load the stocks into the table by entering: 

    sbc enqueue -dtab Nasdaq100In < nasdaq100.tab

  4. In window 2, start the dequeuer. Enter: 

    sbc dequeue

    No output is displayed at this point, but the dequeuer is prepared to receive output. This window eventually shows the output of the all the query operations.

  5. In window 3, enqueue data to your application. Enter the following command: 

    sbc enqueue SymbolStream

  6. Window 3 now waits for manual input. Type: 

    INTC

    In window 2, observe the symbol, name, and description of the Intel stock. This stock has been successfully read from the table.

  7. In window 3, type: 

    GE

    Observe a message like the following in window 2: 

    "Unknown", "Not a member of the Nasdaq100"

  8. In window 3, type: 

    Ctrl-C
sbc eng PriceStream
10,5

    Observe the symbol, name, price, color and description of five different stocks in window 2, ranging in price from $8.94 to $5.92. The stock entries are sent in descending order of the secondary key (Price,Color).

  9. In window 3, type: 

    Ctrl-C
sbc eng PriceColorStream
10,5

    Observe the symbol, name, price, color and description of 4 different blue stocks in window 2, ranging in price from $2.13 to $8.94. The stock entries are sent in ascending order of the secondary key (Price,Color).

  10. In window 3, type: Ctrl-C to exit the sbc session.

  11. In window 3, type the following command to terminate the server and dequeuer:

    sbadmin shutdown

Back to Top ^

How We Created the Query Sample

  1. Launched StreamBase Studio

  2. Created the sample_operator project.

  3. From the top menu, in the SB Authoring perspective, selected File > New > EventFlow Application. Selected the sample_operator project and entered Query for the diagram name.

  4. Created four input streams. For each one:

    1. Dragged an input stream from the palette to the canvas.

    2. Clicked the input stream icon on the canvas to its Properties view.

    3. On the General tab, entered the stream name in the Name: field.

    4. On the Edit Schema tab, entered the schema's field names, types, and sizes, as in the following example:

      Schema data properties used for Query sample application PriceColorStream input stream.

      The following table shows the stream names and schema values used for all four input streams:

      Input Stream Field Name Type Size
      PriceColorStream MaxPrice double 8
      Color string 8
      PriceStream MaxPrice double 8
      MinPrice double 8
      Nasdaq100In Name string 30
      Symbol string 5
      Price double 8
      Color string 8
      Description string 1024
      SymbolStream Symbol string 5

  5. Created the Query Table data construct:

    1. Dragged a Query Table data construct icon from the palette to the canvas.

    2. Clicked the Query Table icon on the canvas to open its Properties view.

    3. On the General tab, Name: Nasdaq100Table

    4. On the Table Settings tab, selected Type: In memory.

      Note: When you are defining a Query Table data construct, your choice of "In memory" or "On disk" options should be based on the persistence and performance requirements of your application. The memory-resident Query Table will always be faster, but its size is restricted by address space limitations. Use the disk-based Query Table (available only in StreamBase Enterprise Edition) when address space is an issue, or you need your data to persist when the server is shut down and restarted.

    5. On the Edit Schema tab, clicked the Copy Schema button in the upper right corner. On the Copy Schema from Existing Component dialog, we selected:

      Project: sample_operator

      Application: Query

      Copy schema from: Nasdaq100In Input Stream

      For example:

    6. Clicked OK.

    7. On the Primary Index tab, selected Symbol as the primary key. For example:

    8. On the Secondary Indices tab, added a secondary key:

      1. Clicked Add....

      2. In the Edit Secondary Index dialog, selected Price and Color in Available Fields. Then clicked the top right-arrow button to load Price and Color in Selected Fields.

      3. Selected the option Ordered, with ranges (btree).

      4. Clicked OK.

  6. Set up the LoadNasdaq100Table Query operator to write input tuples to (load) Nasdaq100Table Query Table:

    1. Dragged a Query operator icon from the palette to the canvas.

    2. Connected the new Query operator to the Nasdaq100Table Query Table data construct.

    3. Connected the Nasdaq100Ininput stream to the new Query operator

    4. Clicked the Query operator on the canvas to open its Properties view.

    5. In the General tab Name field, entered ReadNasdaq100Table.

    6. In the Query Settings tab:

      • Operation: Write

      • Where: Primary Index

      • Matches:

        Symbol: Symbol

    7. In the Operation Settings tab:

      • Type of write: Insert

      • Values to insert

        For each Field Name the following Expression:

        • Name: Name

        • Price: Price

        • Color: Color

        • Description: Description

      • If insert fails because an existing row was found: Ignore.

        Here we elected to do nothing to the existing record because we chose to not overwrite existing stock records that reside in the Query Table.

        At this point, the Operations Settings tab looks like this:

    8. On the Output Values tab, we want to emit only the Name field for the stock as received from the Nasdaq100In input stream. We chose the explicitly specified fields option, then selected only the input.Name field.

      Note: Only fields that are selected (checked) are output; unselected (unchecked) fields not shown are omitted.

  7. Set up the QueryPriceColor operator to read data from the Query Table:

    1. Dragged a Query operator icon from the palette to the canvas.

    2. Connected the new Query operator to the Nasdaq100Table Query Table data construct.

    3. Connected the PriceColorStreaminput stream to the new Query operator

    4. Clicked the Query operator on the canvas to open its Properties view.

    5. In the General tab Name field, entered QueryPriceColor.

    6. In the Query Settings tab:

      Operation: Read

      Where: Secondary Index (Price,Color)

      Because the secondary index in the Query Table is a btree index and this is a read operation, this tab enabled us to specify a query range for each field in the index.

    7. In the Operation Settings tab:

      The tab loads non-key fields, based on the key we selected in the Query Settings tab. For each of the non-key fields, we entered values to by used if the field's data record is not found in the Query Table. If a record is found, we will emit its matched value from the Query Table.

      For each Field name, we defined the output Expression as follows:

      • Symbol: string(null)

      • Name: string(null)

      • Price: string(null)

      • Color: string(null)

      • Description: "No stock matched the color and price requirements"

    8. In the Output Settings tab:

      Order: Ascending

      Note: The sort order option was available because we are doing a read operation on a btree index.

      Next, we selected the fields to be output from this operator's input stream and query table, and changed the output field names to make them more readable. (Note: the output field names will be referenced later by the output stream connected to this operator.) The following table shows only the fields that were selected:

      Input stream fields chosen for this operator to output

      Field Output Field Name
      input.MaxPrice MaxPrice
      input.Color Color

      Input stream fields chosen for this operator to output

      Field Output Field Name
      current.Symbol Symbol
      current.Name Name
      current.Price Price
      current.Description Description

  8. Set up the QueryPrice operator to read data from the Query Table:

    1. Dragged a Query operator icon from the palette to the canvas.

    2. Connected the new Query operator to the Nasdaq100Table Query Table data construct.

    3. Connected the PriceStreaminput stream to the new Query operator

    4. Clicked the Query operator on the canvas to open its Properties view.

    5. In the General tab Name field, entered QueryPrice.

    6. In the Query Settings tab:

      Operation: Read

      Where: Secondary Index (Price,Color)

      Again, we specified a query range for each field in the btree index:

      Query Settings for the QueryPriceColor operator

      Field Name Type Size Start Operation Start Expression End Operation End Expression
      Price double 8 > MinPrice ≤ MaxPrice
      Color string 8 begin   end  

    7. In the Operation Settings tab:

      Entered the following alternate values for records not found. Note: We completed this tab just as in the QueryPriceColor operator; only the Description is different:

      • Symbol: string(null)

      • Name: string(null)

      • Price: string(null)

      • Color: string(null)

      • Description: "No stocks from within this price range"

    8. In the Output Settings tab:

      For this operator we chose the opposite btree sort order from the previous operator, and we chose to output all of the available fields.

      Order: Descending

      Input stream fields chosen for this operator to output

      Field Output Field Name
      input.MaxPrice MaxPrice
      input.MinPrice MinPrice

      Input stream fields chosen for this operator to output

      Field Output Field Name
      current.Symbol Symbol
      current.Name Name
      current.Price Price
      current.Color Color
      current.Description Description

  9. Set up the ReadNasdaq100Table operator to read data from the Query Table:

    1. Dragged a Query operator icon from the palette to the canvas.

    2. Connected the new Query operator to the Nasdaq100Table Query Table data construct.

    3. Connected the SymbolStreaminput stream to the new Query operator

    4. Clicked the Query operator on the canvas to open its Properties view.

    5. In the General tab Name field, entered ReadNasdaq100Table.

    6. In the Query Settings tab:

      • Operation: Write

      • Where: Primary Index

      • Matches:

        Symbol: Symbol

    7. In the Operation Settings tab:

      Type of write: Insert

      Entered the following alternate values for records not found:

      • Symbol: input.Symbol

      • Name: '"UNKNOWN"'

      • Price: double(null)

      • Color: string(null)

      • Description: '"Not a member of the Nasdaq100"'

    8. In the Output Settings tab:

      Since the Primary Index key used by this operator is not a btree index, we cannot sort on it (the Order is not applicable). We choose to output all the Query Table fields, and none of the input stream fields:

      Input stream fields chosen for this operator to output

      Field Output Field Name
      current.Symbol Symbol
      current.Name Name
      current.Price Price
      current.Color Color
      current.Description Description

  10. Created three output streams. For each one:

    1. Dragged an output stream from the palette to the canvas.

    2. Clicked the output stream icon on the canvas to open its Properties view.

    3. On the General tab, entered its name in the Name field. Used the following names for the three streams:

      • PriceColorQueryOutput

      • PriceQueryOutput

      • SymbolOutputStream

  11. Connected the three output streams to their corresponding Query operators, as follows:

    Output stream connections to Query operators

    Input Stream Query Operator connected
    PriceColorStream QueryPriceColor
    PriceStream QueryPrice
    Nasdaq100In Nasdaq100Table
    SymbolStream ReadNasdaq100Table


Back to Top ^

Related Topics

For another use of the Query operator, see the Query TopN Sample, which tracks a sorted range of values, such as the highest (or lowest) prices.