Canonical Hive Commands Report

GET /api/v1.2/reports/canonical/_hive/_commands/

This API provides you the canonical Hive commands report in JSON format. Currently, this report is not generated and Qubole intends to provide this report very soon.

Note

If the difference between the start and end dates is greater than 60 days, the system defaults to a 1-month window from the current date.
If either the start date or end date is not provided, then the system defaults to a 1-month window from the current date.
To get data for a window that is greater than 60 days, create a ticket with Qubole Support.

Required Role

The following users can make this API call:

Users who belong to the system-user or system-admin group.
Users who belong to a group associated with a role that allows viewing canonical Hive commands reports. See Managing Groups and Managing Roles for more information.

Parameters

Parameter	Description
start_date	The date from which you want the report (inclusive). This parameter supports the timestamp in the UTC timezone (YYYY-MM-DDTHH:MM:SSZ) format. The date cannot be earlier than 90 days.
end_date	The date until which you want the report. The report contains data from this date also. The API default is today or now. This parameter also supports timestamp in the UTC timezone (YYYY-MM-DDTHH:MM:SSZ) format.
offset	The starting point of the results. The API default is 0.
limit	The number of results to fetch. The API default is 10.
sort_column	The column used to sort the report. Since this report returns the top canonical_hive_commands, the sort order is always descending. Valid choices are ‘frequency’, ‘cpu’, ‘fs_bytes_read’, and ‘fs_bytes_written’. The API default is, frequency.
show_ast	Also return the serialized AST corresponding to the canonical query. (Not returned by default.)

Response Parameters

Parameter	Description
start_date	The actual starting date of the report.
end_date	The actual ending date of the report.
sort_column	The sort column used.

An array of:

canonical_query_id	The ID of the canonical query.
canonical_query	The AST dump of the canonical query. (This is returned only when the show_ast parameter is passed.)
recent_example	The most recent example of this type of queries.
frequency	The number of queries of this type.
cpu	The total cumulative CPU, (in ms), consumed by these queries.
fs_bytes_read	The total bytes read by these queries.
fs_bytes_written	The total bytes written by these queries.

Examples

Without any parameters

curl -i -X GET -H "X-AUTH-TOKEN: $AUTH_TOKEN" -H "Accept: application/json" \
"https://api.qubole.com/api/v1.2/reports/canonical_hive_commands"

Note

The above syntax uses https://api.qubole.com as the endpoint. Qubole provides other endpoints to access QDS that are described in Supported Qubole Endpoints on Different Cloud Providers.

Sample Response

{
  "sort_column": "frequency",
  "canonical_queries": [
    {
      "canonical_query_id": "af09cd5799e52f450a87e236f453b864833afac97603409a17f3df4d010b1814",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 8800,
      "cpu": 0,
      "recent_example": "alter table demo_memetracker recover partitions"
    },
    {
      "canonical_query_id": "9548ac7ec7defe3c3251da2544ec545c9bd578cb308c6c3c1936e48df0bdfdb4",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 4547,
      "cpu": 0,
      "recent_example": "alter table daily_tick_data recover partitions"
    },
    {
      "canonical_query_id": "69dae07fc876927b9daba6279c962bc343131c08d8f9f98adfa0c05ef90b40a4",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 768,
      "cpu": 0,
      "recent_example": "show tables"
    },
    {
      "canonical_query_id": "89720fe23d2a85ac217a3b230e992c45dd523b65e6d45863cc410f4b5e4795ea",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 53,
      "cpu": 0,
      "recent_example": "select * from `default`.`memetracker` limit 400"
    },
    {
      "canonical_query_id": "04bccd848172842c8fadd687aef72ac2161f72895dfd3c1d3c31a96411d34095",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 49,
      "cpu": 0,
      "recent_example": "select * from `default`.`30days_test` limit 1 "
    },
    {
      "canonical_query_id": "9996d665cf077f60b1ee87d3b5b80cd65ce078f77935096441433628909b9ddb",
      "fs_bytes_written": 9,
      "fs_bytes_read": 28482500000,
      "frequency": 48,
      "cpu": 0,
      "recent_example": "select count(*) from memetracker"
    },
    {
      "canonical_query_id": "492ea35ff3d58d0d07e70bcc68ea33eadb7bb572f6fdf14f7220931cc94b1abc",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 37,
      "cpu": 0,
      "recent_example": "select * from `default`.`default_qubole_airline_origin_destination` limit 1000"
    },
    {
      "canonical_query_id": "5e2bb3326c4cf2c5b669d60729c5697fb6fdef4f1e09be41e37f424eb96b0c74",
      "fs_bytes_written": 0,
      "fs_bytes_read": 0,
      "frequency": 30,
      "cpu": 0,
      "recent_example": "select * from default_qubole_memetracker limit 10;"
    },
    {
      "canonical_query_id": "aa1c7a294f6e18feec68175a643814f06e180f4ac5e62eb6b556d9bf72830bc2",
      "fs_bytes_written": 25326,
      "fs_bytes_read": 85944,
      "frequency": 22,
      "cpu": 0,
      "recent_example": "select * from test_csv limit 5;"
    },
    {
      "canonical_query_id": "2145af0ee70e1cd93c9901cd41dee8285faacaefe86e4a4f22880316cc4e63c3",
      "fs_bytes_written": 21050200,
      "fs_bytes_read": 321138000,
      "frequency": 21,
      "cpu": 0,
      "recent_example": "select * from demo_memetracker limit 100"
    }
  ]
}

With a different sort column and limit and show_ast=true

curl -i -X GET -H "X-AUTH-TOKEN: $AUTH_TOKEN" -H "Accept: application/json" "https://api.qubole.com/api/v1.2/reports/canonical_hive_commands?sort_column=cpu&limit=2&show_ast=true"

Note

The above syntax uses https://api.qubole.com as the endpoint. Qubole provides other endpoints to access QDS that are described in Supported Qubole Endpoints on Different Cloud Providers.

Sample Response

{
 "sort_column": "cpu",
 "canonical_queries": [
    {
      "canonical_query_id": "d9635e3ad5501c9ad47bb728c35b63e1b41f8c2ba0fb4f7533b9ab701ce503c4",
      "canonical_query": "(null(TOK_QUERY(TOK_FROM(TOK_TABREF(TOK_TABNAME(lineitem))))(TOK_INSERT(TOK_DESTINATION(TOK_DIR(TOK_TMP_FILE)))(TOK_SELECT(TOK_SELEXPR(TOK_FUNCTIONSTAR(count))))))())",
      "fs_bytes_written": 18,
      "fs_bytes_read": 7726360000,
      "frequency": 2,
      "cpu": 423130,
      "recent_example": "set fs.s3.inputpathprocessor=false;\nselect count(*) from lineitem;"
    },
    {
      "canonical_query_id": "ea1a37cf6b4694293d15cadfaf4bbae2459f12475cd86ea90e6c4f8e31945bda",
      "canonical_query": "(null(TOK_QUERY(TOK_FROM(TOK_TABREF(TOK_TABNAME(default_qubole_memetracker))))(TOK_INSERT(TOK_DESTINATION(TOK_DIR(TOK_TMP_FILE)))(TOK_SELECT(TOK_SELEXPR(TOK_FUNCTIONSTAR(count))))(TOK_WHERE(=(TOK_TABLE_OR_COL(month))(LITERAL)))))())",
      "fs_bytes_written": 108,
      "fs_bytes_read": 54673600000,
      "frequency": 20,
      "cpu": 416200,
      "recent_example": "SELECT count(*) FROM default_qubole_memetracker where month = '2008-08';"
    }
 ]
}

For a specific time period

curl -i -X GET -H "X-AUTH-TOKEN: $AUTH_TOKEN" -H "Accept: application/json" "https://api.qubole.com/api/v1.2/reports/canonical_hive_commands?start_date=2014-04-01&end_date=2014-04-21&sort_column=fs_bytes_read&limit=2"

Note

The above syntax uses https://api.qubole.com as the endpoint. Qubole provides other endpoints to access QDS that are described in Supported Qubole Endpoints on Different Cloud Providers.

Sample Response

{
  "canonical_queries": [
     {
       "canonical_query_id": "55ebb0cc47e0dc74c70245b026126bba191969dee1dc380a6f98698e6b194085",
       "cpu": 75720,
       "frequency": 1,
       "recent_example": "select dt, count(*) from  junk_temp \ngroup by dt order by dt\n\n\n",
       "fs_bytes_read": 308582016,
       "fs_bytes_written": 1514
     },
     {
       "canonical_query_id": "1c421d2e65407650650cbc2ee80f9a59863875f52d1a9ddd5a051118678a3a6c",
       "cpu": 34980,
       "frequency": 1,
       "recent_example": "select created_at from  junk_temp \nwhere dt=2014-01-03 limit 10\n\n",
       "fs_bytes_read": 308582016,
       "fs_bytes_written": 0
     }
  ],
   "start_date": "2014-03-31T10:00:00Z",
   "end_date": "2014-04-21T20:00:00Z",
   "sort_column": "fs_bytes_read"
}

To learn more about canonicalization of hive queries, see this blog post.

Caution

Qubole started collecting the CPU metrics only from the last week of December, 2013. So, if you have some queries before that, the CPU metrics is considered as 0.