Exception: Unexpected character: b'<'

Programming & Data Ecosystem Tools Python
#1

Hi Team,

I’m running the python script to import new data to omnisci using pymapd. I’m experiencing below mentioned exception from “execute” method.

Exception

Unexpected character: b’<’
Traceback (most recent call last):
File “Omnisci-Import.py”, line 137, in mapd_execute
resCur = mapdcon.execute(sql,parameters=values)
File “/home/ubuntu/.local/lib/python3.7/site-packages/pymapd/connection.py”, line 291, in execute
return c.execute(operation, parameters=parameters)
File “/home/ubuntu/.local/lib/python3.7/site-packages/pymapd/cursor.py”, line 117, in execute
nonce=None, first_n=-1, at_most_n=-1)
File “/home/ubuntu/.local/lib/python3.7/site-packages/omnisci/mapd/MapD.py”, line 1598, in sql_execute
return self.recv_sql_execute()
File “/home/ubuntu/.local/lib/python3.7/site-packages/omnisci/mapd/MapD.py”, line 1615, in recv_sql_execute
(fname, mtype, rseqid) = iprot.readMessageBegin()
File “/home/ubuntu/.local/lib/python3.7/site-packages/thrift/protocol/TJSONProtocol.py”, line 417, in readMessageBegin
self.readJSONArrayStart()
File “/home/ubuntu/.local/lib/python3.7/site-packages/thrift/protocol/TJSONProtocol.py”, line 405, in readJSONArrayStart
self.readJSONSyntaxChar(LBRACKET)
File “/home/ubuntu/.local/lib/python3.7/site-packages/thrift/protocol/TJSONProtocol.py”, line 256, in readJSONSyntaxChar
“Unexpected character: %s” % current)
thrift.protocol.TProtocol.TProtocolException: Unexpected character: b’<’

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “Omnisci-Import.py”, line 619, in main
process_new_data_to_omnisci(conn,cur,mapdcon,mapdTbl,pgTbl)
File “Omnisci-Import.py”, line 208, in process_new_data_to_omnisci
create_or_update_rental_units(pgTbl, mapdTbl, df, mapdcon)
File “Omnisci-Import.py”, line 439, in create_or_update_rental_units
resCur = mapd_execute(“SELECT count(*) FROM {0} where id = :id”.format(mapdTbl,id),{“id” : int(id)})
File “Omnisci-Import.py”, line 153, in mapd_execute
raise Exception(ex)
Exception: Unexpected character: b’<’

Inputs

Sql query string : “SELECT count(*) FROM {TABLE} where id = :id”
Parameters : {“id” : 12345}

After getting this error, the subsequent call also throws the same error while re-connecting into the omnisci.

Only after sometime, If I run the same script again then I’m able to process for another 500 to 1000 records, then again it throws the same error.

I’m trying to push new updates(atleast 5000 records) daily to omnisci. Its like find and create/update the record(query to find if record is exist then sql script to create/update the record).
Currently, I’m not able to process even 2000 records successfully.

Could you please give some suggestion? Thanks!

#2

Hi David, that error typically means there’s some other format that’s not Thrift being returned. This is typically something like a non-200 HTTP response, so this particular error does not tell us anything very specific about what is going on.

Side question: what port are you using to connect to OmniSci?

In order to troubleshoot, it’s worth ruling out the cursor as an issue. Instead of using a cursor, are you able to use pandas.readsql to get the results, and we can see if that makes any difference? https://pymapd.readthedocs.io/en/latest/usage.html#pandas-read-sql

(once we isolate the issue we can work on resolving it permanently)

#3

Hi @jpharvey, Thanks for the response. Here is my response and additional question

Side question: what port are you using to connect to OmniSci?
PORT : 443

And additional question is?

Daily, I would like to execute series of insert or update query to omnisci. So, I depending on this execute method from pymapd. I hope, I can’t achieve this by using pandas.readsql. So, It would be great to know how to overcome this issues? Or What is the cause of the issue? or Any alternative to run insert or update query to omnisci.

Thanks!

#4

Ok so this error makes a little more sense, if you’re connecting through 443 you’re probably connecting through a webserver so a non-200 HTTP response is a little more likely.

If loading data through OmniSci INSERTS are far inferior to doing a columnar load, and in some circumstances will negatively impact performance. Have a look at the load_table method. This is possibly also the best answer to your question here Pymapd : How to insert/update NULL or None values? using executemany method!

UPDATE queries do need to be run as raw SQL as you have been doing, the cause of the issue above is not clear at this point, but ideally we would rule out something to do with the Cursors first.

What product are you using (open source / AWS AMI / Cloud)?

#5

@jpharvey, Product : Cloud

#6

No worries, if you continue to have issues can you please email cloudsupport … at … omnisci.com?

#7

Hi @jpharvey, We are still experiencing this issue. I’ve emailed to cloudsupport, sales and initiated chat but we haven’t received any response yet.

Even in OmniSci Immerse, We are not able to run any updates query in “Sql editor” too, If I switch the tab to “Data Manager” then it show popup message as “Failed To Load Tables”

While running drop query from sql editor in “Omnisci Immerse” received following exception. Exception: Sqlite3 Error: unable to open database file

Is it any limitation over the current plan? Or Issue related to Cursor? Any suggestion

I’m looking for any quick solution or work around for the mentioned issue.

Thanks!

#8

Hi @DavidLivingston I have access to all of those interfaces and am not seeing anything at all from you. I will PM you.