本文档主要介绍了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 |
    +-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+​