如何从命令行使用 BigQuery REST API? [英] How can I use the BigQuery REST API from the command line?

问题描述

尝试向 BigQuery REST API 之一发出简单的 GET 请求会出现如下错误:

Attempting to make a plain GET request to one of the BigQuery REST APIs gives an error that looks like this:

curl https://www.googleapis.com/bigquery/v2/projects/$PROJECT_ID/jobs/$JOBID

输出:

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "required",
    "message": "Login Required",
    "locationType": "header",
    "location": "Authorization",
  ...

从命令行调用其中一个 REST API(例如 query 或 insert API)的正确方法是什么?API 参考 有一个试试这个 API"，但这些示例并不能直接转换为您可以从命令行运行的内容.

What is the correct way to invoke one of the REST APIs from the command-line, such as the query or insert APIs? The API reference has a "Try this API", but the examples don't translate directly to something you can run from the command-line.

推荐答案

作为免责声明，从命令行工作时，使用 bq 工具通常就足够了，或者用于更复杂的使用在这种情况下，BigQuery 客户端库支持使用多种语言的 BigQuery 进行编程.然而，有时向 REST API 发出简单请求以查看某些 API 如何在低级别工作仍然很有用.

As a disclaimer, when working from the command-line, using the bq tool will usually be sufficient, or for more complex use cases, the BigQuery client libraries enable programming with BigQuery from multiple languages. It can still be useful sometimes to make plain requests to the REST APIs to see how certain APIs work at a low level, however.

首先，确保您安装了 Google Cloud SDK.这应该包括 gcloud 和 bq 命令行工具.如果您还没有，请从终端运行以下命令来授权您的帐户:

First, make sure that you have installed the Google Cloud SDK. This should include the gcloud and bq command-line tools. If you haven't already, authorize your account by running this command from your terminal:

gcloud auth login

这应该会提示您登录，然后为您提供一个可以粘贴到终端中的访问代码.(确切的过程可能会随着时间的推移而改变).

This should prompt you to log in and then give you an access code that you can paste into your terminal. (The exact process may change over time).

现在让我们尝试使用 BigQuery REST API 进行查询，调用 jobs.query 方法.使用您自己的项目名称修改此脚本，您可以在 Google Cloud Console 中找到该名称，然后粘贴脚本到您的终端:

Now let's try a query using the BigQuery REST API, calling the jobs.query method. Modify this script with your own project name, which you can find from the Google Cloud Console, then paste the script into your terminal:

PROJECT="YOUR_PROJECT_NAME"
QUERY=""SELECT 1 AS x, 'foo' AS y;""
REQUEST="{"kind":"bigquery#queryRequest","useLegacySql":false,"query":$QUERY}"
echo $REQUEST | 
  curl -X POST -d @- -H "Content-Type: application/json" 
    -H "Authorization: Bearer $(gcloud auth print-access-token)" 
    https://www.googleapis.com/bigquery/v2/projects/$PROJECT/queries

如果成功，您应该会看到如下所示的输出:

If it worked, you should see output that looks like this:

{
 "kind": "bigquery#queryResponse",
 "schema": {
  "fields": [
   {
    "name": "x",
    "type": "INTEGER",
    "mode": "NULLABLE"
   },
   {
    "name": "y",
    "type": "STRING",
    "mode": "NULLABLE"
   }
  ]
 },
 "jobReference": {
  "projectId": "<your project ID>",
  "jobId": "<your job ID>"
 },
 "totalRows": "1",
 "rows": [
  {
   "f": [
    {
     "v": "1"
    },
    {
     "v": "foo"
    }
   ]
  }
 ],
 "totalBytesProcessed": "0",
 "jobComplete": true,
 "cacheHit": false
}

如果您还没有设置 bq 命令行工具，您可以从终端使用 bq init 来设置.完成后，您可以尝试使用它运行相同的查询:

If you haven't set up the bq command-line tool, you can use bq init from your terminal to do so. Once you have, you can try running the same query using it:

bq query --use_legacy_sql=False "SELECT 1 AS x, 'foo' AS y;"

您还可以通过传递 --apilog= 选项来查看 bq 工具发出的 REST API 请求:

You can also see the REST API requests that the bq tool makes by passing the --apilog= option:

bq --apilog= query --use_legacy_sql=False "SELECT [1, 2, 3] AS x;"

现在让我们尝试使用 jobs 的示例.插入 方法 而不是query API.运行此脚本，将 YOUR_PROJECT_NAME 替换为您的项目名称:

Now let's try an example using the jobs.insert method instead of the query API. Run this script, replacing YOUR_PROJECT_NAME with your project name:

PROJECT="YOUR_PROJECT_NAME"
QUERY=""SELECT 1 AS x, 'foo' AS y;""
REQUEST="{"configuration":{"query":{"useLegacySql":false,"query":${QUERY}}}}"
echo $REQUEST | 
curl -X POST -d @- -H "Content-Type: application/json" 
    -H "Authorization: Bearer $(gcloud auth print-access-token)" 
    https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs

与立即返回响应的 query API 不同，您将看到类似于以下内容的结果:

Unlike the query API, which returned a response immediately, you will see a result that looks similar to this:

{
 "kind": "bigquery#job",
 "etag": ""<etag string>"",
 "id": "<project name>:<job ID>",
 "selfLink": "https://www.googleapis.com/bigquery/v2/projects/<project name>/jobs/<job ID>",
 "jobReference": {
  "projectId": "<project name>",
  "jobId": "<job ID>"
 },
 "configuration": {
  "query": {
   "query": "SELECT 1 AS x, 'foo' AS y;",
   "destinationTable": {
    "projectId": "<project name>",
    "datasetId": "<anonymous dataset>",
    "tableId": "<anonymous table>"
   },
   "createDisposition": "CREATE_IF_NEEDED",
   "writeDisposition": "WRITE_TRUNCATE",
   "useLegacySql": false
  }
 },
 "status": {
  "state": "RUNNING"
 },
 "statistics": {
  "creationTime": "<timestamp millis>",
  "startTime": "<timestamp millis>"
 },
 "user_email": "<your email address>"
}

注意状态:

 "status": {
  "state": "RUNNING"
 },

如果您想立即查看作业，可以使用 jobs.get 方法.与之前类似，从终端运行此程序，使用上一步中输出的作业 ID:

If you want to check on the job now, you can use the jobs.get method. Similar to before, run this from your terminal, using the job ID from the output in the previous step:

PROJECT="YOUR_PROJECT_NAME"
JOB_ID="YOUR_JOB_ID"
curl -H "Authorization: Bearer $(gcloud auth print-access-token)" 
  https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs/$JOB_ID

如果查询完成，您将得到一个表示同样多的响应:

If the query is done, you'll get a response that indicates as much:

...
"status": {
 "state": "DONE"
},
...

最后，我们可以发出请求以获取查询结果，同样使用 REST API.

Finally, we can make a request to fetch the query results, also using the REST API.

curl -H "Authorization: Bearer $(gcloud auth print-access-token)" 
  https://www.googleapis.com/bigquery/v2/projects/$PROJECT/queries/$JOB_ID

输出将类似于我们使用上面的 jobs.query 方法:

The output will look similar to when we used the jobs.query method above:

{
 "kind": "bigquery#getQueryResultsResponse",
 "etag": ""<etag string>"",
 "schema": {
  "fields": [
   {
    "name": "x",
    "type": "INTEGER",
    "mode": "NULLABLE"
   },
   {
    "name": "y",
    "type": "STRING",
    "mode": "NULLABLE"
   }
  ]
 },
 "jobReference": {
  "projectId": "<project ID>",
  "jobId": "<job ID>"
 },
 "totalRows": "1",
 "rows": [
  {
   "f": [
    {
     "v": "1"
    },
    {
     "v": "foo"
    }
   ]
  }
 ],
 "totalBytesProcessed": "0",
 "jobComplete": true,
 "cacheHit": true
}

这篇关于如何从命令行使用 BigQuery REST API?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！