本文档主要介绍了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条记录。
操作步骤
- 创建Tablestore Schema。
- 登录DLA控制台。
- 在左侧导航栏单击。
- 在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的私网地址。
- 创建表。
以下示例通过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表中。
- 读取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 |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+