Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Insert multiple rows with one request using phoenix query server on Hbase

Solved Go to solution
Highlighted

Insert multiple rows with one request using phoenix query server on Hbase

New Contributor

I am looking to combine multiple upsert requests into one request and pass it to the phoenix query server.

I am sending the following json to upsert one record

POST https://tishihdiphoenix.azurehdinsight.net/hbasephoenix/ HTTP/1.1
request:{"request":"prepareAndExecute","connectionId":"000000-0000-0000-00000001","sql":""UPSERT INTO Table( col1 ) VALUES ( value1 )":100}
Authorization: Basic YWRtaW46Tm9tb3JlTm9tb3JlIT0x
Host: tishihdiphoenix.azurehdinsight.net
Content-Length: 0
Connection: Keep-Alive
I want to be able to upsert multiple statements with different values in one json. 
I tried changing the sql statement to something like
UPSERT INTO Table( col1 ) VALUES ( value1 ), ( value2 ) but that didn't work. I followed the steps to prepare request and then created statement. I got a create response but I couldn't get either the fetch or the execute to work. 

Fetch response > Statement not found, potentially expired. 000000-0000-0000-00000001::1453337888</pre></p>

Execute response > com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field "statementHandle" (class org.apache.calcite.avatica.remote.Service$SchemasRequest), not marked as ignorable (2 known properties: , "catalog", "schemaPattern"])
 at [Source: java.io.StringReader@204e1f9c; line: 1, column: 312] (through reference chain: org.apache.calcite.avatica.remote.SchemasRequest["statementHandle"])


Any ideas on what I might need to do after create statement 
 


1 ACCEPTED SOLUTION

Accepted Solutions

Re: Insert multiple rows with one request using phoenix query server on Hbase

You cannot currently perform multiple upserts within a single HTTP call to the Phoenix QueryServer.

Support for commit/rollback was recently added to Calcite (the technology behind the Phoenix QueryServer) in https://issues.apache.org/jira/browse/CALCITE-767 which is relevant for what you're trying to do. However, this was not yet released in a version of HDP. The ability to use commit and rollback lets you batch multiple calls inside of the PQS instance before committing them to HBase.

6 REPLIES 6

Re: Insert multiple rows with one request using phoenix query server on Hbase

What version of HDP are you using? It seems like there is a mismatch between your client and server libraries.

Re: Insert multiple rows with one request using phoenix query server on Hbase

New Contributor

Re: Insert multiple rows with one request using phoenix query server on Hbase

You cannot currently perform multiple upserts within a single HTTP call to the Phoenix QueryServer.

Support for commit/rollback was recently added to Calcite (the technology behind the Phoenix QueryServer) in https://issues.apache.org/jira/browse/CALCITE-767 which is relevant for what you're trying to do. However, this was not yet released in a version of HDP. The ability to use commit and rollback lets you batch multiple calls inside of the PQS instance before committing them to HBase.

Re: Insert multiple rows with one request using phoenix query server on Hbase

New Contributor

Thanks for the quick answer, would you happen to know when it would become available?

Re: Insert multiple rows with one request using phoenix query server on Hbase

I am not sure, but I can see if we have any public roadmap posted for you.

Re: Insert multiple rows with one request using phoenix query server on Hbase

New Contributor

Yes There is a way.....

Take a look at following working query and modify your code accordingly to generate like queries.

Consider a table XYZ with columns ID,CF.RISK :

UPSERT INTO XYZ(ID,CF.RISK SELECT ID,(CASE ID WHEN '12341e65-a68b-4f88-93ce-33dd94497a6d' THEN 'MEDIUM_RISK' WHEN '73f41e65-a68b-4f88-93ce-33dd94497a6d' THEN 'HIGH_RISK' ELSE 'LOW_RISK' END) FROM ACCOUNTS WHERE ID IN('12341e65-a68b-4f88-93ce-33dd94497a6d','73f41e65-a68b-4f88-93ce-33dd94497a6d')

It updates column CF.RISK for all ID's in WHERE clause list.

,

Yes there is a way;

Take a look at following working query and modify your code accordingly: Consider a table XYZ with columns ID,CF.RISK;

UPSERT INTO XYZ(ID,CF.RISK SELECT ID,(CASE ID WHEN '12341e65-a68b-4f88-93ce-33dd94497a6d' THEN 'MEDIUM_RISK' WHEN '73f41e65-a68b-4f88-93ce-33dd94497a6d' THEN 'HIGH_RISK' ELSE 'LOW_RISK' END) FROM ACCOUNTS WHERE ID IN('12341e65-a68b-4f88-93ce-33dd94497a6d','73f41e65-a68b-4f88-93ce-33dd94497a6d')

Don't have an account?
Coming from Hortonworks? Activate your account here