Query Builder Reference Help

This is the reference page for the Query Builder, which is a specialized editor for the Astronomical Data Query Language (ADQL). The Query Builder is only viewable from within Task Runner.

Below is a screen shot of the Task Runner with the full Query Builder displayed. Below it is another screen shot of the Task Runner with the Query Builder toggled to show minimum detail. If either of these is similar to your display, read on. But for a less detailed, pragmatic overview of Query Builder usage go to: Building Queries.

Screen shot of full Query Builder:

Screen shot of Query Builder toggled to show minimum detail:

Introduction

The Task Runner can run specialized tasks on the AstroGrid network (eg: Annz or SExtractor). But it can also run queries against astronomical catalogues if the catalogues have been configured to do so. These queries are carried out by a server programme located alongside the catalogue, and the results can be stored in VOSpace (the Virtual Observatory file system) and/or downloaded to the desktop. The results can be produced in different formats, and can be analysed using tools such as TopCat and Aladin.

Inputs, Outputs and Execution

A user must define inputs and outputs for the Task Runner, and it has an execution environment where you can submit the query for execution and monitor its progress, as well as choose to look at the output. You can see the three areas for each of these topics quite distinctly in the screen shot above.

Inputs

The main input to a query is, not surprisingly, the query. Most of the reference details shown here will concentrate on this aspect. Another input to the query is the format required of the output. This can be chosen from a drop down list, but defaults to VOTable.

Outputs

What happens to the results of a query is defined in this section of the display. It requires some careful thought. The default setting is to store results in the desktop cache, which can be very useful for casual exploration. But beware that a large result will overflow the cache. There are ways of investigating and limiting the size of a result, but it is always a matter of judgement. If in doubt, toggle the button within the results section, which will enable you to store results within VOSpace (and not your desktop!). If need be, you can upload a file from VOSpace to your local file system later.

Execution

The Execution area of the display remains empty until the query has been submitted for execution. Then it will show progress, a transcript of details, and a choice to view results on the desktop (but see above).

Toggling the Query Builder

As shown in the above screen shots, it is possible to toggle the full details on and off. When toggled off, a minimal text version of the ADQL query is displayed in the Inputs section. There are two ways of toggling:

• The large blue double arrow can be used in the top left hand corner.
• There is a choice within the drop down Edit menu.

The Editing, Metadata and Diagnostic Areas

The Query Builder display is divided into three separate areas, illustrated below.

Screen shot of Editing Area:

Screen shot of Metadata Area:

Screen shot of Diagnostics Area:

The Editing Area

The editing area is where a query is largely built. It is composed of two notebook pages Edit Page and Comment Page.

The Edit Page

The Edit Page is split into two halves

• A top half which we term the Edit Pane

Screen shot of the Edit Pane:

• A bottom half which we term the Tree View.

Screen shot of the Tree View:

The Edit Pane is for entering ADQL text by typing (or by pasting from the clipboard), whereas the Tree View is a structural depiction of the query. Any text which is typed into the Edit Pane must be validated by using the Validate Edit button at the base of the Edit Page. If the query does not validate as correct, any error messages are shown within the Diagnostics Area. If there are no errors, the Tree View is updated accordingly.

The Edit Pane and the Tree View work in tandem. You can chop and change between entering ADQL in the Edit Pane and manipulating the Tree View, but one thing to be aware of is that the Edit Pane is itself context sensitive. When the Query Builder first opens the Tree View is focused upon the top SELECT statement in the query: the SELECT branch is highlighted in the tree (see above screen shots). This effectively targets the Edit Pane to accept complete queries. If you wish to type in and see the whole query as you work, then that is what you want. However, if you choose a different branch of the Tree View, then the Edit Pane will be targetted at that point, and will only accept ADQL suitable for the specifically chosen construct. Effectively, you can target the query from its smallest component to its largest construct. For complex queries this can help in breaking down the query into managable chunks. If you cursor down the tree using the keyboard (try the arrow keys), you will see the contents of the Edit Pane alter as the context changes. At each point the query is editable in the Edit Pane. Just be careful: if at any point you start getting errors on what appears to be sensible ADQL, check to see you have not inadvertently changed the context by clicking on another branch in the Tree View!

The Edit Pane

The Edit Pane accepts ADQL (the Astronomical Data Query Language) as text. ADQL is based upon SQL, which is a standardardized query language. The definitive definition of ADQL as currently supported by AstroGrid, is given here, but this is an abstract definition and not easy reading. The best approach is to try some examples by copying into the Edit Pane (see Building Queries and Library of Examples), or fall back upon the Tree View, which is more involved to use but enables the building of a query in a more piecemeal fashion. Try chopping and changing between the Edit Pane and the Tree View whilst a knowledge of ADQL is built up.

The Tree View

The Tree View is an expandable view of the query. It is possible to drill down to small parts of the query by clicking on the tree with the mouse, or by using the arrow keys (right and left arrows expand and contract branches, whilst up and down keys will traverse the tree). Below are two screen prints showing the Edit Page with a query fully expanded in the tree, one with the SELECT branch highlighted, and one with a WHERE highlighted in the sub query; note the different displays of text in the Edit Pane. In effect you can choose at what level you edit the query, although expanding all branches within the tree is admittedly at one extreme.

The Comment Page

Screen shot of the Comment Page:

The Comment Page is there to enter an overall descriptive comment of the query. If you save to VOSpace, or to your local file system, the comment will be preserved along with the query itself.

There are two ways of writing into the Comment Page.

• You can type directly into the page.
• If you type or cut-n-paste a query containing a top comment into the Edit Pane itself, then validating the query (with no errors) will automatically extract the comment for you. The comment can be SQL92 style, or C or C++ style, as illustrated below.

SQL92 Style comment:

 -- Find the observed object with the highest good quality redshift 
 -- and return its observed RA and DEC, z, specid and targetname
 select s.obsra, s.obsdec, s.z, s.specid, s.targetname from spectra as s 
 where s.z in ( select max(b.z) from spectra as b where b.quality >= 3 )

C Style comment:

 /* Find the observed object with the highest good quality redshift 
    and return its observed RA and DEC, z, specid and targetname */

C++ Style comment:

 // Find the observed object with the highest good quality redshift 
 // and return its observed RA and DEC, z, specid and targetname 

Please note that you do not have to include comment markers when typing directly into the Comment Page. This may disrupt formatting of the comment. A comment can be seen from the Edit Page as a tool tip if the mouse pointer is hovered over the top SELECT branch in the Tree View.

The Metadata Area

The Metadata Area shows all the table and column details for an astronomical collection.

Choosing to display a table

To choose a table, use the table dropdown as illustrated below:

Screen shot of tables dropdown:

Choosing columns

Examining all the columns available in a table can be difficult where the number of columns is large. There are two basic solutions to this problem:

• Each of the column headings can be used to sort the display by clicking on the column heading.
• Use the very useful facility.

Screen shot of columns sorted by name, alphabetical descending:

Screen shot of columns filtered on the string "DEC":

The Diagnostics Area

The Diagnostics Area has two separate tabs:

• One for diagnostics to show any error messages when a query fails validation.
• One to show a history of edits.

Diagnostics

Screen shot of Diagnostics with errors

Before submitting a query for execution, ensure you have validated the query and there are no error messages in the diagnostics.

The History Stack

The History Stack shows the last sixteen edits undertaken. You can page through your edits. It can be useful if you inadvertently lose an edit. The edits can be copied and pasted back into the Edit Pane.

Screen shot of History Stack

Using the Tree View

The Tree View is useful if you don't know or are uncertain of the detailed syntax of ADQL. The tree's menus can be used to build the query. It is also useful for building very complex queries that would be difficult to get correct in one go, in that it tolerates errors in partially formatted queries.

Here we will build a simple query using the Tree View and the Metadata Area. The menus displayed are in-context menus available by right-clicking (Ctrl-button click on the Mac), although most are also available from the Edit Menu as well.

The query will be against table spectra in 6df. I'll start with the finished query:

-- Observations where the cross-correlation redshift is greater than or equal to 0.25
Select a.TARGETID, a.RA_R, a.DEC_R, a.Z_ABS From spectra as a Where a.Z_ABS>=0.25

Choosing the Table:

Once you have chosen your table using the Metadata Area, choose your columns by selecting with the mouse button. Then bring up the Metadata Area's in-context menu as shown below.

Selecting the Columns:

Click on the Insert part of the menu to insert the columns into the query.

Columns inserted into the Select list:

Note that this is a valid query. Check by pressing the Validate Edit button. However, it is not much of a query in that all observations will be selected from the table, whereas we want only those with a cross correlation redshift greater than or equal to 0.25. We need a WHERE clause. Insert a WHERE clause by highlighting the SELECT branch of the tree and right clicking, then choose to insert a WHERE:

Inserting a WHERE:

WHERE inserted:

Note that the query is being flagged as containing an error. The tree is displaying an empty WHERE clause, which is the error. Nevertheless, the tree has built the query. We need to include some criteria to finish. Highlight the WHERE clause in the tree and bring up its in-context menu. Insert a suitable comparison...

Insert a suitable comparison:

At this point, for such a simple query it would be easier to change to the Edit Pane and finish the comparison by typing. This is always an option that you have when query building. But for now we will persist with the tree.

Once inserted, you will need to expand the WHERE clause in the tree before adding some suitable condition to the comparison. We need the a.Z_ABS column, and there are a number of ways of achieving this. We could use the Metadata Area again:

Inserting into the comparison using the Metadata Area:

But instead we'll copy-n-paste from the SELECT item list within the query:

Inserting into the comparison using copy-n-paste:

Once the column has been inserted into the comparison, insert a literal into the comparison...

Inserting a literal into the comparison:

Then bring up the Micro Editor and enter the figure 0.25 ...

Invoking the Micro Editor:

Click anywhere outside of the Micro Editor to close it and accept the edit. Validate the query by using the Validate Edit button. You should have a query without errors...

Finished query, ready for exection:

OK, that was pretty involved! But you get the idea that a query can be built up by using the Tree View. It's always handy when you are in doubt, and at any time you can switch your attention to the Edit Pane, and from any level within the tree!

The Edit Menu

This describes the Edit menu part of Task Runner when the Query Builder is displayed. For details of other menus go to Task Runner.

The Edit Menu for Query Builder (sometimes also called the Query Editor) is context sensitive. This means the contents of the menu and any associated sub-menus vary according to what is focussed upon and highlighted in the different parts of the display.

Cut

Cut will remove any highlighted item in the underlying display and place a copy on the clipboard. If the focus is within the edit window, text will be removed. If the focus is upon the tree view of the query (below the edit window), cut will remove the branch currently selected within the tree view.

Copy

Copy will copy any highlighted item in the underlying display to the clipboard. If the focus is upon the tree view of the query (below the edit window), it will copy the branch currently selected within the tree view.

Paste

Paste will take the contents of the clipboard and paste it into wherever the current focus is within the Query Builder. You can paste into the edit window as text or the tree view as a branch. The latter is restricted to items cut/copied from the tree view and may further restrict you if the paste is inappropriate for the branch selected. If you paste using the tree view, this is effectively over the currently selected branch.

Select All

Greyed out, as not implemented in the Query Builder at present. You can effectively select all, either by clicking on the top SELECT branch of the tree view or by using the mouse to highlight all text within the edit window.

Hide Full Query Editor

Used to hide the Query Builder display. With this selected, the Task Runner reverts back to its normal display. The menu item toggles between hiding and showing the Query Builder.

Query Extras

These choices apply solely to the tree view. They are also available as popup menus on any item in the tree view.

Paste Into

If you have copied or cut a branch from the tree, it is possible to paste it into another branch if that branch is suitable for containing it. For example, you could cut a column reference, say a.ra, and paste into into an aggregate function MAX(), effectively producing something like MAX(a.ra). If the target can contain an array of items, Paste Into will insert it at the end of the array. If inappropriate, this action will be greyed out.

Paste Before

If you have copied or cut a branch from the tree, it may be possible to paste it into an array of similar items. Select an item and this action will insert the cut or copied item before the selected item. If inappropriate, this action will be greyed out.

Paste After

If you have copied or cut a branch from the tree, it may be possible to paste it into an array of similar items. Select an item and this action will insert the cut or copied item after the selected item. If inappropriate, this action will be greyed out.

Undo / Redo

If present, this will undo an edit, or redo a previously undone edit. It does not walk back through a whole series of edits. It will not be present if no edits have been undertaken.

Insert Into

Will display all suitable items that can be created in the current branch of the tree view. If created within an array, will always insert at the end of the array. This menu is highly dynamic, with the contents varying according to the type of branch selected.

Hints

• 1. Validate your query using the Validate Edit button before executing it!
• 2. Save your work periodically.
• 3. The quickest way to start a simple query is to use the Metadata Area:
  • 3.1 First of all, ensure that either the SELECT branch or the item list within the Tree View is highlighted.
  • 3.2 Choose your table using the Metadata Area.
  • 3.3 Then select your columns. Remember to hold down the Ctrl/Shift keys to choose multiple columns. Just be careful you now don't deselect them inadvertently!
  • 3.4 Bring up the in-context menu within the Metadata Area by clicking the right hand mouse button (Ctrl-mouse click on the Mac).
  • 3.5 The insert action will then fold both table and columns into the query.
• 4. Use the facility for filtering columns available within the Metadata Area. Some tables have a considerable number of columns. This saves paging through them.
• 5. The column displays in the Metadata Area can be sorted by clicking on the column heading.
• 6. You can undo / redo an edit. This is available from the Edit Menu, either the pull down menu or the in-context menu on the Tree View. It's only a toggle, but it's there to be used.
• 7. Use the Tree View to focus your efforts when dealing with a complex query.
• 8. If you appear to "lose" an edit inadvertently, examine the History Stack. You can copy/paste from here into the Edit Pane.
• 9. Returning the query results to the Desktop cache is good for casual exploring, but remember you may overflow the cache.
• 10. You can limit the number of hits returned by a query by using the TOP construct.

  SELECT top 100 s.specid,s.targetname,s.z_comm,t.progid 
  FROM spectra as s , target as t 
  WHERE s.targetid = t.targetid and s.z_comm like '%star%'
  

• 11. You can find the number of rows a query will return by first of all submitting it with only a COUNT(*) construct in the item list. This is failsafe for the Desktop cache, but it does mean executing a similar query twice.

  SELECT COUNT(*) 
  FROM spectra as s , target as t 
  WHERE s.targetid = t.targetid and s.z_comm like '%star%'
  

• 12. If you join two or more tables, remember that you need something "common" between them, usually a column in common (strictly speaking a domain in common), but sometimes a calculated amount. Otherwise... a table with 1000 rows joined with another table with 1000 produces a result with 1,000,000 rows. I'll leave you to do the arithmetic for three tables!
• 13 The Micro Editor is very useful if you wish to change the alias of a table to something more meaningful. It will propogate the changed alias throughout the query.
• 14 The Tree View displays everything using a container analogy, which can sometimes be confusing. A simpler display can usually be seen by collapsing a branch, or by viewing the Edit Pane.