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 07:07:39 GMT
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