You can translate the question and the replies:

Data movement for same source

Need to re-factor complex SQL code in Denodo layer. Query is not getting executed in the source(Snowflake) layer after re-factoring it. It is failing with compilation error due to memory/warehouse limitation set at source layer. Can I temporarily store the data of a view that is being re-used multiple times in denodo implementation to snowflake during query processing using DataMovement feature ? Please note that, actual source is snowflake and planning to store the data of a view(re-used multiple times in implementation) temporarily also in Snowflake during execution. Can I design my code this way ? Is this something Denodo also recommends ? Is there anyway I can trace and re-produce the error incase it fails ? Kindly suggest me here.. Regards Nagaraja
DataMovement
user
24-01-2023 02:41:35 -0500
code

3 Answers

Hi, The Denodo Platform is able to support creating a separate temporary table to store data by creating a materialized table or a temporary table. The difference between the two tables is that the materialized table will persist between sessions and the temporary table will be deleted after 48 hours or if the user logs out of the session. It seems like you will need to make a materialized table, but before creating a materialized table, you must enable the cache engine. To do this, follow the steps in the [Configuring the Cache](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/server_configuration/configuring_the_cache/configuring_the_cache#configuring-the-cache) section of the Virtual DataPort Administration Guide. After configuring the cache, you can use the CREATE MATERIALIZED TABLE command to create the temporary table. You can refer to [Creating Materialized Tables](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/materialized_tables/creating_materialized_tables/creating_materialized_tables) in the Virtual DataPort VQL Guide for more information. Hope this helps!
Denodo Team
24-01-2023 20:55:08 -0500
code
Thanks for the inputs ! I don't want to use materialized views as I need to refresh this using scheduler. However, I need to extract the data and store temporarily for a denodo a view during query execution(runtime) for the inputs user enters. Please note that, view data I'm storing temoparily is being re-used multiple times in an implementation. This helps for better query execution. Can I design my code this way ? What is the recommendattion from Denodo here ? Hope I have stated the requirement clearly.. Kindly help me with your suggestions.. Regards Nagaraja
user
24-01-2023 21:35:18 -0500
Hi, You could create a *remote table* which will save the results of a query into a table. This may benefit you because you do not need another tool to execute the query in Denodo and store the result on the database. To use this feature, you need to have *Data Movement* enabled. There are three ways to create a remote table: * Using the wizard of the administration tool * Using the CREATE-REMOTE_TABLE stored procedure * Using the CREATE REMOTE TABLE command (this will not create an associated base view like the other 2 options) To create a remote table in the **Virtual DataPort Administration Tool**, right click the JDBC source where you want the remote table and click ‘Create Remote Table’. Then you are going to configure the remote table with your desired specifications and click create. You can refer to [Remote Tables](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/remote_tables/remote_tables) and [Managing Remote Tables](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/remote_tables/managing_remote_tables/managing_remote_tables#managing-remote-tables) in the **Virtual DataPort Administration Guide** for more information. Hope this helps!
Denodo Team
01-02-2023 20:19:02 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here