HI Team, I'm trying to ingest metadata from Oracle...
# troubleshoot
f
HI Team, I'm trying to ingest metadata from Oracle DB, but I'm getting numerous exceptions at runtime:
Copy code
[2023-04-19 14:00:07,217] ERROR    {datahub.utilities.sqlalchemy_query_combiner:257} - Failed to execute query normally, using fallback:
SELECT hire_date
FROM hr.employees
WHERE (hire_date NOT IN (NULL) OR (1 = 1)) AND hire_date IS NOT NULL
AND ROWNUM <= 20
Traceback (most recent call last):
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/utilities/sqlalchemy_query_combiner.py", line 119, in get_query_columns
    return list(query.inner_columns)
AttributeError: 'str' object has no attribute 'inner_columns'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/utilities/sqlalchemy_query_combiner.py", line 253, in _sa_execute_fake
    handled, result = self._handle_execute(conn, query, args, kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/utilities/sqlalchemy_query_combiner.py", line 218, in _handle_execute
    if not self.is_single_row_query_method(query):
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/source/ge_data_profiler.py", line 227, in _is_single_row_query_method
    query_columns = get_query_columns(query)
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/utilities/sqlalchemy_query_combiner.py", line 121, in get_query_columns
    return list(query.columns)
AttributeError: 'str' object has no attribute 'columns'
They all refer to the same place. This is the get_query_columns function in the file
~\.local\lib\python3.10\site-packages\datahub\utilities\sqlalchemy_query_combiner.py
As I understand it, in this function the request variable is passed with the
str
type, and due to the lack of attributes
inner_columns
and
columns
for this type, the python gives an error. How can this be fixed? OS: Linux Ubuntu 22.04 Database: Oracle Database 21c Express Edition Release 21.0.0.0.0 Datahub version (quickstart): 0.10.1.1 Python: 3.10.6 My recipe:
Copy code
pipeline_name: "Oracle_HR"
source:
    type: oracle
    config:
        host_port: "localhost:1521"
        username: HR
        password: HR
        service_name: XEPDB1
        schema_pattern:
            allow:
                - HR
        profiling:
            enabled: true
        stateful_ingestion:
            enabled: true
        profile_pattern:
            allow:
            - "HR.*.*"
sink:
    type: "datahub-rest"
    config:
        server: "http://localhost:8080"
I launch the recipe through the CLI with the following command:
datahub ingest -c Oracle_HR.dhub.yaml
📖 1
🔍 1
l
This content can't be displayed.
f
Also at the beginning of ingestion I get this warning
Copy code
[2023-04-20 09:19:30,788] WARNING {py.warnings:109} - /home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/source/sql/oracle.py:114: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to "sqlalchemy<2.0". Setenvironmentvariable SQLALCHEMY_WARN_20=1 to show all deprecation warnings. Setenvironmentvariable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
I am using
sqlalchemy==1.4.47
. If I install
sqlalchemy==1.4.0
, I get additional exceptions. When installing
sqlalchemy==2.0.9
, ingestion crashes.
a
@hundreds-photographer-13496 might be able to help you here
h
Hey @flaky-dinner-67771 can you please update the recipe to disable query combiner by setting:
Copy code
profiling:
    enabled: True
    query_combiner_enabled: False
I would also request you to enable debug logs and share the same if the problem continues. From the screenshot all columns (country_id, country_name, region_id) appear non-numeric to me and it appears correct that min, max, stddev, median are not extracted.
f
Hi @hundreds-photographer-13496 Yes, the column
country_id
and
country_name
are non-numeric. But, the region_id is numeric:
@hundreds-photographer-13496, I added this line, and now my recipe looks like this:
Copy code
pipeline_name: "Oracle_HR"
source:
    type: oracle
    config:
        host_port: "localhost:1521"
        username: HR
        password: HR
        service_name: XEPDB1
        schema_pattern:
            allow:
                - HR
        profiling:
            enabled: true
            query_combiner_enabled: false
        stateful_ingestion:
            enabled: true
        table_pattern:
            deny:
                - 'HR.G[EX]_*'
sink:
    type: "datahub-rest"
    config:
        server: "http://localhost:8080"
The exceptions that I wrote about above have disappeared. However, now I get a fatal exception:
Copy code
> datahub --debug ingest -c Oracle_HR.dhub.yaml
......
[2023-04-21 11:12:26,791] INFO     {datahub.cli.ingest_cli:137} - Sink (datahub-rest) report:
{'total_records_written': 41,
 'records_written_per_second': 5,
 'warnings': [],
 'failures': [],
 'start_time': '2023-04-21 11:12:19.143039 (7.65 seconds ago)',
 'current_time': '2023-04-21 11:12:26.790475 (now)',
 'total_duration_in_seconds': 7.65,
 'gms_version': 'v0.10.2',
 'pending_requests': 0}
[2023-04-21 11:12:26,792] DEBUG    {datahub.telemetry.telemetry:248} - Sending telemetry for function-call
[2023-04-21 11:12:27,073] ERROR    {datahub.entrypoints:195} - Command failed:
Traceback (most recent call last):
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/entrypoints.py", line 182, in main
    sys.exit(datahub(standalone_mode=False, **kwargs))
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/telemetry/telemetry.py", line 379, in wrapper
    raise e
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/telemetry/telemetry.py", line 334, in wrapper
    res = func(*args, **kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/utilities/memory_leak_detector.py", line 95, in wrapper
    return func(ctx, *args, **kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/cli/ingest_cli.py", line 198, in run
    loop.run_until_complete(run_func_check_upgrade(pipeline))
  File "/usr/lib/python3.10/asyncio/base_events.py", line 646, in run_until_complete
    return future.result()
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/cli/ingest_cli.py", line 158, in run_func_check_upgrade
    ret = await the_one_future
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/cli/ingest_cli.py", line 149, in run_pipeline_async
    return await loop.run_in_executor(
  File "/usr/lib/python3.10/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/cli/ingest_cli.py", line 140, in run_pipeline_to_completion
    raise e
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/cli/ingest_cli.py", line 132, in run_pipeline_to_completion
    pipeline.run()
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/run/pipeline.py", line 359, in run
    for wu in itertools.islice(
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/utilities/source_helpers.py", line 104, in auto_stale_entity_removal
    yield from stale_entity_removal_handler.gen_removed_entity_workunits()
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/source/state/stale_entity_removal_handler.py", line 267, in gen_removed_entity_workunits
    last_checkpoint: Optional[Checkpoint] = self.source.get_last_checkpoint(
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/source/state/stateful_ingestion_base.py", line 320, in get_last_checkpoint
    self.last_checkpoints[job_id] = self._get_last_checkpoint(
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/source/state/stateful_ingestion_base.py", line 295, in _get_last_checkpoint
    self.ingestion_checkpointing_state_provider.get_latest_checkpoint(
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/source/state_provider/datahub_ingestion_checkpointing_provider.py", line 76, in get_latest_checkpoint
    ] = self.graph.get_latest_timeseries_value(
  File "/home/vladimir/.local/lib/python3.10/site-packages/datahub/ingestion/graph/client.py", line 299, in get_latest_timeseries_value
    assert len(values) == 1
AssertionError
[2023-04-21 11:12:27,075] DEBUG    {datahub.entrypoints:197} - DataHub CLI version: 0.10.2 at /home/vladimir/.local/lib/python3.10/site-packages/datahub/__init__.py
[2023-04-21 11:12:27,075] DEBUG    {datahub.entrypoints:200} - Python version: 3.10.6 (main, Mar 10 2023, 10:55:28) [GCC 11.3.0] at /usr/bin/python3 on Linux-5.15.90.1-microsoft-standard-WSL2-x86_64-with-glibc2.35
[2023-04-21 11:12:27,075] DEBUG    {datahub.entrypoints:205} - GMS config {'models': {}, 'patchCapable': True, 'versions': {'linkedin/datahub': {'version': 'v0.10.2', 'commit': '6802142f6e76e7a5449f7446b1fbbefbdc26b9fb'}}, 'managedIngestion': {'defaultCliVersion': '0.10.2', 'enabled': True}, 'statefulIngestionCapable': True, 'supportsImpactAnalysis': True, 'timeZone': 'GMT', 'telemetry': {'enabledCli': True, 'enabledIngestion': False}, 'datasetUrnNameCasing': False, 'retention': 'true', 'datahub': {'serverType': 'quickstart'}, 'noCode': 'true'}
I reinstalled sqlalchemy back to version 1.4.47 but that didn't help. I did not see any errors in the logs of the gms container - only info messages.
I looked through the Debug messages and see that datahub just doesn't collect statistics for the numeric field (
region_id
):
Copy code
[2023-04-21 11:36:20,307] DEBUG    {datahub.emitter.rest_emitter:250} - Attempting to emit to DataHub GMS; using curl equivalent to:
curl -X POST -H 'User-Agent: python-requests/2.28.2' -H 'Accept-Encoding: gzip, deflate' -H 'Accept: */*' -H 'Connection: keep-alive' -H 'X-RestLi-Protocol-Version: 2.0.0' -H 'Content-Type: application/json' --data '{"proposal": {"entityType": "dataset", "entityUrn": "urn:li:dataset:(urn:li:dataPlatform:oracle,hr.countries,PROD)", "changeType": "UPSERT", "aspectName": "datasetProfile", "aspect": {"value": "{\"timestampMillis\": 1682066179765, \"partitionSpec\": {\"type\": \"FULL_TABLE\", \"partition\": \"FULL_TABLE_SNAPSHOT\"}, \"rowCount\": 25, \"columnCount\": 3, \"fieldProfiles\": [{\"fieldPath\": \"country_id\", \"uniqueCount\": 25, \"uniqueProportion\": 1, \"nullCount\": 0, \"nullProportion\": 0.0, \"sampleValues\": [\"AR\", \"AU\", \"BE\", \"BR\", \"CA\", \"CH\", \"CN\", \"DE\", \"DK\", \"EG\", \"FR\", \"GB\", \"IL\", \"IN\", \"IT\", \"JP\", \"KW\", \"ML\", \"MX\", \"NG\"]}, {\"fieldPath\": \"country_name\", \"uniqueCount\": 25, \"uniqueProportion\": 1, \"nullCount\": 0, \"nullProportion\": 0.0, \"sampleValues\": [\"Argentina\", \"Australia\", \"Belgium\", \"Brazil\", \"Canada\", \"Switzerland\", \"China\", \"Germany\", \"Denmark\", \"Egypt\", \"France\", \"United Kingdom of Great Britain and Northern Ireland\", \"Israel\", \"India\", \"Italy\", \"Japan\", \"Kuwait\", \"Malaysia\", \"Mexico\", \"Nigeria\"]}, {\"fieldPath\": \"region_id\", \"uniqueCount\": 5, \"uniqueProportion\": 0.2, \"nullCount\": 0, \"nullProportion\": 0.0, \"sampleValues\": [\"20\", \"40\", \"10\", \"20\", \"10\", \"10\", \"30\", \"10\", \"10\", \"50\", \"10\", \"10\", \"30\", \"30\", \"10\", \"30\", \"30\", \"50\", \"20\", \"50\"]}]}", "contentType": "application/json"}, "systemMetadata": {"lastObserved": 1682066180297, "runId": "oracle-2023_04_21-11_36_15"}}}' 'http://localhost:8080/aspects?action=ingestProposal'
At the same time, the region_id in the database has the numeric type. Also, there are numerous warnings throughout the output:
Copy code
[2023-04-21 11:36:20,313] WARNING  {great_expectations.dataset.sqlalchemy_dataset:2131} - No recognized sqlalchemy types in type_list for current dialect.
h
In that case, there appears to be some great-expectations library limitation coming into picture. DataHub uses GE for profiling. Here is the piece of code that checks for data type and decides whether to profile the column for min, max, etc What is the oracle native type for column region_id ?
f
Number.
a
This appears to be an issue indeed. Can you please confirm if min, max, etc succeed if the column type is NUMERIC or FLOAT ?
f
I don't have this type in profiled tables. I will now create a table with new data with these types and try to check whether profiling will be done.
@hundreds-photographer-13496 I created a table with 3 fields:
int
,
numeric
and
float
.
Copy code
CREATE TABLE HR.test (
	int_col int,
	num_col numeric(19, 12),
	float_col float
);
Oracle converted them into the
number
and
float
fields. As I understand it, there are no
numeric
and
int
types in oracle. They are converted to
number
. The
float
type remained. Only for the
float
type field the datahub profiling is done.
a
That makes sense. Can you please create github issue for this ?
f
Sure. I created github issue the following link: https://github.com/datahub-project/datahub/issues/7879
Hi, @hundreds-photographer-13496. I apologize for distracting you from your work. Can you tell if this problem will be solved and if there are any specific deadlines?
h
Hey @flaky-dinner-67771 unfortunately this not planned yet. I'll let you know if there is any update.