中介軟體Phoenix實現用SQL查詢HBase

文章來源:大牛筆記

HBase,一個NoSQL資料庫,可儲存大量非關係型資料。

HBase,可以用HBase shell進行操作,也可以用HBase Java api進行操作。HBase雖然是一個資料庫,但是它的查詢語句,很不太好用。要是能像使用Mysql等關係型資料庫一樣用sql語句操作HBase,那就很Perfect了。

現有工具有很多Hive,Tez,Impala,Shark/Spark,Phoenix等。今天主要記錄Phoenix。

phoenix,由saleforce.com開源的一個專案,後又捐給了Apache。它相當於一個Java中介軟體,幫助開發者,像使用jdbc訪問關係型資料庫一些,訪問NoSql資料庫HBase。

phoenix,操作的表及資料,儲存在hbase上。phoenix只是需要和Hbase進行表關聯起來。然後再用工具進行一些讀或寫操作。

其實,可以把Phoenix只看成一種代替HBase的語法的一個工具。雖然可以用java可以用jdbc來連線phoenix,然後操作HBase,但是在生產環境中,不可以用在OLTP中。線上事務處理的環境中,需要低延遲,而Phoenix在查詢HBase時,雖然做了一些優化,但延遲還是不小。所以依然是用在OLAT中,再將結果返回儲存下來。

 

Phoenix官網上,對Phoenix講解已經很屌了。如果英語好,可以看官網,更正式一些。

 

Phoenix安裝
1、下載phoenix

phoenix與HBase版本對應關係

Phoenix 2.x – HBase 0.94.x

Phoenix 3.x – HBase 0.94.x

Phoenix 4.x – HBase 0.98.1

我目前測試使用版本概況:

Hadoop1.0.4

HBase0.94.18

所以我可以用phoenix2.x,phoenix3.x。

官網download頁面有

image

我選用的是phoenix3.1.0版本。

2、上傳到主節點linux就ok了,解壓縮

tar –zxvf phoenix.tar.gz

pwd

/root/phoenix

ll phoenix

image

phoenix目錄結構可能會有點不同,主要是bin目錄的位置,可能在hadoop1下,也可能直接在 /root/phoenix下。沒關係,都差不多。

3、拷貝一些檔案

既然用的hadoop1.x叢集,那麼我們使用phoenix目錄下,hadoop1目錄下的內容。

①將hadoop1下,phoenix-core-3.x.jar拷貝到hadoop叢集各個節點HBase的lib目錄下。

②重啟一下HBase

4、驗證是否安成功

在主節點上,切換到/root/phoenix/hadoop1/bin目錄下

輸入 ./sqlline.py master:2181

image

如果出現這個畫面,那就是成功了。如果不成功,可能是zookeeper配置的有一些問題吧。

好吧,先退出此介面,輸入!quit回車然後就可以退出了。

這個phoenix挺有意思,有一些命令需要輸入歎號的!

 

phoenix的使用

mysql的話,可以CLI命令列的方式操作;可以通過用jdbc,在Java程式碼中訪問;可以通過用SQLyog進行訪問管理;

phoenix,怎麼用呢?~可以看成是mysql。Phoenix可以在CLI下操作;可以用jdbc操作;可以用phoenix的一個客戶端工具Squirrel 訪問;

先說Squirrel吧,這個簡單一些。

Squirrel SQL Client,是一個連線資料庫的客戶端工具。一般支援JDBC的資料庫都可以用它來連線。(如Squirrel連線Mysql)

下載Squirrel SQL Client,解壓縮就可以了。執行squirrel-sql.bat就出現了圖形介面。

這肯定要說怎樣連Phoenix?

①在Squirrel安裝目錄的lib下,新增幾個jar包

a,phoenix-core-xxx.jar

b,phoenix-3.0-client.jar

c,hbase-0.94.18.jar

d,hadoop1.0.4.jar

e,hadoop-common-xxx.jar

需要點選“Drivers”,將phoenix的驅動新增進去。

③點選左上角 藍色的 “ ” 加號,新增

image

按上面的順序,依次填寫。第一步Name:隨便寫個名字,標記連線;第二步Example URL:相當於mysql的jdbc連線串,這裡的alias寫zookeeper的主機名稱,埠號,可以寫,可以不寫,我一般不寫;第三步選擇Phoenix-core的jar包;第四步就是手動輸入org.apache.phoenix.jdbc.PhoenixDriver。

然後點選OK。

④配置連線

image

Name:為隨便起的名稱。

Driver:選中③中新增的phoenix驅動。

URL:寫如上內容,jdbc:phoenix:node1,node2,master等這裡主要是zookeeper主機名。

User Name:要連線的主機的使用者名稱

Password:要連線的主機的密碼

點選Test可以進行測試,或點OK連線。

連線完畢,啟動後,就可以看到如下的效果了。這裡我已經建立了幾個表了,這些表都是存在於HBase上的。

image

Squirrel的一些佈局簡介:

1,用squirrel建立的一些連線

2,當前連線下,所有物件,包括主見系統表,普通表,檢視。

3,為表,這些表都是實際存在於zookeeper所管理的HBase上的。右鍵此表,可以對錶進行管理。

4,為檢視。

5,編寫sql指令碼的地方,可以輸入指令碼執行。指令碼執行方式,在5上面有一個小人,選中sql,點選小人就可以執行了。或者按ctrl enter鍵,執行。

6,為選中的物件的一些基本資訊,列資訊,行數等。

7,為sql執行的一些狀態。

下面在Squirrel中建立一個表

在Squirrel中建立表的過程主要是編寫sql,進行執行。sql該怎麼寫,需要看phoenix驅動都支援什麼效果。

這需要看phoenix的官網了。

需要注意的是phoenix是區分大小寫的,自己定義的HBase中的 HTableName,ColumnFamily,以及欄位Column,需要和Phoenix中保持一致。

phoenix操作hbase,我們有兩種方式,建立表,建立檢視。

這兩種方式,有區別。

比如,建立表的話,就可以對HBase進行插入,查詢,刪除操作。檢視的話,一般就只可以進行查詢操作

雖然看起來,表的功能,比檢視更強大一些。但是就像是mysql等關係型資料庫一樣,刪除表操作,會將表刪掉。但是刪除檢視操作,卻不會影響原始表的結構。

因為使用phoenix,建立表後,會自動和hbase建立關聯對映。當你使用phoenix刪除和hbase之間的關係時,就會將hbase中的表也刪掉了

所以用檢視,會對原始的HBase表影響小一些。

phoenix可以建立表,

若hbase中,不存在HTable:

create htablename (

pk VARCHAR primary key not null,

col1 VARCHAR null,

col2 VARCHAR null,

col3 VARCHAR null

)

create htablename2(

pk VARCHAR primary key null,

“cf”.”col1″ VARCHAR null,

“cf”.”col2″ VARCHAR null,

“cf2”.”col3″ VARCHAR null,

“cf2”.”col4″ VARCHAR null,

)

上面的SQL指令碼,可以在SQuirreL中進行執行,執行過程中,如果出現錯誤,會在工具的下面進行提示。若成功後,就可以在HBase中看到這個表了。

若Hbase中,已存在名為htablename3的HTable,那麼SQuirrel是不會直接顯示出hbase中這個已存在的表的,我們還需要額外做一些操作。

在SQuirreL中進行執行,執行完畢後,就會將HBase的htablename3,對映到SQuirreL中。這樣我們就可以在Java api中進行操作了。否則是不可以的。

那麼需要哪些具體操作呢?其實很簡單,我當時沒想到

就像建立表一樣,使用Create table就可以了。就這樣簡單。

不過這個語句怎麼寫呢?怎樣對應呢?

create htablename3(

pk VARCHAR primary key null,       ——-這句話直接寫就可以了,這樣的話,HBase中的RowKey轉換成phoenix中的主鍵,列名就叫 pk。rowkey自動會和primary key進行對應。

“cf”.”col1″ VARCHAR null,             ——-將名為cf的columnFamily下,欄位名為col1的欄位,寫在這裡。

“cf”.”col2″ VARCHAR null,            ——-將名為cf的columnFamily下,欄位名為col2的欄位,寫在這裡。。。下面就以此類推

“cf2”.”col3″ VARCHAR null,

“cf2”.”col4″ VARCHAR null,

)

然後在SQuirreL中執行,然後就可以看到資料了。

不過此時,可能還會有問題,亂碼。 在SQuirrel中,主鍵以及一些包含漢字的欄位,都是方塊等亂碼了。這個怎麼解決???

建立試圖

CREATE VIEW “heihei”

(pk VARCHAR primary key)

default_column_family = ‘FM’

建立完成後,這裡的“heihei” 是HBase中table的名稱。然後定義一個主鍵,就可以了。

建立檢視

①CREATE VIEW “DAMAI” ( PK VARCHAR PRIMARY KEY) DEFAULT_COLUMN_FAMILY=’FM’

這裡雙引號內的 “DAMAI” 和HBase中的表名是一樣的,所以會自動關聯。

image

那麼,如果想針對HBase中的一個表,建多個檢視呢?

②第二種檢視,可以在Phoenix table的基礎上建立。

CREATE VIEW my_VIEW (new_col VARCHAR,new_col2 VARCHAR) AS SELECT * FROM phoenix_Table WHERE ……

③第三種檢視,是建立在檢視之上,

CREATE VIEW my_VIEW_ON_VIEW AS SELECT * FROM MY_VIEW WHERE ……

所以說,在建立DAMAI檢視的時候,可以將全部欄位都包括進來。然後再在此檢視基礎上,建立其它檢視。

隨著資料的增長,檢視中可以看到的資料的條數,也在同步增加。

image

 

使用命令檢視一下檢視中的資料

select * from damai   這時可以用大小寫都行了。沒有區分

查詢結果現在,只有一列。看來是建立檢視時,沒有關聯好其他列。沒關係,刪掉,重建。

 

Phoenix中的語法

Phoenix中的資料型別

Phoenix中的方法

我自己使用過程中一些簡單語句,如下:

select * from shuju;

select count(1) from shuju;

select cmtid,count(1) as num from shuju group by cmtid order by num desc;

select avg(TO_NUMBER(avgt)) from shuju;

select cmtid,count(1) as num,avg(TO_NUMBER(avgt)) as avgt,avg(TO_NUMBER(loss)) as loss from shuju group by cmtid order by num desc;

select acm,dtype,cmtid,count(1) as num,avg(TO_NUMBER(avgt)) as avgt,avg(TO_NUMBER(loss)) as loss

from shuju

group by acm,dtype,cmtid

order by num desc;

select acm,dtype,porgcode,orgid,cmtid,count(1) as num,avg(TO_NUMBER(avgt)) as avgt,avg(TO_NUMBER(loss)) as loss

from shuju

group by acm,dtype,porgcode,orgid,cmtid

order by num desc;

where TO_DATE(ttime,’yyyyMMddHHmmss’)=TO_DATE(‘20141125′,’yyyyMMdd’)

select ttime from shuju order by ttime desc;

 

where TO_DATE(ttime,’yyyyMMddHHmmss’)=TO_DATE(‘20141125′,’yyyyMMdd’)

 

select TO_DATE(ttime,’yyyyMMddHHmmss’) from shuju;

select TO_DATE(‘20141125′,’yyyyMMdd’) from shuju;

select (TO_DATE(ttime,’yyyyMMddHHmmss’)=TO_DATE(‘20141125′,’yyyyMMdd’)) as aaa from shuju order by aaa asc;

 

用SHELL命令來操作phoenix

用SHELL來操作phoenix,不太好用。無助的時候,你可以喊救命!So,Say Help Help Help

./sqlline.py master:2181

進入shell後,輸入help

可以看到很多命令,前面都帶了一個歎號。根據意思自己猜一猜功能,然後試一試效果,就可以了。不記錄了。

 

用Phoenix Java api操作HBase

這個過程就想是JDBC一樣使用就可以了。

①先將phoenix的 core.jar包 和 phoenix的client.jar 包放到lib裡。

②建立連線,過程和mysql類似

public Connection GetConnection(){
Connection cc = null;
String driver = “org.apache.phoenix.jdbc.PhoenixDriver”;
//String url = “jdbc:phoenix:192.168.206.21:2181”;

try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

if (cc == null) {
try {
cc = DriverManager.getConnection(url);
} catch (SQLException e) {
e.printStackTrace();
}
}
return cc;
}

QQ截圖20141202101221

OK 搞定了。