8.15 MySQL connector how to sync using a sql statement

Elastic Stack Elastic Agent
1

Greetings,

I'm using the 8.15 free trial and want to retrieve data from MySQL.
I'm using the MySQL connector and in the Configuration, I add some tables under Comma-separated list of tables. On sync, all the rows of the tables are correctly indexed.
I want to index the result of a sql statement, one having multiple inner joins. You cannot add the statement under comma-separated list of tables. I tried to add it in the Sync rules, but it didn't pass the validation rules.
I then created a view from that statement in MySQL and used the view name in the Configuration/list of tables. No errors on sync but no documents have been indexed.
I then created a table from that statement in MySQL. I can see and use select in MySQL on that table name. But when I use that table name in the Configuration/List of tables, no errors on sync, but nothing has been indexed.

How can I index a sql statement using the MySQL connector?

Any help will be appreciated!
Thanks!

2

Hi Gertjan, You can use Advance sync rule to specify the sql statement on given table.

[
    {
        "tables": ["table1", "table2"],
        "query": "SELECT ... FROM ..."
    }
]
3

Hi Ashish,

Thanks for your response.
And I could get my sql statement to work on the condition that you define the same tables in Configuration.

I do have now another interesting problem.

Running my sql statement in MySQL results in 384 rows. However, Elastic only ingest 1 document (the last one).
This is a part of the sql statement:

    , min(case when (project_property_definition_id = 135) then coalesce(value_int, value_float, value_double, value_string, value_datetime) else null end) as client_reference
    from project_properties   
group by project_id;

In Documents, it shows only 1 ingested document, the last one, with this reference:
Document id: project_properties_

It looks that it overwrites the documents.
How can I ensure that each mysql row will be 1 document?

Thanks

4

To index the results of a complex SQL statement with multiple inner joins using the MySQL connector in Elastic, here are some steps and troubleshooting tips:

Steps to Index SQL Statement Results

  1. Use Views:
  • You mentioned creating a view from your SQL statement. This is typically the best approach, as views can encapsulate complex queries, including joins. Ensure that:
    • The view is properly created in MySQL and returns the expected results when queried.
    • The user account that Elastic is using to connect to MySQL has the necessary permissions to access the view.
  1. Check View Configuration:
  • Ensure that the view is defined correctly in MySQL. You can verify this by running a SELECT * FROM your_view_name command in MySQL to see if it returns the expected rows.
  • If the view works in MySQL but not in Elastic, check for any naming issues or permissions.
  1. Use Sync Rules:
  • Since you cannot directly use SQL statements in the comma-separated list of tables, ensure that you have correctly configured the sync rules for your view.
  • In the Elastic configuration, you may need to define how to identify new or changed rows. Make sure the sync rules align with the primary key or unique identifier in your view.
  1. Review Connector Logs:
  • Check the logs for the MySQL connector in Elastic to see if there are any error messages or warnings during the sync process. Logs can provide insights into why no documents are being indexed.
  • Look for any indication that the view is being queried and whether any results are returned.
  1. Test Simple Queries:
  • Before running the complex view, try using simpler queries or a simple view to ensure that the connector can successfully index data from MySQL. This will help isolate the issue.
  1. Indexing Configuration:
  • Make sure that your indexing configuration in Elastic is set up correctly. This includes ensuring the mapping and types match what is expected.
  1. Check for Data Volume:
  • If the view returns a large number of rows, it might take longer to index. Monitor the indexing status to see if it eventually processes the data.
  1. Elasticsearch Version Compatibility:
  • Verify that the version of the MySQL connector you are using is compatible with your version of Elasticsearch (8.15 in this case).