主頁
- 個人微信公眾號：密碼應用技術實戰
- 個人部落格園首頁：https://www.cnblogs.com/informatics/
SQLCoder簡介
SQLCoder是一個用於生成SQL語句的工具，可以透過輸入自然語言描述的需求，生成對應的SQL語句。SQLCoder支援連線資料庫，對生成的SQL語句可以直接自動執行，並以圖表的形式展示結果。SQLCoder是一個開源專案，可以在GitHub上找到原始碼和文件。
SQLCoder部署
SQLCoder可以使用pip安裝，也可以從GitHub上下載原始碼進行部署。下面以pip安裝為例，介紹SQLCoder的部署方法。
注：SQLCoder部署依賴於硬體環境，本文以MacOS M3為例，其他環境可能有所不同。
SQLCoder安裝&啟動
- 安裝SQLCoder
CMAKE_ARGS="-DLLAMA_METAL=on" pip install "sqlcoder[llama-cpp]"
- 下載模型&啟動服務
➜ ~ sqlcoder launch
Downloading the SQLCoder-7b-2 GGUF file. This is a ~5GB file and may take a long time to download. But once it's downloaded, it will be saved on your machine and you won't have to download it again.
sqlcoder-7b-q5_k_m.gguf: 73%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████
Starting SQLCoder server...
Serving static server...
Press Ctrl+C to exit.
Static folder is /usr/local/lib/python3.11/site-packages/sqlcoder/static
127.0.0.1 - - [15/Jul/2024 15:44:41] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:41] "GET /_next/static/css/321c398b2a784143.css HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:41] "GET /_next/static/chunks/webpack-1657be5a4830bbb9.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/framework-02223fe42ab9321b.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/main-d30d248d262e39c4.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/pages/_app-db0976def6406e5e.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/238-21e16f207d48d221.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/pages/index-a1b2fa2d87d27d8d.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/PhIFrR5mo2t2wIFmxfdiU/_buildManifest.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/PhIFrR5mo2t2wIFmxfdiU/_ssgManifest.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /favicon.ico HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/pages/extract-metadata-2dc614052128d5d3.js HTTP/1.1" 200 -
127.0.0.1 - - [15/Jul/2024 15:44:42] "GET /_next/static/chunks/pages/query-data-0be55b0a48827890.js HTTP/1.1" 200 -
/bin/sh: lspci: command not found
llama_model_loader: loaded meta data with 22 key-value pairs and 291 tensors from /Users/hxy/.defog/sqlcoder-7b-q5_k_m.gguf (version GGUF V3 (latest))
llama_model_loader: Dumping metadata keys/values. Note: KV overrides do not apply in this output.
llama_model_loader: - kv 0: general.architecture str = llama
llama_model_loader: - kv 1: general.name str = .
llama_model_loader: - kv 2: llama.context_length u32 = 16384
llama_model_loader: - kv 3: llama.embedding_length u32 = 4096
llama_model_loader: - kv 4: llama.block_count u32 = 32
llama_model_loader: - kv 5: llama.feed_forward_length u32 = 11008
llama_model_loader: - kv 6: llama.rope.dimension_count u32 = 128
llama_model_loader: - kv 7: llama.attention.head_count u32 = 32
llama_model_loader: - kv 8: llama.attention.head_count_kv u32 = 32
llama_model_loader: - kv 9: llama.attention.layer_norm_rms_epsilon f32 = 0.000010
llama_model_loader: - kv 10: llama.rope.freq_base f32 = 1000000.000000
llama_model_loader: - kv 11: general.file_type u32 = 17
llama_model_loader: - kv 12: tokenizer.ggml.model str = llama
llama_model_loader: - kv 13: tokenizer.ggml.tokens arr[str,32016] = ["<unk>", "<s>", "</s>", "<0x00>", "<...
llama_model_loader: - kv 14: tokenizer.ggml.scores arr[f32,32016] = [0.000000, 0.000000, 0.000000, 0.0000...
llama_model_loader: - kv 15: tokenizer.ggml.token_type arr[i32,32016] = [2, 3, 3, 6, 6, 6, 6, 6, 6, 6, 6, 6, ...
llama_model_loader: - kv 16: tokenizer.ggml.bos_token_id u32 = 1
llama_model_loader: - kv 17: tokenizer.ggml.eos_token_id u32 = 2
llama_model_loader: - kv 18: tokenizer.ggml.unknown_token_id u32 = 0
llama_model_loader: - kv 19: tokenizer.ggml.add_bos_token bool = true
llama_model_loader: - kv 20: tokenizer.ggml.add_eos_token bool = false
llama_model_loader: - kv 21: general.quantization_version u32 = 2
llama_model_loader: - type f32: 65 tensors
llama_model_loader: - type q5_K: 193 tensors
llama_model_loader: - type q6_K: 33 tensors
llm_load_vocab: special tokens cache size = 259
llm_load_vocab: token to piece cache size = 0.1686 MB
llm_load_print_meta: format = GGUF V3 (latest)
llm_load_print_meta: arch = llama
llm_load_print_meta: vocab type = SPM
llm_load_print_meta: n_vocab = 32016
llm_load_print_meta: n_merges = 0
llm_load_print_meta: vocab_only = 0
llm_load_print_meta: n_ctx_train = 16384
llm_load_print_meta: n_embd = 4096
llm_load_print_meta: n_layer = 32
llm_load_print_meta: n_head = 32
llm_load_print_meta: n_head_kv = 32
llm_load_print_meta: n_rot = 128
llm_load_print_meta: n_swa = 0
llm_load_print_meta: n_embd_head_k = 128
llm_load_print_meta: n_embd_head_v = 128
llm_load_print_meta: n_gqa = 1
llm_load_print_meta: n_embd_k_gqa = 4096
llm_load_print_meta: n_embd_v_gqa = 4096
llm_load_print_meta: f_norm_eps = 0.0e+00
llm_load_print_meta: f_norm_rms_eps = 1.0e-05
llm_load_print_meta: f_clamp_kqv = 0.0e+00
llm_load_print_meta: f_max_alibi_bias = 0.0e+00
llm_load_print_meta: f_logit_scale = 0.0e+00
llm_load_print_meta: n_ff = 11008
llm_load_print_meta: n_expert = 0
llm_load_print_meta: n_expert_used = 0
llm_load_print_meta: causal attn = 1
llm_load_print_meta: pooling type = 0
llm_load_print_meta: rope type = 0
llm_load_print_meta: rope scaling = linear
llm_load_print_meta: freq_base_train = 1000000.0
llm_load_print_meta: freq_scale_train = 1
llm_load_print_meta: n_ctx_orig_yarn = 16384
llm_load_print_meta: rope_finetuned = unknown
llm_load_print_meta: ssm_d_conv = 0
llm_load_print_meta: ssm_d_inner = 0
llm_load_print_meta: ssm_d_state = 0
llm_load_print_meta: ssm_dt_rank = 0
llm_load_print_meta: model type = 7B
llm_load_print_meta: model ftype = Q5_K - Medium
llm_load_print_meta: model params = 6.74 B
llm_load_print_meta: model size = 4.45 GiB (5.68 BPW)
llm_load_print_meta: general.name = .
llm_load_print_meta: BOS token = 1 '<s>'
llm_load_print_meta: EOS token = 2 '</s>'
llm_load_print_meta: UNK token = 0 '<unk>'
llm_load_print_meta: LF token = 13 '<0x0A>'
llm_load_print_meta: max token length = 48
llm_load_tensors: ggml ctx size = 0.14 MiB
llm_load_tensors: offloading 0 repeating layers to GPU
llm_load_tensors: offloaded 0/33 layers to GPU
llm_load_tensors: CPU buffer size = 4560.96 MiB
..................................................................................................
llama_new_context_with_model: n_ctx = 4096
llama_new_context_with_model: n_batch = 512
llama_new_context_with_model: n_ubatch = 512
llama_new_context_with_model: flash_attn = 0
llama_new_context_with_model: freq_base = 1000000.0
llama_new_context_with_model: freq_scale = 1
llama_kv_cache_init: CPU KV buffer size = 2048.00 MiB
llama_new_context_with_model: KV self size = 2048.00 MiB, K (f16): 1024.00 MiB, V (f16): 1024.00 MiB
llama_new_context_with_model: CPU output buffer size = 0.12 MiB
llama_new_context_with_model: CPU compute buffer size = 296.01 MiB
llama_new_context_with_model: graph nodes = 1030
llama_new_context_with_model: graph splits = 514
AVX = 0 | AVX_VNNI = 0 | AVX2 = 0 | AVX512 = 0 | AVX512_VBMI = 0 | AVX512_VNNI = 0 | AVX512_BF16 = 0 | FMA = 0 | NEON = 0 | SVE = 0 | ARM_FMA = 0 | F16C = 0 | FP16_VA = 0 | WASM_SIMD = 0 | BLAS = 1 | SSE3 = 1 | SSSE3 = 1 | VSX = 0 | MATMUL_INT8 = 0 | LLAMAFILE = 0 |
Model metadata: {'general.quantization_version': '2', 'tokenizer.ggml.add_eos_token': 'false', 'tokenizer.ggml.add_bos_token': 'true', 'tokenizer.ggml.unknown_token_id': '0', 'tokenizer.ggml.eos_token_id': '2', 'tokenizer.ggml.bos_token_id': '1', 'tokenizer.ggml.model': 'llama', 'llama.attention.head_count_kv': '32', 'llama.context_length': '16384', 'llama.attention.head_count': '32', 'llama.rope.freq_base': '1000000.000000', 'llama.rope.dimension_count': '128', 'general.file_type': '17', 'llama.feed_forward_length': '11008', 'llama.embedding_length': '4096', 'llama.block_count': '32', 'general.architecture': 'llama', 'llama.attention.layer_norm_rms_epsilon': '0.000010', 'general.name': '.'}
Using fallback chat format: llama-2
INFO: Started server process [51187]
INFO: Waiting for application startup.
INFO: Application startup complete.
INFO: Uvicorn running on http://localhost:1235 (Press CTRL+C to quit)
使用SQLCoder
SQLCoder支援多種資料庫型別，下面以PostgreSQL為例，介紹SQLCoder的使用方法。
注：在使用SQLCoder之前，需確保已經安裝了PostgreSQL資料庫，並且資料庫服務已經啟動。
初始化PostgreSQL測試資料
安裝PostgreSQL
以下是在MacOS上安裝PostgreSQL的方法，其他系統可能有所不同。
- 安裝PostgreSQL
brew install postgresql@15
- 啟動PostgreSQL服務
brew services start postgresql@15
- 安裝PostgreSQL命令列客戶端
brew install libpq
- 建立使用者和資料庫
# 建立使用者
➜ createuser --interactive --pwprompt
輸入要增加的角色名稱: root
為新角色輸入的口令:
再輸入一遍:
新的角色是否是超級使用者? (y/n) y
# 建立資料庫
➜ createdb test
# 連線資料庫
➜ psql -U root -d test
psql (16.3, server 15.7 (Homebrew))
Type "help" for help.
test=#
初始化測試資料
# 建立表
test=# CREATE TABLE myuser (
test(# username VARCHAR(50),
test(# password VARCHAR(50),
test(# age INT,
test(# email VARCHAR(100)
test(# );
test=# INSERT INTO myuser (username, password, age, email)
test-# VALUES ('JohnDoe', 'password123', 25, 'johndoe@example.com');
INSERT 0 1
test=#
test=# INSERT INTO myuser (username, password, age, email)
test-# VALUES ('JaneSmith', 'pass456', 30, 'janesmith@example.com');
INSERT 0 1
test=# CREATE TABLE myphone (
test(# username VARCHAR(50),
test(# type VARCHAR(50),
test(# price DECIMAL(10, 2)
test(# );
CREATE TABLE
test=#
test=# INSERT INTO myphone (username, type, price)
test-# VALUES ('JohnDoe', 'iPhone', 999.99),
test-# ('JohnDoe', 'Samsung', 799.99),
test-# ('JohnDoe', 'Google Pixel', 699.99),
test-# ('JaneSmith', 'iPhone', 999.99),
test-# ('JaneSmith', 'OnePlus', 699.99),
test-# ('JaneSmith', 'Xiaomi', 499.99);
INSERT 0 6
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | myphone | table | root
public | myuser | table | root
(2 rows)
開啟SQLCoder前端頁面
- 開啟瀏覽器，輸入URL：http://localhost:8002，如果SQLCoder部署成功的話，會顯示如下頁面：
- 錄入資料庫連線資訊，並載入測試表Schema
-
輸入需求，生成SQL語句，並檢視結果（表格形式）
-
檢視結果（圖表展示）
總結
SQLCoder是一個用於生成SQL語句的工具，本文介紹了SQLCoder的部署方法和使用方法。希望本文對大家有所幫助。
參考文獻
- [1] SQLCoder: https://github.com/defog-ai/sqlcoder