1 / 32

SQL Maestro Hello World

SQL Maestro Hello World. IQ Associates. Contents. Initial setup Hello World. Prerequisites. SQL Maestro installed ABC database available Netezza machine available SQL Maestro DB set up on the Netezza machine Ability to create tables on the Netezza machine

gema
Download Presentation

SQL Maestro Hello World

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Maestro Hello World IQ Associates

  2. Contents • Initial setup • Hello World

  3. Prerequisites • SQL Maestro installed • ABC database available • Netezza machine available • SQL Maestro DB set up on the Netezzamachine • Ability to create tables on the Netezza machine • Basic familiarity with SQL

  4. Initial setup There are two things you need to do (after installation) to start using SQL Maestro: • Establish the SQL Maestro Global Workspace. • Create a SQL Maestro Connection File.

  5. Establish Global Workspace Choose “Set Workspace” from the File menu, and type the name of a folder. This folder should be accessible to everyone who needs access to the jobs you are about to create. Then click the “Load/Create Workspace” button.

  6. Set Global Connection You need to tell SQL Maestro how to connect to Netezza and the ABC database. (Whoever installed SQL Maestro will have this information.) From the File menu, choose Set Global Connection, and fill in the fields. Choose the “New Connection File” radio button, name your connection file, and click on “SAVE File,” to save your connection in your workspace. Then click on “Apply Configuration.”

  7. Create Netezza tables In a suitable database on the Netezza run the following SQL statements: create table in_table (member varchar(30), pluses int, minuses int); insert into in_table values ('albert', 4, 3); insert into in_table values ('barbara', 4, 5); insert into in_table values ('calvin', 6, 6); select * from in_table; The last statement should return MEMBER | PLUSES | MINUSES ---------+--------+--------- barbara | 4 | 5 albert | 4 | 3 calvin | 6 | 6 (3 rows)

  8. Create Netezza tables Create the output table: create table out_table (member varchar(30), score int); out_table will remain empty for now. We’ll assume that both of these tables are created in a database named HELLO_DB; if your database is named something else, make the corresponding changes in your Hello World program.

  9. Create a job In the Workspace Navigator tab, click on Create New Job. Name the job helloWorld.xml, and click Save. The new job will appear in the JOBS pane.

  10. Edit the job Double-click on the job you just created. This will open the Job editor. The pane at the left contains steps (Table, Join, Union, etc.) which are different tasks that a job can perform. In the Hello World job, we will use just one of them, the Table step.

  11. Drag Table steps onto the Job pane Drag the “Table” icon onto the Job editing pane, twice, as shown above. Note that the steps are assigned labels (table_1 and table_2). Click on the labels and change table_1 and table_2 to IN_TABLE and OUT_TABLE, respectively.

  12. Double-click on the left table icon (IN-TABLE), which will bring up the Table Properties dialog, as shown above.

  13. Set the Database drop-down if necessary, choose the “Existing Table” radio button, and set the Table drop-down to the “IN_TABLE” table you created earlier. Click OK.

  14. Now left-click on the red dot at the upper-right corner of IN_TABLE, and, while holding down the left mouse button, drag the mouse over to OUT_TABLE. Then release the mouse button. You should get a red arrow pointing from IN_TABLE to OUT_TABLE.

  15. The red arrow is called a transformer. It’s read because something is wrong – in this case, because we haven’t yet set the table information for OUT_TABLE.

  16. Now open the properties dialog for the OUT_TABLE step and set its table information to the OUT_TABLE table you created earlier, then click OK.

  17. You are now automatically placed in the transformer properties dialog, shown above. Note that the information in the dialog is incorrect; the OUT_TABLE table only has two columns, but the properties dialog shows 3. Click on the “Refresh Target” button.

  18. The transformer properties dialog will change so that the are two columns in the target dialog (which is correct.)

  19. We want out transformer to do the following: • Member should be mapped directly from input to output, unchanged. • The output column SCORE should be the sum of the input columns PLUSES and MINUSES. This is done by creating an expression which is the sum of the PLUSES and MINUSES columns, and then mapping the expression to the SCORE column in the output.

  20. Left-click on the blue dot on the MEMBER column on the left, and drag to the MEMBER column on the right, creating a path which connects the input MEMBER column to the output MEMBER column.

  21. Next, click on the Add Expression button, which will create an additional source column with the default name EXPRESSION_1.

  22. Rename the new column from Expression_1 to SCORE, and set its datatype to INTEGER.

  23. Right-click on the left-hand side of the new expression column you just created (where you just typed “SCORE”)…

  24. …which will bring up the ExpressionBuilder window shown above.

  25. Using the COLUMN and OPERATOR drop-downs on the left, or simply by typing, enter PLUSES + MINUSES in the Expression window, then click OK.

  26. Back in the transformer properties dialog, connect SCORE in the input to SCORE in the output, by clicking and dragging between the two blue dots, then click OK.

  27. Note that the arrow connecting the IN_TABLE and OUT_TABLE steps is now blue, indicating that there are currently no detectable errors in the transformation. From the FILE menu, save the job. (The job will be saved in the current Global Workspace.)

  28. Go to the Workspace Navigator tab and click on Create New Batch. Name the batch job HELLO_WORLD_bj. Make sure that BatchType ONREQ is chosen, and click OK.

  29. Your new batch will appear in the BATCHES pane in the Workspace Navigator. Double-click on the new batch and you’ll be transferred into the Batch editor tab. Right-click on the background and choose Add Job  helloWorldjb.xml.

  30. Next, choose Run  Run Locally from the menu…

  31. A dialog will appear with the text of a (long) DOS command. Paste the text into a DOS command window, and type <CR>.

  32. The command should execute successfully, print out some diagnostics, and run your SQL Maestro program on the Netezza machine. Finally, check that the target table is correctly populated: HELLO_DB(USER1)=> select * from out_table; MEMBER | SCORE ---------+------- barbara | 9 albert | 7 calvin | 12 (3 rows)

More Related