This topic describes how to use Object Storage Service (OSS), Data Lake Analytics (DLA), and Quick BI to store data, perform interactive query and analytics, and generate business intelligence (BI) reports.
Step 1: Create an OSS schema and tables
Create an OSS schema.
CREATE SCHEMA my_schema_name WITH DBPROPERTIES (
CATALOG = 'oss',
LOCATION = 'oss://bucket_name/workshop_sh/'
);
// Create a table that contains transaction records
CREATE EXTERNAL TABLE tradelist_csv (
t_userid STRING COMMENT 'User ID',
t_dealdate STRING COMMENT 'Application time',
t_businflag STRING COMMENT 'Business code',
t_cdate STRING COMMENT 'Confirmation date',
t_date STRING COMMENT 'Application date',
t_serialno STRING COMMENT'Application No.',
t_agencyno STRING COMMENT'Agency No.',
t_netno STRING COMMENT'Branch No.',
t_fundacco STRING COMMENT'Fund account',
t_tradeacco STRING COMMENT'Transaction account',
t_fundcode STRING COMMENT'Fund code',
t_sharetype STRING COMMENT'Share type',
t_confirmbalance DOUBLE COMMENT'Confirmed balance',
t_tradefare DOUBLE COMMENT'Transaction fees',
t_backfare DOUBLE COMMENT "Service fees",
t_otherfare1 DOUBLE COMMENT'Other fees 1',
t_remark STRING COMMENT'Remarks'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFIlE
LOCATION 'oss://bucket_name/workshop_sh/trade/';
// Create a table that contains user information.
CREATE EXTERNAL TABLE userinfo (
u_userid STRING COMMENT 'User ID',
u_accountdate STRING COMMENT 'Account creation date',
u_gender STRING COMMENT 'Gender',
u_age INT COMMENT 'Age',
u_risk_tolerance INT COMMENT 'Risk tolerance, 1-10, 10 indicates the highest level',
u_city STRING COMMENT'City',
u_job STRING COMMENT'Job category, A-K',
u_income DOUBLE COMMENT'Annual income (USD 10,000)'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFIlE
LOCATION 'oss://bucket_name/workshop_sh/user/';
SELECT * FROM tradelist_csv
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010'
limit 100;
Step 2: Add a DLA data source
Add a DLA data source to Quick BI. For more information, see Add a cloud data source DLA.
Parameter | Description |
---|---|
Name | Optional. The name of the DLA data source. We recommend that you choose an identifiable name, which facilitates subsequent management. |
Database Address | The endpoint of the DLA data source. For more information about how to obtain the endpoint, see Create an endpoint. |
Port Number | The port number in the endpoint of the DLA data source. |
Database | The name of the schema in the DLA data source. |
Username | The account that is used to access the DLA data source. For more information, see Account types. |
Password | The password that corresponds to the account. |
Step 3: Create a dataset
Create a dataset in Quick BI. For more information, see Create a dataset. In this step, you use custom SQL statements to create a dataset named citygender and execute the following statement to query the total amount of funds purchased by males and females in different cities by using JOIN operations. Sample statement:
SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance
FROM tradelist_csv , userinfo
where u_userid = t_userid
GROUP BY u_city, u_gender
ORDER BY sum_balance DESC;
Step 4: Create a dashboard
Perform dashboard operations in Quick BI. For more information, see Basic dashboard operations. In this step, you select the citygender dataset, specify Value Axis (Mea.), Category Axis (Dim.), and Color Legend (Dim.), and then click Update to view the total amount of funds purchased by males and females in different cities.
You can then create other datasets and dashboards in Quick BI.