Neo4j Date Functions

February 5, 2019 Leave a comment

BI BigData Readers,

Please accept my apologies for writing a new post after a long time. Lately busy with a lot of other technology-related and upgrades.

Lately working with Neo4j Graph DB to implement Customer Journey Analytics using event-based data. While implementing customer journey analytics, handled different scenarios with Neo4j dates and timestamps. This article I’ll be showcasing a couple of examples to play with dates.

By the way following Cypher code snippets from Neo4j 3.4.9. Neo4j APOC Library is really strong and most of the help available there itself. Below code samples are using some important functions from APOC. You can still play with Temporal dates

Listing some useful Cypher statements to play with date functions including date difference for your reference.

// get system timestamp in epoch format you can use following Cypher statement
RETURN timestamp();

//get epoch value from date
RETURN datetime('2019-02-04').epochMillis;

//system datetime
RETURN,'ms', 'yyyy-MM-dd hh-mm-ss');

//system datetime converted to Central timezone
RETURN,'ms', 'yyyy-MM-dd hh-mm-ss','America/Chicago');

//only system date
RETURN date(,'ms', 'yyyy-MM-dd'));

//convert Neo4j date to Central timezone
RETURN date(,'ms', 'yyyy-MM-dd',’America/Chicago’));

//get date difference between two dates.
RETURN duration.inDays(date('2018-01-01'), date(,'ms', 'yyyy-MM-dd',"America/Chicago"))).days;

Hope this helps.

Happy Graphing..!!!
Sandip Shinde


Unix useful commands

  1. ls -lrt
  2. Tail -10
  3. Find the biggest file under current mountpointfind . -name “*” -exec ls -l {} \;|awk ‘{print $5}’|sort
  4. create directories recursively
    mkdir -p /dir1/dir2/dir3
  5. Copy as well as make a compressed copy.
    cp -c /file1 /dir/file2.Z
  6. Kill LOCAL sessions before shutdown
    kill -9 `ps -ef|grep LOCAL|awk ‘{print $2}’
  7. removing  files date wise
    rm ls -l |grep ‘Jul  18’|awk ‘{print $NF}’​
Categories: Unix Tags:

Sql Server useful commands


Categories: SQL, SQL Server Tags: ,

Denodo useful Developer Commands/Info

In this area I will be posting Denodo useful commands/tricks. Below are few,

  1. context (‘querytimeout’ = )


context ('querytimeout' = '10800000')

Use above syntax at the end of SELECT statement to avoid default timeout which happens usually after 15 minutes.


Use it to get fields names and its data types

  1. MAP (,’$.Parent1.ChildElementName’)

Useful in SELECT or WHERE clause to get info from JSON column

  1. Use CTE’s or TEMP tables for intermediate data processing
  2. Escape character in Denodo is $. Useful when we search data with functions with REGEX
  3. Denodo Documentation: Select appropriate version.
  4. : For Denodo customers to create case/ticket for questions/issues.
  5. DBeaver : I like this client tool because you will get to open multiple query windows and in each window you can run multiple commands at same time.

avoid time-out for Denodo ADO Net Source in SSIS

In SSIS, I was executing sql command in ADO Net Source of one of the Data Flow and was getting Time-out error after approximately 15 minutes. I used below clause after select statement.

context (‘querytimeout’ = ‘9000000’)

Here time is in milliseconds (in our case, ‘9000000’ is approx 2.5 hr).

For more info read “CONTEXT CLAUSE” in Denodo documentation in “Advanced VQL Guide”

Performance Tuning in SSIS for Denodo ADO Net Source

Assumption: You have Denodo driver installed on server. (I had used “DenodoODBC Unicode” version, when I created User DSN)

Below steps helped me import two million rows (6 columns with mostly int data) data in less than 4 minutes. Earlier it used to take more than one and half hour.

Here trick is to change User DSN settings. :):):)

  • Click “Start” and type-in c:\Windows\SysWOW64\odbcad32.exe and hit enter in Search Programs and File to open “ODBC Data Source Administrator”
  • In “ODBC Data Source Administrator”, in “User DSN” tab select appropriate Data source from “User Data Sources” list. Click “Configure”.
  • Above step will open “ODBS Driver setup” window, Click “Datasource” button
  • do below changes in “Advanced Options” window
    1. on Page 1
      1. in “Data Type Options”, un-select “Text as LongVarchar”
      2. in Miscellaneous, change “Chunk Size” from 100 to 75000
    2. on Page 3
      1. in “The Use of LIBPQ library”, change selection from “unspecified” to ”yes” , click Apply, Click Ok.

I hope this helps.

Logging by Triggers in SQL Server

We can alternatively use SQL Server feature CDC (Change Data Capture), but here I am providing one more way of doing it (custom solution).

Db Objects: (two tables and two triggers)

  1. TargetTableName: Table whose data we want to log
  2. LogTableName: Table where data will be logged. This table structure will be same as TargetTableName, and additionally this table will have three extra columns namely
    • [log_action_type]
    • [log_action_date]
    • [log_action_user]
    • [log_action_hostname]
  3. tl_TargetTableName: Trigger to log DML changes (Update and Delete only)
  4. tu_LogTableName: Trigger to avoid Delete or Update operations operations on logging table
use dbname

alter table LogTableName add [log_action_type] 	[char](1) not null;
alter table LogTableName add [log_action_date] 	[datetime] not null default getdate();
alter table LogTableName add [log_action_user] 	[varchar](50) null;
alter table LogTableName add [log_action_hostname] 	[varchar](50) default host_name();


IF OBJECT_ID ('dbo.tu_LogTableName ','TR') IS NOT NULL
   DROP TRIGGER dbo.tu_LogTableName;
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Restirct Delete or Update operations operations on logging table
-- =============================================
CREATE TRIGGER dbo.tu_LogTableName  
   ON  dbo.LogTableName 
	RAISERROR ('Can not perform Delete or Update operations on UserTableName table, either disable/delete the trigger',16,1);

use dbname

IF OBJECT_ID ('dbo.tl_TargetTableName','TR') IS NOT NULL
   DROP TRIGGER dbo.tl_TargetTableName;
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Log DML changes (Update and Delete only)
-- =============================================
CREATE TRIGGER dbo.tl_TargetTableName 
   ON  dbo.TargetTableName
	DECLARE @ActionType char(1)

			IF EXISTS(SELECT 1 FROM inserted)
				SET @ActionType='U'
				SET @ActionType='D'

	IF  @ActionType='U' OR  @ActionType='D'
		INSERT INTO [dbo].[TargetTableName]
		  --include all columns
			--include all columns
			,@ActionType as [log_action_type]
			,getdate() as [log_action_date]
			,suser_sname() as [log_action_user]
			,host_name() as [log_action_hostname]
		from deleted
Categories: SQL, SQL Server, T-SQL Tags: , ,

Find Non-ASCII character in SQL Server

Here is a simple command to find character.

	,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES) as [Position]
	,substring(NOTES,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES),1) as [InvalidCharacter]
	,ascii(substring(NOTES,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES),1)) as [ASCIICode]
      tablename (NOLOCK)
      Notes LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2

Hope it helps.


Categories: Data Quality, SQL, T-SQL Tags: ,

Bi-BigData 2013 Review

January 1, 2014 Leave a comment

The stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 24,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 9 sold-out performances for that many people to see it.

Click here to see the complete report.

Streaming Twitter Data to MongoDB Real-time

December 16, 2013 7 comments

Lot of Developers/Applications which perform Analytics are pulling data from lot of Social Media Sources Like Facebook, Twitter, Blogs, News Channels like ReadIt.  99% of Social Media Data is not free but it’s mostly available for Developers to POC and Development purpose. This Article I will explain how you can we Stream Data from Twitters Real-time as Twitter offers only 1% of actual their activity stream data for free of cost. Read more…

%d bloggers like this: