当前位置： 首页 > news >正文

厦门网站建设2015/百度优化培训

news 2025/8/3 13:11:48
厦门网站建设2015,百度优化培训,学习软件测试,怎么找外包公司点击关注上方“知了小巷”，设为“置顶或星标”，第一时间送达干货。描述：需要将数据从Oracle批量和实时采集到HBase中，并通过Phoenix映射表进行查询，支持二级索引。二级索引类型选择使用覆盖索引。当在Phoenix表创建二级…

点击关注上方“知了小巷”，

设为“置顶或星标”，第一时间送达干货。

描述：需要将数据从Oracle批量和实时采集到HBase中，并通过Phoenix映射表进行查询，支持二级索引。

二级索引类型选择使用覆盖索引。

当在Phoenix表创建二级索引之后，从Oracle采集到HBase的增量数据并没有从二级索引的条件中筛选出来，也就是Phoenix没有自动为通过HBase API进来的增量数据创建和维护索引，一般需要rebuild，方式有两种：

1. 先删除索引，再重新创建索引

2. ALTER语法

以上均从Phoenix端进行操作。

现在进行演示，数据采集工具使用DataX。

创建namespace和HBase表

hbase shell：

create_namespace 'ZLXX'create 'ZLXX:WT_TRADE_REFUND', 'INFO'

创建schema和Phoenix表并映射到HBase表

./sqlline.py：

create schema ZLXX;create table ZLXX.WT_TRADE_REFUND (    id varchar primary key,    info.trade_id varchar,    info.amount  varchar,    info.status varchar,    info.modify_time varchar) column_encoded_bytes=0;

phoenix sql查询数据

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund;+-----+-----------+---------+---------+--------------+| ID  | TRADE_ID  | AMOUNT  | STATUS  | MODIFY_TIME  |+-----+-----------+---------+---------+--------------++-----+-----------+---------+---------+--------------+No rows selected (0.066 seconds)

初始数据采集SQL(Oracle)

select id, trade_id, amount, status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id in ('1215529','1215528','1205528','1195531','1195530');

数据采集DataX配置(hbase11xwriter)

这里先使用hbase11xwriter，实际上使用的是HBase的Client API。

$ vi zlxx_test.json{  "job": {    "content": [      {        "reader": {          "name": "oraclereader",          "parameter": {            "column": [],            "connection": [              {                "fetchSize": "1024",                "jdbcUrl": [                  "$DW_ORCL_ZLXX_JDBCURL"                ],                "querySql": [                  "select id, trade_id, amount, status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id in ('1215529','1215528','1205528','1195531','1195530')"                ]              }            ],            "password": "$DW_ORCL_ZLXX_PASSWORD",            "username": "$DW_ORCL_ZLXX_USERNAME"          }        },        "writer": {          "name": "hbase11xwriter",          "parameter": {            "hbaseConfig": {              "hbase.zookeeper.quorum": "192.168.10.211:12181,192.168.10.212:12181,192.168.10.213:12181"            },            "table": "ZLXX:WT_TRADE_REFUND",            "mode": "normal",            "nullMode": "empty",            "rowkeyColumn": [              {                "index":0,                "type":"string"              }            ],            "column": [              {                "index":1,                "name": "INFO:TRADE_ID",                "type": "string"              },              {                "index":2,                "name": "INFO:AMOUNT",                "type": "string"              },              {                "index":3,                "name": "INFO:STATUS",                "type": "string"              },              {                "index":4,                "name": "INFO:MODIFY_TIME",                "type": "string"              }            ],            "encoding": "utf-8"          }        }      }    ],    "setting": {      "speed": {        "channel": "10"      }    }  }}

数据采集Shell脚本

$ vi zlxx_test.shsource ~/.bash_profilepython /zlxx/datax/bin/datax.py -j "-Xms1g -Xmx1g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/zlxx/datax/log" -p"-DDW_ORCL_ZLXX_JDBCURL=$DW_ORCL_ZLXX_JDBCURL -DDW_ORCL_ZLXX_PASSWORD=$DW_ORCL_ZLXX_PASSWORD -DDW_ORCL_ZLXX_USERNAME=$DW_ORCL_ZLXX_USERNAME" zlxx_test.json

执行脚本同步数据

$ sh zlxx_test.sh......任务启动时刻                    : 2020-08-10 14:29:41任务结束时刻                    : 2020-08-10 14:29:52任务总计耗时                    :                 10s任务平均流量                    :               17B/s记录写入速度                    :              0rec/s读出记录总数                    :                   5读写失败总数                    :                   0

分隔线 分隔线 分隔线 分隔线 分隔线 分隔线

经过以上步骤之后，查询数据：

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund limit 5;+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195530  | Z12193932  | 5.3     | 01      | 20200721170400  || 1195531  | Z12193932  | 5       | 01      | 20200721172800  || 1205528  | Z12193989  | .03     | 03      | 20200728145130  || 1215528  | Z12194190  | .01     | 01      | 20200805181300  || 1215529  | Z12194202  | 5       | 01      | 20200807154300  |+----------+------------+---------+---------+-----------------+5 rows selected (0.111 seconds)

首先查看一下主键的查询执行计划和查询效果：

0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where id='1195530';+-------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                                    PLAN                                                     | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+-------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK 1 ROWS 403 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 1 KEY OVER ZLXX:WT_TRADE_REFUND  | 403             | 1              | 0            |+-------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+1 row selected (0.053 seconds)

PARALLELx-WAY—describes how many parallel scans will be merge sorted during the operation.

ROUND ROBIN—when the query doesn’t contain ORDER BY and therefore the rows can be returned in any order, ROUND ROBIN order maximizes parallelization on the client side.

Phoenix and HBase work well when your application does point lookups and small range scans.

根据主键ID执行查询：

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where id='1195530';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195530  | Z12193932  | 5.3     | 01      | 20200721170400  |+----------+------------+---------+---------+-----------------+1 row selected (0.048 seconds)

创建索引、查看执行计划并查询数据：

没有二级索引的执行计划(FULL SCAN)

时间范围查询，FULL SCAN

0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where modify_time>='20200801' and modify_time<'20200806';+------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                           PLAN                                           | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ZLXX:WT_TRADE_REFUND            | null            | null           | null         ||     SERVER FILTER BY (INFO.MODIFY_TIME >= '20200801' AND INFO.MODIFY_TIME < '20200806')  | null            | null           | null         |+------------------------------------------------------------------------------------------+-----------------+----------------+--------------+

带上状态，FULL SCAN

0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where modify_time>='20200728' and modify_time<'20200806' and status='01';+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                                      PLAN                                                       | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ZLXX:WT_TRADE_REFUND                                   | null            | null           | null         ||     SERVER FILTER BY (INFO.MODIFY_TIME >= '20200728' AND INFO.MODIFY_TIME < '20200806' AND INFO.STATUS = '01')  | null            | null           | null         |+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+2 rows selected (0.03 seconds)

创建覆盖索引，并满足查询全部字段数据的需求

CREATE INDEX IDX_WT_TRADE_REFUND ON ZLXX.WT_TRADE_REFUND(MODIFY_TIME,STATUS) INCLUDE(ID,TRADE_ID,AMOUNT); 0: jdbc:phoenix:> CREATE INDEX IDX_WT_TRADE_REFUND ON ZLXX.WT_TRADE_REFUND(MODIFY_TIME,STATUS) INCLUDE(ID,TRADE_ID,AMOUNT);5 rows affected (6.529 seconds)

查看执行计划，时间范围(RANGE SCAN)

0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where modify_time>='20200801' and modify_time<'20200806';+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                                      PLAN                                                       | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER ZLXX:IDX_WT_TRADE_REFUND ['20200801'] - ['20200806']  | null            | null           | null         |+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+1 row selected (0.035 seconds)

带上状态(SKIP SCAN)

Phoenix uses Skip Scan for intra-row scanning which allows for significant performance improvement over Range Scan when rows are retrieved based on a given set of keys. 0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where modify_time>='20200728' and modify_time<'20200806' and status='01';+-------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                                                PLAN                                                                 | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+-------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN SKIP SCAN ON 1 RANGE OVER ZLXX:IDX_WT_TRADE_REFUND ['20200728','01'] - ['20200806','01']  | null            | null           | null         |+-------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+1 row selected (0.043 seconds)

执行查询均正常

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='20200801' and modify_time<'20200806';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1215528  | Z12194190  | .01     | 01      | 20200805181300  |+----------+------------+---------+---------+-----------------+1 row selected (0.05 seconds) 0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='20200728' and modify_time<'20200806' and status='01';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1215528  | Z12194190  | .01     | 01      | 20200805181300  |+----------+------------+---------+---------+-----------------+1 row selected (0.048 seconds)

分隔线 分隔线 分隔线 分隔线 分隔线 分隔线

现在使用DataX往HBase采集一条新数据和变更一条旧数据，采集SQL如下(脚本执行略)

select id, trade_id, amount, status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id='1195529' union all select id, trade_id, amount, '07' AS status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id='1215529'

执行同步脚本

$ sh zlxx_test.sh...任务启动时刻                    : 2020-08-10 15:21:40任务结束时刻                    : 2020-08-10 15:21:51任务总计耗时                    :                 10s任务平均流量                    :                6B/s记录写入速度                    :              0rec/s读出记录总数                    :                   2读写失败总数                    :                   0

不使用任何索引进行查询(查不出增量数据)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund limit 6;+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195530  | Z12193932  | 5.3     | 01      | 20200721170400  || 1195531  | Z12193932  | 5       | 01      | 20200721172800  || 1205528  | Z12193989  | .03     | 03      | 20200728145130  || 1215528  | Z12194190  | .01     | 01      | 20200805181300  || 1215529  | Z12194202  | 5       | 01      | 20200807154300  |+----------+------------+---------+---------+-----------------+5 rows selected (0.045 seconds)

Phoenix查询结果还是只有5条数据，新插入的数据是没有的，而且ID='1215529'的STATUS字段并没有变更为'07'。

hbase shell【新数据是有的，旧数据也是正常变更了的】:

hbase(main):001:0> get 'ZLXX:WT_TRADE_REFUND','1215529'COLUMN                                          CELL                                                                                                                                        INFO:AMOUNT                                    timestamp=1597044103540, value=5                                                                                                            INFO:MODIFY_TIME                               timestamp=1597044103540, value=20200807154300                                                                                               INFO:STATUS                                    timestamp=1597044103540, value=07                                                                                                           INFO:TRADE_ID                                  timestamp=1597044103540, value=Z12194202                                                                                                   1 row(s) in 0.4170 seconds hbase(main):002:0> get 'ZLXX:WT_TRADE_REFUND','1195529'COLUMN                                          CELL                                                                                                                                        INFO:AMOUNT                                    timestamp=1597044103540, value=10                                                                                                           INFO:MODIFY_TIME                               timestamp=1597044103540, value=20200721164730                                                                                               INFO:STATUS                                    timestamp=1597044103540, value=01                                                                                                           INFO:TRADE_ID                                  timestamp=1597044103540, value=Z12193930                                                                                                   1 row(s) in 0.0210 seconds

使用ID查询变更的数据(正常)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where id='1215529';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1215529  | Z12194202  | 5       | 07      | 20200807154300  |+----------+------------+---------+---------+-----------------+1 row selected (0.07 seconds)

使用ID查询新增的数据(正常)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where id='1195529';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195529  | Z12193930  | 10      | 01      | 20200721164730  |+----------+------------+---------+---------+-----------------+1 row selected (0.045 seconds)

使用时间范围查询新增的数据(查不出来)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='2020072116' and modify_time<'2020072117';+-----+-----------+---------+---------+--------------+| ID  | TRADE_ID  | AMOUNT  | STATUS  | MODIFY_TIME  |+-----+-----------+---------+---------+--------------++-----+-----------+---------+---------+--------------+No rows selected (0.044 seconds)

这里想到了重建索引，先删除索引再创建索引，肯定是可以的(不做演示)。

下面尝试第二种方法，通过ALTER语法进行索引重建

ALTER INDEX IDX_WT_TRADE_REFUND ON ZLXX.WT_TRADE_REFUND REBUILD; 0: jdbc:phoenix:> ALTER INDEX IDX_WT_TRADE_REFUND ON ZLXX.WT_TRADE_REFUND REBUILD;6 rows affected (5.071 seconds)

诡异的事情发生了！！！只查出来两条数据！！！

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund limit 6;+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195529  | Z12193930  | 10      | 01      | 20200721164730  || 1215529  | Z12194202  | 5       | 07      | 20200807154300  |+----------+------------+---------+---------+-----------------+2 rows selected (0.047 seconds)

时间范围查询新增数据(正常)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='2020072116' and modify_time<'2020072117';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195529  | Z12193930  | 10      | 01      | 20200721164730  |+----------+------------+---------+---------+-----------------+1 row selected (0.037 seconds)

但是原来其他的数据都查不出来了！！！

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='20200728' and modify_time<'20200806' and status='01';+-----+-----------+---------+---------+--------------+| ID  | TRADE_ID  | AMOUNT  | STATUS  | MODIFY_TIME  |+-----+-----------+---------+---------+--------------++-----+-----------+---------+---------+--------------+No rows selected (0.037 seconds)

实际上数据仍然是有的

hbase(main):001:0> get 'ZLXX:WT_TRADE_REFUND','1195530'COLUMN                                          CELL                                                                                                                                        INFO:AMOUNT                                    timestamp=1597040984132, value=5.3                                                                                                          INFO:MODIFY_TIME                               timestamp=1597040984132, value=20200721170400                                                                                               INFO:STATUS                                    timestamp=1597040984132, value=01                                                                                                           INFO:TRADE_ID                                  timestamp=1597040984132, value=Z12193932                                                                                                   1 row(s) in 0.3980 seconds

咋整？？？

随着增量数据的不断增加，存量数据也会越积越多，先删除索引再创建索引显然不可行，索引rebuild也不可行。

因此，放弃HBase API和Phoenix混用吧。

数据入口和出口都统一从Phoenix进和出。

分隔线 分隔线 分隔线 分隔线 分隔线 分隔线

DataX是支持phoenix jdbc方式写数据的。

https://github.com/alibaba/DataX

https://github.com/alibaba/DataX/blob/master/hbase11xsqlwriter/doc/hbase11xsqlwriter.md

重建数据表，先不创建索引，再试试rebuild重建索引的操作。

./sqlline.py：

drop table ZLXX.WT_TRADE_REFUND;

hbase shell：

create 'ZLXX:WT_TRADE_REFUND', 'INFO'

./sqlline.py：

create table ZLXX.WT_TRADE_REFUND (    id varchar primary key,    info.trade_id varchar,    info.amount  varchar,    info.status varchar,    info.modify_time varchar) column_encoded_bytes=0;

数据采集DataX配置(hbase11xsqlwriter)

$ vi zlxx_test_phoenix.json{  "job": {    "content": [      {        "reader": {          "name": "oraclereader",          "parameter": {            "column": [],            "connection": [              {                "fetchSize": "1024",                "jdbcUrl": [                  "$DW_ORCL_ZLXX_JDBCURL"                ],                "querySql": [                  "select id, trade_id, amount, status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id in ('1215529','1215528','1205528','1195531','1195530')"                ]              }            ],            "password": "$DW_ORCL_ZLXX_PASSWORD",            "username": "$DW_ORCL_ZLXX_USERNAME"          }        },        "writer": {          "name": "hbase11xsqlwriter",          "parameter": {            "batchSize": "256",            "hbaseConfig": {              "hbase.zookeeper.quorum": "192.168.13.219,192.168.13.220,192.168.13.221:12181",              "zookeeper.znode.parent": "/hbase"            },            "phoenixConfig": {                "phoenix.schema.isNamespaceMappingEnabled": "true",                "phoenix.schema.mapSystemTablesToNamespace": "true"            },            "table": "ZLXX:WT_TRADE_REFUND",            "nullMode": "empty",            "column": [              "ID",              "TRADE_ID",              "AMOUNT",              "STATUS",              "MODIFY_TIME"            ],            "encoding": "utf-8"          }        }      }    ],    "setting": {      "speed": {        "channel": "10"      }    }  }}

shell同步脚本

$ vi zlxx_test_phoenix.shsource ~/.bash_profilepython /zlxx/datax/bin/datax.py -j "-Xms1g -Xmx1g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/zlxx/datax/log" -p"-DDW_ORCL_ZLXX_JDBCURL=$DW_ORCL_ZLXX_JDBCURL -DDW_ORCL_ZLXX_PASSWORD=$DW_ORCL_ZLXX_PASSWORD -DDW_ORCL_ZLXX_USERNAME=$DW_ORCL_ZLXX_USERNAME" zlxx_test_phoenix.json

执行数据同步

$ sh zlxx_test_phoenix.sh...任务启动时刻                    : 2020-08-10 17:54:41任务结束时刻                    : 2020-08-10 17:55:22任务总计耗时                    :                 40s任务平均流量                    :               17B/s记录写入速度                    :              0rec/s读出记录总数                    :                   5读写失败总数                    :                   0

Phoenix jdbc写数据相对来说要慢一些？！

直接创建索引

0: jdbc:phoenix:> CREATE INDEX IDX_WT_TRADE_REFUND ON ZLXX.WT_TRADE_REFUND(MODIFY_TIME,STATUS) INCLUDE(ID,TRADE_ID,AMOUNT);

采集增量数据(SQL)

select id, trade_id, amount, status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id='1195529' union all select id, trade_id, amount, '07' AS status, TO_CHAR(modify_time,'YYYYMMDDhh24miss') AS modify_time from zlxx.trade_refund where id='1215529'

执行数据同步

$ sh zlxx_test_phoenix.sh...任务启动时刻                    : 2020-08-11 14:47:15任务结束时刻                    : 2020-08-11 14:47:31任务总计耗时                    :                 16s任务平均流量                    :                6B/s记录写入速度                    :              0rec/s读出记录总数                    :                   2读写失败总数                    :                   0

查询数据(正常显示全部增量：变更和新增)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund limit 6;+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195529  | Z12193930  | 10      | 01      | 20200721164730  || 1195530  | Z12193932  | 5.3     | 01      | 20200721170400  || 1195531  | Z12193932  | 5       | 01      | 20200721172800  || 1205528  | Z12193989  | .03     | 03      | 20200728145130  || 1215528  | Z12194190  | .01     | 01      | 20200805181300  || 1215529  | Z12194202  | 5       | 07      | 20200807154300  |+----------+------------+---------+---------+-----------------+6 rows selected (0.072 seconds)

时间范围查询

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='20200721' and modify_time<'20200722';+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1195529  | Z12193930  | 10      | 01      | 20200721164730  || 1195530  | Z12193932  | 5.3     | 01      | 20200721170400  || 1195531  | Z12193932  | 5       | 01      | 20200721172800  |+----------+------------+---------+---------+-----------------+3 rows selected (0.078 seconds)

执行计划

时间范围RANGE SCAN

0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where modify_time>='20200721' and modify_time<'20200722';+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                                      PLAN                                                       | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER ZLXX:IDX_WT_TRADE_REFUND ['20200721'] - ['20200722']  | null            | null           | null         |+-----------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+1 row selected (0.079 seconds)

时间范围，带上状态

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund where modify_time>='20200715' and modify_time<'20200809' and status in ('03', '07');+----------+------------+---------+---------+-----------------+|    ID    |  TRADE_ID  | AMOUNT  | STATUS  |   MODIFY_TIME   |+----------+------------+---------+---------+-----------------+| 1205528  | Z12193989  | .03     | 03      | 20200728145130  || 1215529  | Z12194202  | 5       | 07      | 20200807154300  |+----------+------------+---------+---------+-----------------+2 rows selected (0.052 seconds)

执行计划

时间范围，使用in带上状态SKIP SCAN

0: jdbc:phoenix:> explain select * from zlxx.wt_trade_refund where modify_time>='20200715' and modify_time<'20200809' and status in ('03', '07');+--------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+|                                                                 PLAN                                                                 | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+--------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN SKIP SCAN ON 2 RANGES OVER ZLXX:IDX_WT_TRADE_REFUND ['20200715','03'] - ['20200809','07']  | null            | null           | null         |+--------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+1 row selected (0.043 seconds)

【END】

往期推荐：

元数据中⼼的关键⽬标和技术实现⽅案

Hive程序相关规范-有助于调优

HBase内部探险-数据模型

HBase内部探险-HBase是怎么存储数据的

HBase内部探险-一个KeyValue的历险

数据中台到底怎么建设呢？

到底什么样的企业应该建设数据中台？

数据中台到底是不是大数据的下一站？

StreamSets实时采集MySQL数据到HBase

Phoenix Java API配置及使用总结

Phoenix表映射

Phoenix视图映射

Kafka消息送达语义说明

Kafka基础知识总结

Hadoop YARN：ApplicationMaster向ResourceManager注册AM源码调试

Apache Hadoop YARN：ClientResourceManager源码解析

Apache Hadoop YARN：ClientResourceManager源码DEBUG

Hadoop YARN：ApplicationMaster与ResourceManager交互源码解析

Hive企业级调优

HiveQL查询连续三天有销售记录的店铺

HiveQL实战蚂蚁森林低碳用户排名分析：解法一

HiveQL实战蚂蚁森林低碳用户排名分析：解法二

HiveQL实战蚂蚁森林植物申领统计分析

Hive-函数

Hive-查询

Hive-DML(Data Manipulation Language)数据操作语言

Hive-DDL(Data Definition Language)数据定义

Hive优化(整理版)

Spark Core之Shuffle解析

数据仓库开发规范

ceedc348fdcf1e4a664d7aa3087767ee.png

04fb71276368c378773dee1e3a946c3f.gif

http://www.lbrq.cn/news/1377919.html

相关文章：

  • 在网站底部给网站地图做链接/东莞网站seo推广
  • 推广网站怎么做模版/首页优化排名
  • 做酒店的网站/北京网讯百度科技有限公司
  • 桂林小学网站建设/西安seo外包公司
  • wordpress中文url/重庆seo网站系统
  • 如何做平台网站/今天今日头条新闻
  • 网站建设制作设计seo优化珠海/百度搜索官方网站
  • 多语言网站建设/广告seo是什么意思
  • 东莞网站建设哪家专业/软文推广文案
  • 软件开发网站能做seo吗/郑州百度推广开户
  • 做网站真辛苦/宁德seo公司
  • 武汉做网站的有哪些/小程序开发公司前十名
  • 河南网站建设的详细策划/网站制作app免费软件
  • 高端网站建设公司哪家专业靠谱/网站流量统计系统
  • 农特产品电商网站建设目标/李江seo
  • 微博分享的网站怎么做/成都seo达人
  • net网站建设/北京公司排名seo
  • 广告设计与制作主修课程有哪些/百度站长工具seo查询
  • 品牌网站建设h5/搜索引擎的设计与实现
  • 推销产品什么网站好/郑州百度网站快速优化
  • 代理注册公司网站模版/搜索引擎优化的方法和技巧
  • 三门峡网站建设/杭州云优化信息技术有限公司
  • 郑州 网站报价/软文文章
  • 滨海县建设局网站/搜索引擎优化的作用是什么
  • 建设英文网站的申请怎么写/长春百度seo公司
  • 郴州宜章疫情最新情况/谷歌seo是指什么意思
  • 无锡企业制作网站/企业查询软件
  • 帮客户做网站挣钱吗/信息流优化师培训机构
  • 网站开发感受/seo是怎么优化上去
  • 域名同时做邮箱和网站/发广告去哪个平台
  • 对于考研数学的理解
  • 操作系统：远程过程调用（ Remote Procedure Call，RPC）
  • 福彩双色球第2025088期篮球号码分析
  • 服务器地域选择指南：深度分析北京/上海/广州节点对网站速度的影响
  • USRP捕获手机/路由器数据传输信号波形（下）
  • 【LeetCode 热题 100】4. 寻找两个正序数组的中位数——（解法一）线性扫描