RDB/IHDB/HDB - What is the best way of a copy of the values of an in-memory table?

[adrianosm_]

Assume I have an RDB (or IHDB / HDB) that has access to a schema called people.

Here is some data you can put on people:

([] sym: `ada`maggie; name: `ada`maggie; age 33 25)

Assume also that I have a function called doSomething that receives the table name as it is argument and performs a series of operations, including an xkey at some point:

doSomething:{[tableName]
// blablablah some code
r: `sym xkey value tableName;
// some more code
}

q does not like to perform the xkey directly on value tableName, so the solution I currently have for this is performing a select:

doSomething:{tableName
  //blablablah some code
  r: `sym xkey ?[value tableName; (); 0b; ()];
  //blablablah some more code
}

 

My question is: is there a better way to get around that or is my solution fine?

Cheers,
Adriano

[kjamison · Accepted solution]

Hi Adriano,

We've had a look at your example and for in-memory tables your first solution appears to work perfectly as long as you are passing a reference into your function.

for example:

doSomething[`tablename]

However if you were working with an on-disk table rather than an in-memory table your solution could be simplified to

q)r:`sym xkey select from tableName

using the select function in order to pull your table into memory first.

Your functional form solution works fine however most people will use q-SQL as it is a more readable format. A where clause can be used alongside the select query to avoid pulling in a large amount of data from disk.

Cheers, 

Kate

[kjamison · Accepted solution]

Hi Adriano,

We've had a look at your example and for in-memory tables your first solution appears to work perfectly as long as you are passing a reference into your function.

for example:

doSomething[`tablename]

However if you were working with an on-disk table rather than an in-memory table your solution could be simplified to

q)r:`sym xkey select from tableName

using the select function in order to pull your table into memory first.

Your functional form solution works fine however most people will use q-SQL as it is a more readable format. A where clause can be used alongside the select query to avoid pulling in a large amount of data from disk.

Cheers, 

Kate

[dcrossey · Accepted solution]

Hi Adriano,

By "q does not like to perform the xkey directly on value table", do you mean as follows:

q)`name xkey value people
'type
  [0]  `name xkey value people
                  ^

Here, we are referring to the table with pass-by-value and q throws a type error. You don't need to use 'value' if you are referring to a table in this manner (note the lack of backtick).

If you used the ` people with 'value' this would have worked as expected (pass-by-reference) :

q)`name xkey value `people
name  | age sym
------| ----------
ada   | 33  ada
maggie| 25  maggie

 In general, I would advise the following syntax for general usage (pass-by-value) :

people:`name xkey people;

Otherwise using pass-by-reference:

people:`name xkey value `people;

Hope this helps.

Best,
David

[adrianosm_]

Hi Kate and David,

Both of your tips were great, thank you very much 😄

It seems I will have to adopt the solution for on-disk tables that Kate suggested because the function doSomething is routed to the IHDB and performs its operations on on-disk tables.

So, when people is an on-disk table, if i try pass by value:

people: `name xkey people

then the IHDB throws a int error message
if i do:

`name xkey select from people

then the IHDB is happy. Since I won't dare 😅 to change the key of my table on the IHDB, it looks like pulling the values to the memory first is the safest option.

Thanks again!