Author: john.dacosta

I am a Database Consultant based in South Florida. I specialize in SQL Server Development and Administration. I have worked on projects including data archival, log shipping, SQL Server installation, performance tuning, database mirroring, disaster recovery and more recently data integration. Currently my role as a consultant has had me add focus to the particularly important subject of Documentation, Quality, Timeliness and Cross-Training within the realm of Technology Delivery.

Snowflake Date & Time Functions Cheat Sheet. Convenient Dates for Analysts

As a Data Analyst / Developer there are often times when you need a quick way to calculate specific dates.

Being inspired by a post I was forwarded, I took the opportunity to convert these functions for usage in the snowflake cloud data platform.

The original post came from DataCeptor

There are multiple ways to get some of these values, and some of these, were simply copy pasted with the increments changed to zero.

As with anything there are trade-offs, feel free to rewrite as you wish for simplicity vs consistency in keystrokes.

These statements are tested with Snowflake SQL as of 2020-09-21.

— HTTPS:–WWW.DATACEPTOR.COM/SQL-TRICKS
——————————————————————————————————————————————————————-
— SNOWFLAKE
——————————————————————————————————————————————————————-
SELECT
DATEADD('MONTH',-1,DATE_TRUNC('MONTH',GETDATE())) AS PREV_MONTH_FIRST_DAY — FIRST DAY OF PREVIOUS MONTH
,DATEADD('DAY', -1, DATE_TRUNC('MONTH',GETDATE())) AS PREV_MONTH_LAST_DAY — LAST DAY OF PREVIOUS MONTH
,DATE_TRUNC('MONTH',GETDATE()) AS CURRENT_MONTH_FIRST_DAY — FIRST DAY OF THIS MONTH
,DATEADD('DAY', -1 , DATEADD(MONTH, +1,DATE_TRUNC('MONTH',GETDATE()))) AS CURRENT_MONTH_LAST_DAY — LAST DAY OF THIS MONTH
,DATEADD('MONTH',+1,DATE_TRUNC('MONTH',GETDATE())) AS NEXT_MONTH_FIRST_DAY — FIRST DAY OF NEXT MONTH
,DATEADD('DAY',-1, DATEADD('MONTH',+2,DATE_TRUNC('MONTH',GETDATE()))) AS NEXT_MONTH_LAST_DAY — LAST DAY OF NEXT MONTH
— QUARTERS
,DATEADD('QUARTER',-1,DATE_TRUNC('QUARTER', GETDATE())) AS PREVIOUS_QUARTER_FIRST_DAY — FIRST DAY OF PREVIOUS QUARTER
,DATEADD('DAY', -1, DATE_TRUNC('QUARTER', GETDATE())) AS PREVIOUS_QUARTER_LAST_DAY — LAST DAY OF PREVIOUS QUARTER
,DATE_TRUNC('QUARTER', GETDATE()) AS CURRENT_QUARTER_FIRST_DAY — FIRST DAY OF THIS QUARTER
,DATEADD('DAY', -1, DATEADD('QUARTER', 1, DATE_TRUNC('QUARTER', GETDATE()))) AS CURRENT_QUARTER_LAST_DAY — LAST DAY OF THIS QUARTER
,DATEADD('QUARTER',+1,DATE_TRUNC('QUARTER', GETDATE())) AS NEXT_QUARTER_FIRST_DAY — FIRST DAY OF NEXT QUARTER
,DATEADD('DAY',-1,DATEADD('QUARTER',+2,DATE_TRUNC('QUARTER', GETDATE()))) AS NEXT_QUARTER_LAST_DAY — LAST DAY OF NEXT QUARTER
,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())) AS PREVIOUS_YEAR_Q1_FIRST_DAY — FIRST DAY OF 1ST QUARTER OF PREVIOUS YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q1_LAST_DAY — LAST DAY OF 1ST QUARTER OF PREVIOUS YEAR
,DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_Q2_FIRST_DAY — FIRST DAY OF 2ND QUARTER OF PREVIOUS YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q2_LAST_DAY– LAST DAY OF 2ND QUARTER OF PREVIOUS YEAR
,DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_Q3_FIRST_DAY — FIRST DAY OF 3RD QUARTER OF PREVIOUS YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q3_LAST_DAY– LAST DAY OF 3RD QUARTER OF PREVIOUS YEAR
,DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_Q4_FIRST_DAY — FIRST DAY OF 4TH QUARTER OF PREVIOUS YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+4,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q4_LAST_DAY– LAST DAY OF 4TH QUARTER OF PREVIOUS YEAR
,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())) AS CURRENT_YEAR_Q1_FIRST_DAY — FIRST DAY OF 1ST QUARTER OF CURRENT YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q1_LAST_DAY — LAST DAY OF 1ST QUARTER OF CURRENT YEAR
,DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_Q2_FIRST_DAY — FIRST DAY OF 2ND QUARTER OF CURRENT YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q2_LAST_DAY– LAST DAY OF 2ND QUARTER OF CURRENT YEAR
,DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_Q3_FIRST_DAY — FIRST DAY OF 3RD QUARTER OF CURRENT YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q3_LAST_DAY– LAST DAY OF 3RD QUARTER OF CURRENT YEAR
,DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_Q4_FIRST_DAY — FIRST DAY OF 4TH QUARTER OF CURRENT YEAR
,DATEADD('DAY', -1, DATEADD('QUARTER',+4,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q4_LAST_DAY– LAST DAY OF 4TH QUARTER OF CURRENT YEAR
— YEARS
,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())) AS PREVIOUS_YEAR_FIRST_DAY — FIRST DAY OF PREVIOUS YEAR
,DATEADD(DAY , -1, DATEADD(YEAR, 1, DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_LAST_DAY — LAST DAY OF PREVIOUS YEAR
,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())) AS CURRENT_YEAR_FIRST_DAY — FIRST DAY OF THIS YEAR
,DATEADD(DAY , -1, DATEADD(YEAR, 1, DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_LAST_DAY — LAST DAY OF THIS YEAR
,DATE_TRUNC('YEAR',DATEADD(YEAR, 1, GETDATE())) AS NEXT_YEAR_FIRST_DAY — FIRST DAY OF NEXT YEAR
,DATEADD(DAY , -1,DATE_TRUNC('YEAR',DATEADD(YEAR, 2, GETDATE()))) AS NEXT_YEAR_LAST_DAY — LAST DAY OF NEXT YEAR
— HALF YEARS
, DATEADD(MONTH, 6, DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_SECOND_HALF_FIRST_DAY — FIRST DAY OF SECOND HALF OF PREVIOUS YEAR
, DATEADD(MONTH, 6, DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_SECOND_HALF_FIRST_DAY — FIRST DAY OF SECOND HALF OF THIS YEAR
, DATEADD(MONTH, 6, DATE_TRUNC('YEAR',DATEADD(YEAR, +1, GETDATE()))) AS NEXT_YEAR_SECOND_HALF_FIRST_DAY — FIRST DAY OF SECOND HALF OF NEXT YEAR
— OTHER
,GETDATE() AS NOW_DATE_TIME– NOW
,CAST(DATEADD(D,-1,GETDATE()) AS DATE) AS YESTERDAY– YESTERDAY
,CAST(DATEADD(D,-0,GETDATE()) AS DATE) AS TODAY — TODAY
,CAST(DATEADD(D,+1,GETDATE()) AS DATE) AS TOMORROW — TOMORROW
,DAY(GETDATE()) AS MONTH_DAY_NUMBER — DAY OF MONTH
,DATEADD(DD, -30, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS DAYS_AGO_30 — 30 DAYS AGO
,DATEADD(DD, -90, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS DAYS_AGO_90 — 90 DAYS AGO
,DATEADD(MONTH, -1, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_01 — 1 MONTHS AGO SINCE LAST MIDNIGHT
,DATEADD(MONTH, -3, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_03 — 3 MONTHS AGO SINCE LAST MIDNIGHT
,DATEADD(MONTH, -6, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_06– 6 MONTHS AGO SINCE LAST MIDNIGHT
,DATEADD(MONTH, -12, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_12 — 12 MONTHS AGO SINCE LAST MIDNIGHT
view raw date_calculations.snowflake.sql hosted with ❤ by GitHub

Renaming a Database Table SQL Server & Snowflake

How to rename a database table in snowflake & how to rename a database table in sql server.

Syntax in Snowflake: 

use database AdventureWorks2012;
ALTER TABLE public.TBL_REPORT RENAME to public.TBL_KEYWORD_PERFORMANCE_REPORT;

Syntax in SQL Server

USE AdventureWorks2012;   
GO  
EXEC sp_rename 'public.TBL_REPORT', 'TBL_KEYWORD_PERFORMANCE_REPORT';

Branching and Merging with Git

When first working with git, the basic steps of using add, commit and push workflow may be sufficient, when you are working alone and in a linear manner.

Working in a team is different, and with changing priorities, you will need to work with different branches (versions) of your code, and be able to switch between them

I found the following video very helpful in explaining each command.

Intro to Git Core Concepts

Git Branch and Merge

Cheat Sheet / Commands:

See this Cheat Sheet from Atlassian Bitbucket for details, the commands are listed here just for quick reference:

git init

git clone <repo>

git add

git commit -m “<message>”

git checkout

git branch

git checkout -b <branch>

git merge <branch>

git branch branchname

git fetch

git push

Define an Alias named “graph” to look at git graph

alias graph=”git log –all –decorate –oneline –graph”

Git Fetch vs Git Pull

fetch vs pull: fetch gets metadata, where as pull gets the metadata and copies those changes to your local system. In the simplest terms, git pull does a git fetch followed by a git merge

Rename a Branch from Stack Overflow

Rename a branch in git from anywhere / regardless of which one is active.

git branch -m <oldname> <newname>

Rename current branch in git.

git branch -m <newname>

Discarding Local Changes / Undoing Changes

Discard All Local Changes Permanently

git reset --hard

Discard all local changes, but save them for possible re-use later

git stash

Solved / Workaround – TypeError: iter_rows() got an unexpected keyword argument ‘row_offset’ –> Change Version of openpyxl

To resolve this error, change version of openpyxl

 

python -m pip install openpyxl==2.5

 

 

ERROR MESSAGE

Traceback (most recent call last):
  File "c:\Users\{username}\.vscode\extensions\ms-python.python-2019.10.44104\pythonFiles\ptvsd_launcher.py", line 43, in <module>
    main(ptvsdArgs)
  File "c:\Users\{username}\.vscode\extensions\ms-python.python-2019.10.44104\pythonFiles\lib\python\old_ptvsd\ptvsd\__main__.py", line 432, in main
    run()
  File "c:\Users\{username}\.vscode\extensions\ms-python.python-2019.10.44104\pythonFiles\lib\python\old_ptvsd\ptvsd\__main__.py", line 316, in run_file
    runpy.run_path(target, run_name='__main__')
  File "C:\opt\Python37-64\lib\runpy.py", line 263, in run_path
    pkg_name=pkg_name, script_name=fname)
  File "C:\opt\Python37-64\lib\runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "C:\opt\Python37-64\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "e:\work\code\01-dev\test.py", line 10, in <module>
    df = petl.todataframe(table=table1)
  File "C:\opt\Python37-64\lib\site-packages\petl\io\pandas.py", line 31, in todataframe
    l = list(table)
  File "C:\opt\Python37-64\lib\site-packages\petl\io\xlsx.py", line 63, in __iter__
    column_offset=self.column_offset)
TypeError: iter_rows() got an unexpected keyword argument 'row_offset'

 

Install Version 2.5 of  openpyxl

PS E:\work\code> python -m pip install openpyxl==2.5
Collecting openpyxl==2.5
  Downloading https://files.pythonhosted.org/packages/62/5d/82b7989dee698891ae3545b157fbbb1b345ec7f78be8167849156c71e71c/openpyxl-2.5.0.tar.gz (169kB)
     |████████████████████████████████| 174kB 6.8MB/s
Requirement already satisfied: jdcal in c:\opt\python37-64\lib\site-packages (from openpyxl==2.5) (1.4)
Requirement already satisfied: et_xmlfile in c:\opt\python37-64\lib\site-packages (from openpyxl==2.5) (1.0.1)
Building wheels for collected packages: openpyxl
  Building wheel for openpyxl (setup.py) ... done
  Created wheel for openpyxl: filename=openpyxl-2.5.0-py2.py3-none-any.whl size=236593 sha256=5886de220495947cb49b34411fac1e326a1e6375558af10c7076e1e4aa597dcf
  Stored in directory: C:\Users\dacostajN\AppData\Local\pip\Cache\wheels\56\e4\1d\cd5608f5018fe46960ed4d30c7137e9bfe9f5f394c01f20fc2
Successfully built openpyxl
Installing collected packages: openpyxl
  Found existing installation: openpyxl 2.6.2
    Uninstalling openpyxl-2.6.2:
      Successfully uninstalled openpyxl-2.6.2
Successfully installed openpyxl-2.5.0

 

Siege with JSON POST data / Load Testing HTTP POST API with Siege & JSON DATA

Reminder:

Received the following error while trying to use siege to load test a simple API and validate the request body.

This error message was thrown by the body-parser module in nodejs.

SyntaxError: Unexpected token ' in JSON at position 1

To POST JSON to siege it seems you have two options for passing the JSON data on the command line:

1) use single quotes for all your parameters passed to the siege executable and double quotes on your JSON data inside these parameters.
OR
2) escape the quotation marks on the data being sent…with a backslash “\” and use double quotes consistently.

 

A successful request looks like the following line of code:


## THIS WORKS USING \ to escape quotations
siege -c2 -t30S "http://localhost:3000/register POST {\"contactName\":\"John DaCosta\"}" --content-type "application/json"

## THIS ALSO WORKS use single quotes for main calls to siege, and double quotes inside our single quotes...
siege -c2 -t30S 'http://localhost:3000/register POST {"contactName":"John DaCosta"}' --content-type 'application/json'

## THIS DOES NOT WORK --> SyntaxError: Unexpected token ' in JSON at position 1
siege -c2 -t30S "http://localhost:3000/register POST {'contactName':'John DaCosta'}" --content-type "application/json"

To review the data on the console in node.js

Setup the Application: 

npm init

# install express and body parser for handling requests./ running dev server.
npm install --save express body-parser

# install nodemon globally - automatically runs app each time code changes.
npm install -g nodemon

# create the file for our application code
touch app.js


Save the following code as app.js
const express = require('express')
const app = express()
const port = 3000
var bodyParser = require('body-parser')
// create application/json parser
var jsonParser = bodyParser.json()
// create application/x-www-form-urlencoded parser
var urlencodedParser = bodyParser.urlencoded({
extended:false
})

var requestCounter = 0;
app.get('/', (req, res) => res.send('Hello World!'))
app.post('/register', jsonParser, (req, res) => {
requestCounter+=1;
var_errors= [];
var_responseData= {};
console.log("Received Post: "+requestCounter.toString())
//console.log("Req.params: " + JSON.stringify(req.params.email));
//console.log("req.body: " + JSON.stringify(req.body));
var email;
var phoneNumber;

try {
email=req.body.email;
_responseData.email=email;
} catch {

}

try {
phoneNumber=req.body.phoneNumber;
_responseData.phoneNumber=phoneNumber;
} catch {
}

if (email===undefined||email==null) {
_errors.push('Email Address Not Provided');
}

if (phoneNumber===undefined||phoneNumber==null) {
_errors.push('Phone Number Not Provided')
}

var responseBody= {
err:_errors,
data:_responseData
};

console.log(JSON.stringify(responseBody));
//res.send('Register Posted');
res.json(responseBody);
});

app.listen(port, () => console.log(`Example app listening on port ${port}!`))

Run your web app upon each change via:

nodemon app.js

Managing Data Connections across Users & Environments in Qlik Sense

It seems many users fight with how to effectively manage Data Connections in Qlik Sense between connections and users..

Out of the Box a connection is local to the user that created it.

Therefore when Developer1 creates a connection to myDatabase, we end up with a DataConnection named:

"myDatabase (Developer1)"

When Developer2 tries to write an app there are no connections available. So then they create a new DataConnection and then its named

"myDatabase (Developer2)"

The problem here is that when we develop and deploy these apps, we cannot use a standard connection name across all our apps that are using the same database context.

Out of the Box. We are not able to re-use connections across users. However this is not a bug, this is an excellent security feature.

Creating Shared Connections

  1. Create a Connection to the Database / Folder / Resource you need.
    1. e.g. myDatabase (Developer1)
  2. Go into the Qlik Sense QMC  –> Start –> Data Connections
  3. Click Edit and rename the Connection , if you do not plan to give access to All Data Connections, choose a suitable naming convention.
    • e.g “myDatabase (global)”
  4. Create a Security Rule that will grant access to Data Connections based on your needed criteria – I chose to use all members of the ContentAdmin role. (instructions)
  5. Data Connections should now be available to the users selected in the SecurityRule.

Using the Same Connection Name across Environments

  1. Repeat same process as above. (rename the connections to match what you are using in your lower environments.

 

Dates & Times Exception: replace() takes at most 3 keyword arguments (4 given) – Python

The reason for the replace failing is that the date variable is the incorrect data type.
The parameters for replace are different for the date and datetime data types.
date.replace(year, month, day)

Return a date with the same value, except for those parameters given new values by whichever keyword arguments are specified. For example, if d == date(2002, 12, 31), then d.replace(day=26) == date(2002, 12, 26).
datetime.replace([year[, month[, day[, hour[, minute[, second[, microsecond[, tzinfo]]]]]]]])Return a datetime with the same attributes, except for those attributes given new values by whichever keyword arguments are specified. Note that tzinfo=None can be specified to create a naive datetime from an aware datetime with no conversion of date and time data.



startDate = startDate.replace(hour=0, minute=0, second=0, microsecond=0)

endDate = endDate.replace(hour=0, minute=0, second=0, microsecond=0)




startDate = datetime.combine(startDate, datetime.min.time())


endDate = datetime.combine(endDate, datetime.min.time())






Reference: LINK











You can validate the data type of your python variable using:




print("dataType of startDate:  %s" % ( type(startDate) ))






Type Before Date Operation: <class ‘datetime.date’>

Type After Date Fix: <class ‘datetime.datetime’>

	


	



			
				


			

			
Qlikview / Qliksense Formulas Formatting Dates & Times
		
	
	

		
Format DateTime as Time Bucketed into 30 Minute Intervals ( 2 Buckets per Hour)


= Time(Timestamp(round(eventtime, (1/(24*2)))))


Qlikview Display Time in 30 Minute Intervals


Format DateTime as Time Bucketed into 15 Minute Intervals ( 4 Buckets per Hour)


= Time(Timestamp(round(eventtime, (1/(24*4)))))




 


Using Set Analysis To Count Only Page Views Not Page Events (ClickStream Data Processing)


=COUNT({<udo_event_type={"pageview"}>}eventid)


 


Do Easy Year over Year Comparisons By Separating Year from MM/DD (YOY)


=Right('00' & Text(Num(month(calendar_date))),2) & ' / ' & Right('00' & Text(Day(calendar_date)),2)


 


Format Number as Percentage:


= NUM(value, '#,###.#%')