Oracle Ingestion SQL Query Truncation Breaks Lineage Parsing – DataHub Customer Support Portal

Area: Ingestion Issues

Sub-Area: Oracle Source / SQL Aggregator / Lineage Parsing

Issue

When running Oracle ingestion with DataHub CLI versions prior to 1.7.0.18, the sql_aggregator component reads query text from Oracle's V$SQL.SQL_TEXT column, which is a VARCHAR2(1000) field limited to exactly 1,000 characters. Any SQL statement longer than 1,000 characters is silently truncated by Oracle before DataHub receives it. The truncated, syntactically incomplete text is then passed to the sqlglot-based parser, which cannot parse an incomplete statement, causing lineage extraction to fail for that query. In environments with complex or verbose SQL, this can produce a parse failure rate exceeding 25% of all observed queries. Compounding the problem, these parse failures do not surface as warnings or failures in the ingestion run summary — the run reports status: SUCCESS with empty warnings and failures arrays. Additional related issues include: empty Oracle optimizer hints (/*+ */) causing parser errors; column-level lineage being attempted on DELETE statements (which is unsupported); and Oracle spatial and XML column types (SDO_GEOMETRY, XMLTYPE) being ingested with an empty field type due to missing type mappings.

Error Messages

Line 1, Col: 999 — sqlglot parse error at exactly character 999/1000, mid-token
Line 1, Col: 1000 — sqlglot parse error at the truncation boundary
Failed to parse ' ' into <class 'sqlglot.expressions.core.Hint'>
Can only generate column-level lineage for select-like inner statements, not <class 'sqlglot.expressions.dml.Delete'>
Unable to map column types to DataHub types (info-level, for SDO_GEOMETRY / XMLTYPE columns)
Error tokenizing — unterminated string literal inside truncated query text

You Might Be Asking

Why does my Oracle ingestion run show status: SUCCESS with no warnings, but lineage is missing for many tables?
Why do so many Oracle SQL parse failures happen at exactly column 999 or 1000?
Where can I see the actual number of query parse failures if the run report shows no warnings?
Why are my SDO_GEOMETRY and XMLTYPE columns ingested with an empty field type?
Why does DataHub report column-level lineage failures for DELETE statements?
Is this a DataHub bug or an Oracle limitation?

Solution

Root Cause

Oracle's V$SQL.SQL_TEXT column is defined as VARCHAR2(1000) and stores only the first 1,000 characters of any SQL statement. This is an Oracle database architecture constraint, not a DataHub configuration setting. DataHub CLI versions prior to 1.7.0.18 queried SQL_TEXT for lineage and usage extraction. The correct column to use is V$SQL.SQL_FULLTEXT, a CLOB column that holds the complete SQL text without a character limit.

Fix: Upgrade to DataHub CLI 1.7.0.18 or Later

All issues described in this article are resolved in DataHub CLI 1.7.0.18 (included in GMS releases 2.3.6 and 2.4.0 and later). Upgrading is the recommended and complete solution.

Upgrade the DataHub CLI to version 1.7.0.18 or later:
```
pip install "acryl-datahub[oracle]>=1.7.0.18"
```
Verify the installed version:
```
datahub version
```
Re-run your Oracle ingestion recipe. No recipe-level configuration changes are required. The fix is entirely within the connector.

What the Fix Changes

Full query text from Oracle: The connector now reads V$SQL.SQL_FULLTEXT (CLOB) instead of V$SQL.SQL_TEXT (VARCHAR2(1000)), using a CLOB output handler on the metadata engine. This eliminates the mid-token truncation errors ("Col: 999/1000") that caused the majority of parse failures.
Empty optimizer hints: Queries containing empty Oracle optimizer hint blocks (INSERT /*+ */ INTO ...) are now preprocessed to strip the empty hint before being passed to the parser, eliminating the Failed to parse ' ' into Hint error.
DELETE statement column lineage: The connector no longer attempts to extract column-level lineage from DELETE statements. Table-level lineage and operation metadata for DELETE statements continue to work. This removes the false inflation of num_observed_queries_column_failed.
SDO_GEOMETRY and XMLTYPE type mapping: Oracle spatial types (SDO_GEOMETRY, SDO_POINT_TYPE, SDO_ELEM_INFO_ARRAY, SDO_ORDINATE_ARRAY) are now mapped to a record type, and XMLTYPE is mapped to a string type. The "Unable to map column types to DataHub types" info messages for these columns are eliminated.
Recipe filter alignment: Query usage from Oracle now respects the recipe's schema and table allow/deny filters, so queries against excluded schemas are no longer parsed or reported.

Diagnosing Parse Failures Before Upgrading

If you are on an affected CLI version and need to quantify the problem, parse failure counts are not shown in the run summary UI. Inspect the raw ingestion report JSON directly:

Download the full run report from the DataHub UI (Ingestion → Run History → View Details → Download Report).

Search the JSON for the following key path:

source.report.sql_aggregator.observed_query_parse_failures

Also check these summary counters in the report:

num_observed_queries
num_observed_queries_failed
num_observed_queries_column_failed

Parse failures that stop at exactly column 999 or 1000 confirm the truncation root cause. For example:
```
sqlglot.errors.ParseError: Required keyword: 'end' missing for ...
  at Line 1, Col: 1000.
```

Temporary Workaround (If Upgrade Is Not Immediately Possible)

There is no recipe-level workaround that prevents individual queries from being truncated. The only partial mitigation is to reduce the number of queries ingested per run, which limits exposure but does not fix truncation for any individual long query:

source:
  type: oracle
  config:
    # ... other config ...
    usage:
      max_query_duration:
        days: 1          # Reduce lookback window to lower total query volume

Upgrading to CLI 1.7.0.18 is the only complete fix.

Additional Notes

Affected versions: DataHub CLI versions prior to 1.7.0.18 when using the Oracle source with usage/lineage extraction (include_usage_statistics: true or include_table_lineage: true).
Fix shipped in: CLI 1.7.0.18, GMS 2.3.6 and 2.4.0.
Oracle version compatibility: The V$SQL.SQL_FULLTEXT CLOB column is available in Oracle 11g and later. The fix is compatible with Oracle 11g, 12c, 18c, 19c, and 21c.
Queries over 4,000 characters: The CLOB is read using DBMS_LOB.SUBSTR() which returns up to 4,000 characters (the VARCHAR2 maximum in Oracle). SQL statements longer than 4,000 characters remain subject to truncation, but such statements are uncommon in practice.
Known remaining parser limitations (not fixed in 1.7.0.18):
- UPDATE ... PARTITION FOR (...) SET ... — this Oracle partition syntax is not yet supported by the sqlglot Oracle dialect. Queries using this form will still fail to parse for lineage.
- Inline external table syntax (SELECT ... FROM EXTERNAL (...)) is also unsupported by the parser.
Run-level warning for high parse failure rates: As of CLI 1.7.0.18, a high query parse failure ratio does not yet generate a warning in the run summary. This improvement is planned as a separate enhancement to the SqlParsingAggregator and will apply to all sources that use it. Until that ships, monitor parse failure counts by inspecting the raw run report JSON as described above.
DELETE statement column lineage failures are benign: If you see num_observed_queries_column_failed elevated due to DELETE statements on older CLI versions, this is expected behavior. Table-level lineage and operation metadata for those statements are still captured correctly.