examples/using_fastdb_client.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b03cc028-749b-4503-a318-65f0a48e7b11",
   "metadata": {},
   "source": [
    "# Using fastdb_client\n",
    "\n",
    "This example is for running in Perlmutter.  You may be able to easily convert it for somewhere else.\n",
    "\n",
    "Note that this example notebook was built when `fastdb-rknop-dev.lbl.gov` had the ELAsTiCC2 data loaded into it.  It's possible at some later time that you will not get the same results.  When a more stable version of fastdb exists, I will update this example notebook.\n",
    "\n",
    "## What is fastdb_client\n",
    "\n",
    "It's a python program useful for connecting to instances of FASTDB.  At some level, it's just a wrapper around python `requests`, but it handles some other stuff you wouldn't want to handle manually.  For example, logging into FASTDB is not just a matter of sending a password over the net, but is a complicated handshaking of public key cryptography (created by somebody trying to be paranoid to make sure the password itself never needed to be sent over the network from your machine to the server machine).  The fastdb_client takes care of all of that for you.  It has the ability to do some format parsing of responses from the web server.  Finally, it has a built-in \"sleep and retry\" cycle, where if the connection to the FASTDB server fails for some reason (which does happen--- this is the Internet, after all), it will sleep a few seconds and retry, repeating that (by default) five times before giving up for good.  This can greatly increase the reliability of scripts that use the fastdb_client, saving you from having to implement your own error catching and retry loop.\n",
    "\n",
    "## Getting an account\n",
    "\n",
    "To use FASTDB, you need an account on the instance of the FASTDB web server you want to interact with.  As of this writing, the only one that exists is `https://fastdb-rknop-dev.lbl.gov`, and as its name suggests, it's a development server that may be down at any time, or that may have unexpected changes of contents and state at any time.  To get an account, ask Rob Knop (via Discovery Alliance Slack) for an account; I will need the username you want for the account, the name you want displayed when you are logged in, and the email address associated with the account.  Once I tell you the account is created, go to the webserver in your browser, and click on \"Request Password Reset\".  Put in your username, and click \"Email Password Resent Link\".  Shortly thereafter, in your email you should find a message with a link to click on to reset your password.  (When your account is first created, it does not have a password; you can not log into it until you set one.)\n",
    "\n",
    "Make sure to choose a <a href=\"http://rknop.net/password.html\">good password</a>.  FASTDB is currently not using any TFA, and I'm really hoping we won't feel the need to go that way, as that will make a lot of life a lot more complicated.\n",
    "\n",
    "### Setting up a convenience initialization file\n",
    "\n",
    "You can always just use the url, username, and password every time you connect to fastdb.  However, it will probably make your life easier if you create a file `.fastdb.ini` (notice the period at the beginning) in your home directory.  This file will have contents like:\n",
    "\n",
    "```\n",
    "[rknop-dev]\n",
    "url = https://fastdb-rknop-dev.lbl.gov\n",
    "username = <your username>\n",
    "password = <your password>\n",
    "\n",
    "[production]\n",
    "url = https://desc-fastdb.lbl.gov\n",
    "username = <your username>\n",
    "password = <your password>\n",
    "```\n",
    "\n",
    "Each block of lines starting with a line in brackets represents one FASTDB server you might want to connect to.  The name in brackets can be whatever you want; it will be wha tyou use in order to specify the server you want to conenct to.  Below each bracketed line, you need three lines, similar to the example above, which specify the URL of the web server, your username on that server, and your password on that server.  (As of this writing you do not want to include the `[production]` block because that server does not exist.)\n",
    "\n",
    "**Important**: the fastdb client will refuse to use this `.ini` file if it's permissions aren't sufficiently paranoid.  After you've created the file, run\n",
    "```\n",
    "cd\n",
    "chmod go -rwx .fastdb.ini\n",
    "```\n",
    "to make sure that only you can read it.\n",
    "\n",
    "There are actually a number of other configuration options you can specify here, but for now they're not that important.  Talk to Rob, or read the `fastdb_client.py` source code if you're interested.\n",
    "\n",
    "## Setting up your environment\n",
    "\n",
    "Hopefully, before long, the necessary python libraries will be in the desc `td_env` environment.  The FASTDB client requires that `pycryptodome` be installed (which, if you're building your own environment, is a simple matter of `pip install pycryptodome`), but as of this writing it's not in `td_env`.\n",
    "\n",
    "### Running from the command line\n",
    "\n",
    "If you're running from the command line, you can get a minimal environment that has what you need to run `fastdb` by running\n",
    "```\n",
    "source /global/cfs/cdirs/lsst/groups/TD/SOFTWARE/fastdb_deployment/fastdb_client_venv/bin/activate\n",
    "```\n",
    "Then, later, just run `deactivate` to leave the environment.\n",
    "\n",
    "### Running from jupyter\n",
    "\n",
    "At some point in the future, you will be able to just the `desc-td` kernel.  But, as of this writing, that kernel doesn't have the necessary `pycryptodome` libraries.\n",
    "\n",
    "To set yourself up, on perlmutter, run\n",
    "```\n",
    "cd\n",
    "mkdir -p .local/share/jupyter/kernels/fastdb-client\n",
    "cd .local/share/jupyter/kernels/fastdb-client\n",
    "```\n",
    "once you're in that directory (which the last `cd` command did), create a text file `kernel.json` with the following contents:\n",
    "```\n",
    "{\n",
    "  \"argv\": [\n",
    "    \"/global/cfs/cdirs/desc-td/SOFTWARE/fastdb_deployment/fastdb_client_venv/fastdb_client-env.sh\",\n",
    "    \"-f\",\n",
    "    \"{connection_file}\"\n",
    "  ],\n",
    "  \"display_name\": \"fastdb-client-env\",\n",
    "  \"language\": \"python\"\n",
    "}\n",
    "```\n",
    "\n",
    "Thereafter, if you log into Perlmutter's Jupyter hub, you should be able to start a notebook that uses the `fastdb-client-env` kernel.  This example script was run within that kernel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9dc7702b-5b4b-4782-9775-034d5cc6fac6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The FastDB client can be found on Perlmutter in the directory\n",
    "#   /global/cfs/cdirs/lsst/groups/TD/SOFTWARE/fastdb_deployment/fastdb_client\n",
    "# (The client there is actually a symbolic link to something else underneath\n",
    "# fastdb_deployment.  If you know what you're doing, you may want to use a\n",
    "# version of the client other than the one there, but by default that's the\n",
    "# right one to use.)  Put this directory in your PYTHONPATH so it will be found.\n",
    "import sys\n",
    "sys.path.insert( 0, '/global/cfs/cdirs/lsst/groups/TD/SOFTWARE/fastdb_deployment/fastdb_client' )\n",
    "\n",
    "#...and import!\n",
    "from fastdb_client import FASTDBClient\n",
    "\n",
    "# Also import other stuff we'll need\n",
    "import io\n",
    "import time\n",
    "import pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8fa70078-91e4-4a1e-8c28-651e65d41d11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create your connection to fastdb.  This will log you in.\n",
    "#\n",
    "# There are two ways to do this.\n",
    "# You can always just specify the url, username, and password, with \n",
    "#   something like:\n",
    "#      fdb = FASTDBClient( 'https://fastdb-rknop-dev.lbl.gov', 'rknop', 'not_really_robs_password' )\n",
    "#   where the three arguments are url, username, and password.  BE CAREFUL PUTTING PASSWORDS IN SCRIPTS,\n",
    "#   and NEVER commit a script that has a password in it to a git archive anywhere!  If you do, immmediately\n",
    "#   change your password.\n",
    "# The other alternative is just to use the name you set up in your ~/.fastdb.ini file (described above).\n",
    "#   that's what I do here.\n",
    "\n",
    "fdb = FASTDBClient( \"rknop-dev\" )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0ee9191-5a7b-42e9-bc01-afd47f8ff1d8",
   "metadata": {},
   "source": [
    "## WEB API Endpoints\n",
    "\n",
    "(Nothing currently exists.  Watch this space.)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96d7179f-0e56-446c-a7e0-ec28b0782209",
   "metadata": {},
   "source": [
    "## Direct SQL Queries\n",
    "\n",
    "You can make make direct read-only queries to the PostgreSQL database behind the FASDTB webserver.  You construct queries and substitution dictionaries just like you'd send to `psycopg2`'s `cursor.execute` method.  (Use the `%(name)s` format for substitutions in your query, *not* the simple `%s` format where you'd pass a list as the second argument of `cursor.execute.)\n",
    "\n",
    "There are two interfaces.  The \"short\" query interface is for queries that will complete in less than five minutes.  (This is actually not a database restriction, but a restriction of the web proxy in front of the FASTDB web server; it will time any connection out after five minutes.)  The \"long\" query interface is for longer queries.  It normally has a cycle of submitting a query, repeatingly checking to see if it's done, and then asking for the results.  However, the fastdb_client has a function that wraps all of that into one call that's as simple to use as the short SQL query.\n",
    "\n",
    "For both interfaces, you can either pass a single query, or a list of queries.  If you pass a list of queries, you will only get the results from the *last* query.  If you want the results of multiple queries, send them each in individually.  The purpose of allowing multiple queries is so that you can play games with temporary tables and such while doing a complicated query.  If you past a list of queries, then you must also pass a list of substitution dictionaries that has the same length as your query list.\n",
    "\n",
    "Using either interface requires you to understand the structures of the tables.  In particular, you need to undertand about `processing_version` and `snapshot`.  If you do not, and just look at columns in the `diaobject` and `diasource` tables, there is an excellent chance you will not get what you think you are getting.  See ROB PUT IN THE PLACE WHERE YOU DOCUMENT THE DATABASE SCHEMA AND STRUCTURE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "533ba52a-dc5a-48e4-8eda-888eb74b0f45",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Got back a <class 'list'> of length 10\n",
      "The keys in the first element of the resonse are: dict_keys(['diaobjectid', 'processing_version', 'radecmjdtai', 'validitystart', 'validityend', 'ra', 'raerr', 'dec', 'decerr', 'ra_dec_cov', 'nearbyextobj1', 'nearbyextobj1id', 'nearbyextobj1sep', 'nearbyextobj2', 'nearbyextobj2id', 'nearbyextobj2sep', 'nearbyextobj3', 'nearbyextobj3id', 'nearbyextobj3sep', 'nearbylowzgal', 'nearbylowzgalsep', 'parallax', 'parallaxerr', 'pmra', 'pmraerr', 'pmra_parallax_cov', 'pmdec', 'pmdecerr', 'pmdec_parallax_cov', 'pm_ra_dec_cov'])\n"
     ]
    }
   ],
   "source": [
    "# The short query interface is for queries that take less than 5 minutes.\n",
    "#\n",
    "# If all is well, you get back either a list of dictionaries, or a dictionary of\n",
    "# column→list, depending on how you call the short query interface.\n",
    "\n",
    "# A quick example: let's get the 10 objects with the lowest diaobjectid.  (This isn't something that\n",
    "# would usually be useful, but it's an easy example.)  (What's more, it's a misuse\n",
    "# of FASTDB, because it's ignoring processing_version, which you should never do.)\n",
    "\n",
    "res = fdb.submit_short_sql_query( \"SELECT * FROM diaobject ORDER BY diaobjectid LIMIT 10\" )\n",
    "print( f\"Got back a {type(res)} of length {len(res)}\" )\n",
    "print( f\"The keys in the first element of the resonse are: {res[0].keys()}\" )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e2d2c19e-f526-47d4-a97d-7553ba4d0ca2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Got back a <class 'dict'> with 30 keys; res['diaobjectid'] has length 10\n",
      "The diaobjectids returned are 1000052, 1000065, 1000111, 1000154, 1000155, 1000168, 1000283, 1000294, 1000296, 1000344\n"
     ]
    }
   ],
   "source": [
    "# While a list of dictionaries is sometimes convenient, it's also a wasteful format,\n",
    "# as the JSON that's passed from server to client repeats all of the column names every\n",
    "# single row.  You can also ask for a dictionary of columns to lists of values.\n",
    "# Do this by passing return_format=1 to submit_short_sql_query.\n",
    "\n",
    "res = fdb.submit_short_sql_query( \"SELECT * FROM diaobject ORDER BY diaobjectid LIMIT 10\", return_format=1 )\n",
    "print( f\"Got back a {type(res)} with {len(res.keys())} keys; res['diaobjectid'] has length {len(res['diaobjectid'])}\" )\n",
    "print( f\"The diaobjectids returned are {', '.join([ str(i) for i in res['diaobjectid'] ])}\" )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2210cdcf-7bda-4b89-86e6-3ab58cdd6547",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 objects within 4.00 arcmin of (228.00000, 5.00000)\n"
     ]
    }
   ],
   "source": [
    "# You can pass variables when constructing your query. The query below is going to ask for all objects within\n",
    "#   4 arcminutes of (RA, Dec) = (228°, 5.°) \n",
    "# The q3c_radial_query function is from the q3c postgres extension:\n",
    "#   https://github.com/segasai/q3c\n",
    "# A number of the tables have q3c indices defined for faster spatial querying.\n",
    "#\n",
    "# NOTE: as before, this is not acutally a query you'd ever want to do, because\n",
    "#  it's totally ignoring processing_version.\n",
    "\n",
    "query = \"SELECT * FROM diaobject WHERE q3c_radial_query(ra,dec,%(ra)s,%(dec)s, %(rad)s)\"\n",
    "subs = { 'ra': 228., 'dec': 5., 'rad': 240./3600. }\n",
    "res = fdb.submit_short_sql_query( query, subs, return_format=0 )\n",
    "print( f\"{len(res)} objects within {subs['rad']*60.:.2f} arcmin of ({subs['ra']:.5f}, {subs['dec']:.5f})\" )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "efdbeed1-aa96-4b76-b3f6-3260b3c8301a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>diaobjectid</th>\n",
       "      <th>processing_version</th>\n",
       "      <th>radecmjdtai</th>\n",
       "      <th>validitystart</th>\n",
       "      <th>validityend</th>\n",
       "      <th>ra</th>\n",
       "      <th>raerr</th>\n",
       "      <th>dec</th>\n",
       "      <th>decerr</th>\n",
       "      <th>ra_dec_cov</th>\n",
       "      <th>...</th>\n",
       "      <th>nearbylowzgalsep</th>\n",
       "      <th>parallax</th>\n",
       "      <th>parallaxerr</th>\n",
       "      <th>pmra</th>\n",
       "      <th>pmraerr</th>\n",
       "      <th>pmra_parallax_cov</th>\n",
       "      <th>pmdec</th>\n",
       "      <th>pmdecerr</th>\n",
       "      <th>pmdec_parallax_cov</th>\n",
       "      <th>pm_ra_dec_cov</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111922253</td>\n",
       "      <td>0</td>\n",
       "      <td>61475.324</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>228.027582</td>\n",
       "      <td>None</td>\n",
       "      <td>4.988687</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15157720</td>\n",
       "      <td>0</td>\n",
       "      <td>61552.105</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>228.015401</td>\n",
       "      <td>None</td>\n",
       "      <td>5.030694</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85647835</td>\n",
       "      <td>0</td>\n",
       "      <td>61828.375</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>228.063170</td>\n",
       "      <td>None</td>\n",
       "      <td>4.987572</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28724374</td>\n",
       "      <td>0</td>\n",
       "      <td>61222.066</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>227.999486</td>\n",
       "      <td>None</td>\n",
       "      <td>5.046197</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>114967799</td>\n",
       "      <td>0</td>\n",
       "      <td>61120.387</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>228.040880</td>\n",
       "      <td>None</td>\n",
       "      <td>4.968512</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   diaobjectid  processing_version  radecmjdtai validitystart validityend  \\\n",
       "0    111922253                   0    61475.324          None        None   \n",
       "1     15157720                   0    61552.105          None        None   \n",
       "2     85647835                   0    61828.375          None        None   \n",
       "3     28724374                   0    61222.066          None        None   \n",
       "4    114967799                   0    61120.387          None        None   \n",
       "\n",
       "           ra raerr       dec decerr ra_dec_cov  ...  nearbylowzgalsep  \\\n",
       "0  228.027582  None  4.988687   None       None  ...              None   \n",
       "1  228.015401  None  5.030694   None       None  ...              None   \n",
       "2  228.063170  None  4.987572   None       None  ...              None   \n",
       "3  227.999486  None  5.046197   None       None  ...              None   \n",
       "4  228.040880  None  4.968512   None       None  ...              None   \n",
       "\n",
       "  parallax  parallaxerr  pmra pmraerr  pmra_parallax_cov pmdec pmdecerr  \\\n",
       "0     None         None  None    None               None  None     None   \n",
       "1     None         None  None    None               None  None     None   \n",
       "2     None         None  None    None               None  None     None   \n",
       "3     None         None  None    None               None  None     None   \n",
       "4     None         None  None    None               None  None     None   \n",
       "\n",
       "  pmdec_parallax_cov pm_ra_dec_cov  \n",
       "0               None          None  \n",
       "1               None          None  \n",
       "2               None          None  \n",
       "3               None          None  \n",
       "4               None          None  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note that the data you get back both with `return_format=0` and `return_format=1`\n",
    "# are suitable for direct import into a Pandas dataframe.\n",
    "#\n",
    "# (You will probably notice that most of the columns have \"None\" in them.  Some of\n",
    "# that is because the table includes columns that had no corresponence in the \n",
    "# the SNANA simulations of ELAsTiCC2.)\n",
    "df = pandas.DataFrame( res )\n",
    "df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be7c63c2-e7e4-4350-a35e-f4453f250f5e",
   "metadata": {},
   "source": [
    "### The long query interface\n",
    "\n",
    "If your query is going to take more than 5 minutes, a call to `submit_short_sql_query` will time out.  (The backend database server will actually still be performing your query, but the web proxy will time out.)  FASTDB also has a system whereby you can submit a query (or a sequence of queries) to be run, but instead of the results, you get back a query id.  A job running on the backend server keeps track of which of these \"long queries\" have been submitted, and submits them in order, saving the results.  (The backend run several processes, so it can work on a handful of long queries at once.)  The normal way of using this long query interface is through the following methods of FASTDBClient:\n",
    "\n",
    "* `submit_long_sql_query` : send the query and substitution dictionary (or queries and substitution dictionaries, if you want to send a sequence of queries and only get the results from the last one).  Get back an opaque string (called the \"query id\") that you will use to identify this query.\n",
    "* `check_long_sql_query` : send the query id, get back a response from the server that tells you the state of the query.  It will tell you if the query is still queued, if it has started, if it errored out, or if it has finished.\n",
    "* `get_long_sql_query_result` : only call this once `check_long_sql_query` tells you the query is finished.  Send the query id, get back the results.  The format of the results depends on the `return_format `parameter you passed to `submit_long_sql_query`.  Right now, only `return_format='csv'` is supported.  You will get back a string that is the contents of a CSV file with your query results.\n",
    "\n",
    "Look at the dockstrings in `fastdb_client.py` for more information about using this interface.  Normally, you would save the returned query id somewhere, and every so often run a new script that sends that query id and looks for results.  However, there's an easier interface that works in a manner very similar to `submit_short_sql_query`, it just takes longer.\n",
    "\n",
    "### An easier (but, of course, less flexible) way\n",
    "\n",
    "The FASTDBClient method `synchronous_long_sql_query` will take care of all the calls above.  You call it with your query or queries, wait a long time, and get the results back.  In addition to the first two arguments (which are the query and substitution dictionary), you can pass a `return_format` (which defaults to `csv`, which is currently the only thing installed).  Two more parameters, `checkeach` (defaults to 300) and `maxwait` (defaults to 3600) tell it how often to check if your query is finished.  `checkeach` is the number of seconds to wait between checking if your query is finished.  It won't even check the first time before this much time has elapsed, so you know your call to `synchronous_long_sql_query` will take at least this long.  Please do not set `checkeach` to anything less than (say) 30 seconds.  (Indeed, if you're setting it to less than 300 seconds, there's an excellent chance you could just use the short sql query interface.)  After waiting `maxwait` seconds, it will give up altogether.  The reason to set this is if (for instance) the backend query runner has crashed; in that case, your query will languish in 'queuing mode' forever.  If you have confidence that the backend is working right, you can set `maxwait` to something huge, and eventually you will get back either the results of your query, or an execption will be raised if the query errored out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5ddea3a9-d1d0-4834-bdd4-7f0a0c9ce04c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2025-02-26 08:49:53 - FASTDB - INFO] - Submitted query fb13e1b3-45cf-4978-969d-3087b8d29785\n",
      "[2025-02-26 08:50:24 - FASTDB - INFO] - Long query started at 2025-02-26T16:50:00.745752+00:00 and finished at 2025-02-26T16:50:18.692953+00:00\n",
      "[2025-02-26 08:50:24 - FASTDB - INFO] - Got long query result after 30.13036185799865 seconds.\n",
      "synchronous long query returned after 30.4 seconds."
     ]
    },
    {
     "data": {
      "text/plain": [
       "51"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A more complicated example long query.  We're first going to look for all detections within a given MJD range.\n",
    "# We'll then pull out lightcurves of all objects found in that MJD range.\n",
    "# You could probably do this with a single long SQL query.  However, it's easier to think about this\n",
    "# with multiple queries.  You *could* run the first query to get the object ids, and then send a second\n",
    "# query where you pass all the object IDs.  However, that is not the most efficient way to do it; SQL\n",
    "# queries where you have a long list in a WHERE ... IN(...) clause can be inefficient.  Instead, we're\n",
    "# going to use a temporary table.  This also allows us to demonstrate sending a series of queries.\n",
    "#\n",
    "# We are using processing_version here, but normally you'd probably actually use snapshot rather than\n",
    "# processing version, which would make this query more complicated.  Consider this an advertisement for\n",
    "# web api endpoints.\n",
    "\n",
    "subdict = { 'pv': 'elasticc2' }\n",
    "queries = [\n",
    "    # The first query is to the diasource table, which is a table of detections\n",
    "    # The LIMIT and OFFSET here are because there's a N² scaling in the next query\n",
    "    #   after this one.  By limiting ourselves to small nubmers of objects, we\n",
    "    #   end up getting everyting out an order of magnitude faster.  (By sorting\n",
    "    #   by diaobjectid and doing LIMIT and OFFSET, you can iterate through\n",
    "    #   a list.  You'd have to do an earlier query to get out how many objects\n",
    "    #   were going to be in the list so you know how far to increment OFFSET;\n",
    "    #   perhaps there will be an example later.)\n",
    "    # The hint (IndexScan...) doesn't really seem to make much difference here *if*\n",
    "    #   VACUUM ANALYZE has been recently run on the database.  It's the difference\n",
    "    #   between a bitmap scan and a regular scan of the index.  Postgres wants to\n",
    "    #   do a bitmap scan, but the regular scan runs in ~2/3 the time here.\n",
    "    ( \"/*+ IndexScan(s) */ \"\n",
    "      \"SELECT s.diaobjectid, s.diaobject_procver INTO TEMP TABLE temp_diaobjectid \"\n",
    "      \"    FROM diasource s \"\n",
    "      \"    INNER JOIN processing_version p ON s.processing_version=p.id \"\n",
    "      \"    WHERE p.description=%(pv)s AND s.midpointmjdtai>61110. AND s.midpointmjdtai<61115. \"\n",
    "      \"    ORDER BY s.diaobjectid \"\n",
    "      \"    LIMIT 1000 OFFSET 0 \" ),\n",
    "    # Now that the temp_diabojectid table has the objects we care about,\n",
    "    #   get the lightcurves from the diaforcedsource table\n",
    "    # Notice the query hint (\"IndexScan...\").  Postgres's query optimizer normally \n",
    "    #   decides *not* to use the index when joining, but (at least for modest numbers\n",
    "    #   of objects in the table above) the query runs a whole lot faster when it uses\n",
    "    #   the index.  How would you know to put this hint in? Yet another reason why we \n",
    "    #   should have web APIs instead of asking users to manually craft SQL queries.\n",
    "    ( \"/*+ IndexScan(f idx_diaforcedsource_diaobjectidpv) */ \"\n",
    "      \"SELECT f.diaobjectid,f.midpointmjdtai,f.band,f.psfflux,f.psffluxerr \"\n",
    "      \"    FROM temp_diaobjectid t \"\n",
    "      \"    INNER JOIN diaforcedsource f ON t.diaobjectid=f.diaobjectid \"\n",
    "      \"                                 AND t.diaobject_procver=f.diaobject_procver \"\n",
    "      \"    INNER JOIN processing_version p ON f.processing_version=p.id \"\n",
    "      \"    WHERE p.description=%(pv)s \"\n",
    "      \"    ORDER BY f.diaobjectid,f.midpointmjdtai,f.band \" )\n",
    "]\n",
    "# For substitution dictionaries, both queries just have %(pv)s in them, so we can \n",
    "# use the same substitution dictionary for both queries.\n",
    "subdicts = [ subdict, subdict ]\n",
    "\n",
    "# Submit the query.  Because I'm impatient, I'm setting the checkeach to 30 seconds, but\n",
    "#   this is not good practice, because if the query runs that fast, just use the short\n",
    "#   query interface!  But, this is for demonstration purposes.\n",
    "t0 = time.perf_counter()\n",
    "res = fdb.synchronous_long_sql_query( queries, subdicts, checkeach=30 )\n",
    "sys.stderr.write( f\"synchronous long query returned after {time.perf_counter()-t0:.1f} seconds.\" )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "00fa06e3-fab0-425c-a9bc-a9d5d147f8fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>diaobjectid</th>\n",
       "      <th>midpointmjdtai</th>\n",
       "      <th>band</th>\n",
       "      <th>psfflux</th>\n",
       "      <th>psffluxerr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1001305</td>\n",
       "      <td>60936.3934</td>\n",
       "      <td>r</td>\n",
       "      <td>449.98105</td>\n",
       "      <td>915.24490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1001305</td>\n",
       "      <td>60936.3934</td>\n",
       "      <td>r</td>\n",
       "      <td>449.98105</td>\n",
       "      <td>915.24490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1001305</td>\n",
       "      <td>60936.3964</td>\n",
       "      <td>r</td>\n",
       "      <td>-1908.21410</td>\n",
       "      <td>1227.92310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1001305</td>\n",
       "      <td>60936.3964</td>\n",
       "      <td>r</td>\n",
       "      <td>-1908.21410</td>\n",
       "      <td>1227.92310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1001305</td>\n",
       "      <td>60936.3995</td>\n",
       "      <td>r</td>\n",
       "      <td>1484.39820</td>\n",
       "      <td>1467.50100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245913</th>\n",
       "      <td>245913</td>\n",
       "      <td>1316680</td>\n",
       "      <td>61887.1168</td>\n",
       "      <td>r</td>\n",
       "      <td>2227.22880</td>\n",
       "      <td>203.25096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245914</th>\n",
       "      <td>245914</td>\n",
       "      <td>1316680</td>\n",
       "      <td>61887.1413</td>\n",
       "      <td>i</td>\n",
       "      <td>-491.00250</td>\n",
       "      <td>246.25208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245915</th>\n",
       "      <td>245915</td>\n",
       "      <td>1316680</td>\n",
       "      <td>61890.1340</td>\n",
       "      <td>r</td>\n",
       "      <td>1878.30710</td>\n",
       "      <td>192.39696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245916</th>\n",
       "      <td>245916</td>\n",
       "      <td>1316680</td>\n",
       "      <td>61892.2074</td>\n",
       "      <td>z</td>\n",
       "      <td>-972.12640</td>\n",
       "      <td>487.86780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245917</th>\n",
       "      <td>245917</td>\n",
       "      <td>1316680</td>\n",
       "      <td>61892.2322</td>\n",
       "      <td>Y</td>\n",
       "      <td>1852.25680</td>\n",
       "      <td>1436.26320</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>245918 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0  diaobjectid  midpointmjdtai band     psfflux  psffluxerr\n",
       "0                0      1001305      60936.3934    r   449.98105   915.24490\n",
       "1                1      1001305      60936.3934    r   449.98105   915.24490\n",
       "2                2      1001305      60936.3964    r -1908.21410  1227.92310\n",
       "3                3      1001305      60936.3964    r -1908.21410  1227.92310\n",
       "4                4      1001305      60936.3995    r  1484.39820  1467.50100\n",
       "...            ...          ...             ...  ...         ...         ...\n",
       "245913      245913      1316680      61887.1168    r  2227.22880   203.25096\n",
       "245914      245914      1316680      61887.1413    i  -491.00250   246.25208\n",
       "245915      245915      1316680      61890.1340    r  1878.30710   192.39696\n",
       "245916      245916      1316680      61892.2074    z  -972.12640   487.86780\n",
       "245917      245917      1316680      61892.2322    Y  1852.25680  1436.26320\n",
       "\n",
       "[245918 rows x 6 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remember, what we got back was the text contents of a CSV file.\n",
    "# We can read this into pandas by using a StringIO object\n",
    "# to fake a file for it to read.\n",
    "df = pandas.read_csv( io.StringIO(res), header=0 )\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4b36e00-7074-4ab0-a317-de1f9cc310f9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "fastdb-client-env",
   "language": "python",
   "name": "fastdb-client"
  },
  "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": 5
}