Tablestore:Connect a Tablestore instance to DataWorks

Go to the data analysis page.

1. Log on to the DataWorks console as a project administrator.

2. In the left-side navigation pane, choose DataAnalysis > SQL Query.

3. On the SQL Query page, select a region, select a workspace that you want to manage, and then click Go to SQL Query.

Create an SQL query file.

1. In the left-side navigation pane of the SQL Query page, move the pointer over the plus icon to the right of My Files and select Create File.

2. In the Create File dialog box, enter a file name and click OK.

   In the left-side navigation pane, you can view the created file.

Open the SQL editor for the created file and configure the information about the data source whose data you want to query.

1. In the left-side navigation pane of the SQL Query page, click My Files and click the created file. In the upper-right corner of the SQL editor that appears, click the icon.

2. In the dialog box that appears, configure the parameters. The following table describes the parameters.

   Parameter	Description
   Workspace	The workspace whose data you want to query. Select a workspace based on your business requirements.
   Data Source Type	The type of the data source whose data you want to query. Select Tablestore.
   Data Source Name	The name of the data source whose data you want to query. Select the data source that you added. The table that you want to query belongs to the instance that is specified for the data source. Important The Data Source Name drop-down list displays only the data sources that you are authorized to use. If you want to use other data sources, contact an administrator to grant the required permissions on the Security Center page. For more information, see Use the data query and analysis control feature.

3. Click OK.

Create a mapping table for a table and execute SQL statements.

If a mapping table is created for your table, you can skip this step.

1. In the SQL editor for the file, write an SQL statement to create a mapping table. For more information, see Create a mapping table for a table.

   When you create a mapping table for a table, make sure that the mapping table has the same name and primary key columns as the table.

   Note

   If a search index is created for a data table, you can create a mapping table for the search index. This way, you can execute SQL statements to query data based on the search index. For more information, see Create mapping tables for search indexes.

   The following sample SQL statement provides an example on how to create a mapping table for a table named test_table:

   CREATE TABLE `test_table` (
       `pk` VARCHAR(1024), 
       `long_value` BIGINT(20), 
       `double_value` DOUBLE, 
       `string_value` MEDIUMTEXT, 
       `bool_value` BOOL, 
       PRIMARY KEY(`pk`)
   );

2. Click the SQL statement and click Run in the toolbar.

   The execution result is displayed on the Result tab of the SQL editor.

Execute SQL statements to query data in the table.

1. In the SQL editor for the file, write a SELECT statement to query data. For more information, see Query data.

   The following sample SQL statement provides an example on how to query data in the test_table table and obtain up to 20 rows of data:

   SELECT `pk`, `long_value`, `double_value`, `string_value`, `bool_value` FROM test_table LIMIT 20;

2. Click the SQL statement and click Run in the toolbar.

   The execution result is displayed on the Result tab of the SQL editor.

Go to the data analysis page.

1. Log on to the DataWorks console as a project administrator.

2. In the left-side navigation pane, choose DataAnalysis > SQL Query.

3. On the SQL Query page, select a region, select a workspace that you want to manage, and then click Go to SQL Query.

Create an SQL query file.

1. In the left-side navigation pane of the SQL Query page, move the pointer over the plus icon to the right of My Files and select Create File.

2. In the Create File dialog box, enter a file name and click OK.

   In the left-side navigation pane, you can view the created file.

Open the SQL editor for the created file and configure the information about the data source whose data you want to query.

1. In the left-side navigation pane of the SQL Query page, click My Files and click the created file. In the upper-right corner of the SQL editor that appears, click the icon.

2. In the dialog box that appears, configure the parameters. The following table describes the parameters.

   Parameter	Description
   Workspace	The workspace whose data you want to query. Select a workspace based on your business requirements.
   Data Source Type	The type of the data source whose data you want to query. Select Tablestore.
   Data Source Name	The name of the data source whose data you want to query. Select the data source that you added. The table that you want to query belongs to the instance that is specified for the data source. Important The Data Source Name drop-down list displays only the data sources that you are authorized to use. If you want to use other data sources, contact an administrator to grant the required permissions on the Security Center page. For more information, see Use the data query and analysis control feature.

3. Click OK.

Create a mapping table for a table and execute SQL statements.

After you create a time series table, the system automatically creates a mapping table in the single-value model and a mapping table for time series metadata for the time series table. The name of the mapping table in the single-value model is the same as the name of the time series table. The name of the mapping table for time series metadata is the name of the time series table suffixed by ::meta.

If you want to use a mapping table in the multi-value model to query time series data in the time series table, you must create a mapping table in the multi-value model. If you do not want to use a mapping table in the multi-value model to query time series data, you do not need to create a mapping table in the multi-value model.

1. In the SQL editor for the file, write an SQL statement to create a mapping table. For more information, see Mapping tables for a time series table in SQL.

   The following sample SQL statement provides an example on how to create a mapping table in the multi-value model named timeseries_table::muti_model for the time series table. The metrics in the mapping table are cpu, memory, and disktop: Sample SQL statement:

   CREATE TABLE `timeseries_table::muti_model` (
     `_m_name` VARCHAR(1024), 
     `_data_source` VARCHAR(1024), 
     `_tags` VARCHAR(1024),
     `_time` BIGINT(20),
     `cpu` DOUBLE(10),
     `memory` DOUBLE(10),
     `disktop` DOUBLE(10),
     PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`)
   );

2. Click the SQL statement and click Run in the toolbar.

   The execution result is displayed on the Result tab of the SQL editor.

Execute SQL statements to query data in the table.

1. In the SQL editor for the file, write a SELECT statement to query data. For more information, see SQL examples.

   • Query data by using a mapping table in the single-value model

     The following sample SQL statement provides an example on how to query the data whose metric type is basic_metric in the time series data table:

     SELECT * FROM timeseries_table WHERE _m_name = "basic_metric" LIMIT 10;

   • Query data by using a mapping table for time series metadata

     The following sample SQL statement provides an example on how to query the time series whose metric name is basic_metric in the mapping table for time series metadata:

     SELECT * FROM  `timeseries_table::meta` WHERE _m_name = "basic_metric" LIMIT 100;

   • Query data by using a mapping table in the multi-value model

     The following sample SQL statement provides an example on how to query information about the metrics in the time series whose cpu value is greater than 20.0 by using the mapping table in the multi-value model:

     SELECT cpu,memory,disktop FROM `timeseries_table::muti_model` WHERE cpu > 20.0 LIMIT 10;

2. Click the SQL statement and click Run in the toolbar.

   The execution result is displayed on the Result tab of the SQL editor.