The Presto query engine has some known limitations.

Column Name Escaping

When a column name matches the name of a reserved keyword, the name of the column needs to be quoted. While in Hive, as most of the SQL-based query languages, the quotation character is the back tick character `. In Presto, quoting of a column name is accomplished with the double-quote character instead . See the following comparison:

SELECT `join` FROM mytbl      # Hive
SELECT "join" FROM mytbl      # Presto

Quoting a column name in single quotes will make the query parser interpret the content as a simple string that might produce an unexpected result. For example:

SELECT "join" FROM mytbl LIMIT 10

result:
value1
value2
value3
value4
...
value10

SELECT 'join' FROM mytbl

result:
join
join
join
join
...
join


DELETE Statement

The Presto DELETE statement has several known limitations.

No DELETE Without a WHERE Clause

To reduce the occurrence of unintentional DELETE commands, DELETE statements require you to include a WHERE clause.

Error sample:

Does not support DELETE statement without WHERE condition

No DELETE for Recently Collected Streaming Data

Data that is recently collected (typically last 2-4 hours) through streaming ingestion method can not be deleted using the DELETE statement because streaming data is temporarily stored in the TD “real-time” data storage.

If you try to delete data in real-time storage, the query will fail to run.

To work around this issue, you can choose one of the following:

  • Select a range of data that does not include data within real-time storage.

  • Wait for data in real-time storage to be propagated to archive storage, then delete it. This typically occurs within 2-6 hours after arrival, though it can be longer.

  • You can add the following magic comment within the SQL statement, to force the query to run.

    • -- set session force_delete = 'true'

    The query will NOT DELETE data in real-time storage, but the query is considered to have run successfully.
    To ensure the deletion of records in a table, including those in real-time storage, use the following steps:

  1. Use DELETE with the magic comment to delete the records in archive storage.

  2. Use a SELECT(COUNT(1)) ... LIMIT 1 with the same predicates as the DELETE to determine whether there are any matching records remaining. These will be in real-time storage, or they might have been propagated to archive storage after the initial DELETE statement.

  3. Repeat the preceding steps periodically, until SELECT(COUNT(1))... LIMIT 1 returns a zero result.

Depending on propagation time from real-time storage to archive storage, it may take a while before all matching records are deleted.

For example

Delete your records:

-- set session force_delete = 'true'
DELETE FROM lineitem where ship='AIR';

Then check whether there are remaining records in the table not covered by the delete:

SELECT COUNT(1)  as REMAINING_ROWS FROM lineitem where ship='AIR' LIMIT 1;

After this SELECT statement returns 0 for REMAINING_ROWS, you know there are no rows left in realtime storage or archive storage.

DELETE against Data Tank

DELETE statements cannot be issued against data stored in Data Tank. Connect directly to the PostgreSQL Data Tank to issue DELETE statements on data stored there.

Multiple DELETE jobs on the same table are NOT allowed when there is overwrap on a partition file

Multiple DELETE jobs on the same table are allowed only when there’s no overwrap on a partition file. If the table is partitioned by 1-hour, it can be deleted with different time-range. But there could be large time range data files so it’s not always guaranteed to be executed. More precisely, multiple jobs cannot remove the same partition file at the same time.

Also when a DELETE job is running and internal optimization process for partitioning merges (deletes small files and creates large ones) the data which DELETE involves, an error occurs: Metadata transaction conflicted with others.

In this case, retry the DELETE job manually.

Undoing Delete Statements

If you want to rollback an executed delete statement and recover deleted data, you must contact Treasure Data technical support and provide the jobID of the job that contained the executed Presto DELETE statement. In many cases, you can undo a delete statement. However, certain situations prevent the rollback of Presto DELETE statements.

Situations that prevent rollback include:

  • When new partitions have been deleted by another DELETE statement.

  • When original partitions have been discarded, which can happen after a retention period.

  • When a table is dropped and a new table with the same name is created.

  • When new partitions have been further modified or replaced, which can happen as a result of internal storage maintenance processes.

DELETE statement Timeout Error

Depending on the number of partitions that contain rows to be deleted, a DELETE operation can take several hours. An operation can time out and the job fail if too many partitions are affected. If the job fails and the output contains the following message:

org.plazmadb.metadb.MetadataSQLException: 
ERROR: canceling statement due to statement timeout

Then you should rewrite the DELETE statement to affect fewer rows or fewer partitions. The most effective way to do this is to include a WHERE clause that limits the time range for the DELETE with TD_TIME_RANGE or TD_INTERVAL. For example, if you have a year's worth of game history data, instead of

DELETE FROM game_history WHERE player_id=1000;

try

DELETE FROM game_history 
  WHERE player_id=1000 
  AND TD_TIME_RANGE(time, '2018-01-01', '2018-02-01','PDT')

and then delete more time ranges until all rows are deleted. TD support can provide a workflow that simplifies automating this process.

Presto DELETE Statement Resource Consumption

DELETE queries issue jobs and consume resources. Because of the storage and indexing methods used in Treasure Data, a DELETE query can be resource intensive. 

To prevent a DELETE query from performing a full table scan, you should use:

  • a time expression, like TD_TIME_RANGE or TD_INTERVAL, if the table uses default time-based partitioning

  • an equality predicate on all partition keys, if the table uses user-defined partitioning

For example, if your table is partitioned by time, and you are trying to delete a set of users that were created in June 2018, include the time restriction even if that’s not a critical parameter for your DELETE statement.

For example, you should:

DELETEFROM logtable WHERE userid IN (1234, 1235, 1236) AND TD_TIME_RANGE(time, '2017-06-01','2017-07-01')

Including the time restriction significantly improves the speed of your query, and reduces the resources this query consumes.

6-Hour Limit for Presto Queries

Presto queries that run more than 6 hours are automatically canceled. Typically, Presto queries that run more than a day or so are failing queries.

For long-running queries, you can rewrite the Presto query as a Hive query.

Table Schemas are Required

When querying using Presto, you must set a Schema for the tables; the v syntax doesn’t work.

JOIN Order

Presto does not support cost-based JOIN optimizations, meaning JOINs are not automatically reordered based on table size. Make sure that smaller tables are on the right-hand side of JOIN, and they must fit in memory. Otherwise out of memory exceptions will cause the query to fail.

SELECT
  ...
FROM
  large_table
JOIN
  small_table


INSERT OVERWRITE Statements are NOT Supported

Presto does not support INSERT OVERWRITE Statements. Make sure that you delete the table before using INSERT INTO.

Error Message – Killed by the system because this query generates output for more than 100GB.

This error is caused by trying to generate more than 100GB output by a single query.

As a workaround, use result_output_redirect option or CREATE TABLE AS.

Error Message - Output size too large. Exceeds maximum file size: 25GB

This message might appear if result_output_redirect is enabled by default or used in the magic comment.

Error Message – Accessing too many(N) tables. Maximum 300 tables allowed

This error is caused by trying to scan more than 300 tables by a single query.

As a workaround, tune the SQL as reducing scan tables, or divide single query to multiple jobs.

Error Message – Creating partitions(N) exceeds the limit(500000) 

This error is caused by trying to commit more than 500,000 (500K) partitions by a single query.

As a workaround, use User Defined Partitioning (UDP) or tune parameters for UDP.

  • Use User-Defined Partitioning

  • Tune parameters for UDP.  For example:

    • Reduce bucket_count to 256. (512 in default)

    • Update max_time_range to adopt a more broad time range. 

    • Increase max_file_size to 512MB. (256MB in default)

Error Message – Remote page is too large


This error is caused by hitting the limits of the intermediate buffers in Presto. The cause could be the size of the row or column being too high. The limit is set to 64MB in TD Presto.

As a workaround, reduce the size of a single row:

  • Reduce the number of columns to be scanned.

  • Reduce the size of a large column using SMART_DIGEST.
    SMART_DIGEST()

  • Use the Hive engine instead of Presto.

  • No labels