Postgres Pro AXE : Documentation: 1: 24.3. Scenario: S3 Storage Connection : Postgres Professional

24.3. Scenario: S3 Storage Connection
Prev	Up	Chapter 24. Storages	Home	Next

24.3. Scenario: S3 Storage Connection #

For more information about storages, refer to Chapter 24.

Before performing this scenario:

To configure this scenario:

Set duckdb.unsafe_allow_execution_inside_functions and duckdb.convert_unsupported_numeric_to_double configuration parameters to true:

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:
```
  SELECT pg_reload_conf();
```

Create a simple secret.

Example 24.4.

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 an S3 storage.

Example 24.5.

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

Ensure that the S3 storage is configured correctly:

Create a heap table using the CREATE TABLE command.

Example 24.6.

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

Insert rows into the heap table using the INSERT command.

Example 24.7.

INSERT INTO heap_table_example VALUES (1, 'Item 1', 150.99, '2025-08-09 00:01:01.75');
INSERT INTO heap_table_example VALUES (2, 'Item 2', 300.99, '2025-08-10 00:01:01.75');

Create an analytical table from the heap table.

Example 24.8.

SELECT metastore.add_table('analytical_table_example', 's3_storage_example', 'public.heap_table_example', '');

Copy the OLAP data from the heap table to the analytical table.

Example 24.9.

SELECT metastore.copy_table('analytical_table_example', $$select * from heap_table_example$$);

Create a Postgres Pro view for the analytical table.
Example 24.10.
```
SELECT metastore.create_view('analytical_table_example');
```
Ensure that the values that you inserted into the heap table can be retrieved from the analytical table.
Example 24.11.
```
SELECT * FROM analytical_table_example;
```

Prev	Up	Next
24.2. Deleting a Storage (`metastore.remove_storage`)	Home	Chapter 25. Analytical Tables

epub pdf