LM计算引擎 INSERT/DELETE语法是什么?

当表定义为实时表时（指定 options(updateType='realtime')），只能采用实时写入的方式录入数据。目前ADS支持的实时写入语句包括：INSERT和DELETE两种类型，不支持UPDATE。 另外，实时表必须定义主键primarykey，主键可包含一列或多列，主键保证唯一性。
[font='iconfont']

实时数据可见性
实时数据通常在INSERT语句执行成功后，一分钟内可查。目前还在持续优化实时数据的可见性时延。针对大批量写入和大批量delete的场景，时效性在数分钟级别，若对时效性有要求，可执行命令optimize table xxx (参见本节“OPTIMIZE TABLE操作”内容)来加速数据的生效时间。

INSERT IGNORE vs INSERT


ADS实时数据INSERT的默认行为为主键覆盖，即后INSERT的记录将覆盖系统中已有的相同主键的记录。 如果INSERT指定 IGNORE关键字，若在系统中已经有相同主键的记录，则当前INSERT IGNORE语句执行能成功，但是新记录将会被丢弃掉。用户可以根据业务应用的实际需求，选择INSERT IGNORE或者INSERT。

INSERT语句


INSERT语句符合标准SQL INSERT语法，支持单条插入:

1. INSERT INTO db_name.table_name (col1, col2, col3) VALUES ('xxx', 123, 'xxx');

多条批量插入:

1. INSERT INTO db_name.table_name (col1, col2, col3) VALUES ('xxx', 111, 'xxx'), ('xxx', 222, 'xxx'), ('xxx', 333, 'xxx');

如果插入数据指定了所有列，可以省略INSERT语句中的列名部分:

1. INSERT INTO db_name.table_name VALUES ('xxx', 111, 'xxx'), ('xxx', 222, 'xxx'), ('xxx', 333, 'xxx');

注意：VALUES中的数据对应的列顺序必须符合表定义时的列顺序，可通过如下语句查询表的列定义的顺序:

1. SHOW CREATE TABLE db_name.table_name;

数据表示


ADS的INSERT语句中的实时数据，采用强类型的策略，如不符合强类型的数据表征，INSERT语句将抛出异常：
VARCHAR, DATE, TIME, TIMESTAMP, MULTIVALUE类型的数据必须加上'', 例如：'abc','2017-01-01';
数值类型的数据不能加上'', 例如：1, 1.9, 0.009

特殊字符


由于ADS实时数据写入链路设计，如下特殊字符是不能出现在INSERT语句的字段数据中：
#, ', \n, \r, \
应用程序应该在数据字段写入ADS前进行特殊字符清洗或过滤处理，通常包含复杂字符的字段为明细信息字段，最终用来在业务层做展示使用，不参与关联或聚合计算。有一种解决方案为，在数据入库前对这类字段进行Base64编码，最终查询这些字段出去后，进行Base64解码获得原始数据。

实时数据写入性能优化


为了提高实时数据写入性能，通常采用如下两种优化策略：
1 批量写入：

• 
  默认单条INSERT语句的最大限制为1MB，但基于实践经验，批量写入的一批的记录数通常控制在100 ~ 200比较合适；
• 
  若表的列非常多，酌情减少一批的记录数，控制一条INSERT语句的大小在1MB以内。

2 分区聚合：

• 
  在批量写入的前提下，尽量保证一批记录的目标hash分区相同，这样可以充分利用批量写入提交的性能优化；
• 
  业务开发者可向ADS的技术支持获取ADS的数据分区算法，用来预先进行数据分区聚合。

删除数据


通过DELETE语句执行删除操作，必须指定WHERE条件:

1. DELETE FROM db_name.table_name WHERE col1 = xxx;

删除全表数据（不包含二级分区，请谨慎使用）:

1. DELETE FROM db_name.table_name WHERE 1=1;

如果目标表包含二级分区，WHERE条件必须包含二级分区列的等值条件:

1. DELETE FROM db_name.table_name WHERE sub_part_col = xxx;

通过系统元数据查询实时数据写入量


查询语句:

1. SELECT * FROM information_schema.realtime_table_traffic_cross;

1. +--------------+-------------+-----------+----------+-------------------+---------------------+--------------+----------------+-----------------------------+-------------------------+-------------------------+-------------------------+
2. | TABLE_SCHEMA | TABLE_NAME  | SCHEMA_ID | TABLE_ID | INSERT_STMT_COUNT | INSERT_RECORD_COUNT | DELETE_COUNT | SUB_PART_COUNT | SUB_PART_RECORD_COUNT_STATS | LAST_RESET_TIME         | CREATE_TIME             | UPDATE_TIME             |
3. +--------------+-------------+-----------+----------+-------------------+---------------------+--------------+----------------+-----------------------------+-------------------------+-------------------------+-------------------------+
4. | xxxxxxxxxxx  | xxxxxxxxxxx |      9096 |       14 |                 0 |                   0 |            0 |              0 | {}                          | 2017-07-24 14:15:09.294 | 2017-07-25 09:39:38.848 | 2017-07-25 09:39:38.848 |
5. +--------------+-------------+-----------+----------+-------------------+---------------------+--------------+----------------+-----------------------------+-------------------------+-------------------------+-------------------------+

列定义：

• INSERT_STMT_COUNT: 从LAST_RESET_TIME到目前为止，实时INSERT的语句条数；
• INSERT_RECORD_COUNT:从LAST_RESET_TIME到目前为止，实时INSERT的记录条数，批量插入的情况下，INSERT_RECORD_COUNT大于INSERT_STMT_COUNT；
• DELETE_COUNT: 从LAST_RESET_TIME到目前为止，DELETE的语句条数；
• SUB_PART_COUNT: 从LAST_RESET_TIME到目前为止，实时INSERT涉及到的二级分区的个数；
• SUB_PART_RECORD_COUNT_STATS: 从LAST_RESET_TIME到目前为止，实时INSERT涉及到的各个二级分区的记录数；
• LAST_RESET_TIME:realtime_table_traffic_cross表默认24小时reset一次，该字段记录reset的时间点。

FLUSH操作


由于AnalyticDB的实时数据写入和计算节点实时数据消费是异步流程，所以，FLUSH操作用来检查FLUSH时间点之前的实时数据是否都被正常入库到计算节点上了。

1. FLUSH db=<schema> table=<table> timeout=<timeout_duration_ms>

其中 timeout=<timeout_duration_ms>用来指定FLUSH操作在多长时间内完成（单位：ms），否则FLUSH超时失败，未能保证FLUSH时间点之前的实时数据被正常入库完成。

1. FLUSH db=<schema> table=<table> timeout=<timeout_duration_ms> return_version_onfailure=[true|false]

其中 return_version_onfailure=[true|false]用来指定在FLUSH失败时是否返回当前计算节点的数据版本号，true 表示返回， false表示不返回。

1. FLUSH db=<schema> table=<table> timeout=<timeout_duration_ms> expected_version=<partition_version_list>

其中 expected_version=<partition_version_list>用来传入期望的数据分区版本号，仅仅当期望的数据分区版本号满足时，FLUSH才执行成功。

1. <partition_version_list> pattern should be 0:xxxx,1:xxxx,2:xxxx,...

OPTIMIZE TABLE操作


OPTIMIZE TABLE是实时表的的索引构建操作，用来对实时数据构建索引并且与基线数据进行合并，以提升实时表的查询性能。

1. OPTIMIZE TABLE [dbname.]table_name1 [, [dbname.]table_name2]