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);"
8. Create Measures Using the APIs