Script di query
Mantieni tutto organizzato con le raccolte
Salva e classifica i contenuti in base alle tue preferenze.
Esegui uno script di query.
Esempio di codice
Salvo quando diversamente specificato, i contenuti di questa pagina sono concessi in base alla licenza Creative Commons Attribution 4.0, mentre gli esempi di codice sono concessi in base alla licenza Apache 2.0. Per ulteriori dettagli, consulta le norme del sito di Google Developers. Java è un marchio registrato di Oracle e/o delle sue consociate.
[[["Facile da capire","easyToUnderstand","thumb-up"],["Il problema è stato risolto","solvedMyProblem","thumb-up"],["Altra","otherUp","thumb-up"]],[["Difficile da capire","hardToUnderstand","thumb-down"],["Informazioni o codice di esempio errati","incorrectInformationOrSampleCode","thumb-down"],["Mancano le informazioni o gli esempi di cui ho bisogno","missingTheInformationSamplesINeed","thumb-down"],["Problema di traduzione","translationIssue","thumb-down"],["Altra","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)."]]