如何在具有时区支持的BigQuery中设置计划查询(通过Python SDK) [英] How can I set up scheduled queries in BigQuery with timezone support (through Python SDK)

问题描述

在BigQuery用户界面中，我可以安排一个具有特定时区的查询(如下面的屏幕截图所示).

In the BigQuery UI, I can schedule a query with a specific timezone (as you can see in the screenshot below).

有了这些设置，我可以在正确的本地时间安排查询时间，但是当我尝试使用Python自动执行此过程时，看不到任何指定时区的选项(

With these settings, I'm able to schedule my query at the correct local time, but when I try to automate this process with Python I can't see any option to specify the timezone (https://cloud.google.com/bigquery/docs/scheduling-queries)

def create_scheduled_query(project_id, dataset_id, query_string, dest_table, write_disposition=WriteDisposition.WRITE_TRUNCATE):
    parent = client.project_path(project_id)

    transfer_config = google.protobuf.json_format.ParseDict(
        {
            "destination_dataset_id": dataset_id,
            "display_name": dest_table,
            "data_source_id": "scheduled_query",
            "params": {
                "query": query_string,
                "destination_table_name_template": dest_table,
                "write_disposition": write_disposition,
                "partitioning_field": "",
            },
            "schedule": "every day 06:00",  # How can add timezones here?
        },
        bigquery_datatransfer_v1.types.TransferConfig(),
    )

    response = client.create_transfer_config(
        parent, transfer_config, authorization_code=authorization_code
    )

    print("Created scheduled query '{}'".format(response.name))

有什么方法可以通过Python客户端完成此操作吗?谢谢！

Is there any way to accomplish this with the Python Client? Thanks!

推荐答案

仔细阅读文档后，我得出结论，无法在Python脚本中选择时区.

After carefully reviewing the documentation, I concluded that selection the time zone within your Python script is not possible.

在 API中和参考文档，您可以在 transfer_config 中找到计划查询的所有可能参数. 时间表 参数描述为:

Within the APIs and Reference document, you can find out all the possible arguments for you scheduled query in transfer_config. The schedule parameter is described as :

数据传输时间表.如果数据源不支持自定义时间表，这个应该是空的.如果为空，则默认值为数据源将被使用.指定的时间以UTC为单位.例子有效格式:每月15:30的第1，第3周一，1月6月13日15:15，以及季度00​​:00的第一个星期日.

Data transfer schedule. If the data source does not support a custom schedule, this should be empty. If it is empty, the default value for the data source will be used. The specified times are in UTC. Examples of valid format: 1st,3rd monday of month 15:30, every wed,fri of jan,jun 13:15, and first sunday of quarter 00:00.

因此，在控制台中，所选时区仅用于显示目的，因为时间已转换为 UTC时间，以便运行计划的查询.

Therefore, in the Console the time zone selected is used for display purpose only, since the time is converted to UTC Time in order to run your scheduled query.

此外，作为奖励信息，我想指出，在App Engine中，您可以使用 cron.yaml 选择您计划的工作的时区.您可以看到 时区 参数

In addition, just as a bonus information, I would like to point that in App Engine you can select the time zone for your scheduled job, using cron.yaml. You can see the timezone parameter here.

我希望它可以澄清您对此的疑问.

I hope it clarifies your doubt about it.

这篇关于如何在具有时区支持的BigQuery中设置计划查询(通过Python SDK)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！