开源云计算技术系列（六）hypertable (HQL)

既然已经安装配置好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, visit http://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 ’, where 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 (.)  Execute commands in file .
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;

 
   
      1
      date
      false
   
   
      1
      refer-url
      false
   
   
      1
      http-code
      false
   
 

  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
0x9db0140       date    2008-01-25 15:19:32
0x9db0140       refer-url       events.mercurynews.com/search
0x9db0140       http-code       200
0x9db0170       date    2008-01-25 15:19:32
0x9db0170       refer-url       www.zvents.com/indio-ca/events/show/81296496-coachella
0x9db0170       http-code       200
0x9db0170       date    2008-01-25 15:19:32
0x9db0170       refer-url       calendar.denverpost.com/search
0x9db0170       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;

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

  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;

 
   
      1
      date
      false
   
   
      1
      refer-url
      false
   
   
      1
      http-code
      true
   
   
      2
      error-message
      false
   
 
 
   
      2
      load-time
      false
   
 

  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, visit http://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, visit http://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

其他更多功能体验待续。