I have installed Dremio version 24.3.2. While attempting to connect to my SingleStore DB using the SingleStore Data source, I am encountering a “500 - Request failed” error. However, I am able to connect using the same credentials with MySQL as the Data Source.After downgrading to Dremio version 23.1.0, I successfully connected to my SingleStore database using the SingleStore DataSource. I executed the following two queries:
Query 1
SELECT * FROM “Customer 360 SingleStore”.“Voice_Seg_Fct”
WHERE Event_Date >= TO_DATE(DATE_ADD(DATE_SUB(LAST_DAY(now()), CAST(2 AS INTERVAL MONTH)), 1))
AND Event_Date <= LAST_DAY(DATE_SUB(now(), CAST(1 AS INTERVAL MONTH)));
Query 2
SELECT * FROM “Customer 360 SingleStore”.“Voice_Seg_Fct”
WHERE Event_Date >= ‘2024-05-01’ AND Event_Date <= ‘2024-05-31’;
When I checked the query plans for these two queries, I noticed that both queries were not drilling down in the Jdbc level. Even to the query where data condition is hardcoded.Instead, a separate filter step was executed within Dremio, negatively impacting performance.
I have a query regarding the SingleStore driver. I noticed that the only available driver is dremio-singlestore-plugin-23.1.0-1.0.0.jar for version 23.x. I used the same plugin in Dremio 24.x but encountered a “500 - Request failed” error when connecting to the SingleStore database, whereas it works in version 23.x. Could this issue be due to plugin compatibility with Dremio 24.x?
After rebuilding the SingleStore driver for Dremio 24.x, I managed to establish a connection to the SingleStore database using the SingleStore data source. However, I encountered an issue with the query optimization for the following two queries:
Query 1
SELECT * FROM “Customer 360 SingleStore”.“Voice_Seg_Fct”
WHERE Event_Date >= TO_DATE(DATE_ADD(DATE_SUB(LAST_DAY(now()), CAST(2 AS INTERVAL MONTH)), 1))
AND Event_Date <= LAST_DAY(DATE_SUB(now(), CAST(1 AS INTERVAL MONTH)));
Query 2
SELECT * FROM “Customer 360 SingleStore”.“Voice_Seg_Fct”
WHERE Event_Date >= ‘2024-05-01’ AND Event_Date <= ‘2024-05-31’;
Upon reviewing the query plans generated by Dremio, I observed that both queries did not drill down to the JDBC level, even when the date conditions were hardcoded. Instead, a separate filter step was executed within Dremio, impacting performance negatively.
Great to hear that you have built the Singlestore ARP connector for 24.x, and you found it to work.
Next is to identify and eliminate potential bugs in the connector. You should ideally reach out to the maintainers of the connector. But because you have access to the code, you can fork your own, fix it and do a PR.
It is relatively easy to build and maintain an ARP connector. There are tutorials here to familiarize yourself. You also have a wealth of examples of other ARP connectors to refer to (BigQuery, Vertica, Snowflake etc.)
An example of a potential bug that may be related to your case, the Singlestore ARP yaml does not have DATE data type specified for the >= or <= operators. Here’s the section of the code I’m referring to.
I added the following configuration to the SingleStore ARP connector YAML file and rebuilt the Dremio SingleStore plugin JAR. However, with the newly built JAR, queries using the < and > symbols are not working. Additionally, I checked the previous JAR build, and queries with < and > symbols on integer and string columns are also not being pushed down correctly.
args:
“date”
“date”
return: “boolean”
I reviewed the Dremio documentation on creating an ARP connector to develop a custom connector. However, I am still unsure how this custom connector will resolve the query pushdown issue in the SingleStore plugin.
As mentioned, I had highlighted just one issue. There mostly are others. Please compare other yamls of other ARP connectors (Community Snowflake, Vertica, BigQuery)
If you’re comparing DATE with VARCHAR (i.e. Event_Date >= ‘2024-05-01’), you’re definitely going to need more than just what I gave above because that just compares DATE with DATE.
I’ve added the above signature for the operators in the singlestore-arp.yaml file and built the Dremio SingleStore plugin. However, queries using the < and > symbols are still not working.