BigQuery标准SQL中批量加前缀/后缀重命名列以实现表连接

Great question! When dealing with 100+ columns in BigQuery, manually renaming each one with AS is obviously tedious—luckily, we can leverage INFORMATION_SCHEMA and dynamic SQL to batch-rename columns with prefixes/suffixes without writing repetitive code. Here's how to do it:

Step 1: Generate Batch Rename Clauses with INFORMATION_SCHEMA #

BigQuery's INFORMATION_SCHEMA.COLUMNS view lets you fetch all column names for a table. We can use STRING_AGG to concatenate these column names into a single SELECT clause with automatic prefixes/suffixes.

For example, to add a a_ prefix to all columns in my_project.my_dataset.table_a:

-- Generate the SELECT clause for table_a with a_ prefix
SELECT STRING_AGG(
  CONCAT('`', column_name, '` AS `a_', column_name, '`'),
  ', '
) AS select_clause_a
FROM `my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'table_a';

Run this query, and you'll get a string like:

col1 AS a_col1, col2 AS a_col2, ..., col100 AS a_col100

Repeat this for your second table (e.g., add a b_ prefix to table_b):

-- Generate the SELECT clause for table_b with b_ prefix
SELECT STRING_AGG(
  CONCAT('`', column_name, '` AS `b_', column_name, '`'),
  ', '
) AS select_clause_b
FROM `my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'table_b';

Step 2: Build and Run the Join Query #

Take the two generated select_clause strings and plug them into a join query. This ensures all columns have unique names before joining:

SELECT *
FROM (
  -- Paste select_clause_a here
  SELECT `col1` AS `a_col1`, `col2` AS `a_col2`, ..., `col100` AS `a_col100`
  FROM `my_project.my_dataset.table_a`
) a
JOIN (
  -- Paste select_clause_b here
  SELECT `col1` AS `b_col1`, `col2` AS `b_col2`, ..., `col100` AS `b_col100`
  FROM `my_project.my_dataset.table_b`
) b
ON a.a_id = b.b_id; -- Replace with your actual join condition

Step 3: (Advanced) Fully Automated Dynamic SQL #

If you want to skip copying/pasting the clauses, use EXECUTE IMMEDIATE to run the entire join dynamically. This is perfect if you need to run this regularly or want to avoid manual steps:

DECLARE select_a STRING;
DECLARE select_b STRING;
DECLARE join_sql STRING;

-- Fetch and format columns for table_a with a_ prefix
SET select_a = (
  SELECT STRING_AGG(
    CONCAT('`', column_name, '` AS `a_', column_name, '`'),
    ', '
  )
  FROM `my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'table_a'
);

-- Fetch and format columns for table_b with b_ prefix
SET select_b = (
  SELECT STRING_AGG(
    CONCAT('`', column_name, '` AS `b_', column_name, '`'),
    ', '
  )
  FROM `my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'table_b'
);

-- Build the full join query
SET join_sql = CONCAT(
  'SELECT * FROM (SELECT ', select_a, ' FROM `my_project.my_dataset.table_a`) a ',
  'JOIN (SELECT ', select_b, ' FROM `my_project.my_dataset.table_b`) b ',
  'ON a.a_id = b.b_id' -- Update this to your join condition
);

-- Execute the dynamic query
EXECUTE IMMEDIATE join_sql;

Key Notes: #

• Wrap column names in backticks ` to handle special characters (like spaces or reserved words) in column names.
• Replace my_project.my_dataset, table_a, table_b, and the join condition with your actual values.
• This approach works for both prefixes and suffixes—just adjust the CONCAT string (e.g., CONCAT('', column_name, 'AS', column_name, '_a') for a suffix).

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