LSST Catalog Access Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# LSST Catalog Access Tutorial\n",
    "\n",
    "This notebook provides a simple demonstration on how to access the catalog data for LSST. It is primarily provided for system testing. For science-centered tutorial notebooks please see the Rubin Community Science Team's collection at https://github.com/rubin-dp0/tutorial-notebooks\n",
    "\n",
    "We use the IVOA TAP (Table Access Procotol) standard to access the catalog data.  This standardizes usage so that 3rd party clients and astronomers can easily move their queries around between datasets, and discover what data is available for them to query.\n",
    "\n",
    "Here's a link to the standard: http://www.ivoa.net/documents/TAP/\n",
    "\n",
    "The TAP service uses a query language similar to SQL called ADQL.  For the specifics about the syntax and keywords, look at: http://www.ivoa.net/documents/latest/ADQL.html\n",
    "\n",
    "### TOPCAT usage\n",
    "\n",
    "Some astronomers prefer to use tools like TOPCAT to access the catalogs.  In order to connect TOPCAT to the TAP service, you will need to provide a service endpoint for TOPCAT to use.  Hou can find the service endpoint for the environment you are using by executing the cell in [Section 1](#1.-Create-the-client).  If you can contact the endpoint printed by that cell from your network, you can also use this as the \"Selected TAP Service\" in TOPCAT.\n",
    "\n",
    "See [this](https://nb.lsst.io/environment/tokens.html) page for more in formation on using the TAP service with TOPCAT.  Note that those instructions assume a TAP service running at NCSA, so be sure to substitute the endopint for your specific environment as provided below for the NCSA specific one in the documentation.\n",
    "\n",
    "### Portal usage\n",
    "\n",
    "The TAP service is used by the portal and users can use the Portal UI to help them construct queries.\n",
    "\n",
    "### Notebook Usage\n",
    "\n",
    "In the following steps, we will create a client to query the catalog, show how you can discover what tables and columns exist in the catalog, as well as how to run queries and process their results using python.\n",
    "\n",
    "For this example, we will be using pyvo: https://pyvo.readthedocs.io/en/latest/index.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Create the client\n",
    "\n",
    "First, you must retrieve the TAP service object to use for querying the data.  This will be the object you call to run queries on.  Run the cell below to create your client."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas\n",
    "from IPython.display import Markdown as md\n",
    "\n",
    "from lsst.rsp import get_tap_service, retrieve_query\n",
    "\n",
    "pandas.set_option('display.max_rows', 1000)\n",
    "service = get_tap_service(\"tap\")\n",
    "\n",
    "md(f'The service endpoint for TAP in this environment is:\\n\\n &#10145;&nbsp;&nbsp;   {service.baseurl}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Schema discovery\n",
    "\n",
    "Now that you've connected to the TAP service, you need to know the names of schemas, tables, and columns (and their datatypes) to be able to write queries to access the data.\n",
    "\n",
    "Luckily, TAP provides for a standard way of discovering what schemas, tables, and columns are available.  This information is stored in a database named TAP_SCHEMA.\n",
    "\n",
    "### 2.1 What schemas can I query?\n",
    "\n",
    "In order to discover what database schemas are being served and can be queried against, you can run the following cell.  You should see a numpy compatible table printed out in the notebook that has two important columns, description and schema_name.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT schema_name, description FROM TAP_SCHEMA.schemas\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TAP_SCHEMA.schemas** behaves like a normal SQL table, with mostly the same query language. Just like standard SQL, you can also retrieve all the columns by using the following example.\n",
    "\n",
    "**TIP:** Calling to_table() on a result will give you an AstroPy table (http://docs.astropy.org/en/stable/_modules/astropy/table/table.html).\n",
    "\n",
    "From an astropy table, you can also convert to a pandas dataframe by using to_pandas()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT * FROM TAP_SCHEMA.schemas\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 What tables can I query?\n",
    "\n",
    "Like the TAP_SCHEMA.schemas table, there is also a **TAP_SCHEMA.tables** table, which contains the names of the tables, and which schema each table belongs to."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT * FROM TAP_SCHEMA.tables\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes, you may want to act programmatically on the results.  There are also functions you can call on the results object to get the columns / fields, and iterate through the rows, as demonstrated below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(\"Column names for TAP_SCHEMA.tables are:\")\n",
    "for field in results.fieldnames:\n",
    "    print(field)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now use the column names to limit the columns returned by the query, as seen below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT schema_name, table_name FROM TAP_SCHEMA.tables\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 What columns are in a table, and what datatype is each column?\n",
    "\n",
    "Now that we've learned what tables exist, we're close to querying for real catalog data.  But generally, we want to limit the number of columns to return to exactly what we need to get the job done.  This generally makes things faster and better for everyone.  While you can get all the columns that exist (with a \"SELECT * from TAP_SCHEMA.columns\"), this will be over all the tables in all the schemas, so normally we want to limit the query to a particular table.\n",
    "\n",
    "Let's discover what columns exist in the dp02_dc2_catalogs.DiaObject table using the following code.\n",
    "\n",
    "**TIP**: Be careful with your quoting, since some quotes are processed by python, and some quotes are passed down as a part of the query.  My suggestion is to use double quotes for python, and single quotes for SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT * from TAP_SCHEMA.columns WHERE table_name = 'dp02_dc2_catalogs.DiaObject'\")\n",
    "\n",
    "print(\"Column names for TAP_SCHEMA.columns are:\")\n",
    "print(results.fieldnames)\n",
    "\n",
    "print(\"Columns that exist in dp02_dc2_catalogs.DiaObject are:\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The most useful columns in TAP_SCHEMA.columns are the column_name, unit, and description.  Let's just query those."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT column_name, description, unit FROM TAP_SCHEMA.columns WHERE table_name = 'dp02_dc2_catalogs.DiaObject'\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# 3. Example queries\n",
    "\n",
    "Here are some example queries against the DC02 dataset.  As more queries are supported, they will be added here.\n",
    "\n",
    "**Note**: Not all of these are against the same table, or against the same columns.  These queries are simply for reference."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1 Cone searches\n",
    "\n",
    "How to do a cone search (with a center RA/DEC, and a radius):\n",
    "\n",
    "The format is CONTAINS(POINT('ICRS', dec column or number, ra column or number), CIRCLE('ICRS', center RA, center DEC, radius)) = 1.  All units are in degrees.\n",
    "\n",
    "**TIP**: You can limit the number of rows returned by using maxrec=x as a parameter.  This means it will only return x rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT ra, decl FROM dp02_dc2_catalogs.DiaObject WHERE CONTAINS(POINT('ICRS', ra, decl), CIRCLE('ICRS', 60.0, -30.0, 0.05)) = 1\", maxrec=10)\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 Polygon searches\n",
    "\n",
    "Sometimes cone search isn't enough.  Sometimes you want to submit a set of arbitrary vertices and draw your own polygon to limit to.  You can use the POLYGON function.  This behaves like CIRCLE, but allows for any number of RA/DEC coordinate pairs to be vertices.  The polygon is automatically closed between the last vertex and the first vertex.\n",
    "\n",
    "The format is CONTAINS(POINT('ICRS', dec column or number, ra column or number), POLYGON('ICRS', RA 1, DEC 1, RA 2, DEC 2, ...)) = 1. All units are in degrees.\n",
    "\n",
    "Following is an example of using POLYGON to make a square like shape."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.search(\"SELECT ra, decl FROM dp02_dc2_catalogs.DiaObject WHERE CONTAINS(POINT('ICRS', ra, decl), POLYGON('ICRS', 60.0, -30.0, 60.05, -30, 60.05, -30.05, 60, -30.05)) = 1\", maxrec=10)\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4 Asynchronous queries\n",
    "\n",
    "So far, all the queries you have run are called a synchronous queries.  This means that it will continue executing in the notebook until it is finished.  You can see when a Jupyter cell is running because it will have an asterisk to the left of the running cell.  When the query is finished executing and results are returned, the asterisk will become a number.  This is great for short queries that may take seconds or a minute.\n",
    "\n",
    "For longer queries, or running multiple queries at the same time, you may want to consider running an asynchronous query.  Asynchronous queries can be started and allow you to execute more python while the query runs on the database.  Results can be retrieved later on.  This is especially important for queries that are long or may return a lot of results.\n",
    "\n",
    "### 4.1 Asynchronous query example\n",
    "Let's run one of these queries asynchronously:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# First, submit the job.  This creates it but doesn't run the query yet.\n",
    "job = service.submit_job(\"SELECT schema_name, description FROM TAP_SCHEMA.schemas\")\n",
    "\n",
    "# Here's the URL representing your query.  This is what you need to retrieve\n",
    "# the data again.\n",
    "print('Job URL is', job.url)\n",
    "\n",
    "# Here you can see the job's phase, which is PENDING.\n",
    "print('Job phase is', job.phase)\n",
    "\n",
    "# Now, run the query.\n",
    "job.run()\n",
    "\n",
    "# Here, you can tell python to wait for the job to finish if you don't want to\n",
    "# run anything else while you are waiting.  Then we print out the final state.\n",
    "job.wait(phases=['COMPLETED', 'ERROR'])\n",
    "print('Job phase is', job.phase)\n",
    "\n",
    "# Here's a helpful function that will raise an exception if\n",
    "# your query had an unfortunate incident.\n",
    "job.raise_if_error()\n",
    "\n",
    "# Once it completes successfully, you can work with the results\n",
    "# the same exact way as if it were synchronous. \n",
    "results = job.fetch_result()\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.2 Retrieving results from a previous query\n",
    "\n",
    "If you have already run a query, the reuslts may still be available.  You can easily retrieve the results from a previous query again if you know the URL of the query.\n",
    "\n",
    "You can use the query URL to do a few things:\n",
    "1. Use the File -> Open Query ID and put in the URL to start a notebook to load your results.\n",
    "2. Use TOPCAT TAP Query tool to load the query results into TOPCAT.  Use the \"Resume Job\" tab in the TAP Service tool.\n",
    "3. Use the retrieve_query function from jupyterhubutils.notebook to retrieve the results.\n",
    "\n",
    "Here we will show you the last one to load the same query results as the query we just ran above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "retrieved_job = retrieve_query(job.url)\n",
    "results = retrieved_job.fetch_result()\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 Deleting results\n",
    "\n",
    "Once your asynchronous job is finished, the results are stored on the server until you are ready to retrieve them, as shown in the above section.  When you are done with your results, you can delete them yourself, or the server will delete results automatically after a period of time.  After a job is deleted, you will have to re-run the original query on the server to get the results again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "job.delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4 Long running queries running as asynchronous\n",
    "\n",
    "Even if you don't have something else to do in the background, sometimes you will want to run queries that take a long time asynchronously.  This is because it is safer from network outages and timeouts to run it that way.  Luckily, there's a helper function to run a query asynchronously and wait for it to finish, just like a synchronous query.  It basically does what we've done above.  run_async will also automatically delete the results after they are received.\n",
    "\n",
    "Let's try that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = service.run_async(\"SELECT schema_name, description FROM TAP_SCHEMA.schemas\")\n",
    "results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Temporary Table Uploads\n",
    "\n",
    "Sometimes you want to be able to upload your own table, and then run a query not only against the data contained in the backend database, but both the backend database and your own table.  This may include things like joining your own table with a data in the database, among other things.\n",
    "\n",
    "Here we will begin with an example of this type of query with pyvo.\n",
    "\n",
    "First, let's create our own table of data by querying against the TAP_SCHEMA data.  In a real case, you will probably be making a table against the larger tables such as the sources table.\n",
    "\n",
    "### Note: This does not work on the qserv or the tap service returned by get_tap_service(\"tap\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sso_tap = get_tap_service(\"ssotap\")\n",
    "results = sso_tap.search(\"SELECT schema_name, table_name FROM TAP_SCHEMA.tables\")\n",
    "\n",
    "upload_table = results.to_table()\n",
    "upload_table.show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can store the table results as a few formats, but let's pick a votable in XML.  We will later upload this table back to the TAP service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "upload_table.write('upload_table.xml', format='votable', overwrite=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's upload the contents of upload_table.xml, and run a query against it.  For this example, you can refer to the table in your query as TAP_UPLOAD.upload_table.  If you want to change the name of your uploaded table, change the key in the uploads parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "file_results = sso_tap.search(\n",
    "    \"SELECT *, 'xml_file' as upload_type from TAP_UPLOAD.upload_table\", \n",
    "    uploads={\"upload_table\": \"upload_table.xml\"})\n",
    "file_results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you find it easier to just use the returned python object from the query, you can also do that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Second, we can use the table object returned\n",
    "object_results = sso_tap.search(\n",
    "    \"SELECT *, 'python_object' as upload_type from TAP_UPLOAD.upload_table\", \n",
    "    uploads={\"upload_table\": upload_table})\n",
    "object_results.to_table().show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 6. Future work / Known issues\n",
    "\n",
    "The work for this service is ongoing, and there will be known limitations and bugs.  If you want to contact someone for help, please go to the #dm-rsp-team channel on the LSST Slack.\n",
    "\n",
    "### 6.1 Known limitations\n",
    "\n",
    "This TAP layer is built on top of QServ, which has its own query limitations that may come up.  Here is a document outlining those restrictions:\n",
    "\n",
    "Some ADQL functions are not yet supported:\n",
    "\n",
    "- AREA\n",
    "- BOX\n",
    "- COORDSYS\n",
    "- COORD1\n",
    "- COORD2\n",
    "- INTERSECTS\n",
    "\n",
    "The only coordinate system currently supported is 'ICRS'.\n",
    "\n",
    "https://github.com/lsst/qserv/blob/master/UserManual.md\n",
    "\n",
    "### 6.2 Performance\n",
    "\n",
    "If your query takes more than a minute or so, it's likely you have run a query that is enacting a full table scan.  This can take a long time (~ hour) to return.  Please be patient and don't re-run your query as this may put the database under additional load."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "LSST",
   "language": "python",
   "name": "lsst"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}