def metadata_worker(): while True: global consumer token = consumer.poll(0) # file_name = input("Input file name \n") if token is None: break print("all done") else: token = msgpack.unpackb(token, raw=True) if token[0] == 'metadata': CasJobs.executeQuery("INSERT Successful INTO TABLE test_table", context='MyDB', format='json')
def retrieve_sdss_photometry(ra: float, dec: float): """ Retrieve SDSS photometry for a given field, in a 0.2 x 0.2 degree box centred on the passed coordinates coordinates. (Note - the width of the box is in RA degrees, not corrected for spherical distortion) :param ra: Right Ascension of the centre of the desired field, in degrees. :param dec: Declination of the centre of the desired field, in degrees. :return: Retrieved photometry table, as a pandas dataframe, if successful; if not, None. """ try: from SciServer import Authentication, CasJobs except ImportError: print("It seems that SciScript/SciServer is not installed, or not accessible to this environment. " "\nIf you wish to automatically download SDSS data, please install " "\nSciScript (https://github.com/sciserver/SciScript-Python); " "\notherwise, retrieve the data manually from " "\nhttp://skyserver.sdss.org/dr16/en/tools/search/sql.aspx") return None print(f"Querying SDSS DR16 archive for field centring on RA={ra}, DEC={dec}") user = keys['sciserver_user'] password = keys["sciserver_pwd"] Authentication.login(UserName=user, Password=password) # Construct an SQL query to send to SciServer query = "SELECT objid,ra,dec" for f in sdss_filters: query += f",psfMag_{f},psfMagErr_{f},fiberMag_{f},fiberMagErr_{f},fiber2Mag_{f},fiber2MagErr_{f},petroMag_{f},petroMagErr_{f} " query += "FROM PhotoObj " query += f"WHERE ra BETWEEN {ra - 0.1} AND {ra + 0.1} " query += f"AND dec BETWEEN {dec - 0.1} AND {dec + 0.1} " print(f"Retrieving photometry from SDSS DR16 via SciServer for field at {ra}, {dec}...") df = CasJobs.executeQuery(sql=query, context='DR16') if len(df.index) == 0: df = None return df
def testCasJobsQuery(token): sql="""select top 10 galaxyId,snapnum,stellarmass from MRIIscPlanck1""" queryResponse=CasJobs.executeQuery(sql,context="Henriques2015a",token=token) gals= pandas.read_csv(queryResponse,index_col=None) print gals return gals
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 test_CasJobs_uploadPandasDataFrameToTable_uploadCSVDataToTable(self): try: df = pandas.read_csv(StringIO(CasJobs_TestTableCSV), index_col=None) result = CasJobs.uploadPandasDataFrameToTable( dataFrame=df, tableName=CasJobs_TestTableName2, context="MyDB") table = CasJobs.executeQuery(sql="select * from " + CasJobs_TestTableName2, context="MyDB", format="pandas") result2 = CasJobs.executeQuery(sql="DROP TABLE " + CasJobs_TestTableName2, context="MyDB", format="csv") self.assertEqual(result, True) self.assertItemsEqual(table, df) result = CasJobs.uploadCSVDataToTable( csvData=CasJobs_TestTableCSV, tableName=CasJobs_TestTableName2, context="MyDB") df2 = CasJobs.executeQuery(sql="select * from " + CasJobs_TestTableName2, context="MyDB", format="pandas") result2 = CasJobs.executeQuery(sql="DROP TABLE " + CasJobs_TestTableName2, context="MyDB", format="csv") self.assertEqual(result, True) self.assertItemsEqual(df, df2) finally: try: csv = CasJobs.executeQuery(sql="DROP TABLE " + CasJobs_TestTableName2, context="MyDB", format="csv") except: pass
def testCasJobsQuery(token): sql="""select top 10 galaxyId,snapnum,stellarmass from MRIIscPlanck1""" gals= CasJobs.executeQuery(sql,context="Henriques2015a",format="pandas", token=token) print("PANDAS") print(gals) gals= CasJobs.executeQuery(sql,context="Henriques2015a",format="csv", token=token) print("CSV") print(gals) gals= CasJobs.executeQuery(sql,context="Henriques2015a",format="json", token=token) print("JSON") print(gals) gals= CasJobs.executeQuery(sql,context="Henriques2015a",format="readable", token=token) print("READABLE") gals=pandas.read_csv(gals) print(gals) gals= CasJobs.executeQuery(sql,context="Henriques2015a",format="blabal", token=token) print("ERROR") print(gals) return gals
print(casJobsId) # In[ ]: #get info about tables inside MyDB database context: tables = CasJobs.getTables(context="MyDB") print(tables) # 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[ ]:
def test_CasJobs_executeQuery(self): df = CasJobs.executeQuery(sql=CasJobs_TestQuery, context=CasJobs_TestDatabase, format="pandas") self.assertEqual(CasJobs_TestTableCSV, df.to_csv(index=False))
password = "******" token = Authentication.login(username, password) user = Authentication.getKeystoneUserWithToken(token) #csv = open("./metadata_table.csv", 'r') #csv_str = csv.read() #csv.close() #success = CasJobs.uploadCSVDataToTable(csv_str, "osc_metadata") #print(success) query1 = "create table osc_metadata (FileName varchar(255), FileSize varchar(255))" CasJobs.executeQuery(sql=query1, format='json') # get file name and size f = argv[1] f_name = os.path.basename(f) f_size = os.path.getsize(f) print(f_name) print(f_size) # upload data to table query2 = "insert into osc_metadata (FileName, FileSize) values (" + "'" + f_name[ 0:2] + "''" + f_name[2:3] + "''" + f_name[3:12] + "''" + f_name[ 12] + "''" + f_name[13:] + "'" + ", " + "'" + str(f_size) + "'" + ")"
# Don't show python warnings warnings.filterwarnings('ignore') print("SciServer library imported") print('Supporting libraries imported') print('Settings applied') # This query finds all objects from galSpecInfo # with extra data pulled from PhotoObjAll. query = """ SELECT g.specObjID, p.type, p.u, p.g, p.r, p.i, p.z, g.spectrotype FROM galSpecInfo AS g JOIN PhotoObjAll as p ON p.specObjID = g.specObjID ORDER BY g.specObjID """ # Then, query the database. The answer is a table that is # being returned as a dataframe. q = CasJobs.executeQuery(query, "dr14") # Check data and drop any row that has NaN. q.dropna() # Check that all types in columns are as expected. q.info() # Write to file. q.to_csv("result.csv") print("SQL query finished.")
def do(self, user='******', password='******', search=1, path_to_model='YSE_App\\data_ingest\\RF_model.sav'): """ Predicts photometric redshifts from RA and DEC points in SDSS An outline of the algorithem is: first pull from SDSS u,g,r,i,z magnitudes from SDSS; should be able to handle a list/array of RA and DEC place u,g,r,i,z into a vector, append the derived information into the data array predict the information from the model return the predictions in the same order to the user inputs: Ra: list or array of len N, right ascensions of target galaxies in decimal degrees Dec: list or array of len N, declination of target galaxies in decimal degrees search: float, arcmin tolerance to search for the object in SDSS Catalogue path_to_model: str, filepath to saved model for prediction Returns: predictions: array of len N, photometric redshift of input galaxy """ nowdate = datetime.datetime.utcnow() - datetime.timedelta(1) from django.db.models import Q #HAS To Remain Here, I dunno why print('Entered the photo_z cron') #save time b/c the other cron jobs print a time for completion transients = (Transient.objects.filter(Q(host__photo_z__isnull=True))) #print('Number of test transients:', len(transients)) RA = [] #Needs to be list b/c don't know how many hosts are None DEC = [] outer_mask = [ ] #create an integer index mask that we will place values into because some hosts dont have a RA and DEC assigned for i, transient_obj in enumerate(transients): if transient_obj.host != None: RA.append(transient_obj.host.ra) DEC.append(transient_obj.host.dec) outer_mask.append(i) #provides integer index mask outer_mask = np.array(outer_mask) #make that an array N_outer = len(transients) #gives size of returned array Ra = np.array(RA) Dec = np.array(DEC) N = len(Ra) #gives size of query array Q = N // 1000 #decompose the length of transients needing classification if N % 1000 != 0: Q = Q + 1 #catch remainder and start a new batch total_job = [] #store all pandas job dataframes for j in range(Q): #iterate over batches if j == (Q - 1): Ra_batch = Ra[j * 1000:((j + 1) * 1000 + N % 1000)] #grab batch remainder Dec_batch = Dec[j * 1000:((j + 1) * 1000 + N % 1000)] else: Ra_batch = Ra[j * 1000:(j + 1) * 1000] #other wise grab batch of 1000 Dec_batch = Dec[j * 1000:(j + 1) * 1000] hold = [ ] #a list for holding the strings that I want to place into an sql query for val in range(len(Ra_batch)): string = '({},{},{}),|'.format(str(val), str(Ra[val]), str(Dec[val])) hold.append(string) #Now construct the full query sql = "CREATE TABLE #UPLOAD(|id INT PRIMARY KEY,|up_ra FLOAT,|up_dec FLOAT|)|INSERT INTO #UPLOAD| VALUES|" for data in hold: sql = sql + data #there is a comma that needs to be deleted from the last entry for syntax to work sql = sql[0:(len(sql) - 2)] + '|' #append the rest to it sql = sql + "SELECT|p.u,p.g,p.r,p.i,p.z|FROM #UPLOAD as U|OUTER APPLY dbo.fGetNearestObjEq((U.up_ra),(U.up_dec),{}) as N|LEFT JOIN Galaxy AS p ON N.objid=p.objid".format( str(search)) #change all | to new line: when we change to Unix system will need to change this new line sql = sql.replace('|', '\n') #login, change to some other credentials later Authentication.login('awe2', 'StandardPassword') job = CasJobs.executeQuery( sql, 'DR15', 'pandas') #this lines sends and retrieves the result print('Query {} of {} complete'.format(j + 1, Q)) job['u-g'] = job['u'].values - job['g'].values job['g-r'] = job['g'].values - job['r'].values job['r-i'] = job['r'].values - job['i'].values job['i-z'] = job['i'].values - job['z'].values job['u_over_z'] = job['u'].values / job['z'].values total_job.append(job) #print('left the query loop') query_result = pd.concat(total_job) #now feed to a RF model for prediction X = query_result.values #load the model, will need to change the path later model = pickle.load(open(path_to_model, 'rb')) #Need to deal with NANs now since many objects are outside the SDSS footprint, later models will learn to deal with this #ideas: need to retain a mask of where the nans are in the row mask = np.invert((query_result.isna().any(1).values )) #true was inside SDSS footprint #also will want this mask in indices so we can insert the predicted data correctly indices = [] for i, val in enumerate(mask): if val == True: indices.append(i) predictions = model.predict((X[mask, :])) #make nan array with size of what user asked for return_me = np.ones(N) * np.nan #now replace nan with the predictions in order return_me[indices] = predictions #something is wrong here!, line works inside a try statement but not outside. raises no error for some reason...? return_me_outer = np.ones(N_outer) * np.nan return_me_outer[outer_mask] = return_me #print('debug: made it here') print('time taken:', datetime.datetime.utcnow() - nowdate) print('uploading now') for t, pz in zip(transients, return_me): #print('1') host = t.host #print('2') host.photo_z = pz #print('3') host.save() #print('4') print('time taken with upload:', datetime.datetime.utcnow() - nowdate)
def do(self,user='******',password='******',search=1,path_to_model='YSE_App/data_ingest/YSE_DNN_photoZ_model_315.hdf5'): """ Predicts photometric redshifts from RA and DEC points in SDSS An outline of the algorithem is: first pull from SDSS u,g,r,i,z magnitudes from SDSS; should be able to handle a list/array of RA and DEC place u,g,r,i,z into a vector, append the derived information into the data array predict the information from the model return the predictions in the same order to the user inputs: Ra: list or array of len N, right ascensions of target galaxies in decimal degrees Dec: list or array of len N, declination of target galaxies in decimal degrees search: float, arcmin tolerance to search for the object in SDSS Catalogue path_to_model: str, filepath to saved model for prediction Returns: predictions: array of len N, photometric redshift of input galaxy """ try: nowdate = datetime.datetime.utcnow() - datetime.timedelta(1) from django.db.models import Q #HAS To Remain Here, I dunno why print('Entered the photo_z cron') #save time b/c the other cron jobs print a time for completion transients = (Transient.objects.filter(Q(host__photo_z__isnull=True) & Q(host__isnull=False))) #print('Number of test transients:', len(transients)) RA=[] #Needs to be list b/c don't know how many hosts are None DEC=[] outer_mask = [] #create an integer index mask that we will place values into because some hosts dont have a RA and DEC assigned transients_withhost = [] for i,transient_obj in enumerate(transients): if transient_obj.host != None: RA.append(transient_obj.host.ra) DEC.append(transient_obj.host.dec) outer_mask.append(i) #provides integer index mask outer_mask = np.array(outer_mask) #make that an array N_outer = len(transients) #gives size of returned array Ra = np.array(RA) Dec = np.array(DEC) N = len(Ra)#gives size of query array Q = N//1000#decompose the length of transients needing classification if N%1000 != 0: Q=Q+1 #catch remainder and start a new batch total_job = [] #store all pandas job dataframes for j in range(Q): #iterate over batches if j == (Q-1): Ra_batch = Ra[j*1000:((j+1)*1000 + N%1000)] #grab batch remainder Dec_batch = Dec[j*1000:((j+1)*1000 + N%1000)] else: Ra_batch = Ra[j*1000:(j+1)*1000] #other wise grab batch of 1000 Dec_batch = Dec[j*1000:(j+1)*1000] hold=[] #a list for holding the strings that I want to place into an sql query for val in range(len(Ra_batch)): string = '({},{},{}),|'.format(str(val),str(Ra[val]),str(Dec[val])) hold.append(string) #Now construct the full query sql = "CREATE TABLE #UPLOAD(|id INT PRIMARY KEY,|up_ra FLOAT,|up_dec FLOAT|)|INSERT INTO #UPLOAD| VALUES|" for data in hold: sql = sql + data #there is a comma that needs to be deleted from the last entry for syntax to work sql = sql[0:(len(sql)-2)] + '|' #append the rest to it sql = sql + "SELECT|p.u,p.g,p.r,p.i,p.z,p.extinction_u,p.extinction_g,p.extinction_r,p.extinction_i,p.extinction_z,p.petroRad_u,p.petroRad_g,p.petroRad_r,p.petroRad_i,p.petroRad_z,p.petroR50_r,p.petroR90_r,zi.e_bv_sfd|FROM #UPLOAD as U|OUTER APPLY dbo.fGetNearestObjEq((U.up_ra),(U.up_dec),{}) as N|LEFT JOIN PhotoObjAll AS p ON N.objid=p.objID|JOIN SpecObjAll za on (p.objID = za.bestObjID)|JOIN galSpecInfo zi ON (zi.SpecObjID = za.specObjID)".format(str(search)) #change all | to new line: when we change to Unix system will need to change this new line sql = sql.replace('|','\n') #login, change to some other credentials later Authentication.login('awe2','StandardPassword') job = CasJobs.executeQuery(sql,'DR12','pandas') #this line sends and retrieves the result print('Query {} of {} complete'.format(j+1,Q)) job['dered_u'] = job['u'].values - job['extinction_u'].values job['dered_g'] = job['g'].values - job['extinction_g'].values job['dered_r'] = job['r'].values - job['extinction_r'].values job['dered_i'] = job['i'].values - job['extinction_i'].values job['dered_z'] = job['z'].values - job['extinction_z'].values job['u-g']= job['dered_u'].values - job['dered_g'].values job['g-r']= job['dered_g'].values - job['dered_r'].values job['r-i']= job['dered_r'].values - job['dered_i'].values job['i-z']= job['dered_i'].values - job['dered_z'].values job['u_over_z']= job['dered_u'].values / job['dered_z'].values job['C'] = job['petroR90_r'].values/job['petroR50_r'].values total_job.append(job) print('left the query loop') query_result = pd.concat(total_job) #now feed to a RF model for prediction X = query_result[['dered_u','dered_g','dered_r','dered_i','dered_z','u-g','g-r','r-i','i-z','u_over_z','petroRad_u','petroRad_g','petroRad_r','petroRad_i','petroRad_z','petroR50_r','petroR90_r','C','e_bv_sfd']].values print(X.shape) #define and load in the model def create_model(learning_rate): model = keras.Sequential([]) model.add(keras.layers.Dense(input_shape=(19,),units=19,activation=keras.activations.linear)) #tried relu #model.add(keras.layers.Dropout(rate=0.1)) model.add(keras.layers.Dense(units=19,activation=tf.nn.relu)) #model.add(keras.layers.Dropout(rate=0.1)) model.add(keras.layers.Dense(units=19,activation=tf.nn.relu)) #model.add(keras.layers.Dropout(rate=0.1)) model.add(keras.layers.Dense(units=19,activation=tf.nn.relu)) #tf.nn.relu #model.add(keras.layers.Dropout(rate=0.1)) model.add(keras.layers.Dense(units=315,activation=keras.activations.softmax)) #RMS = keras.optimizers.RMSprop(learning_rate=learning_rate) adam = keras.optimizers.Adam(lr=learning_rate) model.compile(optimizer=adam, loss='categorical_crossentropy') return(model) keras.backend.clear_session() model = create_model(learning_rate = 1e-3)#couldve been anything for this, just gonna predict model.load_weights(path_to_model) #Need to deal with NANs now since many objects are outside the SDSS footprint, later models will learn to deal with this #ideas: need to retain a mask of where the nans are in the row mask = np.invert((query_result.isna().any(1).values)) #true was inside SDSS footprint #also will want this mask in indices so we can insert the predicted data correctly indices=[] for i,val in enumerate(mask): if val == True: indices.append(i) #predict on data that is not NAN predictions = model.predict(X[mask,:], verbose=2) #make nan array with size of what user asked for return_me = np.ones(N)*np.nan #now replace nan with the predictions in order return_me[indices] = predictions return_me_outer = np.ones(N_outer) * np.nan return_me_outer[outer_mask] = return_me print('time taken:', datetime.datetime.utcnow() - nowdate) print('uploading now') tz,mpz = [],[] for t,pz in zip(transients,return_me): if pz != pz: continue host = t.host #import pdb; pdb.set_trace() host.photo_z = pz host.save() tz += [host.redshift] mpz += [pz] plt.plot(tz,mpz,'.') plt.savefig('test.png') print('time taken with upload:', datetime.datetime.utcnow() - nowdate) except Exception as e: exc_type, exc_obj, exc_tb = sys.exc_info() print("""Photo-z cron failed with error %s at line number %s"""%(e,exc_tb.tb_lineno))
import matplotlib.pyplot as plt import skimage.io import SciServer.CasJobs as CasJobs import SciServer.SciDrive query = """ select top 16 g.objId,g.ra,g.dec,g.petror90_r, dbo.fGetUrlFitsCFrame(F.FIELDiD,'r') as fieldURL from galaxy g , field f where f.run=g.run and f.camcol=g.camcol and f.field=g.field and f.rerun=g.rerun """ # query CasJobs table. Using DR12 as context queryResponse = CasJobs.executeQuery(query, "DR12") # parse results into pandas.DataFrame for further in memory processing gals = pandas.read_csv(queryResponse, index_col=None) gals['objId'] = gals['objId'].astype(np.int64) width = 200 height = 200 pixelsize = 0.396 plt.figure(figsize=(15, 15)) subPlotNum = 1 for index, gal in gals.iterrows(): scale = 2 * gal['petror90_r'] / pixelsize / width url = "http://skyservice.pha.jhu.edu/DR12/ImgCutout/getjpeg.aspx?ra=" + str( gal['ra']) url += "&dec=" + str(
FROM fGetNearbyObjEq(" + ra + ", " + dec + ", " + radius + ") n, Star p \ LEFT OUTER JOIN specObj s ON s.bestObjID = p.objID \ LEFT OUTER JOIN photoz pz ON pz.objid = p.objid \ WHERE n.objID = p.objID AND(s.class IS NULL or s.class='STAR') " # Query for galaxy with modelMag and cmodelMag and extinctions q_gal = "SELECT p.ra, p.dec, p.modelMag_u, p.modelMagErr_u, p.modelMag_g, p.modelMagErr_g, p.modelMag_r, p.modelMagErr_r, p.modelMag_i, p.modelMagErr_i, p.modelMag_z, p.modelMagErr_z, \ p.petroMag_u, p.petroMagErr_u, p.petroMag_g, p.petroMagErr_g, p.petroMag_r, p.petroMagErr_r, p.petroMag_i, p.petroMagErr_i, p.petroMag_z, p.petroMagErr_z, \ p.cmodelMag_u, p.cmodelMagErr_u, p.cmodelMag_g, p.cmodelMagErr_g, p.cmodelMag_r, p.cmodelMagErr_r, p.cmodelMag_i, p.cmodelMagErr_i, p.cmodelMag_z, p.cmodelMagErr_z, \ extinction_u, extinction_g, extinction_r, extinction_i, extinction_z, clean, s.z, s.zerr, pz.z as photoz, pz.zerr as photozerr \ FROM fGetNearbyObjEq(" + ra + ", " + dec + ", " + radius + ") n, Galaxy p \ LEFT OUTER JOIN specObj s ON s.bestObjID = p.objID \ LEFT OUTER JOIN photoz pz ON pz.objid = p.objid \ WHERE n.objID = p.objID AND(s.class IS NULL or s.class='GALAXY') " star_data = CasJobs.executeQuery(q_star, context=context, format='pandas') gal_data = CasJobs.executeQuery(q_gal, context=context, format='pandas') star_tab = Table(star_data.values, names=star_data.columns, meta={ 'clustername': clustername, 'ra': ra, 'dec': dec, 'radius': radius }, dtype=[float for x in range(len(star_data.columns))]) gal_tab = Table(gal_data.values, names=gal_data.columns, meta={ 'clustername': clustername,
import skimage.io import SciServer.CasJobs as CasJobs import SciServer.SciDrive query = """ select top 16 g.objId,g.ra,g.dec,g.petror90_r, dbo.fGetUrlFitsCFrame(F.FIELDiD,'r') as fieldURL from galaxy g , field f where f.run=g.run and f.camcol=g.camcol and f.field=g.field and f.rerun=g.rerun """ # query CasJobs table. Using DR12 as context queryResponse = CasJobs.executeQuery(query, "DR12") # parse results into pandas.DataFrame for further in memory processing gals = pandas.read_csv(queryResponse, index_col=None) gals['objId'] = gals['objId'].astype(np.int64) width = 200 height = 200 pixelsize = 0.396 plt.figure(figsize=(15, 15)) subPlotNum = 1 for index, gal in gals.iterrows(): scale = 2 * gal['petror90_r'] / pixelsize / width url = "http://skyservice.pha.jhu.edu/DR12/ImgCutout/getjpeg.aspx?ra=" + str(gal['ra']) url += "&dec=" + str(gal['dec']) + "&scale=""" + str(scale) + "&width=" + str(width) url += "&height=" + str(height)
{} ORDER BY class,subclass, u,g,r,i,z""".format('', '') sql_spec = ''' 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, (r-15)/5 as r,objid --into MYDB.spectrain from (SELECT p.psfMag_u AS u, p.psfMag_g AS g, p.psfMag_r AS r, p.psfMag_i AS i, p.psfMag_z AS z, s.class as class, s.subclass as subclass,s.BestObjID as objid FROM SpecObj as s left join PhotoObj as P on s.BestObjID= P.objID WHERE s.BestObjID in (select objid from MYDB.specDR13) ) x order by 1''' 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',
def delete_table(self, tablename): sql = "DROP TABLE {}".format(tablename) CasJobs.executeQuery(sql=sql, context="MyDB", format="pandas")