SQL analysis

Data analysis

1. Log on to the DataAnalysis console. In the top navigation bar, choose Analysis and click the SQL Analysis tab.

2. Click New SQL Analysis. In the New SQL Analysis dialog box, enter a name and description.

3. Click Confirm.

   SQL Data Analysis WorkbenchDevice Data SourceData TableExternal Data SourceBusiness Model Data SourceOn the page, under is selected by default. You can change this to or .

   Note

   The images in this topic use the default Light theme. IoT Data Analytics lets you set the theme for the workbench. To change the theme, click the Settings icon in the upper-right corner of the workbench and select Dark Theme.

   You can also click Thing Tags, select a target product, and then perform an SQL analysis on its tags. The following procedure describes the SQL analysis process, using a data table as an example.

4. In the SQL Data Analysis workbench, write your SQL statement.

   To obtain the table information for your SQL statement, find the table in the corresponding table folder. Hover over the target data table and click the Copy Table Name or Preview Data Table icon on the right.

   For more information about system data tables and external data tables, see How to use data tables.

   To query data from a table, select the target table. In the column information table in the lower-left corner, select the fields you want to query. Click Generate Query SQL at the bottom. The corresponding SQL statement then appears in the SQL editor on the right.

   The SQL Data Analysis workbench provides multiple SQL statement templates. To view or import a template, click Import Template in the upper-right corner of the SQL editor, as shown in the following figure.

5. After you write the SQL statement, you can perform the following operations on the statement as needed.

   Operation	Description
   Save	Saves the SQL statement. The save operation does not check for syntax errors and directly saves the content.
   Run	Runs the SQL statement. Note To ensure the statement runs successfully, perform a syntax check before you run it. If the SQL syntax is correct and a query result exists, the result is displayed in the Result box. The field names are displayed in the first row. If the SQL syntax is incorrect, the system reports that the execution failed.
   Stop	Cancels the running SQL statement. This button becomes available after you run an SQL statement.
   Scheduled Task	Executes the SQL statement at a scheduled time to run an offline task. Click the Scheduled Task icon and turn on the scheduling switch in the pop-up window. Effective Date: The date range during which the scheduled task is active. Scheduling Cycle: The cycle for the scheduled task. You can select Minute, Hour, Week, or Month. Specified Time: The time to run the scheduled task. Specific Time: The exact time of day to run the task.
   SQL Check	Checks the syntax of the SQL statement.

   After you run the SQL statement:

   • You can view the execution details of the SQL statement on the Operational Log page.

     The following code block shows a sample operational log and describes each line.

     2019-04-30 17:59:23 -- startPoint:[]   # Runtime parameter information
     2019-04-30 17:59:23 -- updatePipelineToActive:success   # Pipeline activation status
     2019-04-30 17:59:23 -- checkWorkFlow:workflow is INTERACTIVE   # Check the WorkFlow type
     2019-04-30 17:59:23 -- checkTransformer:transformer is 99d*****724b1a5   # The TransformerUid that is executed
     2019-04-30 17:59:23 -- dispatchToTaskExecutor:success   # Obtain the executor for the computing platform
     2019-04-30 17:59:23 -- addMaxLimit:success   # Add paging limits
     2019-04-30 17:59:23 -- callType:sync   # Synchronous/Asynchronous execution
     2019-04-30 17:59:25 -- updateWorkflowToStandBy:success   # Update the WorkFlow status after success

   • If the statement is executed successfully, you can view the results on the Results page. The Results box displays up to 200 results. To download the first 1,000 results for the task, click CSV Download on the right.

   • Operation LogIf the execution fails, you can view the error message on the page.

     The following code block shows a sample error log and describes each line.

     2019--04-30 15:31:33 -- startPoint:[]   # Runtime parameter information
     2019-04-30 15:31:33 -- updatePipelineToActive:success   # Pipeline activation status
     2019-04-30 15:31:33 -- checkWorkFlow:workflow is INTERACTIVE   # Check the WorkFlow type
     2019-04-30 15:31:33 -- checkTransformer:transformer is 426*****5286502   # The TransformerUid that is executed
     2019-04-30 15:31:33 -- dispatchToTaskExecutor:success   # Obtain the executor for the computing platform
     2019-04-30 15:31:33 -- addMaxLimit:success   # Add paging limits
     2019-04-30 15:31:33 -- callType:sync   # Synchronous/Asynchronous execution
     2019-04-30 15:31:33 -- taskExecuted:engine error, traceId 644*****03d05dd   # An execution error is reported and the traceId for the call is returned
     2019-04-30 15:31:33 -- taskExecuted:INSERT statement has mismatched column types   # The specific cause of the error

Generate a data API

A data API is a data service. You can call the data API using a software development kit (SDK) to embed the data in other applications for visualization. For more information, see How to use DataService Studio.

1. Perform SQL data analysis in the SQL Data Analysis workbench. For more information, see Data analysis.

2. In the upper-right corner of the page, click the Configuration icon, as shown in the following figure.

3. In the pane that appears on the right, configure the parameters and click Apply.

   Parameter	Description
   Request Parameters	The request parameters that you set during SQL analysis. You can customize the configurations.
   Response Parameters	All response parameters are displayed in the Result box at the bottom of the SQL Data Analysis workbench. You can customize the configurations.
   Advanced Settings	Expand this section: Select the Enable Paging for Response check box to display a large volume of data in pages. Clear the Enable Paging for Response check box to return a maximum of 2,000 records by default.

4. Click the Test API icon in the upper-right corner of the SQL Data Analysis workbench. On the Test Data Service page, set the request parameter values and click Start Test

5. After the test is successful, click Close in the lower-right corner to return to the SQL Data Analysis workbench page.

6. Click the Publish button in the upper-right corner of the page and click OK.

   After the data API is generated, click the View button to view the API details on the Data Service Details page.

   You can also click Revoke Publication to edit the SQL statement again.

   On the SQL Analysis tab, the status of the SQL analysis changes to Published.