book

Google BigQuery: The Definitive Guide

by Valliappa Lakshmanan, Jordan Tigani

October 2019

Intermediate to advanced

519 pages

13h 39m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Who Is This Book For?Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgmentsUpdates to First Edition
Data Processing ArchitecturesRelational Database Management SystemMapReduce FrameworkBigQuery: A Serverless, Distributed SQL EngineWorking with BigQueryDeriving Insights Across DatasetsETL, EL, and ELTPowerful AnalyticsSimplicity of ManagementHow BigQuery Came AboutWhat Makes BigQuery Possible?Separation of Compute and StorageStorage and Networking InfrastructureManaged StorageIntegration with Google Cloud PlatformSecurity and ComplianceSummary
Simple QueriesRetrieving Rows by Using SELECTAliasing Column Names with ASFiltering with WHERESELECT *, EXCEPT, REPLACESubqueries with WITHSorting with ORDER BYAggregatesComputing Aggregates by Using GROUP BYCounting Records by Using COUNTFiltering Grouped Items by Using HAVINGFinding Unique Values by Using DISTINCTA Brief Primer on Arrays and StructsCreating Arrays by Using ARRAY_AGGArray of STRUCTTUPLEWorking with ArraysUNNEST an ArrayJoining TablesThe JOIN ExplainedINNER JOINCROSS JOINOUTER JOINSaving and SharingQuery History and CachingSaved QueriesViews Versus Shared QueriesSummary
Numeric Types and FunctionsMathematical FunctionsStandard-Compliant Floating-Point DivisionSAFE FunctionsComparisonsPrecise Decimal Calculations with NUMERICWorking with BOOLLogical OperationsConditional ExpressionsCleaner NULL-Handling with COALESCECasting and CoercionUsing COUNTIF to Avoid Casting BooleansString FunctionsInternationalizationPrinting and ParsingString Manipulation FunctionsTransformation FunctionsRegular ExpressionsSummary of String FunctionsWorking with TIMESTAMPParsing and Formatting TimestampsExtracting Calendar PartsArithmetic with TimestampsDate, Time, and DateTimeWorking with GIS FunctionsSummary
The BasicsLoading from a Local SourceSpecifying a SchemaCopying into a New TableData Management (DDL and DML)Loading Data EfficientlyFederated Queries and External Data SourcesHow to Use Federated QueriesWhen to Use Federated Queries and External Data SourcesInteractive Exploration and Querying of Data in Google SheetsSQL Queries on Data in Cloud BigtableTransfers and ExportsData Transfer ServiceExporting Cloud Logging LogsUsing Cloud Dataflow to Read/Write from BigQueryMoving On-Premises DataData Migration MethodsSummary
Developing ProgrammaticallyAccessing BigQuery via the REST APIGoogle Cloud Client LibraryAccessing BigQuery from Data Science ToolsNotebooks on Google Cloud PlatformWorking with BigQuery, pandas, and JupyterWorking with BigQuery from RCloud DataflowJDBC/ODBC driversIncorporating BigQuery Data into Google Slides (in G Suite)Bash Scripting with BigQueryCreating Datasets and TablesExecuting QueriesBigQuery ObjectsSummary
High-Level ArchitectureLife of a Query RequestBigQuery UpgradesQuery Engine (Dremel)Dremel ArchitectureQuery ExecutionStorageStorage DataMetadataSummary
Principles of PerformanceKey Drivers of PerformanceControlling CostMeasuring and TroubleshootingMeasuring Query Speed Using REST APIMeasuring Query Speed Using BigQuery Workload TesterTroubleshooting Workloads Using Cloud LoggingReading Query Plan InformationIncreasing Query SpeedMinimizing I/OCaching the Results of Previous QueriesPerforming Efficient JoinsAvoiding Overwhelming a WorkerUsing Approximate Aggregation FunctionsOptimizing How Data Is Stored and AccessedMinimizing Network OverheadChoosing an Efficient Storage FormatPartitioning Tables to Reduce Scan SizeClustering Tables Based on High-Cardinality KeysCost OptimizationBatch QueriesOptimizing Storage CostQuery Cost ControlFile LoadsSummaryChecklist
Reusable QueriesParameterized QueriesSQL User-Defined FunctionsReusing Parts of QueriesAdvanced SQLWorking with ArraysWindow FunctionsTable MetadataData Definition Language and Data Manipulation LanguageBeyond SQLJavaScript UDFsScriptingAdvanced FunctionsBigQuery Geographic Information SystemsUseful Statistical FunctionsHash AlgorithmsSummary
What Is Machine Learning?Formulating a Machine Learning ProblemTypes of Machine Learning ProblemsBuilding a Regression ModelChoose the LabelExploring the Dataset to Find FeaturesCreating a Training DatasetTraining and Evaluating the ModelPredicting with the ModelExamining Model WeightsMore-Complex Regression ModelsBuilding a Classification ModelTrainingEvaluationPredictionChoosing the ThresholdCustomizing BigQuery MLControlling Data SplitBalancing ClassesRegularizationk-Means ClusteringWhat’s Being Clustered?Clustering Bicycle StationsCarrying Out ClusteringUnderstanding the ClustersData-Driven DecisionsRecommender SystemsThe MovieLens DatasetMatrix FactorizationMaking RecommendationsIncorporating User and Movie InformationCustom Machine Learning Models on GCPHyperparameter TuningAutoMLSupport for TensorFlowSummary

Infrastructure SecurityIdentity and Access ManagementIdentityRoleResourceAdministering BigQueryJob ManagementAuthorizing UsersRestoring Deleted Records and TablesContinuous Integration/Continuous DeploymentSlot ReservationsCost/Billing ExportsDashboards, Monitoring, and Audit LoggingAvailability, Disaster Recovery, and EncryptionZones, Regions, and MultiregionsBigQuery and Failure HandlingDurability, Backups, and Disaster RecoveryPrivacy and EncryptionRegulatory ComplianceData LocalityRestricting Access to Subsets of DataRemoving All Transactions Related to a Single IndividualData Loss PreventionCMEKData Exfiltration ProtectionSummary

Content preview from Google BigQuery: The Definitive Guide

Chapter 1. What Is Google BigQuery?

Data Processing Architectures

Google BigQuery is a serverless, highly scalable data warehouse that comes with a built-in query engine. The query engine is capable of running SQL queries on terabytes of data in a matter of seconds, and petabytes in only minutes. You get this performance without having to manage any infrastructure and without having to create or rebuild indexes.

BigQuery has legions of fans. Paul Lamere, a Spotify engineer, was thrilled that he could finally talk about how his team uses BigQuery to quickly analyze large datasets: “Google’s BigQuery is *da bomb*,” he tweeted in February 2016. “I can start with 2.2Billion ‘things’ and compute/summarize down to 20K in < 1 min.” The scale and speed are just two notable features of BigQuery. What is more transformative is not having to manage infrastructure because the simplicity inherent in serverless, ad hoc querying can open up new ways of working.

Companies are increasingly embracing data-driven decision making and fostering an open culture where the data is not siloed within departments. BigQuery, by providing the technological means to enact a cultural shift toward agility and openness, plays a big part in increasing the pace of innovation. For example, Twitter recently reported in its blog that it was able to democratize data analysis with BigQuery by providing some of its most frequently used tables to Twitter employees from a variety of teams (Engineering, Finance, and Marketing ...