列出BigQuery中的预定查询 [英] List Scheduled Queries in BigQuery

问题描述

我需要(以编程方式)分析BigQuery中的计划查询的详细信息(例如，哪些表已更新以及在SQL中访问了哪些表).我已经使用Apps脚本BigQuery.Tables.list()对BQ表/视图进行了类似的操作，但是找不到用于访问计划查询的API.

I have a need to (programatically) analyse the details of Scheduled Queries in BigQuery (eg which tables are updated and which tables accessed in the SQL). I have done something similar for the BQ Tables/Views using Apps Script BigQuery.Tables.list(), but I cannot find an API to access the Scheduled Queries.

UI可以列出它们，所以我认为这可以通过编程实现，例如通过REST API.有谁知道这是否可行，支持什么接口(App脚本，REST ...)以及可能的使用方法示例.

The UI is able to list them, so I feel this should be possible programmatically, e.g. via a REST API. Does anyone know if this is possible, what interface is supported (Apps Script, REST ...), and possibly an example of how to use it.

推荐答案

计划的查询是BigQuery的数据传输服务的一部分，因此您必须使用其API.特别是 projects.transferConfigs.list 方法.用scheduled_query填写dataSourceIds字段，用projects/PROJECT_ID填写parent.如评论中所述，如果您使用的是欧洲(欧洲)之类的区域性地理位置，而不是使用多区域性(欧盟或美国)，则应使用

Scheduled queries are part of BigQuery's Data Transfer Service so you have to use its API. In particular, the projects.transferConfigs.list method. Fill in the dataSourceIds field with scheduled_query and parent with projects/PROJECT_ID. As discussed in the comments, if you are using a regional location such as europe-west2 instead of a multi-regional one (EU or US) you should use projects.locations.transferConfigs.list instead. Now, parent resource will be in the form of projects/PROJECT_ID/locations/REGIONAL_LOCATION.

此外，对于其他传输，您可以使用

In addition, for other transfers you can get the corresponding dataSourceIds using the projects.dataSources.list method. That's how I got the scheduled_query one.

响应将是一组预定查询，例如:

Response will be an array of scheduled queries such as:

{
  "name": "projects/<PROJECT_NUMBER>/locations/us/transferConfigs/<TRANSFER_CONFIG_ID>",
  "destinationDatasetId": "<DATASET>",
  "displayName": "hacker-news",
  "updateTime": "2018-11-14T15:39:18.897911Z",
  "dataSourceId": "scheduled_query",
  "schedule": "every 24 hours",
  "nextRunTime": "2019-04-19T15:39:00Z",
  "params": {
    "write_disposition": "WRITE_APPEND",
    "query": "SELECT @run_time AS time,\n  title,\n  author,\n  text\nFROM `bigquery-public-data.hacker_news.stories`\nLIMIT\n  1000",
    "destination_table_name_template": "hacker_daily_news"
  },
  "state": "SUCCEEDED",
  "userId": "<USER_ID>",
  "datasetRegion": "us"
}

使用bash和curl的API调用示例:

Example of an API call with bash and curl:

#!/bin/bash

# parameter(s)
location=europe-west2

authToken="$(gcloud auth print-access-token)"
projectId=$(gcloud config get-value project 2>\dev\null)

# API call
scheduled_queries=$(curl  -H "Authorization: Bearer $authToken" \
https://bigquerydatatransfer.googleapis.com/v1/projects/$projectId/locations/$location/transferConfigs?dataSourceIds=scheduled_query)

# pretty print results
echo $scheduled_queries | python -m json.tool

这篇关于列出BigQuery中的预定查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！