Creating Tables in Data Warehouse from CloudConnect

Before you load data into staging tables, create the tables for staging and production.

In this example, staging tables with the “in_” prefix are created for a dataset called opportunities.

Steps:

1. In the graph, add a DBExecute component.

2. Edit the component: 

   • DB Connection: select the connection that you created

   • SQL query: create the staging tables. The following sample creates the table for in_opportunities. Note the use of the in_ prefix for the staging environment:

     CREATE TABLE IF NOT EXISTS in_opportunities (  _oid IDENTITY PRIMARY 
     KEY,  id VARCHAR(32),  name VARCHAR(255) NOT NULL,  created TIMESTAMP 
     NOT NULL,  closed TIMESTAMP,  stage VARCHAR(32) NOT NULL,  is_closed 
     BOOLEAN NOT NULL,  is_won BOOLEAN NOT NULL,  amount DECIMAL(20,2),  
     last_modified TIMESTAMP)
     

   • Print statements: true

   • Transaction set: All statements

   Your DBExecute component looks like the following: