Request a Demo
3.3. Analytical Tables
Prev UpChapter 3. AdministrationHome Next

3.3. Analytical Tables #

3.3.1. Creating an Analytical Table
3.3.2. Renaming an Analytical Table
3.3.3. Creating a Postgres Pro View From an Analytical Table
3.3.4. Deleting an Analytical Table

Analytical tables are sets of columns and rows with the OLAP data featuring a full history of data and table type updates.

Rows of analytical tables are stored as Parquet files in a storage. The metadata of analytical tables is stored in the pga_table metadata table.

3.3.1. Creating an Analytical Table #

Postgres Pro AXE provides the following ways of creating analytical tables:

3.3.1.1. Creating an Analytical Table from a Parquet File #

Execute the following query: 

    SELECT metastore.add_table('table_name', 'storage_name', 'Parquet_file_URI');

Where:

  • table_name: The unique analytical table name.

    The fully qualified name of the analytical table consists of the analytical schema and table name. It is generated in analytical_schema_name.analytical_table_name format.

  • storage_name: The name of the storage where Parquet files will be located.

  • Parquet_file_URI: The URI of the Parquet file from which the analytical table will be created.

    The URI must either start with file:// or s3://.

Example 3.18. 

    SELECT metastore.add_table('example_table', 'example_storage', 'file:///home/user/tmp/parquet_data/t.parquet');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Ensures that the source of metadata of the analytical table exists.

  3. Uses the Apache Arrow library to export information about columns from the Parquet file.

  4. Associates the data type of each column of the analytical table with a pgpro_metastore data type.

  5. Creates a new snapshot in the pga_snapshot metadata table, a new analytical table in the pga_table metadata table, and new columns in the pga_column metadata table.

3.3.1.2. Creating an Analytical Table From a Heap Table #

Execute the following query: 

    SELECT metastore.add_table('table_name', 'storage_name', 'heap_table_name');

Where:

  • table_name: The unique analytical table name.

    The fully qualified name of the analytical table consists of the analytical schema and table name. It is generated in analytical_schema_name.analytical_table_name format.

  • storage_name: The name of the storage where Parquet files will be located.

  • heap_table_name: The fully qualified heap table name starting with the schema name.

Example 3.19. 

    SELECT metastore.add_table('example_table', 'example_storage', 'public.example_heap_table');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Ensures that the source of metadata of the analytical table exists.

  3. Exports information about columns of the analytical table from the heap table by querying Postgres Pro service tables.

  4. Associates the data type of each column with a pgpro_metastore data type.

  5. Creates a new snapshot in the pga_snapshot metadata table, a new analytical table in the pga_table metadata table, and new columns in the pga_column metadata table.

3.3.2. Renaming an Analytical Table #

Execute the following query: 

  SELECT metastore.rename_table('schema_name.current_table_name' or 'current_table_name', 'new_table_name');

Where:

  • schema_name: The name of the analytical schema where the table is created.

    Optional parameter. If you do not specify it, the current schema is used.

  • current_table_name: The current analytical table name.

  • new_table_name: The new unique analytical table name.

Example 3.20. 

  SELECT metastore.rename_table('testtable', 'testtable1');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Updates the table_name value for the analytical table in the pga_table metadata table.

  3. Updates the view_sql value for the analytical table.

3.3.3. Creating a Postgres Pro View From an Analytical Table #

Execute the following query: 

  SELECT metastore.create_view('table_name', 'Postgres_Pro_schema_name');

Where:

  • table_name: The name of the analytical table from which the Postgres Pro view will be created.

  • Postgres_Pro_schema_name: The name of the Postgres Pro schema associated with the view.

    Optional parameter. If you do not specify it, the schema is selected automatically.

Example 3.21. 

  SELECT metastore.create_view('example_table');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Receives metadata of the analytical table and the path to the shared directory.

  3. Generates the CREATE VIEW query with the data source being the result of calling the read_parquet() function.

  4. Executes the CREATE VIEW query.

  5. Grants privileges to the Postgres Pro view.

  6. Creates a new snapshot in the pga_snapshot metadata table.

3.3.4. Deleting an Analytical Table #

Execute the following query: 

  SELECT metastore.remove_table('table_name');

Where table_name is the name of the analytical table that will be deleted.

Example 3.22. 

  SELECT metastore.remove_table('example_table');

Once the query is executed, pgpro_metastore performs the following actions:

  1. Verifies user privileges.

  2. Creates a new snapshot in the pga_snapshot metadata table and specifies for the analytical table and all its associated pgpro_metastore objects the end_snapshot value in the pga_table metadata table and other metadata tables.

Prev Up Next
3.2. Storages Home 3.4. Columns of Analytical Tables
epub pdf