2023.01

2023.01

  • Imply
  • Ingest
  • Query
  • Visualize
  • Administer
  • Deploy

›Advanced features

Overview

  • Pivot overview
  • Datasources
  • SQL

Data cubes

  • About data cubes
  • Managing data cubes
  • Visualizations
  • Dimensions
  • Measures
  • Custom dimensions and measures
  • Time compare
  • Filter by measure
  • Annotations
  • Data export

Dashboards

  • Viewing dashboards
  • Creating dashboards
  • Managing dashboards

Alerts & Reports

  • Alerts
  • Scheduled reports

User access

  • Access control
  • User management in Pivot
  • Manage users with LDAP
  • Manage users with OIDC

Advanced features

  • Feature Flags
  • Customizing the Pivot UI
  • Generating links into Pivot
  • Monitor queries
  • Explain
  • CrossTab
  • Pivot server config
  • Plywood Expressions
  • Query parameters reference

Pivot API

  • Pivot API overview
  • Pivot users API
  • Pivot UserAuth API
  • Pivot user statuses API
  • Pivot roles API
  • Pivot connections API
  • Pivot data cubes API
  • Pivot dashboards API
  • Pivot alerts API
  • Pivot reports API

Pivot server config

This information applies to on-prem Pivot instances only.

The Pivot server can read a config YAML file via the --config parameter, where you can specify the server specific options described below. Some (but not all) of these options are also available through the command line. The config is read when the Pivot process starts and a restart is required for any changes to take effect. Any option specified in the command line will override its equivalent in the config. Note that you can also specify config values through environment variables or file paths.

# verbose (boolean)

Indicates that Pivot should run in verbose mode. This will log all the database queries done by Pivot.

# varDir (string)

The var directory to be used by Pivot

# licenseFile (string)

The license file to be used by Pivot

# port (number)

The port the server should run on. Defaults to 9095.

# serverHost (string)

The host that the server will bind to. Defaults to all hosts.

# serverRoot (string)

A custom path to act as the server string. Default: ''.

Setting serverRoot to ui will tell the server to serve content from http://your-host/ui.

# healthRoute (string)

The health route that returns 200. Can be used in a load balancer to check that the instance is healthy. Defaults to health.

# serverHttpsOptions (object)

A custom object that will be passed to the node's tls.createSecureContext and will cause the server to listen on an HTTPS port. Use this option to enable TLS based serving.

Example:

serverHttpsOptions:
  cert: $read_text(../cert.pem)
  key: $read_text(../key.pem)

You can also inline the certificates like so:

serverHttpsOptions:
  cert: |
    -----BEGIN CERTIFICATE-----
    MIID/DCCAmSgAwIBAgIQa/oVbFwIQU6UP/cn9jwb0DANBgkqhkiG9w0BAQsFADBu
    ...omitted...
    bWbmFE68ei4p1mVynelYjpSwesnFrIJAvtoqx/Z8ubiExumqMRp5juoH0wHPHgVe
    UFRVFdnYcRaoYHHLw6xWgA==
    -----END CERTIFICATE-----
  key: |
    -----BEGIN PRIVATE KEY-----
    MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCx7zU0LVHdS7S+
    ...omitted...
    ODOBKSs+xekw3ioN2eFse/sovLN4icpXSBE1G5hl2zfBNEg5E3r5PtTwu8BRMBc2
    M4ZzeSarlzOFZvMaTvLM36Q=
    -----END PRIVATE KEY-----

Note that when you are writing out a PEM encoded certificate inline you must use the | multiline starter to preserve the newlines which are needed for the PEM format.

For more information about how to generate certificates with OpenSSL see the TLS documentation.

# servingMode ('single' | 'clustered')

Indicates whether the server is running in standalone mode (single) or clustered mode (clustered). Setting this option to clustered will ensure that no other options are set in a way that would not work in a clustered setup, such as using a local state store. Default to clustered.

# zookeeperConnection (string)

A standard ZK connection string. Only needs to be configured in clustered servingMode.

Example: 127.0.0.1:2181,127.0.0.1:2182,127.0.0.1:2183,127.0.0.1:2184/moon

# zookeeperPath (string)

The path within which to create the ZK objects. Default to /pivot.

# ownHostname (string)

For rolling updates to work, hostname needs to be routable, if the OS reported hostname is not routable you can override it with this setting. Defaults to the OS reported hostname.

# disableXsrfLoginCheck (boolean)

Disable the extra header used for XSRF protection in the login screen. This should only be set to true if doing a rolling deployment from 2.9.8 or below to 2.9.9 or above.

# enableApiEndpoint (boolean)

Allow access via the metadata API. Enabling this flag also enables the token configuration view in the UI. This token is persistent. It does not time out or roll over.

# secureCookie (boolean)

Instructs the server to send the cookie as secure. You should set this if the end connection goes over HTTPS, such as if serverHttpsOptions is set, or if there is a TLS terminator in use.

# keepAliveTimeout (number)

Set the keep alive timeout on the HTTP connections.

# requestLogFormat (string)

A morgan string representing how the request logs will be formatted. For more info about possible values see the Morgan API docs.

# iframe ("allow" | "deny")

Specify whether embedding within an iframe will be allowed. If set to deny, the server will set the following headers:

X-Frame-Options: "DENY"
Content-Security-Policy: "frame-ancestors 'none'"

This prevents Clickjacking. Learn more about it on MDN.

For more information, see this repository.

# trustProxy ("none" | "always")

Specify whether the server should trust the X-Forwarded-* headers.

# strictTransportSecurity ("none" | "always")

Specify that the server should set the StrictTransportSecurity header.

# keepAlivePingInterval (number)

How often (in ms) to send pings on long running queries to make sure the connection stays open. Defaults to 5000.

This is helpful with certain types of gateways and browsers that otherwise can timeout and close the connection. Note that once the first keep alive is sent the status code will be set as 200, even if the request will eventually fail.

# sessionStore

The store config that defines where the session information for the logged in users will be stored. Only used when userMode is native-users, external-users, or ldap-authentication.

Must have a key storageType set to either redis, mysql, pg, or sqlite. The rest of the object can have any options that can be passed to connect-redis (for storageType Redis) or connect-session-knex (for the other options).

Example (MySQL):

sessionStore:
  storageType: 'mysql'
  tablename: 'imply_sessions'
  connection:
    host: 'awesomeco.3242452413.us-east-1.rds.amazonaws.com'
    user: 'root'
    password: 'super-secret-1234'
    database: 'my_db'

Example (PostgreSQL):

sessionStore:
  storageType: 'pg'
  tablename: 'imply_sessions'
  connection:
    host: 'pghost'
    user: 'root'
    password: 'super-secret-1234'
    database: 'my_db'

Example (SQLite):

sessionStore:
  storageType: 'sqlite'
  connection: 'path-to/session.sqlite'

Example (MySQL + TLS):

sessionStore:
  storageType: 'mysql'
  tablename: 'imply_sessions'
  connection:
    host: 'awesomeco.3242452413.us-east-1.rds.amazonaws.com'
    user: 'root'
    password: 'super-secret-1234'
    database: 'my_db'
    ssl:
      ca: $read_text(../cert.pem)

Where the content of the ssl key is in the same format as tls.createSecureContext. Note that if you are connecting to an AWS RDS you can simply set ssl: "Amazon RDS".

# stateStore

The location, format and other options of the state store. This is an object that has the following keys:

  • type

    The type of the store, can be one of mysql, pg, or sqlite.

  • connection

    The connection string (or filename in case of SQLite).

  • tablePrefix (optional)

    The string with which to prefix the tables that will be created. Defaults to pivot-store-.

  • pool (optional)

    The min and max of the connections in the pool. Defaults to { min: 2, max: 10 }.

  • acquireConnectionTimeout (optional)

    Used to determine how long to wait before throwing a timeout error when acquiring a connection is not possible. The most common cause for this is using up all of the pool for transaction connections and then attempting to run queries outside of transactions while the pool is still full. Defaults to 60000.

Example:

stateStore:
  type: 'mysql'
  tablePrefix: 'awesome-state-'
  pool: { min: 2, max: 30 }
  acquireConnectionTimeout: 20000
  connection:
    host: 'awesomeco.3242452413.us-east-1.rds.amazonaws.com'
    user: 'root'
    password: 'super-secret-1234'
    database: 'my_db'

Example with a secure connection to the DB instance:

stateStore:
  type: 'mysql'
  tablePrefix: 'awesome-state-'
  pool: { min: 2, max: 30 }
  acquireConnectionTimeout: 20000
  connection:
    host: 'awesomeco.3242452413.us-east-1.rds.amazonaws.com'
    user: 'root'
    password: 'super-secret-1234'
    database: 'my_db'
    ssl:
      ca: |
        -----BEGIN CERTIFICATE-----
        MIID/DCCAmSgAwIBAgIQa/oVbFwIQU6UP/cn9jwb0DANBgkqhkiG9w0BAQsFADBu
        ...omitted...
        bWbmFE68ei4p1mVynelYjpSwesnFrIJAvtoqx/Z8ubiExumqMRp5juoH0wHPHgVe
        UFRVFdnYcRaoYHHLw6xWgA==
        -----END CERTIFICATE-----

Where the content of the ssl key is in the same format as tls.createSecureContext. Note that if you are connecting to an AWS RDS you can simply set ssl: "Amazon RDS".

# maxUrlEntries (number)

Default: 50000. The maxim number of saved URL states to store. Once this limit is reached the oldest URLs would be cleaned up.

# maxAlertOccurrenceEntries (object)

The maximum number of alert occurrences to store. Once this limit is reached the oldest alerts are removed. Default is 500000.

# maxReportInstanceEntries (object)

The maximum number of reports to store. Once this limit is reached the oldest reports are removed. Default is 500000.

# initialSettings (object)

A free form object that can be used to seed Pivot's initial state when first run. Once the state store is populated, this object will be ignored.

# maxTotalQueriesForExports (number)

Specify the maximum number of queries for a single download or export request. Defaults to 5000. For a fast or iterative query download in a multi-tenant application, you might need to increase this number if your data has more than 10 columns. See Configuring aggregated data download limits for more information.

# userMode

Pivot can operate in several user access modes determined by the userMode in the config.yaml file. You have to restart the server in order to affect a change to the config.

The access modes are:

  • default-user

    There is no login screen; all users accessing the server are given the privileges defined by defaultUserPermissions.

  • all-users

    There is no login screen; all users accessing the server are given user privileges meaning they have no access to the settings view and can not modify clusters. Users are allowed to create, modify, and delete data cubes and dashboards freely.

  • all-dashboard-only-users

    There is no login screen; all users accessing the server are given dashboard-only-user privileges meaning they have no access to the settings view and can not modify clusters or data cubes. Users are allowed to create, modify, and delete dashboards freely.

  • all-admin (default)

    There is no login screen; all users accessing the server are given admin privileges meaning they have full access to the settings view and can modify clusters, data cubes, and dashboards.

  • native-users

    There is a login screen and all users accessing the server must login with their username and password. When first activating this mode, if no users were previously created, a dummy admin user with the user id of admin@admin.com and password password will be created. You should login with that user and promptly create a user for yourself by navigating to the Users tab in the settings view.

    An admin user can create and delete users and change their type to user or admin. After creating a new user account you can get an invitation link to send to that user so they can define their own password.

    Note that in this user mode everyone always has access to the same data. The only thing differentiating users and admins is schema editing privileges.

  • ldap-authentication

    It is possible to use an LDAP server for external user authentication. Set the ldapOptions accordingly.

  • oidc-authentication

    You can configure Pivot for single sign-on using an external OIDC identity provider. See Manage users with OIDC for more information.

# defaultUserPermissions

An array of objects representing the permissions that will be assigned if running in userMode: default-user.

This example shows what all the possible permissions would look like, effectively giving the default user super-admin access:

defaultUserPermissions:
  - { name: 'ManageUsers' }
  - { name: 'ConfigureLookAndFeel' }
  - { name: 'ManageConnections' }
  - { name: 'ManageDatasets' }
  - { name: 'AdministerDataCubes' }
  - { name: 'AdministerDashboards' }
  - { name: 'ChangeDataCubes' }
  - { name: 'ChangeDashboards' }
  - { name: 'SeeOtherUsers' }
  - { name: 'AccessSQL' }
  - { name: 'AccessVisualization' }
  - { name: 'AccessDatasets' }
  - { name: 'QueryRawData' }

# ldapOptions

The options to pass to the LDAP server (only used when userMode: ldap-authentication). For a detailed explanation of all the possible configurations see the node-ldapauth options.

Extra options (that are not part of the ldapauth options) are:

  • rolesKey - the LDAP key from which to receive role information, this key's value can either be a string or a an array of strings, if it is a string then it will be split on (,).
  • groupKeyAttribute - the LDAP key of the group to map to the external role name of the imply user role, defaults to dn.

The values of the rolesKey parameter (falling back to defaultRole) will be looked up and mapped to Pivot roles by the External ID which can be configured in the role edit UI.

Example with roles taken from a memberOf attribute:

ldapOptions:
  url: 'ldap://ldap_host:389' # The URL of the LDAP
  bindDN: 'cn=admin,dc=imply,dc=io'
  bindCredentials: 'JonSn0w'
  searchBase: 'ou=people,dc=imply,dc=io'
  searchFilter: '(uid={{username}})'
  rolesKey: 'memberOf'

Example with roles taken a separate group search:

ldapOptions:
  url: 'ldap://ldap_host:389'
  bindDN: 'cn=admin,dc=imply,dc=io'
  bindCredentials: 'JonSn0w'
  searchBase: 'dc=imply,dc=io'
  searchFilter: '(uid={{username}})'
  groupSearchBase: 'ou=groups,dc=imply,dc=io'
  groupSearchFilter: '(member={{dn}})'
  groupSearchAttributes: ['dn', 'cn']
  groupKeyAttribute: 'dn'

Before setting these options in the config, please confirm that they work using the ldapsearch command.

ldapsearch -x -h ldap_host -p 389 -b "ou=people,dc=imply,dc=io" -D "cn=admin,dc=imply,dc=io" -w "JonSn0w" "(uid=some_username_that_exists)"

Update the parameters as necessary. If run from the same machine that will run the Pivot process, this should authenticate and return a list of users.

# superAdminUser

The username of one LDAP user that will be granted super admin access. This property is useful for bootstrapping a server with LDAP users. The user configured here can go in and set up all the roles and permissions as needed.

# defaultRole

Set this role if no role was attached to the user from LDAP.

# roleAuthority

Determines whether Pivot or LDAP is in charge of setting user roles. Can have values of native and ldap. Default: native.

When a user logs into Pivot (via LDAP) for the first time, their role is determined and registered in Pivot by reading the rolesKey key (as specified in the ldapOptions) from the returned LDAP record. If the role is not set, the role defaults to the defaultRole.

On subsequent logins, the role calculation behavior differs based on the value of roleAuthority:

  • native - the user's role is preserved to whatever was previously registered in Pivot. Any changes to the user's rolesKey in the LDAP record are ignored.

  • ldap - the user's role is taken from the LDAP record as described above (defaulting to defaultRole) and registered on every login.

# appName (string)

Changes the name on the 'app' on the login screen.

# userNameLabel

Set the user name label in the login route (default: "Email"). Set this if you are using native-users or ldap-authentication user mode and the users are meant to login with something other than their email.

# suppressUserIcon (boolean)

Suppress the user icon and menu in the top right corner of every view.

# queryCache

Sets the server side query cache options.

The possible types are:

  • none

    Do not have any query cache.

    queryCache:
      type: 'none'
    
  • local (default)

    Cache queries locally (per node). This is the default because it requires no setup, but it would be better to use the memcached option if you are running more than one query server.

  • memcached

    It is possible to define the following properties:

    • servers: the memcached server or servers to connect to

    • options: the specific connection options for the memcached server fallbacks

    • lifetime: the lifetime, in seconds, that the cache entry will persist for

    • prefix: the key prefix that could be used if the memcached server was shared with other services

    Example:

    queryCache:
      type: memcached
      servers:
        - my.memcached1.internal:11211
        - my.memcached2.internal:11211
        - my.memcached3.internal:11211
    

# latestUrl

A URL for checking the latest version. Defaults to https://static.imply.io/release/LATEST.txt. The file should contain a plain version text like 2.6.7. Update checks can be disabled by setting latestUrl to none

# decorators

A set of key-value pairs defining the Druid decorator modules by name.

Example:

decorators:
  basicAuth: './basicAuthDecorator.js'
  union: './unionDecorator.js'

# helpCenterHref

Link to the help center. If not set will not show help center button.

# showHeader ("show" | "hide" | "show-if-top-level"")

Controls visibility of data cube and dashboard headers. show will always display headers. hide will never display headers. show-if-top-level will hide headers if Pivot is embedded in an iframe, headers will still be shown in the web console.

If not set data cube and dashboard headers will default to be set to show.

# loginDisclaimerContent

Optional text which can be displayed on the login screen below the username and password fields—for example, for adding privacy or liability disclaimers. If this parameter is not set, nothing will be displayed.

# linkHostName

Specify the hostname in the links that appear to users in generated alerts and reports. This populates the link token that may appear in scheduled report and alert templates.

# featureFlagsAvailabilityThreshold

Sets the minimum maturity level of preview features that appear in the Feature flags tab, where features can be enabled in the Pivot UI. Default is alpha, which results in all levels (alpha and beta) appearing. Set to:

  • beta to have only beta-level features appear, with alpha-level features omitted, or
  • none to have no preview features appear.

See Feature flags for more information.

# featureFlagsDefault

By default, preview features are disabled in the Pivot UI. Set to enabled to enable all preview features by default.

See Feature flags for more information.

Sending email

Configure the following properties to enable email sending in pivot. You must specify both mailTransportOptions and defaultSender.

# mailTransportOptions

For all possible configurations see nodemailer's options for SMTP and other transports.

You can also specify one of nodemailer's well known SMTP services in the service field which will automatically set host and other SMTP fields depending on the service value.

Example (Mailtrap):

mailTransportOptions:
  host: 'smtp.mailtrap.io'
  port: 587
  secure: false
  auth:
    user: <userName>
    pass: <pass>

Example (Amazon SES):

mailTransportOptions:
  transportType: ses

Additional account configuration may be required to use Gmail as your provider. See using Gmail with nodemailer

Example (Gmail):

mailTransportOptions:
  service: 'gmail'
  auth:
    user: <userName>
    pass: <pass>

# defaultSender

An email address that will appear on the Sender: field.

Example:

defaultSender: '"Imply (testing)" <no-reply@imply.io>'

# emailTemplates

Contains a custom email template. An example alert email template is as follows:

emailTemplates:
  - name: 'alert'
    headers:
       X-Auto-Response-Suppress: All
       X-Priority: High
    subject: '[${severity}] ${title} has triggered'
    html: |
      <h2>Alert: ${title}</h2>
      <p>
        ${summary}
      </p>
      <p>
        Learn more: http://localhost:9095${link}
      </p>

All Pivot emails contain the X-Auto-Response-Suppress: All header by default. If you configure a custom email template, you must include this header if you want to suppress responses to Pivot emails.

See Configure an alert email for more information on the default template.

Interpolating environment variables

You can reference environment variables inside the config file.

For example:

stateStore:
  type: 'mysql'
  connection: 'mysql://%{MYSQL_CONNECTION}%:3306/pivot'
  tablePrefix: 'pivot-'

will resolve %{MYSQL_CONNECTION}% to the value of the MYSQL_CONNECTION environment variable.

Referencing files

You can reference files inside the config file.

For example:

emailTemplates:
  - name: invite
    subject: Create your pivot account
    html: $read_text(../templates/invite.html)

will resolve the html value to the contents of reference.html.

Note that files will be resolved relative to the path of the config file.

You can also use $read_json and $read_yaml for files containing JSON and YAML respectively.

Configuration overrides

You can override previously specified configuration values. Overrides can be specified as JSON or YAML.

For example, specifying a configuration file as

fixedConnections:
  - name: druid
    title: Local Druid
    type: druid
    host: localhost:8888
    timeout: 40000

and then another as

fixedConnections.0.timeout: 50000

will result in the following configuration:

fixedConnections:
  - name: druid
    title: Local Druid
    type: druid
    host: localhost:8888
    timeout: 50000
Last updated on 11/3/2022
← CrossTabPlywood Expressions →
  • Sending email
  • Interpolating environment variables
  • Referencing files
  • Configuration overrides
2023.01
Key links
Try ImplyApache Druid siteImply GitHub
Get help
Stack OverflowSupportContact us
Learn more
Apache Druid forumsBlog
Copyright © 2023 Imply Data, Inc