DuckDB 定位OLAP方向的SQLite, 适合嵌入式数据分析 - tpch测试与简单试用
digoal约 10 分钟标签
PostgreSQL , DuckDB , SQLite , OLAP , TPC-H
背景
duckdb的定位是OLAP方向的sqlite, 适用于嵌入式环境的数据分析. SQL语法支持丰富, duckdb的语法非常接近PostgreSQL. 由于duckdb使用简单, 也非常适合开发者做数据科学分析.
下面简单测试一下dockdb的tpch测试, 以及一些常用的数据生成, SQL用法.
1、下载
下载macos下的命令行版本, 用户学习duckdb
https://duckdb.org/docs/installation/index
https://github.com/duckdb/duckdb/releases/download/v0.4.0/duckdb_cli-osx-universal.zip
unzip duckdb_cli-osx-universal.zip
2、启动
./duckdb
3、安装tpch插件
https://duckdb.org/docs/extensions/overview
https://duckdb.org/docs/api/cli
D install 'tpch';
4、加载tpch插件
D load 'tpch';
5、生成tpch数据
https://github.com/pdet/duckdb-tutorial
D CALL dbgen(sf=0.1);
6、查看表
D show tables;
┌──────────┐
│ name │
├──────────┤
│ customer │
│ lineitem │
│ nation │
│ orders │
│ part │
│ partsupp │
│ region │
│ supplier │
└──────────┘
7、配置参数
D .timer on
8、查询tpc-h SQL
SELECT
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) AS revenue,
o_orderdate,
o_shippriority
FROM
customer,
orders,
lineitem
WHERE
c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < CAST('1995-03-15' AS date)
AND l_shipdate > CAST('1995-03-15' AS date)
GROUP BY
l_orderkey,
o_orderdate,
o_shippriority
ORDER BY
revenue DESC,
o_orderdate
LIMIT 10; ┌────────────┬─────────────┬─────────────┬────────────────┐
│ l_orderkey │ revenue │ o_orderdate │ o_shippriority │
├────────────┼─────────────┼─────────────┼────────────────┤
│ 223140 │ 355369.0698 │ 1995-03-14 │ 0 │
│ 584291 │ 354494.7318 │ 1995-02-21 │ 0 │
│ 405063 │ 353125.4577 │ 1995-03-03 │ 0 │
│ 573861 │ 351238.2770 │ 1995-03-09 │ 0 │
│ 554757 │ 349181.7426 │ 1995-03-14 │ 0 │
│ 506021 │ 321075.5810 │ 1995-03-10 │ 0 │
│ 121604 │ 318576.4154 │ 1995-03-07 │ 0 │
│ 108514 │ 314967.0754 │ 1995-02-20 │ 0 │
│ 462502 │ 312604.5420 │ 1995-03-08 │ 0 │
│ 178727 │ 309728.9306 │ 1995-02-25 │ 0 │
└────────────┴─────────────┴─────────────┴────────────────┘
Run Time: real 0.011 user 0.022415 sys 0.001322
其他常用的用法
1、generate_series, range 生成数据
https://duckdb.org/docs/sql/functions/nested
range(start, stop, step)
range(start, stop)
range(stop)
generate_series(start, stop, step)
generate_series(start, stop)
generate_series(stop)
例如: 生成大量测试数据
create table tbl (gid bigint, crt_time timestamp, info text, v numeric);
insert into tbl select t1.generate_series, now() + (t1.generate_series*t2.generate_series||' second')::interval, md5(random()::text), random()*1000 from
(select * from generate_series(1,10) ) t1,
(select * from generate_series(1,100000)) t2;
create index idx_tbl_1 on tbl (gid, crt_time);
D select * from tbl limit 10;
┌─────┬─────────────────────────┬──────────────────────────────────┬─────────┐
│ gid │ crt_time │ info │ v │
├─────┼─────────────────────────┼──────────────────────────────────┼─────────┤
│ 1 │ 2022-08-26 06:45:55.731 │ 10ac3e73c0c92e79119ad97e4e23dd14 │ 397.538 │
│ 1 │ 2022-08-26 06:45:56.731 │ d8326304f6e0c7fd0706f2be3f3bec18 │ 792.504 │
│ 1 │ 2022-08-26 06:45:57.731 │ ce61632252bc56f161a0732131eb8258 │ 866.972 │
│ 1 │ 2022-08-26 06:45:58.731 │ 706e6740e66c9daf203b93deb321fcf0 │ 803.576 │
│ 1 │ 2022-08-26 06:45:59.731 │ 57f22d800fd0afd5905341e3fcca693f │ 232.984 │
│ 1 │ 2022-08-26 06:46:00.731 │ d27dc48c88cb2de684085b82770ebb2a │ 179.774 │
│ 1 │ 2022-08-26 06:46:01.731 │ b5c8015e94f72bf0f3472eb7b4e6548f │ 891.579 │
│ 1 │ 2022-08-26 06:46:02.731 │ d70d14790a9b3a00d4c6dbc2f7c448b7 │ 204.278 │
│ 1 │ 2022-08-26 06:46:03.731 │ 5bd45f42c9cf073eaeb8e81eb54c8f7a │ 814.422 │
│ 1 │ 2022-08-26 06:46:04.731 │ d90e8764f1b8f37ee34afdd7024774bd │ 589.196 │
└─────┴─────────────────────────┴──────────────────────────────────┴─────────┘
Run Time: real 0.002 user 0.002828 sys 0.000333
2、统计数据柱状图信息
https://duckdb.org/docs/guides/meta/summarize
D SUMMARIZE tbl;
┌─────────────┬───────────────┬──────────────────────────────────┬──────────────────────────────────┬───────────────┬───────────────┬───────────────────┬─────┬─────┬─────┬─────────┬─────────────────┐
│ column_name │ column_type │ min │ max │ approx_unique │ avg │ std │ q25 │ q50 │ q75 │ count │ null_percentage │
├─────────────┼───────────────┼──────────────────────────────────┼──────────────────────────────────┼───────────────┼───────────────┼───────────────────┼─────┼─────┼─────┼─────────┼─────────────────┤
│ gid │ BIGINT │ 1 │ 10 │ 10 │ 5.5 │ 2.87228275941074 │ 3 │ 6 │ 8 │ 1000000 │ 0.0% │
│ crt_time │ TIMESTAMP │ 2022-08-26 06:45:55.731 │ 2022-09-06 20:32:34.731 │ 502481 │ │ │ │ │ │ 1000000 │ 0.0% │
│ info │ VARCHAR │ 000013c1e3a86c264720b7115e583653 │ ffffd28e7a1fac6d008abf4d1d8799d9 │ 992246 │ │ │ │ │ │ 1000000 │ 0.0% │
│ v │ DECIMAL(18,3) │ 0.003 │ 1000.000 │ 626691 │ 500.097364103 │ 288.8111132165053 │ 251 │ 500 │ 751 │ 1000000 │ 0.0% │
└─────────────┴───────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────┴───────────────┴───────────────────┴─────┴─────┴─────┴─────────┴─────────────────┘
Run Time: real 0.074 user 0.511695 sys 0.002223
3、查询计划
D explain select * from tbl where gid in (9,10) order by crt_time limit 10; ┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ TOP_N │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ Top 10 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ tbl.crt_time ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ tbl │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ gid │
│ crt_time │
│ info │
│ v │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│Filters: gid>=9 AND gid<=10│
│ AND gid IS NOT NULL │
└───────────────────────────┘
Run Time: real 0.001 user 0.001180 sys 0.000133
4、客户端帮助
./duckdb --help
Usage: ./duckdb [OPTIONS] FILENAME [SQL]
FILENAME is the name of an DuckDB database. A new database is created
if the file does not previously exist.
OPTIONS include:
-append append the database to the end of the file
-ascii set output mode to 'ascii'
-bail stop after hitting an error
-batch force batch I/O
-box set output mode to 'box'
-column set output mode to 'column'
-cmd COMMAND run "COMMAND" before reading stdin
-c COMMAND run "COMMAND" and exit
-csv set output mode to 'csv'
-echo print commands before execution
-init FILENAME read/process named file
-[no]header turn headers on or off
-help show this message
-html set output mode to HTML
-interactive force interactive I/O
-json set output mode to 'json'
-line set output mode to 'line'
-list set output mode to 'list'
-lookaside SIZE N use N entries of SZ bytes for lookaside memory
-markdown set output mode to 'markdown'
-memtrace trace all memory allocations and deallocations
-mmap N default mmap size set to N
-newline SEP set output row separator. Default: '\n'
-nofollow refuse to open symbolic links to database files
-no-stdin exit after processing options instead of reading stdin -nullvalue TEXT set text string for NULL values. Default ''
-pagecache SIZE N use N slots of SZ bytes each for page cache memory
-quote set output mode to 'quote'
-readonly open the database read-only
-s COMMAND run "COMMAND" and exit
-separator SEP set output column separator. Default: '|'
-stats print memory stats before each finalize
-table set output mode to 'table'
-version show DuckDB version
-vfs NAME use NAME as the default VFS
5、命令行帮助
D .help
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?TABLE? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode
.nullvalue STRING Use STRING in place of NULL values
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILE Read input from FILE
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
.separator COL ?ROW? Change the column and row separators
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ?on|off? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ... Run various sqlite3_test_control() operations
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set minimum column widths for columnar output
6、元命令
https://duckdb.org/docs/guides/meta/explain
7、读取PostgreSQL的数据
https://duckdb.org/docs/extensions/overview
postgres_scanner
D INSTALL 'postgres_scanner';
Run Time: real 0.000 user 0.000192 sys 0.000165
D load 'postgres_scanner';
Run Time: real 0.001 user 0.000697 sys 0.000395
Error: Invalid Input Error: Initialization function "postgres_scanner_init" from file "/Users/digoal/.duckdb/extensions/da9ee490d/osx_amd64/postgres_scanner.duckdb_extension" threw an exception: "Catalog Error: Table Function with name "postgres_scan" already exists!" postgres=# create table t1 (id int, info text);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,1000), 'test';
INSERT 0 1000
D SELECT * FROM POSTGRES_SCAN('host=127.0.0.1 user=postgres dbname=postgres port=1921', 'public', 't1') limit 10;
┌────┬──────┐
│ id │ info │
├────┼──────┤
│ 1 │ test │
│ 2 │ test │
│ 3 │ test │
│ 4 │ test │
│ 5 │ test │
│ 6 │ test │
│ 7 │ test │
│ 8 │ test │
│ 9 │ test │
│ 10 │ test │
└────┴──────┘
Run Time: real 0.034 user 0.016557 sys 0.001078
8、插件
https://duckdb.org/docs/extensions/overview
install安装
load加载 安装后, 直接load加载.
IT-C02YW2EFLVDL:osx_amd64 digoal$ ll
total 89472
drwxr-xr-x 3 digoal staff 96B Aug 26 14:20 ..
-rw-r--r-- 1 digoal staff 15M Aug 26 14:20 fts.duckdb_extension
-rw-r--r-- 1 digoal staff 14M Aug 26 14:20 postgres_scanner.duckdb_extension
drwxr-xr-x 5 digoal staff 160B Aug 26 14:22 .
-rw-r--r-- 1 digoal staff 15M Aug 26 14:22 tpch.duckdb_extension
IT-C02YW2EFLVDL:osx_amd64 digoal$ pwd
/Users/digoal/.duckdb/extensions/da9ee490d/osx_amd64
9、web shell
https://shell.duckdb.org/
10、查询duckdb支持的所有函数
D select * from duckdb_functions() where function_name like '%db%';
┌─────────────┬─────────────────────┬───────────────┬─────────────┬─────────────┬─────────────────────────────────┬─────────────────────────────────────┬─────────┬──────────────────┬──────────────────┐
│ schema_name │ function_name │ function_type │ description │ return_type │ parameters │ parameter_types │ varargs │ macro_definition │ has_side_effects │
├─────────────┼─────────────────────┼───────────────┼─────────────┼─────────────┼─────────────────────────────────┼─────────────────────────────────────┼─────────┼──────────────────┼──────────────────┤
│ main │ duckdb_columns │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_constraints │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_functions │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_keywords │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_indexes │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_schemas │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_dependencies │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_sequences │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_settings │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_tables │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_types │ table │ │ │ [] │ [] │ │ │ │
│ main │ duckdb_views │ table │ │ │ [] │ [] │ │ │ │
│ main │ dbgen │ table │ │ │ [suffix, schema, overwrite, sf] │ [VARCHAR, VARCHAR, BOOLEAN, DOUBLE] │ │ │ │
│ main │ roundbankers │ macro │ │ │ [x, n] │ [NULL, NULL] │ │ round_even(x, n) │ │
└─────────────┴─────────────────────┴───────────────┴─────────────┴─────────────┴─────────────────────────────────┴─────────────────────────────────────┴─────────┴──────────────────┴──────────────────┘
D select * from duckdb_functions() where function_name like '%tpc%';
┌─────────────┬───────────────┬───────────────┬─────────────┬─────────────┬────────────┬─────────────────┬─────────┬──────────────────┬──────────────────┐
│ schema_name │ function_name │ function_type │ description │ return_type │ parameters │ parameter_types │ varargs │ macro_definition │ has_side_effects │
├─────────────┼───────────────┼───────────────┼─────────────┼─────────────┼────────────┼─────────────────┼─────────┼──────────────────┼──────────────────┤
│ main │ tpch_queries │ table │ │ │ [] │ [] │ │ │ │
│ main │ tpch_answers │ table │ │ │ [] │ [] │ │ │ │
│ main │ tpch │ pragma │ │ │ [col0] │ [BIGINT] │ │ │ │
└─────────────┴───────────────┴───────────────┴─────────────┴─────────────┴────────────┴─────────────────┴─────────┴──────────────────┴──────────────────┘
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出.