Hypertable HQL指南

jieforest

Row Intervals

To restrict the MapReduce to a specific row interval of the input table, a row range can be specified with the hypertable.mapreduce.input.scan_spec.row_interval Hadoop configuration property. The row interval predicate is specified using the same format as the timestamp predicate in the WHERE clause of the SELECT statement, as illustrated in the following examples:

ROW < foo
ROW >= bar
bar <= ROW <= 'foo;'

jieforest

The following example illustrates how a row interval is passed into a Hadoop Streaming MapReduce program.

1. $ hadoop jar /usr/lib/hadoop-0.20/contrib/streaming/hadoop-streaming-0.20.2-cdh3u*.jar \
   
2. -libjars /opt/hypertable/current/lib/java/hypertable-*.jar,/opt/hypertable/current/lib/java/libthrift-*.jar \
   
3. -Dhypertable.mapreduce.namespace=test \
   
4. -Dhypertable.mapreduce.input.table=wikipedia \
   
5. -Dhypertable.mapreduce.output.table=wikipedia2 \
   
6. -Dhypertable.mapreduce.input.scan_spec.columns="id,title" \
   
7. -Dhypertable.mapreduce.input.scan_spec.row_interval="Dog <= ROW <= Kitchen" \
   
8. -mapper /bin/cat -reducer /bin/cat \
   
9. -inputformat org.hypertable.hadoop.mapred.TextTableInputFormat \
   
10. -outputformat org.hypertable.hadoop.mapred.TextTableOutputFormat \
    
11. -input wikipedia -output wikipedia2

复制代码

jieforest

Options

A subset of the WHERE clause options of the HQL SELECT statement can be specified by supplying the options with the hypertable.mapreduce.input.scan_spec.options Hadoop configuration property. The following options are supported:

MAX_VERSIONS
CELL_LIMIT
KEYS_ONLY

jieforest

The following example illustrates how to pass options to a Hadoop Streaming MapReduce program.

1. $ hadoop jar /usr/lib/hadoop-0.20/contrib/streaming/hadoop-streaming-0.20.2-cdh3u*.jar \
   
2. -libjars /opt/hypertable/current/lib/java/hypertable-*.jar,/opt/hypertable/current/lib/java/libthrift-*.jar \
   
3. -Dhypertable.mapreduce.namespace=test \
   
4. -Dhypertable.mapreduce.input.table=wikipedia \
   
5. -Dhypertable.mapreduce.output.table=wikipedia2 \
   
6. -Dhypertable.mapreduce.input.scan_spec.options="MAX_VERSIONS 1 KEYS_ONLY CELL_LIMIT 2" \
   
7. -mapper /bin/cat -reducer /bin/cat \
   
8. -inputformat org.hypertable.hadoop.mapred.TextTableInputFormat \
   
9. -outputformat org.hypertable.hadoop.mapred.TextTableOutputFormat \
   
10. -input wikipedia -output wikipedia2

复制代码

jieforest

REGULAR EXPRESSION FILTERING

Hypertable supports filtering of data using regular expression matching on the rowkey, column qualifiers and value. Hypertable uses RE2 for regular expression matching, the complete supported syntax can be found in the RE2 Syntax document.

Example

In this example we'll use a DMOZ dataset which contains title, description and a bunch of topic tags for a set of URLs. The domain components of the URL have been reversed so that URLs from the same domain sort together. In the schema, the rowkey is a URL and the title, description and topic are column families. Heres a small sample from the dataset:

1. com.awn.www     Title   Animation World Network com.awn.www     Description     Provides information resources to the international animation community. Features include searchable database archives, monthly magazine, web animation guide, the Animation Village, discussion forums and other useful resources.
   
2. com.awn.www     Topic:Arts     
   
3. com.awn.www     Topic:Animation

复制代码

jieforest

Exit the hypertable shell and download the dataset, which is in the .tsv.gz format which can be directly loaded into Hypertable without unzipping:

1. hypertable> quit
   
2. 
   
3. $ wget http://cdn.hypertable.com/pub/dmoz.tsv.gz

复制代码

Jump back into the hypertable shell and create the dmoz table as follows:

1. /opt/hypertable/current/bin/ht shell
   
2. 
   
3. hypertable> USE "/";
   
4. hypertable> CREATE TABLE dmoz(Description, Title, Topic, ACCESS GROUP topic(Topic));
   
5. hypertable> LOAD DATA INFILE "dmoz.tsv.gz" INTO TABLE dmoz;
   
6. 
   
7. Loading 412,265,627 bytes of input data...
   
8. 
   
9. 0%   10   20   30   40   50   60   70   80   90   100%
   
10. |----|----|----|----|----|----|----|----|----|----|
    
11. ***************************************************
    
12. Load complete.
    
13. 
    
14.   Elapsed time:  242.26 s
    
15. Avg value size:  15.09 bytes
    
16.   Avg key size:  24.76 bytes
    
17.     Throughput:  6511233.28 bytes/s (1701740.27 bytes/s)
    
18.    Total cells:  39589037
    
19.     Throughput:  163414.69 cells/s
    
20.        Resends:  144786

复制代码

jieforest

In the following queries we limit the number of rows returned to 2 for brevity. Suppose you want a subset of the URLs from the domain inria.fr where the first component of the domain doesn't start with the letter 'a', you could run:

1. hypertable> SELECT Title FROM dmoz WHERE ROW REGEXP "fr\.inria\.[^a]" LIMIT 2 REVS=1 KEYS_ONLY;
   
2. 
   
3. fr.inria.caml
   
4. fr.inria.caml/pub/docs/oreilly-book

复制代码

To look at all topics which start with write (case insensitive):

1. hypertable> SELECT Topic:/(?i)^write/ FROM dmoz LIMIT 2;
   
2. 
   
3. 13.141.244.204/writ_den Topic:Writers_Resources
   
4. ac.sms.www/clubpage.asp?club=CL001003004000301311       Topic:Writers_Resources

复制代码

jieforest

The next example shows how to query for data where the description contains the word game followed by either foosball or halo:

1. hypertable> SELECT CELLS Description FROM dmoz WHERE VALUE REGEXP "(?i:game.*(foosball|halo)\s)" LIMIT 2 REVS=1;
   
2. 
   
3. com.armchairempire.www/Previews/PCGames/planetside.htm  Description     Preview by Mr. Nash. "So, on the one side the game is sounding pretty snazzy, on the other it sounds sort of like Halo at its core."
   
4. com.digitaldestroyers.www       Description     Video game fans in Spartanburg, South Carolina who like to get together and compete for bragging rights. Also compete with other Halo / Xbox fan clubs.

复制代码

jieforest

ATOMIC COUNTERS

Column families can optionally act as atomic counters by supplying the COUNTER option in the column specification of the CREATE TABLE command. Counter columns are accessed using the same methods as other columns. However, to modify the counter, the value must be formatted specially, as described in the following table.

1. Value        Format Description
   
2. ['+'] n         Increment the counter by n
   
3. '-' n         Decrement the counter by n
   
4. '=' n         Reset the counter to n

复制代码

Example

In this example we create a table of counters called counts that contains a single column family url that acts as an atomic counter for urls. By convention, the row key is the URL with the domain name reversed (so that URLs from the same domain sort next to each other) and the column qualifier is the hour in which the "hit" occurred. The table is created with the following HQL:

1. hypertable> use "/";
   
2. hypertable> create table counts ( url COUNTER );

复制代码

jieforest

Let's say we've accumulated url "hit" occurrences in the following .tsv file:

1. #row    column  value
   
2. org.hypertable.www/     url:2010-10-26_09       +1
   
3. org.hypertable.www/     url:2010-10-26_09       +1
   
4. org.hypertable.www/download.html        url:2010-10-26_09       +1
   
5. org.hypertable.www/documentation.html   url:2010-10-26_09       +1
   
6. org.hypertable.www/download.html        url:2010-10-26_09       +1
   
7. org.hypertable.www/about.html   url:2010-10-26_09       +1
   
8. org.hypertable.www/     url:2010-10-26_09       +1
   
9. org.hypertable.www/     url:2010-10-26_10       +1
   
10. org.hypertable.www/about.html   url:2010-10-26_10       +1
    
11. org.hypertable.www/     url:2010-10-26_10       +1
    
12. org.hypertable.www/download.html        url:2010-10-26_10       +1
    
13. org.hypertable.www/download.html        url:2010-10-26_10       +1
    
14. org.hypertable.www/documentation.html   url:2010-10-26_10       +1
    
15. org.hypertable.www/     url:2010-10-26_10       +1

复制代码