Pymapd error with FLOAT[] data type

Follow
Avatar
zipeng liu

Hi Experts,

I created a table with FLOAT[] as a column. but I am not able to query FLOAT[] data from pymapd. Please see the error below.

query = "SELECT * FROM samplevector limit 100" df = con.select_ipc(query) Traceback (most recent call last): File "", line 1, in File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py", line 316, in select_ipc first_n=first_n File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/mapd/MapD.py", line 1560, in sql_execute_df return self.recv_sql_execute_df() File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/mapd/MapD.py", line 1588, in recv_sql_execute_df raise result.e mapd.ttypes.TMapDException: TMapDException(error_msg='Exception: FLOAT[] is not supported in Arrow result sets.')

Best, Zipeng

0

Comments

9 comments

Sort by Date Votes
  • Avatar
    Randy Zwitch

    Hi @zipeng, sorry to hear you're having trouble. What version of pymapd and OmniSciDB are you using?

    0
    Comment actions Permalink
  • Avatar
    zipeng liu

    (post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

    0
    Comment actions Permalink
  • Avatar
    zipeng liu

    OmniSciDB: 5.1.2 CPU Ubuntu. pymapd ==0.20.0

    Additional info, I am able to use omnisql to query the table with float array, but failed with pymapd.

    Best, Zipepng

    0
    Comment actions Permalink
  • Avatar
    Randy Zwitch

    Now that I re-look at your question, I see that you are trying to use a FLOAT array. This in fact does not work, due to some Arrow compatibility issues. Currently, the work-around is to use either con.execute() to return the results as a list of tuples, or you can use pandas df = pd.read_sql("select * from...", con) to get a dataframe.

    Version 5.2 of OmniSci should be using Arrow 0.16, and we'll try and get the Arrow compatibility issues straightened out.

    0
    Comment actions Permalink
  • Avatar
    zipeng liu

    unfortunatelly still not working.

    df = pd.read_sql(query,con) Traceback (most recent call last): File "", line 1, in File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 410, in read_sql chunksize=chunksize, File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 1658, in read_query data = self._fetchall_as_list(cursor) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 1671, in _fetchall_as_list result = cur.fetchall() File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 170, in fetchall return list(self) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 206, in make_row_results_set yield tuple(columns[j][i] for j in range(ncols)) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 206, in yield tuple(columns[j][i] for j in range(ncols)) IndexError: list index out of range

    I also tried Cursors.

    c.execute(query) c.rowcount 2 result=c.fetchall() Traceback (most recent call last): File "", line 1, in File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 170, in fetchall return list(self) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 206, in make_row_results_set yield tuple(columns[j][i] for j in range(ncols)) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 206, in yield tuple(columns[j][i] for j in range(ncols)) IndexError: list index out of range c.fetchall() [] result=c.fetchall() len(result) 0

    seems like the Cursors can not fetch the data. the c.rowcount is correct but no data.

    Best, Zipeg

    0
    Comment actions Permalink
  • Avatar
    Randy Zwitch

    For this simple example, it seems to work.

    In [1]: import pymapd
   ...: import pandas as pd
   ...: 
   ...: con = pymapd.connect(user="admin", password="HyperInteractive", host="localhost")
   ...: 
   ...: con.execute("DROP TABLE IF EXISTS test_lists;")
   ...: con.execute("CREATE TABLE IF NOT EXISTS test_lists \\
   ...:             (col1 TEXT, col2 FLOAT[]);")
   ...: 
   ...: row = [("row1", "{1.23,3.66898,15.8765}"),
   ...:        ("row2", "{19874.0,10,3983.3432}")]
   ...: 
   ...: con.load_table_rowwise("test_lists", row)

In [2]: ans = con.execute("select * from test_lists").fetchall()

In [3]: ans
Out[3]: 
[('row1', [1.2300000190734863, 3.6689798831939697, 15.876500129699707]),
 ('row2', [19874.0, 10.0, 3983.34326171875])]

In [4]: df = pd.read_sql("select * from test_lists", con)

In [5]: df
Out[5]: 
   col1                                               col2
0  row1  [1.2300000190734863, 3.6689798831939697, 15.87...
1  row2                  [19874.0, 10.0, 3983.34326171875]

In [6]:

    Does your FLOAT[] column have nulls or any other special values?

    0
    Comment actions Permalink
  • Avatar
    zipeng liu

    Hi Randy,

    I tried your code.still getting error:

    uri = "mapd://admin:HyperInteractive@localhost:6274/omnisci?protocol=binary" con = pymapd.connect(uri=uri) con.execute("DROP TABLE IF EXISTS test_lists;") con.execute("CREATE TABLE IF NOT EXISTS test_lists (col1 TEXT, col2 FLOAT[]);") row = [("row1", "{1.23,3.66898,15.8765}"), ("row2", "{19874.0,10,3983.3432}")] con.load_table_rowwise("test_lists", row) ans = con.execute("select * from test_lists").fetchall() Traceback (most recent call last): File "", line 1, in File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 170, in fetchall return list(self) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 206, in make_row_results_set yield tuple(columns[j][i] for j in range(ncols)) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/cursor.py", line 206, in yield tuple(columns[j][i] for j in range(ncols)) IndexError: list index out of range

    By the way, I have another strange error maybe related.

    I am not able to connect with con = pymapd.connect(user="admin", password="HyperInteractive", host="localhost")

    strangly it automaticall try to connect to port 9091.

    Could not connect to any of [('127.0.0.1', 9091)] Traceback (most recent call last): File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py", line 160, in init self._transport.open() File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/thrift/transport/TTransport.py", line 153, in open return self.__trans.open() File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/thrift/transport/TSocket.py", line 113, in open raise TTransportException(TTransportException.NOT_OPEN, msg) thrift.transport.TTransport.TTransportException: Could not connect to any of [('127.0.0.1', 9091)]

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last): File "", line 1, in File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py", line 71, in connect port=port, dbname=dbname, protocol=protocol) File "/home/ubuntu/anaconda3/lib/python3.7/site-packages/pymapd/connection.py", line 164, in init six.raise_from(err, e) File "", line 3, in raise_from pymapd.exceptions.OperationalError: Could not connect to database

    Best, Zipeng

    0
    Comment actions Permalink
  • Avatar
    Randy Zwitch

    I wonder if you have a path issue of some kind. In version 0.8 of pymapd, we switched the default of port to 6274, so if your code tries to connect to 9091, you might have an install issue.

    https://github.com/omnisci/pymapd/releases/tag/v0.8.0

    0
    Comment actions Permalink
  • Avatar
    zipeng liu

    Hi Randy,

    Thanks a lot! You are right. it is something with my Python environment. I cleaned up my Python env and it works. Really Appreciate your help!

    Best, Zipeng

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post