All Products
Search
Document Center

Procedure

Last Updated: May 22, 2019

Step 1: Create an OSS schema and create tables in the schema

Use the OSS table creation wizard or write SQL statements to create an OSS schema and tables.

In this example, use the following statement to create an OSS schema:

  1. CREATE SCHEMA my_schema_name WITH DBPROPERTIES (
  2. CATALOG = 'oss',
  3. LOCATION = 'oss://dlaossfile1/workshop_sh/'
  4. );

Create a transaction record table:

  1. CREATE EXTERNAL TABLE tradelist_csv (
  2. t_userid STRING COMMENT 'user ID',
  3. t_dealdate STRING COMMENT 'application time',
  4. t_businflag STRING COMMENT 'business code',
  5. t_cdate STRING COMMENT 'confirmation date',
  6. t_date STRING COMMENT 'application date',
  7. t_serialno STRING COMMENT'application No.',
  8. t_agencyno STRING COMMENT'agency No.',
  9. t_netno STRING COMMENT'network No.',
  10. t_fundacco STRING COMMENT'fund account',
  11. t_tradeacco STRING COMMENT'transaction account',
  12. t_fundcode STRING COMMENT'fund code',
  13. t_sharetype STRING COMMENT'share type',
  14. t_confirmbalance DOUBLE COMMENT'confirmed balance',
  15. t_tradefare DOUBLE COMMENT'transaction fee',
  16. t_backfare DOUBLE COMMENT'later payment fee',
  17. t_otherfare1 DOUBLE COMMENT'other fees 1',
  18. t_remark STRING COMMENT'remarks'
  19. )
  20. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  21. STORED AS TEXTFILE
  22. LOCATION 'oss://dlaossfile1/workshop_sh/trade/';

Create an account information table:

  1. CREATE EXTERNAL TABLE userinfo (
  2. u_userid STRING COMMENT 'user ID',
  3. u_accountdate STRING COMMENT 'account opening date',
  4. u_gender STRING COMMENT 'gender',
  5. u_age INT COMMENT 'age',
  6. u_risk_tolerance INT COMMENT 'risk tolerance level within the 1-10 range (10: highest level)',
  7. u_city STRING COMMENT'city',
  8. u_job STRING COMMENT'job category (A to K)',
  9. u_income DOUBLE COMMENT'annual income (CNY 10,000)'
  10. )
  11. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  12. STORED AS TEXTFILE
  13. LOCATION 'oss://dlaossfile1/workshop_sh/user/';

After the preceding tables are created, you can execute SQL statements to test whether the data in the tables is correct. For example, execute the following statement to query 100 transaction records of the agency SXS_0010 that were generated on June 3 and 4, 2018.

  1. SELECT * FROM tradelist_csv
  2. WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010'
  3. limit 100;

1

Step 2: Add the DLA data source

Note: Before you add the data source, add CIDR blocks 10.152.69.0/24,10.152.163.0/24,139.224.4.0/24 to the DLA whitelist. Otherwise, Quick BI cannot connect to DLA and the data source cannot be added.

2

Step 3: Create a dataset

For example, execute the following statement to query the total amounts of funds that are bought by male and female investors in different cities (multi-table JOIN query). Then, save the query result in dataset citygender. Select Custom SQL as the data source:

  1. SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance
  2. FROM tradelist_csv , userinfo
  3. where u_userid = t_userid
  4. GROUP BY u_city, u_gender
  5. ORDER BY sum_balance DESC;

3

Step 4: Create a dashboard

Set Dataset to citygender, set Value Axis (Measures), Category Axis (Dimensions), and Color Legend (Dimension), and then click Update to display the total amounts of funds that are bought by male and female investors in different cities.

4

You can create other datasets and dashboards as required in Quick BI.