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

5

Hi,
Thanks for the tips, it helped to find the problem.
My observations:

  1. using a complicated sql statement in Advanced Sync Rules, will not work. (0 docus)
  2. creating a view in MySQL and use the view in the configuration will not index any doc.
  3. create a table, based on the complicated sql statement, then Elastic can index all the rows.

BUT only if the new table has a PK! Without the PK it will not index any doc!