0 votes
21 views
Hi,

I want to cross-match with ls_dr8.photoz, but that table doesn't have RA,Dec columns.

I therefore used the cross-match tool with my uploaded csv file to match to the ls_dr8.tractor table, saving to "mydb.matchmg".

Then I want to join that with the photoz table.  But I'm getting an error that is not very helpful:

My query is

"

SELECT * FROM mydb.matchmg AS mg
JOIN ls_dr8.photoz AS p
ON mg.ls_id = p.ls_id;

"

and error is

"Error: IllegalArgumentException: Table [ mydb.matchmg AS mg ] is not found in TapSchema"

I've tried a number of other variants, but not having any luck.

Thanks for any tips...

--dustin
asked Aug 7 by dstndstn (120 points) | 21 views

2 Answers

0 votes
When using MyDB in a query you need to specify the table using a URI syntax, e.g  "mydb://matchmg".  Please let us know if you still have problems.
answered Aug 7 by datalab (8,420 points)
Hi, Thanks for the answer, but I did try that, and no luck:

"SELECT * FROM mydb://matchmg AS mg
JOIN ls_dr8.photoz AS p
ON mg.ls_id = p.ls_id;
"


"Error: IllegalArgumentException: Table [ mydb."matchmg" AS mg ] is not found in TapSchema"
I don't see a 'matchmg' table in your mydb schema, however I did find a zombie foreign table definition in the main database that could either be preventing the table from being created or later used.  I've deleted the bad link, but you'll need to try to recreate the 'matchmg' again before using it.
I created "matchmg" using the xmatch service.
And when I go to recreate the "matchmg" table using the xmatch tool, I already see it listed in the MyDB list.
0 votes

The issue is you are using adql instead of sql, if you are using notebook, probably change it from 

queryClient.query(adql='SELECT * FROM mydb://matchmg AS mg JOIN ls_dr8.photoz AS p ON mg.ls_id = p.ls_id;', async_=False)

to 

queryClient.query(sql='SELECT * FROM mydb://matchmg AS mg JOIN ls_dr8.photoz AS p ON mg.ls_id = p.ls_id;', async_=False)

answered Aug 7 by whuang (180 points)
Hi,

I'm not using a notebook, I am using the query interface web site.

https://datalab.noao.edu/query.php?name=ls_dr8.photo_z

(And yes, I see that the table is called "photo_z" not "photoz", so I updated my query to

SELECT * FROM mydb://matchmg AS mg
JOIN ls_dr8.photo_z AS p
ON mg.ls_id = p.ls_id;

still no luck.)
Sorry, my mistake. ls_dr8.photo_z does exist.

The query interface is using adql by default. The problem with adql in this case is it cannot query against mydb table, so you would have to use datalab command line tool or notebook server to run sql query.

If you have a datalab command line tool installed, you can run:

datalab query --sql="SELECT * FROM mydb://matchmg AS mg JOIN ls_dr8.photo_z AS p ON mg.ls_id = p.ls_id;" --fmt="votable"

But be sure to login first using
datalab login
So there is no way to use the query interface to cross-match to the ls_dr8.photo_z table?
Yes, that is correct. We currently does not support that in the web query interface. But you can use notebook server or datalab command line tool to cross-match ls_dr8.photo_z table.

192 questions

189 answers

176 comments

1,335 users

Welcome to Data Lab Help Desk, where you can ask questions and receive answers from other members of the community.