Can we load a Json File from aws S3 into MemSQL database through Pipeline.
is there any way in MemSql which loads complete Json file into MemSQL standard data type
and once all json data in one column , we can transform this json elements and breaks into column and more readable format.
Note: i donot want to insert json data using Insert statement and i donot want to create a pre-defined mapping to load json files
I had process json files into snowflake through aws s3 and transform json data in snowflake using lateral function , so looking for the same in MemSQL
Yes, you can use pipelines or LOAD DATA in SingleStore to load data in JSON format:
If you want to load a chunk of JSON into a regular string type, like LONGTEXT, and they move it later to other places (in JSON format or not) you can certainly do that.
Check out the documentation for pipelines and LOAD DATA to see how to load json data using the FORMAT JSON option. The options are shared between the two commands, so even if you want to use pipelines, check out the LOAD DATA topic for some examples.
Check out also ability to do this, which can convert json arrays to rowsets of JSON objects.
SELECT …
FROM … JOIN TABLE(JSON_TO_ARRAY(t.json_val))
…;
Thanks Hanson for clarification
Based on below statements :
Info See more syntax options for LOAD DATA in CREATE PIPELINE. SingleStore Managed Service only supports LOAD DATA with the LOCAL option.
I understood , we can load json files from Local env (local system) . so we cannot process aws s3 Json files into MemSQL (using managed service)
is this correct ?
Hi Hanson, adding one more doubt to same topic . I have only MemSQL studio (Free trial MemSQL env) . i have open SQL editor and created json_ingest table
and i tried to load one local json file into table but i got below error .
LOAD DATA LOCAL INFILE “C:\WIP\example.txt” INTO TABLE json_ingest(a <- a::b) FORMAT JSON
Local file data loading is not supported in SingleStore Studio
i donot have any other MemSQL tool . i have only MemSQL studio and its sql editor.
is any workaround . if i can load Json files from aws S3 into Memsql env by creating a pipeline
and once Json file gets loaded into Memsql table with Longtext data type , i can do transform
these data into rows ,
As you said earlier :
If you want to load a chunk of JSON into a regular string type, like LONGTEXT, and they move it later to other places (in JSON format or not) you can certainly do that.
Check out the documentation for pipelines and LOAD DATA to see how to load json data using the FORMAT JSON option. The options are shared between the two commands, so even if you want to use pipelines, check out the LOAD DATA topic for some examples.
can i load a Json file elements into Memsql Longtext column