How to send BINARY parameters using the REST Sql API?

[Gusman]

We are trying to send a SQL query to the REST API including a BINARY parameter, EX:

"INSERT INTO MyTable (BinaryField) VALUES(:binaryData)"

We tried to encode the parameter as base64 and specify that is a BINARY type but it throws a mapping error, if we send it as STRING base64 encoded it inserts the literal string.

It is possible to use "unbase64" in each query but we would like to avoid this as we want to automate the process.

What would be the way to properly encode the parameter in order to send it?

[cgrant · Accepted solution]

Trying to serialize as binary can be pretty challenging, here's a way to do this with base64 - the trick is to serialize as base64 string and insert as binary with unbase64. 

databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id": "warehouse-id",
  "catalog": "catalog",
  "schema": "schema", 
  "statement": "INSERT INTO binary (bin) VALUES (unbase64(:bin))",
  "parameters": [
    {"name": "bin", "value": "$(cat ~/pixel.png | base64)", "type": "STRING"}
  ]
}'