Table of Contents

About

This Howto will show you how to copy the data definition of all SQL tables from a star/snowflake schema by using the Tabul data create command.

This command does not copy the data. It just creates the structure (ie it performs a SQL CREATE statement). If you want to copy the data, use the tabul data transfer command instead.

Steps

Start Postgres

The next steps will be using the postgres howto connection. 

tabul service start postgres

1 service was started
Name       Type
--------   ------
postgres   docker
  • Ping your connection to check that the database is up.
tabul connection ping postgres

The connection (postgres) has been pinged successfully

Select the fact table

The trick to copy a star/snowflake schema is:

  • to give the fact table (ie the table at the center of the star/snowflake schema) to the Tabul table create command
  • and to ask to create it with its dependencies.

Create the fact table with its dependencies

The below command will create: 

# in bash, you need to escape the $ sign with \ so that it's not seen as a bash variable
tabul data create --with-dependencies store_sales@tpcds tpc_\${input_logicalName}@postgres

Results of the create operation
input                          target
----------------------------   -----------------------------------
date_dim@tpcds                 tpc_date_dim@postgres
household_demographics@tpcds   tpc_household_demographics@postgres
item@tpcds                     tpc_item@postgres
income_band@tpcds              tpc_income_band@postgres
customer_demographics@tpcds    tpc_customer_demographics@postgres
customer_address@tpcds         tpc_customer_address@postgres
store_sales@tpcds              tpc_store_sales@postgres
customer@tpcds                 tpc_customer@postgres
promotion@tpcds                tpc_promotion@postgres
time_dim@tpcds                 tpc_time_dim@postgres
store@tpcds                    tpc_store@postgres

List the created tables

With the tabul data list command, we can list the tables created with the tpc prefix. 

tabul data list --strict-selection  -a count tpc*@postgres

path                         media_type   count
--------------------------   ----------   -----
tpc_customer                 table            0
tpc_customer_address         table            0
tpc_customer_demographics    table            0
tpc_date_dim                 table            0
tpc_household_demographics   table            0
tpc_income_band              table            0
tpc_item                     table            0
tpc_promotion                table            0
tpc_store                    table            0
tpc_store_sales              table            0
tpc_time_dim                 table            0

Next

You can fill them with generated data.

How to fill a schema with generated data