无数据的db

看这篇文章《DuckDB不需要数据就可以成为数据库》，学到一个技巧, 可以使用创建view的方式定义好数据库结构，创建一个空的db，然后分发给使用者，让他们使用SQL来在本地分析个人电脑上的csv文件。

在一个目录中放置需要分析的csv文件，约定文件名格式，如access-*.log，attack-*.log, 然后执行如下命令，下面的操作会创建一个mydata.db文件，这个文件里不含有实际数据，只有表结构定义，

# 创建一个数据库
duckdb mydata.db

# 使用视图，从csv文件中读取日志数据, 指定数据格式
create view access as select * from read_csv('*access-*.log', delim=' ', timestampformat='%Y-%m-%d<SP>%H:%M:%S', names=['timestamp', 'start_time', 'req_time', '...']) ;

create view attack as select * from read_csv('*attack-*.log', delim=' ', timestampformat='%Y-%m-%d<SP>%H:%M:%S', names=['timestamp', 'start_time', 'req_time', 'attack_type', 'ban_type', '...']) ;

操作完成后，mydata.db文件就可以分发给其他人，他们放置到放日志文件的目录里，连接数据库，就可以看到access, attack两个表了，就可以进行操作。

~ duckdb mydata.db
v1.1.3 19864453f7
Enter ".help" for usage hints.
D .tables
access  attack
D select * from access limit 10

URL as db

将您的根URL转换为DuckDB远程数据库

ATTACH 'https://steplist.app/' AS steplist;
SELECT * FROM steplist.lists;

读json，代替jq

参考这个：DuckDB 作为新的 jq ，可以用duckdb读取json，替代jq，sql语法确实比jq的语法易读多了。

# curl 'https://api.github.com/orgs/golang/repos' > repos.json

select license.key as k, count(*) from 'repos.json' group by k limit 10;

# 按星标数排序项目
select full_name, stargazers_count as star, watchers_count as watch from 'repos.json' order by star desc;
┌───────────────────┬────────┬────────┐
│     full_name     │  star  │ watch  │
│      varchar      │ int64  │ int64  │
├───────────────────┼────────┼────────┤
│ golang/go         │ 124210 │ 124210 │
│ golang/groupcache │  12962 │  12962 │
│ golang/protobuf   │   9792 │   9792 │
│ golang/mock       │   9321 │   9321 │

select full_name, open_issues from 'repos.json' where open_issues > 10 order by open_issues desc limit 5;
┌─────────────────┬─────────────┐
│    full_name    │ open_issues │
│     varchar     │    int64    │
├─────────────────┼─────────────┤
│ golang/go       │        9711 │
│ golang/tour     │         421 │
│ golang/oauth2   │         208 │
│ golang/gddo     │         167 │
│ golang/protobuf │         110 │
└─────────────────┴─────────────┘

一次分解一个向量，分解深层嵌套的 JSON

Parquet

Parquet是一种列压缩的文件格式，一个文件就是一个数据表，里面分段存储了数据的一些meta信息，因此查询时可以跳过某些分段从而加速。

https://duckdb.org/2021/06/25/querying-parquet.html

select * from read_parquet(['prices.parquet', 'holdings.parquet'], union_by_name=True, filename=True);


SELECT * from parquet_metadata('prices.parquet');


SELECT * from parquet_file_metadata('prices.parquet');

-- schema
SELECT * from parquet_schema('prices.parquet');


-- create parquet from query
copy (select * from icp) to 'icp.parquet' (format 'parquet');

Plot

使用uplot来在终端绘图。

~ duckdb -s "copy (SELECT userId, count(*) AS count FROM read_json_auto('todos.json') where completed = True GROUP BY userId ORDER BY count DESC) to '/dev/stdout' with (format 'csv', HEADER) " | uplot bar -d, -H -t "Completed tasks count"

                Completed tasks count
      ┌                                        ┐
   10 ┤■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 12.0
    5 ┤■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 12.0
    1 ┤■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 11.0
    8 ┤■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 11.0
    7 ┤■■■■■■■■■■■■■■■■■■■■■■■■■■ 9.0
    9 ┤■■■■■■■■■■■■■■■■■■■■■■■ 8.0
    2 ┤■■■■■■■■■■■■■■■■■■■■■■■ 8.0
    3 ┤■■■■■■■■■■■■■■■■■■■■ 7.0
    6 ┤■■■■■■■■■■■■■■■■■ 6.0
    4 ┤■■■■■■■■■■■■■■■■■ 6.0
      └                                        ┘

Minio

参考： https://blog.min.io/duckdb-and-minio-for-a-modern-data-stack/

可以使用duckdb操作在minio中的csv、parquet数据。

INSTALL httpfs;
LOAD httpfs;
SET s3_region='us-east-1';
SET s3_url_style='path';
SET s3_endpoint='play.min.io:9000';
SET s3_access_key_id='***' ;
SET s3_secret_access_key='***';

# query remote parquet
SELECT owner, count(*) as c FROM read_parquet('s3://demo/icp.parquet') group by owner order by c desc limit 10

SQL


-- basic
show tables
show all tables
.tables

-- Describing a Table
show access
show table access;
DESCRIBE access
DESCRIBE SELECT host,req_uri FROM access


-- sumarize a table.
SUMMARIZE access;

-- sumarize a query;
SUMMARIZE select * from access where req_uri != '-';

-- 将下一条命令的输出在excel中打开。输出结果会生成一个临时文件，然后调用excel打开
.excel
select * from access where resp_code != 200;

-- between
select start_time, host, req_uri from access where resp_code between 1 and 1000 limit 10;

-- in
select start_time, host, req_uri from access where req_uri in ('-', '/a') limit 100;

-- distinct
select DISTINCT host,req_uri,resp_code from access order by host;

-- sub 
select * from access where send_size =  (select max(send_size) from access);

-- max, group
select host,max(send_size) from access group by host;


-- group by, having
select host,max(send_size) from access group by host having max(send_size) > 1000;

参考资料：

无数据的db#

URL as db#

读json，代替jq#

Parquet#

Plot#

Minio#

SQL#