Hypertable HQL指南

jieforest

Now try deleting one of the notes we just added

1. hypertable> delete Notes from QueryLogByUserID where ROW ="000019058 2008-11-13 07:24:43";
   
2. 
   
3.   Elapsed time:  0.00 s
   
4.    Total cells:  1
   
5.     Throughput:  256.41 cells/s
   
6.        Resends:  0

复制代码

jieforest

And verify that the cell was, indeed, deleted:

1. hypertable> select * from QueryLogByUserID where row =^ '000019058';
   
2. 000019058 2008-11-13 07:24:43   Query   tigers
   
3. 000019058 2008-11-13 07:57:16   Query   bell peppers
   
4. 000019058 2008-11-13 07:57:16   Notes   food
   
5. 000019058 2008-11-13 07:58:24   Query   bell peppers
   
6. 000019058 2008-11-13 07:58:24   ClickURL        http://agalternatives.aers.psu.edu
   
7. 000019058 2008-11-13 07:59:36   Query   growing bell peppers
   
8. 000019058 2008-11-13 07:59:36   Query   growing bell peppers
   
9. 000019058 2008-11-13 07:59:36   ClickURL        http://www.farm-garden.com
   
10. 000019058 2008-11-13 07:59:36   ClickURL        http://www.organicgardentips.com
    
11. 000019058 2008-11-13 07:59:36   Notes   gardening
    
12. 000019058 2008-11-13 12:31:02   Query   tracfone
    
13. 000019058 2008-11-13 12:31:02   ClickURL        http://www.tracfone.com
    
14. 
    
15.   Elapsed time:  0.00 s
    
16. Avg value size:  16.38 bytes
    
17.   Avg key size:  30.00 bytes
    
18.     Throughput:  162271.26 bytes/s
    
19.    Total cells:  12
    
20.     Throughput:  3498.39 cells/s

复制代码

jieforest

DROP TABLE

The DROP TABLE command is used to remove tables from the system. The IF EXISTS option prevents the system from throwing an error if the table does not exist:

1. hypertable> drop table IF EXISTS foo;

复制代码

Let's remove one of the example tables:

1. hypertable> drop table QueryLogByUserID;
   
2. hypertable> show tables;
   
3. QueryLogByTimestamp

复制代码

jieforest

Then let's remove the other:

1. hypertable> drop table QueryLogByTimestamp;
   
2. hypertable> show tables;

复制代码

GET LISTING & DROP NAMESPACE

Now, we want to get rid of the Tutorial namespace and verify that we have:

1. hypertable> use "/";
   
2. 
   
3. hypertable> get listing;
   
4. Tutorial        (namespace)
   
5. sys     (namespace)
   
6. 
   
7. hypertable> drop namespace Tutorial;
   
8. 
   
9. hypertable> get listing;
   
10. sys     (namespace)

复制代码

jieforest

The sys namespace is used by the Hypertable system and should not be used to contain user tables.

Note that a namespace must be empty (ie must not contain any sub-namespaces or tables) before you can drop it. In this case since we had already dropped the QueryLogByUserID and QueryLogByTimestamp tables, we could go ahead and drop the Tutorial namespace.

HADOOP MAPREDUCETOP

In order to run this example, Hadoop needs to be installed and HDFS and the MapReduce framework needs to be up and running.  Hypertable builds against Cloudera's CDH3 distribution of hadoop.  See CDH3 Installation for instructions on how to get Hadoop up and running.

Hypertable ships with a jar file, hypertable-x.x.x.x.jar (where x.x.x.x is the hypertable release level, e.g., 0.9.5.5) that contains Hadoop InputFormat and OutputFormat classes that allow MapReduce programs to directly read from and write to tables in Hypertable.  In this section, we walk you through an example MapReduce program, WikipediaWordCount, that tokenizes articles in a table called wikipedia that has been loaded with a Wikipedia dump.  It reads the article column, tokenizes it, and populates the word column of the same table. Each unique word in the article turns into a qualified column and the value is the number of times the word appears in the article.

jieforest

Setup

First, exit the Hypertable command line interpreter and download the Wikipedia dump, for example:

$ wget http://cdn.hypertable.com/pub/wikipedia.tsv.gz
Next, jump back into the Hypertable command line interpreter and create the wikipedia table by executing the HQL commands show below.

CREATE NAMESPACE test;
USE test;
DROP TABLE IF EXISTS wikipedia;
CREATE TABLE wikipedia (
       title,
       id,
       username,
       article,
       word
);

jieforest

Now load the compressed Wikipedia dump file directly into the wikipedia table by issuing the following HQL commands:

1. hypertable> LOAD DATA INFILE "wikipedia.tsv.gz" INTO TABLE wikipedia;
   
2. 
   
3. Loading 638,058,135 bytes of input data...
   
4. 
   
5. 0%   10   20   30   40   50   60   70   80   90   100%
   
6. |----|----|----|----|----|----|----|----|----|----|
   
7. ***************************************************
   
8. Load complete.
   
9. 
   
10.   Elapsed time:  78.28 s
    
11. Avg value size:  1709.59 bytes
    
12.   Avg key size:  24.39 bytes
    
13.     Throughput:  25226728.63 bytes/s (8151017.58 bytes/s)
    
14.    Total cells:  1138847
    
15.     Throughput:  14548.46 cells/s
    
16.        Resends:  8328

复制代码

jieforest

Example

In this example, we'll be running the WikipediaWordCount program which is included in the hypertable-X.X.X.X-examples.jar file included in the binary package installation. The following is a link to the source code for this program.

WikipediaWordCount.java

To get an idea of what the data looks like, try the following select:

1. hypertable> select * from wikipedia where row =^ "Addington";
   
2. Addington, Buckinghamshire      title   Addington, Buckinghamshire
   
3. Addington, Buckinghamshire      id      377201
   
4. Addington, Buckinghamshire      username        Roleplayer
   
5. Addington, Buckinghamshire      article {{infobox UK place \n|country = England\n|latitude=51.95095\n|longitude=-0.92177\n|official_name= Addington\n| population = 145 ...

复制代码

jieforest

Now exit from the Hypertable command line interpreter and run the WikipediaWordCount MapReduce program:

1. hypertable> quit
   
2. 
   
3. HYPERTABLE_HOME=/opt/hypertable/current
   
4. VERSION=`$HYPERTABLE_HOME/bin/ht version | head -1 | cut -f1 -d' '`
   
5. env HADOOP_CLASSPATH=$HYPERTABLE_HOME/lib/java/hypertable-$VERSION.jar:$HYPERTABLE_HOME/lib/java/libthrift-0.8.0.jar hadoop jar $HYPERTABLE_HOME/lib/java/hypertable-*-examples.jar org.hypertable.examples.WikipediaWordCount -libjars $HYPERTABLE_HOME/lib/java/hypertable-$VERSION.jar,$HYPERTABLE_HOME/lib/java/libthrift-0.8.0.jar -Dmapred.reduce.tasks=6 --columns=article

复制代码

jieforest

To verify that it worked, jump back into the Hypertable command line interpreter and try selecting for the word column:

1. $ /opt/hypertable/current/bin/ht shell
   
2. 
   
3. hypertable> select word from wikipedia where row =^ "Addington";
   
4. ...
   
5. Addington, Buckinghamshire      word:A  1
   
6. Addington, Buckinghamshire      word:Abbey      1
   
7. Addington, Buckinghamshire      word:Abbotts    1
   
8. Addington, Buckinghamshire      word:According  1
   
9. Addington, Buckinghamshire      word:Addington  6
   
10. Addington, Buckinghamshire      word:Adstock    1
    
11. Addington, Buckinghamshire      word:Aston      1
    
12. Addington, Buckinghamshire      word:Aylesbury  3
    
13. Addington, Buckinghamshire      word:BUCKINGHAM 1
    
14. Addington, Buckinghamshire      word:Bayeux     2
    
15. Addington, Buckinghamshire      word:Bene       1
    
16. Addington, Buckinghamshire      word:Bishop     1
    
17. ...

复制代码