Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Perform Serverless Query and Export Visualized BI Reports on Alibaba Cloud

Perform Serverless Query and Export Visualized BI Reports on Alibaba Cloud

This article shows you how to use OSS, DLA, and Quick BI to perform data query and analysis, and export visualized BI reports based on OSS.

Multiple methods are available for querying and analyzing data stored in Alibaba Cloud Object Storage Service (OSS). For example, you can use Alibaba Cloud serverless query and analysis services such as the big data computing service MaxCompute and Data Lake Analytics (DLA). You can also create Spark, Presto, or Impala applications to analyze data on OSS.

1

This document describes a test for using OSS, DLA, and Quick BI to implement data storage, serverless ad hoc queries, and visualized BI reports.

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.

2

Enable Services

OSS:

Go to the Object Storage Service page and click Buy Now.

DLA:

Apply for a free trial subscription to DLA.

Note: DLA is in open beta testing (OBT) now. To use DLA and its console, you must apply for the OBT qualification first. DLA can be used for query and analysis execution against specified OSS data files, including CSV, JSON, Parquet, ORC, Avro, and RCFile files.

Procedure

Prepare Data

Log on to the OSS console and create a directory.

3

Create an OSS test bucket. In the Region drop-down list, select your preferred region. We will be using China East 1 in this example.

Note: The bucket name must be unique among all existing buckets in Alibaba Cloud OSS. Change your bucket name if the system prompts that it already exists.

Create Directories

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

4

5

Download Simulation Data Package

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

Upload the transaction record and account information files to the trade and user directories, respectively

6

7

Log on to the DLA console

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

8

Check your username and password in the DLA service activation notification email in your http://www.alibabacloud.com website mailbox.

9

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/'
  );

Note:

  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.

Enter the following CREATE TABLE statements and click "Synchronous Execution." The LOCATION directory is oss://bucket name/test data directory/.

Create a transaction record table.

Note: The LOCATION directory is in the 'oss://bucket name/transaction record table directory/' format.

Replace the LOCATION directory with 'oss://your OSS bucket name/workshop_sh/user/' in the test.

For example, enter oss://workshopsh20180608100/workshop_sh/user/.

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 an account information table.

Note: The LOCATION directory is in the 'oss://bucket name/account information table directory/' format.

Replace the LOCATION directory with 'oss://your OSS bucket name/workshop_sh/user/' in the test.

For example, enter oss://workshopsh20180608100/workshop_sh/user/.

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/';

After creating the tables, refresh the page. The two tables are displayed under the schema created for the test.

10

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;

    Execution result:

    11

  2. 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;

    12

SQL Query (Asynchronous Execution)

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

13

Click Execution Status and check the asynchronous query task status

The execution status can be "RUNNING", "SUCCESS", or "FAILURE."

Click "Update." When "SUCCESS" is displayed in "STATUS", the OSS directory for storing the query result is displayed.

14

View the query result file in OSS

15

Make a Data Report Using Quick BI

Create a Data Source

Only Quick BI Professional supports connections to DLA data sources.

You can check the values of "Database Endpoint" and "Port" on the DLA console.

16

Set "Database" to the schema name you have created in DLA.

"Access Id" and "Access Key" are the username and password for DLA database logon, respectively. (For more information, check the DLA service activation notification email in your http://www.alibabacloud.com website mailbox.)

17

Create a Dataset

Click the "Create Dataset" icon next to a table.

To select a data source, click "Customize SQL."

18

Enter SQL query statements and save the output (for example, in a file named "citygender").

19

20

Create a Dashboard

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

Select a dataset (for example, the "citygender" dataset).

Set "Value Axis (Measures)", "Category Axis (Dimensions)", and "Colors (Dimensions)."

Click "Update."

The following figure shows the total balances in accounts of male and female users in different cities.

21

To learn more about Alibaba Cloud Object Storage Service, www.alibabacloud.com/product/oss

Storage MaxCompute Big Data Data Analytics Business Intelligence Serverless Data Lake Analytics
1 1 1
Share on

Read previous post:

Table Store Time Series Data Storage Architecture

Read next post:

Real-Time Database Backup to OSS on the OSS Console

Alibaba Cloud Storage

57 posts | 12 followers

You may also like

Comments

Raja_KT February 18, 2019 at 5:30 am

Interesting you shared sample data besides telling the beauty of serverless DLA on OSS... QuickBI. Connecting to DataV too?

Alibaba Cloud Storage

57 posts | 12 followers

Related Products

More Posts by Alibaba Cloud Storage

See All