same SQL query executed until timing out in queryClient.query with async_=False, but meet error with async_=True

Question

Hello, I want to get the counting of sources in Gaia_dr3.gaia_source with different Healpixs, colors, and magnitudes. I run a SQL query and it runs successfully but time out. Therefore, I use async_=True to longer the query time but meets an error. 

from dl import queryClient as qc

querysen='SELECT healpix_, phot_g_mean_mag_, g_rp_, COUNT(*) AS n FROM (SELECT ceiling(source_id/562949953421312) AS healpix_, ceiling(floor((phot_g_mean_mag - 8)/0.2)) AS phot_g_mean_mag_, ceiling(floor((g_rp - 0.5)/0.1)) AS g_rp_ FROM gaia_dr3.gaia_source WHERE phot_g_mean_mag > 8 AND phot_g_mean_mag < 18 AND g_rp > 0.5 AND g_rp < 1.5) AS subquery GROUP BY healpix_, phot_g_mean_mag_, g_rp_'

result = qc.query(sql=querysen,fmt='csv', async_=False)

but when I use job=qc.query(sql=querysen,fmt='csv', async_=True), I get an error with "Error: UnsupportedOperationException: sub-select not supported in FROM clause."

I find that the same error if I use adql query in result = qc.query(adql=querysen,fmt='csv', async_=False).

Therefore, If I use async_=True, I can only use adql query rather than sql query?

Answer 1

Hi, thanks for reaching out. Indeed, sub-selects are not supported in async queries. (and adql queries are async). The typical work-around for your use case is that you first generate a table for the subquery, and store ii in your MyDB. Then run your outer query on that new table.

To create a MyDB table from a query:

from dl import queryClient as qc
query = """SELECT ceiling(source_id/562949953421312) AS healpix_, [...] FROM gaia_dr3.gaia_source WHERE phot_g_mean_mag > 8 AND [...]"""
jobid = qc.query(query,async_=True,out="mydb://mytablename")

You can check on the progress of the job with

print(qc.status(jobid))

Once it says 'COMPLETED'', the table can be used:

SELECT healpix_, phot_g_mean_mag_, g_rp_, COUNT(*) AS n
FROM mydb://mytable
GROUP BY healpix_, phot_g_mean_mag_, g_rp_