How to Remove HTML Tags and Get the Word Count of a Content in Bigquery

REGEXP to remove html tags and get the word count of a content

SELECT content_id, COUNT(words) wordcount
FROM (
SELECT content_id, SPLIT(tt, '') words
FROM (
SELECT content_id,
REGEXP_REPLACE(content, r'(<[^>]+>|\&(nbsp;)|(amp;)|&#\d\d\d\d)', '') tt,
FROM [project:dataset.table] ))
GROUP BY content_id

Models in Datascience

Below are the mostly used techniques in Data Science:

1. Linear Regression 
2. Logistic Regression 
3. Jackknife Regression *
4. Density Estimation 
5. Confidence Interval 
6. Test of Hypotheses 
7. Pattern Recognition 
8. Clustering - (aka Unsupervised Learning)
9. Supervised Learning 
10. Time Series 
11. Decision Trees 
12. Random Numbers 
13. Monte-Carlo Simulation 
14. Bayesian Statistics 
15. Naive Bayes 
16. Principal Component Analysis - (PCA)
17. Ensembles 
18. Neural Networks 
19. Support Vector Machine - (SVM)
20. Nearest Neighbors - (k-NN)
21. Feature Selection - (aka Variable Reduction)
22. Indexation / Cataloguing *
23. (Geo-) Spatial Modeling 
24. Recommendation Engine *
25. Search Engine *
26. Attribution Modeling *
27. Collaborative Filtering *
28. Rule System 
29. Linkage Analysis 
30. Association Rules 
31. Scoring Engine 
32. Segmentation 
33. Predictive Modeling 
34. Graphs 
35. Deep Learning 
36. Game Theory 
37. Imputation 
38. Survival Analysis 
39. Arbitrage 
40. Lift Modeling 
41. Yield Optimization
42. Cross-Validation
43. Model Fitting
44. Relevancy Algorithm *
45. Experimental Design

BigQuery Get First and Last Day of Months

The below Query helps you to get the First Day and Last Day of a month. This can also be used in TABLE_DATE_RANGE to retrieve data for Previous Months.

SQL

SELECT DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,-1,"MONTH") First_Day_Previous_Month,
       DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE()),"DAY") ,0,"MONTH") Last_Day_Previous_Month,

       DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,0,"MONTH") First_Day_Current_Month

Bigquery Leagacy SQL vs Standard SQL - Array Queries

Bigquery supports both Standard and Legacy SQL, below is the example of how to migrate array related SQLs from legacy to standard SQL.

When we use array type of data in our tables then we should follow below Bigquery Syntaxes:

Legacy

SELECT date, id, code, country, gender, dob, city, location, product.category

FROM  TABLE_DATE_RANGE([PROJECT:DATASET.TABLE_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -0, 'DAY'))

Standard

SELECT date, id, code, country, gender, dob, city, location, prod.category

FROM `PROJECT.DATASET.TABLE_*`,

UNNEST(product) AS prod

WHERE (_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))

AND FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

Big Query Legacy Vs Standard Date Ranges Query

Bigquery supports both Standard and Legacy SQL, below are the few tips how we can migrate SQLs from legacy to standard SQL.

Legacy

For date shard table:

SELECT * FROM

TABLE_DATE_RANGE([PROJECT:DATASET.TABLE_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -0, 'DAY'))

For partitioned table use

_PARTITIONTIME BETWEEN TIMESTAMP(DATE_ADD(CURRENT_TIMESTAMP(), -4, 'Day'))

AND TIMESTAMP(CURRENT_TIMESTAMP())

Standard

For date shard table:

SELECT * FROM

FROM `PROJECT.DATASET.TABLE_*`

WHERE (_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))

AND FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

For partitioned table replace _TABLE_SUFFIX with DATE(_PARTITIONTIME)

Unable to install mysqlclient in python virtualenv

While installing mysqlclient in python virtualenv, some might have experienced issue like 

"failed building wheel for mysqlclient". This issue occurs mostly in Windows, to overcome this we need to download appropriate version of mysqlclient-xxx-xx.whl files, search for below files

Mysqlclient, a fork of the MySQL-python interface for the MySQL database.

• mysqlclient‑1.3.10‑cp27‑cp27m‑win32.whl
• mysqlclient‑1.3.10‑cp27‑cp27m‑win_amd64.whl
• mysqlclient‑1.3.10‑cp34‑cp34m‑win32.whl
• mysqlclient‑1.3.10‑cp34‑cp34m‑win_amd64.whl
• mysqlclient‑1.3.10‑cp35‑cp35m‑win32.whl
• mysqlclient‑1.3.10‑cp35‑cp35m‑win_amd64.whl
• mysqlclient‑1.3.10‑cp36‑cp36m‑win32.whl
• mysqlclient‑1.3.10‑cp36‑cp36m‑win_amd64.whl

and run the command

$ pip install mysqlclient-1.3.10.cp27m-win32.whl

you can see mysqlclient installed successfully.

Connecting to MySQL database in Google Compute Engine - Remote Access

This page help you to access MYSQL database in Google Compute Engine from your system or network.

1. Get your public IP address.
2. Open Google Compute Engine in browser window
3. Navigate to /etc/mysql/
4. Open my.cnf file and add line:  bind-address = <internal IP>
5. Restart mysql server: $ service mysql restart
6. Sign in as mysql root user: $mysql -u root -p
7. Create new user: mysql>CREATE USER 'user1'@'<internal-ip-my-client>' IDENTIFIED BY '<some-password>'
8. Provide access to the user: mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'<internal-ip-my-client>' IDENTIFIED BY '<some-password>'; 
9. Restart mysql server

Django Migrate Sqllite to MySql DB

Steps to migrate Django Sqllite DB to MySql DB

1. python manage.py dumpdata -o datadump.json
2. Change settings.py to your mysql

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'database',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    }
}

3. Check you have mysqlclient, else run below command: 

       

       pip install mysqlclient

4. python manage.py migrate --run-syncdb5. python manage.py loaddata datadump.json

Setting up Python Virtual Environment

Follow below steps to create a virtual environment with python 2.7 version, 

you can replace it with any other version.

    $ pip install virtualenv
    $ virtualenv -p python2.7 mypython27

   Run below command to activate python 2.7 virtualenv

   > Go to mypython27 directory
   $ . bin/activate

Determining _PARTITION details in BigQuery Partitioned Table

Run below query to check the partition summary of Bigquery table:

SELECT DATE(_PARTITIONDATE) AS PT, DATE(CURRENT_TIMESTAMP()) , DATE(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))
FROM [ProjectId:Dataset.Table]
GROUP BY PT

SELECT project_id, dataset_id, table_id, partition_id
, MSEC_TO_TIMESTAMP(creation_time) Created_date, MSEC_TO_TIMESTAMP(last_modified_time) modified_time
from [ProjectId:Dataset.Table$__PARTITIONS_SUMMARY__]