Machine Learning with BigQuery [Trailer]

Are you excited to understand Machine Leaning(ML) basic and do some experiment. This blog is best fit for you. This is ML With BigQuery just Trailer.

BigQuery is Google cloud based Big Data Data warehouse it provides machine learning capability as well.

 BigQuery also provides Public dataset where you can play around, so no need to waste time on populate huge test data. Here we will try to trained a Model and then will give some sample and test model will classify the text source.

Step-1 : Create Dataset for Input

WITH
  extracted AS (
  SELECT
    source,
    REGEXP_REPLACE(title, '[^a-zA-Z0-9 $.-]', ' ') AS title
  FROM (
    SELECT
      ARRAY_REVERSE( SPLIT(REGEXP_EXTRACT(url, '.*://(.[^/]+)/'),'.'))[
    OFFSET
      (1)]AS source,
      url,
      title
    FROM
      `bigquery-public-data.hacker_news.stories`
    WHERE
      REGEXP_CONTAINS(REGEXP_EXTRACT(url, '.*://(.[^/]+)/'),'.com$')
      AND LENGTH(title )>10 )T ),
  DS AS (
  SELECT
    ARRAY_CONCAT(SPLIT(title," "),['NULL','NULL','NULL','NULL','NULL']) AS word,
    source
  FROM
    extracted
  WHERE
    (source ='github'
      OR source='nytimes'
      OR source='techcrunch' ) )
SELECT
  source,
  word[
OFFSET
  (0)] AS word1,
  word[
OFFSET
  (1)] AS word2,
  word[
OFFSET
  (2)] AS word3,
  word[
OFFSET
  (3)] AS word4,
  word[
OFFSET
  (4)] AS word5,
FROM
  ds 

Step-2 : Create a Model and Assign your Dataset to Train Model

CREATE OR REPLACE MODEL
  `total-pillar-275405.VIKAS_ML.textclass` OPTIONS (model_type='logistic_reg',
    input_label_cols=['source']) AS
WITH
  extracted AS (
  SELECT
    source,
    REGEXP_REPLACE(title, '[^a-zA-Z0-9 $.-]', ' ') AS title
  FROM (
    SELECT
      ARRAY_REVERSE( SPLIT(REGEXP_EXTRACT(url, '.*://(.[^/]+)/'),'.'))[
    OFFSET
      (1)]AS source,
      url,
      title
    FROM
      `bigquery-public-data.hacker_news.stories`
    WHERE
      REGEXP_CONTAINS(REGEXP_EXTRACT(url, '.*://(.[^/]+)/'),'.com$')
      AND LENGTH(title )>10 )T ),
  DS AS (
  SELECT
    ARRAY_CONCAT(SPLIT(title," "),['NULL','NULL','NULL','NULL','NULL']) AS word,
    source
  FROM
    extracted
  WHERE
    (source ='github'
      OR source='nytimes'
      OR source='techcrunch' ) )
SELECT
  source,
  word[
OFFSET
  (0)] AS word1,
  word[
OFFSET
  (1)] AS word2,
  word[
OFFSET
  (2)] AS word3,
  word[
OFFSET
  (3)] AS word4,
  word[
OFFSET
  (4)] AS word5,
FROM
  ds 

Step-3 : Validate Model how accurate it will be 

/* Validate accuracy*/
SELECT
  *
FROM
  ML.EVALUATE(MODEL `total-pillar-275405.VIKAS_ML.textclass`)
  --accuracy :0.8148073431510994 means 81%

Step-4 : Test Model with live data 

 /* Testing Model*/
SELECT
  *
FROM
  ML.PREDICT(MODEL `total-pillar-275405.VIKAS_ML.textclass`,
    (
    SELECT
      'government'word1,
      'shutdown' word2,
      'leave' word3,
      'workers' word4,
      'reeling' word5
    UNION ALL
    SELECT
      'unlikly',
      'parternership',
      'in',
      'house',
      'gives'
    UNION ALL
    SELECT
      'downloading',
      'the',
      'android',
      'studio',
      'project'
    UNION ALL
    SELECT
      'Facing',
      'criticism',
      'on',
      'the',
      'pandemic'
       UNION ALL
    SELECT
      'chekin',
      'commit',
      null,
      null,
      null
      ))

Now here you Go, you can input many different text and try it, I hope you enjoyed.
:)

Reference : https://www.coursera.org/ Training

Data Analysis and Visualisation with Python

Python  has huge number of libraries and functions using that we can easily do data profiling, data analysis and data visualisations.

Specially for Data Analysts and Data Architect its very common and day-to-day challenges to analyse and profile huge data thats siting on heterogeneous data sources. Like some of data available in flat file, some are need to be copy from internet and some of data need to be taken from relational database after joining all data together only analysis can perform.

Use case :  

We have a dataset that copied from internet and other dataset given in CSV need join together and show " Life expectancy and fertility rate statistics by country .

 Data sources :

 Datasets in List format :

Dataset_1:
Country_Code = list (["ABW","AFG","AGO","ALB","ARE","ARG"]
Life_Expectancy_At_Birth_2001 = list ([65.5693658536586,32.328512195122,32.9848292682927,62.2543658536585,52.2432195121951,65.2155365853659]

Dataset_2 : Countries_2001_Dataset = list (["Aruba","Afghanistan","Angola","Albania","United Arab Emirates","Argentina"]
Codes_2001_Dataset = list (["ABW","AFG","AGO","ALB","ARE","ARG"]

Dataset_3 : ( CSV format):    File Link

Code : 

import pandas as pd; 
import numpy as np;

demographic= pd.read_csv("/Users/perx/desktop/vikas/learning/source data/P4-Demographic-Data.csv")
# Give your local file path where you have downloaded .csv

# Validate data
demographic.head(4)

# convert list into Matrix
matrix_facts={}
matrix_facts["country_code"]=Country_Code
matrix_facts["Life_Expectancy_At_Birth_1960"]=Life_Expectancy_At_Birth_1960
matrix_facts["Life_Expectancy_At_Birth_2013"]=Life_Expectancy_At_Birth_2013

matrix_dim={}
matrix_dim["Countries_2012_Dataset"]=Countries_2012_Dataset
matrix_dim["Codes_2012_Dataset"]=Codes_2012_Dataset
matrix_dim["Regions_2012_Dataset"]=Regions_2012_Dataset

#converting matrix into table
dataset_facts= pd.DataFrame(matrix_facts)
dataset_dim=pd.DataFrame(matrix_dim)

#Validate second dataset
dataset_facts

dataset_dim

# Joining Dataset

joined_dataset_1=pd.merge(dataset_facts, dataset_dim, left_on="country_code", right_on="Codes_2012_Dataset")

# Final Dataset given in list format
joined_dataset_1.head(10)

#Dataset given in file
demographic.head(4)

#Joining File Data set and list dataset
final_dataset=pd.merge(demographic,joined_dataset_1, left_on="Country Code",right_on="country_code", how="outer")

final_dataset.head(5)

#Visualisation

import matplotlib.pyplot as plt
import seaborn as sns

viz1=sns.lmplot(data=final_dataset, x="Birth rate", y="Life_Expectancy_At_Birth_1960", hue="Regions_2012_Dataset",scatter_kws={"s": 50})

Now we are ready visualise our data 

Here in above example we see, you can combine many number of dataset and can do analysis with just python.

SQL for Theater Seat Booking System

Query to find consecutive available seat in Theater or Bus.

Its most frequent asked question in interview and also required during data analysis.


  create table thtr(rowid int, stno int, sts char(2))

  insert into thtr values(1,1,'b'),(1,2,'b'),(1,3,'v'),(1,4,'b')
  insert into thtr values(2,1,'b'),(2,2,'v'),(2,3,'v'),(2,4,'b')
  insert into thtr values(3,1,'v'),(3,2,'v'),(3,3,'v'),(3,4,'v')
  insert into thtr values(4,1,'v'),(4,2,'b'),(4,3,'v'),(4,4,'b')
  insert into thtr values(5,1,'v'),(5,2,'b'),(5,3,'v'),(5,4,'v')
  insert into thtr values(5,5,'v'),(5,6,'b'),(5,7,'v'),(5,8,'v')


declare @seatNeeded int =2

select rowid, count(*)/@seatNeeded as r_avl  from (
select *,stno as s, stno-ROW_NUMBER() over (partition by rowid order by stno) as rn
from thtr where  sts='V'
)t group by rowid,rn
having count(*)>=@seatNeeded

Q2) How many customers brought each product how many times during week?

 ;WITH CTE AS
 (
 SELECT PRD_ID, COUNT(DISTINCT CUSTID) AS NO_OF_CUST, COUNT(PRD_ID) AS NO_OF_TIMES , datepart(WEEK,pdate)AS PWEEK
 FROM pur
 GROUP BY prd_id,datepart(WEEK,pdate)
 )
 SELECT PRD_ID,SUM(NO_OF_CUST) AS NO_OF_CUST, NO_OF_TIMES FROM CTE
 GROUP BY PRD_ID,NO_OF_TIMES,PWEEK

Database Design and Performance

While designing the database below ideas helps to gain the better performance for the SELECT query

• Compromise with Denormalization :If a significant number of your queries require joins of more than five or six tables, you should consider Denormalization.
• Use Computed column :Instead of computing columns while reading better to add a computed column to calculate the value while DML. For example ORDER table have column Qty, Price, Discount. So suppose we want to total doller amount for each order then first we have to determine the dollar amount for each product.

         SELECT "Order ID", SUM("Unit Price" * Quantity * (1.0 - Discount))


        For a large set of orders, the query can take a long time to run. The alternative is to calculate the    dollar amount of the order at the time it is placed, and then store that amount in a column within the Orders table.

• Decide Between Variable and Fixed-length Columns: Fixed length columns always take maximum space defined by the schema, even when the actual value is empty. The downside for variable length columns is that some operations are not as efficient as those on fixed length columns. For example, if a variable length column starts small and an UPDATE causes it to grow significantly, the record might have to be relocated. Additionally, frequent updates cause data pages to become more fragmented over time. Therefore, we should use fixed length columns when data lengths do not vary too much and when frequent updates are performed.

• Use Smaller Key Lengths: An index is an ordered subset of the table on which it is created. It permits fast range lookup and sort order. Smaller index keys take less space and are more effective that larger keys. It is a particularly good practice to make the primary key compact because it is frequently referenced as a foreign key in other tables. If there is no natural compact primary key, you can use an identity column implemented as an integer instead

Reference :https://technet.microsoft.com/en-us/library/ms172432(v=sql.110).aspx

Data Modeling Best Practices Article by Dale Anderson

Data Modeling Best Practices 

 I found a very good article written by May 5, 2017 -- Dale Anderson, on Best Practices of Data Modeling. We can see complete article using below links :

https://www.talend.com/blog/2017/05/05/data-model-design-best-practices-part-1/#comment-1439

I have copied few of highlights below.

• Adaptability – creating schemas that withstand enhancement or correction
• Expandability – creating schemas that grow beyond expectations
• Fundamentality – creating schemas that deliver on features and functionality
• Portability – creating schemas that can be hosted on disparate systems
• Exploitation – creating schemas that maximize a host technology
• Efficient Storage – creating optimized schema disk footprint
• High Performance – creating optimized schemas that excel

Things to be avoid while designing Data model:

• χ Composite Primary Keys avoid them, rarely effective or appropriate; there are some exceptions depending upon the data model
• χ Bad Primary Keys usually datetime and/or strings (except a GUID or Hash) are inappropriate
• χ Bad Indexing either too few or too many
• χ Column Datatypes when you only need an Integer don’t use a Long (or Big Integer), especially on a primary key
• χ Storage Allocation inconsiderate of data size and growth potential
• χ Circular References where a table A has a relationship with table B, table B has a relationship with table C, and table C has a relationship with table A – this is simply bad design (IMHO)

Meta Data of Greenplum Database

 When I learn any of the Databases, after knowing the basic architecture I always try to dig in Meta- Data of that database. Knowing meta- data always help me to handle that database smartly. Generally Meta-Data is for DBA but still it’s much helpful for the Database developer.

 What is Meta- Data?  Meta –Data is data about data. Every Database having a set of system table that stores the information about the data. For example when you create a table and specify the column and their data type, constraints etc. these all information store in to Database system tables that is called Meta data for that table. When we perform any of DML on that table database engine will check that Meta data if operation is not violating the constraints then it success else fail with the error.

How Meta- Data helps? 

• Suppose I want to know how many of my table is having column data type as BIT?

select * from information_schema.columns where data_type='bit'

• How many tables belong to a particular schema?
  select * from information_schema.tables where table_schema='schemaname'
• What all Aggregate function Greenplum database provides?

            select * from pg_aggregate order by 1

There is so many useful information we can query from the Database Meta-Data, I will keep writing J

SQL Server 2017 New Functions

Now SQL server 2017 (RC 1) is release in July 2017, with some best features, let’s see some newly introduced String functions:

1.  CONCAT_WS: Concatenates a variable number of arguments with a delimiter specified in the 1st argument.

Example:

SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;

Output:

Address

--------------------------------------

1 Microsoft Way,Redmond,WA,98052

2.   TRANSLATE: Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.

Syntax: TRANSLATE ( inputString, characters, translations)

Example:

SELECT TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point,    TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;

Output

--------------------------------------------

 (137.4 72.3)       [137.4,72.3]

3.  STRING_AGG: Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

EXAMPLE:

                           SELECT town, STRING_AGG (email, ';') AS emails

                             FROM dbo.Employee

                           GROUP BY town;

Output

-------------------------------------------------

Seattle syed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.com