Read and write data from Tablestore

Preparations

Before you use DLA to read data from Tablestore, you must prepare test data in Tablestore. For more information, see Create instances.

Procedure

1. Create a Tablestore schema.
   1. Log on to the DLA console.
   2. In the left-side navigation pane, choose Serverless Presto > SQL access point. On the SQL access point page, find your virtual private cloud (VPC) and click Log on in DMS in the Actions column. Then, execute the following statement to create a Tablestore schema.
      You can also connect to DLA by using the MySQL client or program code. Then, execute the following statement to create a Tablestore schema:

      CREATE SCHEMA hangzhou_ots_test  WITH DBPROPERTIES (
       catalog = 'ots',
       location = 'https://otsInstanceName.cn-hangzhou.ots-internal.aliyuncs.com',
       instance = 'ots-instance-name'
       );​

      Note You can connect DLA to Tablestore over a VPC based on VPC-related policies. In the preceding statement, location indicates the VPC endpoint of Tablestore.

      • catalog: the type of the schema that you want to create. In this topic, a Tablestore schema is to be created.
      • location: the DLA endpoint or VPC endpoint in Tablestore.
      • instance: the name of the Tablestore instance.
2. Create a Tablestore table.
   In this topic, a MySQL command-line tool is used to connect the Tablestore instance to DLA. After you connect the Tablestore instance to DLA, create a Tablestore table in DLA.

   CREATE EXTERNAL TABLE `nation` (
    `N_NATIONKEY` int COMMENT '',
    `N_NAME` char not NULL COMMENT '',
    `N_REGIONKEY` tinyint NULL COMMENT '',
    `N_COMMENT` STRING NULL COMMENT ''
   );

   Note

   • The names and sequence of fields in the table that you created must be the same as those in the table in Tablestore.
   • After the table is created, DLA automatically synchronizes data from the table in Tablestore to the table that you created.
3. Read data from the table in Tablestore.
   After Tablestore data is synchronized to DLA, you can use standard SQL statements of DLA to query and analyze the data.

   ​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 |
   +-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+​