How to convert a QUERY object to RECORD object

Help Procedural SQL
1

I have a stored procedure to ingest data from pipeline, something like
CREATE OR REPLACE PROCEDURE proc_to_tables(csv_data query(var1 varchar(512),
var2 MEDIUMINT unsigned,
time int(10) unsigned,
var4 MEDIUMINT unsigned,
var5 BIGINT unsigned,)
AS
DELARE
// Here I want to create a record object with query object csv_data that I have
// Something like 'row_data = RECORD(csv_data)
BEGIN
…
END

Please let me know the best way to create a record object from query object.

2

The pipeline itself will deserialize the SP, so you don’t have to do anything. You can use the csv_data as a TVF, and contains all the rows in that batch. To say that another way, the SP doesn’t run on single rows, it runs on a distributed dataset of a batch of rows. For instance, if you want to insert each row of the batch into a table, you can do insert into t select * from batch, or whatever more sophisticated logic you want. If you must go row-by-row, you can collect(csv_data) into an array, but that will accumulate the whole batch on the aggregator, which can be slow or have scaling problems.

3

Thanks for the reply, the reason I am asking this is, in this SP i need to do an aggregation of the data, which is not supported by memsql. Somethig like this

memsql> insert into t1 (a,b,c) select x, y, z from csv_data on duplicate key update a = a+x;

So, my idea was to create a variable of RECORD type and later refer to that variable for any aggregation.

Thanks
Surendra

4

As Joseph mentioned, use COLLECT() SingleStoreDB Cloud · SingleStore Documentation
I think it is exactly what you are looking for. It returns an array of records over which you can iterate for your aggregation. Look at the example in the link for more information.