Perform Serverless Query and Export Visualized BI Reports on Alibaba Cloud

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post
Image for post
Image for post

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

Image for post
Image for post
Image for post
Image for post

Log on to the DLA console

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

Image for post
Image for post

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

Image for post
Image for post

Create Schema and Tables

Create a Schema

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

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 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/.

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

Image for post
Image for post

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:
Image for post
Image for post
  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;
Image for post
Image for post

SQL Query (Asynchronous Execution)

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

Image for post
Image for post

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.

Image for post
Image for post

View the query result file in OSS

Image for post
Image for post

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.

Image for post
Image for post

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.)

Image for post
Image for post

Create a Dataset

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

To select a data source, click “Customize SQL.”

Image for post
Image for post

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

Image for post
Image for post
Image for post
Image for post

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.

Image for post
Image for post

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

Reference:https://www.alibabacloud.com/blog/perform-serverless-query-and-export-visualized-bi-reports-on-alibaba-cloud_594244?spm=a2c41.12386618.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

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