Skip to content

Latest commit

 

History

History
138 lines (110 loc) · 3.54 KB

本地数据库调用本地模型.md

File metadata and controls

138 lines (110 loc) · 3.54 KB
Raw

本地数据库调用本地模型

1. 部署本地模型

此例中我们部署的模型是defog/llama-3-sqlcoder-8b

部署后暴露为接口：

curl -X 'POST' \
  'http://111.111.111.111:8000/generate' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "prompt": "......."
}'

返回结果：

{
  "sql_query": "SELECT ......"
}

2. 在数据库中打开访问权限

本地19c数据库可以直接访问http网址，云数据库如果不支持http，需要将上一步的端口部署为https.

BEGIN  
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => '111.111.111.111',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'DEMO',
                             principal_type => xs_acl.ptype_db)
   );
END;
/

测试网络访问：

select UTL_HTTP.REQUEST('http://111.111.111.111:8000/docs') RESPONSE from dual;

3. 注册模型

BEGIN
    -- CUSTOM_CLOUD_AI.UNREGIST_MODEL(provider => 'local_sqlcoder');

    CUSTOM_CLOUD_AI.REGIST_MODEL(
		provider    =>    'local_sqlcoder',
		endpoint    =>    'http://111.111.111.111:8000/generate',
		response_parse_path    =>    '$.sql_query',
		request_template    =>    '{
  "prompt": "<CONTENT>"
}'
	);
END;
/

4. 创建Profile

BEGIN
    -- CUSTOM_CLOUD_AI.DROP_PROFILE(profile_name => 'DEEPSEEK_LIANG');

	CUSTOM_CLOUD_AI.CREATE_PROFILE(
      profile_name    =>'local_sqlcoder',
	  description     => 'Use local deploy LLM AI API in Oracle Database',
      attributes      => '{
	    "provider": "local_sqlcoder",
        "object_list": [{"owner": "DEMO", "name": "SALES"},
                        {"owner": "DEMO", "name": "CUSTOMERS"}
						]
       }');
END;
/

5. 更新提示词

sqlcoder对于提示词有特殊要求，因此参考huggingface，修改提示词模板。

BEGIN
	CUSTOM_CLOUD_AI.UPDATE_PROFILE_PROMPT(
      profile_name    =>'local_sqlcoder',
	  template_name     => 'prompt_template',
	  template_value     => '<|start_header_id|>user<|end_header_id|>

Generate a SQL query to answer this question: `<prompt>`
- Ensure that the generated SQL query is syntactically correct and applicable to the provided table schema.
- Only generate SELECT SQL queries, never answer INSERT, UPDATE, DELETE etc.
- Generate plain text without markdown formate. Do not write anything else except the SQL query.
- Always use English column names and Chinese column alias. 
- Select proper columns. Keep the column sort order as above.
- If the question is too complex to answer, you can think it step by step. For example, the question "How many times do customers who spend more than 1000 spend" can be decomposed into "1. Who are the customers who spend more than 1000, 2. What are the times these users spend" .

DDL statements:
<table_infos><|eot_id|><|start_header_id|>assistant<|end_header_id|>

The following SQL query best answers the question `<prompt>`:
```sql');
END;
/

测试生成SQL

SELECT 
	CUSTOM_CLOUD_AI.SHOWSQL(
      profile_name    =>'local_sqlcoder',
	  prompt     => '消费金额超过1000的用户的城市分布'
	  ) as RESPONSE
FROM DUAL;

结果如下：

SELECT c.CUST_CITY,
       COUNT(*) AS COUNT
FROM DEMO.SALES s
JOIN DEMO.CUSTOMERS c ON s.CUST_ID = c.CUST_ID
WHERE s.AMOUNT_SOLD > 10
GROUP BY c.CUST_CITY
ORDER BY COUNT DESC NULLS LAST