SELECT INTO S3 - file format

Off-Topic
1

Hello,

I managed to successfully load data from mem to S3 bucket.
But the format is something I cannot use.

Can i make it JSON or CSV?

SELECT *
FROM table
WHERE time > '2020-05-20 00:00:00' 
INTO S3 'bucket/file'
CONFIG '{}'
CREDENTIALS '{"aws_access_key_id":"***","aws_secret_access_key":"***"}'

Regards,

2

With 7.1 you would be able to run select to_json(*) from table

3
4

You can configure the output format of INTO OUTFILE via the same options as LOAD DATA. See here for reference: SELECT · SingleStore Documentation

5

You can export to CSV format using SELECT INTO OUTFILE/S3, using the instructions that @rob linked to above. The CONFIG and CREDENTIALS go first, then the formatting instructions. E.g.

SELECT * FROM t INTO S3 'tmp/a'
        CONFIG '{"region":"us-east-1"}'
        CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'

The TO_JSON function implementation was not quite ready for 7.1, so has been removed from the general availability (GA) release, coming shortly. We are considering options for when to deliver it, and will report back later when we have a firm delivery date.

6

How do we restore data from an OUTFILE on S3?

7

With LOAD DATA or PIPELINES, typically. You’d have to write your LOAD DATA or CREATE PIPELINE command in a way that understood the file format.