The HEASARC Database System

Accessing the HEASARC Database and Metadata

This section describes three mechanisms through which (primarily internal) users can query the HEASARC database interactively beyond standard Browse user table queries. Users may wish to examine the HEASARC metadata, perform queries that cannot be made by Browse directly, investigate problems with table queries, or have other reasons for such access. APIs for software access are discussed elsewhere.

The three mechanisms are the HDBmc command-line interface, the Browse Keyword Search interface to query HEASARC metadata, and vendor-supplied tools for database access. After discussing these access methods, a few examples of the kinds of queries a user might attempt using either HDBmc or other access methods supporting SQL queries are given.

The HDBmc command

The HDBmc command is available for querying the HEASARC database from most HEASARC servers in the /heasarc/bin/ directory. This command provides a tool for doing a single simple query of the database or metadata.

The HDBmc command may be used in the following manner:

    HDBmc observatory=AnObservatoryName
    HDBmc table=ATableName
    HDBmc parameter=ATableName
    HDBmc sql="Any sql query"

The optional command-line parameter -host=hostname[:port] may be used to specify the database host name and socket port. The default port is 40975. This argument can be used to connect to the backup database if desired, but it is mostly used when debugging databases or software. There are four commands available within HDBmc.

HDBmc: observatory

The observatory command lists tables available for a given observatory with each table giving a pair of lines of the form

    table_name=sometable
    observatory_name=someobservatory

The observatory may include a '*' as a wildcard character. This query uses the observatory name as specified in the ZZEXT observatory_name virtual parameter.

Example:

   HDBmc observatory="GENERAL CATALOG"

Response:

   table_name=B/2mass
   observatory_name=GENERAL CATALOG
   table_name=I/252
   observatory_name=GENERAL CATALOG
   table_name=I/271
   observatory_name=GENERAL CATALOG
   table_name=heasarc_class
   observatory_name=GENERAL CATALOG
   table_name=heasarc_messier
   observatory_name=GENERAL CATALOG
   table_name=heasarc_ngc2000
   observatory_name=GENERAL CATALOG
  table_name=heasarc_pg
   observatory_name=GENERAL CATALOG

HDBmc: table

The table request returns the ZZGEN and ZZEXT metadata associated with a given table as a list of the form

   field=value

where field gives the values in the ZZGEN table and then any virtual parameters for this table from the ZZEXT table. The input table name can be specified as a wildcard so that the output can include many tables. Output for each table begins with a line of the form

  table_name=theName

Example:

    HDBmc table=heasarc_messier

Response:

   table_name=heasarc_messier
   table_location=dbms1.gsfc.nasa.gov
   table_description=Messier Nebulae
   table_document_url=http://heasarc.gsfc.nasa.gov/W3Browse/general-catalog/messier.html
   create_date=2003-05-05 18:39:01
   modify_date=2003-05-05 18:39:01
   table_rows=109
   equinox=2000
   unique_key=name
   declination=@dec
   table_priority=3
   table_type=Object
   target_name=@name
   right_ascension=@ra
   default_search_radius=60
   frequency_regime=Optical
   observatory_name=GENERAL CATALOG

HDBmc: parameter

The parameter request gives the details for the parameters of the table. A new parameter is signaled by a pair of lines of the form

  table_name=aTableName
  parameter_name=aParameterNAme

Example:

   HDBmc parameter=heasarc_messier

Response:

   table_name=heasarc_messier
   parameter_name=object_type
   parameter_description=Object Category
   parameter_format=char2
   parameter_unit=
   parameter_is_index=Y
   parameter_minval=DI
   parameter_maxval=S
   parameter_comment=
   parameter_default=0
   table_name=heasarc_messier
   parameter_name=bii
   parameter_description=Galactic Latitude
   parameter_format=float8
   parameter_unit=degree
   parameter_is_index=Y
   parameter_minval=-51.932565
   parameter_maxval=84.424982
   parameter_comment=
   parameter_default=0
   table_name=heasarc_messier
   parameter_name=lii
   parameter_description=Galactic Longitude
   parameter_format=float8
   parameter_unit=degree
   parameter_is_index=Y
   parameter_minval=1.722578
   parameter_maxval=356.865940
   parameter_comment=
   parameter_default=0
   ...

HDBmc: sql

The SQL command lets the user send an arbitrary SQL query to the database. The result will be pipe delimited table. Note that the SQL statement needs to be enclosed in quotes to ensure that it is seen as a single token. Double quotes are recommended since SQL uses single quotes as the delimiter for strings. E.g.,

   HDBmc sql="select * from zzext where table_name='zzdp'"

would return all of the ZZEXT entries for the ZZDP table (the shortcuts discussed in the previous chapter).

Example:

   HDBmc sql="select * from heasarc_messier"

Response:

+--------------+----------------------+-----------+--------------+----------------------+...
|alt_name      |bii                   |class      |constell      |dec                   |
+--------------+----------------------+-----------+--------------+----------------------+
|NGC 6475      |            -4.5212902|       3600|SCO           |      -34.816664907176|
|NGC 6637      |           -10.2731749|       3080|SGR           |      -32.350009145856|
|NGC 6681      |           -12.5107943|       3080|SGR           |      -32.300012468954|
|NGC 6405      |            -0.7155348|       3600|SCO           |       -32.21666091568|
|NGC 6809      |           -23.2733634|       3080|SGR           |      -30.966694770854|
|NGC 6715      |           -14.0974428|       3080|SGR           |      -30.483349176839|
|NGC 6266      |             7.3185708|       3080|OPH           |      -30.116649736484|
...
|NGC 3034      |            40.5596785|       6600|UMA           |       69.683390351975|
+--------------+----------------------+-----------+--------------+----------------------+
(109 rows)

(Note that only the first five fields of the output were shown.)

Note: The HDBfilter utility is extremely useful for formatting the results of a SQL query made using HDBmc.

A more detailed discussion (suitable for programmers) pertaining to the HDBmc command and its inner workings is in another document.

Querying HEASARC Metadata Using Browse Keyword Search

The Browse Keyword Search interface (help) provides a way that users can query Browse metadata through the Web. If users enter the name of a Browse metadata table, then that table will be queried. If there are other keyword parameters, then the specified metadata table will be queried for values for the Browse user tables whose metadata matches the other keywords.

E.g., to simply dump the first few values in the ZZGEN table the user can enter

      zzgen

But if the user wants only the XMMMASTER entry for ZZGEN they could try

      zzgen xmmmaster

Or to get the ZZGEN entries for all ASCA tables

      zzgen asca

Keywords are searched against all metadata so one can get more results than one expects. E.g.,

      zzgen xtemaster

will return results for both XTEMASTER and XTEINDEX since XTEINDEX mentions XTEMASTER in its metadata. If the user is searching a table that does not have a table_name field, then the additional keywords should not be specified. The table name is matched against the list of tables that the user has specified.

Users can put qualifications on the query themselves. E.g.,

      zzext table_name=zzdp

will list the data product shortcuts which are stored in the ZZEXT table with the table_name set to zzdp. Or try

      zzext parameter_name=observatory_name

to get the mission/observatory associated with each table.

The ZZGEN, ZZPAR, ZZEXT, ZZDP, ZZDPSETS, ZZMASTER, ZZWORDS, ZZLINK, and ZZBIB tables can all be queried using this interface. One note regarding querying the ZZBIB table: It is possible for Browse Keyword Search interface to get confused with the bibcode strings. These often have multiple consecutive periods which can be interpreted as the Browse parameter value range operator ("..").

The row and table limits of the keyword search interface apply to these metdata queries. Only the first 200 matching rows will be shown by default if keywords are specified only the top 10 matching tables will be included. The rows and tables qualifiers can be used to change these settings. E.g.,

     zzpar x-ray r:all t:all

will describe all parameters of all rows for any table that includes x-ray in its metadata.

Vendor-Supplied Methods of Accessing the HEASARC Database

All major database vendors provide tools to access their respective database systems. These tools usually need to run on the machines on which the database software is installed, but they provide full access to the capabilities of the database. With these tools users can create, view, modify and query tables. The specific vendor-supplied tools available at the HEASARC are discussed in the appendix. Generally, they provide a mechanism for executing an arbitrary SQL command and displaying the results to a terminal. More sophisticated GUI tools may also be available.

Typically, database configuration and performance tuning are possible only through these vendor-supplied interfaces.

Common Database Tasks

While the Browse tools simplify access to the database, they provide only read access to the database and do not give the users access to all the query capibilities availble through SQL. In this section we describe a few queries that cannot be executed in Browse but might be of interest to a user. This is not intended as an SQL primer. SQL documentation is supplied with the vendor-supplied database system and is also widely available on the Web.

List all of the tables in the database

   SQL:
       select table_name from zzgen

Note that this will include tables which are not displayed in Browse either because they are metadata tables, they are missing required ZZEXT metadata (e.g., a mission to be associated with), or they have been explicitly turned off using the w3browse_display=N virtual parameter mechanism. An equivalent query can be done through the Browse Keyword Search interface.

List all of the tables with data products

  SQL:
      select distinct table_name from zzdpsets

Note the usage of the distinct attribute since we don't want to display a table multiple times when if it has multiple data products.

List all of the missions that have data products

  SQL:
      select distinct parameter_value from zzext
         where table_name in (select distinct table_name from zzdpsets) and
	       parameter_name='observatory_name'

This involves a join of tables. We put on a requirement that the tables we're interested have data products and then display the missions.

Fix a particular value in a table where it is wrong in only a single location

  SQL:
     update heasarc_table
        set theparameter=newValue
	where _unique_id=12345

This uses the SQL update command. Normally, updates should be done by modifying the appropriate TDAT file and re-ingesting the table (see the next section), but this can be used under some circumstances. Note the use of the '_unique_id'. Almost all tables have this field which increments for each row so that it is guranteed to be unique. This field should be used in preference to other fields if you want to be absolutely certain you are modifying only a single row.

Change a field value whereever it has a certain value

Suppose a table uses the string 'STORED' to indicate the data is in our archive, but you wish to change that string to 'ARCHIVED' to be more compatible with the rest of our tables.

  SQL:
    update heasarc_table
       set status='ARCHIVED' where status='STORED'

The system will normally give an indication of the number of rows updated. Once such a change has been made, you may wish to immediately exgest the table to a TDAT file after making such changes.

Create a copy of a table

  SQL
    select  * into newtable from heasarc_table

This syntax is not standard SQL, but it is widely supported. Note that this does not copy the metadata.

Note: You can also create a copy of a table including the metadata easily using the HDBexgest tool to export the table as a TDAT. Then, after a simple modification of the TDAT file, you can use the HDBingest tool to ingest the new table. This is probably the preferred procedure under most circumstances and is descibed in another section.

Copy the ZZEXT metadata from an old table to a new table

E.g., the table created in the previous example.

  SQL
    insert into zzext
       select "newtable", parameter_name, parameter_value
          from zzext
	  where table_name='heasarc_table'

This is a little more tedious for the other tables, since they have more fields, but it is easy enough to write a script and then edit it when needed. Note how we do not just copy the old table name (heasarc_table) into zzext.

Once you have a copy of the table and metadata, you may modify the new table however you like. The HDBexgest tool can be used to save the table you end up with.

How many objects of each different class are there in the WGACAT catalog?

  SQL:
    select distinct count(*),class from heasarc_wgacat
      group by class
      order by class

Using the group by clause is advanced SQL. The standard Browse interface does not expose such advanced querying methods.