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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 零售连锁, 制作业的工厂等场景中, 普遍数字化率较低, 通常存在这些问题:数据离线, 例如每天盘点时上传, 未实现实时汇总到数据库中.数据格式多, 例如excel, csv, txt, 甚至纸质手抄.让我们一起来思考一下, 如何使用较少的投入实现数据汇总分析?

作者

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      : 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?

参考

202303/20230308_01.md 《PolarDB-PG | PostgreSQL + duckdb_fdw + 阿里云OSS 实现高效低价的海量数据冷热存储分离》
202212/20221209_02.md 《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP》
202209/20220924_01.md 《用duckdb_fdw加速PostgreSQL分析计算, 提速40倍, 真香.》
202010/20201022_01.md 《PostgreSQL 牛逼的分析型功能 - 列存储、向量计算 FDW - DuckDB_fdw - 无数据库服务式本地lib库+本地存储》
202306/20230624_01.md 《使用DuckDB分析高中生联考成绩excel(xlst)数据, 文理选课分析》
202211/20221124_02.md 《DuckDB 0.6.0 支持 csv 并行读取功能, 提升查询性能》
202210/20221026_03.md 《DuckDB COPY 数据导入导出 - 支持csv, parquet格式, 支持CODEC设置压缩》
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
12天前
|
关系型数据库 Serverless 分布式数据库
【公测】PolarDB PostgreSQL版Serverless功能免费使用​!
【公测】PolarDB PostgreSQL版Serverless功能免费使用​,公测于2024年3月28日开始,持续三个月,公测期间可以免费使用!
|
12天前
|
存储 运维 数据可视化
具有中国特色的普及工厂数字化转型的新路子
具有中国特色的普及工厂数字化转型的新路子
26 1
|
12天前
|
数据采集 机器学习/深度学习 数据可视化
深入学习NumPy库在数据分析中的应用场景
深入学习NumPy库在数据分析中的应用场景
|
12天前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。
|
12天前
|
关系型数据库 Serverless 分布式数据库
PolarDB PostgreSQL版Serverless功能上线公测啦,公测期间免费使用!
Serverless数据库能够使得数据库集群资源随客户业务负载动态弹性扩缩,将客户从复杂的业务资源评估和运维工作中解放出来。PolarDB PostgreSQL版 Serverless提供了CPU、内存、存储、网络资源的实时弹性能力,构建计算与存储分离架构下的 PolarDB PostgreSQL版产品新形态。
|
1天前
|
监控 关系型数据库 分布式数据库
【PolarDB开源】PolarDB在电商场景的应用:应对高并发与数据一致性挑战
【5月更文挑战第26天】阿里云PolarDB是为电商解决高并发和数据一致性问题的云原生数据库。它采用读写分离、弹性扩展和分布式缓存策略应对高并发,通过全局时钟、分布式事务和数据复制保证数据一致性。在大型促销活动中,电商平台可提前扩容、启用读写分离、优化索引并设置监控告警来应对挑战。PolarDB助力电商构建高性能、高可用的数据处理系统,赢得市场优势。
12 1
|
5天前
|
关系型数据库 Serverless 分布式数据库
体验PolarDB PostgreSQL Serverless极致弹性
体验PolarDB PostgreSQL Serverless极致弹性
|
12天前
|
存储 Cloud Native 关系型数据库
PolarDB-X 是面向超高并发、海量存储和复杂查询场景设计的云原生分布式数据库系统
【5月更文挑战第14天】PolarDB-X 是面向超高并发、海量存储和复杂查询场景设计的云原生分布式数据库系统
121 2
|
12天前
|
关系型数据库 Serverless 分布式数据库
PolarDB PostgreSQL版Serverless技术原理解读
数据库是现代企业IT系统中非常重要的一部分。在创建数据库时,客户往往需要比较保守地去配置数据库集群的资源,包括CPU、内存、存储以及连接数等多种参数配置,以确保业务能够在波峰和波谷都能平稳运行。在这种情况下,客户购买的集群资源在业务波谷时期会被闲置,导致整体成本偏高;而在业务压力增长阶段,集群资源又应对不足。Serverless数据库可以很好地解决这个问题。它能够让数据库集群资源随客户业务负载动态弹性扩缩,将客户从复杂的业务资源评估和运维工作中解放出来。 本文描述PolarDB PostgreSQL版Serverless的构建中, 如何实现弹得快、弹得准、弹得稳、弹得广的几个关键技术点。
75920 7
PolarDB PostgreSQL版Serverless技术原理解读
|
12天前
|
机器学习/深度学习 数据可视化 算法
从零到精通:学习这些R语言必学包成为数据分析高手!
从零到精通:学习这些R语言必学包成为数据分析高手!
45 0

相关产品

  • 云原生数据库 PolarDB