Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
132 lines (92 sloc) 4.29 KB
Level Technologies Target Product Product Versions Source

Beginners

Teiid, HBase Translator, Foreign Table

DV

DV 6.1

https://github.com/teiid/teiid-quickstarts

What is it?

hbase-as-a-datasource demonstrates using the HBase Translator with Phoenix Data Source to access data in HBase.

System requirements

Setup and Deployment

1. Start the server

To start the server, open a command line and navigate to the "bin" directory under the root directory of the Teiid server and run:

./standalone.sh //For Linux
standalone.bat //for Windows

If Teiid isn’t configured in the default configuration, append the following arguments to the command to specify the configuration -c {configuration.file}

Example
./standalone.sh -c standalone-teiid.xml

2. Setup HBase

$ tar -xvf hbase-0.98.8-hadoop2-bin.tar.gz
$ cd hbase-0.98.8-hadoop2/
  • Download Phoenix 4.x from Phoenix Downloads Page, install Phoenix via copying phoenix-core.jar to HBase lib directory,

$ tar -xvf phoenix-4.2.1-bin.tar.gz
$ cp phoenix-4.2.1-bin/phoenix-core-4.2.1.jar hbase-0.98.8-hadoop2/lib/
$ ./bin/start-hbase.sh
$ ./bin/hbase shell
hbase(main):002:0> create 'Customer', 'customer', 'sales'
hbase(main):003:0> put 'Customer', '101', 'customer:name', 'John White'
...

3. Setup Phoenix Data Source

Note
The folowing referenced setup.cli script is configured to use the phoenix-4.2.1-client.jar. If the version you are using is different, the setup.cli script will need to be udpated.
  • Copy phoenix-[version]-client.jar and link:src/scripts/setup.cli [setup.cli] to $JBOSS_HOME,

$ cd $JBOSS_HOME
$ cp {path}/src/scripts/setup.cli ./
$ cp {path}/phoenix-4.2.1-bin/phoenix-4.2.1-client.jar ./
  • Execute CLI commands to setup Phoenix Data Source

$ ./bin/jboss-cli.sh --connect --file=setup.cli
  • Use Phoenix Command Line execute customer-schema.sql to map Customer table in HBase, for example

$ cd PHOENIX_HOME
$ ./bin/sqlline.py localhost .../src/teiidfiles/customer-schema.sql
Note
More details about Phoenix Data Sources and Mapping Phoenix table to an existing HBase table please refer to Teiid Documents.

4. Teiid Deployment

Copy the following files to the "/standalone/deployments" directory

src/vdb/hbase-vdb.xml
src/vdb/hbase-vdb.xml.dodeploy

Open the admin console(http://localhost:9990/console) to make sure the VDB is deployed. See Query Demonstrations below to demonstrate query.

Query Demonstrations

  1. Change your working directory to "${quickstart.install.dir}/simpleclient"

  2. Use the simpleclient example to run the following queries:

Example: mvn exec:java -Dvdb="portfolio" -Dsql="example query" -Dusername="xx" -Dpassword="xx"

The following sql can be used with -Dsql:

SELECT city, amount FROM Customer
SELECT DISTINCT city FROM Customer
SELECT city, amount FROM Customer WHERE PK='105'
SELECT * FROM Customer WHERE PK BETWEEN '105' AND '108'
SELECT * FROM Customer WHERE PK='105' AND name='John White'
SELECT * FROM Customer ORDER BY PK
SELECT * FROM Customer ORDER BY name, city DESC
SELECT name, city, COUNT(PK) FROM Customer GROUP BY name, city
SELECT name, city, COUNT(PK) FROM Customer GROUP BY name, city HAVING COUNT(PK) > 1
Note
depending on your OS/Shell the quoting/escaping required to run the example can be complicated. It would be better to install a Java client, such as SQuirreL, to run the queries.