A SQL parser for plywood

PlyQL is built on top of node so make sure you have node >= 0.12 installed.

npm install -g plyql

The global install will make the plyql command available system wide.

Currently only queries to Druid are supported. More support will come in the future.

The CLI supports the following options:

Say your Druid broker node is located at 10.20.30.40 on the standard port (8080) and that there is a datasource called twitterstream that has tweets in it.

Here is a simple query that gets the maximum of the __time column which tells you what how up to date the data in the database is.

plyql -h 10.20.30.40 -q "SELECT MAX(__time) AS maxTime FROM twitterstream"

Returns:

[
  {
    "maxTime": {
      "type": "TIME",
      "value": "2015-04-16T20:49:00.000Z"
    }
  }
]

Ok now you might want to examine the different hashtags that are trending.

You might do a group by on the first_hashtag column like this:

plyql -h 10.20.30.40 -q "
SELECT
first_hashtag as hashtag,
COUNT() as cnt
FROM twitterstream
WHERE '2015-04-15T00:00:00' <= __time AND __time < '2015-04-16T00:00:00'
GROUP BY first_hashtag
ORDER BY cnt DESC
LIMIT 5
"

This will throw an error because there is no time filter specified and the plyql guards against this.

This behaviour can be disabled using the --allow eternity flag but it is generally bad idea to do it when working with large amounts of data as it can issue computationally prohibitive queries.

Try it again, with a time filter:

plyql -h 10.20.30.40 -q "
SELECT
first_hashtag as hashtag,
COUNT() as cnt
FROM twitterstream
WHERE '2015-04-15T00:00:00' <= __time AND __time < '2015-04-16T00:00:00'
GROUP BY first_hashtag
ORDER BY cnt DESC
LIMIT 5
"

Results:

[
  {
    "cnt": 3480496,
    "hashtag": "No Hashtag"
  },
  {
    "cnt": 7691,
    "hashtag": "FOLLOWTRICK"
  },
  {
    "cnt": 6059,
    "hashtag": "5YearsSinceNiallsAudition"
  },
  {
    "cnt": 4392,
    "hashtag": "ShotsUpdateNextWeek"
  },
  {
    "cnt": 3729,
    "hashtag": "EXO"
  }
]

The plyql has an option --interval (-i) that automatically filters time on the last interval worth of time. It is useful if you do not want to type out a time filter.

plyql -h 10.20.30.40 -i P1D -q "
SELECT
first_hashtag as hashtag,
COUNT() as cnt
FROM twitterstream
GROUP BY first_hashtag
ORDER BY cnt DESC
LIMIT 5
"

To get a breakdown by time the TIME_BUCKET function can be used:

plyql -h 10.20.30.40 -i P1D -q "
SELECT
SUM(tweet_length) as TotalTweetLength
FROM twitterstream
GROUP BY TIME_BUCKET(__time, PT1H, 'Etc/UTC')
"

Returns:

[
  {
    "TotalTweetLength": 9748287,
    "split": {
      "start": "2015-04-18T15:00:00.000Z",
      "end": "2015-04-18T16:00:00.000Z",
      "type": "TIME_RANGE"
    }
  },
  {
    "TotalTweetLength": 15321726,
    "split": {
      "start": "2015-04-18T16:00:00.000Z",
      "end": "2015-04-18T17:00:00.000Z",
      "type": "TIME_RANGE"
    }
  },
  "... results omitted ..."
]

Note that the grouping column was not selected but was still returned as if TIME_BUCKET(__time, PT1H, 'Etc/UTC') as 'split' was one of the select clauses.

Time parting is also supported, here is an example:

plyql -h 10.20.30.40 -i P1W -q "
SELECT
TIME_PART(__time, HOUR_OF_DAY, 'Etc/UTC') as HourOfDay,
SUM(tweet_length) as TotalTweetLength
FROM twitterstream
GROUP BY TIME_PART(__time, HOUR_OF_DAY, 'Etc/UTC')
ORDER BY TotalTweetLength DESC
LIMIT 3
"

Returns:

[
  {
    "HourOfDay": 14,
    "TotalTweetLength": 115983429
  },
  {
    "HourOfDay": 13,
    "TotalTweetLength": 111398041
  },
  {
    "HourOfDay": 15,
    "TotalTweetLength": 106998577
  }
]

Here is an advanced example that gets the top 5 hashtags by time. PlyQL allows us to nest queries as aggregates like so:

plyql -h 10.20.30.40 -i P1D -q "
SELECT
first_hashtag as hashtag,
COUNT() as cnt,
(
  SELECT
  SUM(tweet_length) as TotalTweetLength
  GROUP BY TIME_BUCKET(__time, PT1H, 'Etc/UTC')
  LIMIT 3    -- only get the first 3 hours to keep this example output small
) as 'ByTime'
FROM twitterstream
GROUP BY first_hashtag
ORDER BY cnt DESC
LIMIT 5
"

Returns:

[
  {
    "hashtag": "No Hashtag",
    "cnt": 3628171,
    "ByTime": [
      {
        "TotalTweetLength": 658517,
        "split": {
          "start": "2015-04-19T06:00:00.000Z",
          "end": "2015-04-19T07:00:00.000Z",
          "type": "TIME_RANGE"
        }
      },
      {
        "TotalTweetLength": 8218030,
        "split": {
          "start": "2015-04-19T07:00:00.000Z",
          "end": "2015-04-19T08:00:00.000Z",
          "type": "TIME_RANGE"
        }
      },
      {
        "TotalTweetLength": 8480978,
        "split": {
          "start": "2015-04-19T08:00:00.000Z",
          "end": "2015-04-19T09:00:00.000Z",
          "type": "TIME_RANGE"
        }
      }
    ]
  },
  {
    "hashtag": "FOLLOWTRICK",
    "cnt": 11553,
    "ByTime": [
      {
        "TotalTweetLength": 5227,
        "split": {
          "start": "2015-04-19T06:00:00.000Z",
          "end": "2015-04-19T07:00:00.000Z",
          "type": "TIME_RANGE"
        }
      },
      "... results omitted ..."
    ]
  },
  "... results omitted ..."
]

Here is a list of features that is not currently supported that are in the works:

• Different outputs like CSV, TSV, etc.
• Query simulation - preview the queries that will be run without running them
• Expressions within aggregate functions - SUM(price + tax)
• Sub-queries in WHERE clauses
• JOIN support
• DESCRIBE queries to introspect tables
• Window functions

Please direct all questions to our user groups.