Open source Cloud Computing Technology Series (vi) hypertable (HQL)

Since the installation has been installed well hypertable, the Iron strike experience hql.

Prepare the experimental data.

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  
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      
calendar.denverpost.com/search  2008-01-25 15:19:32      calendar.denverpost.com/search 
www.zvents.com/search   2008-01-25 15:19:32     www.zvents.com/search  
events.newsherald.com/events/show/ 81138187      2008-01-25 15:19:34     events.newsherald.com
/ Venues
www.zvents.com/search   2008-01-25 15:19:35     www.zvents.com/search   
events.ocregister.com/search    2008-01-25 15:19:36     events.ocregister.com/ 
events.mercurynews.com/search   2008-01-25 15:19:37      events.mercurynews.com/search  
events.idahostatesman.com/search         2008-01-25 15:19:38     Events.idahostatesman.com/sea
rch     
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 a row from a table
DESCRIBE TABLE ... Displays a table ' s schema
DROP TABLE .... Removes a table
ALTER TABLE .... Add/remove column accessibility 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 moment-in be terminated with '; For more information on
A specific statement, type ' help <statement> ', where <statement> are from
The preceeding list.

Hypertable> Help Shell

Interpreter Meta Commands
-------------------------
? (\?) Synonym for ' help '.
Clear (\c) Clear command.
Exit (q) Exit program. Mahouve 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
0x9db0140       date    2008-01-25 15:19:32
0x9db0140       refer-url        Events.mercurynews.com/search
0x9db0140       http-code       
0x9db0170       date    2008-01-25 15:19:32
0x9db0170       refer-url       Www.zvents.com/indio-ca/events/show/81296496-coachella
0x9db0170       http-code      
0x9db0170       date     2008-01-25 15:19:32
0x9db0170       refer-url        Calendar.denverpost.com/search
0x9db0170       http-code       

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      
2009-08-18 14:03:49.035165895   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin        http-code      
2009-08-18 14:03:48.606115877    www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      
2009-08-18 14:03:47.197737861   www.zvents.com/ san-francisco-ca/events/show/80283482-steve-martin       http-code       
2009-08-18 14:03:47.197737858   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       
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 Unlimited ' display_timestamps;
2009-08-18 14:03:49.166173772   Www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin        http-code      
2009-08-18 14:03:49.035165895   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin        http-code      
2009-08-18 14:03:48.606115877    www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      
2009-08-18 14:03:47.197737861   www.zvents.com/ san-francisco-ca/events/show/80283482-steve-martin       http-code       
2009-08-18 14:03:47.197737858   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin        http-code      
2009-08-18 14:03:46.955504822   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       Http-code       

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;
<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, 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>

More HQL syntax can refer to: http://www.hypertable.org/hql/index.html

More features to be continued.