• We are using Javascript + SQL
• We are trying to check for duplicates (with a algorithm since we have paired data). Then we want add the data if certain columns changed data for a give ID (ID Column, not unique column)
••• We do not want to update the rows, if the row data changes we want to create a new row.
Here are the questions:
1.). Is there a way to use drop/create temp tables and then remove these temp tables?
• Here are the operations I would typically use: CREATE, INSERT, DELETE, DROP, RANK which are all unavailable since they are not read only.
2.) Is there a way to direct insert rows from one table/list to another without first downloading the data?
3.) Is there a way to delete all rows of a table in Javascript quickly? It takes me 29 seconds to delete 3000 rows using the deleteRows() javascript API
Here is the current process:
1.) I am selecting all keys from the temp table (pre-made as a list) and downloading them the script as a data object using labkey.query.selectRows(): <1 seconds (also can have a problem with multiple users accessing the temp table at the same time)
2.) Deleting all rows using labkey.query.deleteRows()
3. ) Inserting the new data as a labkey.query.importData() JSON row object (~3000 rows x 14 columns, 328kb as a text file)
4.) SQL Operation to find duplicates (Based on stored queries/views)
5.) Get new/unique rows and download to javascript
6.) Insert new rows
|
| |
| Jon (LabKey DevOps) responded: |
2018-10-24 10:46 |
Hi David,
LabKey SQL doesn't have the functionality to create/drop temp tables. Something like that would have to be done directly on the database itself, so it's possible to have these actions done through a custom module. If you're using Javascript, it may be possible to create a localstore of your data to temporarily hold the information within the browser session to then work with it and/or send the information elsewhere doing something like a POST to the API and having that localstore information in JSON format when it gets applied to the API.
Regarding inserting rows from one table/list to another without downloading is possible through things such as using ETLs.
https://www.labkey.org/Documentation/wiki-page.view?name=etlModule
Another possible way to do this is using the API to do a selectRows on the one table/list and have that success response point to the insertRows (or importRows) API call in a for loop.
Regarding deleting rows quickly, you're likely going to find the same level of performance when it comes to deleting in the UI as well, like the 29 seconds for it to delete 3000 rows when you were doing this via the Javascript API. LabKey does more behind the scenes when it comes to deleting data, not just removing the rows, but also checking for any dependencies as well to make sure the delete is as clean as it can be.
Based on your description, have you considered using an ETL for your work? Although we'd have to fully assess what you're working with, but it sounds like you could use the ETL to obtain your source data, have a stored procedure on the database server to identify the duplicates, then proceed to insert the values into the target table as new rows.
Regards,
Jon |
| |
| Ben Bimber responded: |
2018-10-24 10:55 |
Is it possible for your duplicate row logic to be performed on the client using JS, instead of via SQL? For example, if you could load whatever source data you need to the client, you could potentially very quickly compare your incoming data to that, identify the minimal inserts, and then insert those new rows using the API. If so, the avoids needing the temp table. Otherwise, like Jon says ETL is probably a good option. |
| |
| dabaker3 responded: |
2018-10-24 11:04 |
I did get this following code to work for delete all rows in a table, but it does use EXT4.
I am not sure if there is any negative impacts, but the keys and list does look like they remained intact.
It may be that the deleteRows() is not doing a batch delete, where as the truncateTable will do batch(s) delete which is at least 30x faster.
I scrubbed it from code in a truncate list post from a few years back and combined it with what a delete all button javascript code in the that the console reported:
var waitMask = Ext4.Msg.wait('Deleting All Rows from List...', 'Deleting Rows');
Ext4.Ajax.request({
url : LABKEY.ActionURL.buildURL('query', 'truncateTable'), // or 'query/[containerPath]/truncateTable.view' if it is in another container,
method : 'POST',
success: function(response) {
waitMask.close();
next_function_to_do();
},
failure : function(response, opts) {
waitMask.close();
Ext4.getBody().unmask();
LABKEY.Utils.displayAjaxErrorResponse(response, opts);
},
jsonData : {
schemaName : 'lists',
queryName : '[queryName]'
},
scope : this
}); |
| |
| dabaker3 responded: |
2018-10-24 11:23 |
• Using Rstudio worked client side, and I can convert that code to Javascript.
• One issue I ran into was with filtering there is a PostgreSQL limit on an "IN" or "NOT IN" of ~2000 items, in which I would have to programmatically disable the filter. An SQL INNER JOIN would have alleviated this limit, but I will go with the javascript solution instead. |
| |
|
|