本文主要介绍如何读写Tablestore中的数据。

步骤一:创建Tablestore Schema

  1. 登录DLA控制台

  2. 单击左侧导航栏的访问点管理,然后单击登录DMS,执行以下SQL创建Tablestore Schema。

    您也可以通过MySQL客户端或者程序代码等方式连接DLA,然后执行以下SQL创建Tablestore Schema。

    ​​​​​ CREATE SCHEMA hangzhou_ots_test  WITH DBPROPERTIES (
     catalog = 'ots',
     location = 'https://otsInstanceName.cn-hangzhou.ots-internal.aliyuncs.com',
     instance = 'ots-instance-name'
     );​
    说明 DLA和Tablestore通过VPC相关策略,直接为您打通私网环境,location需要填写Tablestore的私网地址。
    • catalog:指定创建的Schema类型是Tablestore。
    • location:Tablestore中的DLA接入地址或者VPC访问地址,如下图所示:

    • instance:Tablestore实例名称。

步骤二:创建表

以下示例通过MySQL命令行工具连接DLA,然后在DLA中创建Tablestore表。

mysql>  CREATE EXTERNAL TABLE hangzhou_ots_test with dbproperties (
  catalog = 'ots',
  location = 'https://hz-tpch-1x-vol.cn-hangzhou.vpc.tablestore.aliyuncs.com',
  instance = 'hz-tpch-1x-vol'
);

Query OK, 0 rows affected (0.23 sec)

#hangzhou_ots_test             
#catalog = 'ots',              
#location = 'https://xxx'      
#instance = 'hz-tpch-1x-vol'   
说明
  • 字段的名称、顺序需要与Tablestore中的字段名称、顺序相同。
  • 表创建成功后,DLA自动将Tablestore中的表数据同步到DLA表中。

步骤三:读取数据

Tablestore中的数据同步到DLA之后,您就可以使用标准SQL语法来查询和分析Tablestore数据。

​mysql> select count(*) from nation;
+-------+
| _col0 |
+-------+
|    25 |
+-------+
1 row in set (1.19 sec)
mysql> select * from nation;
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
| n_nationkey | n_comment                                                                                                          | n_name         | n_regionkey |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
|           0 |  haggle. carefully final deposits detect slyly agai                                                                | ALGERIA        |           0 |
|           1 | al foxes promise slyly according to the regular accounts. bold requests alon                                       | ARGENTINA      |           1 |
|           2 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special         | BRAZIL         |           1 |
|           3 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold              | CANADA         |           1 |
|           4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d                | EGYPT          |           4 |
|           5 | ven packages wake quickly. regu                                                                                    | ETHIOPIA       |           0 |
|           6 | refully final requests. regular, ironi                                                                             | FRANCE         |           3 |
|           7 | l platelets. regular accounts x-ray: unusual, regular acco                                                         | GERMANY        |           3 |
|           8 | ss excuses cajole slyly across the packages. deposits print aroun                                                  | INDIA          |           2 |
|           9 |  slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull | INDONESIA      |           2 |
|          10 | efully alongside of the slyly final dependencies.                                                                  | IRAN           |           4 |
|          11 | nic deposits boost atop the quickly final requests? quickly regula                                                 | IRAQ           |           4 |
|          12 | ously. final, express gifts cajole a                                                                               | JAPAN          |           2 |
|          13 | ic deposits are blithely about the carefully regular pa                                                            | JORDAN         |           4 |
|          14 |  pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t                      | KENYA          |           0 |
|          15 | rns. blithely bold courts among the closely regular packages use furiously bold platelets?                         | MOROCCO        |           0 |
|          16 | s. ironic, unusual asymptotes wake blithely r                                                                      | MOZAMBIQUE     |           0 |
|          17 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun         | PERU           |           1 |
|          18 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos                        | CHINA          |           2 |
|          19 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account    | ROMANIA        |           3 |
|          20 | ts. silent requests haggle. closely express packages sleep across the blithely                                     | SAUDI ARABIA   |           4 |
|          21 | hely enticingly express accounts. even, final                                                                      | VIETNAM        |           2 |
|          22 |  requests against the platelets use never according to the quickly regular pint                                    | RUSSIA         |           3 |
|          23 | eans boost carefully special requests. accounts are. carefull                                                      | UNITED KINGDOM |           3 |
|          24 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be     | UNITED STATES  |           1 |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+​

从下图中的id可以看出,DLA中查询到的数据与Tablestore中的数据完全相同,接下来您就可以使用DLA进行数据分析了。