Introduction to GCP's BigQuery - GDG PuraVida

This presentation covers an introduction to Google Cloud Platform's BigQuery.

Transcript

2. Akshay Kumar Pallerla Big Data Engineer at NTT DATA Inc., Costa Rica.
3. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery. • Secret sauce for BigQuery’s speed. • Cooking your first ever query. • Top it up & Share your dish. • Discussing – [Diner]o. • The usual : Thank you and Questions
4. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH.
5. Definitions
6. Warehouse "Amazon Japan’s Kawasaki Fulfillment Center 2" by ykanazawa1999 is licensed under CC BY-NC-SA 2.0 “Jack Sealey Warehouse" by toolstop is licensed under CC BY 2.0 A large building where raw materials or manufactured goods may be stored before their export or distribution for sale.
7. Data Warehouse ecommerce External ERP Other Legacy E T L Enterprise Datawarehouse EDW *Extremely oversimplified diagram of an ETL pipeline populating a DWH
8. Data Warehouse In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered a core component of business intelligence.[1] throughout the enterprise.[3] *https://en.wikipedia.org/wiki/Data_warehouse
9. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery.
10. Introduction to Google’s BigQuery
11. BigQuery BigQuery is a serverless, highly-scalable, and cost-effective cloud data warehouse with an in-memory BI Engine and machine learning built in. ❑Real-time analytics ❑Standard SQL ❑Federated query and logical data warehousing ❑Storage and compute separation ❑Geospatial data types and functions ❑Big data ecosystem integration
12. Indexing in BigQuery This page is intentionally left blank
13. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery. • Secret sauce for BigQuery’s speed.
14. Google’s BigQuery - Architecture
15. Salsa Lizano, (salsa means "sauce" in Spanish), is a Costa Rican condiment developed in 1920 by the Lizano Company. It is now a product of Unilever. It is a thin, smooth, light brown sauce (akin to such condiments as HP Sauce or Worcestershire sauce). It is meant to be used while cooking or at tableside to flavor one's food when serving. It is slightly sweet and acidic, with a hint of spiciness lent by black pepper and cumin. The ingredients include water, sugar, salt, vegetables (onions, carrots, cauliflower, cucumbers), spices, pepper, mustard, turmeric, modified corn starch, hydrolyzed vegetable protein, sodium benzoate. *https://en.wikipedia.org/wiki/Salsa_Lizano
16. BigQuery - Architecture https://cloud.google.com/files/BigQueryTechnicalWP.pdf https://cloud.google.com/blog/products/gcp/bigquery-under-the-hood
17. BigQuery – Architecture (Contd. . .) https://ai.google/research/pubs/pub36632 More details : https://medium.com/@pallerlaakshay/wrapping-head-around-repetition-and-definition-levels-in-dremel-powering- bigquery-c1a33c9695da
18. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery. • Secret sauce for BigQuery’s speed. • Cooking your first ever query.
19. Querying BigQuery
20. Querying BigQuery Classic Web UI bq CLI Rest API & Client Libraries Web UI in Google Cloud Console Interacting with BigQuery Operations : • Loading & Exporting Data • Querying & Viewing Data • Managing Data
21. Querying BigQuery – Plan B *Used my own amazon prime subscription Live Demos can fail  Murphy’s Law: Anything that can go wrong will go wrong
22. Querying BigQuery – Plan B
23. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery. • Secret sauce for BigQuery’s speed. • Cooking your first ever query. • Top it up & Share your dish.
24. BigQuery – Features
25. BigQuery – Features •File formats supported : JACOP [JSON, Avro , CSV, ORC, Parquet] •BigQuery supports UTF-8 encoding for both nested or repeated and flat data. BigQuery supports ISO- 8859-1 encoding for flat data only for CSV files • Nested and repeated fields are supported in the following data formats : JACCOP [JSON, Avro, Cloud Datastore exports, Cloud Firestore exports, ORC, Parquet] • Schema Auto detection available for JSON and CSV files • BigQuery Data Transfer Service automates data delivery into BigQuery – SaaS, S3,Teradata etc., • BigQuery allows you to save and share queries with others • BigQuery supports Table partitioning – By ingestion time and date/time partitioned tables
26. BigQuery – Features (Contd. . .) •BigQuery provides metadata access through INFORMATION_SCHEMA •BigQuery supports Labelling resources : Key-value pairs •Access is controlled via Authorized views and IAM roles •Point in time snapshots available for 7-days •Supports Machine Learning Modeling •Easily integrated with other Google Cloud Platform products like Data Studio for reporting, Cloud Storage for data loads, Stack driver for auditing and monitoring etc., •Supports querying Nested and repeated fields •Supports querying wildcard tables
27. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery. • Secret sauce for BigQuery’s speed. • Cooking your first ever query. • Top it up & Share your dish • Discussing – [Diner]o.
28. BigQuery - Costing
29. BigQuery – Costing Refer to https://cloud.google.com/bigquery/ for current pricing
30. BigQuery – Costing Refer to https://cloud.google.com/bigquery/ for current pricing
31. BigQuery – Costing Google BigQuery offers 26-34% Three-year TCO savings versus alternative cloud- based EDW deployments https://services.google.com/fh/files/blogs/esg_economic_validation_google_bigquery_vs_cloud-based-edws-september_2019.pdf
32. `DevFest2019.IntrotoBigQuery.Agenda` [Project].[Dataset].[Table name] • Boring. . .Mmmm. . .No, meaningful definitions – DWH. • Scuba dive into Google’s BigQuery. • Secret sauce for BigQuery’s speed. • Cooking your first ever query. • Top it up & Share your dish • Discussing – [Diner]o. • The usual : Thank you and Questions
33. Thank You
34. Questions ? [Don’t trouble me please] @AkshayPallerla pallerlaakshay@gmail.com https://www.linkedin.com/in/akshaypallerla