Posts Tagged - qwiklabs

BigQuery CLI usage

Check: Get meaningful insights with BigQuey

BigQuery offers a number of sample tables that you can run queries against. This examples run queries against the shakespeare table, which contains an entry for every word in every play.

General help

# see a list of all bq commands  
bq help  

# show info for query command  
bq help query  

Search info

# examine the shakespeare table  
bq show bigquery-public-data:samples.shakespeare

# query to see how many times the substring "raisin" appears in Shakespeare's works.
bq query --use_legacy_sql=false \
    'SELECT word, SUM(word_count) AS count
     FROM `bigquery-public-data`.samples.shakespeare
     WHERE word LIKE "%raisin%"
     GROUP BY word'

 # see the top 5 most popular names  
 bq query "SELECT name,count
             FROM babynames.names2010
             WHERE gender = 'F'
             ORDER BY count DESC
             LIMIT 5"

# list any existing dataset in your project
bq ls

Create queries and upload a dataset

# create a new dataset named babynames
bq mk babynames

Before you can build a table, you need to add the dataset to your project. The custom data file you’ll use contains approximately 7 MB of data about popular baby names, provided by the US Social Security Administration.

The bq load command creates or updates a table and loads data in a single step.

# create the table
bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer

this is the equivalent to

datasetID: babynames  
tableID: names2010  
source: yob2010.txt  
schema: name:string,gender:string,count:integer  
# confirm the table appears
bq ls babynames

# see the table schema
bq show babynames.names2010

# remove table
bq rm -r babynames

Read More

gsutil commands for Buckets and Objects

System management

# get project id  
gcloud config get-value project

# set environmental vars  
PROJECT_ID=`gcloud config get-value project`  

Buckets and objects operations

List, download and sync buckets. Upload files

# gsutil modificators
#   -m multithread

# list buckets and objects in your bucket
gsutil ls
gsutil ls gs://${BUCKET}

# check storage classes
gsutil ls -Lr gs://${BUCKET} | more

# download the whole bucket
gsutil -m cp -R gs://${BUCKET} .

# sync local folder with bucket content
gsutil -m rsync -d -r local-folder gs://${BUCKET}
#  use it with the whole root local folder
# -d delete files on target if they're missing on local
# -r recursive

# upload a file with nearline storage.
gsutil cp -s nearline thisanearlinefile gs://${BUCKET}

For more info on file storage classes check here

Modify objects

# make all objects in a folder public
gsutil -m acl set -R -a public-read gs://${BUCKET}/folder
# to confirm they're public go to
#<your-bucket-name>/folder/old.txt on a web-browser

Create and delete buckets

# Create a bucket and a multi-regional class store  
gsutil mb -c multi_regional gs://${BUCKET}

# delete bucket with object
gsutil rm -rf gs://${BUCKET}
# delete an empty bucket
gsutil rb gs://${BUCKET}

Read More

Configuring IAM Permissions via gcloud

Identity access management (IAM) lets you manage access control by defining who (identity) has what access (role) for which resource.

With Cloud IAM it’s possible to grant granular access to specific GCP Resources and prevent unwanted access to other resources.


Members can be of the following types

  • Google account
  • Service account
  • Google group
  • G Suite Domain
  • Cloud Identity Domain

more information on this here

Read More

Configuring Networks via gcloud

A virtual private cloud (VPC) network is a global resource which consists of a list of regional subnetworks (subnets) in data centes, all connected by a global wide network (WAN).

VPC networks are isolated from each other. They provide functionality to Compute Engine VMs, GKE and App Engine.

Each GCP Project has a default network config. which provides each region with an auto subnet network.

Create a network

You can choose to create a VPC network with auto mode or custom mode
You can create up to 4 addition networks in a project. Each of them must have a unique name inside the project.

# creates a network called labnet
gcloud compute networks create labnet --subnet-mode=custom

Read More

Getting started with Cloud Shell and gcloud

Cloud Shell is a Debian-base virtual machine with a persistent 5GB home directory, which makes it easy to manage GCP projects and resources.

The gcloud command is a part of Google Cloud SDK. You need to download and install it on your own system and initialize it. It’s pre-installed in Google Cloud Shell to access to computing resources hosted on the GCP.

For more in-depth resources, check here
Google Cloud Shell docs
gcloud tool docs

Read More