We want to utilize CREATE [OR REPLACE] EXTERNAL FUNCTION to call a REST API

Help Connectors and Pipelines
1

Hi Team,
We would like to invoke an external service from Singlestore StoredProcedure and exploring External Function feature but in docs it says -
“CREATE [OR REPLACE] EXTERNAL FUNCTION` is currently a private preview feature and must be enabled via a Support Request for SingleStoreDB Cloud users.”

image
image1505×783 89.3 KB

Can you please suggest if it is possible for us to use this feature and how we can enable it. Currently we are using on - premise database.

When we tried this we got error like “ERROR 2576 UNKNOWN_ERR_CODE: External function runtime failure: Couldn’t connect to server”

Thanks.

2

Hi Team , any response please

3

Hi prakash! Welcome! :wave:

Can you please supply the version number you are running on?

Thanks

4

I see below when I executing like below

cmd>docker exec -it singlestore-ciab memsql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 504
Server version: 5.7.32 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)

Also in other environment we have - DB version is : 7.5.18

5

Hi Team , please respond

6

Hi Maria , please respond. A faster reply would be really apriciated.

7

I see below when I executing like below

cmd>docker exec -it singlestore-ciab memsql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 504
Server version: 5.7.32 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)

Also in other environment we have - DB version is : 7.5.18

Currently I m trying in 5.7.32

8

Hi Maria please respond

9

Hi Prakash! Thanks for your patience. I was able to get some advice from the support team and have included it below.

Because it looks like you are looking at the SingleStoreDB Cloud docs, while running SingleStoreDB On-Premises - the team has suggested this link to our SingleStoreDB documentation.

From here you can filter for the version you are running and to get a better understanding of your environments functionality.

In this particular case with the CREATE [OR REPLACE] EXTERNAL FUNCTION for SingleStoreDB On-Premises - this feature is not available until version 7.6. The associated docs are linked here.

As this feature is unavailable in 7.5, could you please try to upgrade the environment to at least 7.6 to get access?

Looking forward to hearing this resolved your issue. :pray:

10

Hi Maria,

I have 7.8 version docker image install now. but I m not able to use [CREATE [OR REPLACE] EXTERNAL FUNCTION feature to call a REST API hosted outside of memsql. I created the function and when calling the function I m getting -

  • ERROR 2576 UNKNOWN_ERR_CODE: External function runtime failure: Couldn’t connect to server

Attached code snippet of create function and api call.

image
image782×445 28.9 KB

Please note -
We are using in-premises db
I testing feature locally - API up and running locally , memsql setup locally.

Looking forward for response.
Thanks.

11

Have you looked into this: GitHub - memsql/deployment-docker: This repository contains our official deployment Docker images for various products. ? We will add this section into our documentation.

The HTTP API and External Functions features can be enabled when you create the container via passing environment variables.

HTTP API:

Add the following flags to your docker run command:

-e HTTP_API=ON -p 9000:9000

By default, the HTTP API runs on port 9000. If you want to use a different port you can instead run:

-e HTTP_API=ON -e HTTP_API_PORT=$PORT -p $PORT:$PORT

External Functions:

Add the following flag to your docker run command:

-e EXTERNAL_FUNCTIONS=ON

12

Hi arnaud,
I think the things are getting confusing for me now like what is external function vs calling HTTP rest api. Let me rephrase it please-

We have SingleStoredb-studio version 7.8 docker image db is in-primises. In this SingleStoreDB we have some stored procedures written which contains the pipeline which consume the data from a topic.

Similarly what I want is - there is a RestAPI as well which is hosted on tomcat server i.e like hosted somewhere else which takes some input and then calls to some tables and return the response. we have all the details like endpoint url token generator and all and we are like calling the API from postman.

Similarly we want to call this Rest API from singleStoreBD store procedure to fetch some data like and want to utilize RestAPI response .

For that I wrote the above code I might be wrong in understand what CREATE [OR REPLACE] EXTERNAL FUNCTION is for.

Now please let me know if this is possible form SingleStoreDB or not? Like do we need to create external function or we can directly call the API. If possible what all flag/ settings we needs to do and how to call the RestAPI (steps to call the API)

Also which version of singlestoredb-server and singlestoredb-studio support this feature.

Thanks

13

Have you tried installing your image with that command? -e HTTP_API=ON -p 9000:9000

I think that what you have started is the correct way to use that function. I would like to see if you still get the same error message when you have added the command.

1 Like
14

Hi arnaud
First thank you for quick response really appreciated.

I tried all like what you have been suggesting but still getting same error.
Bellow is the container command I run and have added like you suggested please check if command looks good.

docker run -i --init --name singlestore-ciab -v singlestore-ciab-data:/var/lib/memsql -e HTTP_API=ON -p 9000:9000 -e LICENSE_KEY=$LICENSE_KEY -e ROOT_PASSWORD=$ROOT_PASSWORD -p 3306:3306 -p 8080:8080 {proxy}/singlestore/cluster-in-a-box

15

To help you further, we would have to get more details in being able to repro those steps (I have tried to repo but I cannot connect to your URL and you probably don’t want to give me the credentials and configs on the forum).

Can you create a support ticket to be able to share more confidential information to repro your issue?

1 Like