How do you return a row from a query outside of the mapdql console?


#1

When I use the linux client ‘mapdql’ I get to type a query and get a pipe delimited row like:

SELECT name,age,city from table;
name|age|city
Daryl|52|Bristol

When I make a post request from a system on my LAN I get a huge long, very deeply nested json object of arrays and objects that you can see below.

The question is, how can I make a request that returns something intelligible? Is there a response map so I can map the resulting object to the keys and values I’m trying to extract?

I’ve noticed that the resulting object looks different depending on the type of query. A count(*) result object has a different nesting structure than a select columnname.

arr
 (
[0] => arr
  (
    [0] => 1
    [1] => sql_execute
    [2] => 2
    [3] => 0
    [4] => obj
      (
        [0] => obj
          (
            [rec] => obj
              (
                [1] => obj
                  (
                    [rec] => obj
                      (
                        [1] => obj
                          (
                            [lst] => arr
                              (
                                [0] => rec
                                [1] => 3
                                [2] => obj
                                  (
                                    [1] => obj
                                      (
                                        [str] => personfirstname
                                      )

                                    [2] => obj
                                      (
                                        [rec] => obj
                                          (
                                             [1] => obj
                                              (
                                                [i32] => 6
                                              )

                                            [2] => obj
                                              (
                                                [tf] => 1
                                              )

                                            [3] => obj
                                              (
                                                [tf] => 0
                                              )

                                            [4] => obj
                                              (
                                                [i32] => 4
                                              )

                                            [5] => obj
                                              (
                                                [i32] => 30
                                              )

                                            [6] => obj
                                              (
                                                [i32] => 0
                                              )

                                            [7] => obj
                                              (
                                                [i32] => 3
                                              )

                                          )

                                      )

                                    [3] => obj
                                      (
                                        [tf] => 0
                                      )

                                    [4] => obj
                                      (
                                        [str] => 
                                      )

                                    [5] => obj
                                      (
                                        [tf] => 0
                                      )

                                  )

                                [3] => obj
                                  (
                                    [1] => obj
                                      (
                                        [str] => InferredAge
                                      )

                                    [2] => obj
                                      (
                                        [rec] => obj
                                          (
                                            [1] => obj
                                              (
                                                [i32] => 0
                                              )

                                            [2] => obj
                                              (
                                                [tf] => 1
                                              )

                                            [3] => obj
                                              (
                                                [tf] => 0
                                              )

                                            [4] => obj
                                              (
                                                [i32] => 0
                                              )

                                            [5] => obj
                                              (
                                                [i32] => 0
                                              )

                                            [6] => obj
                                              (
                                                [i32] => 0
                                              )

                                            [7] => obj
                                              (
                                                [i32] => 0
                                              )

                                          )

                                      )

                                    [3] => obj
                                      (
                                        [tf] => 0
                                      )

                                    [4] => obj
                                      (
                                        [str] => 
                                      )

                                    [5] => obj
                                      (
                                        [tf] => 0
                                      )

                                  )

                                [4] => obj
                                  (
                                    [1] => obj
                                      (
                                        [str] => cityname
                                      )

                                    [2] => obj
                                      (
                                        [rec] => obj
                                          (
                                            [1] => obj
                                              (
                                                [i32] => 6
                                              )

                                            [2] => obj
                                              (
                                                [tf] => 1
                                              )

                                            [3] => obj
                                              (
                                                [tf] => 0
                                              )

                                            [4] => obj
                                              (
                                                [i32] => 4
                                              )

                                            [5] => obj
                                              (
                                                [i32] => 14
                                              )

                                            [6] => obj
                                              (
                                                [i32] => 0
                                              )

                                            [7] => obj
                                              (
                                                [i32] => 25
                                              )

                                          )

                                      )

                                    [3] => obj
                                      (
                                        [tf] => 0
                                      )

                                    [4] => obj
                                      (
                                        [str] => 
                                      )

                                    [5] => obj
                                      (
                                        [tf] => 0
                                      )

                                  )

                              )

                          )

                        [2] => obj
                          (
                            [lst] => arr
                              (
                                [0] => rec
                                [1] => 0
                              )

                          )

                        [3] => obj
                          (
                            [lst] => arr
                              (
                                [0] => rec
                                [1] => 3
                                [2] => obj
                                  (
                                    [1] => obj
                                      (
                                        [rec] => obj
                                          (
                                            [1] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => i64
                                                    [1] => 0
                                                  )

                                              )

                                            [2] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => dbl
                                                    [1] => 0
                                                  )

                                              )

                                            [3] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => str
                                                    [1] => 1
                                                    [2] => Daryl
                                                  )

                                              )

                                            [4] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => rec
                                                    [1] => 0
                                                  )

                                              )

                                          )

                                      )

                                    [2] => obj
                                      (
                                        [lst] => arr
                                          (
                                            [0] => tf
                                            [1] => 1
                                            [2] => 0
                                          )

                                      )

                                  )

                                [3] => obj
                                  (
                                    [1] => obj
                                      (
                                        [rec] => obj
                                          (
                                            [1] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => i64
                                                    [1] => 1
                                                    [2] => 52
                                                  )

                                              )

                                            [2] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => dbl
                                                    [1] => 0
                                                  )

                                              )

                                            [3] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => str
                                                    [1] => 0
                                                  )

                                              )

                                            [4] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => rec
                                                    [1] => 0
                                                  )

                                              )

                                          )

                                      )

                                    [2] => obj
                                      (
                                        [lst] => arr
                                          (
                                            [0] => tf
                                            [1] => 1
                                            [2] => 0
                                          )

                                      )

                                  )

                                [4] => obj
                                  (
                                    [1] => obj
                                      (
                                        [rec] => obj
                                          (
                                            [1] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => i64
                                                    [1] => 0
                                                  )

                                              )

                                            [2] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => dbl
                                                    [1] => 0
                                                  )

                                              )

                                            [3] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => str
                                                    [1] => 1
                                                    [2] => Bristol
                                                  )

                                              )

                                            [4] => obj
                                              (
                                                [lst] => arr
                                                  (
                                                    [0] => rec
                                                    [1] => 0
                                                  )

                                              )

                                          )

                                      )

                                    [2] => obj
                                      (
                                        [lst] => arr
                                          (
                                            [0] => tf
                                            [1] => 1
                                            [2] => 0
                                          )

                                      )

                                  )

                              )

                          )

                        [4] => obj
                          (
                            [tf] => 1
                          )

                      )

                  )

                [2] => obj
                  (
                    [i64] => 54
                  )

                [3] => obj
                  (
                    [i64] => 54
                  )

                [4] => obj
                  (
                    [str] => 0
                  )

              )

          )

      )

  )

  )

I was surprised that the mapdql console is making an http request, like apache thrift and whatever else. You can see that what I say is true in lines 1113-1118 from mapdql.cpp. I think that lines 1196-1217 seem to deal with piecing the columns back in to rows. It uses a method called columnar_val_to_datum and I believe I’m trying to replicate that functionality. Rather than reinvent what someone here has already done, it would be a dream come true if someone could direct me how to get a row returned from this columnar data set.


#2

Hi

Assuming you are running from a JavaScript client, please take a look at the Mapd-connect repo in GitHub.

There are clients for Java (via jdbc) and Python also available for community version and ODBC for enterprise version.

Regards


#3

Thanks. I did look there when you directed me there in a different post about interfaces. There isn’t any documentation. The example calls a file ‘node-connector.js’ that has over 28,000 lines of code. Without documentation it’s kind of like my garage. There are lots of valuable and useful tools that no one can find.

Right now I’m not asking about interfaces, just an explanation or documentation of the http responses from the mapdql server.

Right now I’m using raw, commandline curl because I can execute curl requests from any platform. The only thing still left before I can integrate is to reassemble the columns into rows, which I’m working on right now.

Where is the documentation that outlines the http response from the mapd server? If there isn’t any, can you or someone else explain it?


#4

Hi,

From the mapd-connector readme it does point to these documents

I am assuming these don’t cover what you are looking for?

The return from a MapD api call is packaged up via Thrift. If you choose not to use one of the existing wrappers for communicating with MapD you will need to unbundle directly from the thrift objects. Thrift builds language specific objects to allow access to the contents of the returned results. You can see the layout of these thrift structures in the file mapd.thrift file in the root directory of install or OS repo.

An sql_execute call returns a

struct TQueryResult {
  1: TRowSet row_set
  2: i64 execution_time_ms
  3: i64 total_time_ms
  4: string nonce
`

a TRowSet is defined as

struct TRowSet {
  1: TRowDescriptor row_desc
  2: list<TRow> rows
  3: list<TColumn> columns
  4: bool is_columnar
}

The TRowDescriptor gives you the metadata of the columns in the response

typedef list<TColumnType> TRowDescriptor

so it is a list of

struct TColumnType {
  1: string col_name,
  2: TTypeInfo col_type,
  3: bool is_reserved_keyword,
  4: string src_name,
  5: bool is_system
}

Now you know what the result set metadata is you can look at the data contents.

You first need to determine if it is a columnar resultset or a row based result set (we normally recommend always using columnar) its specified on the sql_execute call.

So now you would run down the columnar path.

Looking at the list of results list<TColumn> columns

The first column will be the first entry in the list, second column will be the second entry etc…

each TColumn consists of

struct TColumn {
  1: TColumnData data,
  2: list<bool> nulls
}

the list<bools> nulls tells you if the data item at a given point in the list is actually null and overrides whatever else is in the list of data at that point.

If the value is not null

struct TColumnData {
  1: list<i64> int_col,
  2: list<double> real_col,
  3: list<string> str_col,
  4: list<TColumn> arr_col
}

We must now look at the contents, depending on the type (from the meta data identified above) the actual data for the row and column will be stored in these lists.

We need to note here we do not guarantee that we will not change the details of how we layout the internals of our Thrift definitions so it is definitely safer to use one of the existing wrappers that we maintain to this data. We will keep the definitions public so you will be able to maintain your changes

Regards


#5

Thank you. I didn’t know it until I looked through your github history on the project that you are one of the actual developers of this platform. Next to you I’m basically an amoeba.

Thank you very much for explaining the structure. I had seen the mapd.thrift file but didn’t understand it. Your answer is really thorough and very appreciated.


#6

Yeah, we need to get around to some kind of doc for thrift beyond the code. The one piece that’s there now is the (even-less-then-obvious) render call, and how to construct a valid vega request.


#7

@mdifl You might want to have a look at how mapd-connector handles converting columnar results into rows. While the build files of that project (browser-connector.js and node-connector.js) are huge files, most of it is the generated thrift interface code. If you have a look at the src folder you should get an idea of what you might need to build around the thrift interface to make querying the server easy.

Best,


#8

:slight_smile: I finished a small function set in php that gets the information I need in the way that I need it. I approached it in an incredibly amoeba like fashion by iterating over the array and cutting the pieces that I needed and splicing them together as columns of a row based associative array. It’s only 58 lines of code, but it does everything I need for the php applications I have.

While I didn’t particularly like the node-js mapd-connector, I have it working thanks to dwayneberry and like it ok now.


#9

That’s great to hear! Did you actually generate a thrift client for php or are you parsing the http response? Also, out of curiousity, what about the node-js mapd-connector was it that you didn’t like?

I’d be nice to eventually have the mapd-connector api implemented in the various languages supported by thrift.


#10

The php interface that I use simply parses the http output. I don’t understand thrift well enough to do anything with it. I’m kind of like if a cow had hands and worked at a computer.

The php functions allow me to query like this:

$sessionid = getMapdSesssion($host,$username,$password,$database);
$result = mapdQuery($host,$sessionid,$query);

$result contains an array of associative arrays.

The only real complaint with the nodejs mapd-connector was that the files required to run it and the nested dependencies are really big and complicated. I can get it to work, meaning I run queries and get results with it, but I don’t really understand it even now. My ideal way to query would be something similar to what I wrote in php and without the dependencies of mapd-connector. My php script has zero dependencies.

@Tai, what’s your relationship to MapD?


#11

@mdifl I’m actually on the immerse team at MapD, and I’m one of the maintainers of the mapd-connector project.

The Thrift interface can be difficult to understand, especially since in the end it’s just a simple http request that is being issued. However, if you’d like to take advantage of the full mapd-core api, without having to parse thrift responses or construct them, you might want to look into generating a client in the language you’d like to use.

Steps for generating a php client can be found here.

As @dwayneberry mentioned the mapd.thrift file is here

You’ll likely want to use the http client and Json protocol for transport, as that’s easier to setup than the binary protocol.

Let me know if you have any questions. I actually just went throught the process myself this weekend trying to build a ruby client.


#12

Thank you. Again, next to @tai and @dwayneberry I’m less than a simpleton.

I’ve never been able to access Mapd from the localhost where the mapd_server is installed, with the exception of using mapdql. I haven’t been able to use it with mapd-connector in node.js or php. I have no problem accessing it from other systems on the local area network in any of the above mentioned ways.

I get the error “Permission Denied.” Since it’s a new question I asked it here, but you would almost certainly know the answer.