BigQuery - Calculating Runnig Total

The below bigquery helps user to calculate running total of page visit:

SELECT DATE(a.date_time), a.hour,  a.minute, a.views, SUM(a.views) OVER (ORDER BY  a.hour,  a.minute) AS rt
FROM  [sampledate.clicks] a
JOIN  [sampledate.clicks] b ON  a.date_time = b.date_time
WHERE  b.date_time <= a.date_time 
AND DATE(a.date_time) = DATE(DATE_ADD(CURRENT_DATE(),-1,"DAY"))
ORDER BY  a.HOUR,  a.MINUTE

Install Google BigQuery - Command Line Tool

Google BigQuery can also be accessed from command line. To achieve this one need to implement below steps:

1. Install Google cloud using, gcloud install by opening the terminal
2. Check python version -- type python -version
3. Run apt-get install python-setuptools
4. Check if pip is installed, If not found, sudo easy_install pip
5. Install google client tools for python using, pip install --upgrade google-api-python-client .

If not installed try the following commands (only for Mac users)

1. Enter the following at a command prompt:
2. $ curl https://sdk.cloud.google.com | bash
3. Restart your shell:
4. $ exec -l $SHELL
5. Run gcloud init to initialize the gcloud environment:
6. $ gcloud init

6. Initialise gcloud using , gcloud init

7. Check big query using bq command, bq ls

SSH Key and GitHub Configuration

Step 1: Check existing shh keys.             
            > ls -al ~/.ssh

drwxr-xr-x+ 53 ******  staff  1802 Jun 13 10:17 ..
-rw-------   1 ******  staff  1766 May 10 17:10 github_rsa
-rw-r--r--   1 ******  staff   404 May 10 17:10 github_rsa.pub
-rw-------   1 ******  staff  1675 Jun  2 16:12 id_rsa
-rw-r--r--   1 ******  staff   428 Jun  2 16:12 id_rsa.pub
-rw-r--r--   1 ******  staff  2206 Jun  8 11:37 known_hosts


Step 2: Copy SSH Key
            >pbcopy < ~/.ssh/id_rsa.pub

ssh-rsa ******7tuyqguygdygayga/Iugahasbyasgcjhasbcjhasjcjsajhcjh+hhH/HNGjZ +YVVGvvvvvTcchkjnskbjh+users@userssystem.local

Step 3: You can add this ssh key to Github account by navigating to Profile — > Setting —> New Ssh key

Step 4: Validating GitHub ssh account
            > ssh -T git@github.com

Warning: Permanently added the RSA host key for IP address ‘*******’ to the list of known hosts.
Hi *****! You've successfully authenticated, but GitHub does not provide shell access.

BigQuery - Dealing with Date and Time

The below google bigquery provide us idea to convert timestamp to different date and time values:

SELECT CURRENT_DATE() as currentdate
              , CURRENT_TIME() currenttime, CURRENT_timestamp() currentdatetime
              , DATE(TIMESTAMP(CURRENT_timestamp())) AS dateonly
              , MONTH(TIMESTAMP(CURRENT_timestamp())) as monthonly
              , YEAR(TIMESTAMP(CURRENT_timestamp())) as yearonly
              , HOUR(TIMESTAMP(CURRENT_timestamp())) as houronly
              , MINUTE(TIMESTAMP(CURRENT_timestamp())) as minuteonly
              , SECOND(TIMESTAMP(CURRENT_timestamp())) as secondonly
              , DAY(TIMESTAMP(CURRENT_timestamp())) as dayonly
              , PARSE_UTC_USEC(STRING(CURRENT_timestamp())) as timestamtounix
              , NOW() AS CurrentUnixTimestamp
              , USEC_TO_TIMESTAMP(1465814687003321) AS unixtotimestamp
  FROM [project:dataset.table] LIMIT 1;

Vagrant Ubuntu Virtual Machine - Step by Step Installation

Vagrant and Installation

Step 1: Installing Virtualbox

Download Oracle Virtulabox from https://www.virtualbox.org/ install virtual box.

Step 2: Installing Vagrant

Download vagrant from https://www.vagrantup.com/downloads.html and install to local machine.

Step 3: Installing Vagrant Host-Manager

Run below commands to install vagrant manager.

$vagrant plugin install vagrant-hostmanager

Step 4: Create vagrant working directory

$mkdir VagrantBox

$cd VagrantBox

$mkdir ubuntu64

$cd ubuntu64

Step 5: Run ubuntu virtual machine

$ vagrant box add {title} {url}

e.g.,

$vagrant box add ubuntu64 https://cloud-images.ubuntu.com/vagrant/trusty/current/trusty-server-cloudimg-amd64-vagrant-disk1.box

Refer: http://www.vagrantbox.es/ for url

Step 6: Initiate vagrant ubuntu machine

$ vagrant init {title}

$ vagrant up

Step 7: A ubuntu machine will start, and requests for username and password. Provide below username and password and start using virtual machine.

username: vagrant

password: vagrant