开源云计算技术系列（六）hypertable (HQL)

既然已经安装配置好hypertable，那趁热打铁体验一下HQL。

准备好实验数据

hadoop@hadoop:~$ gunzip access.tsv.gz
hadoop@hadoop:~$ mv access.tsv ~/hypertable/0.9.2.5/examples/hql_tutorial/
hadoop@hadoop:~$ cd ~/hypertable/0.9.2.5/examples/hql_tutorial/
hadoop@hadoop:~/hypertable/0.9.2.5/examples/hql_tutorial$
hadoop@hadoop:~/hypertable/0.9.2.5/examples/hql_tutorial$ more access.tsv
# rowkey        date    refer-url       http-code
events.mercurynews.com/venues   2008-01-25 15:19:32     events.mercurynews.com/search   200
www.zvents.com/events/auto_complete_for_artist_name     2008-01-25 15:19:32     www.zvents.co
m/indio-ca/events/show/81296496-coachella       200
calendar.denverpost.com/search  2008-01-25 15:19:32     calendar.denverpost.com/search  200
www.zvents.com/search   2008-01-25 15:19:32     www.zvents.com/search   200
events.newsherald.com/events/show/81138187      2008-01-25 15:19:34     events.newsherald.com
/venues 301
www.zvents.com/search   2008-01-25 15:19:35     www.zvents.com/search   200
events.ocregister.com/search    2008-01-25 15:19:36     events.ocregister.com/  200
events.mercurynews.com/search   2008-01-25 15:19:37     events.mercurynews.com/search   200
events.idahostatesman.com/search        2008-01-25 15:19:38     events.idahostatesman.com/sea
rch     200
www.zvents.com/welcome/set_location     2008-01-25 15:19:38     www.zvents.com/movies/show/86
86-27-dresses   302

hadoop@hadoop:~/hypertable/0.9.2.5/log$ 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.

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
ALTER TABLE ........ Add/remove column family from existing table
INSERT ............. Inserts data into a table
LOAD DATA INFILE ... Loads data from a TSV 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
SHUTDOWN ........... Shuts servers down gracefully

Statements must be terminated with ';'.  For more information on
a specific statement, type 'help <statement>', where <statement> is from
the preceeding list.

hypertable> help shell

Interpreter Meta Commands
-------------------------
?          (\?) Synonym for `help'.
clear      (\c) Clear command.
exit       (\q) Exit program. Same as quit.
print      (\p) Print current command.
quit       (\q) Quit program.
source <f> (.)  Execute commands in file <f>.
system     (\!) Execute a system shell command.

hypertable>

hypertable> create table Pages ( date, "refer-url", "http-code");

Elapsed time:  0.15 s
hypertable> show create table Pages;

CREATE TABLE Pages (
  date,
  'refer-url',
  'http-code',
  ACCESS GROUP default (date, 'refer-url', 'http-code')
)

Elapsed time:  0.01 s
hypertable> describe table Pages;
<Schema generation="1">
  <AccessGroup name="default">
    <ColumnFamily id="1">
      <Generation>1</Generation>
      <Name>date</Name>
      <deleted>false</deleted>
    </ColumnFamily>
    <ColumnFamily id="2">
      <Generation>1</Generation>
      <Name>refer-url</Name>
      <deleted>false</deleted>
    </ColumnFamily>
    <ColumnFamily id="3">
      <Generation>1</Generation>
      <Name>http-code</Name>
      <deleted>false</deleted>
    </ColumnFamily>
  </AccessGroup>
</Schema>

Elapsed time:  0.00 s
hypertable>

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.41 s
Avg value size:  18.68 bytes
  Avg key size:  48.70 bytes
    Throughput:  4583496.26 bytes/s (2465499.65 bytes/s)
   Total cells:  300000
    Throughput:  68026.56 cells/s
       Resends:  0
hypertable>

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:  1.61 s
Avg value size:  18.68 bytes
  Avg key size:  48.70 bytes
    Throughput:  12586755.81 bytes/s (6770517.59 bytes/s)
   Total cells:  300000
    Throughput:  186807.99 cells/s

hypertable> quit
hadoop@hadoop:~/hypertable/0.9.2.5$ head -10 test.tsv
rowkey  columnkey       value
0x9db0140       date    2008-01-25 15:19:32
0x9db0140       refer-url       events.mercurynews.com/search
0x9db0140       http-code       200
0x9db0170       date    2008-01-25 15:19:32
0x9db0170       refer-url       www.zvents.com/indio-ca/events/show/81296496-coachella
0x9db0170       http-code       200
0x9db0170       date    2008-01-25 15:19:32
0x9db0170       refer-url       calendar.denverpost.com/search
0x9db0170       http-code       200

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

Elapsed time:  0.01 s
Avg value size:  3.00 bytes
  Avg key size:  64.00 bytes
    Throughput:  51937.98 bytes/s
   Total cells:  4
    Throughput:  775.19 cells/s

hypertable> select "refer-url" from Pages where ROW = "www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin" DISPLAY_TIMESTAMPS;
2009-08-18 14:03:49.166173771   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       refer-url       www.google.com/search
2009-08-18 14:03:49.035165894   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       refer-url       www.google.com/search
2009-08-18 14:03:48.606115876   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       refer-url       www.zvents.com/search
2009-08-18 14:03:47.197737860   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       refer-url       www.google.com/search
2009-08-18 14:03:47.197737857   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       refer-url       www.google.com/search
2009-08-18 14:03:46.955504821   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       refer-url       www.google.com/search

Elapsed time:  0.01 s
Avg value size:  21.00 bytes
  Avg key size:  66.00 bytes
    Throughput:  63821.98 bytes/s
   Total cells:  6
    Throughput:  733.59 cells/s

hypertable> select "http-code" from Pages where ROW = "www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin" DISPLAY_TIMESTAMPS;
2009-08-18 14:03:49.166173772   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:49.035165895   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:48.606115877   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:47.197737861   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:47.197737858   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:46.955504822   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200

Elapsed time:  0.00 s
Avg value size:  3.00 bytes
  Avg key size:  66.00 bytes
    Throughput:  127032.83 bytes/s
   Total cells:  6
    Throughput:  1841.06 cells/s

hypertable> select "http-code" from Pages where ROW = "www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin" AND TIMESTAMP >= '2008-02-02 00:00:00' DISPLAY_TIMESTAMPS;
2009-08-18 14:03:49.166173772   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:49.035165895   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:48.606115877   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:47.197737861   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:47.197737858   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200
2009-08-18 14:03:46.955504822   www.zvents.com/san-francisco-ca/events/show/80283482-steve-martin       http-code       200

Elapsed time:  0.01 s
Avg value size:  3.00 bytes
  Avg key size:  66.00 bytes
    Throughput:  68238.01 bytes/s
   Total cells:  6
    Throughput:  988.96 cells/s

hypertable> insert into Pages values ('2006-01-01 23:59:59', "www.hypertable.org", "refer-url", "www.zvents.com");

Elapsed time:  0.02 s
Avg value size:  14.00 bytes
   Total cells:  1
    Throughput:  53.94 cells/s
       Resends:  0
hypertable> select * from Pages where ROW = "www.hypertable.org" DISPLAY_TIMESTAMPS;
2006-01-01 23:59:59.000000000   www.hypertable.org      refer-url       www.zvents.com

Elapsed time:  0.01 s
Avg value size:  14.00 bytes
  Avg key size:  19.00 bytes
    Throughput:  5936.32 bytes/s
   Total cells:  1
    Throughput:  179.89 cells/s

hypertable> delete * from Pages where ROW = "www.hypertable.org";

Elapsed time:  0.01 s
       Resends:  0
hypertable> select * from Pages where ROW = "www.hypertable.org";

Elapsed time:  0.01 s

hypertable> insert into Pages values ('2037-01-01 23:59:59', "www.hypertable.org", "refer-url", "www.zvents.com");

Elapsed time:  0.02 s
Avg value size:  14.00 bytes
   Total cells:  1
    Throughput:  57.94 cells/s
       Resends:  0
hypertable> delete * from Pages where ROW = "www.hypertable.org";

Elapsed time:  0.01 s
       Resends:  0
hypertable> select * from Pages where ROW = "www.hypertable.org" DISPLAY_TIMESTAMPS;
2037-01-01 23:59:59.000000000   www.hypertable.org      refer-url       www.zvents.com

Elapsed time:  0.00 s
Avg value size:  14.00 bytes
  Avg key size:  19.00 bytes
    Throughput:  2750000.00 bytes/s
   Total cells:  1
    Throughput:  83333.33 cells/s

hypertable> SHOW CREATE TABLE Pages;

CREATE TABLE Pages (
  date,
  'refer-url',
  'http-code',
  ACCESS GROUP default (date, 'refer-url', 'http-code')
)

Elapsed time:  0.02 s
hypertable> ALTER TABLE Pages ADD('load-time', 'error-message', ACCESS GROUP stats('load-time')) DROP ('http-code');

Elapsed time:  0.04 s
hypertable> SHOW CREATE TABLE Pages;

CREATE TABLE Pages (
  date,
  'refer-url',
  'error-message',
  'load-time',
  ACCESS GROUP default (date, 'refer-url', 'http-code', 'error-message'),
  ACCESS GROUP stats ('load-time')
)

Elapsed time:  0.00 s

hypertable> describe table Pages;
<Schema generation="2">
  <AccessGroup name="default">
    <ColumnFamily id="1">
      <Generation>1</Generation>
      <Name>date</Name>
      <deleted>false</deleted>
    </ColumnFamily>
    <ColumnFamily id="2">
      <Generation>1</Generation>
      <Name>refer-url</Name>
      <deleted>false</deleted>
    </ColumnFamily>
    <ColumnFamily id="3">
      <Generation>1</Generation>
      <Name>http-code</Name>
      <deleted>true</deleted>
    </ColumnFamily>
    <ColumnFamily id="5">
      <Generation>2</Generation>
      <Name>error-message</Name>
      <deleted>false</deleted>
    </ColumnFamily>
  </AccessGroup>
  <AccessGroup name="stats">
    <ColumnFamily id="4">
      <Generation>2</Generation>
      <Name>load-time</Name>
      <deleted>false</deleted>
    </ColumnFamily>
  </AccessGroup>
</Schema>

Elapsed time:  0.01 s
hypertable>

hypertable> show tables;
METADATA
Pages

Elapsed time:  0.01 s
hypertable> drop table Pages;

Elapsed time:  1.05 s
hypertable> show tables;
METADATA

Elapsed time:  0.00 s

hadoop@hadoop:~/hypertable/0.9.2.5$ 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.

hypertable> source ./examples/hql_tutorial/Pages-create.hql;

Elapsed time:  1.11 s

Loading 10,872,957 bytes of input data...

0%   10   20   30   40   50   60   70   80   90   100%
|----|----|----|----|----|----|----|----|----|----|
***************************************************
Load complete.

Elapsed time:  3.65 s
Avg value size:  18.68 bytes
  Avg key size:  48.70 bytes
    Throughput:  5533544.02 bytes/s (2976538.01 bytes/s)
   Total cells:  300000
    Throughput:  82126.82 cells/s
       Resends:  0

hypertable> drop table Pages;

Elapsed time:  0.05 s
hypertable> quit
hadoop@hadoop:~/hypertable/0.9.2.5$ hypertable --batch < examples/hql_tutorial/Pages-create.hql
hadoop@hadoop:~/hypertable/0.9.2.5$ 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.

hypertable> show tables;
METADATA
Pages

Elapsed time:  0.01 s
hypertable>

更多的hql语法可以参考：http://www.hypertable.org/hql/index.html

其他更多功能体验待续。

时间： 2024-09-01 15:23:00

开源云计算技术系列（六）hypertable (HQL)的相关文章

开源云计算技术系列（五）（崛起的黑马Sector/Sphere 实战篇)

在基于java的hadoop如日中天的时代,开源云计算界有一匹基于C++的黑马,Sector/Sphere在性能方面对hadoop提出了挑战,在Open Cloud Consortium(OCC)开放云计算协会建立的Open Cloud Testbed开放云实验床的软件测试中, Sector is about twice as fast as Hadoop. 本篇先对这匹黑马做一次实战演习,先感受一下,下一篇深入其设计原理,探讨云计算的本质. OCT是一套跨核心10G带宽教育网的多个数据中心的计

开源云计算技术系列（四）（Cloudera安装配置hadoop 0.20最新版配置）

接上文,我们继续体验Cloudera 0.20最新版. wget hadoop-0.20-conf-pseudo_0.20.0-1cloudera0.5.0~lenny_all.deb wget hadoop-0.20_0.20.0-1cloudera0.5.0~lenny_all.deb debian:~# dpkg –i hadoop-0.20-conf-pseudo_0.20.0-1cloudera0.5.0~lenny_all.deb dpkg –i hadoop-0.20_0.20.0

开源云计算技术系列（六）hypertable（hadoop hdfs)

选择virtualbox建立ubuntu server 904 的虚拟机作为基础环境. hadoop@hadoop:~$ sudo apt-get install g++ cmake libboost-dev liblog4cpp5-dev git-core cronolog libgoogle-perftools-dev libevent-dev zlib1g-dev libexpat1-dev libdb4.6++-dev libncurses-dev libreadline5-dev ha

开源云计算技术系列三（10gen）安装配置

10gen 是一套云计算平台,可以为web应用提供可以扩展的高性能的数据存储解决方案.10gen的开源项目是mongoDB,主要功能是解决website的操作性数据存储,session对象的存储,数据缓存,高效率的实时计数(比如统计pv,uv),并支持ruby,python,java,c++,php等众多的页面语言. MongoDB主要特征是存储数据非常方便,不在是传统的object-relational mapping的模式,高性能,可以存储大对象数据,比如视频等,可以自动复制和failove

开源云计算技术系列（四）(Cloudera体验篇)

Cloudera  的定位在于 Bringing Big Data to the Enterprise with Hadoop Cloudera为了让Hadoop的配置标准化,可以帮助企业安装,配置,运行hadoop以达到大规模企业数据的处理和分析. 既然是给企业使用,Cloudera的软件配置不是采用最新的hadoop 0.20,而是采用了Hadoop 0.18.3-12.cloudera.CH0_3的版本进行封装,并且集成了facebook提供的hive,yahoo提供的pig等基于hado

开源云计算技术系列（四）（Cloudera安装配置）

节省篇幅,直入正题. 首先用虚拟机virtualbox 配置一台debian 5.0. debian在开源linux里面始终是最为纯正的linux血统,使用起来方便,运行起来高效,重新审视一下最新的5.0,别有一番似是故人来的感觉. 只需要下载debian-501-i386-CD-1.iso进行安装,剩下的基于debian强大的网络功能,可以很方便的进行软件包的配置.具体过程这里略去,可以在www.debian.org里面找到所有你需要的信息. 下面我们来体验一下稳定版0.183的方便和简洁.

源云计算技术系列（七）Cloudera (hadoop 0.20)

虚拟一套centos 5.3 os. 下载 jdk-6u16-linux-i586-rpm.bin [root@hadoop ~]# chmod +x jdk-6u16-linux-i586-rpm.bin [root@hadoop ~]# ./jdk-6u16-linux-i586-rpm.bin [root@hadoop ~]#  java -version java version "1.6.0" OpenJDK  Runtime Environment (build 1.6.0

云计算技术发展的六大趋势

一.数据中心向整合化和绿色节能方向发展 目前传统数据中心的建设正面临异构网络.静态资源.管理复杂.能耗高等方面问题,云计算数据中心与传统数据中心有所不同,它既要解决如何在短时间内快速.高效完成企业级数据中心的扩容部署问题,同时要兼顾绿色节能和高可靠性要求.高利用率.一体化.低功耗.自动化管理成为云计算数据中心建设的关注点,整合.绿色节能成为云计算数据中心构建技术的发展特点. 数据中心的整合首先是物理环境的整合,包括供配电和精密制冷等,主要是解决数据中心基础设施的可靠性和可用性问题.进一步的整合是

从OpenStack Newton发布看开源云计算

不可否认,在目前的云计算市场中,开源云计算是一个非常重要的组成部分,特别是OpenStack Newton版本的发布,将开源云计算提升到了一个新的高度.据悉,此次推出的新功能包括:Ironic裸机开通服务,Magnum容器编排集群管理器,此外,Kuryr容器组网项目可将容器.虚拟和物理基础设施无缝集成于统一控制面板. 这些新功能,为异构环境下的组织机构提供了更多用例,助其利用最新容器技术获得更快更好开发体验,满足负载对虚机及更高可用性架构的需要.除了能够提高在容器集群管理和组网方面的用户体验之外