DEV Community: Mr. Yap

Getting started with BigQuery

Mr. Yap — Thu, 27 Feb 2020 15:59:50 +0000

BigQuery is Google's data warehouse service, dedicated to managing and querying large datasets.

As BigQuery is one of many Google Cloud Platform products, here are some great benefit to organisations:
1. Serverless. No need to download data to your machine in order to work with it - the dataset will remain on the cloud.
2. Ease of use. Run ad-hoc SQL queries on your dataset without having to prepare the data, like indexes, beforehand. This is invaluable for data exploration.
3. Scale. Carry out data exploration on extremely large datasets interactively. You don't need to sample the data in order to work with it in a timely manner.
4. Shareability. You will be able to run queries on data from different datasets without any issues. BigQuery is a convenient way to share datasets. Of course, you can also keep your data private, or share them only with specific persons -- not all data need to be public.

BigQuery charges for storing data, streaming inserts, and querying data. Loading and exporting data are free of charge.

Getting started in BigQuery can be overwhelming. You need to have experience with sql query, there are platform-specific nuances that might trip you up. You also need to be comfortable working with web analytics data outside the Google Analytics interface.

Data Ingestion

To ingest data into BigQuery, you can use an

EL pipeline (used for periodic loads of log files),
ETL pipeline (used when data needs to be enriched or quality controlled),
ELT pipeline (used for exploratory work)

Data Processing Tasks

Piping Cloud Pub/Sub messages to BigQuery or bulk-compressing files in Cloud Storage are common data processing tasks perform in GCP.

As pipeline templates are simply stored in Cloud Storage buckets, they can be made publicly available for use across organizations and teams.

Google provides ready-made templates for several of these common processing tasks, your teams can accomplish these common tasks without ever developing a pipeline.

Interacting with BigQuery

You access BigQuery through the

Google Cloud Platform Console (via a browser),
Command-line tool, or
Making calls to the BigQuery REST API using client libraries such as Java, .NET, or Python.

There are also a variety of third-party tools that you can use to interact with BigQuery, such as visualizing the data or loading the data.

Using the BigQuery web UI in the GCP Console as a visual interface to complete tasks like running queries, loading data, and exporting data is the most common way to use BigQuery

“External Table” in BigQuery

External Tables are used to query data that you don’t want to import into BigQuery and remain in an external source such as Google Cloud Storage, Google Drive, BigTable.

Since External tables in BigQuery reference data from an external source, an explicit BigQuery schema cannot be specified for an external table. The format of the external source defines the schema for the table.

https://cloud.google.com/bigquery/external-data-sources

What I learnt today about Google Cloud Platform

Mr. Yap — Tue, 28 May 2019 09:29:11 +0000

Billing

In GCP, virtual machines are billed in one-second intervals.

The unit per minute cost of a virtual server is based on the characteristics of the server, such as the number of virtual servers, the amount of memory, and the region where you run the virtual machine, influence the cost. Time of day is not a factor nor is the type of application you run on the virtual machine.

You want to estimate the cost of GCP resources you will be using Compute Engine and Kubernetes Engine services to have information on the virtual machines you will be using.

The following commands will stop a virtual machine named web-server-1?

gcloud compute instances stop web-server-1

gcloud compute instances, to indicate you are working with virtual machines, followed by the stop command and the name of the VM.

Your management team is considering 3 different cloud providers. You have been asked to summarize billing and cost information to help the management team compare cost structures between clouds. Which of the following would you mention about the cost of virtual machines in GCP?

VMs are billed in one-second increments, cost varies with the number of CPUs and amount of memory in a machine type, you can create custom machine types, preemptible VMs cost up to 80 percent less than standard VMs, and Google offers discounts for sustained usage.

Cloud Memorystore

Cloud Memorystore is the only GCP designed to cache data in memory. Cloud SQL is a relational database service and might be a good option for the backend database. Cloud Spanner is a global relational database and is a good option when you need a globally consistent database. Cloud Datastore is a document database suitable for product catalogs, user profiles, and other semistructured data.

Cloud Memorystore Use Case : Your department is deploying an application that has a database backend. You are concerned about the read load on the database server and want to have data available in memory to reduce the time to respond to queries and to reduce the load on the database server.

Dataproc

Dataproc is designed to execute workflows in both batch and streaming modes, which makes option A correct. Bigquery is a data warehouse service. Datastore is a document database. AutoML is a machine learning service.

IAM

Owner, Editor, and Viewer are the three primitive privileges in Google Cloud Platform.

Identities are abstractions of users. They can also represent characteristics of processes that run on behalf of a human user or a virtual machine in the GCP. Identities are not related to virtual machine IDs. Roles are collections of privileges that can be granted to identities.

Organization, folders, and projects are the components used to manage an organizational hierarchy. Buckets, directories, and subdirectories are used to organize storage.

Network

subnet is not a parameter to gcloud to create a firewall. source-ranges is for specifying sources of network traffic the rule applies to.

gcloud command

Assuming there is a MySQL database running Cloud SQL called ace_exam_mysql to connect to a MySQL database running in Cloud SQL. The command is

gcloud sql connect ace_exam_mysql --user = root.

The correct command starts with gcloud sql not gcloud connect. The term sql is needed to identify the GCP service. A password is not needed on the command line; the user will be prompted for it.

Stackdriver Logging

To view admin activity, system events, and data access on an App Engine project, Stackdriver Logging service you would use to view audit logs

Cloud Function

You want to create a cloud function to transform audio files into different formats. The audio files will be uploaded into Cloud Storage. You want to start transformations as soon as the files finish uploading. Which trigger would you specify in the cloud function to cause it to execute after the file is uploaded?

AnS: google.storage.object.finalize

Deployment

What parameters of a deployment can be set in the Create Deployment page in Cloud Console?

You can specify container image, cluster name, and application name along with the labels, initial command, and namespace.

Monitoring

You have just created a virtual machine, and you'd like to monitor the VM and have an email sent to you whenever the CPU average utilization exceeds 75 percent for five minutes. What do you need to do to the VM to have this happen?

Install the monitoring agent on the virtual machine

Security

You are running applications that manage personal information for clients, and you want to take advantage of enhanced security features available to virtual machines in GCP. What option would you enable when creating a VM to get these enhanced security features?

Shielded VM is a set of enhanced security mechanisms, including Secure Book, Virtual Trusted Platform Module (vTPM), and Integrity monitoring. Integrity monitoring and secure boot are included with Shield VM and are not enabled separately.

4 things about Cloud Storage

Cloud Storage buckets can have retention periods.
Cloud Storage does not provide block-level access to data within files stored in buckets.
Cloud Storage is designed for high durability.
Lifecycle configurations cannot be used to change storage class from regional to multiregional.(Once a bucket is created as regional or multiregional, it cannot be changed to the other)

App Engine

You are running a production application in App Engine. You would like to upgrade the application, but you want to minimize the risk of disrupting the service. The best approach is create a new version of the service and route a small subset of traffic to that version and monitor for errors or other issues. Take advantage of App Engine versions and the ability to split traffic. If there is a problem, you can detect it while exposing a small number of users to the new version.

Preemptible virtual machine

Preemptible virtual machines may be shut down at any time, but they will be shut down if they run over 24 hours.

BigQuery

To get an estimate of the volume of data scanned by BigQuery from the command line, type

bq --location=[LOCATION] query --use_legacy_sql=false --dry_run [SQL_QUERY]

The correct bq command structure, which includes location and the --dry_run option. This option calculates an estimate without actually running the query.

You have received a large data set from an Internet of Things (IoT) system. You want to use BigQuery to analyze the data. What command-line command would you use to make data available for analysis in BigQuery?

bq load --autodetect --source_format=[FORMAT] [DATASET].[TABLE] [PATH_TO_SOURCE]

The autodetect and source_format parameters and path to source are correctly specified in all options.

A client asks for recommendations for deploying a data warehouse in GCP. The client wants to minimize the amount of time spent on systems administration. Most of the analysts using the data warehouse know Microsoft Excel, SQL, and Python. They want the data warehouse infrastructure to grow as needed without having a database administrator modifying database configurations. What GCP services would I recommend?

BigQuery is a data warehouse and analytic database managed service that minimizes administration overhead. There is no need to configure storage; that is managed by Google. BigQuery uses SQL as a query language. Cloud Storage is an object storage system and not suitable for data warehouses. Datastore is an object store and not suitable for analyzing with SQL. Bigtable is not appropriate for large, analytic queries like those found in data warehouse. Bigtable does not support SQL.

Stackdriver

What Stackdriver service is best for identifying bottlenecks in your application code?

Cloud Trace is a distributed tracing application that provides details on how long different parts of code run
Monitoring is used to notify DevOps engineers when resources are not functioning as expected.
Logging is for collecting, storing, and viewing log data, and although log entries might help diagnose bottlenecks, it is not specifically designed for that.
Debug is used to generate snapshot and inject logpoints.

What you need to know about Firebase

Mr. Yap — Mon, 27 May 2019 11:59:56 +0000

Firebase allows you to do

A/B Testing 🆎
Machine Learning 🤖
Hosting 🖥️
Storage 🏬
Data Analytics 📊 in one platform for all your mobile apps (including PWA)

Within the platform, a suite of Firebase products available for web apps:

Firebase Authentication to easily allow your users to sign into your app.
Cloud Firestore to save structured data on the cloud and get instant notification when data changes.
Cloud Storage for Firebase to save files in the cloud.
Firebase Hosting to host and serve your assets.
Firebase Cloud Messaging to send push notifications and display browser popup notifications.

Follow me as I attempt to built a chat web app on Firebase platform

I am doing this to get myself similar what out there so it makes my job as a web analyst easier.