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