小提示：快速体验表格存储 Tablestore CLI 工具前，请您先阅读《表格存储快速上手准备》
模型简介
宽表模型是（Wide column） Tablestore 采用的几个模型之一。宽表模型是 Schema-free 的，创建一张宽表仅需要定义 1-4个主键 结构，无需定义属性列结构，在插入数据时添加任意多个属性列即可。主键列表中第一个主键将作为分区键，按照分区键值的范围将数据负载均衡到多个分区 (Partition) 中。
以订单场景为例，一张订单数据表order的表主键为_id，包含了若干个属性列，例如: cName(消费者姓名)，pType(产品类型)，sld(售货员ID)，total_Price(订单总价格)等等。对应的宽表数据结构如下图所示
订单表数据结构
下面将带您快速体验使用 Tablestore CLI 工具如何实现对上述订单表order的基本操作。
宽表操作
- 创建数据表。执行 create 命令创建一张订单表，表名为 order。
create -t order --pk '[{"c":"id","t":"string"}]'
- 选择数据表。执行 use --wc 命令选择操作 order 表。
use --wc -t order
- 数据导入。这里提供两种方式导入数据，二选一即可。
- 自定义数据，执行 put命令单行写入。示例中写入了5条订单数据。
put --pk '["0000000f470ef0f548b925ceffe1a7e3"]' --attr '[{"c":"pBrand","v":"oppo"},{"c":"pPrice","v":2498.99},{"c":"totalPrice","v":1599.0},{"c":"sName","v":"售郑七"},{"c":"pId","v":"p0004001"},{"c":"oId","v":"o0057022192"},{"c":"hasPaid","v":false},{"c":"sId","v":"s0007"},{"c":"orderTime","v":1518510583886,"isint":true},{"c":"pName","v":"oppo K1"},{"c":"cName","v":"消郑七"},{"c":"pType","v":"手机"},{"c":"pCount","v":1,"isint":true},{"c":"cId","v":"c0017"}]' put --pk '["000000114d884ca1dbd6b9a58e8d0d94"]' --attr '[{"c":"pBrand","v":"vivo"},{"c":"pPrice","v":1599.0},{"c":"payTime","v":1509615334404,"isint":true},{"c":"totalPrice","v":2498.99},{"c":"sName","v":"售周五"},{"c":"pId","v":"p0003004"},{"c":"oId","v":"o0039248410"},{"c":"hasPaid","v":true},{"c":"sId","v":"s0015"},{"c":"orderTime","v":1509614885965,"isint":true},{"c":"pName","v":"vivo x21"},{"c":"cName","v":"消冯八"},{"c":"pType","v":"手机"},{"c":"pCount","v":1,"isint":true},{"c":"cId","v":"c0018"}]' put --pk '["0000004dbeb751e77cf0b3f0da90b6ee"]' --attr '[{"c":"pBrand","v":"小米"},{"c":"pPrice","v":2002.0},{"c":"payTime","v":1491560220742,"isint":true},{"c":"totalPrice","v":6006.0},{"c":"sName","v":"售楚十"},{"c":"pId","v":"p0005001"},{"c":"oId","v":"o0003171350"},{"c":"hasPaid","v":true},{"c":"sId","v":"s0021"},{"c":"orderTime","v":1491560154808,"isint":true},{"c":"pName","v":"小米 pad"},{"c":"cName","v":"消赵一"},{"c":"pType","v":"平板"},{"c":"pCount","v":3,"isint":true},{"c":"cId","v":"c0022"}]' put --pk '["00000057f33ff1d0a2d00ff6dbf4c411"]' --attr '[{"c":"pBrand","v":"oppo"},{"c":"pPrice","v":3199.98},{"c":"totalPrice","v":3199.98},{"c":"sName","v":"售周五"},{"c":"pId","v":"p0004003"},{"c":"oId","v":"o0036473830"},{"c":"hasPaid","v":false},{"c":"sId","v":"s0015"},{"c":"orderTime","v":1508226047439,"isint":true},{"c":"pName","v":"oppo R17"},{"c":"cName","v":"消吴六"},{"c":"pType","v":"手机"},{"c":"pCount","v":1,"isint":true},{"c":"cId","v":"d0006"}]' put --pk '["0000005be2b43dd134eae18ebe079774"]' --attr '[{"c":"pBrand","v":"小米"},{"c":"pPrice","v":2299.21},{"c":"totalPrice","v":6897.63},{"c":"sName","v":"售郑七"},{"c":"pId","v":"p0005003"},{"c":"oId","v":"o0035062633"},{"c":"hasPaid","v":false},{"c":"sId","v":"s0007"},{"c":"orderTime","v":1507519847532,"isint":true},{"c":"pName","v":"小米 6"},{"c":"cName","v":"消周五"},{"c":"pType","v":"手机"},{"c":"pCount","v":3,"isint":true},{"c":"cId","v":"c0015"}]'
- 下载样例数据压缩包到本地并解压。执行 import 命令批量导入。样例数据中共包含100万条订单数据，可通过 import -l 参数自定义导入行数（1000万行内免费使用），示例中导入了5万条订单数据。yourFilePath表示样例数据压缩包解压后的路径。
导入命令
import -i yourFilePath --ignore_version -l 50000
日志输出
Current speed is: 10000 rows/s. Total succeed count 10000, failed count 0. Current speed is: 12600 rows/s. Total succeed count 22600, failed count 0. Current speed is: 17200 rows/s. Total succeed count 39800, failed count 0. Import finished, total count is 50000, failed 0 rows.
- 执行 get命令按照 order_Md5 和 order_id 查询一行数据。
get --pk '["0000005be2b43dd134eae18ebe079774"]'
输出
+----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+--------+---------+-------+-------+--------+------------+ | id | cId | cName | hasPaid | oId | orderTime | pBrand | pCount | pId | pName | pPrice | pType | sId | sName | totalPrice | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+--------+---------+-------+-------+--------+------------+ | 0000005be2b43dd134eae18ebe079774 | c0015 | 消周五 | false | o0035062633 | 1507519847532 | 小米 | 3 | p0005003 | 小米 6 | 2299.21 | 手机 | s0017 | 售郑七 | 6897.63 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+--------+---------+-------+-------+--------+------------+
- 执行 scan 命令查询多行数据。示例中查询了5条订单数据。
scan -l 5
输出
+----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+ | id | cId | cName | hasPaid | oId | orderTime | pBrand | pCount | pId | pName | pPrice | pType | sId | sName | totalPrice | payTime | +----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+ | 0000000f470ef0f548b925ceffe1a7e3 | c0017 | 消郑七 | false | o0057022192 | 1.518510583886e+12 | oppo | 1 | p0004001 | oppo K1 | 2498.99 | 手机 | s0007 | 售郑七 | 1599 | | +----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+ | 000000114d884ca1dbd6b9a58e8d0d94 | c0018 | 消冯八 | true | o0039248410 | 1.509614885965e+12 | vivo | 1 | p0003004 | vivo x21 | 1599 | 手机 | s0015 | 售周五 | 2498.99 | 1.509615334404e+12 | +----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+ | 0000004dbeb751e77cf0b3f0da90b6ee | c0022 | 消赵一 | true | o0003171350 | 1.491560154808e+12 | 小米 | 3 | p0005001 | 小米 pad | 2002 | 平板 | s0021 | 售楚十 | 6006 | 1.491560220742e+12 | +----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+ | 00000057f33ff1d0a2d00ff6dbf4c411 | d0006 | 消吴六 | false | o0036473830 | 1508226047439 | oppo | 1 | p0004003 | oppo R17 | 3199.98 | 手机 | s0015 | 售周五 | 3199.98 | | +----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+ | 0000005be2b43dd134eae18ebe079774 | c0015 | 消周五 | false | o0035062633 | 1507519847532 | 小米 | 3 | p0005003 | 小米 6 | 2299.21 | 手机 | s0017 | 售郑七 | 6897.63 | | +----------------------------------+-------+--------+---------+-------------+--------------------+--------+--------+----------+----------+---------+-------+-------+--------+------------+--------------------+
SQL模式
- 执行 sql 命令进入 SQL 命令行模式，可通过 sql 语句查询数据表。
sql
- 建立 order 数据表的映射表。可执行 describe `order` 查看订单表 order 的映射表是否已创建，若已创建则跳过此步骤。
CREATETABLE `order` ( `id` VARCHAR(1024), `cId` MEDIUMTEXT, `cName` MEDIUMTEXT, `hasPaid` BOOL, `oId` MEDIUMTEXT, `orderTime` BIGINT(20), `pBrand` MEDIUMTEXT, `pCount` BIGINT(20), `pId` MEDIUMTEXT, `pName` MEDIUMTEXT, `pPrice` DOUBLE, `pType` MEDIUMTEXT, `payTime` BIGINT(20), `sId` MEDIUMTEXT, `sName` MEDIUMTEXT, `totalPrice` DOUBLE, PRIMARY KEY(`id`));
示例一：查询10条售货员姓名为“售周五”的订单，按照订单总金额升序排列。
select*from `order` where sName ="售周五"orderby totalPrice asclimit10;
输出
+----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | id | cId | cName | hasPaid | oId | orderTime | pBrand | pCount | pId | pName | pPrice | pType | payTime | sId | sName | totalPrice | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 000d63a8240fd5798ae533fab9627fbd | c0018 | 消冯八 | true | o0067305260 | 1523656305350 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | 1523656890642 | s0005 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 000b836ed542c958f8f1e77edfbb7d77 | c0022 | 消赵一 | false | o0013980680 | 1496968800141 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 000bdf5a862e44e6055861cd82048b68 | d0006 | 消吴六 | true | o0086746505 | 1533387384921 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | 1533387459564 | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 0005843fd4595d992dd656a6dfda3956 | d0016 | 消吴六 | false | o0048417764 | 1514203877923 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0005 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 000604f8ffb6e3b5198da05a804d9738 | c0018 | 消冯八 | true | o0022405938 | 1501185776343 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | 1501186194040 | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 000281b2afb0e19b750dca4477b6c5c0 | c0018 | 消冯八 | false | o0002835300 | 1491391860673 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 0002bf3bfeff3296d72e13a95fe503e6 | c0015 | 消周五 | false | o0022955766 | 1501461219307 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+--------- +--------+-------+---------------+-------+--------+------------+ | 00035d195c8d7a1273b9d7a603b97bf5 | c0022 | 消赵一 | false | o0036716450 | 1508347328100 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 0000f560b62779285e86947f8e8d0e4c | c0008 | 消冯八 | false | o0000826505 | 1490386088808 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+ | 00026613c323ad57e57a87730f316f94 | c0018 | 消冯八 | false | o0094575530 | 1537306505439 | 小米 | 1 | p0005004 | 红米 5s | 499.01 | 手机 | null | s0015 | 售周五 | 499.01 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+---------+--------+-------+---------------+-------+--------+------------+
示例二：统计产品类型为“手机”的订单条数
selectcount(*)from `order` where pType ="手机";
输出
+----------+ | count(*) | +----------+ | 33915 | +----------+
示例3：统计产品个数大于1的订单条数
selectcount(*)from `order` where pCount >1;
输出
+----------+ | count(*) | +----------+ | 33481 | +----------+
退出 sql 模式
exit;
退出 cli 工具
exit
高级特性
多元索引提供了丰富的查询方式和数据聚合能力，例如全文检索、多列排序、分组、求和等等。多元索引在SQL查询加速方面也有着很好的特性，下面将再导入一百万条订单数据（这里不作展示，参考上文导入），使用SQL查询。
执行 create_search_index 命令创建多元索引。
注意：创建多元索引后会按照表中数据量大小产生少量费用，删除索引后停止计费。多元索引创建后需要等待一段时间，数据表中的数据将以异步的方式自动同步到索引中。
create_search_index-torder-norder_index{ "IndexSetting": null, "FieldSchemas": [{ "FieldName": "id", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "cId", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "cName", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "hasPaid", "FieldType": "BOOLEAN", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "oId", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "orderTime", "FieldType": "LONG", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "pBrand", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "pCount", "FieldType": "LONG", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "pId", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "pName", "FieldType": "TEXT", "Index": true, "EnableSortAndAgg": false, "Store": true }, { "FieldName": "pPrice", "FieldType": "DOUBLE", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "pType", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "sId", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "sName", "FieldType": "KEYWORD", "Index": true, "EnableSortAndAgg": true, "Store": true }, { "FieldName": "totalPrice", "FieldType": "DOUBLE", "Index": true, "EnableSortAndAgg": true, "Store": true }] }
示例一：统计所有订单数量
selectcount(*)from `order`;
输出
+----------+ | count(*) | +----------+ | 1000000 | +----------+
示例二：统计每个品牌的订单数量
select pBrand,count(*)from `order` groupby pBrand;
输出
+--------+----------+ | pBrand | count(*) | +--------+----------+ | 联想 | 304252 | +--------+----------+ | oppo | 242513 | +--------+----------+ | vivo | 162539 | +--------+----------+ | 小米 | 194543 | +--------+----------+ | 苹果 | 96153 | +--------+----------+
示例三：检索所有产品名包含 “iphone” 并且消费者姓名为“消赵一”并且已经支付的订单，返回前面10条订单。
select*from `order` where payTime isnotnulland cName ="消赵一"and pName like"%iphone%"limit10;
输出
+----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | id | cId | cName | hasPaid | oId | orderTime | pBrand | pCount | pId | pName | pPrice | pType | payTime | sId | sName | totalPrice | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 000031205a858155c4cce6d464642086 | c0022 | 消赵一 | true | o0035222739 | 1507599844791 | 苹果 | 3 | p0001005 | iphone X | 8989 | 手机 | 1507600392490 | s0021 | 售楚十 | 26967 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 00003ee288bbb3dab82e25276f63c954 | c0011 | 消赵一 | true | o0001755823 | 1490851467185 | 苹果 | 2 | p0001003 | iphone 7 | 7979 | 手机 | 1490851509652 | s0007 | 售郑七 | 15958 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 00004fecfa2d58cd9b22a31e6875f320 | c0021 | 消赵一 | true | o0039692760 | 1509836893267 | 苹果 | 2 | p0001004 | iphone 7p | 8080 | 手机 | 1509837292095 | s0019 | 售陈九 | 16160 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 000055fe36693b07ca1ae2a7803df3f4 | c0021 | 消赵一 | true | o0028497546 | 1504234632705 | 苹果 | 1 | p0001002 | iphone 6p | 7070 | 手机 | 1504234649071 | s0017 | 售郑七 | 7070 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 00005db15274cc3f128a9721ff2efb53 | c0021 | 消赵一 | true | o0025763782 | 1502866718223 | 苹果 | 1 | p0001005 | iphone X | 8989 | 手机 | 1502867272695 | s0011 | 售赵一 | 8989 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 0000d0716e693fed72555f8e4a4537b3 | c0021 | 消赵一 | true | o0097412099 | 1538725936411 | 苹果 | 1 | p0001002 | iphone 6p | 7070 | 手机 | 1538726211705 | s0018 | 售冯八 | 7070 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 0000d4f73247516c1d7aad99721c610f | c0022 | 消赵一 | true | o0061144390 | 1520573840081 | 苹果 | 1 | p0001004 | iphone 7p | 8080 | 手机 | 1520574004218 | s0021 | 售楚十 | 8080 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 0000fe74dbccc478a3db91fadd06ffeb | c0021 | 消赵一 | true | o0073210619 | 1526613119777 | 苹果 | 1 | p0001003 | iphone 7 | 7979 | 手机 | 1526613369627 | s0011 | 售赵一 | 7979 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 0001daac925913db5a995dca78701f60 | c0021 | 消赵一 | true | o0092439401 | 1536237095817 | 苹果 | 2 | p0001005 | iphone X | 8989 | 手机 | 1536237549982 | s0016 | 售吴六 | 17978 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+ | 0002341af23476b87bdc3599826c0a96 | c0023 | 消赵一 | true | o0089564235 | 1534798174984 | 苹果 | 3 | p0001004 | iphone 7p | 8080 | 手机 | 1534798468123 | s0014 | 售李四 | 24240 | +----------------------------------+-------+--------+---------+-------------+---------------+--------+--------+----------+-----------+--------+-------+---------------+-------+--------+------------+
