HDFql表格相关技术咨询：异构行、数据追加及行遍历

1. Does HDFql support creating tables with rows containing different data types? #

Absolutely. HDFql lets you create tables (backed by HDF5 compound datasets) where each row combines columns of distinct data types. You just need to define a compound data type that encapsulates all your desired column types when creating the table.

For example, here's how to make a table with a string ID, integer age, and float score (each row will include all three different types):

-- Create a table named 'user_data' with heterogeneous column types
CREATE TABLE user_data (user_id VARCHAR(30), age INT, average_score FLOAT)

You can run this command via HDFql's API in your preferred language (Python, C/C++, Java, etc.) to set up the table.

2. How to append data to a table? #

Appending data is simple with HDFql's INSERT INTO statement. Since your row count is dynamic (1 to 2ⁿ, no fixed upper limit), you can insert rows one at a time as you generate them, or batch insert for better performance with large datasets.

Example: Dynamic single-row appending #

If you're generating data on the fly and don't know the total row count upfront, loop through your data and insert each row individually:

import HDFql

# Sample dynamic data (can grow to 2ⁿ rows)
dynamic_records = [("alice_123", 28, 91.2), ("bob_456", 34, 87.5), ...]

# Initialize HDFql and target the HDF5 file
HDFql.execute("USE FILE 'my_dataset.h5'")

for record in dynamic_records:
    # Use parameter binding to safely insert values
    HDFql.execute(f"INSERT INTO user_data VALUES ('{record[0]}', {record[1]}, {record[2]})")

Example: Batch appending (for large datasets) #

For bigger datasets, bind arrays of values and insert all rows in one go to optimize performance:

# Split dynamic data into separate arrays per column
user_ids = [r[0] for r in dynamic_records]
ages = [r[1] for r in dynamic_records]
scores = [r[2] for r in dynamic_records]

# Register arrays with HDFql and batch insert
HDFql.variable_register(user_ids)
HDFql.variable_register(ages)
HDFql.variable_register(scores)

HDFql.execute("INSERT INTO user_data VALUES FROM MEMORY ? ? ?")

3. How to iterate through rows in a table? #

To traverse rows, use HDFql's SELECT statement along with cursor operations to fetch each row sequentially. This works seamlessly even if your table has up to 2ⁿ rows.

Example: Iterate through all rows #

# Execute SELECT to retrieve all rows from the table
HDFql.execute("SELECT * FROM user_data")

# Fetch and process rows one by one until no more are left
while HDFql.cursor_next() == HDFql.SUCCESS:
    # Extract each column's value from the cursor
    user_id = HDFql.cursor_get_char()
    age = HDFql.cursor_get_int()
    score = HDFql.cursor_get_float()
    
    # Process the row (e.g., print, store in another structure)
    print(f"User: {user_id}, Age: {age}, Score: {score}")

You can also add a WHERE clause to the SELECT statement if you only need to iterate through filtered rows (e.g., SELECT * FROM user_data WHERE age > 30).

内容的提问来源于stack exchange，提问作者Johan