Changes between Initial Version and Version 1 of HyperTable/HQLTutorial


Ignore:
Timestamp:
Mar 19, 2008, 6:15:18 PM (16 years ago)
Author:
sunny
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • HyperTable/HQLTutorial

    v1 v1  
     1= HQL Tutorial =
     2== Tutorial from Google ==
     3 * HQL Tutorial: [http://code.google.com/p/hypertable/wiki/HQLTutorial Google]
     4== Our Test ==
     5 * Test from local drvie
     6  * CPU: Intel(R) Pentium(R) D CPU 3.40GHz * 2
     7  * Hard Disk: SCSI 150G * 1
     8  * Partition: /dev/sda1 mount on /
     9 * Instruction
     10  * Run Hypertable
     11{{{
     12sunny@hsihdp:~/hypertable/0.9.0.5$ bin/hypertable
     13
     14Welcome to the hypertable command interpreter.
     15For information about Hypertable, visit http://www.hypertable.org/
     16
     17Type 'help' for a list of commands, or 'help shell' for a
     18list of shell meta commands.
     19}}}
     20  * Help Command
     21{{{
     22hypertable> help
     23
     24CREATE TABLE ....... Creates a table
     25DELETE ............. Deletes all or part of a row from a table
     26DESCRIBE TABLE ..... Displays a table's schema
     27DROP TABLE ......... Removes a table
     28INSERT ............. Inserts data into a table
     29LOAD DATA INFILE ... Loads data from a tab delimited input file into a table
     30SELECT ............. Selects (and display) cells from a table
     31SHOW CREATE TABLE .. Displays CREATE TABLE command used to create table
     32SHOW TABLES ........ Displays the list of tables
     33
     34Statements must be terminated with ';' to execute.  For more information on
     35a specific statement, type 'help <statement>', where <statement> is one from
     36the preceeding list.
     37}}}
     38  * Show "Pages" Table Schema
     39{{{
     40hypertable> show create table Pages;
     41
     42CREATE TABLE Pages (
     43  'refer-url',
     44  'http-code',
     45  timestamp,
     46  rowkey,
     47  ACCESS GROUP default ( 'refer-url', 'http-code', timestamp, rowkey )
     48)
     49}}}
     50  * Drop "Pages" Table
     51{{{
     52hypertable> drop table Pages;
     53hypertable> show create table Pages;
     54
     551205915835 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)
     56Error: Problem fetching schema for table 'Pages' from master - HYPERSPACE bad pathname
     57}}}
     58  * Create "Pages" Table
     59{{{
     60hypertable> create table Pages (date, "refer-url", "http-code");
     61}}}
     62  * Show "Pages" Table Schema
     63{{{
     64hypertable> show create table Pages;
     65
     66CREATE TABLE Pages (
     67  'refer-url',
     68  'http-code',
     69  date,
     70  ACCESS GROUP default ( 'refer-url', 'http-code', date )
     71)
     72
     73hypertable> describe table Pages;
     74
     75<Schema generation="1">
     76  <AccessGroup name="default">
     77    <ColumnFamily id="1">
     78      <Name>refer-url</Name>
     79    </ColumnFamily>
     80    <ColumnFamily id="2">
     81      <Name>http-code</Name>
     82    </ColumnFamily>
     83    <ColumnFamily id="3">
     84      <Name>date</Name>
     85    </ColumnFamily>
     86  </AccessGroup>
     87</Schema>
     88}}}
     89  * Load Sample Data into "Pages" Table
     90{{{
     91hypertable> load data infile ROW_KEY_COLUMN=rowkey "examples/hql_tutorial/access.tsv" into table Pages;
     92
     93Loading  10,872,957 bytes of input data...
     94
     950%   10   20   30   40   50   60   70   80   90   100%
     96|----|----|----|----|----|----|----|----|----|----|
     97***************************************************
     98Load complete.
     99
     100  Elapsed time:  4.29 s
     101Avg value size:  18.68 bytes
     102  Avg key size:  48.70 bytes
     103    Throughput:  2534861.65 bytes/s
     104 Total inserts:  300000
     105    Throughput:  69940.36 inserts/s
     106       Resends:  0
     107}}}
     108  * Load Sample Data into File
     109{{{
     110hypertable> load data infile ROW_KEY_COLUMN=rowkey "examples/hql_tutorial/access.tsv" into file "test.tsv";
     111
     112Loading  10,872,957 bytes of input data...
     113
     1140%   10   20   30   40   50   60   70   80   90   100%
     115|----|----|----|----|----|----|----|----|----|----|
     116***************************************************
     117Load complete.
     118
     119  Elapsed time:  0.56 s
     120Avg value size:  18.68 bytes
     121  Avg key size:  48.70 bytes
     122    Throughput:  19275729.29 bytes/s
     123 Total inserts:  300000
     124    Throughput:  531844.17 inserts/s
     125}}}
     126  * Show top 10 lines of "test.tsv" File
     127{{{
     128sunny@hsihdp:~/hypertable/0.9.0.5$ head -10 test.tsv
     129
     130rowkey  columnkey       value
     131events.mercurynews.com/venues   date    2008-01-25 15:19:32
     132events.mercurynews.com/venues   refer-url       events.mercurynews.com/search
     133events.mercurynews.com/venues   http-code       200
     134www.zvents.com/events/auto_complete_for_artist_name     date    2008-01-25 15:19:32
     135www.zvents.com/events/auto_complete_for_artist_name     refer-url       www.zvents.com/indio-ca/events/show/81296496-coachella
     136www.zvents.com/events/auto_complete_for_artist_name     http-code       200
     137calendar.denverpost.com/search  date    2008-01-25 15:19:32
     138calendar.denverpost.com/search  refer-url       calendar.denverpost.com/search
     139calendar.denverpost.com/search  http-code       200
     140}}}
     141  * HQL Command: SELECT
     142{{{
     143hypertable> select 'http-code' from Pages where ROW='events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar';
     144
     145events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code       200
     146events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code       200
     147events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code       200
     148events.getoutaz.com/scottsdale-az/venues/show/455885-scorch-bar http-code       200
     149}}}
     150  * HQL Command: SELECT & Show Timestamps
     151{{{
     152hypertable> select "http-code" from Pages where ROW = "www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin"
     153DISPLAY_TIMESTAMPS;
     154
     1552008-03-19 08:38:53.546882123   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1562008-03-19 08:38:53.546882120   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1572008-03-19 08:38:52.898828170   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1582008-03-19 08:38:51.670966231   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1592008-03-19 08:38:51.670966230   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1602008-03-19 08:38:51.430784186   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     161}}}
     162  * HQL Command: SELECT with Timestamps filter
     163{{{
     164hypertable> 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;
     165
     1662008-03-19 08:38:53.546882123   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1672008-03-19 08:38:53.546882120   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1682008-03-19 08:38:52.898828170   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1692008-03-19 08:38:51.670966231   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1702008-03-19 08:38:51.670966230   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     1712008-03-19 08:38:51.430784186   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code      200
     172}}}