Resources
Database Files Virtual Table
Resources
Database Files Virtual Table
Overview
The oxla_home_files virtual table lists all files associated with a specific table in the oxla home directory. This approach offers a more reliable way to retrieve data than simply scanning files directly.
Fields
| Field | Content | Type |
|---|---|---|
path | Absolute path from the oxla working directory | TEXT |
byte_size | Size of the file in bytes | BIGINT |
start_index | First index in the file, if applicable | NULLABLE TEXT |
end_index | Last index in the file, if applicable | NULLABLE TEXT |
row_count | Number of rows in the file | BIGINT |
batch_count | Number of batches the file is divided into | BIGINT |
table_id | ID of the related table | BIGINT |
namespace_id | ID of the related namespace | BIGINT |
database_id | ID of the related database | BIGINT |
Example Query
This example shows how to query the oxla_home_files table in an Oxla instance.
Scenario 1: Empty Result
- Run the
oxla_home_filesquery below.
SELECT * FROM oxla_internal.oxla_home_files;
- When the
oxla_home_filestable is empty, the query returns an empty result set.
path | byte_size | start_index | end_index | row_count | batch_count | table_id | namespace_id | database_id
------+-----------+-------------+-----------+-----------+-------------+----------+--------------+-------------
(0 rows)
Scenario 2: After Data Insertion
- Create and insert data to the table.
CREATE TABLE orders (
order_id INT,
order_date DATE,
total_amount INT,
shipping_address TEXT,
status TEXT
);
INSERT INTO orders
(order_id, order_date, total_amount, shipping_address, status)
VALUES
(1001, '2024-07-13', 150.75, '123 Main St, Anytown, USA', 'Shipped'),
(1002, '2024-07-12', 200.50, '456 Elm St, Othertown, USA', 'Delivered'),
(1003, '2024-07-12', 350.25, '789 Oak St, Anotherplace, USA', 'Processing'),
(1001, '2024-07-11', 100.00, '321 Pine St, Somewhere, USA', 'Cancelled'),
(1004, '2024-07-10', 500.00, '555 Maple St, Nowhere, USA', 'Pending');
- Run the
oxla_home_filesquery below.
SELECT * FROM oxla_internal.oxla_home_files;
- After inserting data into the table, the query lists the file metadata stored in Oxla.
path | byte_size | start_index | end_index | row_count | batch_count | table_id | namespace_id | database_id
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------+-----------+-----------+-------------+----------+--------------+-------------
s3://regional-providers-eu-central-1-dp/production/2jyd1o7D6CBCKyU2gWafDfEawmP/oxla_data/hack_on_oxla_non_empty_storage/0/0/16385/buffers/cluster-2jyd1jzvhgequov20igkxe4peyl-oxla-0/0 | 978 | | | 5 | 1 | 16385 | 0 | 0
(1 row)