Load the Demo Data to a Different Database

The GoodData.CN Container Edition image contains sample data in a PostgreSQL database. If you want to use a different (supported) database, you can load the sample data to your database of choice and continue with the tutorials normally.

• The demo data is provided as CSV files.
• The DDL is PostgreSQL-specific, but you can update it for compatibility with your supported database of choice.

To load the sample data to a different database, do the following:

1. Download the demo data.
2. Create a database model.
3. Upload the sample data to your database.

Download the Demo Data

Download and review the sample CSV files to help you understand how GoodData processes rows and columns.

Steps:

1. Download the compressed CSV files from https://gooddata-cloud-native-demo-workspace.s3.amazonaws.com/1.1.1/demo/demo.zip.
2. Unzip the downloaded file.
3. Review the sample data in the following files:
   • demo_campaigns.csv
   • demo_customers.csv
   • demo_products.csv
   • demo_campaign_channels.csv
   • demo_order_lines.csv
   • demo_ddl.sql

Create the Database Model

To create the database model, execute the SQL script demo_ddl.sql in your database. If you are using a database other than PostgreSQL, update the script to make it work with your database.

Upload the Sample Data to Your Database

To upload the demo data to your database, run the following command for each dataset (campaigns, customers, products, campaign_channels, and order_lines):

psql -c "\COPY $dataset FROM 'demo_${dataset}.csv' (FORMAT csv, DELIMITER ',', QUOTE '\"', ESCAPE '\\', HEADER true);"

# Consider setting $env:PGUSER and $env:PGPASSWORD variables
# Set your own PostgreSQL version
$PGVERSION = "13"
& "c:\Program Files\PostgreSQL\$PGVERSION\bin\psql.exe" -c "\COPY $dataset FROM 'demo_$dataset.csv' (FORMAT csv, DELIMITER ',', QUOTE '\`"', ESCAPE '`\', HEADER true);"

(Optional) Update the Dates in the Sample Data

To see data when applying filters like “Last week”, update the dates in the sample data by using the following SQL script:

----------------------------
-- Shift the date so that it covers an actual year. This enables filters like "This month" to show data.
-- Date manipulation is not the same in all databases. For compatibility, this script shifts date values by 365 days.
----------------------------
update "order_lines" set "date" = "date" + cast(dateshift as int)
from (
  -- Calculate the difference between the max(date) in the data vs. the actual year.
  select (date_part('year', current_date) - date_part('year', max("date") over ())) * 365  as dateshift,
    "order_line_id" as "update_order_line"
  from "order_lines"
) d
where "update_order_line" = "order_line_id"
;