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

Luigi - Job Monitoring Server

Luigi is a Python (2.7, 3.3, 3.4, 3.5) package that helps you build complex pipelines of batch jobs. It handles dependency resolution, workflow management, visualization, handling failures, command line integration, and much more.

Installing Luigi

Step 1 >> pip install luigi (command line)

Step 2 >> luigid (command line)

Step 3 >> Browse localhost:8082

Google BigQuery: Convert Date Time to YYYYMMDD format

The below syntax helps to convert date time format to date format using Google BigQuery:

SELECT
<date_time column> DATETIME, STRFTIME_UTC_USEC(<date_time column>,'%Y%m%d')
as DATE
FROM <table>

SSAS MDX: Converting a Dimension Value to Calculated Measure

I have a dimensional attribute "Target TOS Monthly", in order to convert this dimensional attribute to measure added a calculated measure with below code.


SUM([Account].[Target TOS Monthly].[Target TOS Monthly].MEMBERS, [Account].[Target TOS Monthly].MEMBER_VALUE)

AWS S3 to Redshift Incremental load

Performing incremental load between AWS S3 and Redshift table.

Step 1: Create a stageing table in AWS Redshift database similar to your  target table. Execute below command to load data from S3 to Staging table.


QUERY 1

copy stage.logtracking from 's3://Bucket/FolderName'
credentials aws_access_key_id="S3Accesskey";aws_secret_access_key="S3Secretkey"
delimiter   '|'  ACCEPTINVCHARS iGNOREHEADER 1;


 Step 2: Run the below query to perform incremental load similar to UPSERT/MERGE statement

QUERY 2

-- Start a new transaction
BEGIN TRANSACTION;

-- Update the target table using an inner join with the staging table
-- The join includes a redundant predicate to collocate on the distribution key
-- A filter on saletime enables a range-restricted scan on SALES

UPDATE ga.logtracking
SET  usertype= stage.logtracking.usertype, sessioncount= stage.logtracking.sessioncount
   , dayssincelastsession = stage.logtracking.dayssincelastsession, userdefinedvalue = stage.logtracking.userdefinedvalue
   , date = CAST(stage.logtracking.date AS DATETIME)
   , userid = stage.logtracking.userid
   , users = stage.logtracking.users
   , newusers = stage.logtracking.newusers
   , percentnewsessions = stage.logtracking.percentnewsessions
   , sessionsperuser = stage.logtracking.sessionsperuser
   , timeonsite = stage.logtracking.timeonsite
   , checksum = stage.logtracking.checksum
FROM stage.logtracking
WHERE ga.logtracking.checksum = stage.logtracking.checksum;

-- Delete matching rows from the staging table
-- using an inner join with the target table

DELETE FROM stage.logtracking
USING ga.logtracking
WHERE ga.logtracking.checksum = stage.logtracking.checksum;

-- Insert the remaining rows from the staging table into the target table
INSERT INTO ga.logtracking
SELECT usertype, sessioncount, dayssincelastsession, userdefinedvalue, CAST(date AS DATETIME), userid
, users, newusers, percentnewsessions, sessionsperuser, timeonsite, checksum
FROM stage.logtracking;

-- End transaction and commit
END TRANSACTION;