如何根据自定义参数在表名称中使用类似 python 的字符串格式查询

Question

所以我有几个表格，其中包含每年的每种产品，表格如下： 2020product5, 2019product5, 2018product6等等。我在谷歌数据工作室中添加了两个自定义参数，分别命名为year和product_id，但无法在表名称本身中使用它们。我之前使用过参数化查询，但在类似的情况下，where product_id = @product_id但此设置仅在所有数据都在同一个表中时才有效，这不是我当前的情况。在 python 中，我使用类似的字符串格式化程序f"{year}product{product_id}"，但在这种情况下显然不起作用...使用 Bigquery 默认CONCAT和FORMAT函数没有帮助，因为两者都会抛出以下验证错误：Table-valued function not found: CONCAT at [1:15]那么，如何在基于自定义参数的表名称中使用类似 python 的字符串格式来查询 google data studio 中的 bigquery 表呢？

Answer 1

经过大量研究后，我（有点）解决了这个问题。事实证明，这是一个数据库级功能，可以动态查询模式级实体（例如表名）。BigQuery 不支持表名称内的格式设置，例如无法直接查询相关表（例如2020product5、2019product5、 ）。2018product6但是，它确实有一个 TABLE_SUFFIX 函数，允许您动态访问表，因为表名的更改位于表的末尾。（此功能还允许进行 dateweise 分区，并且许多使用 BQ 作为数据接收器的工具都可以利用此功能。因此，如果您使用 BQ 作为数据接收器，则很有可能您的原始数据源已经在这样做）。因此，像 ( product52020, product52019, product62018) 这样的表名称也可以动态访问，当然也可以使用以下命令从 Data Studio 访问：

SELECT * FROM `project_salsa_101.dashboards.product*` WHERE _table_Suffix = CONCAT(@product_id,@year) 

PS：使用 python 创建一个脏脚本，该脚本循环遍历产品和表，并复制并创建新的脚本，如下所示：（添加带有格式化字符串的脚本，因此对于任何有这种情况的人来说，只需付出一定的努力，它可能会很有用）

import itertools

credentials = service_account.Credentials.from_service_account_file(

    'project_salsa_101-bq-admin.json')

project_id = 'project_salsa_101'

schema = 'dashboards'

client = bigquery.Client(credentials= credentials,project=project_id)

for product_id, year in in itertools.product(product_ids, years): 

    df = client.query(f"""

            SELECT *  FROM `{project_id}.{schema}.{year}product{product_id}`

            """).result().to_dataframe()

    df.to_gbq(project_id = project_id,

                destination_table = f'{schema}.product{product_id}{year}',

                credentials = service_account.Credentials.from_service_account_file(

                    'credentials.json'),

                if_exists = 'replace')

    client.query(f"""

            DROP TABLE `{project_id}.{schema}.{year}product{product_id}`""").result()