IT??????????????

???Phoenix??SQL?????????HBase

?????IT165??????????????2016-07-14 22:04:47

1.???

HBase??????????????????NOSQL????????????Hadoop??????洢????????????????????????????????????????????????????????洢???????????????HBase????????SQL?????????????????????????÷??????????????????????????SQL??????????HBase??????????д?????????
???????????????????????HBase????SQL?м?????????????HBase????????SQL???????ó???????????ù?????????????HBase??Apache Phoenix???????е?????????????
???????????ν??????????????????????Apache Phoenix?????????HBase????????????????????????????ù????????????????????????????????

HBase?????????????????? HBase??Java API???? Phoenix?????????????? Squirrel?????????????? ???Phoenix???SQL??????HBase?? Phoenix????????

????????????????????????????????????????????????????????????t??????????????

linux????ó???? Hadoop??Hbase??Zookeeper???????????ó???? Java???????????? SQL????????? Oracle??SQLServer??Mysql???????????ù??????

2. HBase?????????????????

2.1 ????????

???????????????linux????CentOS????UNIX???????У??????漰????????????????????Hadoop??Hbase??Zookeeper????汾?????£?

hadoop-2.3.0-cdh5.1.0 zookeeper-3.4.5-cdh5.1.0 hbase-0.98.1-cdh5.1.0
???????????????????汾5.1.0?????????????汾????????????????????????????????cdh??汾??????????????????
????д??????

??????????????Node1~Node6??hadoop??????????????????????Node1??Node2??NameNode????????DataNode??ZooKeeper?????Node4??Node5??Node6??????????????2181??Hbase?????Node1??Node3~Node6?????????"http://www.it165.net/pro/pkqt/" target="_blank" class="keylink">QTm9kZTHKx0hNYXN0ZXKjrMbky/vKx0hSZWdpb25TZXJ2ZXKhoyA8YnI+Cr7fzOWyzsr9xeTWw7/J0tSyzr+8xuTL+87EtbWjrLTLtKayu9f2z+rPuMPoyvahoyA8YnI+CjxlbT7XotLio7q/zbuntsux2NDrzai5/Vpvb0tlZXBlctXStb1IYmFzZbXEyOu/2qGjttTT2r/Nu6fAtMu1o6zWu9Do0qrWqrXAWm9vS2VlcGVy1NrExLb5o7vQ6NKqt8POymhiYXNlyrGjrL/Nu6e2y8il1dJab29LZWVwZXKjrFpvb0tlZXBlctTZyKWy6dGvSEJhc2W1xEhNYXN0ZXK6zUhSZWdpb25TZXJ2ZXK1yNDFz6KjrL7fzOXH6b/2vPuhtkhCYXNlyrXVvaG3NjPSs6GjPC9lbT48L3A+Cgo8aDMgaWQ9"22-hbase-shell???">2.2 HBase Shell???

???????ó???????????HBase Shell??HBase???????в???????????Oracle????sqlplus??
???????????????HBase?????????????

hbase shell
list

?????г???hbase?д洢?????б??
??????????test????????????????cf?????壬?????list???????????????????????Щ?????

hbase(main):003:0> create 'test', 'cf'
0 row(s) in 1.2200 seconds
hbase(main):003:0> list
test
1 row(s) in 0.0550 seconds
hbase(main):004:0> put 'test', 'row1', 'cf:a', 'value1'
0 row(s) in 0.0560 seconds
hbase(main):005:0> put 'test', 'row2', 'cf:b', 'value2'
0 row(s) in 0.0370 seconds
hbase(main):006:0> put 'test', 'row3', 'cf:c', 'value3'
0 row(s) in 0.0450 seconds

???scan????test????е??????

hbase(main):007:0> scan 'test'
ROW        COLUMN+CELL
row1       column=cf:a, timestamp=1288380727188, value=value1
row2       column=cf:b, timestamp=1288380738440, value=value2
row3       column=cf:c, timestamp=1288380747365, value=value3
3 row(s) in 0.0590 seconds

??????е?????????

hbase(main):008:0> get 'test', 'row1'
COLUMN      CELL
cf:a        timestamp=1288380727188, value=value1
1 row(s) in 0.0400 seconds
disable??drop??????
hbase(main):012:0> disable 'test'
0 row(s) in 1.0930 seconds
hbase(main):013:0> drop 'test'
0 row(s) in 0.0770 seconds 

???shell??

hbase(main):014:0> exit

??????????????????ο?HBase?????????????????

3. HBase Java API ???

???HBase??Java API???п??????????HBase?????????????????HBase??????顣
????п????????????????Windows??Linux????CentOS???н??hbase-0.98.1-cdh5.1.0.tar.gz?????????????????????jar????λ??hbase-0.98.1-cdh5.1.0/lib???С?
???????????????Eclipse??NetBean????Intellij???н????????????hbase-0.98.1-cdh5.1.0\lib?е?????jar????

3.1 ???????????HBase

?????????????????????????????HBase????????Oracle?????????????????????????????????????ip??port??Service Name???????????????????HBase????????????????????ZooKeeper????????ip??????hostname????port???????????????????HBase???????????
????????hostname?????ip?????????????????????????????????????????ī????linux??windows??hostname???á?
??linux?£?hostname??????/etc/hosts?????????????????????????????????????

192.168.1.101  Node1
192.168.1.102  Node2
192.168.1.103  Node3
192.168.1.104  Node4
192.168.1.105  Node5
192.168.1.106  Node6

??????/etc/sysconfig/network????У?????HOSTNAME=????????HOSTNAME=Node???????Node??滻???????????hostname????
??Windows?£????????Win7 64???????Windows/System32/drivers/etc/hosts?????????

192.168.1.101  Node1
192.168.1.102  Node2
192.168.1.103  Node3
192.168.1.104  Node4
192.168.1.105  Node5
192.168.1.106  Node6

???????windows??hosts?????λ???????????????????everything??????????????????
?????????????????????ZooKeeper??????ip?????

public static String hbase_svr_ip = "192.168.1.104, 192.168.1.105, 192.168.1.106";
public static String hbase_svr_port = "2181";

????hostname?????

public static String hbase_svr_hostname = "Node4,Node5,Node6";
public static String hbase_svr_port = "2181";

???????????д??ip??

public static String hbase_svr_ip = "192.168.1.104:2181, 192.168.1.105:2181, 192.168.1.106:2181";

???????????д??hostname??

public static String hbase_svr_hostname = "Node4:2181,Node5:2181,Node6:2181";

???????????ZooKeeper????????

public static String hbase_svr_hostname = "Node4:2181";

?????????????????????????????????????????????Щ?汾?п????????????????????????????????????????hostname???????????????????

3.2 ?????

????????????Java API????HBase??????????????????滻?????ZooKeeper???????????hostname??????

public static String hbase_svr_ip = "192.168.1.104, 192.168.1.105, 192.168.1.106";
public static String hbase_svr_port = "2181";
public static String hbase_svr_hostname = "Node4,Node5,Node6";

???????????

???????Hbase????? ??????? ??????б?? ????????? ???????? ???????? ??????
package com.wxb;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.CellUtil;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.HColumnDescriptor;
import org.apache.hadoop.hbase.HTableDescriptor;
import org.apache.hadoop.hbase.client.Delete;
import org.apache.hadoop.hbase.client.Get;
import org.apache.hadoop.hbase.client.HBaseAdmin;
import org.apache.hadoop.hbase.client.HConnection;
import org.apache.hadoop.hbase.client.HConnectionManager;
import org.apache.hadoop.hbase.client.HTableInterface;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.ResultScanner;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.util.Bytes;

/**
 * @author wxb hbase?????????????
 */
public class HBaseSample {
public static String hbase_svr_ip = "192.168.1.104, 192.168.1.105, 192.168.1.106";
    public static String hbase_svr_port = "2181";
    public static String hbase_svr_hostname = "Node4,Node5,Node6";
    private HConnection connection = null;
    Configuration config = null;

    /**
     * ???????????????HBaseSample????????????????close????????????е????????????е????
     */
    public HBaseSample() {
        config = HBaseConfiguration.create();
        config.set("hbase.zookeeper.quorum", hbase_svr_hostname);
        config.set("hbase.zookeeper.property.clientPort", hbase_svr_port);
        // System.out.println(config.get("hbase.zookeeper.quorum"));
        // System.out.println(config.get("hbase.zookeeper.property.clientPort"));

        try {
            connection = HConnectionManager.createConnection(config);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * ??????
     */
    public void close() {
        try {
            if (null != connection) {
                connection.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * ???????
     * 
     * @param tableName
     * @param columnFarily
     */
    public void createTable(final String tableName, String columnFarily) {
        if (null != config) {
            System.out.println("begin create table...");
            HBaseAdmin admin = null;
            try {
                admin = new HBaseAdmin(config);
                if (admin.tableExists(tableName)) {
                    System.out.println(tableName + " is already exist!");
                } else {
                    HTableDescriptor tableDesc = new HTableDescriptor(tableName);
                    tableDesc.addFamily(new HColumnDescriptor(columnFarily));
                    admin.createTable(tableDesc);
                    System.out.println(tableDesc.toString()
                            + " has been created.");
                }
                admin.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("hbase could not connected!");
        }
    }

    /**
     * ????????????????????
     * 
     * @param table
     * @param key
     * @param family
     * @param col
     * @param dataIn
     * @return
     */
    public boolean addOneRecord(String table, String key, String family,
            String col, byte[] dataIn) {
        if (null != connection) {
            try {
                HTableInterface tb = connection.getTable(table);
                Put put = new Put(key.getBytes());
                put.add(family.getBytes(), col.getBytes(), dataIn);
                tb.put(put);
                System.out.println("put data key = " + key);
                return true;
            } catch (IOException e) {
                System.out.println("put data failed.");
                return false;
            }
        } else {
            System.out.println("hbase could not connected!");
            return false;
        }
    }

    /**
     * ???hbase?????е??
     * 
     * @return
     */
    public List getAllTables() {
        List tables = null;
        if (connection != null) {
            try {
                HTableDescriptor[] allTable = connection.listTables();
                if (allTable.length > 0)
                    tables = new ArrayList();
                for (HTableDescriptor hTableDescriptor : allTable) {
                    tables.add(hTableDescriptor.getNameAsString());
                    System.out.println(hTableDescriptor.getNameAsString());
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("hbase could not connected!");
        }
        return tables;
    }

    public byte[] getValueWithKey(String tableName, String rowKey,
            String family, String qualifier) {
        byte[] rel = null;
        if (null != connection) {
            try {
                HTableInterface table = connection.getTable(tableName);
                Get get = new Get(rowKey.getBytes());
                get.addColumn(Bytes.toBytes(family), Bytes.toBytes(qualifier));
                Result result = table.get(get);
                if (!result.isEmpty()) {
                    rel = result.getValue(Bytes.toBytes(family),
                            Bytes.toBytes(qualifier));
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("hbase could not connected!");
        }
        return rel;
    }

    /**
     * ???????????
     * 
     * @param tableName
     * @param rowKey
     */
    public void deleteWithKey(String tableName, String rowKey) {
        if (null != connection) {
            try {
                HTableInterface table = connection.getTable(tableName);
                Delete delete = new Delete(rowKey.getBytes());
                table.delete(delete);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("hbase could not connected!");
        }
    }

    /**
     * ?????????е????????
     * 
     * @param tableName
     */
    public void getAllData(String tableName) {
        if (null != connection) {
            try {
                HTableInterface table = connection.getTable(tableName);
                Scan scan = new Scan();
                ResultScanner rs = table.getScanner(scan);
                for (Result r : rs) {
                    Cell[] cells = r.rawCells();
                    System.out.println("This row have " + cells.length
                            + " cells:");
                    for (Cell cell : cells) {
                        String row = Bytes.toString(CellUtil.cloneRow(cell));
                        String family = Bytes.toString(CellUtil
                                .cloneFamily(cell));
                        String qualifier = Bytes.toString(CellUtil
                                .cloneQualifier(cell));
                        String value = Bytes
                                .toString(CellUtil.cloneValue(cell));
                        System.out.println(String.format("%s:%s:%s:%s", row,
                                family, qualifier, value));
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("hbase could not connected!");
        }
    }

    public void deleteTable(String tableName) {
        if (null != config) {
            System.out.println("begin delete table...");
            HBaseAdmin admin = null;
            try {
                admin = new HBaseAdmin(config);
                if (!admin.tableExists(tableName)) {
                    System.out.println(tableName + " is not exist!");
                } else {
                    admin.disableTable(tableName);
                    admin.deleteTable(tableName);
                    System.out.println(tableName + " has been deleted.");
                }
                admin.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("hbase could not connected!");
        }
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        HBaseSample sample = new HBaseSample();
        // 1.create table and insert data
        sample.createTable("student", "fam1");
        sample.addOneRecord("student", "id1", "fam1", "name", "Jack".getBytes());
        sample.addOneRecord("student", "id1", "fam1", "address",
                "HZ".getBytes());

        // 2.list table
        sample.getAllTables();

        // 3.getValue
        byte[] value = sample.getValueWithKey("student", "id1", "fam1",
                "address");
        System.out.println("value = " + Bytes.toString(value));

        // 4.addOneRecord and delete
//      sample.addOneRecord("student", "id2", "fam1", "name", "wxb".getBytes());
//      sample.addOneRecord("student", "id2", "fam1", "address",
//              "here".getBytes());
//      sample.deleteWithKey("student", "id2");

        // 5.scan table
        sample.getAllData("student");

        // 6.delete table
        // sample.deleteTable("student");

        sample.close();
    }
}

4. Phoenix?????????????

???????????????HBase????????????????????????????????????????SQL????????????????HBase?????Apache Phoenix????????????????????????SQL????????????С???????????????????y?SQL?????????HBase????????

4.1 Phoenix???

?????????????????Phoenix?????????????????????й????汾?????????????????????????С?????Phoenix4.x?汾???м???HBase0.98??汾?????????????????????????Phoenix?汾??HBase0.98??С?汾?????????
?????????????HBase0.98.1???????????Phoenix4.1.0?汾?????????Phoenix?汾??HBase?汾???????????????????????HBase???????????????????
Phoenix????尲????????£?
?????????phoenix-4.1.0-bin.tar.gz??????Node1??HBase??HMaster?????·???£????????????hadoop2/phoenix-4.1.0-server-hadoop2.jar??HBase??lib?????
????????????scp??????scp??ssh????????ο?????????????£???????????hadoop????????????regionserver??HBase??lib???£?

scp phoenix-4.1.0-server-hadoop2.jar hadoop@Node3:/home/hadoop/hbase-0.98.1-cdh5.1.0/lib/
phoenix-core-4.6.0-HBase-0.98.jar                                                                                                    
scp phoenix-4.1.0-server-hadoop2.jar hadoop@Node4:/home/hadoop/hbase-0.98.1-cdh5.1.0/lib/
phoenix-core-4.6.0-HBase-0.98.jar                                                                                                  
scp phoenix-4.1.0-server-hadoop2.jar hadoop@Node5:/home/hadoop/hbase-0.98.1-cdh5.1.0/lib/
phoenix-core-4.6.0-HBase-0.98.jar
scp phoenix-4.1.0-server-hadoop2.jar hadoop@Node6:/home/hadoop/hbase-0.98.1-cdh5.1.0/lib/
phoenix-core-4.6.0-HBase-0.98.jar            

??????????HMaster??????hbase????Node1????
?????????phoenix-4.1.0-client-hadoop2.jar?????????CLASSPATH????·???У?????????.bash_profile????????????????????hbase??lib?????
???岽?????????phoenix??????????

sqlline.py Node4:2181

?????????????ZooKeeper????????????
????????????????????????

[hadoop@iips25 hadoop2]$bin/sqlline.py Node1:2181
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:Node4 none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:Node4
16/06/21 08:04:24 WARN impl.MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-phoenix.properties,hadoop-metrics2.properties
Connected to: Phoenix (version 4.1)
Driver: org.apache.phoenix.jdbc.PhoenixDriver (version 4.1)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
59/59 (100%) Done
Done
sqlline version 1.1.2
0: jdbc:phoenix:Node4>

??????????????phoenix????????????????????????HBase????????

0: jdbc:phoenix:Node4> !tables
+------------+-------------+------------+------------+------------+------------+---------------------------+----------------+-------------+----------------+--------+
| TABLE_CAT  | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE |  REMARKS   | TYPE_NAME  | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_B |
+------------+-------------+------------+------------+------------+------------+---------------------------+----------------+-------------+----------------+--------+
| null       | SYSTEM      | CATALOG    | SYSTEM TABLE | null       | null       | null                      | null           | null        | false          | null |
| null       | SYSTEM      | SEQUENCE   | SYSTEM TABLE | null       | null       | null                      | null           | null        | false          | null |
+------------+-------------+------------+------------+------------+------------+---------------------------+----------------+-------------+----------------+--------+
0: jdbc:phoenix:Node4>

??????????????????

0: jdbc:phoenix:Node4> create table abc(a integer primary key, b integer) ;
No rows affected (1.133 seconds)
0: jdbc:phoenix:Node4> UPSERT INTO abc VALUES (1, 1); 
1 row affected (0.064 seconds)
0: jdbc:phoenix:Node4> UPSERT INTO abc VALUES (2, 2); 
1 row affected (0.009 seconds)
0: jdbc:phoenix:Node4> UPSERT INTO abc VALUES (3, 12); 
1 row affected (0.009 seconds)
0: jdbc:phoenix:Node4> select * from abc;
+------------+------------+
|     A      |     B      |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
| 3          | 12         |
+------------+------------+
3 rows selected (0.082 seconds)
0: jdbc:phoenix:Node4>

??????????????????????????VARCHAR????

create table user ( id integer primary key, name VARCHAR);
upsert into user values ( 2, '?????2');
upsert into user values ( 1, '?????1');
select * from user;
+------------+------------+
|     ID     |    NAME    |
+------------+------------+
| 1          | ?????1        |
| 2          | ?????2         |

4.2 phoenix????

??hbase??????????????hbase-site.xml????????У?????


  hbase.regionserver.wal.codec
  org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec

??????phoenix?н????????????????????????????????Phoenix??????????????????????????????

4.3 phoenix?????

phoenix??????ο???????????????????Grammar _ Apache Phoenix.html???????
????Phoenix????????????????sqlline.py????????????????????????????ν??湤??Squirrel?????????????Phoenix????Java API??

4.3.1. ??????

???Phoenix?е????????????????????????????????????????????????????????????

CREATE TABLE IF NOT EXISTS MYTABLE (ID INTEGER PRIMARY KEY, NAME VARCHAR, SEX VARCHAR, ADDRESS VARCHAR);

4.3.2. ?????

DROP TABLE IF EXISTS MYTABLE;

4.3.3. ????????

UPSERT INTO MYTABLE VALUES (1, 'WXB', 'MALE', '010-22222222');

???phoenix???UPSERT??????INSERT??

4.3.4. ???????

DELETE FROM MYTABLE WHERE ID = 1;

4.3.5. ???????

SELECT * FROM MYTABLE WHERE ID=1;

4.3.6. ???????

UPSERT INTO MYTABLE VALUES (1, 'WXB', 'MALE', '010-22222222');

?????????????????????????????????????UPSERT????????????????в????????????????????????????Phoenix?????????????????????

4.3.7. ????????

Phoenix????????Oracle????????????????????next???????????????????????current value???????????????е???next??????????current value??
??????????У?

CREATE SEQUENCE IF NOT EXISTS WXB_SEQ START WITH 1000 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 999999999 CYCLE CACHE 30;

?京?????????Oracle?????

4.3.8. ???????

?????????Select????Upsert???????????????Upsert??????

UPSERT INTO MYTABLE VALUES (NEXT VALUE FOR WXB_SEQ, 'WXB', 'MALE', '010-22222222');

??????е?????????????????

SELECT CURRENT VALUE FOR WXB_SEQ DUALID FROM WXB_DUAL;

?????DUALID????????е??????
?????WXB_DUAL?????????????????????????????Oracle?е?Dual???

CREATE TABLE  IF NOT EXISTS WXB_DUAL (DUALID INTEGER PRIMARY KEY );
UPSERT INTO WXB_DUAL VALUES (1);

4.3.9. ???????

DROP SEQUENCE IF EXISTS WXB_SEQ;

????????????????????????????????

5. ???SQuirrel

Squirrel???????λ??????????????????Phoenix????λ???????????????????????windows??linux???С?

5.1 ???????

???????
???ú?JDK??JAVA_HOME??CLASSPATH?????е?????????????????????windows??????linux?£?????????氲???hbase??phoenix????jar???????????????????CLASSPATH?С?windows?μ?CLASSPATH???£?

%JAVA_HOME%\lib;%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\tools.jar;D:\hbase-0.98.1-cdh5.1.0\lib;D:\phoenix-4.1.0-bin\hadoop2

linux??CLASSPATH???£?

export PHOENIX_HOME=/home/hadoop/phoenix-4.1.0-bin
export CLASSPATH=$PHOENIX_HOME/hadoop2/phoenix-4.1.0-client-hadoop2.jar:$HBASE_HOME/lib/:$CLASSPATH
export PATH=$PHOENIX_HOME/bin:$PATH

???????
??????squirrel-sql-snapshot-20160613_2107-standard.jar?????°汾??squirrel???????????????????????java -jar squirrel-sql-snapshot-20160613_2107-standard.jar????????
??????????????°??
1. Remove prior phoenix-[oldversion]-client.jar from the lib directory of SQuirrel, copy phoenix-[newversion]-client.jar to the lib directory (newversion should be compatible with the version of the phoenix server jar used with your HBase installation)
2. Start SQuirrel and add new driver to SQuirrel (Drivers -> New Driver)
3. In Add Driver dialog box, set Name to Phoenix, and set the Example URL to jdbc:phoenix:localhost.
4. Type ??org.apache.phoenix.jdbc.PhoenixDriver?? into the Class Name textbox and click OK to close this dialog.
5. Switch to Alias tab and create the new Alias (Aliases -> New Aliases)
6. In the dialog box, Name:Any name, Driver: Phoenix, User Name:Anything, Password:Anything
7. Construct URL as follows: jdbc:phoenix:zookeeper quorum server. For example, to connect to a local HBase use: jdbc:phoenix:localhost
8. Press Test (which should succeed if everything is setup correctly) and press OK to close.
9. Now double click on your newly created Phoenix alias and click Connect. Now you are ready to run SQL queries against Phoenix.
????????????URL??jdbc:phoenix:Node4??????????????????ɡ????????????£?
????д??????

5.2 ???

??????????????Squirrel????и???phoenix??????SQL???????????????????SQL???????????????

CREATE TABLE IF NOT EXISTS MYTABLE (ID INTEGER PRIMARY KEY, NAME VARCHAR, SEX VARCHAR, ADDRESS VARCHAR);

UPSERT INTO MYTABLE VALUES (1, 'WXB', 'MALE', '010-22222222');

UPSERT INTO MYTABLE VALUES (2, ??LL??, 'MALE', '010-11111111');

SELECT * FROM MYTABLE;

??????£?
????д??????
???Squirrel??????????????????????е????????????????и????phoenix??sql?????

6. ???Phoenix???SQL??????HBase

Phoenix???????????JDBC??API????????????????????????????Oracle??????????JDBC??????Phoenix???????Phoenix???????????????Ψ???????????????SQL?????????Phoenix???????????????????SQL???????????????????

6.1 Phoenix Java Coding

???????????????????Phoenix JDBC????????????????????????????????????????java.sql.*??????OracleΨ?????????driver???????????????????????????Squirrel????????????????????Squirrel?????driver???????????????????driver?????????jdbc:phoenix:ZooKeeper_hostname:port??????jdbc:phoenix:Node4,Node5,Node6:2181??????????????2181??????????????????
????????phoenix-4.1.0-client-hadoop2.jar????java?????????Libraries????????????£?

package com.wxb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author wxb  Phoenix?????????????
 * 
 */
public class PhoenixSample {
    public static String hbase_svr_ip = "192.168.1.104, 192.168.1.105, 192.168.1.106";
    public static String hbase_svr_port = "2181";
    public static String hbase_svr_hostname = "Node4,Node5,Node6";

    /*
     * ???м???????driver????????????? 1.Node4 2.Node4,Node5,Node6 3.Node4:2181
     * 4.Node4,Node5,Node6:2181 5.Node4:2181,Node5:2181,Node6:2181
     * 6.101.60.27.114
     */
    public static String driver = "jdbc:phoenix:" + hbase_svr_hostname;

    public static void createTable(String tableName) {
        System.out.println("create table " + tableName);
        Statement stmt = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            stmt = con.createStatement();

            stmt.executeUpdate("create table  if not exists " + tableName
                    + " (mykey integer not null primary key, mycolumn varchar)");
            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void addRecord(String tableName, String values) {
        Statement stmt = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            stmt = con.createStatement();

            stmt.executeUpdate("upsert into " + tableName + " values ("
                    + values + ")");
            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void deleteRecord(String tableName, String whereClause) {
        Statement stmt = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            stmt = con.createStatement();

            stmt.executeUpdate("delete from " + tableName + " where "
                    + whereClause);
            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void createSequence(String seqName) {
        System.out.println("Create Sequence :" + seqName);
        Statement stmt = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            stmt = con.createStatement();

            stmt.executeUpdate("CREATE SEQUENCE IF NOT EXISTS "
                    + seqName
                    + " START WITH 1000 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 999999999 CYCLE CACHE 30");
            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void dropSequence(String seqName) {
        System.out.println("drop Sequence :" + seqName);
        Statement stmt = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            stmt = con.createStatement();

            stmt.executeUpdate("DROP SEQUENCE IF EXISTS " + seqName);
            con.commit();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void getAllData(String tableName) {

        System.out.println("Get all data from :" + tableName);
        ResultSet rset = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            PreparedStatement statement = con.prepareStatement("select * from "
                    + tableName);
            rset = statement.executeQuery();
            while (rset.next()) {
                System.out.print(rset.getInt("mykey"));
                System.out.println(" " + rset.getString("mycolumn"));
            }
            statement.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void dropTable(String tableName) {

        Statement stmt = null;

        try {
            Connection con = DriverManager.getConnection(driver);
            stmt = con.createStatement();

            stmt.executeUpdate("drop table  if  exists " + tableName);
            con.commit();
            con.close();
            System.out.println("drop table " + tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        createTable("wxb_test");
        createSequence("WXB_SEQ_ID");

        // ?????Sequence
        addRecord("wxb_test", "NEXT VALUE FOR WXB_SEQ_ID,'wxb'");
        addRecord("wxb_test", "NEXT VALUE FOR WXB_SEQ_ID,'wjw'");
        addRecord("wxb_test", "NEXT VALUE FOR WXB_SEQ_ID,'wjl'");

        // deleteRecord("wxb_test", " mykey = 1 ");
        getAllData("wxb_test");

        // dropTable("wxb_test");
//      dropSequence("WXB_SEQ_ID");

    }
}

6.2 ??????????????????

?????Phoenix????????????????????????????????????????????????????????????????????????????ζ????select????where?????????????????????????????????????????????????????????????????
????????????????????????У??????????????????е??????????????

6.3 JDBC?????

Phoenix?????????????JDBC???????????????JDBC??????????????????Driver?????????????ɡ?

6.4 ???????

?漰???????ο??????VARCHAR??????????????????

6.5 CLOB??BLOB

CLOB??BLOB???????????VARCHAR???????????洢400k??????????????????????в????

6.6 ?????SQL???

???????????Phoenix?汾???????°棬?????????????SQL??????????????????????????????????????

delete from wxb_senword where swid in (select swid from wxb_rela_sw_group where groupid=1)

???????Щ?????SQL??????????????????????????phoenix?н??в??????????????????????????á?
?????????????????е????????£?

SELECT d.swid,d.swname, d.userid, e.groupid FROM wxb_senword d JOIN wxb_rela_sw_group e ON e.swid = d.swid where e.groupid=1;

7. Phoenix???????

7.1 ???????????

??????SQL??java?????????Phoenix??????????Oracle????????????????£?

??Oracle?е????б???Phoenix???????????Σ??????????????????????????????????????У??? ??Oracle?????е????С????????Phoenix???????????Σ? ???????е????SQL????????Phoenix??SQL?????????????????????????У?????????????????????????????????

7.2 Oracle??HBase?????????

???????????????debug??????????????????????????????????????÷???????????????????HBase????????·???????????????????????
Oracle??????·??????????????????в???????????????м????????????????????????????????Oracle???????????????????????????????????????????о?????????????????????????Oracle????????????м??????????????????????忴??????????????м??????????
??HBase????????????????洢???????????????????????????????????ò????????????????????????????????????????????????????????????????????Phoenix????????????????????????????????????????0.2???????????????????ü?????????????????????????Oracle????????????????30ms???????????????

7.3 Phoenix???????????

??Oracle????Phoenix???????????????????????????????????£??????????????
?????±??

CREATE TABLE IF NOT EXISTS WXB_WORD (ID INTEGER PRIMARY KEY, NAME VARCHAR, VALUE DOUBLE, HEAT INTEGER, FOCUSLEVEL INTEGER, USERID INTEGER);

????????????????£????????????????в????????????????????(???????????)??

SELECT * FROM WXB_WORD WHERE NAME=??XXX??;

50???????????????????????0.38??
100???????????????????????0.79??
500???????????????????????4.31??
?????NAME?????????????????????????????1????????????????????0.164???????????Phoenix????????????????????????

7.4 Phoenix???????

Phoenix?е???????????Secondary Indexing??????????????????????HBase????????????????????HBase?У?????????????????????????????????????????????????????в????????????????????????裬Ч???????????Phoenix?У????????е??κ??????λ????????ν????????????????????????????????????????а?????????????????????????????????У?????????include????а??????У?????????????в???????????????????в?????????????????????????????????????????н??в????
???Phoenix??????????????????????????????????????????Phoenix?????????????????д??Ч??????
Phoenix???????????????????????????mutable indexing??????????????immutable indexing?????????????????仯??????????????????ó?????????д???????????????????ò??????????????????????????????

7.5 ??????????????????

???????????????μ??Phoenix?????????HBase????????????????hbase-site.xml????????У?????


  hbase.regionserver.wal.codec
  org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec

???磺??WXB_WORD?????NAME??ν???DESC????????????????????VALUE??ε???????Phoenix???Сд????е????

create index if not exists idx_wxb_word on wxb_word (name desc) include (value) ;

???????????????????

select name,value from wxb_word where name='AHNHLYPKGYAR_59999';

??????????????л?????????????ε???????磺

select name,value,userid from wxb_word where name='AHNHLYPKGYAR_59999';

????????????????????????????????а???userid?????Ρ?
????????????????????????????????????????????HBASE???????????
??????????

drop index if exists idx_wxb_word on wxb_word;

8. ???

???Phoenix??SQL?????????HBase???????????????
???????ó??????????????????????д洢???????????????????????????????????????????????£?Phoenix??????Oracle????????????????????????????????ù????????HBase????????????????????????
?????Phoenix??HBase??Hadoop??ZooKeeper??汾?????????????????£?????????????????HBase??Hadoop??ZooKeeper??汾???????????????Phoenix?汾???????????????????????????μ?Phoenix?汾???????????д???????????????????ЩPhoenix SQL????????ò???????
?????????Phoenix?????????????????????????????HBase??Primary rowkey????????????????????????????????????????????????????????????????????SQL???????????????????????????????????????????????????????任????????????????????????????????????????????????????????
?????????????????????SQL???????????????Phoenix??伴?ɡ????ο?Phoenix????????????????????в????Phoenix??JDBC??????????????????????????????????????????????????????????SQL?????м??????????????????????δ????
???壬??????Phoenix?????????????????????о??????????????????????Ρ?????????????????????????????????????????????

Tag????? ??????
  • ??????

About IT165 - ?????? - ??????? - ??????? - ???????? - ?????? - ??????? - ??????
??????????????????,???????????缼????,??????????????????