WebUI for query engines: Hive and Presto
Shib is web client application for SQL-like query engines, written in Node.js, supporting
Once configured, we can switch query engines per executions.
Some extension features are supported:
Latest version of 'shib' is v1.0.2.
'shib' versions are:
~> v0.10.26)v0.1 tagFor Hive queries, shib requires HiveServer or HiveServer2. Setup and run these.
hive.server2.authentication as NOSASLhive.support.concurrency as falseFor Presto, shib is tested with Presto version 0.57.
To run shib, you must install node.js (v0.10.x recommended), and export PATH for installed node.
Clone shib code.
$ git clone git://github.com/tagomoris/shib.gitInstall libraries, configure addresses of HiveServer (and other specifications).
$ npm install
$ vi config.jsAnd run.
$ npm startShib listens on port 3000. see http://localhost:3000/
To switch environments for each shib instance, use NODE_ENV environment variable. (ex: production.js will be used with NODE_ENV=production)
$ NODE_ENV=production NODE_PATH=lib node app.jsMigration operation required to execute shib v1, with data in v0 era.
npm run migrate (for var/database.sqlite3 file)var/database.sqlite3.v0Shib can have 2 or more query executor engines.
Basic configuration with HiveServer2 in config.js (or productions.js):
var servers = exports.servers = {
listen: 3000,
fetch_lines: 1000, // lines per fetch in shib
query_timeout: null, // shib waits queries forever
setup_queries: [],
storage: {
datadir: './var'
},
engines: [
{ label: 'mycluster1',
executer: {
name: 'hiveserver2',
host: 'hs2.mycluster1.local',
port: 10000,
username: 'hive',
support_database: true
},
monitor: null
},
],
};username should be same as user name that hive job will be executed on. (password is not required for NOSASL transport.)
For UDFs, you can specify statements before query executions in setup_queries.
var servers = exports.servers = {
listen: 3000,
fetch_lines: 1000,
query_timeout: null,
setup_queries: [
"add jar /path/to/jarfile/foo.jar",
"create temporary function foofunc as 'package.of.udf.FooFunc'",
"create temporary function barfunc as 'package.of.udf.BarFunc'"
],
storage: {
datadir: './var'
},
engines: [
{ label: 'mycluster1',
executer: {
name: 'hiveserver2',
host: 'hs2.mycluster1.local',
port: 10000,
support_database: true
},
monitor: null
},
],
};Classic HiveServer is available if you want database supports instead of HiveServer2.
var servers = exports.servers = {
listen: 3000,
fetch_lines: 1000,
query_timeout: null,
setup_queries: [],
storage: {
datadir: './var'
},
engines: [
{ label: 'mycluster1',
executer: {
name: 'hiveserver', // HiveServer(1)
host: 'hs1.mycluster1.local',
port: 10000,
support_database: true,
default_database: 'mylogs1'
},
monitor: null
},
],
};For Presto, use presto executer.
var servers = exports.servers = {
listen: 3000,
fetch_lines: 1000,
query_timeout: 30, // 30 seconds for Presto query timeouts (it will fail)
setup_queries: [],
storage: {
datadir: './var'
},
engines: [
{ label: 'prestocluster1',
executer: {
name: 'presto',
host: 'coordinator.mycluster2.local',
port: 8080,
user: 'shib',
catalog: 'hive', // required configuration argument
support_database: true,
default_database: 'mylogs1'
},
monitor: null
},
],
};For BigQuery, use bigquery executer.
var servers = exports.servers = {
listen: 3000,
fetch_lines: 1000,
query_timeout: 30, // 30 seconds for BigQuery query timeouts (it will fail)
storage: {
datadir: './var'
},
engines: [
{ label: 'bigquery',
executer: {
name: 'bigquery',
default_database: 'mylogs1',
project_id: 'gcp-project-id',
key_filename: '/path/to/keyfile.json'
},
monitor: null
}
]
};For more detail about project_id and key_filename config, see https://github.com/GoogleCloudPlatform/gcloud-node#authorization .
Shib supports 2 or more engines for a cluster, and 2 or more clusters with same engines. These patterns are available.
You should write configurations in engines how you wants. fetch_lines, query_timeout and setup_queries in each engines overwrites global default of these configurations.
For example, This is configuration example.
foo.jar are availabefoo.jar are availablehive catalog and hive2 catalog…Shib have access control list for Databases/Tables. Default is 'allow' for all databases/tables.
Shib's access control rules are:
'unvisible' databases and tables:
Access control options are written in 'executer' like this:
executer: {
name: 'presto',
host: 'coordinator.p.cluster.local',
port: 8080,
catalog: 'hive',
support_database: true,
default_database: 'default',
query_timeout: 30,
setup_queries: [],
access_control: {
databases: {
secret: { default: "deny" },
member: { default: "deny", allow: ["users"] },
test: { default: "allow", deny: ["secretData", "userMaster"] },
},
default: "allow"
}
},For more details, see wiki: Access Control.
monitor configurations are used to get query status and to kill queries.
jobtracker monitor is available in MRv1 environment (w/ both of hiveserver and hiveserver2).
monitor: {
name: 'jobtracker',
host: 'jobtracker.hostname.local',
port: 50030,
mapred: '/usr/bin/mapred' // 'mapred' in PATH by default
}For this feature, shib should be executed by a user who can execute command mapred job -kill JOB_ID.
yarn monitor is available in MRv2 environment (w/ both of hiveserver and hiveserver2).
monitor: {
name: 'yarn',
host: 'resourcemanager.hostname.local',
port: 8088
}In this case, shib kills query with Resource Manager REST API.
If you specify yarn command description, shib kills query with yarn application -kill APP_ID.
monitor: {
name: 'yarn',
host: 'resourcemanager.hostname.local',
port: 8088,
yarn: '/usr/bin/yarn'
}For monitors in CDH4 + MRv1 environment, Huahin manager is available.
To show map/reduce status, and/or to kill actual map/reduce jobs behind hive query, shib can use Huahin Manager. Current version supports only 'Huahin Manager CDH4 + MRv1' only.
http://huahinframework.org/huahin-manager/
Configure monitor argument like below instead of null.
monitor: {
name : 'huahin_mrv1',
host: 'localhost',
port: 9010
}Shib have authentication to log who execute queries and to control accesses: setup_queries_auth option means to specify queries executed before main query when authentication is required.
…For more details, see wiki: Authentication.
Specify disable_history: true on servers.
var servers = exports.servers = {
listen: 3000,
fetch_lines: 1000,
query_timeout: null, // seconds. (null:shib will wait query response infinitely).
setup_queries: [],
disable_history: true,
storage: {
datadir: './var'
},POST query string into /execute with some parameters.
curl -s -X POST -F 'querystring=SELECT COUNT(*) AS cnt FROM yourtable WHERE field="value"' http://shib.server.local:3000/execute | jq .
{
"queryid": "69927e67c5b1d5f665697943cc4867ec",
"results": [],
"dbname": "default",
"engine": "hiveserver",
"querystring": "SELECT COUNT(*) AS cnt FROM yourtable WHERE field=\"value\""
}Specify engineLabel and dbname for non-default query engines and databases:
curl -s -X POST -F "engineLabel=presto" -F "dbname=testing" -F "querystring=SELECT COUNT(*) AS cnt FROM yourtable WHERE field='value'" http://shib.server.local:3000/executeIf you want not to add your query into history tab, specify 'scheduled':
curl -s -X POST -F "scheduled=true" -F "querystring=SELECT COUNT(*) AS cnt FROM yourtable WHERE field='value'" http://shib.server.local:3000/executeThen, fetch query's status whenever you want.
curl -s http://shib.server.local:3000/status/69927e67c5b1d5f665697943cc4867ec
executedOr get whole query object.
curl -s http://shib.server.local:3000/query/69927e67c5b1d5f665697943cc4867ec | jq .
{
"queryid": "69927e67c5b1d5f665697943cc4867ec",
"results": [
{
"resultid": "969629614dff69411a2f4f1733c9616a",
"executed_at": "Wed Feb 26 2014 16:02:00 GMT+0900 (JST)"
}
],
"dbname": "default",
"engine": "hiveserver",
"querystring": "SELECT COUNT(*) AS cnt FROM yourtable WHERE field=\"value\""
}If this query object has executed status, or a member of results, you can fetch its result by resultid.
…These HTTP requests/response are same with that javascript on browser does.
Copyright 2011- TAGOMORI Satoshi (tagomoris)
Licensed under the Apache License, Version 2.0 (the
No open issues yet, or sync has not completed.