求助：Teradata大型CTE子查询转BigQuery实现方案

First, let's map Teradata's system metadata structure to BigQuery's equivalent. Teradata's dbc.tables (which lists all tables across the system) lines up with BigQuery's INFORMATION_SCHEMA.TABLES view—this is the standard, SQL-compliant way to access table metadata in BigQuery.

Your original Teradata query:

select row_number() over (order by tablename) from dbc.tables

Here's the direct BigQuery translation:

SELECT ROW_NUMBER() OVER (ORDER BY table_name)
FROM INFORMATION_SCHEMA.TABLES

Quick Details to Keep in Mind: #

• BigQuery uses table_name (lowercase, underscore-separated) instead of Teradata's tablename—this follows standard SQL naming conventions for system views that BigQuery enforces.
• If you need to narrow results to a specific project or dataset (since INFORMATION_SCHEMA.TABLES defaults to all datasets you have access to), add a filter like this:

  SELECT ROW_NUMBER() OVER (ORDER BY table_name)
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_catalog = 'your_project_id'
    AND table_schema = 'your_dataset_id'
  

• For your cross join use case: BigQuery supports CROSS JOIN exactly like Teradata does. Just make sure both subqueries have clear aliases to avoid column name clashes, for example:

  WITH larger_subquery AS (
    -- Your existing larger subquery logic goes here
    SELECT ...
  ),
  numbered_table_list AS (
    SELECT ROW_NUMBER() OVER (ORDER BY table_name) AS table_row_num, table_name
    FROM INFORMATION_SCHEMA.TABLES
  )
  SELECT *
  FROM larger_subquery
  CROSS JOIN numbered_table_list
  

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