0 votes
38 views

Hi Datalab team,

I have a query as below:

aquery = """
        SELECT ra as RA, dec as DEC, dered_mag_g as mag_g, dered_mag_r as mag_r, dered_mag_z as mag_z, 
               dered_mag_w1 as mag_w1, dered_mag_w2 as mag_w2, dered_flux_g as flux_g, dered_flux_r as flux_r,
               dered_flux_z as flux_z, dered_flux_w1 as flux_w1, dered_flux_w2 as flux_w2
        FROM ls_dr8.tractor_s
        WHERE snr_z>3  and dered_mag_w1 > 15.0 and  dered_mag_w2 > 15.0 
        and dered_mag_g > 20.  and dered_mag_r > 20. and dered_mag_z > 17.5
        and dered_mag_w1 != 'NaN' and dered_mag_w2 != 'NaN'  
        and dered_mag_w1 != 'Infinity' and dered_mag_w2 != 'Infinity' and ra < 40
        """
### divide ra in 60, 120, 240


#print (qc.schema('ls_dr8.tractor_s'))


### async mode returns jobid
jobid = qc.query(token, adql=aquery, format='pandas', async_=True)
status = ''
while (status != 'COMPLETED' and status != 'ERROR'):
    status = qc.status(token,jobid)
    print (status)   # Python 2; for Python 3 instead use print(status)
    time.sleep(2)
    
response=qc.results(token,jobid)
df = convert(response,'pandas')

df.to_csv('Decals_DR8S1.csv')

But it did not execute correctly. Each times it returns:

EXECUTING
ERROR
CPU times: user 76 ms, sys: 3 ms, total: 79 ms
Wall time: 4.33 s
asked Sep 28 by huanian (170 points) | 38 views

1 Answer

0 votes
If you call qc.error(token,jobid)  what does it output?
answered Sep 28 by ascottdemo (780 points)
Here is the error message, I have no any idea about it.

error message  Error: IllegalArgumentException: net.sf.jsqlparser.JSQLParserException JSQLParserException:  ParseException: Encountered "." at line 7, column 29.
Was expecting one of:
    <EOF>
    "GROUP" ...
    "LIMIT" ...
    "ORDER" ...
    "OFFSET" ...
    "HAVING" ...
    ";" ...
    "*" ...
    "/" ...
    "+" ...
    "-" ...
    "AND" ...
    "OR" ...
    "UNION" ...
Thank you for that.
Try replacing 20. with 20.0 and 15. with 15.0.  The ADQL parser is misinterpreting 20.
It works. Thanks a lot.
The resultset may be huge on this query.  If it's too large, it won't finish.

So to be sure,  try  SELECT TOP 100000 ra as RA ...

Notice the TOP 100000

If you really need to work with millions of rows, it may also be useful to save the output to MyDB and work with it from there.
Thanks for the reminder. Yes, it is going to be huge. But I need all those rows, since I am trying to find some new objects from all-sky survey. I will retrieve those rows and then I will download to my local disk form my datalab account home folder.
Hi,

I got another error, which I also do not quite get it. It is strange that it runs very well a few days ago.

The code is pretty much the same as above, as  follows:

aquery = """
        SELECT ra as RA, dec as DEC, dered_mag_g as mag_g, dered_mag_r as mag_r, dered_mag_z as mag_z,
               dered_mag_w1 as mag_w1, dered_mag_w2 as mag_w2, dered_flux_g as flux_g, dered_flux_r as flux_r,
               dered_flux_z as flux_z, dered_flux_w1 as flux_w1, dered_flux_w2 as flux_w2
        FROM   ls_dr8.tractor_n
        WHERE  dered_mag_z > 13.5 and dered_mag_z < 23.5 and dered_mag_w1 != 'NaN' and dered_mag_w2 != 'NaN'  
               and dered_mag_w1 != 'Infinity' and dered_mag_w2 != 'Infinity' and ra < 120
        """

### divide ra in 120, 180, 240

### ls_dr8.tractor is the main catalog combining both north and south

### async mode returns jobid
jobid = qc.query(token, adql=aquery, format='pandas', async_=True)
status = ''
while (status != 'COMPLETED' and status != 'ERROR'):
    status = qc.status(token,jobid)
    print (status)   # Python 2; for Python 3 instead use print(status)
    time.sleep(2)

response=qc.results(token,jobid)
df = convert(response,'pandas')

df.to_csv('Decals_DR8N1.csv')



EXECUTING
COMPLETED
/data0/sw/anaconda2/lib/python2.7/site-packages/IPython/core/magic.py:188: DtypeWarning: Columns (2,3) have mixed types. Specify dtype option on import or set low_memory=False.
  call = lambda f, *a, **k: f(*a, **k)
CPU times: user 6min 44s, sys: 24.3 s, total: 7min 9s
Wall time: 26min 57s
Hi,
just following up on this.  Are you past this warning about mixed types?

198 questions

195 answers

183 comments

1,364 users

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