Load the Demo Data to Your Own Database

How to integrate the demo data into a different database?

The GoodData.CN Community edition image contains the sample data in a PostgreSQL database. If you want to use a different (supported) database, you can load the sample data to this database and continue with the tutorials normally.

  • The demo data is provided as CSV files.
  • The DDL is PostgreSQL-specific, but you can update it as you see fit.

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.CN processes the rows and columns.

Steps:

  1. Download the archived file with the CSV files from https://gooddata-cloud-native-demo-workspace.s3.amazonaws.com/1.0/demo/demo.zip.
  2. Unzip the downloaded file.
  3. Review the sample data for the following datasets:
    • campaigns
    • customers
    • products
    • campaign_channels
    • order_lines

Create a Database Model

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

drop table if exists products;
create table products (
    product_id integer,
    product_name varchar(128),
    category varchar(128),
    constraint pk_products primary key (product_id)
);

drop table if exists campaigns;
create table campaigns (
    campaign_id integer,
    campaign_name varchar(128),
    constraint pk_campaigns primary key (campaign_id)
);

drop table if exists customers;
create table customers (
    customer_id integer,
    customer_name varchar(128),
    state varchar(3),
    region varchar(128),
    constraint pk_customers primary key (customer_id)
);

drop table if exists campaign_channels;
create table campaign_channels (
    campaign_channel_id varchar(128),
    category varchar(128),
    type varchar(128),
    budget decimal(15,2),
    spend decimal(15,2),
    campaign_id integer,
    constraint pk_campaign_channels primary key (campaign_channel_id),
    constraint fk_campaign foreign key (campaign_id) references campaigns (campaign_id)
);

drop table if exists order_lines;
create table order_lines (
    order_line_id varchar(128),
    order_id varchar(128),
    order_status varchar(128),
    date date,
    campaign_id integer,
    customer_id integer,
    product_id integer,
    price decimal(15,2),
    quantity decimal(15,2),
    constraint pk_order_lines primary key (order_line_id),
    constraint fk_campaign foreign key (campaign_id) references campaigns (campaign_id),
    constraint fk_customer foreign key (customer_id) references customers (customer_id),
    constraint fk_product foreign key (product_id) references products (product_id)
);

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):

Bash
PowerShell 7
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);"