google-bigquery - 是否可以在 python 客户端中调用 BigQuery 程序？

Question

BigQuery 的脚本/程序刚刚推出测试版 - 是否可以使用 BigQuery python 客户端调用程序？

我试过了:

query = """CALL `myproject.dataset.procedure`()...."""
job = client.query(query, location="US",)
print(job.results())
print(job.ddl_operation_performed)

print(job._properties) but that didn't give me the result set from the procedure. Is it possible to get the results?

谢谢!

已编辑 - 我正在调用的存储过程

CREATE OR REPLACE PROCEDURE `Project.Dataset.Table`(IN country STRING, IN accessDate DATE, IN accessId, OUT saleExists INT64)
BEGIN
  IF EXISTS (SELECT 1 FROM dataset.table where purchaseCountry = country and purchaseDate=accessDate and customerId = accessId)
  THEN
  SET saleExists = (SELECT 1);
ELSE
  INSERT Dataset.MissingSalesTable (purchaseCountry, purchaseDate, customerId) VALUES (country, accessDate, accessId);
  SET saleExists = (SELECT 0);
END IF;
END;

Answer 1

如果在 CALL 命令后面加上 SELECT 语句，则可以将函数的返回值作为结果集。例如，我创建了以下存储过程:

BEGIN
  -- Build an array of the top 100 names from the year 2017.
DECLARE
  top_names ARRAY<STRING>;
SET
  top_names = (
  SELECT
    ARRAY_AGG(name
    ORDER BY
      number DESC
    LIMIT
      100)
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = 2017 );
  -- Which names appear as words in Shakespeare's plays?
SET
  top_shakespeare_names = (
  SELECT
    ARRAY_AGG(name)
  FROM
    UNNEST(top_names) AS name
  WHERE
    name IN (
    SELECT
      word
    FROM
      `bigquery-public-data.samples.shakespeare` ));
END

运行以下查询会将过程的返回值作为顶级结果集返回。

DECLARE top_shakespeare_names ARRAY<STRING> DEFAULT NULL;
CALL `my-project.test_dataset.top_names`(top_shakespeare_names);
SELECT top_shakespeare_names;

在 Python 中:

from google.cloud import bigquery

client = bigquery.Client()
query_string = """
DECLARE top_shakespeare_names ARRAY<STRING> DEFAULT NULL;
CALL `swast-scratch.test_dataset.top_names`(top_shakespeare_names);
SELECT top_shakespeare_names;
"""
query_job = client.query(query_string)
rows = list(query_job.result())
print(rows)

相关:如果存储过程中有 SELECT 语句，即使 SELECT 语句不是过程中的最后一条语句，也可以遍历作业来获取结果。

# TODO(developer): Import the client library.
# from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

# Run a SQL script.
sql_script = """
-- Declare a variable to hold names as an array.
DECLARE top_names ARRAY<STRING>;

-- Build an array of the top 100 names from the year 2017.
SET top_names = (
SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
FROM `bigquery-public-data.usa_names.usa_1910_2013`
WHERE year = 2000
);

-- Which names appear as words in Shakespeare's plays?
SELECT
name AS shakespeare_name
FROM UNNEST(top_names) AS name
WHERE name IN (
SELECT word
FROM `bigquery-public-data.samples.shakespeare`
);
"""
parent_job = client.query(sql_script)

# Wait for the whole script to finish.
rows_iterable = parent_job.result()
print("Script created {} child jobs.".format(parent_job.num_child_jobs))

# Fetch result rows for the final sub-job in the script.
rows = list(rows_iterable)
print("{} of the top 100 names from year 2000 also appear in Shakespeare's works.".format(len(rows)))

# Fetch jobs created by the SQL script.
child_jobs_iterable = client.list_jobs(parent_job=parent_job)
for child_job in child_jobs_iterable:
    child_rows = list(child_job.result())
    print("Child job with ID {} produced {} rows.".format(child_job.job_id, len(child_rows)))