Perform Serverless Query and Export Visualized BI Reports on Alibaba Cloud

Test Procedure (OSS + DLA + Quick BI)

The overall process of data processing, including data storage, interactive query and analysis, and BI report export, is tested by using OSS, DLA, and Quick BI. This test applies to scenarios such as queries and analysis on logs and transaction records and report export.

Enable Services

OSS:

Procedure

Prepare Data

Log on to the OSS console and create a directory.

Create Directories

We recommend that you create the workshop_sh/trade and workshop_sh/user directories.

Download Simulation Data Package

http://testdatasample.oss-cn-hangzhou.aliyuncs.com/workshop_sh/workshop_sh.zip

Log on to the DLA console

Click here and enter your username and password to log on to the DLA console.

Create Schema and Tables

Create a Schema

Enter the following CREATE SCHEMA statements and click “Synchronous Execution.”

CREATE SCHEMA my_schema_name WITH DBPROPERTIES (
CATALOG = 'oss',
LOCATION = 'oss://bucket name/test data directory/'
);
  1. Your OSS LOCATION directory must be ended with “/” to indicate that it is a directory. For example, LOCATION=’oss://workshopsh20180608100/workshop_sh/’. All the OSS files of the tables you created within this schema must be under this OSS directory or its sub directories.
  2. Database schema names must be unique in each of your Alibaba Cloud regions. Replace “my_schema_name” with your specified schema name in the test. Change your schema name if the system prompts that it already exists.

Create Tables

In the “Database” drop-down list, select the created schema.

CREATE EXTERNAL TABLE tradelist_csv (
t_userid STRING COMMENT 'user ID',
t_dealdate STRING COMMENT 'application time',
t_businflag STRING COMMENT 'service 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'network 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 fee',
t_backfare DOUBLE COMMENT'later payment fee',
t_otherfare1 DOUBLE COMMENT'other fees 1',
t_remark STRING COMMENT'remarks'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFIlE
LOCATION 'oss://testdatasample/workshop_sh/trade/';
CREATE EXTERNAL TABLE userinfo (
u_userid STRING COMMENT 'user ID',
u_accountdate STRING COMMENT 'account activation date',
u_gender STRING COMMENT 'gender',
u_age INT COMMENT 'age',
u_risk_tolerance INT COMMENT 'risk tolerance level within the 1–10 range (10: highest level)',
u_city STRING COMMENT'city',
u_job STRING COMMENT'job category (A to K)',
u_income DOUBLE COMMENT'annual income (CNY10,000)'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFIlE
LOCATION 'oss://testdatasample/workshop_sh/user/';

SQL Query (Synchronous Execution)

  1. Query 100 transaction records of the agency SXS_0010 generated on June 3 and 4, 2018.
  • SELECT * FROM tradelist_csv WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' limit 100;
  1. Execution result:
  1. Query the total balances in accounts of male and female users in different cities (multi-table JOIN query).
  • 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;

SQL Query (Asynchronous Execution)

Export the asynchronous execution result to OSS in the CSV format.

Make a Data Report Using Quick BI

Create a Data Source

Only Quick BI Professional supports connections to DLA data sources.

Create a Dataset

Click the “Create Dataset” icon next to a table.

Create a Dashboard

Display the dashboard editing page and select a dashboard display mode.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com