Skip to content

Instantly share code, notes, and snippets.

@winse

winse/phoenix4.hello.sql

Last active August 29, 2015 14:04
Show Gist options
  • Save winse/f36126a2f07de8303c38 to your computer and use it in GitHub Desktop.
Save winse/f36126a2f07de8303c38 to your computer and use it in GitHub Desktop.
Download ZIP
phoenix4使用笔记
Raw
phoenix4.hello.sql
$ [hadoop@umcc97-44 phoenix-4.0.0-incubating]$ bin/sqlline.py localhost
/*
hbase(main):020:0> describe 't1'
DESCRIPTION ENABLED
't1', {NAME => 'f1', DATA_BLOCK_ENCODING => 'NONE', BLOOMFILTER => 'ROW', REPLICATION_SCOPE => '0', VERSIONS => '1', COMPRESS true
ION => 'NONE', MIN_VERSIONS => '0', TTL => 'FOREVER', KEEP_DELETED_CELLS => 'false', BLOCKSIZE => '65536', IN_MEMORY => 'fals
e', BLOCKCACHE => 'true', METADATA => {'ENCODE_ON_DISK' => 'true'}}
1 row(s) in 0.0830 seconds
hbase(main):022:0> scan 't1', LIMIT=>1
ROW COLUMN+CELL
...
rowid0 column=f1:domain, timestamp=1405654826900, value=minmin0
rowid0 column=f1:houseId, timestamp=1405654826900, value=minmin0
rowid0 column=f1:logId, timestamp=1405654826900, value=minmin0
rowid0 column=f1:name, timestamp=1405654826900, value=minmin0
...
rowid0 column=f1:url, timestamp=1405654772395, val
*/
/* 默认为大写，如果表名字段名为小写需要加双引号 */
create view "t1" ( pk varchar primary key, "f1".name varchar );
alter table "t1" add "f1"."name" varchar;
create table "t1" ( pk varchar primary key, "f1"."name" varchar, "f1"."domain" varchar, "f1"."houseId" varchar, "f1"."url" varchar, "f1"."logId" varchar );
0: jdbc:phoenix:localhost> select count(*) from "t1"
. . . . . . . . . . . . .> ;
+------------+
| COUNT(1) |
+------------+
| 1000000 |
+------------+
1 row selected (8.016 seconds)
0: jdbc:phoenix:localhost> select count(*) from "t1" where "name" > 'minmin55';
+------------+
| COUNT(1) |
+------------+
| 499998 |
+------------+
1 row selected (9.56 seconds)
/*
<!-- phoenix for -->
<property>
<name>hbase.regionserver.wal.codec</name>
<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
http://phoenix.apache.org/language/index.html#create_index
*/
0: jdbc:phoenix:localhost> create index domain_t1 on "t1" ( "domain" desc );
1,000,000 rows affected (113.989 seconds)
0: jdbc:phoenix:localhost> select count(*) from "t1" where "domain" > 'minmin';
+------------+
| COUNT(1) |
+------------+
| 0 |
+------------+
1 row selected (0.494 seconds)
0: jdbc:phoenix:localhost> select count(*) from "t1" where "domain" < 'minmin';
+------------+
| COUNT(1) |
+------------+
| 1000000 |
+------------+
1 row selected (4.889 seconds)
0: jdbc:phoenix:localhost> select count(*) from "t1" where "domain" < 'minmin' limit 5;
+------------+
| COUNT(1) |
+------------+
| 1000000 |
+------------+
1 row selected (4.037 seconds)
0: jdbc:phoenix:localhost> select * from "t1" where "domain" < 'minmin' limit 5;
+------------+------------+------------+------------+------------+------------+
| PK | name | domain | houseId | url | logId |
+------------+------------+------------+------------+------------+------------+
+------------+------------+------------+------------+------------+------------+
No rows selected (8.421 seconds)
0: jdbc:phoenix:localhost> select * from "t1" where "domain" > 'minmin' limit 5;
+------------+------------+------------+------------+------------+------------+
| PK | name | domain | houseId | url | logId |
+------------+------------+------------+------------+------------+------------+
| rowid0 | minmin0 | minmin0 | minmin0 | |
| rowid1 | minmin1 | minmin1 | minmin1 | |
| rowid10 | minmin10 | minmin10 | minmin10 | |
| rowid100 | minmin100 | minmin100 | minmin100 | |
| rowid1000 | minmin1000 | minmin1000 | minmin1000 | |
+------------+------------+------------+------------+------------+------------+
5 rows selected (0.428 seconds)
0: jdbc:phoenix:localhost> explain select * from "t1" where "domain" > 'minmin' limit 5;
+------------+
| PLAN |
+------------+
| CLIENT PARALLEL 32-WAY FULL SCAN OVER t1 |
| SERVER FILTER BY f1.domain > 'minmin' |
| SERVER 5 ROW LIMIT |
| CLIENT 5 ROW LIMIT |
+------------+
4 rows selected (0.066 seconds)
0: jdbc:phoenix:localhost> explain select count(*) from "t1" where "domain" > 'minmin' limit 5;
+------------+
| PLAN |
+------------+
| CLIENT PARALLEL 1-WAY RANGE SCAN OVER DOMAIN_T1 [*] - [~'minmin'] |
| SERVER AGGREGATE INTO SINGLE ROW |
| CLIENT 5 ROW LIMIT |
+------------+
3 rows selected (0.066 seconds)
// 上面的结果很诧异，[count(*)]和查询记录内容[*]反过来了！建索引的时刻还是注意点下最好是正序的！
//
////// 再查看索引表：
hbase(main):029:0> scan 'DOMAIN_T1' , {LIMIT => 5}
ROW COLUMN+CELL
\x92\x96\x91\x92\x96\x91\xC6\x00rowid9 column=0:_0, timestamp=1405661150064, value=
\x92\x96\x91\x92\x96\x91\xC6\xC6\x00rowid99 column=0:_0, timestamp=1405661150064, value=
\x92\x96\x91\x92\x96\x91\xC6\xC6\xC6\x00rowid999 column=0:_0, timestamp=1405661150064, value=
\x92\x96\x91\x92\x96\x91\xC6\xC6\xC6\xC6\x00rowid column=0:_0, timestamp=1405661150064, value=
9999
\x92\x96\x91\x92\x96\x91\xC6\xC6\xC6\xC6\xC6\x00r column=0:_0, timestamp=1405661150064, value=
owid99999
5 row(s) in 0.0350 seconds
hbase(main):030:0> scan 'DOMAIN_T1' , {LIMIT => 5, REVERSED=>true}
ROW COLUMN+CELL
\x92\x96\x91\x92\x96\x91\xCF\x00rowid0 column=0:_0, timestamp=1405661150064, value=
\x92\x96\x91\x92\x96\x91\xCE\xCF\xCF\xCF\xCF\xCF\ column=0:_0, timestamp=1405661150064, value=
x00rowid100000
\x92\x96\x91\x92\x96\x91\xCE\xCF\xCF\xCF\xCF\xCE\ column=0:_0, timestamp=1405661150064, value=
x00rowid100001
\x92\x96\x91\x92\x96\x91\xCE\xCF\xCF\xCF\xCF\xCD\ column=0:_0, timestamp=1405661150064, value=
x00rowid100002
\x92\x96\x91\x92\x96\x91\xCE\xCF\xCF\xCF\xCF\xCC\ column=0:_0, timestamp=1405661150064, value=
x00rowid100003
5 row(s) in 0.3020 seconds
hbase(main):031:0> SCAN 't1', {LIMIT => 5, REVERSED=>true}
NoMethodError: undefined method `SCAN' for #<Object:0x1dd9cc1a>
hbase(main):032:0> scan 't1', {LIMIT => 5, REVERSED=>true}
ROW COLUMN+CELL
rowid999999 column=f1:_0, timestamp=1405654996245, value=
rowid999999 column=f1:accessTime, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:destinationIP, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:destinationPort, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:domain, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:houseId, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:logId, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:name, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:sourceIP, timestamp=1405654996245, value=minmin999999
rowid999999 column=f1:sourcePort, timestamp=1405654996245, value=minmin999999
rowid999998 column=f1:_0, timestamp=1405654996245, value=
rowid999998 column=f1:accessTime, timestamp=1405654996245, value=minmin999998
rowid999998 column=f1:destinationIP, timestamp=1405654996245, value=minmin999998
rowid999998 column=f1:destinationPort, timestamp=1405654996245, value=minmin999998
rowid999998 column=f1:domain, timestamp=1405654996245, value=minmin999998
Raw
phoenix4.use.sql
0: jdbc:phoenix:localhost> create view "t_ods_idc_isp_log2" ( pk varchar primary key,
. . . . . . . . . . . . .> "cf"."attachmentfileName" varchar ,
. . . . . . . . . . . . .> "cf"."attachmentfileNum" varchar ,
. . . . . . . . . . . . .> "cf"."commandld" varchar ,
. . . . . . . . . . . . .> "cf"."content" varchar ,
. . . . . . . . . . . . .> "cf"."destIp" varchar ,
. . . . . . . . . . . . .> "cf"."destPort" varchar ,
. . . . . . . . . . . . .> "cf"."deviceSerialNo" varchar ,
. . . . . . . . . . . . .> "cf"."domainName" varchar ,
. . . . . . . . . . . . .> "cf"."fDIID" varchar ,
. . . . . . . . . . . . .> "cf"."gatherTime" varchar ,
. . . . . . . . . . . . .> "cf"."houseId" varchar ,
. . . . . . . . . . . . .> "cf"."logId" varchar ,
. . . . . . . . . . . . .> "cf"."logType" varchar ,
. . . . . . . . . . . . .> "cf"."manufactureID" varchar ,
. . . . . . . . . . . . .> "cf"."operationTime" varchar ,
. . . . . . . . . . . . .> "cf"."operator" varchar ,
. . . . . . . . . . . . .> "cf"."protoSignature" varchar ,
. . . . . . . . . . . . .> "cf"."proxyIp" varchar ,
. . . . . . . . . . . . .> "cf"."proxyPort" varchar ,
. . . . . . . . . . . . .> "cf"."proxyType" varchar ,
. . . . . . . . . . . . .> "cf"."proxyTypeFlag" varchar ,
. . . . . . . . . . . . .> "cf"."regError" varchar ,
. . . . . . . . . . . . .> "cf"."serviceContent" varchar ,
. . . . . . . . . . . . .> "cf"."srcIp" varchar ,
. . . . . . . . . . . . .> "cf"."srcPort" varchar ,
. . . . . . . . . . . . .> "cf"."timeDetected" varchar ,
. . . . . . . . . . . . .> "cf"."title" varchar ,
. . . . . . . . . . . . .> "cf"."type" varchar ,
. . . . . . . . . . . . .> "cf"."url" varchar ,
. . . . . . . . . . . . .> "cf"."ver" varchar
. . . . . . . . . . . . .> );
No rows affected (3.596 seconds)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment