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

30.1. Loading the Data from a Heap Table to a Local Storage as Parquet Files
Prev	Up	Chapter 30. Local Storages	Home	Next

30.1. Loading the Data from a Heap Table to a Local Storage as Parquet Files #

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

To prepare the environment for this scenario:

Note

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

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

Example 30.1.

  DROP TABLE IF EXISTS public.my_table;

  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);

Create directories for Parquet files and grant write access to the operating system user on behalf of whom the Postgres Pro DBMS runs.
These directories store Parquet files of analytical tables and are required to create a local storage.
Example 30.2.
```
  sudo -u postgres mkdir -p /tmp/my_storage /tmp/my_storage/tmp_mystorage
  sudo chown -R postgres:postgres /tmp/my_storage
```

Create a local storage.

Example 30.3.

  SELECT metastore.add_storage(
      'my_storage',
      'file:///tmp/my_storage/',
      'file:///tmp/my_storage/tmp_mystorage/'
  );

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 30.4.
```
  SELECT metastore.add_table(
      'analytic_my_table',
      'my_storage',
      '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 local storage as Parquet files.
Example 30.5.
```
  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 30.6.
```
  DROP VIEW IF EXISTS analytic_my_table;
  SELECT metastore.create_view('analytic_my_table');
  SELECT * FROM analytic_my_table;
```

Prev	Up	Next
Chapter 30. Local Storages	Home	30.2. Adding a Parquet File to a Local Storage

epub pdf