如何创建含不同长度表及多symbol列的分区历史数据库

Hey there! Let's walk through how to build your date-partitioned historical database, handle those symbol columns, and adapt to tables of different lengths—all while making sure future data can be easily appended.

First, we'll build a partitioned table that organizes data by date, which makes time-range queries fast and simplifies adding new data later. I'll use examples for two common data warehouses: PostgreSQL (declarative partitioning) and BigQuery (auto-partitioning).

PostgreSQL Example #

Start with a parent table defined to partition by date:

-- Parent table with core schema
CREATE TABLE historical_data (
    date_col DATE NOT NULL,
    value NUMERIC,
    symbol_gbp VARCHAR(10),
    symbol_uk VARCHAR(10)
) PARTITION BY RANGE (date_col);

Next, create partitions to cover your existing data ranges. You can split into yearly, monthly, or daily partitions depending on your data volume:

-- Partition for table1's 2017 data
CREATE TABLE historical_data_2017 PARTITION OF historical_data
    FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

-- Partitions for table2's 2017-12 to 2023-01 data (yearly chunks for simplicity)
CREATE TABLE historical_data_2018 PARTITION OF historical_data
    FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');

CREATE TABLE historical_data_2019 PARTITION OF historical_data
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

-- Repeat for 2020, 2021, 2022, and a 2023 partition up to Jan 15
CREATE TABLE historical_data_2023 PARTITION OF historical_data
    FOR VALUES FROM ('2023-01-01') TO ('2023-01-16');

Now import your existing data—PostgreSQL will automatically route rows to the correct partition:

-- Import table1 data
INSERT INTO historical_data SELECT * FROM table1;

-- Import table2 data
INSERT INTO historical_data SELECT * FROM table2;

To support future appends, either pre-create partitions for upcoming dates or set up a script (using pg_cron for example) to auto-create partitions as needed.

BigQuery Example #

BigQuery simplifies auto-partitioning—no need to manually create partitions upfront:

CREATE OR REPLACE TABLE `your_project.your_dataset.historical_data` (
    date_col DATE NOT NULL,
    value NUMERIC,
    symbol_gbp STRING,
    symbol_uk STRING
)
PARTITION BY DATE(date_col) -- Auto-creates partitions for new dates
CLUSTER BY symbol_gbp, symbol_uk; -- Optional: Optimizes queries filtering by symbols

Appending data is as simple as running an INSERT or LOAD job—BigQuery will create new partitions automatically for any new dates in your data.

Since your current table2 has fixed values for symbol_gbp and symbol_uk, but these might change with real data, you don't need any special workarounds:

• Keep both columns in your partitioned table as-is. When real data comes in with varying values, just insert it normally—the table will handle different values without issues.
• If you frequently query by these symbols, add an index (PostgreSQL) or use clustering (BigQuery) as shown above to speed up those queries.

If you anticipate adding more symbol columns later, you could consider a flexible schema (like a JSON column for key-value symbol pairs), but for your current use case, keeping separate columns is cleaner and easier to query.

If by "different length tables" you mean tables with different schemas:

• If schemas are similar (e.g., table1 is missing one symbol column), fill missing fields with NULL when importing:

  INSERT INTO historical_data SELECT date_col, value, symbol_gbp, NULL AS symbol_uk FROM table1;
  

• If schemas are drastically different, create separate partitioned tables for each data source, then use a view to combine them for unified queries:

  CREATE VIEW combined_historical_data AS
  SELECT date_col, value, symbol_gbp, symbol_uk, 'table1' AS source FROM historical_data_table1
  UNION ALL
  SELECT date_col, value, symbol_gbp, symbol_uk, 'table2' AS source FROM historical_data_table2;
  

If you mean tables with different row counts (some have way more data than others):

• Adjust your partition granularity: Use daily partitions for high-volume tables, and monthly/yearly partitions for low-volume ones to avoid creating too many tiny partitions.

内容的提问来源于stack exchange，提问作者Alex R.