wiki:HyperTable/HQLTutorial

HQL Tutorial

Tutorial from 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
Last modified 16 years ago Last modified on Mar 19, 2008, 6:15:18 PM