为什么数据库爱上对象存储这块洼地? pg_parquet 来了!

PostgreSQL rust写的插件(采用pgrx框架) pg_parquet 

https://github.com/CrunchyData/pg_parquet

pg_parquet是CrunchyData开源的PG插件, 用于将数据导入s3对象存储的parquet文件中, 或从s3对象存储的parquet文件中将数据导入到数据库表内. 并没有查询parquet数据/JOIN PG本地表等功能, 仅仅用于数据导入导出, 仅适合数据交换和归档场景.

说起来的话功能不如pg_duckdb/pg_mooncake齐全.

后期将集成到宇宙最强PostgreSQL学习镜像中:

《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 ARM64版, 已集成热门插件和工具》

《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 AMD64版, 已集成热门插件和工具》

安装 pg_parquet (rust写的PG插件基本上都是类似安装步骤)

cd /tmp  
git clone --depth 1 https://github.com/CrunchyData/pg_parquet/  
# rustup self uninstall # 如有必要, 可以删除rust老版本后重新安装rust        
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh  
. "/root/env"      
# grep pgrx Cargo.toml  查看依赖的pgrx版本  
cargo install --locked --version 0.12.6 cargo-pgrx     
cargo pgrx init   # 创建PGRX_HOME后立即 Ctrl^C 退出     
cargo pgrx init --pg14=`which pg_config`     
PGRX_IGNORE_RUST_VERSIONS=y cargo pgrx install --pg-config `which pg_config`  
echo "shared_preload_libraries = 'pg_parquet'" >> $PGDATA/postgresql.auto.conf  

Usage

There are mainly 3 things that you can do with pg_parquet:

1. You can export Postgres tables/queries to Parquet files,
2. You can ingest data from Parquet files to Postgres tables,
3. You can inspect the schema and metadata of Parquet files.

COPY to/from Parquet files from/to Postgres tables

You can use PostgreSQL's COPY command to read and write Parquet files. Below is an example of how to write a PostgreSQL table, with complex types, into a Parquet file and then to read the Parquet file content back into the same table.

-- create composite types  
CREATE TYPE product_item AS (id INT, name TEXT, price float4);  
CREATE TYPE product AS (id INT, name TEXT, items product_item[]);  
  -- create a table with complex types  
CREATE TABLE product_example (  
    id int,  
    product product,  
    products product[],  
    created_at TIMESTAMP,  
    updated_at TIMESTAMPTZ  
);  
  -- insert some rows into the table  
insert into product_example values (  
    1,  
    ROW(1, 'product 1', ARRAY[ROW(1, 'item 1', 1.0), ROW(2, 'item 2', 2.0), NULL]::product_item[])::product,  
    ARRAY[ROW(1, NULL, NULL)::product, NULL],  
    now(),  
    '2022-05-01 12:00:00-04'  
);  
  -- copy the table to a parquet file  
COPY product_example TO '/tmp/product_example.parquet' (format 'parquet', compression 'gzip');  
  -- show table  
SELECT * FROM product_example;  
  -- copy the parquet file to the table  
COPY product_example FROM '/tmp/product_example.parquet';  
  -- show table  
SELECT * FROM product_example;  

Inspect Parquet schema

You can call SELECT * FROM parquet.schema(<uri>) to discover the schema of the Parquet file at given uri.

SELECT * FROM parquet.schema('/tmp/product_example.parquet') LIMIT 10;  
             uri              |     name     | type_name  | type_length | repetition_type | num_children | converted_type | scale | precision | field_id | logical_type   
------------------------------+--------------+------------+-------------+-----------------+--------------+----------------+-------+-----------+----------+--------------  
 /tmp/product_example.parquet | arrow_schema |            |             |                 |            5 |                |       |           |          |   
 /tmp/product_example.parquet | id           | INT32      |             | OPTIONAL        |              |                |       |           |        0 |   
 /tmp/product_example.parquet | product      |            |             | OPTIONAL        |            3 |                |       |           |        1 |   
 /tmp/product_example.parquet | id           | INT32      |             | OPTIONAL        |              |                |       |           |        2 |   
 /tmp/product_example.parquet | name         | BYTE_ARRAY |             | OPTIONAL        |              | UTF8           |       |           |        3 | STRING  
 /tmp/product_example.parquet | items        |            |             | OPTIONAL        |            1 | LIST           |       |           |        4 | LIST  
 /tmp/product_example.parquet | list         |            |             | REPEATED        |            1 |                |       |           |          |   
 /tmp/product_example.parquet | items        |            |             | OPTIONAL        |            3 |                |       |           |        5 |   
 /tmp/product_example.parquet | id           | INT32      |             | OPTIONAL        |              |                |       |           |        6 |   
 /tmp/product_example.parquet | name         | BYTE_ARRAY |             | OPTIONAL        |              | UTF8           |       |           |        7 | STRING  
(10 rows)  

Inspect Parquet metadata

You can call SELECT * FROM parquet.metadata(<uri>) to discover the detailed metadata of the Parquet file, such as column statistics, at given uri.

SELECT uri, row_group_id, row_group_num_rows, row_group_num_columns, row_group_bytes, column_id, file_offset, num_values, path_in_schema, type_name FROM parquet.metadata('/tmp/product_example.parquet') LIMIT 1;  
             uri              | row_group_id | row_group_num_rows | row_group_num_columns | row_group_bytes | column_id | file_offset | num_values | path_in_schema | type_name   
------------------------------+--------------+--------------------+-----------------------+-----------------+-----------+-------------+------------+----------------+-----------  
 /tmp/product_example.parquet |            0 |                  1 |                    13 |             842 |         0 |           0 |          1 | id             | INT32  
(1 row)  

SELECT stats_null_count, stats_distinct_count, stats_min, stats_max, compression, encodings, index_page_offset, dictionary_page_offset, data_page_offset, total_compressed_size, total_uncompressed_size FROM parquet.metadata('/tmp/product_example.parquet') LIMIT 1;  
 stats_null_count | stats_distinct_count | stats_min | stats_max |    compression     |        encodings         | index_page_offset | dictionary_page_offset | data_page_offset | total_compressed_size | total_uncompressed_size   
------------------+----------------------+-----------+-----------+--------------------+--------------------------+-------------------+------------------------+------------------+-----------------------+-------------------------  
                0 |                      | 1         | 1         | GZIP(GzipLevel(6)) | PLAIN,RLE,RLE_DICTIONARY |                   |                      4 |               42 |                   101 |                      61  
(1 row)  

You can call SELECT * FROM parquet.file_metadata(<uri>) to discover file level metadata of the Parquet file, such as format version, at given uri.

SELECT * FROM parquet.file_metadata('/tmp/product_example.parquet')  
             uri              | created_by | num_rows | num_row_groups | format_version   
------------------------------+------------+----------+----------------+----------------  
 /tmp/product_example.parquet | pg_parquet |        1 |              1 | 1  
(1 row)  

You can call SELECT * FROM parquet.kv_metadata(<uri>) to query custom key-value metadata of the Parquet file at given uri.

SELECT uri, encode(key, 'escape') as key, encode(value, 'escape') as value FROM parquet.kv_metadata('/tmp/product_example.parquet');  
             uri              |     key      |    value  
------------------------------+--------------+---------------------  
 /tmp/product_example.parquet | ARROW:schema | /////5gIAAAQAAAA ...  
(1 row)  

Object Store Support

pg_parquet supports reading and writing Parquet files from/to S3 object store. Only the uris with s3:// scheme is supported.

The simplest way to configure object storage is by creating the standard ~/.aws/credentials and ~/.aws/config files:

$ cat ~/.aws/credentials  
[default]  
aws_access_key_id = AKIAIOSFODNN7EXAMPLE  
aws_secret_access_key = wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY  
  $ cat ~/.aws/config   
[default]  
region = eu-central-1  

Alternatively, you can use the following environment variables when starting postgres to configure the S3 client:

• AWS_ACCESS_KEY_ID: the access key ID of the AWS account
• AWS_SECRET_ACCESS_KEY: the secret access key of the AWS account
• AWS_REGION: the default region of the AWS account
• AWS_SHARED_CREDENTIALS_FILE: an alternative location for the credentials file
• AWS_CONFIG_FILE: an alternative location for the config file
• AWS_PROFILE: the name of the profile from the credentials and config file (default profile name is default)

[!NOTE]
To be able to write into a object store location, you need to grant parquet_object_store_write role to your current postgres user.
Similarly, to read from an object store location, you need to grant parquet_object_store_read role to your current postgres user.

Copy Options

pg_parquet supports the following options in the COPY TO command:

• format parquet: you need to specify this option to read or write Parquet files which does not end with .parquet[.<compression>] extension. (This is the only option that COPY FROM command supports.),
• row_group_size <int>: the number of rows in each row group while writing Parquet files. The default row group size is 122880,
• row_group_size_bytes <int>: the total byte size of rows in each row group while writing Parquet files. The default row group size bytes is row_group_size * 1024,
• compression <string>: the compression format to use while writing Parquet files. The supported compression formats are uncompressed, snappy, gzip, brotli, lz4, lz4raw and zstd. The default compression format is snappy. If not specified, the compression format is determined by the file extension.
• compression_level <int>: the compression level to use while writing Parquet files. The supported compression levels are only supported for gzip, zstd and brotli compression formats. The default compression level is 6 for gzip (0-10), 1 for zstd (1-22) and 1 for brotli (0-11).

Configuration

There is currently only one GUC parameter to enable/disable the pg_parquet:

• pg_parquet.enable_copy_hooks: you can set this parameter to on or off to enable or disable the pg_parquet extension. The default value is on.

Supported Types

pg_parquet has rich type support, including PostgreSQL's primitive, array, and composite types. Below is the table of the supported types in PostgreSQL and their corresponding Parquet types.

Nested Types

[!WARNING]

• (1) The numeric types with <= 38 precision is represented as FIXED_LEN_BYTE_ARRAY(16) with DECIMAL(128) logical type. The numeric types with > 38 precision is represented as BYTE_ARRAY with STRING logical type.
• (2) The date type is represented according to Unix epoch when writing to Parquet files. It is converted back according to PostgreSQL epoch when reading from Parquet files.
• (3) The timestamptz and timetz types are adjusted to UTC when writing to Parquet files. They are converted back with UTC timezone when reading from Parquet files.
• (4) The geometry type is represented as BYTE_ARRAY encoded as WKB when postgis extension is created. Otherwise, it is represented as BYTE_ARRAY with STRING logical type.
• (5) crunchy_map is dependent on functionality provided by Crunchy Bridge. The crunchy_map type is represented as GROUP with MAP logical type when crunchy_map extension is created. Otherwise, it is represented as BYTE_ARRAY with STRING logical type.

[!WARNING]
Any type that does not have a corresponding Parquet type will be represented, as a fallback mechanism, as BYTE_ARRAY with STRING logical type. e.g. enum

Postgres Support Matrix

pg_parquet supports the following PostgreSQL versions:

彩蛋:国产数据库周边生态

当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.

1、管控软件

鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.

• https://github.com/apecloud/kubeblocks

PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.

• https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.

• https://pigsty.cc/zh/

2、审计监控诊断优化

翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.

• https://www.dbdoctor.cn/

天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。

• https://bytebase.cc/docs/introduction/what-is-bytebase/

PawSQL, SQL优化和诊断产品.  

D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

• https://www.modb.pro/db/567140

3、国产数据库IDE

IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:

• https://www.deskui.com

4、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

• https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

• https://www.dsgdata.com/

公开课

如果你对PolarDB学习感兴趣可以阅读这个公开课系列:

•   国庆拿下这个国产信创数据库

• 国产数据库PolarDB公开课-1 架构解读
  
• 国产数据库PolarDB公开课-2 快速体验
  
• 国产数据库PolarDB公开课-3 安装部署
  
• 国产数据库PolarDB公开课-4 日常运维
• 国产数据库PolarDB公开课-5 特性解读与体验
  
• 国产数据库PolarDB公开课-6 使用PostgreSQL开源工具/插件
  
• 国产数据库PolarDB公开课-7 应用场景与实践
  
• 国产数据库PolarDB公开课-7.1 TPCH测试
  
• 国产数据库PolarDB公开课-8 生态
  
• 国产数据库PolarDB公开课-9 参与开源社区
  

除了PolarDB还非常值得关注的几款PG栈国产数据库:

• HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、
• IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、
• ProtonBase(云原生分布式数仓. https://protonbase.com/ )、
• 成都文武数据库（https://ww-it.cn）