This topic describes how to read data from and write data to Tablestore.
Background information
Data Lake Analytics (DLA) allows you to query data from and write data to Tablestore
by using standard SQL statements. Tablestore is a NoSQL database service developed
by Alibaba Cloud. It uses a variety of data models and supports search index queries.
DLA allows you to call a Tablestore API operation to query data, such as point query
and range query. In addition, DLA can intelligently select a primary table, secondary
index table, and search index table. This significantly improves query performance
and reduces user costs.
- Database table concepts
| Instance |
Schema or database |
| Table |
Table |
| Primary key |
A column in the database table. Settings of related parameters: isPrimaryKey=true,
isNullable=false
|
| Column |
A column in the database table. Settings of related parameters: isPrimaryKey=false,
isNullable=<Defined in a DDL statement>
|
- Field description
| INTEGER (eight bytes) |
BIGINT (eight bytes) |
| STRING |
VARCHAR |
| BINARY |
VARBINARY |
| DOUBLE |
DOUBLE |
| BOOLEAN |
BOOLEAN |
Preparations
Before you use DLA to read data from Tablestore, you must prepare test data in Tablestore.
For more information, see Create instances.
Procedure
- Create a Tablestore schema.
- Log on to the DLA console.
- 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.
- 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.
- 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 |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+