Re: How do I send binary data to avatica?

[F21]

I have now created a repo on github containing all the binary protobuf 
requests and responses to insert binary data into a VARBINARY column. 
The repo is available here: https://github.com/F21/avatica-binary-protobufs

The file 8-request is of most interest because that's the one where we 
are sending the ExecuteRequest to do the actual insertion of the image.

I am using the protoc tool to decode the messages, e.g.:  protoc 
--decode_raw < 8-request

Let me know if this helps :)

On 18/04/2016 3:31 AM, Josh Elser wrote:
Sorry, yes. I just didn't want to have to post the JSON elsewhere -- 
it would've been gross to include in an email. For JSON, it is base64 
encoded. This is not necessary for protobuf (which can natively handle 
raw bytes).

For #2, did you verify that the data made it into the database 
correctly? HBase/Phoenix still, right? Also, maybe this is a 
SquirrelSQL issue? Can you verify the record is present via Phoenix's 
sqlline-thin.py?

For #3, very interesting. I'm not sure why base64 encoding it by hand 
makes any difference for you. Avatica isn't going to be making any 
differentiation in the types of bytes that you send. Bytes are bytes 
are bytes as far as we care.

F21 wrote:
Just reporting back my experiments:

1. Using JSON: I set the serialization of the query server to JSON and
replayed your requests using CURL. For the binary file, I first base64
encode it into a string and then sent it. This worked properly and I can
see data inserted into the table using SquirrelSQL.

2. Using Protobufs: I inserted the binary data using Rep_BYTE_STRING and
set BytesValues to the byte array read in from the file. It inserts
(upserts) correctly, but if I query the table using SquirrelSQL, the
binary column's cell is shown as <null>.

3. Using Protobufs with Base64 encoding: I first encode the binary data
as base64. I then upsert the parameter as Rep_STRING and set StringValue
to the base64 encoded string. This upserts correctly and I can see the
data in SquirrelSQL. I then SELECT the data and base64 decode it and
write it to a file and generate a hash. The file is written correctly
and the hash also matches.

So, approach 3 works for me, but it doesn't seem to be the correct way
to do it.

On 17/04/2016 8:17 AM, Josh Elser wrote:
I wrote a simple test case for this with the gopher image. Here's the
JSON data (but hopefully this is enough to help out). I'd have to
write more code to dump out the actual protobufs. Let me know if this
is insufficient to help you figure out what's wrong. My test worked 
fine.

CREATE TABLE binaryData(id int, data varbinary(262144));

[{"request":"prepare","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","sql":"INSERT 

INTO binaryData values(?,?)","maxRowCount":-1},
{"response":"prepare","statement":{"connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","id":1,"signature":{"columns":[],"sql":"INSERT 

INTO binaryData
values(?,?)","parameters":[{"signed":true,"precision":10,"scale":0,"parameterType":4,"typeName":"INTEGER","className":"java.lang.Integer","name":"?1"},{"signed":false,"precision":262144,"scale":0,"parameterType":-3,"typeName":"VARBINARY","className":"[B","name":"?2"}],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"}},"rpcMetadata":null}] 



[{"request":"execute","statementHandle":{"connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","id":1,"signature":null},"parameterValues":[{"type":"INTEGER","value":1},{"type":"BYTE_STRING","value":"<REDACTED>"}],"maxRowCount":100}, 

{"response":"executeResults","missingStatement":false,"rpcMetadata":null,"results":[{"response":"resultSet","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":1,"ownStatement":true,"signature":null,"firstFrame":null,"updateCount":1,"rpcMetadata":null}]}] 


[{"request":"closeStatement","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":1}, 

{"response":"closeStatement","rpcMetadata":null}]

[{"request":"prepareAndExecute","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":0,"sql":"SELECT 

*
FROM binaryData","maxRowCount":-1},
{"response":"executeResults","missingStatement":false,"rpcMetadata":null,"results":[{"response":"resultSet","oonnectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":0,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":true,"displaySize":11,"label":"ID","columnName":"ID","schemaName":"SCOTT","precision":32,"scale":0,"tableName":"BINARYDATA","catalogName":"PUBLIC","type":{"type":"scalar","id":4,"name":"INTEGER","rep":"PRIMITIVE_INT"},"readOnly":false,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Integer"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":262144,"label":"DATA","columnName":"DATA","schemaName":"SCOTT","precision":262144,"scale":0,"tableName":"BINARYDATA","catalogName":"PUBLIC","type":{"type":"scalar","id":-3,"name":"VARBINARY","rep":"B 

YTE

_STRING"},"readOnly":false,"writable":false,"definitelyWritable":false,"columnClassName":"[B"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"},"firstFrame":{"offset":0,"done":true,"rows":[[1,"<REDACTED>"]]},"updateCount":-1,"rpcMetadata":null}]}] 



Josh Elser wrote:
Super helpful! Thanks.

I'll try to take a look at this tonight. If not then, over the 
weekend,
likely. Will report back.

F21 wrote:
Hey Josh,

Here's what I am doing:

Create the table: CREATE TABLE test ( int INTEGER PRIMARY KEY, bin
VARBINARY) TRANSACTIONAL=false
Download the binary file we want to insert from here:
https://raw.githubusercontent.com/golang-samples/gopher-vector/master/gopher.png 





Prepare the statement: UPSERT INTO test VALUES (?, ?)

Here's the code I am using to execute the statement:

// read the file into a byte array []byte
file, _ := ioutil.ReadFile(filePath)

// create array of typed values containing the parameters
parameters := []*message.TypedValue{
&message.TypedValue{
Type: message.Rep_LONG,
NumberValue: 1,
},
&message.TypedValue{
Type: message.Rep_BYTE_STRING,
BytesValues: file,
},
}

// create message:
msg := &message.ExecuteRequest{
StatementHandle: &s.handle, // this is just the statement handle
ParameterValues: parameters,
MaxRowCount: 100,
HasParameterValues: true,
}

// encode the message:
wrapped, _ := proto.Marshal(message)

// wrap it in the wire message:
wire := &message.WireMessage{
Name: " org.apache.calcite.avatica.proto.Requests$ ExecuteRequest",
WrappedMessage: wrapped,
}

// encode wire message and send over http:
body, _ := proto.Marshal(wire)
response, _ := ctxhttp.Post(context.Background(), httpClient,
avaticaServer, "application/x-google-protobuf", 
bytes.NewReader(body))

Let me know if there's more information I can provide or if 
something's
unclear :)

On 15/04/2016 2:46 AM, Josh Elser wrote:
Yeah, that sounds right to me too.

I think we have a test for random bytes. Maybe there's something 
weird
happening under the hood in the Avatica JDBC driver that isn't 
obvious
to you in the Go driver.

Any chance you can share some example code you're running? I can try
to convert it to a Java test case, maybe help track down your issue.

F21 wrote:
I also tried casting the data to a string and setting it to
StringValue
and the Rep type to STRING.

This works when I store and retrieve strings from the binary 
column,
but
doesn't work correctly if I try to store something like a small
image.

On 14/04/2016 5:03 PM, Julian Hyde wrote:
BytesValue sounds right. I’m not sure why it isn’t working for 
you.

On Apr 14, 2016, at 6:34 AM, F21 <f21.gro...@gmail.com> wrote:

As mentioned on the list, I am currently working on a golang
client/driver for avatica using protobufs for serialization.

I've got all datatypes working, except for BINARY and VARBINARY.

For my test table looks like this:
CREATE TABLE test (int INTEGER PRIMARY KEY, bin BINARY(20), 
varbin
VARBINARY) TRANSACTIONAL=false

In go, we have a datatype called a slice of bytes ([]byte) 
which is
essentially an array of bytes (8-bits each).

When I generated the golang protobufs using the .proto files,
this is
the definition of TypedValue:
type TypedValue struct {
Type Rep `protobuf:"varint,1,opt,name=type,enum=Rep"
json:"type,omitempty"`
BoolValue bool
`protobuf:"varint,2,opt,name=bool_value,json=boolValue"
json:"bool_value,omitempty"`
StringValue string
`protobuf:"bytes,3,opt,name=string_value,json=stringValue"
json:"string_value,omitempty"`
NumberValue int64
`protobuf:"zigzag64,4,opt,name=number_value,json=numberValue"
json:"number_value,omitempty"`
BytesValues []byte
`protobuf:"bytes,5,opt,name=bytes_values,json=bytesValues,proto3"
json:"bytes_values,omitempty"`
DoubleValue float64
`protobuf:"fixed64,6,opt,name=double_value,json=doubleValue"
json:"double_value,omitempty"`
Null bool `protobuf:"varint,7,opt,name=null" 
json:"null,omitempty"`
}

I am currently creating a TypedValue that looks like this when
sending binary data:
{BYTE_STRING false 0 [116 101 115 116] 0 false}

So, the Rep is set to BYTE_STRING and ByteValues is populated 
with
the string "test" in bytes (it's shown here as decimal because I
printed it).

The problem is that even though the insert executes properly, 
if I
look at the row using SquirrelSQL, both the BINARY and VARBINARY
columns are <null>.

Is BYTE_STRING the correct rep type for sending binary data? Do I
also have to encode my bytes in a special format?

Thanks!