cosmosim_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "minimal-working",
   "metadata": {},
   "source": [
    "# Installation of pyvo\n",
    "---\n",
    "\n",
    "In order to interact with the TAP interface of `www.cosmosim.org` you only require\n",
    "`python 3+` and `pyvo 1+`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "intermediate-hartford",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "pip install pyvo>=1.0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "controlled-hamburg",
   "metadata": {},
   "source": [
    "# Importing PyVo and checking the version\n",
    "---\n",
    "\n",
    "It is useful to always print the version of pyvo you are using. Most of non-working scripts fail because of an old version of `pyvo`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "inappropriate-processing",
   "metadata": {
    "lines_to_next_cell": 0
   },
   "outputs": [],
   "source": [
    "from pkg_resources import parse_version\n",
    "import pyvo\n",
    "\n",
    "#\n",
    "# Verify the version of pyvo\n",
    "#\n",
    "if parse_version(pyvo.__version__) < parse_version('1.0'):\n",
    "    raise ImportError('pyvo version must be at least than 1.0')\n",
    "\n",
    "print('\\npyvo version %s \\n' % (pyvo.__version__,))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "german-norman",
   "metadata": {},
   "source": [
    "# Authentication\n",
    "---\n",
    "\n",
    "After registration you can access your API Token by clicking on your user name in the right side of the menu bar. Then select `API Token`. \n",
    "\n",
    "![aip-token](files/cosmosim-api-token-menu.png)\n",
    "\n",
    "You will see a long alphanumerical word. Just copy it where ever you see `<your-token>` ; in the following examples. \n",
    "\n",
    "![aip-token-blured](files/cosmosim-api-token-page.png)\n",
    "\n",
    "> **The `API Token` identifies you and provides access to the results tables of your queries.**\n",
    "\n",
    "The connection to the TAP service can be done that way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "foreign-rhythm",
   "metadata": {
    "lines_to_next_cell": 0
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import pyvo\n",
    "\n",
    "#\n",
    "# Setup tap_service connection\n",
    "#\n",
    "service_name = \"CosmoSim\"\n",
    "url = \"https://www.cosmosim.org/tap\"\n",
    "token = 'Token <your-token>'\n",
    "\n",
    "print('TAP service %s \\n' % (service_name,))\n",
    "\n",
    "# Setup authorization\n",
    "tap_session = requests.Session()\n",
    "tap_session.headers['Authorization'] = token\n",
    "\n",
    "tap_service = pyvo.dal.TAPService(url, session=tap_session)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "opening-description",
   "metadata": {},
   "source": [
    "# Short queries\n",
    "---\n",
    "\n",
    "Many queries last less than a few seconds, we call them **short queries**. The latter can be executed with **synchronized** jobs. You will retrieve the results interactively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "creative-devices",
   "metadata": {},
   "outputs": [],
   "source": [
    "lang = \"PostgreSQL\"\n",
    "\n",
    "query = '''-- Select surrounding halos\n",
    "SELECT bdmid, x, y, z, rvir, mvir \n",
    "  FROM mdr1.bdmv \n",
    " WHERE pdist(1000, x,y,z, 998,450,500) < 5\n",
    " LIMIT 10\n",
    "'''\n",
    "\n",
    "tap_result = tap_service.run_sync(query, language=lang)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "crude-stamp",
   "metadata": {},
   "source": [
    "> **Remark**:\n",
    "> the `lang` parameter can take two values either `PostgreSQL` or `ADQL`\n",
    "> this allows to access some featured present in the one or the other language \n",
    "> for more details about the difference between both please refer :  [Documentation](http://tapvizier.u-strasbg.fr/adql/help.html) or to [IOVA docs](http://www.ivoa.net/documents/latest/ADQL.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "realistic-polymer",
   "metadata": {},
   "source": [
    "The result `tap_result` is a so called [`TAPResults`](https://pyvo.readthedocs.io/en/latest/api/pyvo.dal.TAPResults.html#pyvo.dal.TAPResults) that is essentially a wrapper around an Astropy `votable.Table`. For standard conversion see [Convert to various python types](#Convert-result-to-various-python-types).\n",
    "\n",
    "The entire script can be found on github: [cosmosim-sync-query.py](files/cosmosim-sync-query.py)\n",
    "\n",
    "# Asynchronous jobs\n",
    "---\n",
    "\n",
    "For slightly longer queries, typically counting or larger selections (>10000 objects) a synchronized job will fail because of timeouts (from http protocol or server settings). This is why we provide the possibility to submit **asynchronous** jobs. These type of jobs will run on the server side, store the results such that you can retrieve them at a later time. They come in 3 flavors:\n",
    "* 1 Minute queue\n",
    "* 1 Hour queue\n",
    "* 5 Hours queue"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "embedded-pitch",
   "metadata": {},
   "source": [
    "## The 1 minute queue\n",
    "\n",
    "Most of the asynchronous queries will require less than 1 minute, basically all queries without `JOIN`, or `CONE SEARCH`. Therefore this queue is the default and should be preferred."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "still-costume",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Submit the query as an async job\n",
    "#\n",
    "query_name = \"select_snapshot_by_redshifts\"\n",
    "lang = 'PostgreSQL' # ADQL or PostgreSQL\n",
    "query = '''\n",
    "-- Select simulation snapshots by redishift\n",
    "SELECT distinct zred, aexp, snapnum \n",
    "  FROM mdr1.redshifts \n",
    " ORDER BY snapnum \n",
    "  DESC      \n",
    "'''\n",
    "\n",
    "job = tap_service.submit_job(query, language=lang, runid=query_name, queue=\"1m\")\n",
    "job.run()\n",
    "\n",
    "#\n",
    "# Wait to be completed (or an error occurs)\n",
    "#\n",
    "job.wait(phases=[\"COMPLETED\", \"ERROR\", \"ABORTED\"], timeout=60.0)\n",
    "print('JOB %s: %s' % (job.job.runid, job.phase))\n",
    "\n",
    "#\n",
    "# Fetch the results\n",
    "#\n",
    "job.raise_if_error()\n",
    "print('\\nfetching the results...')\n",
    "tap_results = job.fetch_result()\n",
    "print('...DONE\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "according-station",
   "metadata": {},
   "source": [
    "As for sync jobs, the result is a [`TAPResults`](https://pyvo.readthedocs.io/en/latest/api/pyvo.dal.TAPResults.html#pyvo.dal.TAPResults) object.\n",
    "\n",
    "The entire script can be found at: [cosmosim-async-1m.py](files/cosmosim-async-1m.py)\n",
    "## The 1 hour queue\n",
    "\n",
    "If you want to extract information on specific stars from various tables you have to `JOIN` tables. Your query may need more than a few seconds. For that, the **1 hour queue** provide a good balance. It should be noticed that for such a queue the wait method should not be used to prevent an overload of the server at peak usage. Therefore using the script with the `sleep()` method is recommended."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cellular-quest",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Submit the query as an async job\n",
    "#\n",
    "lang = 'PostgreSQL'\n",
    "query_name = \"radial_prof_massive_bdmv\"\n",
    "\n",
    "query = '''\n",
    "-- Radial profile of most massive BDMV (z=0)\n",
    "SELECT * FROM bolshoi.bdmvprof\n",
    " WHERE bdmid =\n",
    "       (SELECT bdmid FROM bolshoi.bdmv\n",
    "         WHERE snapnum=416 ORDER BY mvir DESC LIMIT 1)\n",
    " ORDER BY rbin\n",
    "'''\n",
    "\n",
    "job = tap_service.submit_job(query, language=lang, runid=query_name, queue=\"1h\")\n",
    "job.run()\n",
    "\n",
    "print('JOB %s: SUBMITTED' % (job.job.runid,))\n",
    "\n",
    "#\n",
    "# Wait for the query to finish\n",
    "#\n",
    "while job.phase not in (\"COMPLETED\", \"ERROR\", \"ABORTED\"):\n",
    "    print('WAITING...')\n",
    "    time.sleep(3600.0) # do nothing for some time\n",
    "\n",
    "print('JOB ' + (job.phase))\n",
    "\n",
    "#\n",
    "# Fetch the results\n",
    "#\n",
    "job.raise_if_error()\n",
    "print('\\nfetching the results...')\n",
    "results = job.fetch_result()\n",
    "print('...DONE\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "remarkable-basin",
   "metadata": {},
   "source": [
    "The entire script can be found on github: [cosmosim-async-1h.py](files/cosmosim-async-1h.py)\n",
    "## The 5 hours queue\n",
    "\n",
    "Some complex queries like Cross-Matching or geometric search may take more than the short queues allow. For this purpose we provide the **5 hours queue**. If you need longer queues please contact us. \n",
    "\n",
    "When running a long query, you surely don't want to block CPU ressources for a python process that just wait for 5 hours, for the queue to finish. Therefore long queries are typically done in two parts (= two scripts), one that submits the request, another one that retrieve the results.\n",
    "\n",
    "### Submitting a job and store `job_urls` for later retrieval\n",
    "\n",
    "We first submit the query as an async job to the `5h` queue, and store the job (the url) of the newly created job into a file `job_url.txt`. With this url we are able to retrieve the results (when it has finished) at any later time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "buried-oxygen",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Submit the query as an async job\n",
    "#\n",
    "query_name = \"\"\n",
    "lang = 'PostgreSQL'\n",
    "\n",
    "query = '''\n",
    "-- Mass accression history of a halo\n",
    "SELECT p.foftreeid, p.treesnapnum, p.mass, p.np\n",
    "  FROM mdr1.fofmtree AS p, \n",
    "       (SELECT foftreeid, mainleafid FROM mdr1.fofmtree \n",
    "         WHERE fofid=85000000000) AS mycl\n",
    " WHERE p.foftreeid BETWEEN mycl.foftreeid AND mycl.mainleafid\n",
    " ORDER BY p.treesnapnum\n",
    "'''\n",
    "\n",
    "job = tap_service.submit_job(query, language=lang, runid=query_name, queue=\"5h\")\n",
    "job.run()\n",
    "\n",
    "print('JOB %s: SUBMITTED' % (job.job.runid,))\n",
    "print('JOB %s: %s' % (job.job.runid, job.phase))\n",
    "\n",
    "#\n",
    "# Save the job's url in a file to later retrieve results.\n",
    "#\n",
    "print('URL: %s' % (job.url,))\n",
    "\n",
    "with open('job_url.txt', 'w') as fd:\n",
    "    fd.write(job.url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "charged-forty",
   "metadata": {},
   "source": [
    "### Retrieve the results at a later time\n",
    "\n",
    "In order to retrieve the results, we will first recreate the job from the `job_url` stored in the `job_url.txt` file and verify that the job is finished, by asking for its current phase. In case the job is finished we will retrieve the results as usual."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "loaded-final",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Recreate the job from url and session (token)\n",
    "#\n",
    "\n",
    "# read the url\n",
    "with open('job_url.txt', 'r') as fd:\n",
    "    job_url = fd.readline()\n",
    "\n",
    "# recreate the job \n",
    "job = pyvo.dal.AsyncTAPJob(job_url, session=tap_session)\n",
    "\n",
    "#\n",
    "# Check the job status\n",
    "#\n",
    "print('JOB %s: %s' % (job.job.runid, job.phase))\n",
    "\n",
    "# if still running --> exit\n",
    "if job.phase not in (\"COMPLETED\", \"ERROR\", \"ABORTED\"):\n",
    "    exit(0)\n",
    "\n",
    "#\n",
    "# Fetch the results\n",
    "#\n",
    "job.raise_if_error()\n",
    "print('\\nfetching the results...')\n",
    "tap_results = job.fetch_result()\n",
    "print('\\n...DONE\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fancy-weekly",
   "metadata": {
    "lines_to_next_cell": 0
   },
   "source": [
    "Thanks to this method you can submit a job, go for a coffee, write a paper and retrieve the results \n",
    "when it suits you. The job and its results are stored on the server side under your user account.\n",
    "\n",
    "The entire scripts can be here: [cosmosim-async-submit-5h.py](files/cosmosim-async-submit-5h.py) and [cosmosim-async-retrieve-5h.py](files/cosmosim-async-retrieve-5h.py)\n",
    "\n",
    "# Submitting multiple queries\n",
    "\n",
    "Some time it is needed to submit several queries at one time. Either because the entire query may last longer than 5 hours and you need to cut it in smaller parts, or because you need non `JOIN`-able information from various tables."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "advisory-circuit",
   "metadata": {
    "region_name": "md"
   },
   "source": [
    "## Your query is too long? Chunk it!\n",
    "\n",
    "Before contacting us and ask for longer queue time: You may try to cut long queries into smaller chunks, and execute them as a list of shorter queries. \n",
    "\n",
    "Let us consider this query:\n",
    "\n",
    "```sql\n",
    "SELECT \"source\".\"source_id\", \n",
    "       \"source\".\"tycho2_id\", \n",
    "       \"source\".\"tycho2_dist\", \n",
    "       \"source\".\"tycho2_nn_dist\",\n",
    "       \"lightcurve\".\"bmag\", \n",
    "       \"lightcurve\".\"bmagerr\", \n",
    "       \"lightcurve\".\"vmag\", \n",
    "       \"lightcurve\".\"vmagerr\"\n",
    "       \n",
    "FROM \"applause_dr3\".\"lightcurve\" AS \"lightcurve\"\n",
    "JOIN \"applause_dr3\".\"source_calib\" AS \"source\" \n",
    "  ON \"source\".\"source_id\" = \"lightcurve\".\"source_id\"\n",
    "\n",
    "WHERE \"lightcurve\".\"bmag\" > 10 \n",
    "  AND \"lightcurve\".\"bmagerr\" < 0.2\n",
    "\n",
    "ORDER BY \"lightcurve\".\"tycho2_id\"\n",
    "```\n",
    "\n",
    "This query selects some attributes of all sources with a B magnitude larger than 10 mag and an error bellow 0.2 mag. This query will surely take more than 1 hour, and need to be chunked.\n",
    "\n",
    "There are three typical ways to do this:\n",
    "* chunk via halo total mass: `mtot`\n",
    "* chunk via halo bound mass: `mvir`\n",
    "* chunk via Friend-of-Friend tree ID: `foftreeid`\n",
    "\n",
    "\n",
    "Before going into the detail of these methods we may define a useful function to submit a list of queries.\n",
    "\n",
    "This function basically takes a list of queries, and submit them one after another."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "arctic-huntington",
   "metadata": {},
   "outputs": [],
   "source": [
    "def submit_queries(tap_service, queries, lang='PostgreSQL', queue='1m', urls_filename='jobs_url.txt'):\n",
    "    '''Submit a serie of tap queries\n",
    "    \n",
    "    Parameters:\n",
    "    -----------\n",
    "    tap_service: pyvo.dal.tap.TAPService\n",
    "        The TAP service to which the query will be submitted\n",
    "    \n",
    "    queries: list(tuple)\n",
    "        A list consisting of (query_name, query_string) pairs\n",
    "\n",
    "    lang: str, default: PostgreSQL\n",
    "        The language in which the queries a written\n",
    "\n",
    "    queue: str, default: 1m\n",
    "        The name of the queue to use\n",
    "\n",
    "    urls_filename: str, default: jobs_url.txt\n",
    "        The filename of the file holding the urls of the submitted jobs (for later retrieval)\n",
    "    '''\n",
    "\n",
    "    # list of failed jobs\n",
    "    failed = []\n",
    "\n",
    "\n",
    "    # open the file to store the jobs: for later retrieval\n",
    "    fd = open('jobs_url.txt', 'w')\n",
    "\n",
    "\n",
    "    # submit all queries one after another\n",
    "    for name, query in queries:\n",
    "        \n",
    "        print('> Submitting : {name}'.format(name=name))\n",
    "\n",
    "        # Create the async job\n",
    "        try:\n",
    "            job = tap_service.submit_job(query, language=lang, runid=name, queue=queue)\n",
    "        except Exception as e:\n",
    "            print('ERROR could not create the job.')\n",
    "            print(e)\n",
    "            failed.append(runid)\n",
    "            continue        \n",
    "        \n",
    "        # Run the run\n",
    "        try:\n",
    "            job.run()\n",
    "        except Exception as e:\n",
    "            print('Error: could not run the job. Are you sure about: \\n - validity of the SQL query?\\n - valid language?\\n - sufficient quotas?\\n')\n",
    "            print(e)\n",
    "            failed.append(name)\n",
    "            continue\n",
    "\n",
    "        # Save the submitted jobs into a file\n",
    "        fd.write(job.url + '\\n')\n",
    "\n",
    "        \n",
    "    # Verify that all jobs have been submitted\n",
    "    try:\n",
    "        assert(failed == [])\n",
    "        print(\"\\nAll jobs were properly submitted.\")\n",
    "    except AssertionError:\n",
    "        print(\"\\nThe following jobs had failed: {jobs}\".format(jobs=failed))\n",
    "\n",
    "    fd.close() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "martial-damages",
   "metadata": {},
   "source": [
    "To fetch the results of the submitted queries, one can fetch the results via the job url."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "promotional-panel",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetch_results_of_complete_jobs(tap_service, urls_filename):\n",
    "    '''Fetch the results of complete jobs\n",
    "    \n",
    "    Parameters:\n",
    "    -----------\n",
    "    tap_service: pyvo.dal.tap.TAPService\n",
    "        The TAP service to which the query will be submitted\n",
    "    \n",
    "    urls_filename: str\n",
    "        The filename of the file holding the urls of the jobs\n",
    "    '''\n",
    "\n",
    "    running_job_names = []\n",
    "\n",
    "    #\n",
    "    # Recreate the job from url and session (token)\n",
    "    #\n",
    "\n",
    "    # read the url\n",
    "    with open(urls_filename, 'r') as fd:\n",
    "        job_urls = fd.readlines()\n",
    "    \n",
    "    # reopen the file to store the non finished jobs\n",
    "    fd = open(urls_filename, 'w')\n",
    "\n",
    "    # Query status and if COMPLETE fetch results\n",
    "    for job_url in job_urls:\n",
    "\n",
    "        # recreate the job \n",
    "        job = pyvo.dal.AsyncTAPJob(job_url.rstrip('\\n'), session=tap_session)\n",
    "\n",
    "        #\n",
    "        # Check the job status\n",
    "        #\n",
    "        print('JOB {name}: {status}'.format(name=job.job.runid , status=job.phase))\n",
    "\n",
    "        # if still running --> exit\n",
    "        if job.phase not in (\"COMPLETED\", \"ERROR\", \"ABORTED\"):\n",
    "            running_job_names.append(job.job.runid)\n",
    "            fd.write(job_url)\n",
    "            continue\n",
    "\n",
    "        #\n",
    "        # Fetch the results\n",
    "        #\n",
    "        try:\n",
    "            job.raise_if_error()\n",
    "            print('fetching the results...')\n",
    "            tap_results = job.fetch_result()\n",
    "            print('writing results to disk...\\n')\n",
    "            tap_results.votable.to_xml('./' + str(job.job.runid) + '.xml')\n",
    "        \n",
    "        except Exception as e:\n",
    "            running_job_names.append(job.job.runid)\n",
    "            fd.write(job_url)\n",
    "            print(e)\n",
    "    \n",
    "    print('...DONE\\n')\n",
    "\n",
    "    # Output still running jobs\n",
    "    try:\n",
    "        assert(running_job_names == [])\n",
    "    except AssertionError:\n",
    "        print(\"The following jobs are still executing: {}\".format(running_job_names))\n",
    "\n",
    "    fd.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "widespread-prisoner",
   "metadata": {
    "region_name": "md"
   },
   "source": [
    "## List of file queries\n",
    "\n",
    "Sometimes it is useful to just send all `.sql` queries present in a directory. For such purpose you can use comments to provide the proper parameters.\n",
    "\n",
    "Let us consider the file `radial_prof_massive_bdmv.sql`\n",
    "\n",
    "```sql\n",
    "-- Radial profile of most massive BDMV (z=0)\n",
    "\n",
    "-- LANGUAGE = PostgreSQL\n",
    "-- QUEUE = 1h\n",
    "\n",
    "SELECT * FROM bolshoi.bdmvprof\n",
    " WHERE bdmid =\n",
    "       (SELECT bdmid FROM bolshoi.bdmv\n",
    "         WHERE snapnum=416 ORDER BY mvir DESC LIMIT 1)\n",
    " ORDER BY rbin\n",
    "```\n",
    "\n",
    "The `language` and `queue` are prescibed as comments. The query can then be submitted in a script like the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "committed-interaction",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "\n",
    "#\n",
    "# Submit the query as an Asynchrone job\n",
    "#\n",
    "\n",
    "# find all .sql files in current directory\n",
    "queries_filename = sorted(glob.glob('./*.sql'))\n",
    "print('Sending %d examples' % (len(queries_filename),))\n",
    "\n",
    "# initialize test results\n",
    "jobs = []\n",
    "failed =  []\n",
    "\n",
    "# send all queries\n",
    "for query_filename in queries_filename:\n",
    "\n",
    "    # read the .SQL file\n",
    "    with open(query_filename, 'r') as fd:\n",
    "        query = ' '.join(fd.readlines())\n",
    "\n",
    "    # Set language from comments (default: PostgreSQL)\n",
    "    if 'LANGUAGE = ADQL' in query:\n",
    "        lang = 'ADQL'\n",
    "    else:\n",
    "        lang = 'PostgreSQL'\n",
    "\n",
    "    # Set queue from comments (default: 1m)\n",
    "    if 'QUEUE = 5h' in query:\n",
    "        queue = \"5h\"\n",
    "    elif 'QUEUE = 1h' in query:\n",
    "        queue = \"1h\"\n",
    "    elif 'QUEUE = 1m' in query:\n",
    "        queue = \"1m\"\n",
    "    else:\n",
    "        queue = \"1m\"\n",
    "\n",
    "\n",
    "    # Set the runid from sql filename\n",
    "    base = os.path.basename(query_filename)\n",
    "    runid = os.path.splitext(base)[0]\n",
    "    \n",
    "    print('\\n> Query : %s\\n%s\\n' % (runid, query))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rapid-jewel",
   "metadata": {},
   "source": [
    "The rest of the submission process and retrieval can be done in any manner. An example \n",
    "can be found here: [cosmosim-tutorial-from-files.py](files/cosmosim-tutorial-from-files.py)\n",
    "\n",
    "# Convert result to various python types\n",
    "\n",
    "The results obtained via the `fetch_results()` method returns a so called `TAPResults` object. The latter is essencially a `votable`. In case you are not familiar with `votables` here is a few tricks to get back to some more general pythonic types.\n",
    "\n",
    "## Print data to screen\n",
    "\n",
    "* Print the data:\n",
    "  ```python\n",
    "  tap_results.to_table().pprint(max_lines=10)\n",
    "  ```\n",
    "  It is important to notice the `max_lines` keyword, printing too many lines may crash a low-memory machine.\n",
    "  \n",
    "* Show as html (in a browser):\n",
    "  ```python\n",
    "  tap_results.to_table().show_in_browser(max_lines=10)\n",
    "  ```\n",
    "  It is important to notice the `max_lines` keyword, printing too many lines may crash a low-memory machine.\n",
    "  \n",
    "* Show in a notebook (ipython, jupyter or jupyterlab):\n",
    "  ```python\n",
    "  tap_results.to_table().show_in_notebook(display_length=10)\n",
    "  ```\n",
    "  It is important to notice the `display_length` keyword, printing too many lines may crash a low-memory machine.\n",
    "\n",
    "## Write data to file\n",
    "\n",
    "* Write the results to a VOTable\n",
    "  ```python\n",
    "  tap_results.votable.to_xml(\"filename.xml\")\n",
    "  ```\n",
    "  or\n",
    "  ```python\n",
    "  tap_results.to_table().write(\"filename.xml\", format='votable')\n",
    "  ```\n",
    "\n",
    "* Write the results to FITs file\n",
    "  ```python\n",
    "  tap_results.to_table().write(\"filename.fits\", format='fits')\n",
    "  ```\n",
    "\n",
    "* Write the results to CSV file (not recommanded: lose the metadata)\n",
    "  ```python\n",
    "  tap_results.to_table().write(\"filename.csv\")\n",
    "  ```\n",
    "\n",
    "## Convert to alternative format\n",
    "\n",
    "* Get a numpy array:\n",
    "  ```python\n",
    "  np_array = tap_results.to_table().as_array()\n",
    "  ```\n",
    "  \n",
    "* Get a Panda's DataFrame\n",
    "    ```python\n",
    "    df = tap_results.to_table().to_pandas()\n",
    "    ```\n",
    "    \n",
    "    * Get the header of DataFrame:\n",
    "        ```python\n",
    "        df.head()\n",
    "        ```\n",
    "\n",
    "\n",
    "# Archiving your jobs\n",
    "\n",
    "If you submit several large queries you may go over quota: set to 100 GB. In order to avoid to get over quota you may consider archiving your jobs. Archiving removes the data from the server side but keeps the SQL query. This allows to resubmit a query at a later time.\n",
    "\n",
    "Deleting (Archiving) a job with `pyvo` can be simply done that way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bigger-march",
   "metadata": {},
   "outputs": [],
   "source": [
    "job.delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "referenced-dietary",
   "metadata": {},
   "source": [
    "## Archiving all `COMPLETED` jobs\n",
    "\n",
    "A nice feature of the `TAP service` is to retrieve all jobs that are marked as `COMPLETED` and archive them at ones. This can be done as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "incorporated-myanmar",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Archiving all COMPLETED jobs\n",
    "#\n",
    "\n",
    "# obtain the list of completed job_descriptions\n",
    "completed_job_descriptions = tap_service.get_job_list(phases='COMPLETED')\n",
    "\n",
    "# Archiving each of them\n",
    "for job_description in completed_job_descriptions:\n",
    "    \n",
    "    # get the jobid\n",
    "    jobid = job_description.jobid\n",
    "    \n",
    "    # recreate the url by hand\n",
    "    job_url = tap_service.baseurl + '/async/' + jobid\n",
    "    \n",
    "    # recreate the job\n",
    "    job = pyvo.dal.AsyncTAPJob(job_url, session=tap_session)\n",
    "    \n",
    "    print('Archiving: {url}'.format(url=job_url))\n",
    "    job.delete() # archive job"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "appreciated-toilet",
   "metadata": {},
   "source": [
    "## Rerunning `ARCHIVED` jobs\n",
    "\n",
    "Rerunning and retrieving results from a job that have been archived previously, can be achieved that way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "shared-collective",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Rerunning Archived jobs\n",
    "#\n",
    "\n",
    "# obtain the list of the two last ARCHIVED job_descriptions\n",
    "archived_job_descriptions = tap_service.get_job_list(phases='ARCHIVED', last=2)\n",
    "\n",
    "# rerunning the two last Archived jobs\n",
    "for job_description in archived_job_descriptions:\n",
    "    \n",
    "    # get jobid\n",
    "    jobid = job_description.jobid\n",
    "    \n",
    "    # recreate the url by hand\n",
    "    job_url = tap_service.baseurl + '/async/' + jobid\n",
    "    \n",
    "    # recreate the archived job\n",
    "    archived_job = pyvo.dal.AsyncTAPJob(job_url, session=tap_session)\n",
    "\n",
    "    # get the language (with a bit of magic)\n",
    "    lang = [parameter._content for parameter in archived_job._job.parameters if parameter._id == 'query_language'][0]\n",
    "\n",
    "    # extract the query\n",
    "    query = archived_job.query\n",
    "    \n",
    "    # resubmit the query with corresponding parameters\n",
    "    job = tap_service.submit_job(query, language=lang, runid='rerun', queue='1m')\n",
    "    print('%(url)s :\\n%(query)s\\n' % {\"url\": job_url, \"query\": query})\n",
    "    \n",
    "    # start the job\n",
    "    try:\n",
    "        job.run()\n",
    "    except pyvo.dal.DALServiceError:\n",
    "        raise ValueError(\"Please check that the SQL query is valid, and that the SQL language is correct.\")    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "buried-grave",
   "metadata": {},
   "source": [
    "Retrieving the results is done alike explained above.\n",
    "\n",
    "If you prefer you can also filter for a given `runid`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "thirty-kansas",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Filtering by runid\n",
    "#\n",
    "\n",
    "target_runid = 'radial_prof_massive_bdmv'\n",
    "\n",
    "# obtain the list of completed job_descriptions\n",
    "archived_job_descriptions = tap_service.get_job_list(phases='ARCHIVED')\n",
    "\n",
    "for job_description in archived_job_descriptions:\n",
    "    \n",
    "    # select the job with runid fitting target_runid\n",
    "    if job_description.runid == target_runid:\n",
    "        \n",
    "        # get jobid\n",
    "        jobid = job_description.jobid\n",
    "    \n",
    "        # recreate the url by hand\n",
    "        job_url = tap_service.baseurl + '/async/' + jobid\n",
    "    \n",
    "        # recreate the archived job\n",
    "        archived_job = pyvo.dal.AsyncTAPJob(job_url, session=tap_session)\n",
    "\n",
    "        # get the language (with a bit of magic)\n",
    "        lang = [parameter._content for parameter in archived_job._job.parameters if parameter._id == 'query_language'][0]\n",
    "\n",
    "        # extract the query\n",
    "        query = archived_job.query\n",
    "    \n",
    "        # resubmit the query with corresponding parameters\n",
    "        job = tap_service.submit_job(query, language=lang, runid='rerun', queue='1m')\n",
    "        print('%(url)s :\\n%(query)s\\n' % {\"url\": job_url, \"query\": query})\n",
    "    \n",
    "        # start the job\n",
    "        try:\n",
    "            job.run()\n",
    "        except pyvo.dal.DALServiceError:\t\n",
    "            raise ValueError(\"Please check that the SQL query is valid, and that the SQL language is correct.\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.8.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}