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 Tue, 19 Apr 2016 11:21:52 GMT
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