Historical data ingestion failing in Full Load mode

Last updated on May 30, 2023
Applies To Pipelines with Database Sources
Error Message Text(s) Job is failing repeatedly with error: The Full Load query mode is selected for the table, which only allows ingesting up to 5.00M records. Either create indices on the table or use another query mode. You must restart the table to ingest it again.

Potential Causes

This error may be seen during historical data ingestion if there are more than 5 Million Events in the Source object or table and no indexed column is present.

Hevo ingests the historical data, which is the data already present in the table when you create the Pipeline, using any incremental column available in the table. In the absence of such a column, Hevo looks for a timestamp column to identify the Events to be ingested. If this is also not available, it must use the Full Load query mode. This mode can fetch up to 5 Million Events in each ingestion run. It uses the index defined on the table to fetch the next set of Events. However, if no index is defined, ingestion is stuck after 5 Million records, as there is no way to know which rows to ingest next. Read Full Load for more information.

Suggested Actions

Use one of the following ways to resolve the issue.

  • Create an index on the table in your JDBC Source

    The index is a type of data structure. It is used to locate and access the data in a database table quickly. As it minimizes the number of disk accesses required when a query is processed, it helps to optimize the performance of a database.

    To create the index, connect to your database instance using your SQL Client tool, and enter the following command:

    create index <index_name> on <table_name> ( <column1>, <column2>, … );
    

    The above command is for the Oracle database. Syntax might differ for other databases.

  • Use a query mode other than Full Load for the table

    To change the query mode:

    1. Click Edit Config for the object where you are facing the error.

      Edit Config

    2. Select one of the Table query modes for the object. For example, if you have a unique incrementing column in your Source table, you can select Unique Incrementing Append Only. Or, if you have a timestamp column in the table, you can select Delta - Timestamp.

      Change query mode

    When you change the query mode:

    • The ingestion for that table restarts from the beginning. For example, if you change the query mode from Unique Incrementing Append Only to Change Data Capture, the entire data is ingested again from the beginning.

    • The re-ingested Events are considered as historical data and are not billed.


Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Nov-23-2022 NA New document.

Tell us what went wrong