Load JSON Data Containing Base64-encoded binary

Help Connectors and Pipelines
1

Example JSON:

{"a":"ESIzRFVm","b":"ESIzRFVmESIzRFVm"}

With the following table:

CREATE TABLE IF NOT EXISTS example(
  a binary(6) NOT NULL,
  b binary(12) NOT NULL
);

I’m trying to LOAD DATA into the table using FORMAT JSON.
I know there’s a handy function FROM_BASE64(str) but I’m having trouble using it in the LOAD DATA statement. Unfortunately SingleStoreDB Cloud · SingleStore Documentation is not making it any more clear.

2

We do in fact support this via MySQL-style expressions on @-variables in a SET clause. With your example, that’d be:

MySQL [db]> load data local infile “/tmp/example.json” into table example(@foo ← a, @bar ← b) format json set a = from_base64(@foo), b = from_base64(@bar);
Query OK, 1 row affected (0.00 sec)

MySQL [db]> select * from example;
±-------±-------------+
| a | b |
±-------±-------------+
| "3DUf | "3DUf"3DUf |
±-------±-------------+
1 row in set (0.00 sec)

I agree with your analysis of the docs. We’ll make the function of the SET clause there clearer.