def download_query(self, query, table_name, save_to='data', context='MyDB'): """Perform a query based on the context given""" if context != 'MyDB': jobid = cj.submitJob(query, context=context) cj.waitForJob(jobid, verbose=True) job_status = cj.getJobStatus(jobid) if job_status['Success'] != 5: raise CasJobsError( 'Error Performing the query, {}'.format(job_status)) # Now the results are safely saved in myDB # You can go ahead and download by using a session self.download_csv(table_name) self._download_from_scidrive(table_name, save_to)
def testCasJobsSubmit(token): tbl="mriiscplanck1_"+str(randint(0,1000000)) sql="""select top 10 galaxyId,snapnum,stellarmass into """+tbl+""" from MRIIscPlanck1""" jobId= CasJobs.submitJob(sql,context="Henriques2015a",token=token) print("jobId=",jobId) jobDesc = CasJobs.waitForJob(jobId) print( jobDesc)
def test_CasJobs_submitJob(self): jobId = CasJobs.submitJob(sql=CasJobs_TestQuery + " into MyDB." + CasJobs_TestTableName1, context=CasJobs_TestDatabase) jobDescription = CasJobs.waitForJob(jobId=jobId, verbose=True) df = CasJobs.executeQuery(sql="DROP TABLE " + CasJobs_TestTableName1, context="MyDB", format="csv") self.assertNotEqual(jobId, "")
def createQueries(username, password, amount, table_name, context): CasJobs_Query = "SELECT top " + str( amount ) + " statement into [myscratch:default]." + table_name + " FROM sdssweblogs.SqlLog WHERE CHARINDEX('FROM PhotoObjAll', statement) > 0 AND CHARINDEX('p.dec BETWEEN', statement) > 0 AND CHARINDEX('p.ra BETWEEN', statement) > 0 AND access='Skyserver.Search.SQL'" login(username, password) jobId = CasJobs.submitJob(sql=CasJobs_Query, context=context) jobDescription = CasJobs.waitForJob(jobId=jobId, verbose=True) print( "Data has been saved on sciserver at context myscratch with tablename " + table_name)
def createData(username, password, dec_low, dec_high, ra_low, ra_high, table_name, context, all_data): CasJobs_Database = "DR16" CasJobs_Query = "Select ra,dec into [myscratch:default]." + table_name + " from dr16.photoobjall " if (not all_data): CasJobs_Query += "where dec between " + str(dec_low) + " and " + str( dec_high) + " and ra between " + str(ra_low) + " and " + str( ra_high) login(username, password) jobId = CasJobs.submitJob(sql=CasJobs_Query, context=context) jobDescription = CasJobs.waitForJob(jobId=jobId, verbose=True) print( "Data has been saved on sciserver at context myscratch with tablename " + table_name)
tname = 'test' data = CasJobs.getTables('MyDB') if data: print(data) print( 'There are already tables in your database under that name, removing them before doing new query...' ) #CLEAN UP: delete table from myDB SkyQuery.dropTable(tableName=tname, datasetName='myDB') #long asynchronous job print( 'submitting SQL job to SciServer... could take some time depending on query length and SciServer load...' ) jobID = CasJobs.submitJob(query + "into myDB." + tname, "dr14") CasJobs.waitForJob(jobID, verbose=True) try: #Download table from MYDB into Pandas dataframe. additional parms: , top=10 print('Attempting to download table...') data_table = SkyQuery.getTable(tableName=tname, datasetName='MyDB') print('Done! Table shape is: ' + str(data_table.shape)) #save df to disk in case of super long queries: filename = 'test_query_table_1000' print('Saving tables to disk as: ' + filename) save_obj(data_table, filename) except: print( 'ERROR, No data found in your SciServer database, query may not have completed, check your SQL syntax?' ) #see tables in MyDB
def test_CasJobs_waitForJob(self): jobId = CasJobs.submitJob(sql=CasJobs_TestQuery, context=CasJobs_TestDatabase) jobDescription = CasJobs.waitForJob(jobId=jobId, verbose=True) self.assertGreaterEqual(jobDescription["Status"], 3)
# In[ ]: #execute a quick SQL query: df = CasJobs.executeQuery(sql=CasJobs_TestQuery, context=CasJobs_TestDatabase, format="pandas") print(df) # In[ ]: #submit a job, which inserts the query results into a table in the MyDB database context. #Wait until the job is done and get its status. jobId = CasJobs.submitJob(sql=CasJobs_TestQuery + " into MyDB." + CasJobs_TestTableName1, context="MyDB") jobDescription = CasJobs.waitForJob(jobId=jobId, verbose=False) print(jobId) print(jobDescription) # In[ ]: # drop or delete table in MyDB database context df = CasJobs.executeQuery(sql="DROP TABLE " + CasJobs_TestTableName1, context="MyDB", format="pandas") print(df) # In[ ]: #get job status
# Example of a longer query: get magnitudes and sizes (Petrosian radii) of one million galaxies verylongquery = 'select top 10 objid, ra, dec \n' verylongquery += 'u, g, r, i, z, err_u, err_g, err_r, err_i, err_z, petror90_r \n' verylongquery += 'into mydb.' + bigtablename + '\n' verylongquery += 'from galaxy\n' verylongquery += 'where clean = 1' print('Submitting query:\n', verylongquery) print('\n') thisjobid = CasJobs.submitJob(sql=verylongquery, context=this_context) print('Job submitted with jobId = ', thisjobid) print('\n') waited = CasJobs.waitForJob( jobId=thisjobid) # waited is a dummy variable; just print wait msg jobDescription = CasJobs.getJobStatus(thisjobid) print('\n') print('Information about the job:') #pprint(jobDescription) jobDescriber(jobDescription) # ## Thank you! # # Thanks for reviewing this SciServer example notebook. You can use this notebook as a template to develop your own notebooks, but please do so in a copy rather than in the original example notebook. # As you begin to use any of our SciServer modules in your own notebooks, consult the SciServer scripting documentation at http://www.sciserver.org/docs/sciscript-python/SciServer.html (link opens in a new window). # # If you have questions, please email the SciServer helpdesk at [email protected].
sql_drop = """IF OBJECT_ID('spectrain') IS NOT NULL DROP TABLE spectrain""" CasJobs.executeQuery(sql=sql_drop, context='MYDB', format="pandas") sql_get = """ select * from MYDB.NIPSspecphoto order by objid {} OFFSET {} ROWS FETCH NEXT 1407000 ROWS ONLY;""".format('', 1000000 * ii) # ii+=1 df = CasJobs.executeQuery(sql='SELECT * FROM N15', context='MYDB', format="pandas") jobid = CasJobs.submitJob(sql=sql_spec, context='DR13') CasJobs.waitForJob(jobid) df = CasJobs.executeQuery(sql=sql_spec, context='DR13', format="pandas") df = CasJobs.executeQuery(sql='SELECT * FROM MYDB.spectrain', context='MYDB', format="pandas") df.shape df.to_csv('../data/sdss_stars/DR13/544k_spec_objid.csv', index=False) ''' select class, subclass, u-g as ug, g-r as gr, r-i as ri, i-z as iz, u-r as ur, g-i as gi, r-z as rz, u-i as ui, g-z as gz, u-z as uz, (u-15)/7 as u, (g-15)/7 as g, (i-15)/7 as i, (z-15)/7 as z, (r-15)/5 as r, u-0.5*ext_u as u05, g-0.5*ext_g as g05, r-0.5*ext_r as r05, i-0.5*ext_i as i05, z-0.5*ext_z as z05, u-0.25*ext_u as u25, g-0.25*ext_g as g25, r-0.25*ext_r as r25, i-0.25*ext_i as i25,z-0.25*ext_z as z25,