开源云计算技术系列（六）hypertable (HQL)-优快云博客

准备好实验数据

hadoop@hadoop:~$ gunzip access.tsv.gz
hadoop@hadoop:~$ mv access.tsv ~/hypertable/0.9.2.5/examples/hql_tutorial/
hadoop@hadoop:~$ cd ~/hypertable/0.9.2.5/examples/hql_tutorial/
hadoop@hadoop:~/hypertable/0.9.2.5/examples/hql_tutorial$
hadoop@hadoop:~/hypertable/0.9.2.5/examples/hql_tutorial$ more access.tsv
# rowkey date refer-url http-code events.mercurynews.com/venues 2008-01-25 15:19:32 events.mercurynews.com/search 200 www.zvents.com/events/auto_complete_for_artist_name 2008-01-25 15:19:32 www.zvents.co m/indio-ca/events/show/81296496-coachella 200 calendar.denverpost.com/search 2008-01-25 15:19:32 calendar.denverpost.com/search 200 www.zvents.com/search 2008-01-25 15:19:32 www.zvents.com/search 200 events.newsherald.com/events/show/81138187 2008-01-25 15:19:34 events.newsherald.com /venues 301 www.zvents.com/search 2008-01-25 15:19:35 www.zvents.com/search 200 events.ocregister.com/search 2008-01-25 15:19:36 events.ocregister.com/ 200 events.mercurynews.com/search 2008-01-25 15:19:37 events.mercurynews.com/search 200 events.idahostatesman.com/search 2008-01-25 15:19:38 events.idahostatesman.com/sea rch 200 www.zvents.com/welcome/set_location 2008-01-25 15:19:38 www.zvents.com/movies/show/86 86-27-dresses 302

hadoop@hadoop:~/hypertable/0.9.2.5/log$ hypertable

Welcome to the hypertable command interpreter.
For information about Hypertable, visithttp://www.hypertable.org/

Type ‘help’ for a list of commands, or ‘help shell’ for a
list of shell meta commands.

hypertable> help

CREATE TABLE ……. Creates a table
DELETE …………. Deletes all or part of a row from a table
DESCRIBE TABLE ….. Displays a table’s schema
DROP TABLE ……… Removes a table
ALTER TABLE …….. Add/remove column family from existing table
INSERT …………. Inserts data into a table LOAD DATA INFILE … Loads data from a TSV input file into a table SELECT …………. Selects (and display) cells from a table SHOW CREATE TABLE .. Displays CREATE TABLE command used to create table SHOW TABLES …….. Displays the list of tables SHUTDOWN ……….. Shuts servers down gracefully

Statements must be terminated with ‘;’. For more information on
a specific statement, type ‘help <statement>’, where <statement> is from
the preceeding list.

hypertable> help shell

Interpreter Meta Commands
————————-
? (\?) Synonym for `help’.
clear (\c) Clear command.
exit (\q) Exit program. Same as quit.
print (\p) Print current command.
quit (\q) Quit program.
source <f> (.) Execute commands in file <f>.
system (\!) Execute a system shell command.

hypertable>

hypertable> create table Pages ( date, “refer-url”, “http-code”);

Elapsed time: 0.15 s
hypertable> show create table Pages;

CREATE TABLE Pages (
date,
‘refer-url’,
‘http-code’, ACCESS GROUP default (date, ‘refer-url’, ‘http-code’) )

Elapsed time: 0.01 s
hypertable> describe table Pages;
<Schema generation=”1″>
<AccessGroup name=”default”>
<ColumnFamily id=”1″>
<Generation>1</Generation>
<Name>date</Name>
<deleted>false</deleted>
</ColumnFamily>
<ColumnFamily id=”2″>
<Generation>1</Generation>
<Name>refer-url</Name>
<deleted>false</deleted>
</ColumnFamily>
<ColumnFamily id=”3″>
<Generation>1</Generation>
<Name>http-code</Name> <deleted>false</deleted> </ColumnFamily> </AccessGroup> </Schema>

Elapsed time: 0.00 s
hypertable>

hypertable> load data infile ROW_KEY_COLUMN=rowkey “examples/hql_tutorial/access.tsv” into table Pages;

Loading 10,872,957 bytes of input data…

0% 10 20 30 40 50 60 70 80 90 100%
|—-|—-|—-|—-|—-|—-|—-|—-|—-|—-|
***************************************************
Load complete.

Elapsed time: 4.41 s
Avg value size: 18.68 bytes
Avg key size: 48.70 bytes
Throughput: 4583496.26 bytes/s (2465499.65 bytes/s)
Total cells: 300000
Throughput: 68026.56 cells/s
Resends: 0
hypertable>

hypertable> load data infile ROW_KEY_COLUMN=rowkey “examples/hql_tutorial/access.tsv” into file “test.tsv”;

Loading 10,872,957 bytes of input data…

0% 10 20 30 40 50 60 70 80 90 100%
|—-|—-|—-|—-|—-|—-|—-|—-|—-|—-|
***************************************************
Load complete.

Elapsed time: 1.61 s
Avg value size: 18.68 bytes
Avg key size: 48.70 bytes
Throughput: 12586755.81 bytes/s (6770517.59 bytes/s)
Total cells: 300000
Throughput: 186807.99 cells/s

hypertable> quit
hadoop@hadoop:~/hypertable/0.9.2.5$ head -10 test.tsv
rowkey columnkey value
0×9db0140 date 2008-01-25 15:19:32
0×9db0140 refer-url events.mercurynews.com/search
0×9db0140 http-code 200 0×9db0170 date 2008-01-25 15:19:32 0×9db0170 refer-url www.zvents.com/indio-ca/events/show/81296496-coachella 0×9db0170 http-code 200 0×9db0170 date 2008-01-25 15:19:32 0×9db0170 refer-url calendar.denverpost.com/search 0×9db0170 http-code 200

hypertable> select ‘http-code’ from Pages where ROW=’events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar’; events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code 200 events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code 200 events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code 200 events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code 200

Elapsed time: 0.01 s
Avg value size: 3.00 bytes
Avg key size: 64.00 bytes
Throughput: 51937.98 bytes/s
Total cells: 4
Throughput: 775.19 cells/s

hypertable> select “refer-url” from Pages where ROW = “www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin” DISPLAY_TIMESTAMPS;
2009-08-18 14:03:49.166173771 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin refer-url www.google.com/search
2009-08-18 14:03:49.035165894 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin refer-url www.google.com/search
2009-08-18 14:03:48.606115876 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin refer-url www.zvents.com/search
2009-08-18 14:03:47.197737860 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin refer-url www.google.com/search
2009-08-18 14:03:47.197737857 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin refer-url www.google.com/search
2009-08-18 14:03:46.955504821 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin refer-url www.google.com/search

Elapsed time: 0.01 s
Avg value size: 21.00 bytes
Avg key size: 66.00 bytes
Throughput: 63821.98 bytes/s
Total cells: 6
Throughput: 733.59 cells/s

hypertable> select “http-code” from Pages where ROW = “www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin” DISPLAY_TIMESTAMPS; 2009-08-18 14:03:49.166173772 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:49.035165895 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:48.606115877 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:47.197737861 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:47.197737858 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:46.955504822 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200

Elapsed time: 0.00 s
Avg value size: 3.00 bytes
Avg key size: 66.00 bytes
Throughput: 127032.83 bytes/s
Total cells: 6
Throughput: 1841.06 cells/s

hypertable> select “http-code” from Pages where ROW = “www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin” AND TIMESTAMP >= ‘2008-02-02 00:00:00′ DISPLAY_TIMESTAMPS; 2009-08-18 14:03:49.166173772 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:49.035165895 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:48.606115877 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:47.197737861 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:47.197737858 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200 2009-08-18 14:03:46.955504822 www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin http-code 200

Elapsed time: 0.01 s
Avg value size: 3.00 bytes
Avg key size: 66.00 bytes
Throughput: 68238.01 bytes/s
Total cells: 6
Throughput: 988.96 cells/s

hypertable> insert into Pages values (’2006-01-01 23:59:59′, “www.hypertable.org”, “refer-url”, “www.zvents.com”);

Elapsed time: 0.02 s
Avg value size: 14.00 bytes
Total cells: 1
Throughput: 53.94 cells/s
Resends: 0
hypertable> select * from Pages where ROW = “www.hypertable.org” DISPLAY_TIMESTAMPS;
2006-01-01 23:59:59.000000000 www.hypertable.org refer-url www.zvents.com

Elapsed time: 0.01 s
Avg value size: 14.00 bytes
Avg key size: 19.00 bytes
Throughput: 5936.32 bytes/s
Total cells: 1
Throughput: 179.89 cells/s

hypertable> delete * from Pages where ROW = “www.hypertable.org”;

Elapsed time: 0.01 s
Resends: 0
hypertable> select * from Pages where ROW = “www.hypertable.org”;

Elapsed time: 0.01 s

hypertable> insert into Pages values (’2037-01-01 23:59:59′, “www.hypertable.org”, “refer-url”, “www.zvents.com”);

Elapsed time: 0.02 s
Avg value size: 14.00 bytes
Total cells: 1
Throughput: 57.94 cells/s
Resends: 0
hypertable> delete * from Pages where ROW = “www.hypertable.org”;

Elapsed time: 0.01 s
Resends: 0
hypertable> select * from Pages where ROW = “www.hypertable.org” DISPLAY_TIMESTAMPS;
2037-01-01 23:59:59.000000000 www.hypertable.org refer-url www.zvents.com

Elapsed time: 0.00 s
Avg value size: 14.00 bytes
Avg key size: 19.00 bytes
Throughput: 2750000.00 bytes/s
Total cells: 1
Throughput: 83333.33 cells/s

hypertable> SHOW CREATE TABLE Pages;

Elapsed time: 0.02 s
hypertable> ALTER TABLE Pages ADD(’load-time’, ‘error-message’, ACCESS GROUP stats(’load-time’)) DROP (’http-code’);

Elapsed time: 0.04 s
hypertable> SHOW CREATE TABLE Pages;

CREATE TABLE Pages (
date,
‘refer-url’,
‘error-message’,
‘load-time’,
ACCESS GROUP default (date, ‘refer-url’, ‘http-code’, ‘error-message’), ACCESS GROUP stats (’load-time’) )

Elapsed time: 0.00 s

hypertable> describe table Pages;
<Schema generation=”2″>
<AccessGroup name=”default”>
<ColumnFamily id=”1″>
<Generation>1</Generation>
<Name>date</Name>
<deleted>false</deleted>
</ColumnFamily>
<ColumnFamily id=”2″>
<Generation>1</Generation>
<Name>refer-url</Name>
<deleted>false</deleted>
</ColumnFamily>
<ColumnFamily id=”3″>
<Generation>1</Generation>
<Name>http-code</Name> <deleted>true</deleted> </ColumnFamily> <ColumnFamily id=”5″> <Generation>2</Generation> <Name>error-message</Name> <deleted>false</deleted> </ColumnFamily> </AccessGroup> <AccessGroup name=”stats”> <ColumnFamily id=”4″> <Generation>2</Generation> <Name>load-time</Name> <deleted>false</deleted> </ColumnFamily> </AccessGroup> </Schema>

Elapsed time: 0.01 s
hypertable>

hypertable> show tables;
METADATA
Pages

Elapsed time: 0.01 s
hypertable> drop table Pages;

Elapsed time: 1.05 s
hypertable> show tables;
METADATA

Elapsed time: 0.00 s

hadoop@hadoop:~/hypertable/0.9.2.5$ hypertable

Welcome to the hypertable command interpreter.
For information about Hypertable, visithttp://www.hypertable.org/

Type ‘help’ for a list of commands, or ‘help shell’ for a
list of shell meta commands.

hypertable> source ./examples/hql_tutorial/Pages-create.hql;

Elapsed time: 1.11 s

Loading 10,872,957 bytes of input data…

0% 10 20 30 40 50 60 70 80 90 100%
|—-|—-|—-|—-|—-|—-|—-|—-|—-|—-|
***************************************************
Load complete.

Elapsed time: 3.65 s
Avg value size: 18.68 bytes
Avg key size: 48.70 bytes
Throughput: 5533544.02 bytes/s (2976538.01 bytes/s)
Total cells: 300000
Throughput: 82126.82 cells/s
Resends: 0

hypertable> drop table Pages;

Elapsed time: 0.05 s
hypertable> quit
hadoop@hadoop:~/hypertable/0.9.2.5$ hypertable –batch < examples/hql_tutorial/Pages-create.hql
hadoop@hadoop:~/hypertable/0.9.2.5$ hypertable

Welcome to the hypertable command interpreter.
For information about Hypertable, visithttp://www.hypertable.org/

Type ‘help’ for a list of commands, or ‘help shell’ for a
list of shell meta commands.

hypertable> show tables;
METADATA
Pages

Elapsed time: 0.01 s
hypertable>

更多的hql语法可以参考：http://www.hypertable.org/hql/index.html