prompt and embedding function base on http_client

[alitrack]

prompt

CREATE OR REPLACE MACRO prompt(q) AS ( 
WITH __input AS (
    SELECT
      http_post(
          
          'https://api.openai.com/v1/chat/completions',
          headers => MAP {
            'Authorization': 'Bearer sk-your_openai_key',
            'Content-Type': 'application/json; charset=UTF-8'
          },

          params =>{
            'model': 'Qwen/Qwen2.5-7B-Instruct',
            'messages': [
              {'role': 'system', 'content': 'You are a helpful assistant.'},
              {'role': 'user', 'content':  q}
            ]
          }
      ) AS res
)
select json_extract(((table __input)->>'body')::JSON ,'$.choices[0].message.content') as  chat_response
);

select prompt('"introduce DuckDB"') as answer

embedding

CREATE OR REPLACE MACRO embedding(q) AS ( 
WITH  __input AS (
    SELECT
      http_post(
          'https://api.openai.com/v1/embeddings',
          headers => MAP {
            'Authorization': 'Bearer sk_your_openai_key',
            'Content-Type': 'application/json; charset=UTF-8'
          },
          params =>{
            'model': 'BAAI/bge-large-zh-v1.5',
            'input': q,
            'encoding_format':'float'
          }
      ) AS res
)
-- SELECT (table __input)
SELECT json_extract(((table __input)->>'body')::JSON ,'$.data[0].embedding') as  chat_response
);
select embedding('"DuckDB"') as embedding

for Chinese details, please visit
https://mp.weixin.qq.com/s?__biz=MzU1NTg2ODQ5Nw==&mid=2247490026&idx=1&sn=6c619eb49cf4e770fc84e60b37666d25&chksm=fbcc91d4ccbb18c2a3e4e1945caff20bdae2a2e3da16a28afcb72165228c2354aa4a6b6b6c3d&token=499112804&lang=zh_CN#rd

[lmangani]

Thanks @alitrack for the fantastic usage example! We might use this in the wiki/readme.

A similar function is offered with the open_prompt extension helper - do you see any missing feature there?

[alitrack]

Thanks @alitrack for the fantastic usage example! We might use this in the wiki/readme.

A similar function is offered with the open_prompt extension helper - do you see any missing feature there?

sounds great, I will try it.

[lmangani]

@alitrack I really like your article, please feel free to PR any example to the README 👍 or in the open_prompt extension!
@ahuarte47 also implemented parameters for GET requests which you also identified in the article, so you will be able to use those as well in the next release and remove the limitation 🎉

[falling-springs]

@lmangani @alitrack I tried using this example, and got the response below; curious if https is working correctly for you in this instance?

{ "status": 403, "reason": "Forbidden", "body": "{\n \"error\": {\n \"type\": \"invalid_request_error\",\n \"code\": \"http_unsupported\",\n \"message\": \"The OpenAI API is only accessible over HTTPS. Ensure the URL starts with 'https://' and not 'http://'.\",\n \"param\": null\n }\n}" }

[lmangani]

@falling-springs Make sure you're using the latest version of the extension for DuckDB v1.1.3 as this was reported as fix by other users.

[falling-springs]

@falling-springs Make sure you're using the latest version of the extension for DuckDB v1.1.3 as this was reported as fix by other users.

Interesting; I am seeing this error with duckdb version 1.1.3 and http_client extension version "d3a3dee" (which is the latest available via the community source, as far as I can tell, based on using the UPDATE EXTENSIONS statement); I am using duckdb via the python client / on macOS Sequoia 15.1 / on an M3 chip, if relevant.

[falling-springs]

@lmangani I'm not that familiar w/ the C API, but it looks like the SetupHttpClient function is just passing the domain (I.e. "api.openai.com") rather than the scheme + domain ("https://api.openai.com") when creating the client, which I think might default to http unless you explicitly use the SSLClient class? That's based on my quick skim of the readme at cpp-httlib

Happy to make a new issue / PR if helpful to you?

static std::pair<duckdb_httplib_openssl::Client, std::string> SetupHttpClient(const std::string &url) {
    std::string scheme, domain, path;
    size_t pos = url.find("://");
    std::string mod_url = url;
    if (pos != std::string::npos) {
        scheme = mod_url.substr(0, pos);
        mod_url.erase(0, pos + 3);
    }

    pos = mod_url.find("/");
    if (pos != std::string::npos) {
        domain = mod_url.substr(0, pos);
        path = mod_url.substr(pos);
    } else {
        domain = mod_url;
        path = "/";
    }

    // Create client and set a reasonable timeout (e.g., 10 seconds)
    duckdb_httplib_openssl::Client client(domain.c_str());
    client.set_read_timeout(10, 0);  // 10 seconds
    client.set_follow_location(true); // Follow redirects

    return std::make_pair(std::move(client), path);
}

EDIT:

Confirmed this worked as expected for me when I modified the extension to pass scheme + domain:

duckdb_httplib_openssl::Client client((scheme + "://" + domain).c_str());

[lmangani]

Thanks for the PR @falling-springs is the latest release working as expected?