phoenix

   phoenix put the SQL into noSql

  phoenix是hbase之上的sql查询层。

Phoenix JDBC URL syntax?

  Thick Driver

    jdbc:phoenix:[comma-separated ZooKeeper Quorum [:port [:hbase root znode [:kerberos_principal [:path to kerberos keytab] ] ] ]

   jdbc:phoenix:zookeeper1.domain,zookeeper2.domain,zookeeper3.domain:2181:/hbase-1:phoenix@EXAMPLE.COM:/etc/security/keytabs/phoenix.keytab

  Thin Driver

        jdbc:phoenix:thin:[key=value[;key=value...]]

   jdbc:phoenix:thin:url=http://queryserver.domain:8765;serialization=PROTOBUF;authentication=SPENGO;principal=phoenix@EXAMPLE.COM;keytab=/etc/security/keytabs/phoenix.keytab

bulk load in Phoenix

Sql 导入csv格式数据（单线程）

   Create table using psql $ psql.py [zookeeper] ../examples/web_stat.sql

   Upsert CSV bulk data $ psql.py [zookeeper] ../examples/web_stat.csv

Mr 导入csv和json格式数据   

   hadoop jar phoenix-<version>-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table EXAMPLE --input /data/example.csv

  hadoop jar phoenix-<version>-client.jar org.apache.phoenix.mapreduce.JsonBulkLoadTool --table EXAMPLE --input /data/example.json

optimizing Phoenix

   Use Salting to increase read/write performance

        CREATE TABLE TEST (HOST VARCHAR NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR) SALT_BUCKETS=16

  Per-split table Salting

        CREATE TABLE TEST (HOST VARCHAR NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR) SPLIT ON ('CS','EU','NA')

  Use multiple column families

    Use compression On disk compression improves performance on large tables

           CREATE TABLE TEST (HOST VARCHAR NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR) COMPRESSION='GZ'

 

       

• Optimize cluster parameters Seehttp://hbase.apache.org/book/performance.html
• Optimize Phoenix parameters See tuning.html

  • Createindexes See faq.html#/How_do_I_create_Secondary_Index_on_a_table

Data Types

Index

INTEGER Type UNSIGNED_INT Type BIGINT Type UNSIGNED_LONG Type TINYINT Type UNSIGNED_TINYINT Type SMALLINT Type UNSIGNED_SMALLINT Type FLOAT Type

UNSIGNED_FLOAT Type DOUBLE Type UNSIGNED_DOUBLE Type DECIMAL Type BOOLEAN Type TIME Type DATE Type TIMESTAMP Type UNSIGNED_TIME Type

UNSIGNED_DATE Type UNSIGNED_TIMESTAMP Type VARCHAR Type CHAR Type BINARY Type VARBINARY Type ARRAY

Multi-Tenancy 

  multi-tenant tables

   tenant-specific connections 

SQL Support

Commands

SELECT UPSERT VALUES UPSERT SELECT DELETE DECLARE CURSOR OPEN CURSOR FETCH NEXT CLOSE CREATE TABLE

DROP TABLE CREATE FUNCTION DROP FUNCTION CREATE VIEW DROP VIEW CREATE SEQUENCE DROP SEQUENCE ALTER CREATE INDEX

DROP INDEX ALTER INDEX EXPLAIN UPDATE STATISTICS CREATE SCHEMA USE DROP SCHEMA

Other Grammar

Constraint Options Hint Scan Hint Cache Hint Index Hint Small Hint Seek To Column Hint Join Hint Serial Hint Column Def Table Ref Sequence Ref Column Ref Select Expression Select Statement Split Point Table Spec Aliased Table Ref

Join Type Func Argument Class Name Jar Path Order Expression And Condition Boolean Condition Condition RHS Operand Operand Summand Factor Term Array Constructor Sequence Cast Row Value Constructor Bind Parameter

Value Case Case When Name Quoted Name Alias Null Data Type SQL Data Type HBase Data Type String Boolean Numeric Int Long Decimal Number Comments

Transactions

schema