digoal
2023-12-30
PostgreSQL , PolarDB , DuckDB , MotherDuck , s3 , serverless , noserver , 数据集市 , shareable data marketing
更多用法请参考motherduck手册
《期望未来DuckDB的架构演变方向》 里说到了serverless的演进方向, 想不到已经有了motherduck这样的产品.
计算可以在motherduck完成, 也可以在本地完成, 数据可以存储在本地也可以存储在远程. 甚至可以通过创建shared数据快照将数据共享给其他人. 俨然是一个serverless/noserver版的数据分析集市.
分享数据: https://motherduck.com/docs/key-tasks/managing-shared-motherduck-database/
Create a SHARE
Retrieve a URL
Send the URL to your colleague
挂载并查询别人分享的数据: https://motherduck.com/docs/key-tasks/querying-a-shared-motherduck-database
An attached shared database behaves just like a read-only database in every way.
ATTACH '<share URL>' [AS <database name>];
ATTACH 'md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6';
USE sample_data;
1、使用最强学习镜像
-《2023-PostgreSQL Docker镜像学习环境 ARM64版, 已集成热门插件和工具》
-《2023-PostgreSQL Docker镜像学习环境 AMD64版, 已集成热门插件和工具》
2、你需要1个github账号
3、进入容器
docker exec -ti pg bash
su - postgres
1、启动duckdb 0.9.2 及以上版本
./duckdb
v0.9.2 3c695d7ba9
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
2、打开motherduck
D .open md:
1. Please open this link to login into your account: https://auth.motherduck.com/activate
2. Enter the following code: xxxx-xxxx
3、下载sdk并激活motherduck
- 在电脑中使用chrome浏览器打开 : https://auth.motherduck.com/activate
- 输入 : xxxx-xxxx
- 根据提示注册motherduck账号, 我这里关联到1个github账号.
成功. 你以后可以在启动duckdb前执行export motherduck_token=...., 避免每次.open md:都要做一次认证.
Token successfully retrieved ✅
You can store it as an environment variable to avoid having to log in again:
$ export motherduck_token='xx.....xxx'
4、列出的是在motherduckl里面的数据库
D show databases;
┌───────────────┐
│ database_name │
│ varchar │
├───────────────┤
│ my_db │
│ sample_data │
└───────────────┘
5、查询存储在motherduck服务里的数据
D SELECT
> passenger_count,
> avg(total_amount)
> -- reading from shared sample database
> FROM sample_data.nyc.taxi
> GROUP BY passenger_count
> ORDER by passenger_count;
┌─────────────────┬────────────────────┐
│ passenger_count │ avg(total_amount) │
│ double │ double │
├─────────────────┼────────────────────┤
│ 0.0 │ 20.271222848458628 │
│ 1.0 │ 21.225624974276688 │
│ 2.0 │ 24.09675238862258 │
│ 3.0 │ 23.21117216536006 │
│ 4.0 │ 24.267935907713383 │
│ 5.0 │ 21.02734475294877 │
│ 6.0 │ 21.519300095288067 │
│ 7.0 │ 76.26999999999998 │
│ 8.0 │ 61.56470588235294 │
│ 9.0 │ 75.68666666666667 │
│ │ 29.30373481034481 │
├─────────────────┴────────────────────┤
│ 11 rows 2 columns │
└──────────────────────────────────────┘
6、查询存储在s3里的数据
D SET s3_region='us-east-1';
D .timer on
D SELECT
> passenger_count,
> avg(total_amount)
> -- reading from AWS S3 parquet files
> FROM read_parquet('s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet')
> GROUP BY passenger_count
> ORDER by passenger_count;
┌─────────────────┬────────────────────┐
│ passenger_count │ avg(total_amount) │
│ double │ double │
├─────────────────┼────────────────────┤
│ 0.0 │ 20.271222848458628 │
│ 1.0 │ 21.225624974276013 │
│ 2.0 │ 24.096752388622424 │
│ 3.0 │ 23.21117216536007 │
│ 4.0 │ 24.267935907713316 │
│ 5.0 │ 21.02734475294876 │
│ 6.0 │ 21.519300095288088 │
│ 7.0 │ 76.27000000000001 │
│ 8.0 │ 61.56470588235294 │
│ 9.0 │ 75.68666666666667 │
│ │ 29.30373481034313 │
├─────────────────┴────────────────────┤
│ 11 rows 2 columns │
└──────────────────────────────────────┘
Run Time (s): real 1.709 user 1.025378 sys 0.000000
D select count(*) FROM read_parquet('s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet');
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 3252717 │
└──────────────┘
Run Time (s): real 1.361 user 0.726456 sys 0.000000
7、从explain 结果来看使用的motherduck的算力而非本地算力(L)表示本地运行, (R)表示远程即motherduck端运行.:
D explain SELECT
> passenger_count,
> avg(total_amount)
> FROM sample_data.nyc.taxi
> GROUP BY passenger_count
> ORDER by passenger_count;
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ DOWNLOAD_SOURCE (L) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ bridge_id: 1 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ BATCH_DOWNLOAD_SINK (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ bridge_id: 1 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ ORDERS: │
│ taxi.passenger_count ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ #0 │
│ avg(#1) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ passenger_count │
│ total_amount │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ taxi │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ passenger_count │
│ total_amount │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 3252717 │
└───────────────────────────┘
Run Time (s): real 0.633 user 0.314237 sys 0.002464
D explain SELECT
> passenger_count,
> avg(total_amount)
> -- reading from AWS S3 parquet files
> FROM read_parquet('s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet')
> GROUP BY passenger_count
> ORDER by passenger_count;
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ DOWNLOAD_SOURCE (L) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ bridge_id: 1 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ BATCH_DOWNLOAD_SINK (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ bridge_id: 1 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ ORDERS: │
│ read_parquet │
│ .passenger_count ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ #0 │
│ avg(#1) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ passenger_count │
│ total_amount │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ READ_PARQUET (R) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ passenger_count │
│ total_amount │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 3252717 │
└───────────────────────────┘
Run Time (s): real 1.071 user 0.287674 sys 0.000563
8、暂时不支持远程操作的explain analyze
D explain analyze SELECT
> passenger_count,
> avg(total_amount)
> -- reading from AWS S3 parquet files
> FROM read_parquet('s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet')
> GROUP BY passenger_count
> ORDER by passenger_count;
Run Time (s): real 0.558 user 0.007176 sys 0.002299
Error: Not implemented Error: Explain analyze is not yet supported on remote operations
D explain analyze SELECT
> passenger_count,
> avg(total_amount)
> FROM sample_data.nyc.taxi
> GROUP BY passenger_count
> ORDER by passenger_count;
Run Time (s): real 0.001 user 0.000730 sys 0.000000
Error: Not implemented Error: Explain analyze is not yet supported on remote operations
9、将s3里的数据导入另一个motherduck本地表
D create table main.tbl as select * from read_parquet('s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet');
Run Time (s): real 3.592 user 0.021703 sys 0.005232
D show tables;
┌─────────┐
│ name │
│ varchar │
├─────────┤
│ tbl │
└─────────┘
Run Time (s): real 0.034 user 0.040085 sys 0.003168
D select count(*) from main.tbl;
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 3252717 │
└──────────────┘
Run Time (s): real 0.554 user 0.299526 sys 0.000653
10、观察数据结构
D DESCRIBE main.tbl;
┌───────────────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ int32 │
├───────────────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤
│ VendorID │ BIGINT │ YES │ │ │ │
│ tpep_pickup_datetime │ TIMESTAMP │ YES │ │ │ │
│ tpep_dropoff_datetime │ TIMESTAMP │ YES │ │ │ │
│ passenger_count │ DOUBLE │ YES │ │ │ │
│ trip_distance │ DOUBLE │ YES │ │ │ │
│ RatecodeID │ DOUBLE │ YES │ │ │ │
│ store_and_fwd_flag │ VARCHAR │ YES │ │ │ │
│ PULocationID │ BIGINT │ YES │ │ │ │
│ DOLocationID │ BIGINT │ YES │ │ │ │
│ payment_type │ BIGINT │ YES │ │ │ │
│ fare_amount │ DOUBLE │ YES │ │ │ │
│ extra │ DOUBLE │ YES │ │ │ │
│ mta_tax │ DOUBLE │ YES │ │ │ │
│ tip_amount │ DOUBLE │ YES │ │ │ │
│ tolls_amount │ DOUBLE │ YES │ │ │ │
│ improvement_surcharge │ DOUBLE │ YES │ │ │ │
│ total_amount │ DOUBLE │ YES │ │ │ │
│ congestion_surcharge │ DOUBLE │ YES │ │ │ │
│ airport_fee │ DOUBLE │ YES │ │ │ │
├───────────────────────┴─────────────┴─────────┴─────────┴─────────┴───────┤
│ 19 rows 6 columns │
└───────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 0.025 user 0.037545 sys 0.005881
11、观察数据特征
D SUMMARIZE main.tbl;
┌──────────────────────┬─────────────┬─────────────────────┬─────────────────────┬───────────────┬───┬────────────────────┬────────────────────┬─────────┬─────────────────┐
│ column_name │ column_type │ min │ max │ approx_unique │ … │ q50 │ q75 │ count │ null_percentage │
│ varchar │ varchar │ varchar │ varchar │ varchar │ │ varchar │ varchar │ int64 │ varchar │
├──────────────────────┼─────────────┼─────────────────────┼─────────────────────┼───────────────┼───┼────────────────────┼────────────────────┼─────────┼─────────────────┤
│ VendorID │ BIGINT │ 1 │ 6 │ 3 │ … │ 2 │ 2 │ 3252717 │ 0.0% │
│ tpep_pickup_datetime │ TIMESTAMP │ 2002-12-31 23:03:33 │ 2022-12-01 23:55:39 │ 1629458 │ … │ │ │ 3252717 │ 0.0% │
│ tpep_dropoff_datet… │ TIMESTAMP │ 2003-01-01 13:50:43 │ 2022-12-02 00:07:25 │ 1605341 │ … │ │ │ 3252717 │ 0.0% │
│ passenger_count │ DOUBLE │ 0.0 │ 9.0 │ 10 │ … │ 1.0 │ 1.0 │ 3252717 │ 3.75% │
│ trip_distance │ DOUBLE │ 0.0 │ 305756.36 │ 4465 │ … │ 1.881695154430698 │ 3.5818849316889634 │ 3252717 │ 0.0% │
│ RatecodeID │ DOUBLE │ 1.0 │ 99.0 │ 7 │ … │ 1.0 │ 1.0 │ 3252717 │ 3.75% │
│ store_and_fwd_flag │ VARCHAR │ N │ Y │ 2 │ … │ │ │ 3252717 │ 3.75% │
│ PULocationID │ BIGINT │ 1 │ 265 │ 261 │ … │ 162 │ 234 │ 3252717 │ 0.0% │
│ DOLocationID │ BIGINT │ 1 │ 265 │ 262 │ … │ 162 │ 234 │ 3252717 │ 0.0% │
│ payment_type │ BIGINT │ 0 │ 5 │ 6 │ … │ 1 │ 1 │ 3252717 │ 0.0% │
│ fare_amount │ DOUBLE │ -1274.0 │ 1274.0 │ 7625 │ … │ 10.504850022300305 │ 17.11010696527587 │ 3252717 │ 0.0% │
│ extra │ DOUBLE │ -5.5 │ 14.35 │ 65 │ … │ 0.5 │ 1.0 │ 3252717 │ 0.0% │
│ mta_tax │ DOUBLE │ -0.5 │ 16.55 │ 6 │ … │ 0.5 │ 0.5 │ 3252717 │ 0.0% │
│ tip_amount │ DOUBLE │ -81.6 │ 333.0 │ 3658 │ … │ 2.215828224322078 │ 3.5040940837137833 │ 3252717 │ 0.0% │
│ tolls_amount │ DOUBLE │ -58.25 │ 655.55 │ 762 │ … │ 0.0 │ 0.0 │ 3252717 │ 0.0% │
│ improvement_surcha… │ DOUBLE │ -0.3 │ 1.0 │ 4 │ … │ 0.3 │ 0.3 │ 3252717 │ 0.0% │
│ total_amount │ DOUBLE │ -1277.8 │ 1277.8 │ 13439 │ … │ 16.33454854410054 │ 23.749735030885194 │ 3252717 │ 0.0% │
│ congestion_surcharge │ DOUBLE │ -2.5 │ 2.5 │ 5 │ … │ 2.5 │ 2.5 │ 3252717 │ 3.75% │
│ airport_fee │ DOUBLE │ -1.25 │ 1.25 │ 3 │ … │ 0.0 │ 0.0 │ 3252717 │ 3.75% │
├──────────────────────┴─────────────┴─────────────────────┴─────────────────────┴───────────────┴───┴────────────────────┴────────────────────┴─────────┴─────────────────┤
│ 19 rows 12 columns (9 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 1.462 user 1.246896 sys 0.000000
12、将s3里的数据拷贝到本地parquet文件内, 这个耗时就是真实的全量数据网络传输
D copy (select * from read_parquet('s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet')) to './tbl.parquet';
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 34.359 user 37.874342 sys 0.983531
D select count(*) from read_parquet( './tbl.parquet' );
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 3252717 │
└──────────────┘
Run Time (s): real 0.006 user 0.007341 sys 0.002252
注意这里的本地显得很快, 可能是由于我的客户端到motherduck服务端之间网络特别慢, 而不是motherduck的算力差劲.
