DuckDB 开源版本大模型 Prompt 与Embedding来了

显示全部楼层

DuckDB HTTP 客户端扩展

这个非常实验性的扩展从 DuckDB 内部启动了一个 HTTP 客户端，用于处理 GET/POST 请求。

实验性：使用风险自负！

? 安装

INSTALLhttp_clientFROMcommunity;
LOADhttp_client;

注意：目前DuckDB 版本需要在1.1.2 才能直接下载插件，否则需要自己编译。

函数

•http_get(url)
•http_post(url, headers, params)

示例

GET 请求

WITH__inputAS(
SELECT
http_get(
'https://httpbin.org/delay/0'
)ASres
),
__responseAS(
SELECT
(res->>'status')::INTASstatus,
(res->>'reason')ASreason,
unnest(from_json(((res->>'body')::JSON)->'headers','{"Host":"VARCHAR"}'))ASfeatures
FROM
__input
)
SELECT
__response.status,
__response.reason,
__response.HostAShost
FROM
__response
;

┌────────┬─────────┬─────────────┐
│status│reason│host│
│int32│varchar│varchar│
├────────┼─────────┼─────────────┤
│200│OK│httpbin.org│
└────────┴─────────┴─────────────┘

POST 请求

WITH__inputAS(
SELECT
http_post(
'https://httpbin.org/delay/0',
headers=>MAP{
'accept':'application/json'
},
params=>MAP{}
)ASres
),
__responseAS(
SELECT
(res->>'status')::INTASstatus,
(res->>'reason')ASreason,
unnest(from_json(((res->>'body')::JSON)->'headers','{"Host":"VARCHAR"}'))ASfeatures
FROM
__input
)
SELECT
__response.status,
__response.reason,
__response.HostAShost
FROM
__response
;

┌────────┬─────────┬─────────────┐
│status│reason│host│
│int32│varchar│varchar│
├────────┼─────────┼─────────────┤
│200│OK│httpbin.org│
└────────┴─────────┴─────────────┘

POST JSON

前面的一个是POST parameters，下面的这个是POST JSON

WITH__inputAS(
SELECT
http_post(
'https://earth-search.aws.element84.com/v0/search',
headers=>MAP{
'Content-Type':'application/json',
'Accept-Encoding':'gzip',
'Accept':'application/geo+json'
},
params=>{
'collections':['sentinel-s2-l2a-cogs'],
'ids':['S2A_56LPN_20210930_0_L2A'],
'datetime':'2021-09-30/2021-09-30',
'limit':10
}
)ASres
),
__responseAS(
SELECT
unnest(from_json(((res->>'body')::JSON)->'features','["json"]'))ASfeatures
FROM
__input
)
SELECT
features->>'id'ASid
FROM
__response
;

http_post 部分等价于CURL.的

curl-XPOSThttps://earth-search.aws.element84.com/v0/search\
-H"Content-Type:application/json"\
-H"Accept-Encoding:gzip"\
-H"Accept:application/geo+json"\
-d'{
"collections":["sentinel-s2-l2a-cogs"],
"ids":["S2A_56LPN_20210930_0_L2A"],
"datetime":"2021-09-30/2021-09-30",
"limit":10
}
'

包含空间数据的完整示例

这是 @ahuarte47 提供的原始示例，启发了这个社区扩展。

INSTALLjson;
INSTALLhttpfs;
INSTALLspatial;
LOADjson;
LOADhttpfs;
LOADspatial;

WITH__inputAS(
SELECT
http_get(
'https://earth-search.aws.element84.com/v0/search'
)ASres
),
__featuresAS(
SELECT
unnest(from_json(((res->>'body')::JSON)->'features','["json"]'))
ASfeatures
FROM
__input
)
SELECT
features->>'id'ASid,
features->'properties'->>'sentinel:product_id'ASproduct_id,
concat(
'T',
features->'properties'->>'sentinel:utm_zone',
features->'properties'->>'sentinel:latitude_band',
features->'properties'->>'sentinel:grid_square'
)ASgrid_id,
ST_GeomFromGeoJSON(features->'geometry')ASgeom
FROM
__features
;

┌──────────────────────┬──────────────────────┬─────────┬──────────────────────────────────────────────────────────────────────────────────┐
│id│product_id│grid_id│geom│
│varchar│varchar│varchar│geometry│
├──────────────────────┼──────────────────────┼─────────┼──────────────────────────────────────────────────────────────────────────────────┤
│S2B_55GDP_20241003…│S2B_MSIL2A_2024100…│T55GDP│POLYGON((146.7963024570636-42.53859799130381,145.7818492341335-42.53284395…│
│S2B_55HEC_20241003…│S2B_MSIL2A_2024100…│T55HEC│POLYGON((146.9997932100229-34.429312828654396,146.9997955899612-33.4390429…│
│S2B_55JHN_20241003…│S2B_MSIL2A_2024100…│T55JHN│POLYGON((149.9810192714723-25.374826158099584,149.9573295859729-24.3845516…│
│S2B_15MWT_20230506…│S2B_MSIL2A_2023050…│T15MWT│POLYGON((-92.01266261624052-2.357695714729873,-92.0560908879947-2.35076658…│
│S2B_16PBT_20230506…│S2B_MSIL2A_2023050…│T16PBT│POLYGON((-88.7451873620346811.690012668805194,-88.951653651551211.72635252…│
│S2B_16PCT_20230506…│S2B_MSIL2A_2023050…│T16PCT│POLYGON((-87.8270359117675211.483638069337541,-88.834982453382611.70734355…│
│S2B_15PZP_20230506…│S2B_MSIL2A_2023050…│T15PZP│POLYGON((-89.2411388549891211.784951995968179,-89.3883168549088811.8080246…│
│S2B_16PET_20230506…│S2B_MSIL2A_2023050…│T16PET│POLYGON((-87.0001740876826211.277451946475995,-87.0001743848346411.7600349…│
│S2B_16PBU_20230506…│S2B_MSIL2A_2023050…│T16PBU│POLYGON((-88.7451896251917311.690373971442378,-89.6201790786661511.8466519…│
│S2B_16PDU_20230506…│S2B_MSIL2A_2023050…│T16PDU│POLYGON((-87.9178398221418311.670141095427311,-87.9209667656282412.5828090…│
└──────────────────────┴──────────────────────┴─────────┴──────────────────────────────────────────────────────────────────────────────────┘

封装MotherDuck的prompt 和embedding 函数

prompt

CREATEORREPLACEMACROprompt(q)AS(
WITH__inputAS(
SELECT
http_post(

'https://api.siliconflow.cn/v1/chat/completions',
headers=>MAP{
'Authorization':'Bearersk-xxxiobybdngkkkrodyphzwrvroozhgtzdsfkcpumrrtrxxxx',
'Content-Type':'application/json;charset=UTF-8'
},

params=>{
'model':'Qwen/Qwen2.5-7B-Instruct',
'messages':[
{'role':'system','content':'Youareahelpfulassistant.'},
{'role':'user','content':q}
]
}
)ASres
)
selectjson_extract(((table__input)->>'body')::JSON,'$.choices[0].message.content')aschat_response
);

selectprompt('"介绍下DuckDB"')asanswer

DuckDB是一个高性能的嵌入式SQL数据库，它可以在多个编程语言中轻松使用，支持各种数据源，并且可以进行内存计算和外部存储计算。...

WITHquestionas(SELECT*
FROM(VALUES('美国在首府？不超过10个字？',1),('大熊猫是哪个国家的国宝？不超过10个字',2))question(q,id))

SELECTid,q,prompt(q)asaFROMquestion;

q	id	a
0	美国在首府？不超过10个字	华盛顿特区是美国首府。
1	大熊猫是哪个国家的国宝？不超过10个字	大熊猫是中国的国宝。

embedding

CREATEORREPLACEMACROembedding(q)AS(
WITH __inputAS(
SELECT
http_post(
'https://api.siliconflow.cn/v1/embeddings',
headers=>MAP{
'Authorization':'Bearersk-xxxiobybdngkkkrodyphzwrvroozhgtzdsfkcpumrrtrxxxx',
'Content-Type':'application/json;charset=UTF-8'
},
params=>{
'model':'BAAI/bge-large-zh-v1.5',
'input':q,
'encoding_format':'float'
}
)ASres
)
--SELECT(table__input)
SELECTjson_extract(((table__input)->>'body')::JSON,'$.data[0].embedding')aschat_response
);
selectembedding('"介绍下DuckDB"')asembedding

[0.0024559293,0.0032171067,-0.032328855,0.0028547805,0.0151242,0.007012767,-0.03020165,...]

已知问题

http_get 缺少headers和parameters 的支持比如我想获得所有的模型

curlhttps://api.siliconflow.cn/v1/models\
-H"Content-Type:application/json"\
-H"Authorization:Bearersk-xxxiobybdngkkkrodyphzwrvroozhgtzdsfkcpumrrtrxxxx"