You must be in the JDBC Data Source window to follow this process. You should also have configured and tested a JDBC data source connection.
Note: SQL Query Designer does not work with Hadoop Hive data sources.
Follow this process to design an SQL query for your data source with SQL Query Designer:
- Select your data source in the Connections pane on the left, then click the round green + icon above the Available Queries pane on the right (this is the + button in the upper right corner of the window).
- Type a concise yet sufficiently descriptive name for this query in the Query Name field.
- Click the pencil icon above the upper right corner of the Query field. The SQL Query Designer tool will come up.
- In the lower left pane, click to select the first table you want to select data from, then double-click it to move it to the query workspace. The table you selected will appear in the blue workspace as a sub-window containing all of the table's rows.
- Check all of the rows you want to include in the query. By default, all rows are selected. If you only want to select a few rows (or a single row), click the table name at the top of the sub-window, then click deselect all in the popup menu, then check only the rows you want to include in your query.
- Repeat the previous step for other tables you want to work with.
- You can create an SQL JOIN between tables by selecting a reference key in one table, then dragging it to the appropriate row in another table. To modify the JOIN, right-click its red square, then click edit in the popup menu.
- To add a condition or expression, right-click a row in the query workspace, and select the appropriate action from the context menu.
- To order or group by a particular row, drag a statement from the SELECT category in the upper left pane down to the GROUP BY or ORDER BY categories.
- To edit the SQL syntax directly, click the syntax tab in the bottom left corner of the SQL Query Designer window.
- Click Preview to view the unformatted query results; click OK to finish working on the query.
You now have a data source and at least one query that will return a data set that you can use for reporting.