Creating Your First StreamSQL Application

Purpose

In this topic we will complete the stub StreamSQL application named MyFirstApp.ssql that we created earlier using the New Project wizard. MyFirstApp.ssql will do exactly the same work as the MyFirstApp.sbapp application, and in subsequent topics, you will be able to run either version of the application with identical results.

Note

If you have already created the EventFlow version, you can generate MyFirstApp.ssql by simply right-clicking MyFirstApp.sbapp and choosing Convert to StreamSQL. If you prefer to create it manually, continue with this topic.

To Write Your First StreamSQL Application

In StreamBase Studio:

  1. In the Package Explorer, navigate to the MyFirstApp project and double-click the MyFirstApp.ssql file to open it in the StreamSQL Editor.

  2. Before you begin editing, take a look at the first statement in the application (don't enter it yet): 

    CREATE INPUT STREAM TradesIn (
  symbol string(10),
  quantity int
);

    This StreamSQL statement creates an input stream for the application and defines its schema. If you look at the MyFirstApp.sbapp application you created earlier, you can see that the stream names and schemas match. We will continue to emulate that application in the remaining lines.

  3. Now enter just the first line of the statement. Notice that as soon as you start typing, StreamBase Studio begins typechecking your work. In the screen below, a red typecheck error icon is displayed side of the editor:

    The error is to be expected, because the statement is incomplete, and therefore not valid. The important things to remember are:

    • As you resolve errors, the error icon moves to the line where the next detected error occurs, until all typecheck errors in your application are gone. When the icon disappears, you know your code is valid.

    • You can get an idea of what the error is by pausing your cursor over the icon, as shown in the preceding figure. With some knowledge of StreamSQL and experience, these errors can help you troubleshoot your code.

  4. Now enter the second line up to the first parenthesis of the string function, like this:

    This exhibits the autocompletion feature of the StreamSQL Editor. In this example, the list shows the available data types for the string function. By suggesting options like this, autocompletion can help you code more quickly and avoid errors.

  5. Finish entering the rest of the statement as shown in Step 2. If you avoid or eliminate typing errors, the typecheck error icon should disappear.

  6. Define the output streams: 

    CREATE OUTPUT STREAM BigTrades; 
CREATE OUTPUT STREAM AllTheRest;

    At this point, the StreamSQL Editor displays an error message that the first output stream has no source defined. This is normal - go on to the next step to define the source.

  7. Read all the fields from the input stream, and direct the data into each output stream based on the number of trades: 

    SELECT * FROM TradesIn
WHERE quantity >= 10000 INTO BigTrades
WHERE true INTO AllTheRest
;

    The WHERE statements here do the same thing as the Filter operator in the MyFirstApp.sbapp application: they restrict the SELECT statement, which would otherwise select all tuples:

    • The first WHERE clause restricts the selection to tuples for which the predicate resolves to true: only those tuples are selected and emitted on the BigTrades output stream.

    • The second WHERE clause selects all the tuples that were not selected by the previous WHERE statement, and dequeues them to a second output stream called AllTheRest.

  8. Click the Save button to save MyFirstApp.ssql.

Summary and Next Steps

In this topic you learned how to add StreamSQL statements to define a StreamBase schema and a simple query. You also saw how StreamBase typechecking helps detect errors as you write. Your application is now ready to run.

Do not close StreamBase Studio. To continue the tutorial, please click Next to go on to the next topic.

Back to Top ^