phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Plamen Paskov <plamen.pas...@next-stream.com>
Subject Re: apache phoenix json api
Date Thu, 21 Apr 2016 07:37:55 GMT
Hi Francis,
Thank you for supporting me ! I appreciate that!
Below are the commands i executed and the respective responses. As you 
will see the new data is missing.
And these are the details about my setup:

OS: Ubuntu 14.04.3 LTS
hbase: Version 1.1.2.2.3.4.0-3485
phoenix: Version 4.4.0.2.3.4.0-3485


curl -XPOST 52.31.63.96:8765 -d '{"request": "openConnection", 
"connectionId": "12"}'

response:
{
   "response": "openConnection",
   "rpcMetadata": {
     "response": "rpcMetadata",
     "serverAddress": "ip-172-31-27-198:8765"
   }
}
----------------------------------------

curl -XPOST 52.31.63.96:8765 -d '{"request": "createStatement", 
"connectionId": "12"}'

response:
{
   "response": "createStatement",
   "connectionId": "12",
   "statementId": 28,
   "rpcMetadata": {
     "response": "rpcMetadata",
     "serverAddress": "ip-172-31-27-198:8765"
   }
}
----------------------------------------

curl -XPOST 52.31.63.96:8765 --data @data.json

data.json content
{
   "request": "prepareAndExecute",
   "connectionId": "12",
   "statementId": 28,
   "sql": "UPSERT INTO us_population VALUES('CA','Bla bla',100000)",
   "maxRowCount": -1
}

response:
{
   "response": "executeResults",
   "missingStatement": false,
   "rpcMetadata": {
     "response": "rpcMetadata",
     "serverAddress": "ip-172-31-27-198:8765"
   },
   "results": [
     {
       "response": "resultSet",
       "connectionId": "12",
       "statementId": 28,
       "ownStatement": false,
       "signature": null,
       "firstFrame": null,
       "updateCount": 1,
       "rpcMetadata": {
         "response": "rpcMetadata",
         "serverAddress": "ip-172-31-27-198:8765"
       }
     }
   ]
}
----------------------------------------

curl -XPOST 52.31.63.96:8765 -d '{"request": "commit", "connectionId": 
"12"}'

response:
{
   "response": "resultSet",
   "connectionId": "12",
   "statementId": 29,
   "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"
   }
}
----------------------------------------

curl -XPOST 52.31.63.96:8765 -d '{"request": "prepareAndExecute", 
"connectionId": "12", "statementId": 28, "sql": "SELECT * FROM 
us_population","maxRowCount": -1}'

{
   "response": "executeResults",
   "missingStatement": false,
   "rpcMetadata": {
     "response": "rpcMetadata",
     "serverAddress": "ip-172-31-27-198:8765"
   },
   "results": [
     {
       "response": "resultSet",
       "connectionId": "12",
       "statementId": 28,
       "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"
       }
     }
   ]
}

Best wishes
Plamen

On 20.04.2016 13:41, F21 wrote:
> 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