Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Write to Remote Tables in Denodo

HenrietteH
Alteryx
Alteryx
Created

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

 

  • Alteryx Designer Version 23.1 or newer
  • Denodo Version 8 or newer
  • Denodo ODBC Driver, which can be downloaded here
  • Permissions to create materialized tables in Denodo and tables in the external database the external table should live in

 

Procedure

 

  1. In Alteryx, create a workflow preparing the data to be loaded to the external table.
  2. Map an Output Data tool to your Denodo instance and write out a table containing your data. In our example, we are creating a table called "remote_table_community example". This table is a Materialized table which is a pre-requisite to using the stored procedure described in Step #3.
  3. Using a second Output Data tool, execute the following stored procedure in the pre-SQL statement. Denodo provides this stored procedure to allow users to create a Remote table and a corresponding Base view at the same time.

 

 

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.

 

 

Denodo remote tables.png

 

  

Attachments