Course Contents/05-零售连锁、工厂等数字化率较低场景的数据分析.md · PolarDB/whudb-course

沉浸式学习PostgreSQL|PolarDB 5: 零售连锁、工厂等数字化率较低场景的数据分析

作者

digoal

背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

《amd64 image》

ARM机器使用以下docker image:

《arm64 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      : LTAI5tHhtw5amwf7EjHUHrTd  
AK Secret     :  PRpYMgVs195EpANJLPrTBv9F1aajLQ  
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='LTAI5tHhtw5amwf7EjHUHrTd';               -- AK ID        
set s3_secret_access_key='PRpYMgVs195EpANJLPrTBv9F1aajLQ';     -- 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:LTAI5tHhtw5amwf7EjHUHrTd  
Please enter accessKeySecret:PRpYMgVs195EpANJLPrTBv9F1aajLQ  
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='LTAI5tHhtw5amwf7EjHUHrTd';       
$$);      
      
SELECT duckdb_execute('duckdb_server',       
$$      
set s3_secret_access_key='PRpYMgVs195EpANJLPrTBv9F1aajLQ';       
$$);      
      
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?

PolarDB / whudb-course

沉浸式学习PostgreSQL|PolarDB 5: 零售连锁、工厂等数字化率较低场景的数据分析

作者

背景

业务场景1 介绍: 零售连锁、工厂等数字化率较低场景的数据分析

实现和对照

传统方法设计和实验

PolarDB|PG新方法1 设计和实验

对照

知识点

思考

参考

《PolarDB-PG | PostgreSQL + duckdb_fdw + 阿里云OSS 实现高效低价的海量数据冷热存储分离》

《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP》

《用duckdb_fdw加速PostgreSQL分析计算, 提速40倍, 真香.》

《PostgreSQL 牛逼的分析型功能 - 列存储、向量计算 FDW - DuckDB_fdw - 无数据库服务式本地lib库+本地存储》

《使用DuckDB分析高中生联考成绩excel(xlst)数据, 文理选课分析》

《DuckDB 0.6.0 支持 csv 并行读取功能, 提升查询性能》

《DuckDB COPY 数据导入导出 - 支持csv, parquet格式, 支持CODEC设置压缩》

简介

发行版

贡献者

近期动态

PolarDB / whudb-course .gitee-modal { width: 500px !important; }

沉浸式学习PostgreSQL|PolarDB 5: 零售连锁、工厂等数字化率较低场景的数据分析

作者

背景

业务场景1 介绍: 零售连锁、工厂等数字化率较低场景的数据分析

实现和对照

传统方法 设计和实验

PolarDB|PG新方法1 设计和实验

对照

知识点

思考

参考

《PolarDB-PG | PostgreSQL + duckdb_fdw + 阿里云OSS 实现高效低价的海量数据冷热存储分离》

《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP》

《用duckdb_fdw加速PostgreSQL分析计算, 提速40倍, 真香.》

《PostgreSQL 牛逼的分析型功能 - 列存储、向量计算 FDW - DuckDB_fdw - 无数据库服务式本地lib库+本地存储》

《使用DuckDB分析高中生联考成绩excel(xlst)数据, 文理选课分析》

《DuckDB 0.6.0 支持 csv 并行读取功能, 提升查询性能》

《DuckDB COPY 数据导入导出 - 支持csv, parquet格式, 支持CODEC设置压缩》

简介

发行版

开源评估指数源自 OSS-Compass 评估体系，评估体系围绕以下三个维度对项目展开评估：

贡献者

近期动态

搜索帮助

PolarDB / whudb-course

传统方法设计和实验