SQLCoder部署和應用

warm3snow發表於2024-07-15

主頁

個人微信公眾號：密碼應用技術實戰
個人部落格園首頁：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)