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.
- You need to start the postgres docker image.
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:
- the table store_sales from the tpcds system. This is the fact table of the tpcds store schema.
- with all its dependencies (all the foreign tables)
- into the Postgres Howto connection with the prefix tpc_ using the logical name of the source
- with the tabul data create command.
# 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.