在BigQuery中用函数验证MySQL架构表存在性的报错问题

需求：编写一个带schema_name和table_name两个输入参数的BigQuery函数，通过EXTERNAL_QUERY查询MySQL的INFORMATION_SCHEMA.TABLES，验证指定架构下的表是否存在。

两种错误写法及原因分析 #

第一种写法及报错 #

CREATE OR REPLACE FUNCTION `dataset.function_name`(
  schema_name STRING,
  table_name STRING
)
RETURNS INT64
AS (
  (
    SELECT COUNT(DISTINCT TABLE_NAME)
    FROM EXTERNAL_QUERY("us.Cartografia_tool", 
    '''
      SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA = schema_name
        AND TABLE_NAME = table_name
    '''
    )
  )
);

报错信息：

Invalid table-valued function EXTERNAL_QUERY Failed to get query schema from MySQL server. Error: MysqlErrorCode(1054): Unknown column 'schema_name' in 'where clause'

错误原因：EXTERNAL_QUERY中的SQL语句直接在MySQL端执行，BigQuery函数的参数schema_name和table_name不会自动传递到MySQL上下文。MySQL会将这两个参数当作表的列名，而实际不存在这些列，因此抛出1054错误。

第二种写法及报错 #

CREATE OR REPLACE FUNCTION `dataset.function_name`(
  schema_name STRING,
  table_name STRING
)
RETURNS INT64
AS (
  (
    SELECT COUNT(DISTINCT TABLE_NAME)
    FROM EXTERNAL_QUERY(
      "us.Cartografia_tool",
      FORMAT(
        '''
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = "%s"
          AND TABLE_NAME = "%s"
        ''',
        schema_name,
        table_name
      )
    )
  )
);

报错信息：

Invalid table-valued function EXTERNAL_QUERY Connection argument in EXTERNAL_QUERY must be a literal string or query parameter

错误原因：BigQuery要求EXTERNAL_QUERY的第二个参数（待执行的SQL）必须是字面量字符串或查询参数，而FORMAT生成的动态拼接字符串不符合该要求，因此触发报错。

正确实现方法 #

通过EXTERNAL_QUERY的查询参数传递机制，将BigQuery函数的参数安全传递到MySQL查询中，以下是两种可行写法：

写法1：使用位置占位符 #

CREATE OR REPLACE FUNCTION `dataset.function_name`(
  schema_name STRING,
  table_name STRING
)
RETURNS INT64
AS (
  (
    SELECT COUNT(DISTINCT TABLE_NAME)
    FROM EXTERNAL_QUERY(
      "us.Cartografia_tool",
      """
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = ?
          AND TABLE_NAME = ?
      """,
      [schema_name, table_name]
    )
  )
);

• 用?作为MySQL查询的参数占位符
• 通过EXTERNAL_QUERY的第三个参数（数组），按顺序传递BigQuery函数的参数到MySQL端
• 查询返回1表示表存在，返回0表示不存在

写法2：使用命名参数 #

CREATE OR REPLACE FUNCTION `dataset.function_name`(
  schema_name STRING,
  table_name STRING
)
RETURNS INT64
AS (
  (
    SELECT COUNT(DISTINCT TABLE_NAME)
    FROM EXTERNAL_QUERY(
      "us.Cartografia_tool",
      """
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = @schema
          AND TABLE_NAME = @table
      """,
      STRUCT(schema_name AS schema, table_name AS table)
    )
  )
);

• 用@参数名作为MySQL查询的命名占位符
• 通过STRUCT构造命名参数集合，传递给EXTERNAL_QUERY
• 该写法可读性更强，参数顺序不影响结果

调用示例 #

SELECT `dataset.function_name`('your_mysql_schema', 'your_table_name');

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