全部产品
云市场

DML语法

更新时间:2019-11-22 10:33:26

数据写入与删除

通过 Phoenix 提供的 Upsert 语法写入,索引数据会异步同步到全文索引中。主要支持 UPSERT VALUESUPSERT SELECT 两类语法,我们在此不做赘述,具体可参考 这里

通过 Phoenix 提供的 Delete 语法删除数据,索引数据会异步同步到全文索引中,具体可参考 这里

search index Select

全文索引查询语法:

  1. SELECT projected_columns
  2. FROM table_name
  3. WHERE search_query = 'search_expression' [ (and common_column1_filter), (and common_column2_filter)...]
  4. [ ORDER BY (column_name1, column_name2, column_nsearch_queryame3... ]
  5. [ LIMIT n ];

search_expression:

  • 基本表达式,对应 solr 中的 q 参数, 使用说明详见这里
    • Solr JSON query, 使用 JSON 表达式查询 Solr.

对于Phoenix中timestamp类型字段建立的索引,在solr中我们都以Long(unix毫秒时间戳)的方式去进行存储和处理,因此在进行查询的时候,要将查询的日期转换成unix毫秒时间戳来进行查询。例如我对A_TIMESTAMP这个字段建立了全文索引,然后想要查询 ‘2011-11-11 11:11:11’这个时间点的数据,那么我要把表达式写成search_query = ‘A_TIMESTAMP:1320981071000’。

Solr JSON Syntax

  1. {
  2. "q": query_expression (string),
  3. "fq": filter_query_expression(s) (string_or_array_of_strings, ...),
  4. "sort": sort_expression(String),
  5. "start": start_index(number),
  6. "rows": result_rows(number),
  7. "cursor": cursor_mark_string
  8. }

参数说明

  • q: 检索查询
  • fq: filter query, 查询结果不涉及打分,并且会查询结果会被cache到内存,建议对于会复用的查询用,可参考Solr fq文档
  • sort: 字段名字 + asc/desc
  • start: 查询开始位置索引,默认从0开始
  • rows: 每次查询返回结果集大小,默认10
  • cursor: 使用游标做翻页查询,对于深翻查询建议使用,相对于start和rows性能更好,start和rows会重头查。可参考Solr分页文档

JSON 语法说明

语法说明示例表:

  1. create table tableName(id integer primary key, name varchar, addr varchar);
  2. upsert into tableName(id,name)values(10, 'cc');
  3. upsert into tableName(id,name,addr)values(11, 'aa', 'ZheJiang');
  4. upsert into tableName(id,name,addr)values(12, 'bb', 'BeiJin');
  5. create search index on tableName(name);

1. q 和 fq 参数查询

查询结果只显示在 select 中指定的列,可以配合 limit 和 order by 子句使用, 也可以在 JSON 语法中设定分页排序相关的参数。

  1. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*"}';
  2. +-----+-------+-----------+
  3. | ID | NAME | ADDR |
  4. +-----+-------+-----------+
  5. | 10 | cc | |
  6. | 11 | aa | ZheJiang |
  7. | 12 | bb | BeiJin |
  8. +-----+-------+-----------+
  1. SELECT * FROM tableName WHERE search_query='{"q":"*:*","fq":"NAME:bb"}';
  2. +-----+-------+---------+
  3. | ID | NAME | ADDR |
  4. +-----+-------+---------+
  5. | 12 | bb | BeiJin |
  6. +-----+-------+---------+

2. 排序

  1. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*","sort":"NAME desc"}';
  2. +-----+-------+-----------+
  3. | ID | NAME | ADDR |
  4. +-----+-------+-----------+
  5. | 10 | cc | |
  6. | 12 | bb | BeiJin |
  7. | 11 | aa | ZheJiang |
  8. +-----+-------+-----------+

3. 分页查询

start + row 分页

此方法适用于小数据量分页查询,同时 start 参数值不宜太大。

  1. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*","start":"1","rows":"2"}';
  2. +-----+-------+-----------+
  3. | ID | NAME | ADDR |
  4. +-----+-------+-----------+
  5. | 11 | aa | ZheJiang |
  6. | 12 | bb | BeiJin |
  7. +-----+-------+-----------+
  1. // 排序 + 分页
  2. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*","sort":"NAME desc","start":"1","rows":"2"}';
  3. +-----+-------+-----------+
  4. | ID | NAME | ADDR |
  5. +-----+-------+-----------+
  6. | 12 | bb | BeiJin |
  7. | 11 | aa | ZheJiang |
  8. +-----+-------+-----------+

cursor 分页

cursor 适用于深翻,其性能相对于前一种方式更加平稳。每次查询需要指定 cursor 参数,查询结果会返回下一次查询所需的 cursor 字符串(此字段为虚拟字段,字段名为 NEXT_CURSOR)。

测试索引

  1. create search index on tableName(name{stored=true}, addr{stored=true});

Select 查询字段必须全部在索引表中,同时开了 store 或者 doc value 属性。

查询所有数据

  1. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*", "cursor":"*"}';
  2. +-----+-------+-----------+-------------------+
  3. | ID | NAME | ADDR | NEXT_CURSOR |
  4. +-----+-------+-----------+-------------------+
  5. | 10 | cc | | AoEoODAwMDAwMGM= |
  6. | 11 | aa | ZheJiang | AoEoODAwMDAwMGM= |
  7. | 12 | bb | BeiJin | AoEoODAwMDAwMGM= |
  8. +-----+-------+-----------+-------------------+

当 rows 不设置时,使用默认值

第一次查查询,利用 cursor 读取一行数据:

  1. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*", "cursor":"*","rows":"1"}';
  2. +-----+-------+-------+-------------------+
  3. | ID | NAME | ADDR | NEXT_CURSOR |
  4. +-----+-------+-------+-------------------+
  5. | 10 | cc | | AoEoODAwMDAwMGE= |
  6. +-----+-------+-------+-------------------+

当 cursor 的值为 * 时,表示 start marker

第二次查询、读取剩余的两行数据:

  1. SELECT * FROM tableName WHERE search_query='{"q":"NAME:*", "cursor":"AoEoODAwMDAwMGE=","rows":"2"}';
  2. +-----+-------+-----------+-------------------+
  3. | ID | NAME | ADDR | NEXT_CURSOR |
  4. +-----+-------+-----------+-------------------+
  5. | 11 | aa | ZheJiang | AoEoODAwMDAwMGM= |
  6. | 12 | bb | BeiJin | AoEoODAwMDAwMGM= |
  7. +-----+-------+-----------+-------------------+

第二次查询时 cursor 参数的值,为第一次查询 NEXT_CURSOR 字段的结果。除了第一次 cusor 的值是星号,之后都是上一次查询 NEXT_CURSOR 字段的值。

4. 作为查询条件的列的大小写

语法说明实例表:

  1. create table tableName(id integer primary key, "name" varchar, addr varchar);
  2. upsert into tableName(id,"name",addr)values(10, 'cc', 'ShangHai');
  3. upsert into tableName(id,"name",addr)values(11, 'aa', 'ZheJiang');
  4. upsert into tableName(id,"name",addr)values(12, 'bb', 'BeiJin');
  5. create search index on tableName("name",addr);

进行查询:

  1. select * from tableName where search_query = '{"q":"ADDR:ZheJiang"}';
  2. +-----+-------+-----------+
  3. | ID | name | ADDR |
  4. +-----+-------+-----------+
  5. | 11 | aa | ZheJiang |
  6. +-----+-------+-----------+
  7. select * from tableName where search_query = '{"q":"name:aa"}';
  8. +-----+-------+-----------+
  9. | ID | name | ADDR |
  10. +-----+-------+-----------+
  11. | 11 | aa | ZheJiang |
  12. +-----+-------+-----------+

在Phoenix中,列的大小写默认情况下是非大小写敏感的,建表中如果没有把列用双引号包起来则列的大小写默认均为大写(详情请点击这里)。json查询条件中的大小写情况和源表是一致的,但与sql语句中不写双引号默认大写的情况不同,json查询条件中不会将列名自动转为大写,因此,在json查询条件中要注意列名的大小写情况,必须与源表的大小写情况一致,例如上面例子中的 ADDR和 name列。

5. 带自定义列簇的列的作为查询条件进行查询

语法说明示例表:

  1. create table tableName(id integer primary key, name varchar, f.addr varchar);
  2. upsert into tableName(id,name,f.addr)values(10, 'cc', 'ShangHai');
  3. upsert into tableName(id,name,f.addr)values(11, 'aa', 'ZheJiang');
  4. upsert into tableName(id,name,f.addr)values(12, 'bb', 'BeiJin');
  5. create search index on tableName(f.addr);

进行查询:

  1. select * from tableName where search_query = '{"q":"F_ADDR:ZheJiang"}';
  2. +-----+-------+-----------+
  3. | ID | NAME | ADDR |
  4. +-----+-------+-----------+
  5. | 11 | aa | ZheJiang |
  6. +-----+-------+-----------+

在json语法中对带自定义列簇的列进行查询时,要注意做为查询条件的列名为 “列簇”+”_”+”列名”,即上面例子的F.ADDR,在JSON查询时为F_ADDR。

6. JSON查询语法限制

  • 查询的 where 子句 只支持 search_query 条件查询
  • 不支持 limit + offset、order by 子句
  • 不支持复杂查询操作比如:Join、子查询、分组聚合。

在ARRAY类型字段上使用search index

语法说明示例表:

  1. create table tableName(id integer primary key, name varchar, addr varchar array);
  2. upsert into tableName(id,name,addr)values(11, 'aa', ARRAY['ZheJiang','Guangdong']);
  3. upsert into tableName(id,name,addr)values(12, 'bb', ARRAY['BeiJin','ShangHai']);
  4. create search index on tableName(addr);

进行查询:

  1. SELECT * FROM tableName WHERE search_query = '{"q":"ADDR:ZheJiang"}';
  2. +-----+-------+------------------------+
  3. | ID | NAME | ADDR |
  4. +-----+-------+------------------------+
  5. | 11 | aa | [ZheJiang, Guangdong] |
  6. +-----+-------+------------------------+

查询条件只要匹配到该行的ARRAY字段中任意一项,就代表查询命中。

查询计划

SearchIndex会根据元数据判断,Solr中数据是否可查(打开stored或者docValues),如果可查则直接从Solr中返回结果,如果不可查则会自动回查HBase拿到最终结果。使用 epxlain 可以查看查询计划。通过查询计划可以判断,是直接查询索引返回结果,还是查询完索引又回查了数据表。

当 select 的列可以在索引表中直接获取数据时,不需要回查数据表

  1. create search index on test2(name{stored=true});
  2. explain select name from test2 where search_query='NAME:*';
  3. +---------------------------------------------------+-----------------+----------------+
  4. | PLAN | EST_BYTES_READ | EST_ROWS_READ |
  5. +---------------------------------------------------+-----------------+----------------+
  6. | CLIENT EXECUTE QUERY: [ NAME:* ] ON SEARCH INDEX | null | null |
  7. +---------------------------------------------------+-----------------+----------------+

当 select 的列不可以在索引表中直接获取数据时,需要回查数据表

  1. create search index on test2(name{stored=true});
  2. explain select addr from test2 where search_query='NAME:*';
  3. +---------------------------------------------------+-----------------+----------------+
  4. | PLAN | EST_BYTES_READ | EST_ROWS_READ |
  5. +---------------------------------------------------+-----------------+----------------+
  6. | CLIENT EXECUTE QUERY: [ NAME:* ] ON SEARCH INDEX | null | null |
  7. | CLIENT EXECUTE MULTI GET ON DATA TABLE: TEST2 | null | null |
  8. +---------------------------------------------------+-----------------+----------------+