Prestodb | Configuring for accessing Mysql, SqlServer, Redshift and Hive

-
Im trying to configure PrestoDB ( distributed query engine) for my testing/understanding purposes. Here are the steps I did to deploy prestodb in my env.

PrestoDB Setup:
  1. Download the latest prestoDB server(presto-server-<version>.tar.gz) from https://prestodb.io/docs/current/installation/deployment.html
  2. Download presto-CLI(command line interface)- ( presto-cli-<version>-executable.jar) from https://prestodb.io/docs/current/installation/cli.html
  3. Below is my master-worker setup
    • MASTER ( or coordinator)
      • IP- 10.x.x.123
      • works as both Coordinator and worker
    • WORKER
      • IP- 10.x.x.122
      • works are worker
      • presto CLI works from this machine
  4. DBs configured - 
    • Mysql 
    • MSSql-server
    • Redshift
    • Hive

Deployment Steps on MASTER / COORDINATOR
  1. create a folder called "prestodb". I would like to keep all my directories, related to prestodb, under one folder. but in reality you can keep your folder anywhere you want.
  2. now create a folder like "data" in dir - "prestodb"
  3. Copy the file presto-server-<version>.tar.gz into dir prestodb
  4. unzip the above file and you will see a folder like "presto-server-<version>"
  5. Now create a "etc" folder under "presto-server-<version>"
  6. Under "etc" folder create 3 empty config files
    • config.properties
    • jvm.config
    • node.properties
  7. Create a folder "catalog" within dir "etc"
  8. Create the .properties files for the required dbs. 
  9. do the above in both MASTER and WORKER nodes
  10. My folder structure
   /home/vsubr/
              |__prestodb
                        |---- data
                        |---- presto-server-<version>  ( this is the folder created after unziping the .tar.gz file) 
                                            |----etc
                                            |       |----catalog
                                            |                  |----- hive.properties
                                            |                  |----- mysql.properties
                                            |                  |----- rsdd.properties
                                            |                  |----- ss.properties
                                            |---- config.properties
                                            |---- jvm.config
                                            |---- node.properties
                                            
---------------------------------
..../etc/config.properties
---------------------------------

Master configuration ( ip=x.x.x.123)
coordinator=true
node-scheduler.include-coordinator=true
http-server.http.port=8080
query.max-memory=50GB
query.max-memory-per-node=8GB
discovery-server.enabled=true
discovery.uri=http://x.x.x.123:8080
#datasources=mysql,rsdd

Worker configuration ( ip=x.x.x.122)
coordinator=false
http-server.http.port=8080
query.max-memory=50GB
query.max-memory-per-node=8GB
discovery.uri=http://x.x.x.123:8080

---------------------------------
..../etc/node.properties
---------------------------------

Master configuration
node.environment=dev
node.id=87ef5936-fbeb-11e7-938a-005056806f7d   ( unique id can be generated using uuid tool)
node.data-dir=/home/vsubr/prestodb/data    ( data folder can be anywhere )

Worker configuration
node.environment=dev
node.id=8e643b74-fbeb-11e7-a668-005056803f70
node.data-dir=/home/vsubr/prestodb/data

---------------------------------
..../etc/jvm.config
---------------------------------

Master and Worker configuration
-server
-Xmx16G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryError

prestoDB configuration for the databases.

Do this on both the worker and master nodes.

----------------------------------------
..../etc/catalog/hive.properties
-----------------------------------------
connector.name=hive-hadoop2
hive.metastore.uri=thrift://<hive-server-ip>:9083   (you can also find this info under the Hive metastore config)

----------------------------------------
..../etc/catalog/rsdd.properties
-----------------------------------------
connector.name=redshift
connection-url=jdbc:postgresql://<redshift-endpoint-from-aws>:5439/<db-name>  (not schema)
connection-user=<rs-user>
connection-password=<password>

----------------------------------------
..../etc/catalog/ss.properties
-----------------------------------------
connector.name=sqlserver
connection-url=jdbc:sqlserver://<ms-sqlserver-ip/name>:<port>;DatabaseName=<db-name>
connection-user=<user>
connection-password=<pwd>

----------------------------------------
..../etc/catalog/mysql.properties
-----------------------------------------
connector.name=mysql
connection-url=jdbc:mysql://<mysql-ip/name>:3306
connection-user=<user-id>
connection-password=<pwd>

Starting MASTER and WORKER nodes.
  1. run this command ./prestodb/presto-server-0.192/bin/launcher run
  2. Do the same on both master and worker node
Configuring the prestoDB CLI
  1. Get to the machine where you want the CLI to be configured. It can be any machine, but I'm using my worker node(x.x.x.122) to do the job.
  2. copy the file  presto-cli-<version>-executable.jar into prestodb dir
  3. create a softlink (presto)to the above jar as below
    • presto -> ./presto-cli-0.192-executable.jar
  4. now run   ./presto --server x.x.x.123:8080
  5. you can now run the sqls here. 

Comments

Post a Comment

Popular posts from this blog

Tableau - Accessing Tableau's DB

-
Image
Click here for other Tableau related Blogs Please note that TABLEAU does not advice anyone to access their DB. So if you are doing it you are on your own. Tableau has 2 DB Postgresql for managing all its reporting repositories FireBird for mapping repositories Tools Required pgAdmin 3 for PostgreSql Flamerobin for FireDB Accessing Tableau's PostgreSQL DB Tableau has 2 buitin userid tableau = with read-only access to some of Tableau's view tblwgadmin = admin user will all privileges Please note tblwgadmin is like a admin user, so if you are using this you have to be very careful. Or if you dont admin privs but just need enough access to read the tables then use just "tableau" user. Get access for "tableau" user.  First set a password for "tableau" userid open command prompt go to <Tableau_Home>\Tableau Server\7.0\bin run -  tabadmin dbpass <give a password> Restart the server Now open pgAdmin3 and from...
Read more

Tableau : Convert ESRI shapes into Tableau Format

-
Image
Click here for other Tableau related Blogs Requirement is to convert data in ESRI Map shapes into Tableau format. Tools Required Quantum GIS -  http://www.qgis.org/en/site/ Python  Download the below Python Code -  https://github.com/venkat-vs-id/python import csv import re import sys #def convertESRI2TableauFormat( p_inputfile, p_outputfile): l_outfileName = 'c:/ESRI_suburb_tab_format.csv' # make changes here.. l_ESRIFileName ='c:/ESRI_suburb.csv' # make changes here.. rdx=0 csv.field_size_limit(sys.maxsize) outfile = open( l_outfileName, 'w', newline='') csvWriter = csv.writer( outfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL) with open( l_ESRIFileName, 'r') as csvfile: csvR = csv.reader( csvfile, delimiter=',', quotechar='"') for row in csvR: rdx += 1 ci=0 rowValues = [] rowValues.append(row[1]);# ssc_code rowValues.append(...
Read more

Tableau: Convert Oracle Spatial Data into Tableau Format

-
Click here for other Tableau related Blogs I had requirement to shapes we had in Oracle Spatial data( which we used for MapInfo) in Tableau. Since tableau doesnt support Oracle spatial type columns, I had to extract that data in Tableau readable format. Here are the steps to convert oracle spatial data into format useable by Tableau. Run the Oracle package ven_OraSpatial_2_TabData in your oracle environment. Now run the below "ConvertDataFormat" code to extract the data into Tableau format Now use extracted data in tableau ( refer to my blog -  Tableau Maps - Custom shapes / polygon - Basics ) You can also download the code from this link -   VEN_ORASPATIAL_2_TABDATA.zip Oracle package ven_OraSpatial_2_TabData SPEC CREATE OR REPLACE PACKAGE VSUBR.ven_OraSpatial_2_TabData as ----- TYPE TYPE rec_LatLong IS RECORD( Polygon_ID NUMBER, LAT NUMBER, LON NUMBER ); TYPE tab...
Read more