Phoenix 4.x HBase 0.98.1安装

下载phoenix-4.0.0-incubating.tar.gz

wget http://mirror.bit.edu.cn/apache/incubator/phoenix/phoenix-4.0.0-incubating/bin/phoenix-4.0.0-incubating.tar.gz

解压phoenix-4.0.0-incubating.tar.gz到/opt目录下

tar zxvf phoenix-4.0.0-incubating.tar.gz -C /opt

添加phoenix-core-4.0.0-incubating.jar到所有HBase region server的classpath(copy it into the HBase lib directory)中

Add the phoenix-core-[version]-incubating.jar to the classpath of every HBase region server. An easy way to do this is to copy it into the HBase lib directory.

/opt/phoenix-4.0.0/common/phoenix-core-4.0.0-incubating.jar

重启Hbase集群

Add the phoenix-[version]-incubating-client.jar to the classpath of any Phoenix client (both Hadoop-1 or Hadoop-2 compatible client jars are available in latest release).

export JAVA_HOME=/opt/jdk1.7.0_51
export CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib/rt.jar
export PHOENIX_HOME=/opt/phoenix-4.0.0
export CLASS_PATH=$CLASS_PATH:$PHOENIX_HOME/hadoop-2/phoenix-4.0.0-incubating-client.jar

注:
这里不需要在每台机器(HBase region server)上都设置phoenix-4.0.0-incubating-client.jar到classpath,通常在HBase master设置就可以了.

实例

0: jdbc:phoenix:master:2181> !help
!all Execute the specified SQL against all the current connections
!autocommit Set autocommit mode on or off
!batch Start or execute a batch of statements
!brief Set verbose mode off
!call Execute a callable statement
!close Close the current connection to the database
!closeall Close all current open connections
!columns List all the columns for the specified table
!commit Commit the current transaction (if autocommit is off)
!connect Open a new connection to the database.
!dbinfo Give metadata information about the database
!describe Describe a table
!dropall Drop all tables in the current database
!exportedkeys List all the exported keys for the specified table
!go Select the current connection
!help Print a summary of command usage
!history Display the command history
!importedkeys List all the imported keys for the specified table
!indexes List all the indexes for the specified table
!isolation Set the transaction isolation for this connection
!list List the current connections
!manual Display the SQLLine manual
!metadata Obtain metadata information
!nativesql Show the native SQL for the specified statement
!outputformat Set the output format for displaying results
(table,vertical,csv,tsv,xmlattrs,xmlelements)
!primarykeys List all the primary keys for the specified table
!procedures List all the procedures
!properties Connect to the database specified in the properties file(s)
!quit Exits the program
!reconnect Reconnect to the database
!record Record all output to the specified file
!rehash Fetch table and column names for command completion
!rollback Roll back the current transaction (if autocommit is off)
!run Run a script from the specified file
!save Save the current variabes and aliases
!scan Scan for installed JDBC drivers
!script Start saving a script to a file
!set Set a sqlline variable
!sql Execute a SQL command
!tables List all the tables in the database
!typeinfo Display the type map for the current connection
!verbose Set verbose mode on

Comments, bug reports, and patches go to ???

更多介绍请参考http://www.hydromatic.net/sqlline/manual.html

using sqlline

scott@master:/opt/phoenix-4.0.0$ sqlline.py 
Zookeeper not specified.
Usage: sqlline.sh <zookeeper> <optional_sql_file>
Example:
1. sqlline.sh localhost
2. sqlline.sh localhost ../examples/stock_symbol.sql

sqlline.py master:2181

scott@master:/opt/phoenix-4.0.0$ sqlline.py master:2181
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:master:2181 none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:master:2181
Connected to: Phoenix (version 4.0)
Driver: org.apache.phoenix.jdbc.PhoenixDriver (version 4.0)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
53/53 (100%) Done
Done
sqlline version 1.1.2
0: jdbc:phoenix:master:2181>

注:master:2181为zookeeper的地址

!tables

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

实例

create table test (mykey integer not null primary key, mycolumn varchar);
upsert into test values (1,'Hello');
upsert into test values (2,'World!');
select * from test;

执行情况

0: jdbc:phoenix:master:2181> create table test (mykey integer not null primary key, mycolumn varchar);
No rows affected (0.639 seconds)
0: jdbc:phoenix:master:2181> upsert into test values (1,'Hello');
1 row affected (0.123 seconds)
0: jdbc:phoenix:master:2181> upsert into test values (2,'World!');
1 row affected (0.025 seconds)
0: jdbc:phoenix:master:2181> select * from test;
+------------+------------+
| MYKEY | MYCOLUMN |
+------------+------------+
| 1 | Hello |
| 2 | World! |
+------------+------------+
2 rows selected (0.083 seconds)

其中master:2181为zookeeper地址

scott@master:/opt/phoenix-4.0.0$ sqlline.py master:2181 ./examples/STOCK_SYMBOL.sql 
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
Setting property: [run, ./examples/STOCK_SYMBOL.sql]
issuing: !connect jdbc:phoenix:master:2181 none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:master:2181
Connected to: Phoenix (version 4.0)
Driver: org.apache.phoenix.jdbc.PhoenixDriver (version 4.0)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
55/55 (100%) Done
Done
1/5
2/5 /*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/


-- creates stock table with single row
CREATE TABLE IF NOT EXISTS STOCK_SYMBOL (SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);
No rows affected (0.751 seconds)
3/5 UPSERT INTO STOCK_SYMBOL VALUES ('CRM','SalesForce.com');
1 row affected (0.099 seconds)
4/5 SELECT * FROM STOCK_SYMBOL;
+------------+------------+
| SYMBOL | COMPANY |
+------------+------------+
| CRM | SalesForce.com |
+------------+------------+
1 row selected (0.078 seconds)
5/5
Closing: org.apache.phoenix.jdbc.PhoenixConnection
sqlline version 1.1.2

using psql

创建WEB_STAT

scott@master:/opt/phoenix-4.0.0$ psql.py master:2181 ./examples/WEB_STAT.sql 
no rows upserted
Time: 0.784 sec(s)

导入csv格式数据到WEB_STAT表中

scott@master:/opt/phoenix-4.0.0$ psql.py master:2181 ./examples/WEB_STAT.csv 
csv columns from database.
CSV Upsert complete. 39 rows upserted
Time: 0.159 sec(s)

查看所有的表

scott@master:/opt/phoenix-4.0.0$ sqlline.py master:2181
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:master:2181 none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:master:2181
Connected to: Phoenix (version 4.0)
Driver: org.apache.phoenix.jdbc.PhoenixDriver (version 4.0)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
64/64 (100%) Done
Done
sqlline version 1.1.2
0: jdbc:phoenix:master:2181> !tables
+------------+-------------+------------+------------+------------+------------+---------------------------+----------------+-------------+----------------+----------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUC |
+------------+-------------+------------+------------+------------+------------+---------------------------+----------------+-------------+----------------+----------+
| null | SYSTEM | CATALOG | SYSTEM TABLE | null | null | null | null | null | false | null |
| null | SYSTEM | SEQUENCE | SYSTEM TABLE | null | null | null | null | null | false | null |
| null | null | STOCK_SYMBOL | TABLE | null | null | null | null | null | false | null |
| null | null | TEST | TABLE | null | null | null | null | null | false | null |
| null | null | WEB_STAT | TABLE | null | null | null | null | null | false | null |
+------------+-------------+------------+------------+------------+------------+---------------------------+----------------+-------------+----------------+----------+

查询WEB_STAT表

0: jdbc:phoenix:master:2181> select * from WEB_STAT;
+------+------------+------------+---------------------+------------+------------+----------------+
| HOST | DOMAIN | FEATURE | DATE | CORE | DB | ACTIVE_VISITOR |
+------+------------+------------+---------------------+------------+------------+----------------+
| EU | Apple.com | Mac | 2013-01-01 | 35 | 22 | 34 |
| EU | Apple.com | Store | 2013-01-03 | 345 | 722 | 170 |
| EU | Google.com | Analytics | 2013-01-13 | 25 | 2 | 6 |
| EU | Google.com | Search | 2013-01-09 | 395 | 922 | 190 |
| EU | Salesforce.com | Dashboard | 2013-01-06 | 12 | 22 | 43 |
| EU | Salesforce.com | Login | 2013-01-12 | 5 | 62 | 150 |
| EU | Salesforce.com | Reports | 2013-01-02 | 25 | 11 | 2 |
| EU | Salesforce.com | Reports | 2013-01-02 | 125 | 131 | 42 |
| EU | Salesforce.com | Reports | 2013-01-05 | 75 | 22 | 3 |
| EU | Salesforce.com | Reports | 2013-01-05 | 475 | 252 | 53 |
| EU | Salesforce.com | Reports | 2013-01-13 | 355 | 52 | 5 |
| NA | Apple.com | Login | 2013-01-01 | 35 | 22 | 40 |
| NA | Apple.com | Login | 2013-01-04 | 135 | 2 | 110 |
| NA | Apple.com | Mac | 2013-01-02 | 345 | 255 | 155 |
| NA | Apple.com | Mac | 2013-01-08 | 3 | 2 | 10 |
| NA | Apple.com | iPad | 2013-01-05 | 85 | 2 | 18 |
| NA | Apple.com | iPad | 2013-01-06 | 35 | 22 | 10 |
| NA | Apple.com | iPad | 2013-01-07 | 9 | 27 | 7 |
| NA | Google.com | Analytics | 2013-01-07 | 23 | 1 | 57 |
| NA | Google.com | Analytics | 2013-01-11 | 7 | 2 | 7 |
| NA | Google.com | Analytics | 2013-01-14 | 65 | 252 | 56 |
| NA | Google.com | Search | 2013-01-08 | 345 | 242 | 46 |
| NA | Google.com | Search | 2013-01-10 | 835 | 282 | 80 |
| NA | Google.com | Search | 2013-01-12 | 8 | 7 | 6 |
| NA | Salesforce.com | Dashboard | 2013-01-03 | 88 | 66 | 44 |
| NA | Salesforce.com | Dashboard | 2013-01-11 | 335 | 32 | 30 |
| NA | Salesforce.com | Dashboard | 2013-01-14 | 5 | 2 | 9 |
| NA | Salesforce.com | Login | 2013-01-01 | 35 | 42 | 10 |
| NA | Salesforce.com | Login | 2013-01-04 | 3 | 52 | 1 |
| NA | Salesforce.com | Login | 2013-01-04 | 23 | 56 | 45 |
| NA | Salesforce.com | Login | 2013-01-08 | 345 | 242 | 10 |
| NA | Salesforce.com | Login | 2013-01-10 | 345 | 252 | 150 |
| NA | Salesforce.com | Login | 2013-01-16 | 785 | 782 | 80 |
| NA | Salesforce.com | Login | 2013-01-17 | 355 | 242 | 33 |
| NA | Salesforce.com | Login | 2013-01-17 | 1235 | 2422 | 243 |
| NA | Salesforce.com | Reports | 2013-01-10 | 35 | 42 | 15 |
| NA | Salesforce.com | Reports | 2013-01-10 | 355 | 432 | 315 |
| NA | Salesforce.com | Reports | 2013-01-15 | 65 | 26 | 6 |
| NA | Salesforce.com | Reports | 2013-01-15 | 655 | 426 | 46 |
+------+------------+------------+---------------------+------------+------------+----------------+
39 rows selected (0.319 seconds)

Bulk CSV Data Loading

Loading via PSQL

  • Sample data

data.csv

12345,John,Doe
67890,Mary,Poppins

example

CREATE TABLE example (
my_pk bigint not null,
m.first_name varchar(50),
m.last_name varchar(50)
CONSTRAINT pk PRIMARY KEY (my_pk));
scott@master:/opt/phoenix-4.0.0/examples$ psql.py -t EXAMPLE master:2181 data.csv 
csv columns from database.
CSV Upsert complete. 2 rows upserted
Time: 0.072 sec(s)

psql参数明细

The following parameters can be used for loading data with PSQL:

Parameter	Description
-t Provide the name of the table in which to load data. By default, the name of the table is taken from the name of the CSV file.
-h Overrides the column names to which the CSV data maps and is case sensitive. A special value of in-line indicating that the first line of the CSV file determines the column to which the data maps.
-s Run in strict mode, throwing an error on CSV parsing errors
-d Supply a custom delimiter or delimiters for CSV parsing
-a Supply an array delimiter (explained in more detail below)

Loading via MapReduce

hadoop jar /opt/phoenix-4.0.0/hadoop-2/phoenix-4.0.0-incubating-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table EXAMPLE --input /input/example.csv

遇到的问题

hbase-default.xml file seems to be for and old version of HBase (0.98.1-hadoop1), this version is 0.98.1-hadoop2

hadoop jar /opt/phoenix-4.0.0/hadoop-2/phoenix-4.0.0-incubating-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table EXAMPLE --input /input/example.csv

Exception in thread "main" java.lang.RuntimeException: hbase-default.xml file seems to be for and old version of HBase (0.98.1-hadoop1), this version is 0.98.1-hadoop2
at org.apache.hadoop.hbase.HBaseConfiguration.checkDefaultsVersion(HBaseConfiguration.java:70)
at org.apache.hadoop.hbase.HBaseConfiguration.addHbaseResources(HBaseConfiguration.java:102)
at org.apache.phoenix.mapreduce.CsvBulkLoadTool.run(CsvBulkLoadTool.java:159)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:84)
at org.apache.phoenix.mapreduce.CsvBulkLoadTool.main(CsvBulkLoadTool.java:85)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.util.RunJar.main(RunJar.java:212)

修改/opt/phoenix-4.0.0/hadoop-2/phoenix-4.0.0-incubating-client.jar中hbase-default.xml文件

将846行

<property skipInDoc="true">
<name>hbase.defaults.for.version</name>
<value>0.98.1-hadoop1</value>
<description>This defaults file was compiled for version ${project.version}. This variable is used
to make sure that a user doesn't have an old version of hbase-default.xml on the
classpath.</description>
</property>

修改为

<property skipInDoc="true">
<name>hbase.defaults.for.version</name>
<value>0.98.1-hadoop2</value>
<description>This defaults file was compiled for version ${project.version}. This variable is used
to make sure that a user doesn't have an old version of hbase-default.xml on the
classpath.</description>
</property>

或者修改853行将hbase.defaults.for.version.skip设为true,默认为false

<property>
<name>hbase.defaults.for.version.skip</name>
<value>true</value>
<description>Set to true to skip the 'hbase.defaults.for.version' check.
Setting this to true can be useful in contexts other than
the other side of a maven generation; i.e. running in an
ide. You'll want to set this boolean to true to avoid
seeing the RuntimException complaint: "hbase-default.xml file
seems to be for and old version of HBase (\${hbase.version}), this
version is X.X.X-SNAPSHOT"</description>
</property>

重启hbase集群

SQL Client

下载Install jars of SQuirreL 3.5.2 for Windows/Linux/others

Remove prior phoenix-[version]-client.jar from the lib directory of SQuirrel

Copy the phoenix-[version]-client.jar into the lib directory of SQuirrel (Note that on a Mac, this is the internal lib directory).

Start SQuirrel and add new driver to SQuirrel (Drivers -> New Driver)

In Add Driver dialog box, set Name to Phoenix

Press List Drivers button and org.apache.phoenix.jdbc.PhoenixDriver should be automatically populated in the Class Name textbox. Press OK to close this dialog.

Switch to Alias tab and create the new Alias (Aliases -> New Aliases)

In the dialog box, Name: any name, Driver: Phoenix, User Name: anything, Password: anything

Construct URL as follows: jdbc:phoenix: zookeeper quorum server. For example, to connect to a local HBase use: jdbc:phoenix:localhost

Press Test (which should succeed if everything is setup correctly) and press OK to close.

Now double click on your newly created Phoenix alias and click Connect. Now you are ready to run SQL queries against Phoenix.

需拷贝/opt/phoenix-4.0.0/hadoop-1/phoenix-4.0.0-incubating-client.jar到SQuirrel安装目录的lib目录下

注意:这里是拷贝/opt/phoenix-4.0.0/hadoop-1/phoenix-4.0.0-incubating-client.jar到SQuirrel安装目录的lib目录下,我拷贝/opt/phoenix-4.0.0/hadoop-2/phoenix-4.0.0-incubating-client.jar到SQuirrel安装目录的lib目录下不能正常启动SQuirrel,很是纳闷。

参考链接

http://phoenix.incubator.apache.org/faq.html
http://phoenix.incubator.apache.org/download.html
http://phoenix.incubator.apache.org/language/index.html
http://phoenix.incubator.apache.org/bulk_dataload.html