Hypertable HQL指南

jieforest

SELECT

Let's start by examining the QueryLogByUserID table. To select all of the data for user ID 003269359 we need to use the starts with operator =^. Remember that the row key is the concatenation of the user ID and the timestamp which is why we need to use the starts with operator.

1. hypertable> select * from QueryLogByUserID where row =^ '003269359';
   
2. 003269359 2008-11-13 04:36:34 Query binibining pilipinas 2008 winners
   
3. 003269359 2008-11-13 04:36:34 ItemRank 5
   
4. 003269359 2008-11-13 04:36:34 ClickURL http://www.missosology.org
   
5. 003269359 2008-11-13 04:37:34 Query pawee's kiss and tell
   
6. 003269359 2008-11-13 04:37:34 ItemRank 3
   
7. 003269359 2008-11-13 04:37:34 ClickURL http://www.missosology.org
   
8. 003269359 2008-11-13 05:07:10 Query rn jobs in 91405
   
9. 003269359 2008-11-13 05:07:10 ItemRank 9
   
10. 003269359 2008-11-13 05:07:10 ClickURL http://91405.jobs.com
    
11. 003269359 2008-11-13 05:20:22 Query rn jobs in 91405
    
12. ...
    
13. 003269359 2008-11-13 09:42:49 Query wound ostomy rn training
    
14. 003269359 2008-11-13 09:42:49 ItemRank 11
    
15. 003269359 2008-11-13 09:42:49 ClickURL http://www.wocn.org
    
16. 003269359 2008-11-13 09:46:50 Query pych nurse in encino tarzana hospital
    
17. 003269359 2008-11-13 09:47:18 Query encino tarzana hospital
    
18. 003269359 2008-11-13 09:47:18 ItemRank 2
    
19. 003269359 2008-11-13 09:47:18 ClickURL http://www.encino-tarzana.com
    
20. 003269359 2008-11-13 09:52:42 Query encino tarzana hospital
    
21. 003269359 2008-11-13 09:53:08 Query alhambra hospital
    
22. 003269359 2008-11-13 09:53:08 ItemRank 1
    
23. 003269359 2008-11-13 09:53:08 ClickURL http://www.alhambrahospital.com
    
24. 
    
25.   Elapsed time:  0.01 s
    
26. Avg value size:  19.24 bytes
    
27.   Avg key size:  30.00 bytes
    
28.     Throughput:  2001847.79 bytes/s
    
29.    Total cells:  352
    
30.     Throughput:  40651.35 cells/s

复制代码

jieforest

The result set was fairly large (352 cells), so let's now try selecting just the queries that were issued by the user with ID 003269359 during the hour of 5am. To do this we need to add a TIMESTAMP predicate. Each cell has an internal timestamp and the TIMESTAMP predicate can be used to filter the results based on this timestamp.

1. hypertable> select * from QueryLogByUserID where row =^ '003269359' AND "2008-11-13 05:00:00" <= TIMESTAMP < "2008-11-13 06:00:00";
   
2. 003269359 2008-11-13 05:07:10 Query rn jobs in 91405
   
3. 003269359 2008-11-13 05:07:10 ItemRank 9
   
4. 003269359 2008-11-13 05:07:10 ClickURL http://91405.jobs.com
   
5. 003269359 2008-11-13 05:20:22 Query rn jobs in 91405
   
6. 003269359 2008-11-13 05:20:22 ItemRank 16
   
7. 003269359 2008-11-13 05:20:22 ClickURL http://www.careerbuilder.com
   
8. 003269359 2008-11-13 05:34:02 Query usc university hospital
   
9. 003269359 2008-11-13 05:34:02 ItemRank 1
   
10. 003269359 2008-11-13 05:34:02 ClickURL http://www.uscuh.com
    
11. 003269359 2008-11-13 05:37:01 Query rn jobs in san fernando valley
    
12. 003269359 2008-11-13 05:37:01 ItemRank 7
    
13. 003269359 2008-11-13 05:37:01 ClickURL http://www.medhunters.com
    
14. 003269359 2008-11-13 05:46:22 Query northridge hospital
    
15. 003269359 2008-11-13 05:46:22 ItemRank 2
    
16. 003269359 2008-11-13 05:46:22 ClickURL http://northridgehospital.org
    
17. 003269359 2008-11-13 05:53:34 Query valley presbyterian hospital
    
18. 003269359 2008-11-13 05:53:34 ItemRank 4
    
19. 003269359 2008-11-13 05:53:34 ClickURL http://www.hospital-data.com
    
20. 003269359 2008-11-13 05:55:36 Query valley presbyterian hospital website
    
21. 003269359 2008-11-13 05:55:36 ItemRank 1
    
22. 003269359 2008-11-13 05:55:36 ClickURL http://www.valleypres.org
    
23. 003269359 2008-11-13 05:59:24 Query mission community hospital
    
24. 003269359 2008-11-13 05:59:24 ItemRank 1
    
25. 003269359 2008-11-13 05:59:24 ClickURL http://www.mchonline.org
    
26. 
    
27.   Elapsed time:  0.00 s
    
28. Avg value size:  18.50 bytes
    
29.   Avg key size:  30.00 bytes
    
30.     Throughput:  2602086.44 bytes/s
    
31.    Total cells:  36
    
32.     Throughput:  53651.27 cells/s

复制代码

jieforest

Keep in mind that the internal cell timestamp is different than the one embedded in the row key. In this example, they both represent the same time. By specifying the TIMESTAMP_COLUMN option to LOAD DATA INFILE, we extracted the QueryTime field to be used as the internal cell timestamp. If we hadn't supplied that option, the system would have auto-assigned a timestamp. To display the internal cell timestamp, add the DISPLAY_TIMESTAMPS option:

1. hypertable> select * from QueryLogByUserID limit 5 DISPLAY_TIMESTAMPS;
   
2. 2008-11-13 10:30:46.000000000   000000036 2008-11-13 10:30:46   Query   helena ga
   
3. 2008-11-13 10:31:34.000000000   000000036 2008-11-13 10:31:34   Query   helena ga
   
4. 2008-11-13 10:45:23.000000000   000000036 2008-11-13 10:45:23   Query   checeron s
   
5. 2008-11-13 10:46:07.000000000   000000036 2008-11-13 10:46:07   Query   cheveron gas station
   
6. 2008-11-13 10:46:34.000000000   000000036 2008-11-13 10:46:34   Query   cheveron gas station richmond virginia
   
7. 
   
8.   Elapsed time:  0.00 s
   
9. Avg value size:  17.20 bytes
   
10.   Avg key size:  30.00 bytes
    
11.     Throughput:  207563.76 bytes/s
    
12.    Total cells:  5
    
13.     Throughput:  4397.54 cells/s

复制代码

jieforest

There is no index for the internal cell timestamps, so if we don't include a row =^ expression in our predicate, the system will do a full table scan. This is why we imported the data into a second table QueryLogByTimestamp. This table includes the timestamp as the row key prefix which allows us to efficiently query data over a time interval.

The following query selects all query log data for November 14th, 2008:

1. hypertable> select * from QueryLogByTimestamp WHERE ROW =^ '2008-11-14';
   
2. 2008-11-14 00:00:00 001040178   Query   noodle tools
   
3. 2008-11-14 00:00:00 001040178   ItemRank        1
   
4. 2008-11-14 00:00:00 001040178   ClickURL        http://www.noodletools.com
   
5. 2008-11-14 00:00:01 000264655   Query   games.myspace.com
   
6. 2008-11-14 00:00:01 000264655   ItemRank        1
   
7. 2008-11-14 00:00:01 000264655   ClickURL        http://games.myspace.com
   
8. 2008-11-14 00:00:01 000527424   Query   franklinville schools new jersey
   
9. 2008-11-14 00:00:01 000527424   ItemRank        1
   
10. 2008-11-14 00:00:01 000527424   ClickURL        http://www.greatschools.net
    
11. 2008-11-14 00:00:01 000632400   Query   lack of eye contact symptom of...
    
12. ...
    
13. 2008-11-14 06:02:33 003676354   Query   baby 20showers
    
14. 2008-11-14 06:02:35 003378030   Query   task and responsibility matrix
    
15. 2008-11-14 06:02:35 003378030   ItemRank        2
    
16. 2008-11-14 06:02:35 003378030   ClickURL        http://im.ncsu.edu
    
17. 2008-11-14 06:02:36 004578101   Query   jcpenneys
    
18. 2008-11-14 06:02:37 005120734   Query   ebay
    
19. 2008-11-14 06:02:40 000957500   Query   buccal fat size of ping pong ball
    
20. 
    
21.   Elapsed time:  2.37 s
    
22. Avg value size:  15.36 bytes
    
23.   Avg key size:  30.00 bytes
    
24.     Throughput:  1709616.45 bytes/s
    
25.    Total cells:  89412
    
26.     Throughput:  37689.18 cells/s

复制代码

jieforest

And to select all query log data for November 14th, 2008 during the hour of 3am:

1. hypertable> select * from QueryLogByTimestamp WHERE ROW =^ '2008-11-14 03';
   
2. 2008-11-14 03:00:00 002512415   Query   ny times
   
3. 2008-11-14 03:00:00 002512415   ItemRank        1
   
4. 2008-11-14 03:00:00 002512415   ClickURL        http://www.nytimes.com
   
5. 2008-11-14 03:00:00 005294906   Query   kickmeto.fosi
   
6. 2008-11-14 03:00:00 005459226   Query   http://www.dickdyertoyota.com
   
7. 2008-11-14 03:00:02 000637292   Query   days of our lives
   
8. 2008-11-14 03:00:02 000637292   ItemRank        3
   
9. 2008-11-14 03:00:02 000637292   ClickURL        http://www.nbc.com
   
10. 2008-11-14 03:00:03 002675105   Query   ghetto superstar lyrics
    
11. ...
    
12. 2008-11-14 03:59:52 002874080   ClickURL        http://www.paintball-discounters.com
    
13. 2008-11-14 03:59:53 004292772   Query   drop down menu
    
14. 2008-11-14 03:59:55 005656539   Query   to buy indian hair to make wigs in new york
    
15. 2008-11-14 03:59:55 005656539   ItemRank        1
    
16. 2008-11-14 03:59:55 005656539   ClickURL        http://query.nytimes.com
    
17. 2008-11-14 03:59:58 004318586   Query   myspace .com
    
18. 
    
19.   Elapsed time:  0.17 s
    
20. Avg value size:  15.37 bytes
    
21.   Avg key size:  30.00 bytes
    
22.     Throughput:  2267099.06 bytes/s
    
23.    Total cells:  8305
    
24.     Throughput:  49967.51 cells/s

复制代码

jieforest

And finally, to select all query log data for November 14th, 2008 during the minute of 3:45am:

1. hypertable> select * from QueryLogByTimestamp WHERE ROW =^ '2008-11-14 03:45';
   
2. 2008-11-14 03:45:00 003895650   Query   ks lottery.
   
3. 2008-11-14 03:45:00 003895650   ItemRank        2
   
4. 2008-11-14 03:45:00 003895650   ClickURL        http://www.lotterypost.com
   
5. 2008-11-14 03:45:00 005036796   Query   http://www.glasgowdailytimes 10-20-2005
   
6. 2008-11-14 03:45:01 002863052   Query   map quest
   
7. 2008-11-14 03:45:01 005514285   Query   john bermeo
   
8. 2008-11-14 03:45:02 002394176   Query   http://www.eggseye.com
   
9. 2008-11-14 03:45:02 003454227   Query   hawaiian weddig band
   
10. 2008-11-14 03:45:03 001006089   Query   brokers hiring loan officers in indiana
    
11. 2008-11-14 03:45:06 000844720   Query   latest design microsoft freeware
    
12. ...
    
13. 2008-11-14 03:45:55 003920469   ItemRank        3
    
14. 2008-11-14 03:45:55 003920469   ClickURL        http://www.pennyblood.com
    
15. 2008-11-14 03:45:56 002729906   Query   tryaold
    
16. 2008-11-14 03:45:56 003919348   Query   feathered draped fox fur mandalas
    
17. 2008-11-14 03:45:56 003919348   ItemRank        8
    
18. 2008-11-14 03:45:56 003919348   ClickURL        http://www.greatdreams.com
    
19. 2008-11-14 03:45:56 004803968   Query   -
    
20. 
    
21.   Elapsed time:  0.02 s
    
22. Avg value size:  15.71 bytes
    
23.   Avg key size:  30.00 bytes
    
24.     Throughput:  305030.80 bytes/s
    
25.    Total cells:  130
    
26.     Throughput:  6673.51 cells/s

复制代码

jieforest

See the HQL Documentation: SELECT for complete syntax.

ALTER TABLE

The ALTER TABLE command can be used to add and/or remove columns from a table. The following command will add a 'Notes' column in a new access group called 'extra' and will drop column 'ItemRank'.

hypertable> ALTER TABLE QueryLogByUserID ADD(Notes, ACCESS GROUP extra(Notes)) DROP(ItemRank);
To verify the change, issue the SHOW CREATE TABLE command:

hypertable> show create table QueryLogByUserID;

CREATE TABLE QueryLogByUserID (
  Query,
  ClickURL,
  Notes,
  ACCESS GROUP default (Query, ClickURL),
  ACCESS GROUP extra (Notes)
)

jieforest

And to verify that the column no longer exists, issue the same SELECT statement we issued above (NOTE: the data for the column still exists in the filesystem, it will get lazily garbage collected).

1. hypertable> select * from QueryLogByUserID limit 8;
   
2. 000000036 2008-11-13 10:30:46   Query   helena ga
   
3. 000000036 2008-11-13 10:31:34   Query   helena ga
   
4. 000000036 2008-11-13 10:45:23   Query   checeron s
   
5. 000000036 2008-11-13 10:46:07   Query   cheveron gas station
   
6. 000000036 2008-11-13 10:46:34   Query   cheveron gas station richmond virginia
   
7. 000000036 2008-11-13 10:48:56   Query   cheveron glenside road richmond virginia
   
8. 000000036 2008-11-13 10:49:05   Query   chevron glenside road richmond virginia
   
9. 000000036 2008-11-13 10:49:05   ClickURL        http://yp.yahoo.com
   
10. 000000053 2008-11-13 15:18:21   Query   mapquest
    
11. 000000053 2008-11-13 15:18:21   ClickURL        http://www.mapquest.com
    
12. 
    
13.   Elapsed time:  0.00 s
    
14. Avg value size:  21.50 bytes
    
15.   Avg key size:  30.00 bytes
    
16.     Throughput:  140595.14 bytes/s
    
17.    Total cells:  10
    
18.     Throughput:  2730.00 cells/s

复制代码

jieforest

See HQL Documentation: ALTER TABLE for complete syntax.

INSERT & DELETE

Now let's augment the QueryLogByUserID table by adding some information in the Notes column for a few of the queries:

hypertable> INSERT INTO QueryLogByUserID VALUES
("000019058 2008-11-13 07:24:43", "Notes", "animals"),
("000019058 2008-11-13 07:57:16", "Notes", "food"),
("000019058 2008-11-13 07:59:36", "Notes", "gardening");

  Elapsed time:  0.01 s
Avg value size:  6.67 bytes
   Total cells:  3
    Throughput:  298.36 cells/s
       Resends:  0

jieforest

Notice the new data by querying the affected row:

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

复制代码