本文档主要介绍了Data Lake Analytics如何读写Tablestore中的数据。

背景信息

Data Lake Analytics支持接入Tablestore服务，通过标准SQL语句查询Tablestore中的数据或者直接向Tablestore写入数据。Tablestore是阿里云自研的NoSQL多模型数据库，支持多元索引查询。您可以在Data Lake Analytics中调用Tablestore API进行数据查询，例如点查询、范围查询等。同时，支持智能选择主表、二级索引表和多元索引表，大大提升查询性能，降低用户成本。

  • 数据库和表概念映射
    数据库和表概念 DLA映射
    实例（instance） schema或database
    表（table） table
    主键列（pk） column，isPrimaryKey=true，isNullable=false
    非主键列（column） column，isPrimaryKey=false，isNullable=<用户通过DDL定义>
  • 字段映射关系
    数据库字段 DLA映射
    INTEGER（8字节） bigint（8字节）
    STRING varchar
    BINARY varbinary
    DOUBLE double
    BOOLEAN boolean

准备工作

通过DLA读写Tablestore数据前，您需要在Tablestore中准备测试数据，详细操作请参见创建实例。

本示例在Tablestore中创建nation表，并向nation中插入25条记录，如下图所示：nation

操作步骤

  1. 创建Tablestore Schema。
    1. 登录DLA控制台。
    2. 在左侧导航栏单击Serverless Presto > SQL访问点。
    3. 在SQL访问点页面，单击登录到DMS，在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'
 );​
      参数 参数说明
      catalog 指定创建的Schema类型为Tablestore。
      location DLA的公网地址或者VPC访问地址。
      instance Tablestore实例名称。
      说明 DLA和Tablestore支持通过VPC网络为您打通私网环境，为了您业务的安全性，推荐您使用VPC网络。如果您使用的是VPC网络，location需要填写Tablestore的私网地址。
  2. 创建表。
    以下示例通过MySQL命令行工具连接DLA，然后在DLA中创建Tablestore表。
    CREATE EXTERNAL TABLE `nation` (
 `N_NATIONKEY` int COMMENT '',
 `N_NAME` char not NULL COMMENT '',
 `N_REGIONKEY` tinyint NULL COMMENT '',
 `N_COMMENT` STRING NULL COMMENT ''
)
    说明
    • 字段的名称、顺序需要与Tablestore中的字段名称、顺序相同。
    • 表创建成功后，DLA自动将Tablestore中的表数据映射到DLA表中。
  3. 读取Tablestore数据。
    Tablestore中的数据映射到DLA之后，您就可以使用标准SQL语法来查询和分析Tablestore数据。
    ​select count(*) from nation;
+-------+
| _col0 |
+-------+
|    25 |
+-------+
1 row in set (1.19 sec)
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 |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+​