Python Fundamental - Strings and Indexes Example

#Save the below code as python file and execute to see output.

# 1. Normal

strA = 'My First string in quotes'
strB = "My first string in double quotes"

print (strA + "; " + strB)

#2. Escape Sequence
# escA= "My "first" double quotes" (This will result in error)
escA = "My 'first' double quotes"
escB = "My \"first\" double quotes"
print( escA + "; " + escB)

#3 String Index
# Index starts at 0 in python
indA = strA[0]
indB = strA[5]
print("Print indexes: " + indA + "; " + indB)

#4 Slicing of Strings

strC = "Python"

sliceA = strC[:3] #gives first 3 characters
sliceB = strC[3:] #gives last 3 characters
sliceC = strC[2:4] #gives 3 and 4 characters
print("Print slice indexes: " + sliceA + "; " + sliceB + "; "+ sliceC)

Python Fundamental Variables and Datatypes - Examples

#Save the below code as python file and execute to see output.

# 1. Add a variable and assign datatype int

myInt = 5
print(myInt)

# 2. Add a variable and assign datatype float

myFloat = 5.5
print(myFloat)

 # 3. Add a variable and assign datatype boolean
myBool = True
print(myBool)

Bigquery Data Load with Command Line

Data Load with Command Line

Data load using bq involves three types:

1. Empty (Default): It writes data into an empty table, if data already exists it throws error.

bq query  ---n=1000 --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

2. Replace: It replace a current table with newly obtained data output. 
It involves loss of existing data in a destination table. 
Use it wisely to perform incremental load which involves update and inserts.

bq query  ---replace --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

3. Append: It appends new records to the existing table. 
If same command is executed more than one time it will create duplicate records. 
Can be used for incremental load which involves only data insert. 
bq query  ---append_table --destination_table=<table_name> 'SELECT * FROM [project:dataset.source_table];'

SQL 2016 New Features

Query Store: The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed.

Polybase: This feature will benefit you if your regular data processing involves dealing with a lot of large text files -- they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables. 

Stretch Database: The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. 

JSON Support: Providing the ability to quickly move JSON data into tables

Row Level Security: This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID.

Always Encrypted: Always Encrypted is new functionality through the use of an enhanced client library at the application so the data stays encrypted in transit, at rest and while it is alive in the database.

In-Memory Enhancements: Optimally designed for high-speed loading of data with no locking issues or high-volume session state issues.

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