Use OpenTSDB Ambari service to store/visualize stock data on HDP sandbox Goal:
OpenTSDB (Scalable Time Series DB) allows you to store and serve massive amounts of time series data without losing granularity (more details here). In this tutorial we will install it on Hbase on HDP sandbox using the Ambari sevice and use it to import and visualize stock data. Steps: Setup VM and install Ambari service
Download HDP latest sandbox VM image (.ova file) from Hortonworks website Import ova file into VMWare and ensure the VM memory size is set to at least 8GB Now start the VM After it boots up, find the IP address of the VM and add an entry into your machines hosts file e.g. 192.168.191.241 sandbox.hortonworks.com sandbox
Connect to the VM via SSH (password hadoop) ssh root@sandbox.hortonworks.com
Start HBase service from Ambari and ensure Hbase is up and root has authority to create tables. You can do this by trying to create a test table hbase shell
create 't1', 'f1', 'f2', 'f3'
If this fails with the below, you will need to provide appropriate access via Ranger (http://sandbox.hortonworks.com:6080) ERROR: org.apache.hadoop.hbase.security.AccessDeniedException: Insufficient permissions for user 'root (auth:SIMPLE)' (global, action=CREATE) To deploy the OpenTSDB service, run below VERSION=`hdp-select status hadoop-client | sed 's/hadoop-client - \([0-9]\.[0-9]\).*/\1/'`
sudo git clone https://github.com/hortonworks-gallery/ambari-opentsdb-service.git /var/lib/ambari-server/resources/stacks/HDP/$VERSION/services/OPENTSDB
Restart Ambari #on sandbox
sudo service ambari restart
#on non-sandbox clusters
sudo service ambari-server restart
sudo service ambari-agent restart
Then you can click on 'Add Service' from the 'Actions' dropdown menu in the bottom left of the Ambari dashboard: On bottom left -> Actions -> Add service -> check OpenTSDB server -> Next -> Next -> Customize as needed -> Next -> Deploy You can customize the port, ZK quorum, ZK dir in the start command. Note that Hbase must be started if the option to automatically create OpenTSDB schema is selected On successful deployment you will see the OpenTSDB service as part of Ambari stack and will be able to start/stop the service from here: You can see the parameters you configured under 'Configs' tab One benefit to wrapping the component in Ambari service is that you can now automate its deployment via Ambari blueprints or monitor/manage this service remotely via REST API export SERVICE=OPENTSDB
export PASSWORD=admin
export AMBARI_HOST=sandbox.hortonworks.com
export CLUSTER=Sandbox
#get service status
curl -u admin:$PASSWORD -i -H 'X-Requested-By: ambari' -X GET http://$AMBARI_HOST:8080/api/v1/clusters/$CLUSTER/services/$SERVICE
#start service
curl -u admin:$PASSWORD -i -H 'X-Requested-By: ambari' -X PUT -d '{"RequestInfo": {"context" :"Start $SERVICE via REST"}, "Body": {"ServiceInfo": {"state": "STARTED"}}}' http://$AMBARI_HOST:8080/api/v1/clusters/$CLUSTER/services/$SERVICE
#stop service
curl -u admin:$PASSWORD -i -H 'X-Requested-By: ambari' -X PUT -d '{"RequestInfo": {"context" :"Stop $SERVICE via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' http://$AMBARI_HOST:8080/api/v1/clusters/$CLUSTER/services/$SERVICE
To remove the OpenTSDB service:
Stop the service via Ambari
Delete the service #Ambari password
export PASSWORD=admin
#Ambari host
export AMBARI_HOST=localhost
export SERVICE=OPENTSDB
#detect name of cluster
output=`curl -u admin:$PASSWORD -i -H 'X-Requested-By: ambari' http://$AMBARI_HOST:8080/api/v1/clusters`
CLUSTER=`echo $output | sed -n 's/.*"cluster_name" : "\([^\"]*\)".*/\1/p'`
curl -u admin:$PASSWORD -i -H 'X-Requested-By: ambari' -X PUT -d '{"RequestInfo": {"context" :"Stop $SERVICE via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' http://$AMBARI_HOST:8080/api/v1/clusters/$CLUSTER/services/$SERVICE
curl -u admin:$PASSWORD -i -H 'X-Requested-By: ambari' -X DELETE http://$AMBARI_HOST:8080/api/v1/clusters/$CLUSTER/services/$SERVICE
Remove artifacts rm -rf /root/opentsdb
rm -rf /var/lib/ambari-server/resources/stacks/HDP/2.2/services/opentsdb-service/
Import stock data Use below sample code (taken from here) to pull 30 day intraday stock prices for a few securities in both OpenTSDB and csv formats cd
/bin/rm -f prices.csv
/bin/rm -f opentsd.input
wget https://raw.githubusercontent.com/abajwa-hw/opentsdb-service/master/scripts/google_intraday.py
python google_intraday.py AAPL > prices.csv
python google_intraday.py GOOG >> prices.csv
python google_intraday.py HDP >> prices.csv
python google_intraday.py ORCL >> prices.csv
python google_intraday.py MSFT >> prices.csv
Review opentsd.input which contains the stock proces in OpenTSDB-compatible format tail opentsd.input
Import data from opentsd.input into OpenTSDB /root/opentsdb/build/tsdb import opentsd.input --zkbasedir=/hbase-unsecure --zkquorum=localhost:2181 --auto-metric
Open WebUI and import stock data The OpenTSDB webUI login page should be at the below link (or whichever port you configured) http://sandbox.hortonworks.com:9999 Query the data in OpenTSDB webUI by entering values for:
From: pick a date from 3 weeks ago To: pick todays date Check Autoreload Metric: (e.g. volume) Tags: (e.g. symbol GOOG) You can similarly create multiple tabs Tags: symbol ORCL Tags: symbol AAPL To make the charts smoother:
Under Style tab, check the 'Smooth' checkbox Under Axes tab, check the 'Log scale' checkbox You can also open it from within Ambari via iFrame view
... View more
