05-19-2023 09:00 AM
How To: Write to Remote Tables in Denodo
Denodo differentiates between different types of tables. Remote tables are those stored in an external database and they require a two-step process to be created.
The write functionality in the Alteryx Output Data tool currently creates Materialized tables, which are a special type of base view whose data is stored in the database where the data is cached, instead of in an external data source.
This article will walk through the process of creating remote tables in Denodo using the Alteryx Output Data tool.
Prerequisites
Procedure
CREATE_REMOTE_TABLE(
remote_table_name : text
, replace_remote_table_if_exist : boolean
, query : text
, datasource_database_name : text
, datasource_name : text
, datasource_catalog : text
, datasource_schema : text
, base_view_database_name : text
, base_view_name : text
, base_view_folder : text
, replace_base_view_if_exist: boolean
, options : text
)
In our example below, we are converting the table written out in Step #2 to a base view.
See the Denodo documentation on the CREATE_REMOTE_TABLE stored procedure for more information.
4. In order to make the Output Data tool execute, it needs a table to write to. We're writing to a table called "delete_me" that we then drop in the post-SQL Statement.
5. Using a Block-Until-Done tool to make sure the first Output Data tool executes first.