Sign in to your Google Cloud account. If you're new to
Google Cloud,
create an account to evaluate how our products perform in
real-world scenarios. New customers also get $300 in free credits to
run, test, and deploy workloads.
In the Google Cloud console, on the project selector page,
select or create a Google Cloud project.
Wenn Sie die Abrechnung für das Google Cloud -Projekt, das Sie in dieser Anleitung verwenden, nicht aktivieren, arbeiten Sie mit Daten in der BigQuery-Sandbox. Mit der BigQuery-Sandbox können Sie BigQuery mit einer begrenzten Anzahl von BigQuery-Features kostenlos nutzen.
At the bottom of the Google Cloud console, a
Cloud Shell
session starts and displays a command-line prompt. Cloud Shell is a shell environment
with the Google Cloud CLI
already installed and with values already set for
your current project. It can take a few seconds for the session to initialize.
Öffentliches Dataset untersuchen
BigQuery bietet mehrere Beispieltabellen im Dataset bigquery-public-data.samples, die Sie abfragen können. In dieser Anleitung führen Sie Abfragen für die Tabelle shakespeare aus, die einen Eintrag für jedes Wort in jedem Shakespeare-Stück enthält.
Sehen Sie sich die Tabelle shakespeare im Dataset samples an:
bqshowbigquery-public-data:samples.shakespeare
Die entsprechende Ausgabe sieht etwa so aus: Einige Spalten werden weggelassen, um die Ausgabe zu vereinfachen.
Last modified Schema Total Rows Total Bytes
----------------- ------------------------------------ ------------ ------------
14 Mar 17:16:45 |- word: string (required) 164656 6432064
|- word_count: integer (required)
|- corpus: string (required)
|- corpus_date: integer (required)
Öffentliches Dataset abfragen
Führen Sie mit dem Befehl bq query SQL-Abfragen für Daten aus.
Finden Sie heraus, wie oft der Teilstring raisin in Shakespeares Werken vorkommt:
bqquery--use_legacy_sql=false\'SELECT word, SUM(word_count) AS count FROM `bigquery-public-data.samples.shakespeare` WHERE word LIKE "%raisin%" GROUP BY word;'
Suchen Sie in Shakespeares Werken den Teilstring huzzah:
bqquery--use_legacy_sql=false\'SELECT word FROM `bigquery-public-data.samples.shakespeare` WHERE word = "huzzah";'
Da der Teilstring in den Werken von Shakespeare nicht vorkommt, werden keine Ergebnisse zurückgegeben.
Bereinigen
Löschen Sie das Google Cloud -Projekt mit den Ressourcen, damit Ihrem Google Cloud -Konto die auf dieser Seite verwendeten Ressourcen nicht in Rechnung gestellt werden.
Projekt löschen
Wenn Sie das öffentliche Dataset mit der BigQuery-Sandbox abgefragt haben, ist die Abrechnung für Ihr Projekt nicht aktiviert.
Am einfachsten vermeiden Sie weitere Kosten durch Löschen des für die Anleitung erstellten Projekts.
So löschen Sie das Projekt:
In the Google Cloud console, go to the Manage resources page.
[[["Leicht verständlich","easyToUnderstand","thumb-up"],["Mein Problem wurde gelöst","solvedMyProblem","thumb-up"],["Sonstiges","otherUp","thumb-up"]],[["Schwer verständlich","hardToUnderstand","thumb-down"],["Informationen oder Beispielcode falsch","incorrectInformationOrSampleCode","thumb-down"],["Benötigte Informationen/Beispiele nicht gefunden","missingTheInformationSamplesINeed","thumb-down"],["Problem mit der Übersetzung","translationIssue","thumb-down"],["Sonstiges","otherDown","thumb-down"]],["Zuletzt aktualisiert: 2025-08-17 (UTC)."],[[["\u003cp\u003eThis tutorial guides users on how to examine and query a public dataset using the \u003ccode\u003ebq\u003c/code\u003e command-line tool within the Google Cloud environment, specifically focusing on the \u003ccode\u003eshakespeare\u003c/code\u003e table in the \u003ccode\u003ebigquery-public-data.samples\u003c/code\u003e dataset.\u003c/p\u003e\n"],["\u003cp\u003eUsers can test the functionality within a limited environment in the BigQuery sandbox without incurring charges, as long as billing is not enabled for their project, allowing users to work with a limited set of features.\u003c/p\u003e\n"],["\u003cp\u003eThe tutorial demonstrates how to use the \u003ccode\u003ebq show\u003c/code\u003e command to examine the structure of the \u003ccode\u003eshakespeare\u003c/code\u003e table, and the \u003ccode\u003ebq query\u003c/code\u003e command to run SQL queries to search for specific words or substrings, such as "raisin" and "huzzah".\u003c/p\u003e\n"],["\u003cp\u003eThe guide instructs users on how to clean up resources, including deleting the Google Cloud project to avoid ongoing charges, with specific caveats about potential data loss when deleting a project.\u003c/p\u003e\n"],["\u003cp\u003eAdditional resources are provided to extend the learning experience, such as further documentation on using the \u003ccode\u003ebq\u003c/code\u003e tool, the BigQuery sandbox, public datasets, loading data, querying data, pricing and quotas.\u003c/p\u003e\n"]]],[],null,["# Query a public dataset with the bq tool\n=======================================\n\nLearn how to examine and query a public dataset with the bq command-line tool.\n\n*** ** * ** ***\n\nTo follow step-by-step guidance for this task directly in the\nGoogle Cloud console, click **Guide me**:\n\n[Guide me](https://console.cloud.google.com/freetrial?redirectPath=/?walkthrough_id=bigquery--query-public-dataset-bq)\n\n*** ** * ** ***\n\nBefore you begin\n----------------\n\n- Sign in to your Google Cloud account. If you're new to Google Cloud, [create an account](https://console.cloud.google.com/freetrial) to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.\n-\n [Install](/sdk/docs/install) the Google Cloud CLI.\n\n- If you're using an external identity provider (IdP), you must first\n [sign in to the gcloud CLI with your federated identity](/iam/docs/workforce-log-in-gcloud).\n\n-\n To [initialize](/sdk/docs/initializing) the gcloud CLI, run the following command:\n\n ```bash\n gcloud init\n ```\n- [Create or select a Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects).\n\n | **Note**: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.\n - Create a Google Cloud project:\n\n ```\n gcloud projects create PROJECT_ID\n ```\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with a name for the Google Cloud project you are creating.\n - Select the Google Cloud project that you created:\n\n ```\n gcloud config set project PROJECT_ID\n ```\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with your Google Cloud project name.\n\n-\n [Install](/sdk/docs/install) the Google Cloud CLI.\n\n- If you're using an external identity provider (IdP), you must first\n [sign in to the gcloud CLI with your federated identity](/iam/docs/workforce-log-in-gcloud).\n\n-\n To [initialize](/sdk/docs/initializing) the gcloud CLI, run the following command:\n\n ```bash\n gcloud init\n ```\n- [Create or select a Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects).\n\n | **Note**: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.\n - Create a Google Cloud project:\n\n ```\n gcloud projects create PROJECT_ID\n ```\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with a name for the Google Cloud project you are creating.\n - Select the Google Cloud project that you created:\n\n ```\n gcloud config set project PROJECT_ID\n ```\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with your Google Cloud project name.\n\n1.\n [Verify that billing is enabled for your Google Cloud project](/billing/docs/how-to/verify-billing-enabled#confirm_billing_is_enabled_on_a_project).\n\n2. If you don't enable billing for the Google Cloud project that you use in this tutorial, then you will work with data in the BigQuery sandbox. The BigQuery sandbox lets you learn BigQuery with a limited set of BigQuery features at no charge.\n3. Ensure that the BigQuery API is enabled.\n\n [Enable the API](https://console.cloud.google.com/flows/enableapi?apiid=bigquery)\n\n If you created a new project, the BigQuery API is automatically\n enabled.\n4. In the Google Cloud console, activate Cloud Shell.\n\n [Activate Cloud Shell](https://console.cloud.google.com/?cloudshell=true)\n\n\n At the bottom of the Google Cloud console, a\n [Cloud Shell](/shell/docs/how-cloud-shell-works)\n session starts and displays a command-line prompt. Cloud Shell is a shell environment\n with the Google Cloud CLI\n already installed and with values already set for\n your current project. It can take a few seconds for the session to initialize.\n\n\u003cbr /\u003e\n\nView details about a public dataset\n-----------------------------------\n\nBigQuery offers several sample tables in the\n`bigquery-public-data.samples` dataset that you can query. In this tutorial,\nyou run queries on the `shakespeare` table, which contains an entry for every\nword in every Shakespeare play.\n\nView details about the `shakespeare` table in the `samples` dataset: \n\n bq show bigquery-public-data:samples.shakespeare\n\nThe output is similar to the following. Some columns are omitted to simplify the\noutput. \n\n Last modified Schema Total Rows Total Bytes\n ----------------- ------------------------------------ ------------ ------------\n 14 Mar 17:16:45 |- word: string (required) 164656 6432064\n |- word_count: integer (required)\n |- corpus: string (required)\n |- corpus_date: integer (required)\n\nQuery a public dataset\n----------------------\n\nUse the `bq query` command to run SQL queries on data.\n\n1. If you launched Cloud Shell from the documentation, enter the\n following command to set your project ID. This prevents you from having to\n specify the project ID in each CLI command.\n\n gcloud config set project \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with your project ID.\n2. Determine how many times the substring `raisin` appears in Shakespeare's\n works:\n\n bq query --use_legacy_sql=false \\\n 'SELECT\n word,\n SUM(word_count) AS count\n FROM\n bigquery-public-data.samples.shakespeare\n WHERE\n word LIKE \"%raisin%\"\n GROUP BY\n word'\n\n The output is similar to the following: \n\n +---------------+-------+\n | word | count |\n +---------------+-------+\n | praising | 8 |\n | Praising | 4 |\n | raising | 5 |\n | dispraising | 2 |\n | dispraisingly | 1 |\n | raisins | 1 |\n +---------------+-------+\n\n3. Search for the substring `huzzah` in Shakespeare's works:\n\n bq query --use_legacy_sql=false \\\n 'SELECT\n word\n FROM\n bigquery-public-data.samples.shakespeare\n WHERE\n word = \"huzzah\"'\n\n Because the substring doesn't appear in Shakespeare's works, no results are\n returned.\n\nClean up\n--------\n\n\nTo avoid incurring charges to your Google Cloud account for\nthe resources used on this page, delete the Google Cloud project with the\nresources.\n\n### Delete the project\n\nIf you used the [BigQuery sandbox](/bigquery/docs/sandbox) to query the public dataset, then billing is not enabled for your project, and you don't need to delete it.\n\n\u003cbr /\u003e\n\n\nThe easiest way to eliminate billing is to delete the project that you\ncreated for the tutorial.\n\nTo delete the project:\n\n| **Caution** : Deleting a project has the following effects:\n|\n| - **Everything in the project is deleted.** If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.\n| - **Custom project IDs are lost.** When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an `appspot.com` URL, delete selected resources inside the project instead of deleting the whole project.\n|\n|\n| If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects\n| can help you avoid exceeding project quota limits.\n1. In the Google Cloud console, go to the **Manage resources** page.\n\n [Go to Manage resources](https://console.cloud.google.com/iam-admin/projects)\n2. In the project list, select the project that you want to delete, and then click **Delete**.\n3. In the dialog, type the project ID, and then click **Shut down** to delete the project.\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\nWhat's next\n-----------\n\n- Learn more about [using the bq command-line tool](/bigquery/bq-command-line-tool).\n- Learn how to [load and query data using the bq command-line tool](/bigquery/docs/quickstarts/load-data-bq).\n- Learn about the [BigQuery sandbox](/bigquery/docs/sandbox).\n- Learn more about [BigQuery public datasets](/bigquery/public-data)."]]
