Postgres Pro AXE : Documentation: 1: 29.4. Loading the Data from a Heap Table to an S3 Storage as Parquet Files : Postgres Professional

29.4. Loading the Data from a Heap Table to an S3 Storage as Parquet Files
Prev	Up	Chapter 29. S3 Storages	Home	Next

29.4. Loading the Data from a Heap Table to an S3 Storage as Parquet Files #

Consider a scenario where the data already exists in a heap table and must be loaded to an S3 storage as Parquet files for analytical queries.

To prepare the environment for this scenario:

Note

If you already have a heap table and an S3 storage configured, skip these steps.

You can ensure that an S3 storage is configured using the duckdb.show_simple_secret stored procedure. If it outputs the information about a simple secret, the S3 storage is configured.

Create a heap table and insert the test data into it.

Example 29.16.

  CREATE TABLE public.my_table (
      id int4 NULL,
      "name" text NULL,
      price numeric(10,2) NULL,
      created_at timestamp NULL
  );

  INSERT INTO public.my_table (id, "name", price, created_at)
  SELECT
      generate_series,
      'Article' || generate_series,
      ROUND((RANDOM() * 9990 + 10)::NUMERIC, 2),
      TIMESTAMP '2025-01-01 00:00:00'
      + (RANDOM() * (TIMESTAMP '2025-12-31 23:59:59'
      - TIMESTAMP '2025-01-01 00:00:00'))
  FROM generate_series(1, 100000);

Set the duckdb.unsafe_allow_execution_inside_functions and duckdb.convert_unsupported_numeric_to_double configuration parameters to true.
These parameters allow Postgres Pro AXE to work with S3 storages.
Example 29.17.
```
ALTER SYSTEM SET duckdb.unsafe_allow_execution_inside_functions TO true;
SET duckdb.convert_unsupported_numeric_to_double = true;
```
Reload the Postgres Pro AXE server to apply the updated configuration parameters:
```
SELECT pg_reload_conf();
```

Check that the configuration parameters are set:

Example 29.18.

SELECT name, setting, source
FROM pg_settings
WHERE name IN (
    'duckdb.unsafe_allow_execution_inside_functions',
    'duckdb.convert_unsupported_numeric_to_double'
);

Create a simple secret.

The secret stores the credentials for connecting Postgres Pro AXE to the S3 storage.

Example 29.19.

SELECT duckdb.create_simple_secret(
    type     := 'S3',
    key_id   := 'access_key_ID_example',
    secret   := 'simple_secret_example',
    region   := '',
    url_style := 'path',
    endpoint := 'hostname:port',
    validation := '',
    use_ssl  := 'false'
);

Create a bucket in the S3 storage.
The bucket stores Parquet files of analytical tables and is required to create an S3 storage.

Create an S3 storage.

Example 29.20.

  SELECT metastore.add_storage(
      's3_storage_example',
      's3://bucket/s3_storage_example/',
      's3://bucket/s3_storage_example/tmp/'
  );

To configure this scenario:

Create an analytical table from the heap table.
Postgres Pro AXE uses analytical tables to register the data in the metadata catalog.
Example 29.21.
```
  SELECT metastore.add_table(
      'analytic_my_table',
      's3_storage_example',
      'public.my_table',
      ''
  );
  
```
Copy the data from the heap table to the analytical table.
When you copy the data, it is loaded from the heap table to the S3 storage as Parquet files.
Example 29.22.
```
  SELECT metastore.copy_table(
      'analytic_my_table',
      'SELECT * FROM public.my_table'
  );
```
Postgres Pro AXE places Parquet files into a new subdirectory under the storage directory, named after the snapshot ID. Do not delete such directories.
Create a Postgres Pro view for the analytical table.
When you create the view, the data becomes available for analytical queries through this view.
Example 29.23.
```
  DROP VIEW IF EXISTS analytic_my_table;
  SELECT metastore.create_view('analytic_my_table');
  SELECT * FROM analytic_my_table;
```

Prev	Up	Next
29.3. Separating Read and Write Access to an S3 Storage	Home	29.5. Adding a Parquet File to an S3 Storage

epub pdf