查询脚本
使用集合让一切井井有条
根据您的偏好保存内容并对其进行分类。
如未另行说明,那么本页面中的内容已根据知识共享署名 4.0 许可获得了许可,并且代码示例已根据 Apache 2.0 许可获得了许可。有关详情,请参阅 Google 开发者网站政策。Java 是 Oracle 和/或其关联公司的注册商标。
[[["易于理解","easyToUnderstand","thumb-up"],["解决了我的问题","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["很难理解","hardToUnderstand","thumb-down"],["信息或示例代码不正确","incorrectInformationOrSampleCode","thumb-down"],["没有我需要的信息/示例","missingTheInformationSamplesINeed","thumb-down"],["翻译问题","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],[],[[["\u003cp\u003eThis page demonstrates how to run a query script in BigQuery using Java and Python client libraries.\u003c/p\u003e\n"],["\u003cp\u003eThe provided code samples show how to declare variables, build arrays, and perform a query to find common names between the top 100 names from a specific year and Shakespeare's plays.\u003c/p\u003e\n"],["\u003cp\u003eThe examples showcase the use of \u003ccode\u003eBigQuery\u003c/code\u003e client objects and configurations for running scripts and obtaining the results.\u003c/p\u003e\n"],["\u003cp\u003eAuthentication for BigQuery is necessary and requires setting up Application Default Credentials, as detailed in the linked documentation.\u003c/p\u003e\n"],["\u003cp\u003eThe scripts create multiple child jobs that can be retrieved and whose outcomes can be printed, as demonstrated in the Java and Python samples.\u003c/p\u003e\n"]]],[],null,["# Query script\n\nRun a query script.\n\nCode sample\n-----------\n\n### Java\n\n\nBefore trying this sample, follow the Java setup instructions in the\n[BigQuery quickstart using\nclient libraries](/bigquery/docs/quickstarts/quickstart-client-libraries).\n\n\nFor more information, see the\n[BigQuery Java API\nreference documentation](/java/docs/reference/google-cloud-bigquery/latest/overview).\n\n\nTo authenticate to BigQuery, set up Application Default Credentials.\nFor more information, see\n\n[Set up authentication for client libraries](/bigquery/docs/authentication#client-libs).\n\n import com.google.api.gax.paging.https://cloud.google.com/java/docs/reference/gax/latest/com.google.api.gax.paging.Page.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Job.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.JobInfo.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html;\n\n // Sample to run query script.\n public class QueryScript {\n\n public static void main(String[] args) {\n String script =\n \"-- Declare a variable to hold names as an array.\\n\"\n + \"DECLARE top_names ARRAY\u003cSTRING\u003e;\\n\"\n + \"-- Build an array of the top 100 names from the year 2017.\\n\"\n + \"SET top_names = (\\n\"\n + \" SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)\\n\"\n + \" FROM `bigquery-public-data`.usa_names.usa_1910_current\\n\"\n + \" WHERE year = 2017\\n\"\n + \");\\n\"\n + \"-- Which names appear as words in Shakespeare's plays?\\n\"\n + \"SELECT\\n\"\n + \" name AS shakespeare_name\\n\"\n + \"FROM UNNEST(top_names) AS name\\n\"\n + \"WHERE name IN (\\n\"\n + \" SELECT word\\n\"\n + \" FROM `bigquery-public-data`.samples.shakespeare\\n\"\n + \");\";\n queryScript(script);\n }\n\n public static void queryScript(String script) {\n try {\n // Initialize client that will be used to send requests. This client only needs to be created\n // once, and can be reused for multiple requests.\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html bigquery = https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html.getDefaultInstance().getService();\n\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html queryConfig = https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html.newBuilder(script).build();\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Job.html createJob = bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html#com_google_cloud_bigquery_BigQuery_create_com_google_cloud_bigquery_DatasetInfo_com_google_cloud_bigquery_BigQuery_DatasetOption____(JobInfo.of(queryConfig));\n // Wait for the whole script to finish.\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.JobInfo.html jobInfo = createJob.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Job.html#com_google_cloud_bigquery_Job_waitFor_com_google_cloud_bigquery_BigQueryRetryConfig_com_google_cloud_RetryOption____();\n String parentJobId = jobInfo.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.JobInfo.html#com_google_cloud_bigquery_JobInfo_getJobId__().getJob();\n\n // Fetch jobs created by the SQL script.\n Page\u003cJob\u003e childJobs = bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html#com_google_cloud_bigquery_BigQuery_listJobs_com_google_cloud_bigquery_BigQuery_JobListOption____(BigQuery.JobListOption.parentJobId(parentJobId));\n childJobs\n .iterateAll()\n .forEach(job -\u003e System.out.printf(\"Child Job Id: \", job.getJobId().getJob()));\n\n System.out.println(\"Query script performed successfully.\");\n } catch (https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html | InterruptedException e) {\n System.out.println(\"Query not performed \\n\" + e.toString());\n }\n }\n }\n\n### Python\n\n\nBefore trying this sample, follow the Python setup instructions in the\n[BigQuery quickstart using\nclient libraries](/bigquery/docs/quickstarts/quickstart-client-libraries).\n\n\nFor more information, see the\n[BigQuery Python API\nreference documentation](/python/docs/reference/bigquery/latest).\n\n\nTo authenticate to BigQuery, set up Application Default Credentials.\nFor more information, see\n\n[Set up authentication for client libraries](/bigquery/docs/authentication#client-libs).\n\n\n from google.cloud import https://cloud.google.com/python/docs/reference/bigquery/latest/\n\n # Construct a BigQuery client object.\n client = https://cloud.google.com/python/docs/reference/bigquery/latest/.https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client.html()\n\n # Run a SQL script.\n sql_script = \"\"\"\n -- Declare a variable to hold names as an array.\n DECLARE top_names ARRAY\u003cSTRING\u003e;\n\n -- Build an array of the top 100 names from the year 2017.\n SET top_names = (\n SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)\n FROM `bigquery-public-data.usa_names.usa_1910_2013`\n WHERE year = 2000\n );\n\n -- Which names appear as words in Shakespeare's plays?\n SELECT\n name AS shakespeare_name\n FROM UNNEST(top_names) AS name\n WHERE name IN (\n SELECT word\n FROM `bigquery-public-data.samples.shakespeare`\n );\n \"\"\"\n parent_job = client.https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client.html(sql_script)\n\n # Wait for the whole script to finish.\n rows_iterable = parent_job.result()\n print(\"Script created {} child jobs.\".format(parent_job.num_child_jobs))\n\n # Fetch result rows for the final sub-job in the script.\n rows = list(rows_iterable)\n print(\n \"{} of the top 100 names from year 2000 also appear in Shakespeare's works.\".format(\n len(rows)\n )\n )\n\n # Fetch jobs created by the SQL script.\n child_jobs_iterable = client.https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client.html#google_cloud_bigquery_client_Client_list_jobs(parent_job=parent_job)\n for child_job in child_jobs_iterable:\n child_rows = list(child_job.result())\n print(\n \"Child job with ID {} produced {} row(s).\".format(\n child_job.job_id, len(child_rows)\n )\n )\n\nWhat's next\n-----------\n\n\nTo search and filter code samples for other Google Cloud products, see the\n[Google Cloud sample browser](/docs/samples?product=bigquery)."]]