Troubleshooting SQL Query Ingestion Issue in DataHub

ingestion
1

Original Slack Thread

Hi,
I am trying to do a sql-query ingestion. It says: “Pipeline finished successfully; produced 3 events in 0.55 seconds.”
But I can’t see anything in Datahub. I am using v0.13.2

json file:

{"query": "INSERT INTO customers VALUES (101, 'Jana', 'Doe', '1980-01-01', '1234 Main St, City', '123-456-78910')", "timestamp": 1689232737.669, "user": "user_b", "downstream_tables": ["datahub_demo.public.customers"], "upstream_tables": []}```
yaml:
```source:
  type: sql-queries
  config:
    platform: "postgres"
    default_db: "datahub_demo"
    query_file: "./recipes/queries.json"

sink:
  type: "datahub-rest"
  config:
    server: "<http://XXXXX:8080>"
    token: "XXXXXX"```
2

Hey there! :wave: Make sure your message includes the following information if relevant, so we can help more effectively!

  1. Are you using UI or CLI for ingestion?
  2. Which DataHub version are you using? (e.g. 0.12.0)
  3. What data source(s) are you integrating with DataHub? (e.g. BigQuery)

<@U06TM7M40RK> Can you help with the questions in this thread?

:information_source: We’re experimenting with a new AI assistant for questions in the <#CUMUWQU66> channel to help you track down helpful resources more quickly. Keep in mind that responses may not always be spot-on; feel free to ask it follow-up questions and don’t forget to provide its responses a :thumbsup: or :thumbsdown: so other folks in the Community know if you’re still looking for help on the topic!

3

To help you more effectively, could you please provide the following information?

  1. Are you using UI or CLI for ingestion?
  2. Which DataHub version are you using? (e.g., 0.12.0)
  3. What data source(s) are you integrating with DataHub? (e.g., BigQuery)

Based on the information provided and the data sources available, here are some potential troubleshooting steps:

  1. Verify Ingestion Configuration: Ensure that your ingestion configuration is correctly set up. Here is an example configuration for SQL queries ingestion:

    datahub_api:
  server: "<http://localhost:8080>"
  timeout_sec: 60
source:
  type: sql-queries
  config:
    platform: "your_platform"
    default_db: "your_database"
    query_file: "./queries.json"

  2. Check Elasticsearch Indexing: Sometimes, data might be ingested but not indexed correctly in Elasticsearch, which is used by DataHub for search and browse functionalities. Verify the health of your Elasticsearch cluster and ensure that the indices are being updated correctly.

  3. Inspect Ingestion Logs: Review the ingestion logs for any warnings or errors that might indicate why the data is not appearing in the UI. Sometimes, partial ingestion or issues with specific records can cause such problems.

  4. Ensure Correct Configuration: Double-check the configuration used for the ingestion to ensure that all necessary parameters are correctly set. Incorrect configurations can lead to successful ingestion without the data being properly displayed in the UI.

Would you like more detailed steps on any of these troubleshooting points or further assistance on a specific part of the process?

Sources:

Let me know if you need further assistance!

Sources:

4

<@U06TM7M40RK>

5

Sure, let’s dive deeper into the steps to troubleshoot your issue with SQL queries ingestion in DataHub.

Detailed Steps to Troubleshoot SQL Queries Ingestion

  1. Verify Ingestion Configuration:
    Ensure that your ingestion configuration YAML file is correctly set up. Here is a more detailed example configuration for SQL queries ingestion:

    datahub_api:
  server: "http://localhost:8080"
  timeout_sec: 60
source:
  type: sql-queries
  config:
    platform: "your_platform"  # e.g., "mssql"
    default_db: "your_database"  # e.g., "datahub_demo"
    query_file: "./queries.json"

  2. Check Elasticsearch Indexing:
    DataHub relies on Elasticsearch for search and browse functionalities. If the ingestion pipeline logs indicate that events were produced but you can’t see them in the UI, it could be an indexing issue. Verify the health of your Elasticsearch cluster and check if the indices are being updated.

  3. Inspect Ingestion Logs:
    Review the logs generated during the ingestion process. Run the ingestion command in debug mode to get more detailed logs:

    datahub ingest -c ingest_queries.yml --debug

    Look for any warnings or errors that might indicate why the data is not appearing in the UI.

  4. Ensure Correct Configuration:
    Double-check your configuration file to ensure all necessary parameters are correctly set. Here is a sample JSON file and YAML configuration for reference:

    JSON File (queries.json):

    {"query": "SELECT customerid FROM customers", "timestamp": 1689232738.051, "user": "user_a", "downstream_tables": [], "upstream_tables": ["datahub_demo.public.customers"]}
{"query": "INSERT INTO customers VALUES (101, 'Jana', 'Doe', '1980-01-01', '1234 Main St, City', '123-456-78910')", "timestamp": 1689232737.669, "user": "user_b", "downstream_tables": ["datahub_demo.public.customers"], "upstream_tables": []}

    YAML Configuration (ingest_queries.yml):

    datahub_api:
  server: "http://localhost:8080"
  timeout_sec: 60
source:
  type: sql-queries
  config:
    platform: "mssql"
    default_db: "datahub_demo"
    query_file: "./queries.json"

  5. Check DataHub UI:
    After ensuring that the ingestion pipeline runs successfully, check the DataHub UI for the ingested metadata. Sometimes, a simple refresh or clearing the browser cache can help in displaying the latest ingested data.

  6. Review Elasticsearch Logs:
    If the data is still not visible, review the Elasticsearch logs to ensure that the data is being indexed correctly. You might need to reindex the data if there are issues with the current indices.

Would you like more detailed guidance on any specific step or further assistance with a particular part of the process?

Sources:

6