How to create queries using the SQLBuilder

Talend ESB Studio User Guide

EnrichVersion
6.4
EnrichProdName
Talend ESB
task
Design andĀ Development
EnrichPlatform
Talend Studio

SQLBuilder helps you create your SQL queries and monitor the changes between DB tables and metadata tables. This editor is available in all DBInput and DBSQLRow components (specific or generic).

You can create a query using the SQLbuilder whether your database table schema is stored in the Repository tree view or built-in directly in the Job.

Fill in the DB connection details and select the appropriate repository entry if you defined it.

Remove the default query statement in the Query field of the Basic settings view of the Component panel. Then click the [...] button to open the [SQL Builder] editor.

The [SQL Builder] editor is made of the following panels:

  • Current Schema,

  • Database structure,

  • Query editor made of editor and designer tabs,

  • Query execution view,

  • Schema view.

The Database structure shows the tables for which a schema was defined either in the repository database entry or in your built-in connection.

The schema view, in the bottom right corner of the editor, shows the column description.

How to compare database structures

On the Database Structure panel, you can see all tables stored in the DB connection metadata entry in the Repository tree view, or in case of built-in schema, the tables of the database itself.

Note

The connection to the database, in case of built-in schema or in case of a refreshing operation of a repository schema might take quite some time.

Click the refresh icon to display the differences between the DB metadata tables and the actual DB tables.

The Diff icons point out that the table contains differences or gaps. Expand the table node to show the exact column containing the differences.

The red highlight shows that the content of the column contains differences or that the column is missing from the actual database table.

The blue highlight shows that the column is missing from the table stored in Repository > Metadata.

How to create a query

The [SQL Builder] editor is a multiple-tab editor that allows you to write or graphically design as many queries as you want.

To create a new query, complete the following:

  1. Right-click the table or on the table column and select Generate Select Statement on the pop-up list.

  2. Click the empty tab showing by default and type in your SQL query or press Ctrl+Space to access the autocompletion list. The tooltip bubble shows the whole path to the table or table section you want to search in.

    Alternatively, the graphical query Designer allows you to handle tables easily and have real-time generation of the corresponding query in the Edit tab.

  3. Click the Designer tab to switch from the manual Edit mode to the graphical mode.

    Note

    You may get a message while switching from one view to the other as some SQL statements cannot be interpreted graphically.

  4. If you selected a table, all columns are selected by default. Clear the check box facing the relevant columns to exclude them from the selection.

  5. Add more tables in a simple right-click. On the Designer view, right-click and select Add tables in the pop-up list then select the relevant table to be added.

    If joins between these tables already exist, these joins are automatically set up graphically in the editor.

    You can also create a join between tables very easily. Right-click the first table columns to be linked and select Equal on the pop-up list, to join it with the relevant field of the second table.

    The SQL statement corresponding to your graphical handlings is also displayed on the viewer part of the editor or click the Edit tab to switch back to the manual Edit mode.

    Note

    In the Designer view, you cannot include graphically filter criteria. You need to add these in the Edit view.

  6. Once your query is complete, execute it by clicking the icon on the toolbar.

    The toolbar of the query editor allows you to access quickly usual commands such as: execute, open, save and clear.

    The results of the active query are displayed on the Results view in the lower left corner.

  7. If needed, you can select the context mode check box to keep the original query statement and customize it properly in the Query area of the component. For example, if a context parameter is used in the query statement, you cannot execute it by clicking the icon on the toolbar.

  8. Click OK. The query statement will be loaded automatically in the Query area of the component.

How to store a query in the repository

To be able to retrieve and reuse queries, we recommend you to store them in the repository.

In the [SQL Builder] editor, click the icon on the toolbar to bind the query with the DB connection and schema in case these are also stored in the repository.

The query can then be accessed from the Database structure view, on the left-hand side of the editor.