phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From F21 <f21.gro...@gmail.com>
Subject Re: apache phoenix json api
Date Wed, 20 Apr 2016 10:41:24 GMT
Hey Plamen,

I just spun up some clean docker containers running Hbase 1.1.4 and 
Phoenix 4.7.0 to replicate what you did. It appears to work correctly.

Using SquirrelSQL, I created the table: CREATE TABLE IF NOT EXISTS 
us_population (state CHAR(2) NOT NULL, city VARCHAR NOT NULL, population 
BIGINT CONSTRAINT my_pk PRIMARY KEY (state, city));

I then issued a few requests to the query server using curl:

- open a connection
curl -XPOST localhost:8765 -d '{"request": "openConnection", 
"connectionId": "5"}'

- create statement
curl -XPOST localhost:8765 -d '{"request": "createStatement", 
"connectionId": "5"}'

- prepare and execute the upsert
curl -XPOST localhost:8765 -d '{"request": "prepareAndExecute", 
"connectionId": "5", "statementId": 25, "sql": "UPSERT INTO 
us_population VALUES(\'CA\',\'California\',100000)", "maxRowCount": -1}'

- run SELECT * FROM us_population in SquirrelSQL and get no results back

- commit
curl -XPOST localhost:8765 -d '{"request": "commit", "connectionId": "5"}'

- commit response:
{"response":"commit"}

- run SELECT * FROM us_population in SquirrelSQL and get 1 row

- select all cities
curl -XPOST localhost:8765 -d '{"request": "prepareAndExecute", 
"connectionId": "5", "statementId": 25, "sql": "SELECT * FROM 
us_population","maxRowCount": -1}'

- get 1 row back:
{
    "response":"executeResults",
    "missingStatement":false,
    "rpcMetadata":{
       "response":"rpcMetadata",
       "serverAddress":"f826338-phoenix-server.f826338:8765"
    },
    "results":[
       {
          "response":"resultSet",
          "connectionId":"5",
          "statementId":25,
          "ownStatement":true,
          "signature":{
             "columns":[
                {
                   "ordinal":0,
                   "autoIncrement":false,
                   "caseSensitive":false,
                   "searchable":true,
                   "currency":false,
                   "nullable":0,
                   "signed":false,
                   "displaySize":2,
                   "label":"STATE",
                   "columnName":"STATE",
                   "schemaName":"",
                   "precision":2,
                   "scale":0,
                   "tableName":"US_POPULATION",
                   "catalogName":"",
                   "type":{
                      "type":"scalar",
                      "id":1,
                      "name":"CHAR",
                      "rep":"STRING"
                   },
                   "readOnly":true,
                   "writable":false,
                   "definitelyWritable":false,
                   "columnClassName":"java.lang.String"
                },
                {
                   "ordinal":1,
                   "autoIncrement":false,
                   "caseSensitive":false,
                   "searchable":true,
                   "currency":false,
                   "nullable":0,
                   "signed":false,
                   "displaySize":40,
                   "label":"CITY",
                   "columnName":"CITY",
                   "schemaName":"",
                   "precision":0,
                   "scale":0,
                   "tableName":"US_POPULATION",
                   "catalogName":"",
                   "type":{
                      "type":"scalar",
                      "id":12,
                      "name":"VARCHAR",
                      "rep":"STRING"
                   },
                   "readOnly":true,
                   "writable":false,
                   "definitelyWritable":false,
                   "columnClassName":"java.lang.String"
                },
                {
                   "ordinal":2,
                   "autoIncrement":false,
                   "caseSensitive":false,
                   "searchable":true,
                   "currency":false,
                   "nullable":1,
                   "signed":true,
                   "displaySize":40,
                   "label":"POPULATION",
                   "columnName":"POPULATION",
                   "schemaName":"",
                   "precision":0,
                   "scale":0,
                   "tableName":"US_POPULATION",
                   "catalogName":"",
                   "type":{
                      "type":"scalar",
                      "id":-5,
                      "name":"BIGINT",
                      "rep":"PRIMITIVE_LONG"
                   },
                   "readOnly":true,
                   "writable":false,
                   "definitelyWritable":false,
                   "columnClassName":"java.lang.Long"
                }
             ],
             "sql":null,
             "parameters":[

             ],
             "cursorFactory":{
                "style":"LIST",
                "clazz":null,
                "fieldNames":null
             },
             "statementType":null
          },
          "firstFrame":{
             "offset":0,
             "done":true,
             "rows":[
                [
                   "CA",
                   "California",
                   100000
                ]
             ]
          },
          "updateCount":-1,
          "rpcMetadata":{
             "response":"rpcMetadata",
             "serverAddress":"f826338-phoenix-server.f826338:8765"
          }
       }
    ]
}

Can you confirm the versions you are running for HBase and Phoenix and 
try issuing those requests again with a new table?

Cheers,
Francis

On 20/04/2016 5:24 PM, Plamen Paskov wrote:
> Josh,
> I hope someone familiar can answer this question :)
>
> On 19.04.2016 22:59, Josh Elser wrote:
>> Thanks for helping out, Francis!
>>
>> Interesting that Jackson didn't fail when the connectionId was being 
>> passed as a number and not a string (maybe it's smart enough to 
>> convert that?).
>>
>> Why does your commit response have a result set in it? A 
>> CommitResponse is essentially empty.
>>
>> http://calcite.apache.org/avatica/docs/json_reference.html#commitresponse 
>>
>>
>> Plamen Paskov wrote:
>>> i confirm that the data is missing when connecting using sqlline.py
>>> command line client. If i upsert a record from within sqlline.py it's
>>> ok. I will give a try to what you suggest to issue prepare and execute
>>> as separate requests.
>>> thanks !
>>>
>>> On 19.04.2016 14:24, F21 wrote:
>>>> Can you try using something like SquirrelSQL or sqlline to see if the
>>>> data was inserted properly?
>>>>
>>>> Another thing I would try is to use separate prepare and execute
>>>> requests when SELECTing rather than using prepareAndExecute.
>>>>
>>>> On 19/04/2016 9:21 PM, Plamen Paskov wrote:
>>>>> Yep
>>>>> Here are the responses (the new data is missing again):
>>>>>
>>>>> Prepare and execute response for upsert
>>>>>
>>>>> {
>>>>> "response": "executeResults",
>>>>> "missingStatement": false,
>>>>> "rpcMetadata": {
>>>>> "response": "rpcMetadata",
>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>> },
>>>>> "results": [
>>>>> {
>>>>> "response": "resultSet",
>>>>> "connectionId": "9",
>>>>> "statementId": 21,
>>>>> "ownStatement": false,
>>>>> "signature": null,
>>>>> "firstFrame": null,
>>>>> "updateCount": 1,
>>>>> "rpcMetadata": {
>>>>> "response": "rpcMetadata",
>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>> }
>>>>> }
>>>>> ]
>>>>> }
>>>>>
>>>>> commit response
>>>>> {
>>>>> "response": "resultSet",
>>>>> "connectionId": "9",
>>>>> "statementId": 22,
>>>>> "ownStatement": true,
>>>>> "signature": {
>>>>> "columns": [
>>>>> {
>>>>> "ordinal": 0,
>>>>> "autoIncrement": false,
>>>>> "caseSensitive": false,
>>>>> "searchable": true,
>>>>> "currency": false,
>>>>> "nullable": 1,
>>>>> "signed": false,
>>>>> "displaySize": 40,
>>>>> "label": "TABLE_SCHEM",
>>>>> "columnName": "TABLE_SCHEM",
>>>>> "schemaName": "",
>>>>> "precision": 0,
>>>>> "scale": 0,
>>>>> "tableName": "SYSTEM.TABLE",
>>>>> "catalogName": "",
>>>>> "type": {
>>>>> "type": "scalar",
>>>>> "id": 12,
>>>>> "name": "VARCHAR",
>>>>> "rep": "STRING"
>>>>> },
>>>>> "readOnly": true,
>>>>> "writable": false,
>>>>> "definitelyWritable": false,
>>>>> "columnClassName": "java.lang.String"
>>>>> },
>>>>> {
>>>>> "ordinal": 1,
>>>>> "autoIncrement": false,
>>>>> "caseSensitive": false,
>>>>> "searchable": true,
>>>>> "currency": false,
>>>>> "nullable": 1,
>>>>> "signed": false,
>>>>> "displaySize": 40,
>>>>> "label": "TABLE_CATALOG",
>>>>> "columnName": "TABLE_CATALOG",
>>>>> "schemaName": "",
>>>>> "precision": 0,
>>>>> "scale": 0,
>>>>> "tableName": "SYSTEM.TABLE",
>>>>> "catalogName": "",
>>>>> "type": {
>>>>> "type": "scalar",
>>>>> "id": 12,
>>>>> "name": "VARCHAR",
>>>>> "rep": "STRING"
>>>>> },
>>>>> "readOnly": true,
>>>>> "writable": false,
>>>>> "definitelyWritable": false,
>>>>> "columnClassName": "java.lang.String"
>>>>> }
>>>>> ],
>>>>> "sql": null,
>>>>> "parameters": [],
>>>>> "cursorFactory": {
>>>>> "style": "LIST",
>>>>> "clazz": null,
>>>>> "fieldNames": null
>>>>> },
>>>>> "statementType": null
>>>>> },
>>>>> "firstFrame": {
>>>>> "offset": 0,
>>>>> "done": true,
>>>>> "rows": [
>>>>> [
>>>>> null,
>>>>> null
>>>>> ],
>>>>> [
>>>>> "SYSTEM",
>>>>> null
>>>>> ]
>>>>> ]
>>>>> },
>>>>> "updateCount": -1,
>>>>> "rpcMetadata": {
>>>>> "response": "rpcMetadata",
>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>> }
>>>>> }
>>>>>
>>>>>
>>>>> prepare and execute response for select
>>>>> {
>>>>> "response": "executeResults",
>>>>> "missingStatement": false,
>>>>> "rpcMetadata": {
>>>>> "response": "rpcMetadata",
>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>> },
>>>>> "results": [
>>>>> {
>>>>> "response": "resultSet",
>>>>> "connectionId": "9",
>>>>> "statementId": 23,
>>>>> "ownStatement": true,
>>>>> "signature": {
>>>>> "columns": [
>>>>> {
>>>>> "ordinal": 0,
>>>>> "autoIncrement": false,
>>>>> "caseSensitive": false,
>>>>> "searchable": true,
>>>>> "currency": false,
>>>>> "nullable": 0,
>>>>> "signed": false,
>>>>> "displaySize": 2,
>>>>> "label": "STATE",
>>>>> "columnName": "STATE",
>>>>> "schemaName": "",
>>>>> "precision": 2,
>>>>> "scale": 0,
>>>>> "tableName": "US_POPULATION",
>>>>> "catalogName": "",
>>>>> "type": {
>>>>> "type": "scalar",
>>>>> "id": 1,
>>>>> "name": "CHAR",
>>>>> "rep": "STRING"
>>>>> },
>>>>> "readOnly": true,
>>>>> "writable": false,
>>>>> "definitelyWritable": false,
>>>>> "columnClassName": "java.lang.String"
>>>>> },
>>>>> {
>>>>> "ordinal": 1,
>>>>> "autoIncrement": false,
>>>>> "caseSensitive": false,
>>>>> "searchable": true,
>>>>> "currency": false,
>>>>> "nullable": 0,
>>>>> "signed": false,
>>>>> "displaySize": 40,
>>>>> "label": "CITY",
>>>>> "columnName": "CITY",
>>>>> "schemaName": "",
>>>>> "precision": 0,
>>>>> "scale": 0,
>>>>> "tableName": "US_POPULATION",
>>>>> "catalogName": "",
>>>>> "type": {
>>>>> "type": "scalar",
>>>>> "id": 12,
>>>>> "name": "VARCHAR",
>>>>> "rep": "STRING"
>>>>> },
>>>>> "readOnly": true,
>>>>> "writable": false,
>>>>> "definitelyWritable": false,
>>>>> "columnClassName": "java.lang.String"
>>>>> },
>>>>> {
>>>>> "ordinal": 2,
>>>>> "autoIncrement": false,
>>>>> "caseSensitive": false,
>>>>> "searchable": true,
>>>>> "currency": false,
>>>>> "nullable": 1,
>>>>> "signed": true,
>>>>> "displaySize": 40,
>>>>> "label": "POPULATION",
>>>>> "columnName": "POPULATION",
>>>>> "schemaName": "",
>>>>> "precision": 0,
>>>>> "scale": 0,
>>>>> "tableName": "US_POPULATION",
>>>>> "catalogName": "",
>>>>> "type": {
>>>>> "type": "scalar",
>>>>> "id": -5,
>>>>> "name": "BIGINT",
>>>>> "rep": "PRIMITIVE_LONG"
>>>>> },
>>>>> "readOnly": true,
>>>>> "writable": false,
>>>>> "definitelyWritable": false,
>>>>> "columnClassName": "java.lang.Long"
>>>>> }
>>>>> ],
>>>>> "sql": null,
>>>>> "parameters": [],
>>>>> "cursorFactory": {
>>>>> "style": "LIST",
>>>>> "clazz": null,
>>>>> "fieldNames": null
>>>>> },
>>>>> "statementType": null
>>>>> },
>>>>> "firstFrame": {
>>>>> "offset": 0,
>>>>> "done": true,
>>>>> "rows": [
>>>>> [
>>>>> "CA",
>>>>> "Los Angeles",
>>>>> 3844829
>>>>> ],
>>>>> [
>>>>> "IL",
>>>>> "Chicago",
>>>>> 20000000
>>>>> ],
>>>>> [
>>>>> "NY",
>>>>> "New York",
>>>>> 8143197
>>>>> ]
>>>>> ]
>>>>> },
>>>>> "updateCount": -1,
>>>>> "rpcMetadata": {
>>>>> "response": "rpcMetadata",
>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>> }
>>>>> }
>>>>> ]
>>>>> }
>>>>>
>>>>> On 19.04.2016 11:52, F21 wrote:
>>>>>> The connectionId for all requests should be a string. Can you try 
>>>>>> that?
>>>>>>
>>>>>>
>>>>>> On 19/04/2016 5:07 PM, Plamen Paskov wrote:
>>>>>>> That's what i tried but with no luck again:
>>>>>>>
>>>>>>> {
>>>>>>> "request": "openConnection",
>>>>>>> "connectionId": 8
>>>>>>> }
>>>>>>>
>>>>>>> {
>>>>>>> "request": "createStatement",
>>>>>>> "connectionId": 8
>>>>>>> }
>>>>>>>
>>>>>>> {
>>>>>>> "request": "prepareAndExecute",
>>>>>>> "connectionId": 8,
>>>>>>> "statementId": 18,
>>>>>>> "sql": "UPSERT INTO us_population VALUES('YA','Yambol',100000)",
>>>>>>> "maxRowCount": -1
>>>>>>> }
>>>>>>>
>>>>>>> {
>>>>>>> "request": "commit",
>>>>>>> "connectionId": 8
>>>>>>> }
>>>>>>>
>>>>>>> {
>>>>>>> "request": "createStatement",
>>>>>>> "connectionId": 8
>>>>>>> }
>>>>>>>
>>>>>>> {
>>>>>>> "request": "prepareAndExecute",
>>>>>>> "connectionId": 8,
>>>>>>> "statementId": 20,
>>>>>>> "sql": "SELECT * FROM us_population",
>>>>>>> "maxRowCount": -1
>>>>>>> }
>>>>>>>
>>>>>>> And this is the commit command response (if it can give you more
>>>>>>> insights)
>>>>>>>
>>>>>>> {
>>>>>>> "response": "resultSet",
>>>>>>> "connectionId": "8",
>>>>>>> "statementId": 19,
>>>>>>> "ownStatement": true,
>>>>>>> "signature": {
>>>>>>> "columns": [
>>>>>>> {
>>>>>>> "ordinal": 0,
>>>>>>> "autoIncrement": false,
>>>>>>> "caseSensitive": false,
>>>>>>> "searchable": true,
>>>>>>> "currency": false,
>>>>>>> "nullable": 1,
>>>>>>> "signed": false,
>>>>>>> "displaySize": 40,
>>>>>>> "label": "TABLE_SCHEM",
>>>>>>> "columnName": "TABLE_SCHEM",
>>>>>>> "schemaName": "",
>>>>>>> "precision": 0,
>>>>>>> "scale": 0,
>>>>>>> "tableName": "SYSTEM.TABLE",
>>>>>>> "catalogName": "",
>>>>>>> "type": {
>>>>>>> "type": "scalar",
>>>>>>> "id": 12,
>>>>>>> "name": "VARCHAR",
>>>>>>> "rep": "STRING"
>>>>>>> },
>>>>>>> "readOnly": true,
>>>>>>> "writable": false,
>>>>>>> "definitelyWritable": false,
>>>>>>> "columnClassName": "java.lang.String"
>>>>>>> },
>>>>>>> {
>>>>>>> "ordinal": 1,
>>>>>>> "autoIncrement": false,
>>>>>>> "caseSensitive": false,
>>>>>>> "searchable": true,
>>>>>>> "currency": false,
>>>>>>> "nullable": 1,
>>>>>>> "signed": false,
>>>>>>> "displaySize": 40,
>>>>>>> "label": "TABLE_CATALOG",
>>>>>>> "columnName": "TABLE_CATALOG",
>>>>>>> "schemaName": "",
>>>>>>> "precision": 0,
>>>>>>> "scale": 0,
>>>>>>> "tableName": "SYSTEM.TABLE",
>>>>>>> "catalogName": "",
>>>>>>> "type": {
>>>>>>> "type": "scalar",
>>>>>>> "id": 12,
>>>>>>> "name": "VARCHAR",
>>>>>>> "rep": "STRING"
>>>>>>> },
>>>>>>> "readOnly": true,
>>>>>>> "writable": false,
>>>>>>> "definitelyWritable": false,
>>>>>>> "columnClassName": "java.lang.String"
>>>>>>> }
>>>>>>> ],
>>>>>>> "sql": null,
>>>>>>> "parameters": [],
>>>>>>> "cursorFactory": {
>>>>>>> "style": "LIST",
>>>>>>> "clazz": null,
>>>>>>> "fieldNames": null
>>>>>>> },
>>>>>>> "statementType": null
>>>>>>> },
>>>>>>> "firstFrame": {
>>>>>>> "offset": 0,
>>>>>>> "done": true,
>>>>>>> "rows": [
>>>>>>> [
>>>>>>> null,
>>>>>>> null
>>>>>>> ],
>>>>>>> [
>>>>>>> "SYSTEM",
>>>>>>> null
>>>>>>> ]
>>>>>>> ]
>>>>>>> },
>>>>>>> "updateCount": -1,
>>>>>>> "rpcMetadata": {
>>>>>>> "response": "rpcMetadata",
>>>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>>>> }
>>>>>>> }
>>>>>>>
>>>>>>>
>>>>>>> On 19.04.2016 09:56, F21 wrote:
>>>>>>>> That looks fine to me!
>>>>>>>>
>>>>>>>> I think phoenix has AutoCommit set to false by default. So, you
>>>>>>>> will need to issue a commit before selecting:
>>>>>>>> https://calcite.apache.org/docs/avatica_json_reference.html#commitrequest 
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Let me know if it works! :)
>>>>>>>>
>>>>>>>> On 19/04/2016 4:54 PM, Plamen Paskov wrote:
>>>>>>>>> The requests are as follow:
>>>>>>>>>
>>>>>>>>> - open a connection
>>>>>>>>> {
>>>>>>>>> "request": "openConnection",
>>>>>>>>> "connectionId": 5
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> - create statement
>>>>>>>>> {
>>>>>>>>> "request": "createStatement",
>>>>>>>>> "connectionId": 5
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> - prepare and execute the upsert
>>>>>>>>> {
>>>>>>>>> "request": "prepareAndExecute",
>>>>>>>>> "connectionId": 5,
>>>>>>>>> "statementId": 12,
>>>>>>>>> "sql": "UPSERT INTO us_population 
>>>>>>>>> VALUES('CA','California',100000)",
>>>>>>>>> "maxRowCount": -1
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> - create new statement for next select (not sure if i need it)
>>>>>>>>> {
>>>>>>>>> "request": "createStatement",
>>>>>>>>> "connectionId": 5
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> - select all cities
>>>>>>>>> {
>>>>>>>>> "request": "prepareAndExecute",
>>>>>>>>> "connectionId": 5,
>>>>>>>>> "statementId": 13,
>>>>>>>>> "sql": "SELECT * FROM us_population",
>>>>>>>>> "maxRowCount": -1
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> On 19.04.2016 09:50, F21 wrote:
>>>>>>>>>> Can you show the requests you are currently sending? This is
>>>>>>>>>> what a PrepareAndExecute request should look like:
>>>>>>>>>> https://calcite.apache.org/docs/avatica_json_reference.html#prepareandexecuterequest 
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On 19/04/2016 4:47 PM, Plamen Paskov wrote:
>>>>>>>>>>> Josh,
>>>>>>>>>>> I removed the quotation but the result is still the same. I
>>>>>>>>>>> still cannot see the new data added neither with
>>>>>>>>>>> prepareAndExecute or prepareAndExecuteBatch
>>>>>>>>>>>
>>>>>>>>>>> On 17.04.2016 22:45, Josh Elser wrote:
>>>>>>>>>>>> statementId is an integer, not a string. Remove the quotation
>>>>>>>>>>>> marks around the value "2".
>>>>>>>>>>>>
>>>>>>>>>>>> Plamen Paskov wrote:
>>>>>>>>>>>>> Now another error appears for prepare and execute batch 
>>>>>>>>>>>>> request:
>>>>>>>>>>>>>
>>>>>>>>>>>>> <html>
>>>>>>>>>>>>> <head>
>>>>>>>>>>>>> <meta http-equiv="Content-Type"
>>>>>>>>>>>>> content="text/html;charset=ISO-8859-1"/>
>>>>>>>>>>>>> <title>Error 500 </title>
>>>>>>>>>>>>> </head>
>>>>>>>>>>>>> <body>
>>>>>>>>>>>>> <h2>HTTP ERROR: 500</h2>
>>>>>>>>>>>>> <p>Problem accessing /. Reason:
>>>>>>>>>>>>>
>>>>>>>>>>>>> <pre>
>>>>>>>>>>>>> com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: 
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Unrecognized field "statementId" (class
>>>>>>>>>>>>> org.apache.calcite.avatica.remote.Service$SchemasRequest),
>>>>>>>>>>>>> not marked as
>>>>>>>>>>>>> ignorable (3 known properties: , "connectionId", "catalog",
>>>>>>>>>>>>> "schemaPattern"])
>>>>>>>>>>>>> at [Source: java.io.StringReader@3b5c02a5; line: 6, column:
>>>>>>>>>>>>> 2] (through
>>>>>>>>>>>>> reference chain:
>>>>>>>>>>>>> org.apache.calcite.avatica.remote.SchemasRequest["statementId"])</pre> 
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> </p>
>>>>>>>>>>>>> <hr />
>>>>>>>>>>>>> <i>
>>>>>>>>>>>>> <small>Powered by Jetty://</small>
>>>>>>>>>>>>> </i>
>>>>>>>>>>>>> </body>
>>>>>>>>>>>>> </html>
>>>>>>>>>>>>>
>>>>>>>>>>>>> My request looks like:
>>>>>>>>>>>>> {
>>>>>>>>>>>>> "request": "prepareAndExecuteBatch",
>>>>>>>>>>>>> "connectionId": "3",
>>>>>>>>>>>>> "statementId": "2",
>>>>>>>>>>>>> "sqlCommands": [ "UPSERT INTO us_population VALUES('C1','City
>>>>>>>>>>>>> 1',100000)", "UPSERT INTO us_population VALUES('C2','City
>>>>>>>>>>>>> 2',1000000)" ]
>>>>>>>>>>>>> }
>>>>>>>>>>>>>
>>>>>>>>>>>>> Any help will be appreciated!
>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>
>>>>>>>>>>>>> On 14.04.2016 14:58, Plamen Paskov wrote:
>>>>>>>>>>>>>> Ah i found the error. It should be "sqlCommands": instead of
>>>>>>>>>>>>>> "sqlCommands",
>>>>>>>>>>>>>> The documentation syntax is wrong for this request type:
>>>>>>>>>>>>>> http://calcite.apache.org/avatica/docs/json_reference.html#prepareandexecutebatchrequest 
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> On 14.04.2016 11:09, Plamen Paskov wrote:
>>>>>>>>>>>>>>> @Josh: thanks for your answer.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Folks,
>>>>>>>>>>>>>>> I'm trying to prepare and execute batch request with no 
>>>>>>>>>>>>>>> luck.
>>>>>>>>>>>>>>> These are the requests i send:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> {
>>>>>>>>>>>>>>> "request": "openConnection",
>>>>>>>>>>>>>>> "connectionId": "2"
>>>>>>>>>>>>>>> }
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> {
>>>>>>>>>>>>>>> "request": "createStatement",
>>>>>>>>>>>>>>> "connectionId": "2"
>>>>>>>>>>>>>>> }
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> {
>>>>>>>>>>>>>>> "request": "prepareAndExecuteBatch",
>>>>>>>>>>>>>>> "connectionId": "2",
>>>>>>>>>>>>>>> "statementId": 1,
>>>>>>>>>>>>>>> "sqlCommands", [ "UPSERT INTO
>>>>>>>>>>>>>>> us_population(STATE,CITY,POPULATION)
>>>>>>>>>>>>>>> VALUES('C1','City 1',100000)", "UPSERT INTO
>>>>>>>>>>>>>>> us_population(STATE,CITY,POPULATION) VALUES('C2','City
>>>>>>>>>>>>>>> 2',1000000)" ]
>>>>>>>>>>>>>>> }
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> And this is the response i receive:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> <html>
>>>>>>>>>>>>>>> <head>
>>>>>>>>>>>>>>> <meta http-equiv="Content-Type"
>>>>>>>>>>>>>>> content="text/html;charset=ISO-8859-1"/>
>>>>>>>>>>>>>>> <title>Error 500 </title>
>>>>>>>>>>>>>>> </head>
>>>>>>>>>>>>>>> <body>
>>>>>>>>>>>>>>> <h2>HTTP ERROR: 500</h2>
>>>>>>>>>>>>>>> <p>Problem accessing /. Reason:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> <pre> com.fasterxml.jackson.core.JsonParseException:
>>>>>>>>>>>>>>> Unexpected
>>>>>>>>>>>>>>> character (',' (code 44)): was expecting a colon to
>>>>>>>>>>>>>>> separate field
>>>>>>>>>>>>>>> name and value
>>>>>>>>>>>>>>> at [Source: java.io.StringReader@41709697; line: 5, column:
>>>>>>>>>>>>>>> 17]</pre>
>>>>>>>>>>>>>>> </p>
>>>>>>>>>>>>>>> <hr />
>>>>>>>>>>>>>>> <i>
>>>>>>>>>>>>>>> <small>Powered by Jetty://</small>
>>>>>>>>>>>>>>> </i>
>>>>>>>>>>>>>>> </body>
>>>>>>>>>>>>>>> </html>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> On 13.04.2016 19:27, Josh Elser wrote:
>>>>>>>>>>>>>>>> For reference materials: definitely check out
>>>>>>>>>>>>>>>> https://calcite.apache.org/avatica/
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> While JSON is easy to get started with, there are zero
>>>>>>>>>>>>>>>> guarantees on
>>>>>>>>>>>>>>>> compatibility between versions. If you use protobuf, we
>>>>>>>>>>>>>>>> should be
>>>>>>>>>>>>>>>> able to hide all schema drift from you as a client (e.g.
>>>>>>>>>>>>>>>> applications you write against Phoenix 4.7 should continue
>>>>>>>>>>>>>>>> to work
>>>>>>>>>>>>>>>> against 4.8, 4.9, etc).
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Good luck with the PHP client -- feel free to reach out if
>>>>>>>>>>>>>>>> you have
>>>>>>>>>>>>>>>> more issues. Let us know you have something to shared. I'm
>>>>>>>>>>>>>>>> sure
>>>>>>>>>>>>>>>> others would also find it very useful.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> F21 wrote:
>>>>>>>>>>>>>>>>> I am currently building a golang client as well, so I've
>>>>>>>>>>>>>>>>> been looking
>>>>>>>>>>>>>>>>> the api over the last few weeks.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> I am not sure about the decision to have to create a
>>>>>>>>>>>>>>>>> statement first,
>>>>>>>>>>>>>>>>> but in terms of go, it fits the sql package very well, 
>>>>>>>>>>>>>>>>> where
>>>>>>>>>>>>>>>>> statements
>>>>>>>>>>>>>>>>> are opened and closed.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> I don't think there are any books (as of yet), but the
>>>>>>>>>>>>>>>>> references and
>>>>>>>>>>>>>>>>> digging through the code should be quite useful. I also
>>>>>>>>>>>>>>>>> recommend
>>>>>>>>>>>>>>>>> checking out the avatica project (which is a sub project
>>>>>>>>>>>>>>>>> of calcite)
>>>>>>>>>>>>>>>>> which is used to power the query server.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Also, the query server uses protobufs by default now, so
>>>>>>>>>>>>>>>>> it would
>>>>>>>>>>>>>>>>> probably be better to use that rather than the JSON api.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> On 13/04/2016 10:21 PM, Plamen Paskov wrote:
>>>>>>>>>>>>>>>>>> thanks for your quick and accurate answer ! it's working
>>>>>>>>>>>>>>>>>> now!
>>>>>>>>>>>>>>>>>> can you give me a brief explanation of why is it to
>>>>>>>>>>>>>>>>>> mantain the state
>>>>>>>>>>>>>>>>>> via the json api so i can better understand how to
>>>>>>>>>>>>>>>>>> create a php
>>>>>>>>>>>>>>>>>> wrapper library. if there are some books or references
>>>>>>>>>>>>>>>>>> where i can
>>>>>>>>>>>>>>>>>> read more about apache phoenix will be very helpful.
>>>>>>>>>>>>>>>>>> thanks
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> On 13.04.2016 13:29, F21 wrote:
>>>>>>>>>>>>>>>>>>> Your PrepareAndExecute request is missing a 
>>>>>>>>>>>>>>>>>>> statementId:
>>>>>>>>>>>>>>>>>>> https://calcite.apache.org/docs/avatica_json_reference.html#prepareandexecuterequest 
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> Before calling PrepareAndExecute, you need to send a
>>>>>>>>>>>>>>>>>>> CreateStatement
>>>>>>>>>>>>>>>>>>> request to the server so that it can give you a
>>>>>>>>>>>>>>>>>>> statementId. Then,
>>>>>>>>>>>>>>>>>>> use that statementId in your PrepareAndExecute request
>>>>>>>>>>>>>>>>>>> and all
>>>>>>>>>>>>>>>>>>> should
>>>>>>>>>>>>>>>>>>> be fine :)
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> On 13/04/2016 8:24 PM, Plamen Paskov wrote:
>>>>>>>>>>>>>>>>>>>> Hi guys,
>>>>>>>>>>>>>>>>>>>> I just setup apache phoenix 4.7 and set the
>>>>>>>>>>>>>>>>>>>> serialization to JSON.
>>>>>>>>>>>>>>>>>>>> Now i'm trying to run a select statement but what i
>>>>>>>>>>>>>>>>>>>> receive is
>>>>>>>>>>>>>>>>>>>> this:
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> {
>>>>>>>>>>>>>>>>>>>> "response": "executeResults",
>>>>>>>>>>>>>>>>>>>> "missingStatement": true,
>>>>>>>>>>>>>>>>>>>> "rpcMetadata": {
>>>>>>>>>>>>>>>>>>>> "response": "rpcMetadata",
>>>>>>>>>>>>>>>>>>>> "serverAddress": "ip-172-31-27-198:8765"
>>>>>>>>>>>>>>>>>>>> },
>>>>>>>>>>>>>>>>>>>> "results": null
>>>>>>>>>>>>>>>>>>>> }
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> My request looks like this:
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> curl -XPOST -H 'request: 
>>>>>>>>>>>>>>>>>>>> {"request":"prepareAndExecute",
>>>>>>>>>>>>>>>>>>>> "connectionId":"1", "sql":"select * from 
>>>>>>>>>>>>>>>>>>>> us_population",
>>>>>>>>>>>>>>>>>>>> "maxRowCount":-1}' http://52.31.63.96:8765/
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Running the select above from the command line is fine
>>>>>>>>>>>>>>>>>>>> and it
>>>>>>>>>>>>>>>>>>>> returns 2 rows :
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> sqlline version 1.1.8
>>>>>>>>>>>>>>>>>>>> 0: jdbc:phoenix:localhost> select * from 
>>>>>>>>>>>>>>>>>>>> us_population;
>>>>>>>>>>>>>>>>>>>> +-------+------------------------------------------+------------------------------------------+ 
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> | STATE | CITY | POPULATION |
>>>>>>>>>>>>>>>>>>>> +-------+------------------------------------------+------------------------------------------+ 
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> | CA | Los Angeles | 3844829 |
>>>>>>>>>>>>>>>>>>>> | NY | New York | 8143197 |
>>>>>>>>>>>>>>>>>>>> +-------+------------------------------------------+------------------------------------------+ 
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> 2 rows selected (0.087 seconds)
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Can you give me some direction what i'm doing wrong as
>>>>>>>>>>>>>>>>>>>> i'm not java
>>>>>>>>>>>>>>>>>>>> dev and it's not possible for me to read and
>>>>>>>>>>>>>>>>>>>> understand the source
>>>>>>>>>>>>>>>>>>>> code.
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Thanks in advance !
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>


Mime
View raw message