Lesson 1.4: Creating tables and loading sample data

This tutorial shows you how to use predefined scripts to create tables and load data. As you run queries and create reports about the Sample Outdoor Company, you will gain familiarity with the tools and product features that are used in this tutorial.

1. Open the JSqsh client, specifying the bigsql configuration name, and provide the password if prompted:

   /usr/ibmpacks/common-utils/current/jsqsh/bin/jsqsh bigsql

2. (Optional) Skip this step if you have never created any tables in the GOSALESDW schema. Otherwise, from the JSqsh client, run the following script to drop these tables:

   \eval $BIGSQL_HOME/samples/queries/GOSALESDW_drop.sql

   The \eval JSqsh command reads and executes an input file that contains SQL statements.
3. Create new tables by running the GOSALESDW_ddl.sql script:

   \eval $BIGSQL_HOME/samples/queries/GOSALESDW_ddl.sql

   The GOSALESDW_ddl.sql file contains SQL statements to create the tables. The first line of this script (use gosalesdw;) references the USE command, which establishes a default schema (GOSALESDW) for the session. A Db2 Big SQL schema is a way to logically group objects, such as tables or functions, and becomes part of the fully qualified table name; for example, GOSALESDW.DIST_INVENTORY_FACT.
4. Retrieve information about the tables that you created on the Db2 Big SQL server:

   \show tables

5. Load the tables with data by running the GOSALESDW_load.sql script:

   \eval $BIGSQL_HOME/samples/queries/GOSALESDW_load.sql

   Tip: To conserve space in your distributed file system, you can delete the data folder from its download location, /user/bigsql/bi_sample_data/data/:

   hdfs dfs -rm -r /user/bigsql/bi_sample_data/data/

   For more information about the LOAD HADOOP statement, see LOAD HADOOP statement.
6. Retrieve some data:

   
   SELECT * FROM gosalesdw.go_region_dim FETCH FIRST 5 ROWS ONLY;
   SELECT * FROM gosalesdw.sls_sales_fact FETCH FIRST 5 ROWS ONLY;