HotelsDotCom / Waggle Dance

Start using

You can obtain Waggle Dance from Maven Central:

Overview

Waggle Dance is a request routing Hive metastore proxy that allows tables to be concurrently accessed across multiple Hive deployments. It was created to tackle the appearance of dataset silos that arose as our large organization gradually migrated from monolithic on-premises clusters to cloud based platforms.

In short, Waggle Dance provides a unified end point with which you can describe, query, and join tables that may exist in multiple distinct Hive deployments. Such deployments may exist in disparate regions, accounts, or clouds (security and network permitting). Dataset access is not limited to the Hive query engine, and should work with any Hive metastore enabled platform. We've been successfully using it with Spark for example.

We also use Waggle Dance to apply a simple security layer to cloud based platforms such as Qubole, DataBricks, and EMR. These currently provide no means to construct cross platform authentication and authorization strategies. Therefore we use a combination of Waggle Dance and network configuration to restrict writes and destructive Hive operations to specific user groups and applications.

We maintain a mapping of virtual database names to federated metastore instances. These virtual names are resolved by Waggle Dance during execution and requests are forwarded to the mapped metastore instance.

So when we do the following in a Hive CLI client connected to a Waggle Dance instance:

select *
from mydb.table;

We are actually performing the query against the thrift://host:port/ metastore. All metastore calls will be forwarded and data will be fetched and processed locally. This makes it possible to read and join data from different Hive clusters via a single Hive CLI.

System architecture

Install

Waggle Dance is intended to be installed and set up as a service that is constantly running and should be installed on a machine that is accessible from wherever you want to query it from and which also has access to the Hive metastore service(s) that it is federating. Waggle Dance is available as a RPM or TGZ package, steps for installation of both are covered below.

TGZ version

The TGZ package provides a "vanilla" version of Waggle Dance that is easy to get started with but will require some additional scaffolding in order to turn it into a fully-fledged service.

Download the TGZ from Maven central and then uncompress the file by executing:

tar -xzf waggle-dance-<version>-bin.tgz

Although it's not necessary, we recommend exporting the environment variable WAGGLE_DANCE_HOME by setting its value to wherever you extracted it to:

export WAGGLE_DANCE_HOME=/<foo>/<bar>/waggle-dance-<version>

Refer to the configuration section below on what is needed to customise the configuration files before continuing.

Running on the command line

To run Waggle Dance execute:

$WAGGLE_DANCE_HOME/bin/waggle-dance.sh --server-config=$WAGGLE_DANCE_HOME/conf/waggle-dance-server.yml --federation-config=$WAGGLE_DANCE_HOME/conf/waggle-dance-federation.yml

Log messages will be output to the standard output by default.

RPM version

The RPM package provides a fully-fledged service version of Waggle Dance.

Download the RPM from Maven Central and install it using your distribution's packaging tool, e.g. yum:

sudo yum install <waggle-dance-rpm-file>

This will install Waggle Dance into /opt/waggle-dance (this location is referred to as $WAGGLE_DANCE_HOME in this documentation). It will also create a log file output folder in /var/log/waggle-dance and register Waggle Dance as an init.d service.

Refer to the configuration section below on what is needed to customise the configuration files before continuing.

Running as a service

Once configured, the service needs to be started:

sudo service waggle-dance start

Currently any changes to the configuration files require restarting the service in order for the changes to take effect (the exception to this is any changes to the log4j2.xml logging config file which will be picked up while running):

sudo service waggle-dance restart

Log messages can be found in /var/log/waggle-dance/waggle-dance.log.

Configuration

In order to start using Waggle Dance it must first be configured for your environment. The simplest way to do this is to copy and then modify the template configuration files that are provided by the Waggle Dance package, i.e.:

cp $WAGGLE_DANCE_HOME/conf/waggle-dance-server.yml.template $WAGGLE_DANCE_HOME/conf/waggle-dance-server.yml
cp $WAGGLE_DANCE_HOME/conf/waggle-dance-federation.yml.template $WAGGLE_DANCE_HOME/conf/waggle-dance-federation.yml

This sets up the default YAML configuration files which need to be customised for your use case. Now edit the property remote-meta-store-uris in $WAGGLE_DANCE_HOM/conf/waggle-dance-federation.yml and modify this to contain the URI(s) of the metastore(s) you want to federate. The sections below contain further details about the available configuration settings and should be used to customise the rest of the values in these files accordingly.

Server

Server config is by default located in $WAGGLE_DANCE_HOME/conf/waggle-dance-server.yml.

The table below describes all the available configuration values for Waggle Dance server:

Federation

Federation config is by default located in: $WAGGLE_DANCE_HOME/conf/waggle-dance-federation.yml.

Example:

primary-meta-store:                                     # Primary metastore
  access-control-type: READ_AND_WRITE_AND_CREATE_ON_DATABASE_WHITELIST
  name: primary                                         # unique name to identify this metastore
  remote-meta-store-uris: thrift://127.0.0.1:9083
  writable-database-white-list:
  - my_writable_db1
  - my_writable_db2
  - user_db_.*
  - ...
federated-meta-stores:                                  # List of read only metastores to federate
- remote-meta-store-uris: thrift://10.0.0.1:9083
  name: secondary
  metastore-tunnel:
    route: [email protected] -> [email protected]
    private-keys: /home/user/.ssh/bastion-key-pair.pem,/home/user/.ssh/emr-key-pair.pem
    known-hosts: /home/user/.ssh/known_hosts
  hive-metastore-filter-hook: filter.hook.class
  mapped-databases:
  - prod_db1
  - prod_db2
  - dev_group_1.*
  mapped-tables:
  - database: prod_db1
    mapped-tables:
    - tbl1
    - tbl_.*
  - database: prod_db2
    mapped-tables:
    - tbl2
- ...

The table below describes all the available configuration values for Waggle Dance federations:

Metastore tunnel

The table below describes the metastore tunnel configuration values:

Mapped tables

The table below describes the mapped-tables configuration. For each entry in the list, a database name and the corresponding list of table names/patterns must be mentioned.

Access Control

A metastore's access control configuration is controlled by the access-control-type property.

The available values of this property are described below.

Primary metastores can configure access-control-type to have any of the described access-control-types whereas federated metastores may only be configured to READ_ONLY and READ_AND_WRITE_ON_DATABASE_WHITELIST.

There are a number of write operations in the metastore whose requests do not contain database/table name context, and so cannot be routed to federated metastore instances configured with a writeable access control level.

These include:

• Create database
• Function handling: create/delete/get functions
• Type handling: create/delete/get types
• Keys foreign/primary
• Locks
• Transactions / compact
• Security management: roles, prinicpals, grant/revoke
• Delegation tokens
• Notifications
• Config management
• File metadata / cache

This is not an issue for general operation, but may be a problem if you are wanting to use certain specific Hive features. At this time these features cannot be supported in a writable federation model.

Federation configuration storage

Waggle Dance reads and writes federation configuration from and to a YAML file - refer to the section federation for details.

The following properties are configured in the server configuration file(waggle-dance-server.yml) and control the behaviour of the YAML federation storage:

yaml-storage:
  overwrite-config-on-shutdown: true

Configuring a SSH tunnel

Each federation in Waggle Dance can be configured to use a SSH tunnel to access a remote Hive metastore in cases where certain network restrictions prevent a direct connection from the machine running Waggle Dance to the machine running the Thrift Hive metastore service. A SSH tunnel consists of one or more hops or jump-boxes. The connection between each pair of nodes requires a user - which if not specified defaults to the current user - and a private key to establish the SSH connection.

As outlined above the metastore-tunnel property is used to configure Waggle Dance to use a tunnel. The tunnel route expression is described with the following EBNF:

path = path part, {"->", path part}
path part = {user, "@"}, hostname
user = ? user name ?
hostname = ? hostname ?

For example, if the Hive metastore runs on the host hive-server-box which can only be reached first via bastion-host and then jump-box then the SSH tunnel route expression will be bastion-host -> jump-box -> hive-server-box. If bastion-host is only accessible by user ec2-user, jump-box by user user-a and hive-server-box by user hadoop then the expression above becomes [email protected] -> [email protected] -> [email protected].

Once the tunnel is established Waggle Dance will set up port forwarding from the local machine specified in metastore-tunnel.localhost to the remote machine specified in remote-meta-store-uris. The last node in the tunnel expression doesn't need to be the Thrift server, the only requirement is that this last node must be able to communicate with the Thrift service. Sometimes this is not possible due to firewall restrictions so in these cases they must be the same.

All the machines in the tunnel expression can be included in the known_hosts file and in this case the keys required to access each box should be set in metastore-tunnel.private-keys. For example, if bastion-host is authenticated with bastion.pem and both jump-box and hive-server-box are authenticated with emr.pem then the property must be set asmetastore-tunnel.private-keys=<path-to-ssh-keys>/bastion.pem, <path-to-ssh-keys>/emr.pem.

If all machines in the tunnel expression are not included in the known_hosts file then metastore-tunnel.strict-host-key-checking should be set to no.

To add the fingerprint of remote-box in to the known___hosts file the following command can be used:

ssh-keyscan -t rsa remote-box >> .ssh/known_hosts

The following configuration snippets show a few examples of valid tunnel expressions.

Simple tunnel to metastore server

    remote-meta-store-uris: thrift://metastore.domain:9083
    metastore-tunnel:
      route: [email protected]
      private-keys: /home/user/.ssh/user-key-pair.pem
      known-hosts: /home/user/.ssh/known_hosts

Simple tunnel to cluster node with current user

    remote-meta-store-uris: thrift://metastore.domain:9083
    metastore-tunnel:
      route: cluster-node.domain
      private-keys: /home/run-as-user/.ssh/key-pair.pem
      known-hosts: /home/run-as-user/.ssh/known_hosts

Bastion host to cluster node with different users and key-pairs

    remote-meta-store-uris: thrift://metastore.domain:9083
    metastore-tunnel:
      route: [email protected] -> [email protected]
      private-keys: /home/run-as-user/.ssh/bastionuser-key-pair.pem, /home/run-as-user/.ssh/user-key-pair.pem
      known-hosts: /home/run-as-user/.ssh/known_hosts

Bastion host to cluster node with same user

    remote-meta-store-uris: thrift://metastore.domain:9083
    metastore-tunnel:
      route: [email protected] -> [email protected]
      private-keys: /home/user/.ssh/user-key-pair.pem
      known-hosts: /home/user/.ssh/known_hosts

Bastion host to cluster node with current user

    remote-meta-store-uris: thrift://metastore.domain:9083
    metastore-tunnel:
      route: bastion-host.domain -> cluster-node.domain
      private-keys: /home/run-as-user/.ssh/run-as-user-key-pair.pem
      known-hosts: /home/run-as-user/.ssh/known_hosts

Bastion host to metastore via jump-box with different users and key-pairs

    remote-meta-store-uris: thrift://metastore.domain:9083
    metastore-tunnel:
      route: [email protected] -> [email protected] -> [email protected]
      private-keys: /home/run-as-user/.ssh/bastionuser-key-pair.pem, /home/run-as-user/.ssh/user-key-pair.pem, /home/run-as-user/.ssh/hive-key-pair.pem
      known-hosts: /home/run-as-user/.ssh/known_hosts

Metrics

Waggle Dance exposes a set of metrics that can be accessed on the /metrics end-point. These metrics include a few standard JVM, Spring and per-federation metrics which include per-metastore number of calls and invocation duration. If a Graphite server is provided in the server configuration then all the metrics will be exposed in the endpoint and Graphite.

The following snippet shows a typical Graphite configuration:

graphite:
  port: 2003
  host: graphite.domain
  prefix: aws.myservice.myapplication
  poll-interval: 1000
  poll-interval-time-unit: MILLISECONDS

Prometheus can also be used to gather metrics. This can be done by enabling the Prometheus endpoint in the configuration:

management.endpoints.web.exposure.include: health,info,prometheus

If this config is added, all endpoints that are required to be expose need to be specified. The Prometheus endpoint can be accessed at /actuator/prometheus.

Database Resolution

Waggle Dance presents a view over multiple (federated) Hive metastores and therefore could potentially encounter the same database in different metastores. Waggle Dance has two ways of resolving this situation, the choice of which can be configured in waggle-dance-server.yml via the property database-resolution. This property can have two possible values MANUAL and PREFIXED. These are explained below in more detail.

Database resolution: MANUAL

Waggle Dance can be configured to use a static list of databases in the configuration waggle-dance-federations.yml:federated-meta-stores[n].mapped-databases and primary-meta-store.mapped-databases. It is up to the user to make sure there are no conflicting database names in the primary-metastore or other federated metastores. If Waggle Dance encounters a duplicate database it will throw an error and won't start. Example configuration:

waggle-dance-server.yml:

database-resolution: MANUAL

waggle-dance-federation.yml:

primary-meta-store:
  name: primary
  remote-meta-store-uris: thrift://primaryLocalMetastore:9083
federated-meta-stores:
  - name: waggle_prod
    remote-meta-store-uris: thrift://federatedProdMetastore:9083
    mapped-databases:
    - etldata
    - mydata

Using this example Waggle Dance can be used to access all databases in the primary metastore and etldata/mydata from the federated metastore. The databases listed must not be present in the primary metastore otherwise Waggle Dance will throw an error on start up. If you have multiple federated metastores listed a database can only be uniquely configured for one metastore. Following the example configuration a query select * from etldata will be resolved to the federated metastore. Any database that is not mapped in the config is assumed to be in the primary metastore.

All non-mapped databases of a federated metastore are ignored and are not accessible.

Adding a mapped database in the configuration requires a restart of the Waggle Dance service in order to detect the new database name and to ensure that there are no clashes.

Database resolution: PREFIXED

Waggle Dance can be configured to use a prefix when resolving the names of databases in its primary or federated metastores. In this mode all queries that are issued to Waggle Dance need to be written to use fully qualified database names that start with the prefixes configured here. In the example below Waggle Dance is configured with a federated metastore with the prefix waggle_prod_. Because of this it will inspect the database names in all requests, and if any start with waggle_prod_ it will route the request to the configured matching metastore. The prefix will be removed for those requests as the underlying metastore knows nothing of the prefixes. So, the query: select * from waggle_prod_etldata.my_table will effectively be translated into this query: select * from etldata.my_table on the federated metastore. If a database is encountered that is not prefixed then the primary metastore is used to resolve the database name.

waggle-dance-server.yml:

database-resolution: PREFIXED

waggle-dance-federation.yml:

primary-meta-store:
  name: primary
  remote-meta-store-uris: thrift://primaryLocalMetastore:9083
federated-meta-stores:
  - name: federated
    database-prefix: waggle_prod_
    remote-meta-store-uris: thrift://federatedProdMetastore:9083

Note: When choosing a prefix ensure that it does not match the start of any existing database names in any of the configured metastores. To illustrate the problem this would cause, imagine you have a database in the primary metastore named "my_database" and you configure the federated metastore with the prefix my_. Waggle Dance will register the prefix and any requests for a database starting with my_ will be routed to the federated metastore even if they were intended to go to the primary metastore.

In PREFIXED mode any databases that are created while Waggle Dance is running will be automatically visible and will need to adhere to the naming rules described above (e.g. not clash with the prefix). Alternatively, Waggle Dance can be configured to use a static list of unprefixed databases in the configuration waggle-dance-federations.yml:federated-meta-stores[n].mapped-databases and primary-meta-store.mapped-databases. Example configuration:

waggle-dance-server.yml:

database-resolution: PREFIXED

waggle-dance-federation.yml:

primary-meta-store:
  name: primary
  remote-meta-store-uris: thrift://primaryLocalMetastore:9083
federated-meta-stores:
  - name: federated
    database-prefix: waggle_prod_
    remote-meta-store-uris: thrift://federatedProdMetastore:9083
    mapped-databases:
    - etldata

In this scenario, like in the previous example, the query: select * from waggle_prod_etldata.my_table will effectively be this query: select * from etldata.my_table on the federated metastore. Any other databases which exist in the metastore named federated won't be visible to clients.

Sample run through

Assumes database resolution is done by adding prefixes. If database resolution is done manually via the a list of configured databases the prefixes in this example can be ommitted.

Connect to Waggle Dance:

hive --hiveconf hive.metastore.uris=thrift://localhost:48869

Show databases in all your metastores:

hive> show databases;
OK
default
somedata
waggle_aws_dw_default
waggle_aws_dw_mydata
waggle_aws_dw_moredata
waggle_aws_dw_extredata
Time taken: 0.827 seconds, Fetched: 6 row(s)

Join two tables in different metastores:

select h.data_id, h.entity_id, p.entity_id, p.hotel_brand_name
  from waggle_aws_dw_mydata.some_data h
  join somedata.other_table p
    on h.entity_id = p.entity_id
 where h.date = '2016-05-13'
   and h.hour = 1
;

Database Name Mapping

NOTE: mapping names adds an extra layer of abstraction and we advise to use this as a temporary migration solution only. It becomes harder to debug where a virtual (remapped) table actually is coming from.

Waggle Dance allows one to refer to databases by names that are different to what they are defined in the Hive metastore via a database-name-mapping configuration. This feature can be useful when migrating data from existing databases into different environments. To clarify how this feature could be used, below is an example use case: As part of a data migration we have decided that we want to store all Hive tables related to hotel bookings in a database called booking. However we have legacy data lakes that contain booking-related tables which are stored in databases with other names e.g. 'datawarehouse'. To ease migration we want to expose the 'datawarehouse' database via both the original name and the booking name. This way consumers can start using the new name while producers migrate their scripts from the old name or vice versa. When the migration is done the mapping can be removed and the database renamed.

So in this example we have a Data lake X which federates tables from another Data lake Y. The desired end result is to show a 'datawarehouse' database and a 'booking' database in X which is proxied to the same 'datawarehouse' database in Y.

X: show databases;

datawarehouse (proxies to y - datawarehouse)
booking (proxies to y - datawarehouse)

Y: show databases;

datawarehouse

To achieve a unified view of all the booking tables in the different databases (without actually renaming them in Hive) we can configure Waggle Dance in X to map from the old to the new names like so:

federated-meta-stores:
  - remote-meta-store-uris: thrift://10.0.0.1:9083
    name: y
    mapped-databases:
    - datawarehouse
    database-name-mapping:
      datawarehouse: booking

Note: Both the 'datawarehouse' name and the mapped name 'booking' are shown in X, so the mapping adds an additional virtual database mapping to the same remote database. You can only map one extra name and you cannot map different databases to the same name. This is not allowed (will fail to load (invalid yaml)):

database-name-mapping:
  datawarehouse: booking
  datawarehouse: booking2

This is not allowed (will fail to load (invalid mapping)):

database-name-mapping:
  datawarehouse: booking
  datawarehouse2: booking

If an optional mapped-databases is used that filter is applied first and the renaming is applied after.

Endpoints

Being a Spring Boot Application, all standard actuator endpoints are supported.

e.g. Healthcheck Endpoint: http://localhost:18000/actuator/health

In addition to these Spring endpoints Waggle Dance exposes some custom endpoints which provide more detailed information. The URLs of these are logged when Waggle Dance starts up. The most notable is: http://host:18000/api/admin/federations, which returns information about the availability of the configured metastores (it can be used for troubleshooting, but it is not recommended for use as a health check).

Logging

Waggle Dance uses Log4j 2 for logging. In order to use a custom Log4j 2 XML file, the path to the logging configuration file has to be added to the server configuration YAML file:

logging:
    config: file:/home/foo/waggle-dance/conf/log4j2.xml

This only works when Waggle Dance is obtained from the compressed archive (.tar.gz) file. If the RPM version is being used, the default log file path is hardcoded. Refer to the RPM version section for more details.

Notes

• Only the metadata communications are rerouted.
• Access to underlying table data is still directly to the locations encoded in the metadata.
• Users of Waggle Dance must still have the relevant authority to access the underlying table data.
• All data processing occurs in the client cluster, not the external clusters. Data is simply pulled into the client cluster that connect to Waggle Dance.
• Metadata read operations are routed only. Write and destructive operations can be performed on the local metastore only.
• When using Spark to read tables with a big number of partitions it may be necessary to set spark.sql.hive.metastorePartitionPruning=true to enable partition pruning. If this property is false Spark will try to fetch all the partitions of the tables in the query which may result on a OutOfMemoryError in Waggle Dance.
• If a configuration file is updated and the update disappears after server shutdown, yaml-storage.overwrite-config-on-shutdown should be set to false in the federation configuration file (refer to the federation configuration storage section).

Limitations

Hive Views and prefixes

Support for views utilises Hive's query parsing utilities where in some cases the conversion of the original view query may fail. A Hive Table contains two properties that contain the view query: viewExpandedText and viewOriginalText. You can see the values of these by running the desc extended <table> statement. In order for a prefixed federated view to behave correctly Waggle Dance needs to manipulate the queries in those properties and prefix any databases it finds. It does so by calling the org.apache.hadoop.hive.ql.parse.ParseUtils class to parse the query where we discovered (and raised) a Hive issue (HIVE-19896). To work around this issue we store the parseable viewExpandedText in the viewOriginalText property if the viewOriginalText is not parseable. If the viewExpandedText is also not parseable we keep the untransformed original values. This might result in a slight discrepancy when using a view in a federated manner. If you run into this limitation please raise an issue on the Waggle Dance Mailing List.

Hive UDFs and prefixes

Hive UDFs are registered with a database. There are currently two limitations in how Waggle Dance deals with them:

• show functions only returns UDFs that are registered in the primary metastore.
• UDFs used in a view are not prefixed with their corresponding metastore. A workaround is to register the UDF from the federated metastore in your own (primary) metastore.

Due to the distributed nature of Waggle Dance using UDFs is not that simple. If you would like a UDF to be used from a federated metastore we'd recommend registering the code implementing it in a distributed file or object store that is accessible from any client (for example you could store the UDF's jar file on S3). See creating permanent functions in the Hive documentation.

Hive metastore filter hook

You can configure a Hive filter hook via: hive-metastore-filter-hook: filter.hook.class This class needs to be on the classpath and can be an external jar. If so the command to run needs to be updated to ensure correct class loading. This can be done by adding: -Dloader.path=<path_to_jar> Note: The database calls getDatabases and getAllDatabases, as well as getTableMeta do not support having the provided filter applied at the moment, so their result will not be modified by the filter.

Building

Waggle Dance can be built from source using Maven:

mvn clean package

This will produce a .tgz in the waggle-dance module (under waggle-dance/waggle-dance/target/) and an rpm in the waggle-dance-rpm (under waggle-dance/waggle-dance-rpm/target/rpm/waggle-dance-rpm/RPMS/noarch/). This RPM is built using the maven rpm plugin which requires the 'rpm' program to be available on the command line. On OSX this can be accomplished by using the Brew package manager like so brew install rpm.

Contact

Mailing List

If you would like to ask any questions about or discuss Waggle Dance please join our mailing list at

https://groups.google.com/forum/#!forum/waggle-dance-user

Credits

Created by Elliot West, Patrick Duin & Daniel del Castillo with thanks to: Adrian Woodhead, Dave Maughan and James Grant.

The Waggle Dance logo uses the Beetype Filled font by Adrian Candela under the Creative Commons Attribution License (CC BY).

Legal

This project is available under the Apache 2.0 License.

Copyright 2016-2019 Expedia, Inc.