Last active
March 24, 2021 12:16
-
-
Save gdcutting/bb16490f1ec7d44e7035b98e443dd075 to your computer and use it in GitHub Desktop.
Bigquery-cost-control.ipynb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "nbformat": 4, | |
| "nbformat_minor": 0, | |
| "metadata": { | |
| "colab": { | |
| "name": "Bigquery-cost-control.ipynb", | |
| "provenance": [], | |
| "collapsed_sections": [], | |
| "authorship_tag": "ABX9TyPYL/hZ6CTNlB0QkoQvk6I+", | |
| "include_colab_link": true | |
| }, | |
| "kernelspec": { | |
| "name": "python3", | |
| "display_name": "Python 3" | |
| } | |
| }, | |
| "cells": [ | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "view-in-github", | |
| "colab_type": "text" | |
| }, | |
| "source": [ | |
| "<a href=\"https://colab.research.google.com/gist/gdcutting/bb16490f1ec7d44e7035b98e443dd075/bigquery-cost-control.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "zVBpxN0KQTsW" | |
| }, | |
| "source": [ | |
| "# Big Query Cost Control\n", | |
| "## Part 3 in a series working with BigQuery\n", | |
| "\n", | |
| "In this notebook we will look at a few techniques for cost control in BigQuery on Google Cloud Platform, which is quite important when working in a big data context.\n", | |
| "\n", | |
| "Before we start, there is a bit of setup required for using BigQuery from a Jupyter notebook. In this case, our notebook is on [Google Colab](https://colab.research.google.com), so that it is easy for anyone to access, but the setup is the same as if we were using Jupyter on a local machine or anywhere else. See the [Google BigQuery: Getting Started](https://colab.research.google.com/gist/gdcutting/92322a5717cefa556047e76f1529f655/google-bigquery-getting-started.ipynb) notebook for details on BigQuery setup for notebooks.\n", | |
| "\n", | |
| "Fist we need to import the colab package and authenticate:" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "colab": { | |
| "base_uri": "https://localhost:8080/" | |
| }, | |
| "id": "CWcECYkK2SM6", | |
| "outputId": "f5b3ef8b-424a-44b8-dcac-2be6bcd357ea" | |
| }, | |
| "source": [ | |
| "from google.colab import auth\n", | |
| "auth.authenticate_user()\n", | |
| "print('Authenticated')" | |
| ], | |
| "execution_count": 2, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "text": [ | |
| "Authenticated\n" | |
| ], | |
| "name": "stdout" | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "PQEDfZQQTl7N" | |
| }, | |
| "source": [ | |
| "Then we load the bigquery and [colab.data_table](https://colab.research.google.com/notebooks/data_table.*ipynb*) extensions. The latter renders panda dataframes into interactive displays in our notebooks." | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "id": "UViPsaHM1fAN" | |
| }, | |
| "source": [ | |
| "%load_ext google.cloud.bigquery\n", | |
| "%load_ext google.colab.data_table" | |
| ], | |
| "execution_count": null, | |
| "outputs": [] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "2vsaHfckUGAt" | |
| }, | |
| "source": [ | |
| "The final preparation step is to import the biquery python package and configure the project ID, so that bigquery knows which project we are referencing when we make calls to it." | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "colab": { | |
| "base_uri": "https://localhost:8080/" | |
| }, | |
| "id": "pxLcG42u3WTd", | |
| "outputId": "38121304-a3bf-4989-a650-ffa6509b5a80" | |
| }, | |
| "source": [ | |
| "from google.cloud import bigquery\n", | |
| "project_id = 'bigquery-census-ml'\n", | |
| "client = bigquery.Client(project=project_id)\n", | |
| "%env GCP_PROJECT 'bigquery-census-ml'" | |
| ], | |
| "execution_count": null, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "text": [ | |
| "env: GCP_PROJECT='bigquery-census-ml'\n" | |
| ], | |
| "name": "stdout" | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "0xyZ3OGFZeq-" | |
| }, | |
| "source": [ | |
| "Cost control is a significant issue when working with big data queries (and with cloud applications in general.) If you perform queries carelessly, without specifying exactly what data you need and without estimating the cost, you can easily end up with an eye-popping cloud services bill.\n", | |
| "\n", | |
| "Google recommends a number of best practices for controlling costs when using BigQuery:\n", | |
| "\n", | |
| "* Avoid `SELECT *`\n", | |
| "* Sample data using preview options\n", | |
| "* Price queries before running them\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "BrovPqvXZ4F3" | |
| }, | |
| "source": [ | |
| "## Avoid SELECT *\n", | |
| "\n", | |
| " This one seems obvious but can be a difficult habit to break. Developers and database people among us are used to issuing a `SELECT * `statement, maybe with a `LIMIT` clause, to get an overview of the table structure and contents. That can get you in trouble with BigQuery. Remember that it is not only the number of records, but the number of columns which can be very large in a big data setting. Google states that\n", | |
| "\n", | |
| " Applying a `LIMIT` clause to a `SELECT *` query does not affect the amount of data read. You are billed for reading all bytes in the entire table, and the query counts against your free tier quota.\n", | |
| "\n", | |
| " The reason for this behavior is that using `LIMIT` does not affect the number of bytes scanned, only the number of records returned.\n", | |
| "\n", | |
| " Instead, it is recommended that we use one of the data preview options:\n", | |
| "\n", | |
| "* In the Cloud Console or the classic web UI, on the table details page, click the Preview tab to sample the data.\n", | |
| "* In the command-line interface, use the bq head command and specify the number of rows to preview.\n", | |
| "* In the API, use tabledata.list to retrieve table data from a specified set of rows.\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "F9ittgzaaYcJ" | |
| }, | |
| "source": [ | |
| "## Sample data using preview options\n", | |
| "### Previewing queries in the Console\n", | |
| "\n", | |
| " Here is an example of previewing a query on the USA Names dataset, which consists of names from Social Security card applications. This query is large by usual database standards (~100MB), but still very small by big data standards. \n", | |
| "\n", | |
| " \n", | |
| "\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "pE9_sM9Ja-vq" | |
| }, | |
| "source": [ | |
| "## Previewing with bq head\n", | |
| "\n", | |
| " Use the bq head to preview data. This command is very useful for exploring data - since it does not initiate a query job, bq head will not incur costs. It does not show the size of the returned data, but does give a good view of data structure. Notice that the syntax for referring to datasets and table names is a bit different from the syntax used in SQL:\n", | |
| "\n", | |
| "Performing `bq head --maxrows=10 bigquery-public-data:samples.shakespeare` will give:" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "colab": { | |
| "base_uri": "https://localhost:8080/" | |
| }, | |
| "id": "sCum8jATbHld", | |
| "outputId": "71dfeca8-5e76-4a0e-be82-34c0ad8c8386" | |
| }, | |
| "source": [ | |
| "!bq head --max_rows=10 bigquery-public-data:samples.shakespeare" | |
| ], | |
| "execution_count": 6, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "text": [ | |
| "+-----------+------------+---------+-------------+\n", | |
| "| word | word_count | corpus | corpus_date |\n", | |
| "+-----------+------------+---------+-------------+\n", | |
| "| LVII | 1 | sonnets | 0 |\n", | |
| "| augurs | 1 | sonnets | 0 |\n", | |
| "| dimm'd | 1 | sonnets | 0 |\n", | |
| "| plagues | 1 | sonnets | 0 |\n", | |
| "| treason | 1 | sonnets | 0 |\n", | |
| "| surmise | 1 | sonnets | 0 |\n", | |
| "| heed | 1 | sonnets | 0 |\n", | |
| "| Unthrifty | 1 | sonnets | 0 |\n", | |
| "| quality | 1 | sonnets | 0 |\n", | |
| "| wherever | 1 | sonnets | 0 |\n", | |
| "+-----------+------------+---------+-------------+\n" | |
| ], | |
| "name": "stdout" | |
| } | |
| ] | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "metadata": { | |
| "id": "p0cdLL7xcRs5" | |
| }, | |
| "source": [ | |
| "## Price queries before running them\n", | |
| "\n", | |
| " Queries are priced according to the number of bytes read. Unfortunately, there is not a way to view query cost directly before running it. You will have to get the query size and then use the GCP Pricing Calculator to get a cost estimate.\n", | |
| "\n", | |
| " The method for getting the query size in the Console is discussed above. To get the size of a query before actually running it, use `bq` with the `--dry-run` flag:" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "metadata": { | |
| "colab": { | |
| "base_uri": "https://localhost:8080/" | |
| }, | |
| "id": "BVv7nn_ccnI7", | |
| "outputId": "8e4c5f94-c4ef-41f4-8831-094b2b4aeb47" | |
| }, | |
| "source": [ | |
| "!bq query \\\n", | |
| " --use_legacy_sql=false \\\n", | |
| " --dry_run \\\n", | |
| " 'SELECT HCPC, RECID, SHORT_DESCRIPTION, PRICE1 \\\n", | |
| " FROM \\\n", | |
| " `bigquery-public-data.cms_codes.hcpcs` \\\n", | |
| " LIMIT \\\n", | |
| " 1000'" | |
| ], | |
| "execution_count": 7, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "text": [ | |
| "Query successfully validated. Assuming the tables are not modified, running this query will process 292745 bytes of data.\n" | |
| ], | |
| "name": "stdout" | |
| } | |
| ] | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment