Hi all! I’m doing something very similar but am running into errors using either the notebook or portal approach. Has anyone run into similar issues? Screenshots below.
Hi @eastonhonaker , thanks for posting.
Based on the error message, it looks like the service thinks the user table ut1 does not exist. As I understand it, ut1 needs to be an astropy table and to exist in memory. To help with the diagnosis of this error, we need the code snippet that shows how ut1 is defined.
And here’s a bit of extra info that might help. In the examples in this related topic, ut1 is generated from SDSS.query_region and is an astropy table. In the example in this DP0.3 tutorial notebook, ut1 is read in from a file that has column names in the first row with no # symbol at the start of the row.
from astropy.table import Table
fnm1 = 'data/dp03_06_user_table_1.cat'
ut1 = Table.read(fnm1, format='ascii.basic')
Let us know if that solves the issue, and/or post the code snippet where the table is defined if the error persists?
Hi @MelissaGraham, thank you for the help! I get the feeling I’m missing something simple here. I’m reading in a csv file as an astropy table, and I renamed the table from ut1 to something I’m more used to. However, I’m still running into the same issue. The table is not exceptionally long, it’s ~4400 rows and well below the 32MB user upload limit. Here are some code snippets from how the table is defined. I get the same error when I directly upload the table and do a multi-object search in the portal.
#import targets from Backyard worlds
BYW = Table.read('reducedBYW.csv')
BYW.rename_column(';shortname','shortname')
#just in case, make the columns long
BYW['BYWid'] = BYW['BYWid'].astype('long')
BYW['catWISE_RA'] = BYW['catWISE_RA'].astype('long')
BYW['catWISE_Dec'] = BYW['catWISE_Dec'].astype('long')
The BYW table only has 4 columns, shortname is a string that doesn’t get used in the query.
#query to grab crossmatches and photometry from DP1 matching on the BYW ra and decs
query = """
SELECT objectId, coord_ra, coord_dec, u_calibFlux, g_calibFlux, r_calibFlux, i_calibFlux, z_calibFlux, y_calibFlux,
BYW.BYWid AS BYW_id, BYW.catWISE_RA AS BYW_ra, BYW.catWISE_Dec AS BYW_dec
FROM dp1.Object, TAP_UPLOAD.BYW AS BYW
WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec), CIRCLE('ICRS', BYW.catWISE_RA, BYW.catWISE_Dec, 0.0027))=1
ORDER BY coord_ra ASC
"""
job = rsp_tap.submit_job(query, uploads={"BYW": BYW})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result()
print(len(results))
Hi @eastonhonaker , thanks this is informative.
- “I get the same error when I directly upload the table and do a multi-object search in the portal.”
This makes me think the error is actually a problem with the table format, not that it doesn’t exist to TAP (despite that being the error message). Try visualizing the table by executing a single cell just containing BYW. Does it look like a table, are the columns full of values?
Next, I suspect that converting the RA and Dec to long integers might give you a problem, as RA and Dec need to be non-integer values, and to be treated as such (e.g., float). The other topic only converted a column to long because TAP would not accept type unit64 (a different integer type).
Try those things and report back? If these fixes don’t work, sharing the first ~5 lines of the file you’re using might be helpful, if that’s possible.
Hi @MelissaGraham, the table doesn’t have any blank spaces. I switched back from the long to floats and integers as the columns originally were . Here’s a full pdf of the notebook and error.
BYWinDP1.pdf (126.9 KB)
I made a short csv file with just the five:
byw.csv (223 Bytes)
The following set of code will load that table and cross match it. There are no matches, since those 5 don’t overlap the DP1 fields, but that’s ok. It all does seem to be working as it should be.
Imports.
from astropy.table import Table
import matplotlib.pyplot as plt
from lsst.rsp import get_tap_service
rsp_tap = get_tap_service("tap")
Load table.
ut1 = Table.read('byw.csv', format='csv')
ut1
Define query.
query = """
SELECT objectId, coord_ra, coord_dec,
ut1.catWISE_RA AS ut1_ra, ut1.catWISE_Dec AS ut1_dec, ut1.BYWid AS ut1_objid
FROM dp1.Object, TAP_UPLOAD.ut1 AS ut1
WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
CIRCLE('ICRS', ut1.catWISE_RA, ut1.catWISE_Dec, 0.00027))=1
ORDER BY coord_ra ASC
"""
Execute query with cross-match.
job = rsp_tap.submit_job(query, uploads={"ut1": ut1})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
job.raise_if_error()
No errors, so proceed to fetch.
assert job.phase == 'COMPLETED'
results = job.fetch_result()
print(len(results))
Make a plot to illustrate why there are zero matches.
names = ['47Tuc', 'LowEclLat', 'Fornax', 'ECDFS',
'EDFS', 'LowGalLat', 'Seagull']
ras = [6.02, 37.86, 40.00, 53.13, 59.10, 95.00, 106.23]
decs = [-72.08, 6.98, -34.45, -28.10, -48.73, -25.00, -10.51]
plt.plot(ras, decs, 's', ms=20, mew=0, alpha=0.5, color='grey')
plt.plot(ut1['catWISE_RA'], ut1['catWISE_Dec'], 'o', mew=0, alpha=1, color='blue')
plt.xlabel('RA')
plt.ylabel('Dec')
plt.show()
And here it is all in one:
user_uploaded_tables_BYW.ipynb (19.2 KB)
Let us know if that helps – and mark this reply as the solution if it does?
Hi @MelissaGraham thank you for the notebook! I’m still getting the same Query Error: Qserv request failed: error: MySQL partitions removal failed for database: user_eastonhonaker, table: BYW_akkj1ftweu8jqgeo, worker: db02, error: BYW_akkj1ftweu8jqgeo:NO_SUCH_TABLE:Connection[2532]::execute(_inTransaction=1) mysql_real_query failed, query: 'ALTER TABLE us`, even using the notebook above. Are there any office hour/open help hours I can bring this to? If it’s helpful, this is the csv file, but I don’t think that’s where the issue is. While there are no matches in the first 5 sources, I do expect some matches later on.
reducedBYW.csv (154.8 KB)