This topic describes how to connect DataV to DataWorks DataService Studio and then call APIs of DataService Studio to obtain data from MaxCompute. DataV can display the data analysis results on dashboards.
MaxCompute is a fast and fully managed computing platform for large-scale data warehousing. It can process terabytes, petabytes, or even exabytes of data. As a wide variety of data collection methods emerge and huge amounts of data is accumulated, traditional software cannot meet data processing requirements. Being able to store and compute mass structured data, MaxCompute has been providing stable support for all Alibaba's offline data analytics businesses for many years.
In the past, if you want to present the analysis results of large amounts of data in DataV, you need to create a workflow to import data in your system to the MySQL database. This process involves complex operations and requires high costs. DataWorks provides the Data Integration, DataStudio, and DataService Studio services, which, together with MaxCompute, enable you to quickly build data warehouses for enterprises.
DataWorks DataService Studio allows you to use the codeless user interface (UI) to create APIs based on data tables without the need to write code. Then, you can call the APIs in DataV to present data analysis results on dashboards. In this way, you can develop a data warehouse and present data in an efficient manner.
A data store is prepared, and DataV is activated. For more information, see What is DataV?
Create a connection
- Relational databases: ApsaraDB for RDS, Distributed Relational Database Service (DRDS), MySQL, PostgreSQL, Oracle, and SQL Server
- Analytic databases: AnalyticDB
- NoSQL databases: Tablestore and MongoDB
- Log on to the DataWorks console. In the left-side navigation pane, click Workspaces. On the Workspaces page, find the target workspace and click DataService Studio in the Actions column.
- On the DataService Studio page that appears, click the Service Development tab, move the pointer over , and then select New Data Source. The page appears.
- Click Add a Connection in the upper-right corner.
- In the Add Connection dialog box that appears, click PostgreSQL in the Relational Database section.
This topic uses the connection to PostgreSQL as an example, which allows you to query data in MaxCompute in real time.
- In the Add PostgreSQL Connection dialog box that appears, set the required parameters.
Parameter Description Connect To The type of the connection. Select. Connection Name The name of the connection. The name can contain letters, digits, and underscores (_) and must start with a letter. Description The description of the connection. The description can be up to 80 characters in length. Environment The environment in which the connection is used. Select Development or Production.Note This parameter is available only when the workspace is in the standard mode. JDBC URL The Java Database Connectivity (JDBC) URL of the PostgreSQL database. Enter a value in the
jdbc:postgresql://ServerIP:Port/Databaseformat. For more information, see Access domain name.
Username The username for connecting to the database. Password The password for connecting to the database.
- Click Test Connection.
- After the PostgreSQL database passes the connectivity test, click Complete.
Create an API
- Click the DataWorks icon in the upper-left corner and choose DataService Studio page appears. . The
- Click the Service Development tab, move the pointer over , and then choose .
- In the Generate API dialog box that appears, set the required parameters.
Parameter Description API Name The name of the API. The name must be 4 to 50 characters in length, and can contain letters, digits, and underscores (_). It must start with a letter. API Group The group to which the API belongs. An API group is a collection of APIs for a specific feature or scenario. It is also the minimum API management unit of API Gateway. Each API group corresponds to a specific API product in Alibaba Cloud API Marketplace.
To create an API group, move the pointer over the Create icon on the Service Development tab and select New API Group.
API Path The path for storing the API, such as /user. Protocol The protocol used by the API. Valid values: HTTP and HTTPS. Request Method The request method used by the API. Valid values: GET and POST. Response Content Type The return type of the API. Currently, the value is fixed to JSON. Description The description of the API.
- Click OK.
Set the API parameters
- In the Select Table section, set Datasource Type, Datasource Name, and Table Name.
- Before you set API parameters, you must configure a connection in Data Integration. You can enter a table name in the Table Name field to search for the desired table.
- After you create the API, the table configuration tab of the API automatically appears, where you can directly configure a table.
- In the Environment Configuration section, set Memory and Function Timeout.
- After you select a table in the Select Table section, all fields in the table appear in the Select Parameters section. Select the fields that need to be set as request parameters and the fields that need to be set as response parameters. Add them to the request parameter list and the response parameter list, respectively.
- Edit request parameters.
In the right-side navigation pane, click the Request Parameters tab. In the Request Parameters pane that appears, set Parameter Name, Parameter Type, Operator, Required, Example Value, Default Value, and Description.
- Edit response parameters.
In the right-side navigation pane, click the Response Parameters tab. In the Response Parameters pane that appears, set Parameter Name, Parameter Type, Example Value, and Description. You can also select Pagination and Filter in the Advanced Settings section.Select Pagination based on the actual requirements.
- If you do not select Pagination, the API returns a maximum of 2,000 records by default.
- If the API may return more than 2,000 records, we recommend that you select Pagination.
Test the API
Set the parameters and click Test to send an API request. The request and response details appear on the right. If the API fails the test, check the error message, modify the API settings accordingly, and test the API again.
Publish the API
- After the API passes the test, return to the DataService Studio page.
- Click Publish in the upper-right corner. The API is published.
- Click Service Management in the upper-right corner to view the API details.
If you want to call the API, click Service Management. On the page that appears, click Call APIs in the left-side navigation pane. DataService Studio supports quick authentication based on the AppCode and encrypted signature authentication based on the AppKey and AppSecret. The following section describes how to call DataService Studio APIs in DataV.
Add DataService Studio as a data source
- Log on to the DataV console.
- On the homepage, click Data Sources and then Add Source.
- In the Add Data Source dialog box that appears, set the required parameters.
Parameter Description Type The type of the data source. Name The name of the data source. Enter a custom name. Project The DataWorks workspace where the required data comes from. AppKey/AppSecret The AppKey and AppSecret of the account that has the permission to access a DataWorks workspace.Note To obtain the AppKey and AppSecret, log on to the DataWorks console, go to the DataService Studio page, and then click Call APIs in the left-side navigation pane.in the upper-right corner. On the page that appears, click
Call an API of DataService Studio in DataV
- Log on to the DataV console. In the top navigation bar, click Projects. On the page that appears, click Create Project.
- On the page that appears, select a template and click Create Project. In this example, select the Smart Factory template.
Widgets in the template contain static data. This example describes how to call the API to query the growth trend of the transaction amount and present the query result in the widget Basic Line Chart.
- Select the widget Basic Line Chart and go to the data panel. In the data panel, set Data Source Type to DataWorks.
- Select the added data source and created API and set the query parameters. In this example, set Page Size to 31 to query data of one month.
- Click Preview Data Response to view the query result of the API.
- Set the x field to date and the y field to amount.
The preceding figure shows that the x and y fields match no data. This is because DataV has requirements on the data format and cannot identify fields with a deep structure. You need to add a data filter to filter out unnecessary fields.
In this example, enter the code
return data.data.rows;to filter out fields except the rows array returned by the API. Click Preview to preview the filtered data below, and click Complete.
After you add a data filter, DataV can match data based on the specified fields.
The date format returned by the API is different from the default date format of the widget. Therefore, DataV does not correctly display the line chart. You must set the date format for the X axis of the line chart before DataV can correctly display the line chart.
- Go to the configuration panel. Click Axis Label under . Set Data Type to Date and Data Format to a value in the YYYY/MM/DD format, for example, 2016/01/01. Then, DataV displays the line chart correctly.
After the preceding steps are completed, DataV can call the API created in DataService Studio based on a MaxCompute table to obtain and display the data analysis results.
After you connect DataV to DataWorks DataService Studio by adding DataService Studio as a data source of DataV, you can directly call an API of DataService Studio in DataV without the need to enter the API URL. You do not need to enter the AppKey and AppSecret when calling each API. In addition, you can set API parameters by using a table, which is convenient, secure, and reliable.
DataService Studio allows you to directly present the data analysis results processed by MaxCompute in DataV. It seamlessly links data analytics and data presentation.
- APIs created on the codeless UI in DataWorks DataService Studio can only query data in a single table based on simple criteria. APIs created in the code editor allow you to use SQL statements or functions to query data in multiple tables based on complex criteria. You can create APIs by using the codeless UI or code editor based on your needs.
- If you want an API to query data within milliseconds, we recommend that you use a relational database, a NoSQL database, or an AnalyticDB database as the data store.
- DataV widgets only support arrays, but DataService Studio APIs return complete JSON strings that contain error codes. Therefore, you must use filters to process results returned by the APIs. You can add filters in DataV or when you configure APIs in DataService Studio.
If pagination is disabled for an API, add a filter to filter out fields except the data array returned by the API. If pagination is enabled for an API, add a filter to filter out fields except the data.rows array returned by the API.
- If you want to present a data analysis result in multiple types, for example, in both a line chart and a column chart, DataV treats the data of each type as an object and distinguishes the types by field. In this case, you need to use a filter for format conversion.