SELECT INTO S3 - file format

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,

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

You can configure the output format of INTO OUTFILE via the same options as LOAD DATA. See here for reference: https://docs.memsql.com/v7.0/reference/sql-reference/data-manipulation-language-dml/select/#select--into-outfile

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.