Memberi nomor pada kumpulan hasil BigQuery

Jika Anda mencoba mengambil hasil kueri ke set data BigQuery yang lebih besar dari batas memori Workflows, Anda dapat menggunakan token halaman untuk membuat penomoran halaman pada hasil. Token halaman mewakili posisi dalam kumpulan hasil, dan ditampilkan saat hasil tambahan tersedia. Hal ini memungkinkan Anda melakukan loop pada satu halaman hasil.

BigQuery menghosting sejumlah set data publik yang tersedia untuk dikueri oleh masyarakat umum. Pada contoh berikut, Anda akan membuat kueri set data publik Data Nama AS untuk menentukan nama yang paling umum di AS antara tahun 1910 dan 2013.

YAML

# Use a page token to loop through a page of results at a time when
# querying a BigQuery dataset larger than the Workflows memory limit
# This workflow queries a public dataset to determine the most common
# names in the US between 1910 and 2013
main:
  params: [input]
  steps:
    - init:
        assign:
          - pageToken: null
    - startQuery:
        call: googleapis.bigquery.v2.jobs.insert
        args:
          projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
          body:
            configuration:
              query:
                useLegacySql: false
                # Remove LIMIT from the query to iterate through all results
                query: SELECT name, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name ORDER BY total DESC LIMIT 50
        result: query
    - getPage:
        call: googleapis.bigquery.v2.jobs.getQueryResults
        args:
          projectId: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
          jobId: ${query.jobReference.jobId}
          maxResults: 10
          pageToken: ${pageToken}
        result: page
    - processPage:
        for:
          value: row
          in: ${page.rows}
          steps:
            - processRow:
                call: sys.log
                args:
                  data: ${row}
    - checkIfDone:
        switch:
          - condition: ${"pageToken" in page and page.pageToken != ""}
            assign:
              - pageToken: ${page.pageToken}
            next: getPage

JSON

{
  "main": {
    "params": [
      "input"
    ],
    "steps": [
      {
        "init": {
          "assign": [
            {
              "pageToken": null
            }
          ]
        }
      },
      {
        "startQuery": {
          "call": "googleapis.bigquery.v2.jobs.insert",
          "args": {
            "projectId": "${sys.get_env(\"GOOGLE_CLOUD_PROJECT_ID\")}",
            "body": {
              "configuration": {
                "query": {
                  "useLegacySql": false,
                  "query": "SELECT name, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name ORDER BY total DESC LIMIT 50"
                }
              }
            }
          },
          "result": "query"
        }
      },
      {
        "getPage": {
          "call": "googleapis.bigquery.v2.jobs.getQueryResults",
          "args": {
            "projectId": "${sys.get_env(\"GOOGLE_CLOUD_PROJECT_ID\")}",
            "jobId": "${query.jobReference.jobId}",
            "maxResults": 10,
            "pageToken": "${pageToken}"
          },
          "result": "page"
        }
      },
      {
        "processPage": {
          "for": {
            "value": "row",
            "in": "${page.rows}",
            "steps": [
              {
                "processRow": {
                  "call": "sys.log",
                  "args": {
                    "data": "${row}"
                  }
                }
              }
            ]
          }
        }
      },
      {
        "checkIfDone": {
          "switch": [
            {
              "condition": "${\"pageToken\" in page and page.pageToken != \"\"}",
              "assign": [
                {
                  "pageToken": "${page.pageToken}"
                }
              ],
              "next": "getPage"
            }
          ]
        }
      }
    ]
  }
}

Langkah selanjutnya