Postgres Pro AXE : Documentation: 1: 29.5. Adding a Parquet File to an S3 Storage : Postgres Professional

29.5. Adding a Parquet File to an S3 Storage
Prev	Up	Chapter 29. S3 Storages	Home	Next

29.5. Adding a Parquet File to an S3 Storage #

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

To prepare the environment for this scenario:

Note

If you already have a Parquet file 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.

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.24.
```
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.25.

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.26.

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.27.

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

Place a Parquet file to the S3 storage or create a Parquet file by exporting the data from a heap table.
Example 29.28.
```
  COPY my_table TO 's3://bucket/s3_parquet/data/data.parquet';
  
```

To configure this scenario:

Create an analytical table from the Parquet file.

Postgres Pro AXE uses analytical tables to register the data in the metadata catalog.

Example 29.29.

  SELECT metastore.add_table(
      'analytic_table_from_parquet',
      's3parquet',
      's3://bucket/s3_parquet/data/data.parquet',
      ''
  );

Create a shared directory.
The shared directory specifies the path to the Parquet file to be added to the analytical table.
Example 29.30.
```
  SELECT metastore.add_folder(
      'src_folder',
      's3parquet',
      'data/'
  );
  
```
Add the Parquet file to the analytical table.
When you add the file, it is registered in the metadata catalog.
Example 29.31.
```
  SELECT metastore.add_files(
      'analytic_table_from_parquet',
      'src_folder/data.parquet'
  );
  
```
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.32.
```
  SELECT metastore.create_view('analytic_table_from_parquet');
  SELECT * FROM analytic_table_from_parquet;
  
```

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

epub pdf