作者
digoal
日期
2023-08-26
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
业务场景1 介绍: 零售连锁、工厂等数字化率较低场景的数据分析
零售连锁, 制作业的工厂等场景中, 普遍数字化率较低, 通常存在这些问题:
- 数据离线, 例如每天盘点时上传, 未实现实时汇总到数据库中.
- 数据格式多, 例如excel, csv, txt, 甚至纸质手抄.
让我们一起来思考一下, 如何使用较少的投入实现数据汇总分析?
实现和对照
传统方法 设计和实验
通过统一网点的IT应用, 数据治理实现格式统一.
使用高质量的VPN网络将网点和中心点连接起来, 网点数据实时上传中心数据库.
成本较高.
PolarDB|PG新方法1 设计和实验
1、在不破坏现有使用习惯的情况下, 依旧使用离线采集, 数据格式可以使用门槛较低的excel, csv. 数据上传到OSS. (OSS是对象存储, 存储非常廉价, 内网带宽几乎免费.)
2、使用duckdb_fdw或plpython3u读取oss内的数据文件.
3、使用pg|polardb进行汇总分析.
让我们实验阿里云云起实验来验证上面的设计是否可行, 例子:
1、打开云起实验室, 资源栏, 这个例子要用到oss实验资源.
https://developer.aliyun.com/adc/scenario/exp/f55dbfac77c0467a9d3cd95ff6697a31
得到资源如下:
AK ID : xxxxxx AK Secret : xxxxxx Endpoint外网域名|内网域名 : s3.oss-cn-shanghai.aliyuncs.com bucket : tekwvr20230826180728
2、使用duckdb生成excel格式的测试数据, 并写入OSS, 模拟连锁店、工厂的边缘端数据采集和上传行为.
进入容器
docker exec -ti pg bash
启动duckdb
su - postgres ./duckdb
加载连接oss和s3的httpfs插件, 加载解析excel文件的spatial插件:
install httpfs; load httpfs; install spatial; load spatial;
根据云起实验的资源信息来设置oss或s3连接配置:
set s3_access_key_id='xxxxxx'; -- AK ID set s3_secret_access_key='xxxxxx'; -- AK Secret set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com'; -- Endpoint外网域名|内网域名
随机生成100万条记录, 并导出到excel文件中. 包含id,c1,info三个字段.
COPY (select id, (random()*10000)::int as c1, md5(random()::text) as info from range(0,1000000) as t(id)) TO '/tmp/t1.XLSX' WITH (FORMAT GDAL, DRIVER 'XLSX');
下载ossutil 客户端, 用于下载和上传xlsx文件.
https://help.aliyun.com/zh/oss/developer-reference/upload-objects-6
cd /tmp wget https://gosspublic.alicdn.com/ossutil/1.7.16/ossutil-v1.7.16-linux-amd64.zip unzip ossutil-v1.7.16-linux-amd64.zip chmod -R 555 ossutil-v1.7.16-linux-amd64
配置ossutil工具的oss参数, 根据云起实验的资源信息来设置oss或s3连接配置:
root@cf68c33c8144:/tmp# ./ossutil-v1.7.16-linux-amd64/ossutil64 config The command creates a configuration file and stores credentials. Please enter the config file name,the file name can include path(default /root/.ossutilconfig, carriage return will use the default file. If you specified this option to other file, you should specify --config-file option to the file when you use other commands): No config file entered, will use the default config file /root/.ossutilconfig For the following settings, carriage return means skip the configuration. Please try "help config" to see the meaning of the settings Please enter language(CH/EN, default is:EN, the configuration will go into effect after the command successfully executed): Please enter accessKeyID:xxxxxx Please enter accessKeySecret:xxxxxx Please enter stsToken: Please enter endpoint:s3.oss-cn-shanghai.aliyuncs.com
将前面生成的excel文件上传到oss对象存储中.
root@cf68c33c8144:/tmp# ./ossutil-v1.7.16-linux-amd64/ossutil64 cp /tmp/t1.XLSX oss://tekwvr20230826180728/ Succeed: Total num: 1, size: 39,564,235. OK num: 1(upload 1 files). average speed 4742000(byte/s) 8.343042(s) elapsed
同样的, xlsx文件需要下载到本地后再加载进数据库. 模拟下载工厂、零售分店上传到oss的每日数据文件.
root@cf68c33c8144:/tmp# ./ossutil-v1.7.16-linux-amd64/ossutil64 cp oss://tekwvr20230826180728/t1.XLSX /tmp/t1_new.XLSX Succeed: Total num: 1, size: 39,564,235. OK num: 1(download 1 objects). average speed 15147000(byte/s) 2.612532(s) elapsed
在duckdb中可以查询excel文件
SELECT * FROM st_read('/tmp/t1_new.XLSX') limit 10;
2.1、使用duckdb_fdw读取oss的数据(模拟读取连锁店、工厂的边缘端上传到云端oss的数据), 并写入到本地数据库中. 完成数据汇总.
在postgresql|polardb中使用duckdb_fdw, 通过duckdb来读取xlsx(excel)文件内容.
创建插件
create extension duckdb_fdw ;
创建sesrver
CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database ':memory:'); alter server duckdb_server options ( keep_connections 'true');
加载读取excel文件的spatial插件
SELECT duckdb_execute('duckdb_server', $$ load spatial; $$);
创建视图
SELECT duckdb_execute('duckdb_server', $$ create or replace view v_t1_new as SELECT * FROM st_read('/tmp/t1_new.XLSX'); $$);
将duckdb的视图导入postgresql|polardb
IMPORT FOREIGN SCHEMA public limit to (v_t1_new) FROM SERVER duckdb_server INTO public;
可以在postgresql|polardb读取excel文件了
postgres=# select count(*) from v_t1_new ; count --------- 1000000 (1 row)
可以将数据加载进入postgresql|polardb
create table local_t1_new as select * from v_t1_new ; SELECT 1000000 postgres=# select count(*) from local_t1_new ; count --------- 1000000 (1 row)
查看excel和导入到postgresql|polardb中的数据是否一致:
postgres=# select * from v_t1_new limit 10; id | CAST((random() * 10000) AS INTEGER) | info ----+-------------------------------------+---------------------------------- 0 | 9379 | 65ca3162135590a9c49eaed527bb0717 1 | 2050 | 83ff8d7aa48ee3fb06439905f07171f8 2 | 600 | 2e5df489239dc8c715ea5240cb1a5b1b 3 | 7839 | 3ce8ab6d54f2a7c38a075cdcc853c2ae 4 | 881 | 4cc6d2e410f9b34f06a45b65ddbc055c 5 | 2643 | beeceabdba66d6d5f8f278f8af0dde5a 6 | 3516 | aef3ab5795eeb9b621926c0c04091d44 7 | 703 | b26a2038ae2b0d0f1355c212742445c7 8 | 9284 | 39271f49ad09da33add4f77adac37403 9 | 4260 | 8f19c05ef83c33fd2a7dc133ee2e0e93 (10 rows) postgres=# select * from local_t1_new limit 10; id | CAST((random() * 10000) AS INTEGER) | info ----+-------------------------------------+---------------------------------- 0 | 9379 | 65ca3162135590a9c49eaed527bb0717 1 | 2050 | 83ff8d7aa48ee3fb06439905f07171f8 2 | 600 | 2e5df489239dc8c715ea5240cb1a5b1b 3 | 7839 | 3ce8ab6d54f2a7c38a075cdcc853c2ae 4 | 881 | 4cc6d2e410f9b34f06a45b65ddbc055c 5 | 2643 | beeceabdba66d6d5f8f278f8af0dde5a 6 | 3516 | aef3ab5795eeb9b621926c0c04091d44 7 | 703 | b26a2038ae2b0d0f1355c212742445c7 8 | 9284 | 39271f49ad09da33add4f77adac37403 9 | 4260 | 8f19c05ef83c33fd2a7dc133ee2e0e93 (10 rows)
3、使用duckdb生成csv格式的测试数据, 并写入OSS, 模拟连锁店、工厂的边缘端数据采集和上传行为.
随机生成100万条记录, 并导出到oss的csv文件中. 包含id,c1,info,ts 四个字段.
COPY (select id, (random()*10000)::int c1, md5(random()::text) as info, now() as ts from range(0,1000000) as t(id)) TO 's3://tekwvr20230826180728/t2.csv' WITH (HEADER, DELIMITER ',');
3.1、使用duckdb_fdw读取oss的数据(模拟读取连锁店、工厂的边缘端上传到云端oss的数据), 并写入到本地数据库中. 完成数据汇总.
创建插件
create extension duckdb_fdw ;
创建sesrver
CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database ':memory:'); alter server duckdb_server options ( keep_connections 'true');
加载读取oss文件的httpfs插件
SELECT duckdb_execute('duckdb_server', $$ load httpfs; $$);
设置当前duckdb fdw对应的连接oss的配置
SELECT duckdb_execute('duckdb_server', $$ set s3_access_key_id='xxxxxx'; $$); SELECT duckdb_execute('duckdb_server', $$ set s3_secret_access_key='xxxxxx'; $$); SELECT duckdb_execute('duckdb_server', $$ set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com'; $$);
创建读取oss内容的视图
SELECT duckdb_execute('duckdb_server', $$ create or replace view v_t2 as SELECT * FROM 's3://tekwvr20230826180728/t2.csv'; $$);
将duckdb的视图导入postgresql|polardb
IMPORT FOREIGN SCHEMA public limit to (v_t2) FROM SERVER duckdb_server INTO public;
在postgresql|polardb可以读取存储在oss的csv文件了
postgres=# select count(*) from v_t2 ; count --------- 1000000 (1 row)
将oss的csv文件导入到postgresql|polardb
create table local_t2 as select * from v_t2 ; SELECT 1000000 postgres=# select count(*) from local_t2; count --------- 1000000 (1 row)
查看oss的csv文件和导入到postgresql|polardb中的数据是否一致:
postgres=# select * from v_t2 limit 10; id | CAST((random() * 10000) AS INTEGER) | info | ts ----+-------------------------------------+----------------------------------+------------------------- 0 | 9504 | b37a70d10249ec1c19e3ff36d3ae5d9c | 2023-08-26 10:12:52.712 1 | 3650 | 93ce8d8c9047d5d53c3f4c33bd76ec50 | 2023-08-26 10:12:52.712 2 | 3998 | 00ea04f7bfa038a1092d0fd10e814f76 | 2023-08-26 10:12:52.712 3 | 9354 | 70f54f82dab5c6eb6c921e683d02eca1 | 2023-08-26 10:12:52.712 4 | 7991 | 1d9422dc7633f63ec4dfbe7b4f10e2c3 | 2023-08-26 10:12:52.712 5 | 9652 | 872fc6c314f9f548dfedc3edea79ef70 | 2023-08-26 10:12:52.712 6 | 1791 | 2cdeae2af5e21c5dead86e5d79754d57 | 2023-08-26 10:12:52.712 7 | 8911 | 0cc5fb6556183173768a21f7ba31cec0 | 2023-08-26 10:12:52.712 8 | 9281 | 97f7b99b3e80dca4bdf932e231ceb9dc | 2023-08-26 10:12:52.712 9 | 218 | f8a61afc533da2791d6b11d597c31389 | 2023-08-26 10:12:52.712 (10 rows) postgres=# select * from local_t2 limit 10; id | CAST((random() * 10000) AS INTEGER) | info | ts ----+-------------------------------------+----------------------------------+------------------------- 0 | 9504 | b37a70d10249ec1c19e3ff36d3ae5d9c | 2023-08-26 10:12:52.712 1 | 3650 | 93ce8d8c9047d5d53c3f4c33bd76ec50 | 2023-08-26 10:12:52.712 2 | 3998 | 00ea04f7bfa038a1092d0fd10e814f76 | 2023-08-26 10:12:52.712 3 | 9354 | 70f54f82dab5c6eb6c921e683d02eca1 | 2023-08-26 10:12:52.712 4 | 7991 | 1d9422dc7633f63ec4dfbe7b4f10e2c3 | 2023-08-26 10:12:52.712 5 | 9652 | 872fc6c314f9f548dfedc3edea79ef70 | 2023-08-26 10:12:52.712 6 | 1791 | 2cdeae2af5e21c5dead86e5d79754d57 | 2023-08-26 10:12:52.712 7 | 8911 | 0cc5fb6556183173768a21f7ba31cec0 | 2023-08-26 10:12:52.712 8 | 9281 | 97f7b99b3e80dca4bdf932e231ceb9dc | 2023-08-26 10:12:52.712 9 | 218 | f8a61afc533da2791d6b11d597c31389 | 2023-08-26 10:12:52.712 (10 rows)
4、分析汇总数据.
窗口函数、聚合函数、分析函数通常用于数据分析.
对照
使用Polardb|PG提供的方法成本很低, 性能好, 同时几乎不破坏各个网点的用户使用习惯.
知识点
oss
fdw
duckdb_fdw
gdal: https://duckdb.org/docs/archive/0.8.1/extensions/spatial
plpython3u
parquet 格式
窗口函数
分析函数
思考
这种架构还适合什么应用场景?
使用oss低廉的存储、随时随地可访问, 打破各个应用之间的数据孤岛?
教学场景, 通过oss共享数据, 例如企业脱敏后的数据, 提供给学生进行数据分析和实验.
如果数据文件非常多, 如何提升读取效率? 是否可以通过通配符一次性读取多个文件?
为什么要将数据导入集中存储分析?
parquet为什么更适合作为书记分析的文件格式?
在PostgreSQL|polardb中除了使用duckdb_fdw导入oss的数据, 还有什么方法? 例如plpython3u?
