Drill Limitations

Issue

Drill cannot run a query that exceeds the ZooKeeper jute.maxbuffer value of 1 MB.

Solution

For the ZooKeeper jute.maxbuffer property, follow the recommendations in the ZooKeeper documentation.

CAUTION The jute.maxbuffer property is marked as an unsafe option. Do not change it to a higher value to run larger queries. For additional details about why this property is unsafe, see the Apache Zookeeper documentation: https://zookeeper.apache.org/doc/r3.6.2/zookeeperAdmin.html

The following error indicates that the query is too large. To resolve this error, rewrite the query to fit within the jute.maxbuffer limit:

Query execution error. Details: EXECUTION_ERROR ERROR: Failed to persist query info. Query length is too big.

In Drill EEP version 8.1.0 and earlier and EEP version 9.1.1 and earlier, the following type of error may also indicate that the query is too large:

Query execution error. Details: SYSTEM ERROR: ConnectionLossException: KeeperErrorCode = ConnectionLoss for /drill/running/1bb44a40-c715-7b38-c310-05de39dfb3e7

In Drill EEP version 8.1.1, this error could indicate that the jute.maxbuffer value set for the Drillbit does not correspond with the ZooKeeper jute.maxbuffer value.

To change the value of jute.maxbuffer in Drill, add the -Djute.maxbuffer Java property to DRILL_JAVA_OPTS in <drill_home>/conf/drill-env.sh, as shown in the following example:

export DRILL_JAVA_OPTS="$DRILL_JAVA_OPTS -Djute.maxbuffer=900000

If you change the jute.maxbuffer value, you must update the system property on all servers (ZooKeeper nodes) and clients (Drillbit nodes) to have the same value. Failure to change the value on all servers and clients can result in further errors.

• The SELECT list in a scalar subquery can only contain one item/column.
• Correlated subqueries should return exactly one row.
• The WHERE clause of a subquery should not refer to more than one column of the table in the outer query.

For example, the following JSON files could cause Drill to return an OutOfMemoryException when queries that include the key-value pairs run against the files:

JSON file with objects that have many key-value pairs

{
    "context" : {
        "1": "a",
        "2": "b",
        "3": "c",
        ... // many key-value pairs; not showing 9996 of them
        "10000": "d"
    }
}
{
    "context" : {
        "10001": "b"
        ... //many key-value pairs; not showing 19998 of them
        "30000": "z"
    }
}
{
    "context" : {
        "3": "c"
    }
}
{
    "context" : {

    }
}
{
    "context" : {
        "5": "e"
    }
}

JSON file with thousands of objects, each having a unique key

{
    "context" : {
        "1": "a"
    }
}
{
    "context" : {
        "2": "b"
    }
}
{
    "context" : {
        "3": "c"
    }
}
{
    "context" : {
        "4": "d"
    }
}
{
    "context" : {
        "5": "e"
    }
}

In this scenario, if a JSON file contains thousands of objects with unique keys (in 30,000+ documents), the following query would cause an OutOfMemoryException:

SELECT context FROM maprfs.`folderWithJSONDocuments`;

To resolve or prevent this issue, change the format of the key-value pairs in the JSON file from an object to an array of objects, as shown in the following example:

{
  "context" : [
    {
      "key": "1",
      "value": "a"
    },
    {
      "key": "2",
      "value": "b"
    },
    {
      "key": "3",
      "value": "c"
    },
    ...,
    {
      "key": "100000",
      "value": "z"
    }
  ]
}

By default, unequal joins are disabled in Drill because Drill does not have optimized JOIN operators to process these types of queries. An unequal join contains a condition with an inequality comparison between columns. An unequal join uses operators, such as <>, <, >, <=, or >= to establish the relationship between the joined columns. For example:

SELECT *
  FROM table1
  JOIN table2 
  ON table1.column_name <= table2.column_name;

By default, if you run a query with an unequal join, the query fails and Drill returns the following error:

Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join.
If a cartesian or inequality join is used intentionally, set the option 'planner.enable_nljoin_for_scalar_only' to false and try again.

Although Drill does not have optimized JOIN operators, Drill can process these types of queries in a non-efficient way using a nested loop join operator. To enable this, set the planner.enable_nljoin_for_scalar_only option to false. Drill can then execute LEFT and INNER joins; however, RIGHT joins will still fail with the error message shown.

Using the following query as an example:

SELECT *
  FROM table1
  LEFT JOIN table2 
  ON table1.column_name <= table2.column_name;

If table1 is smaller than table2 or even empty, Drill applies a join optimization on the query and the query fails with the following error:

Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join.
If a cartesian or inequality join is used intentionally, set the option 'planner.enable_nljoin_for_scalar_only' to false and try again.

The same optimization can make a RIGHT join executable when the left input is smaller than the right. In this case, the left and right inputs will be flipped and the RIGHT join type will be changed to LEFT. The nested loop operator supports LEFT joins. This is how Drill executes a query with a RIGHT join and no errors.

To avoid non-equi join errors, disable the join optimization by setting the planner.enable_join_optimization option to false.