Stored Procedures In Dremio

We are using MySQL stored procedure to execute some complex query logic. Is there a way to execute the same stored procedures using dremio?

@nageen20 Please check if External query will work

https://docs.dremio.com/data-sources/external-queries.html

@balaji.ramaswamy , thanks for the reply. I understand we can run select queries using external queries. But the stored procedure we have has multiple select queries and some delete statements also in between to get the correct data.

Please can you let me know if external queries can handle this? Or if there is any other option?

@nageen20 If you can put/add all your commands into one stored procedure then we can do it via external query

Can you please elaborate? We already have a stored procedure on MySQL DB. Can we call that using external queries just how we do in MySQL?

Is it possible to give an example of the syntax?

Thanks

Dremio @nageen20

Hi @balaji.ramaswamy ,

I am using the following code to call the stored procedure using the external query.

SELECT * FROM table(“source name”.external_query(
‘CALL db_name.stored_prpcedure_name(’‘2020’’,1,’’-1’’,
‘’-1’’,’’-1’’,’’-1’’,’’-1’’,
‘‘2021-05-05’’,’‘2021-05-05’’,’’-1’’,’’-1’’, ‘’-1’’,
NULL,NULL,
TRUE,TRUE,TRUE,TRUE,
FALSE,FALSE,TRUE,
0,TRUE,TRUE)’
))

But when I do this, I get an error saying ‘Selected table has no columns.’.

Please can you let me know where I could be going wrong?

Thanks

@nageen20

What happens if you run the below query directly on MySQL?

CALL db_name.stored_prpcedure_name(’‘2020’’,1,’’-1’’,
‘’-1’’,’’-1’’,’’-1’’,’’-1’’,
‘‘2021-05-05’’,’‘2021-05-05’’,’’-1’’,’’-1’’, ‘’-1’’,
NULL,NULL,
TRUE,TRUE,TRUE,TRUE,
FALSE,FALSE,TRUE,
0,TRUE,TRUE)

The output is returned on the MySQL client window i.e the output table is displayed on the client window. Let me know if any more is information is required.

@nageen20 Then you should be able to use it as an external query, can you please send us the profile when you got the error from Dremio?