= HQL Tutorial =
== Tutorial from Google ==
 * HQL Tutorial: [http://code.google.com/p/hypertable/wiki/HQLTutorial Google]
== Our Test ==
 * Test from local drvie
  * CPU: Intel(R) Pentium(R) D CPU 3.40GHz * 2
  * Hard Disk: SCSI 150G * 1
  * Partition: /dev/sda1 mount on /
 * Instruction
  * Run Hypertable
{{{
sunny@hsihdp:~/hypertable/0.9.0.5$ bin/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.
}}}
  * Help Command
{{{
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
INSERT ............. Inserts data into a table
LOAD DATA INFILE ... Loads data from a tab delimited 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

Statements must be terminated with ';' to execute.  For more information on
a specific statement, type 'help <statement>', where <statement> is one from
the preceeding list.
}}}
  * Show "Pages" Table Schema
{{{
hypertable> show create table Pages;

CREATE TABLE Pages (
  'refer-url',
  'http-code',
  timestamp,
  rowkey,
  ACCESS GROUP default ( 'refer-url', 'http-code', timestamp, rowkey )
)
}}} 
  * Drop "Pages" Table
{{{
hypertable> drop table Pages;
hypertable> show create table Pages;

1205915835 ERROR hypertable : (/home/sunny/git/hypertable/src/cc/Hypertable/Lib/MasterClient.cc:114) Master 'get schema' error, tableName=Pages : HYPERSPACE bad pathname : Unable to open Hyperspace table file '/hypertable/tables/Pages' (HYPERSPACE bad pathname)
Error: Problem fetching schema for table 'Pages' from master - HYPERSPACE bad pathname
}}}
  * Create "Pages" Table
{{{
hypertable> create table Pages (date, "refer-url", "http-code");
}}}
  * Show "Pages" Table Schema
{{{
hypertable> show create table Pages;

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

hypertable> describe table Pages;

<Schema generation="1">
  <AccessGroup name="default">
    <ColumnFamily id="1">
      <Name>refer-url</Name>
    </ColumnFamily>
    <ColumnFamily id="2">
      <Name>http-code</Name>
    </ColumnFamily>
    <ColumnFamily id="3">
      <Name>date</Name>
    </ColumnFamily>
  </AccessGroup>
</Schema>
}}}
  * Load Sample Data into "Pages" Table
{{{
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.29 s
Avg value size:  18.68 bytes
  Avg key size:  48.70 bytes
    Throughput:  2534861.65 bytes/s
 Total inserts:  300000
    Throughput:  69940.36 inserts/s
       Resends:  0
}}}
  * Load Sample Data into File
{{{
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:  0.56 s
Avg value size:  18.68 bytes
  Avg key size:  48.70 bytes
    Throughput:  19275729.29 bytes/s
 Total inserts:  300000
    Throughput:  531844.17 inserts/s
}}}
  * Show top 10 lines of "test.tsv" File
{{{
sunny@hsihdp:~/hypertable/0.9.0.5$ head -10 test.tsv

rowkey  columnkey       value
events.mercurynews.com/venues   date    2008-01-25 15:19:32
events.mercurynews.com/venues   refer-url       events.mercurynews.com/search
events.mercurynews.com/venues   http-code       200
www.zvents.com/events/auto_complete_for_artist_name     date    2008-01-25 15:19:32
www.zvents.com/events/auto_complete_for_artist_name     refer-url       www.zvents.com/indio-ca/events/show/81296496-coachella
www.zvents.com/events/auto_complete_for_artist_name     http-code       200
calendar.denverpost.com/search  date    2008-01-25 15:19:32
calendar.denverpost.com/search  refer-url       calendar.denverpost.com/search
calendar.denverpost.com/search  http-code       200
}}}
  * HQL Command: SELECT
{{{
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
}}}
  * HQL Command: SELECT & Show Timestamps
{{{
hypertable> select "http-code" from Pages where ROW = "www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin" 
DISPLAY_TIMESTAMPS;

2008-03-19 08:38:53.546882123   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:53.546882120   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:52.898828170   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:51.670966231   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:51.670966230   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:51.430784186   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
}}}
  * HQL Command: SELECT with Timestamps filter
{{{
hypertable> select "http-code" from Pages where ROW = "www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin" && TIMESTAMP > '2008-02-02 15:53:00' && TIMESTAMP <= '2008-02-02 15:53:02' DISPLAY_TIMESTAMPS;

2008-03-19 08:38:53.546882123   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:53.546882120   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:52.898828170   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:51.670966231   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:51.670966230   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
2008-03-19 08:38:51.430784186   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
}}}