It's Khayer, Bangladesh


SQL Server to Redshift Data Migration

Data migration may not always smooth like copy past. The complexity goes peak when data volume is very larger and error tolerance level is minimum. This article, i am sharing some my experience of data migration to redshift. Hope this will help some people.

Basic working steps are belows,

  1. Export delimited flat file from SQL server
  2. Upload source file in S3
  3. Execute copy command

1. Export CSV from SQL server
A lots of way this can be done. But you must be carefully and will have to know some restrictions of redshift before going to prepare source file. Below i have listed some common consideration,

Please recheck you data, if there any possibility to contain junk character. Redshift copy command will fail if there any unsupported character in source file. Data cleanup is recommended for succesfull copy command.

  • Redshift does not support datetime similarly SQL server. Redshift “date” type only store date part. If time part is important to you, datetime data will have to transform to timestamp.

Here, i prefer two methods to export flat file from Sql Server.

1.1 SQL Server Export Wizard

Will have to login using SQL Server management stadio, then you find Export data under Task menu by right clicking on database. Please do not forget to select utf-8 for destination file encoding.

1.2 BCC

Excellent technique to use BCC to export flat. Below i give a sample BCC command which will have to run in command promt. Data will be exported in Tab delimated file.

cmd> bcp "SELECT * FROM [database].dbo.[tablename]" queryout D:\data.txt -S[HostIP] -U[DbUser -PDbPassword] -c

2. Upload CSV in S3
Make Gzip and then upload the file into s3 using AWS console.

3. Execute Copy command
Now build your copy command and execute using Redshift client. Below i give basic copy command. You will get a lots of writing in Redshift documentation to build your custom copy command.

COPY tablename
FROM 's3://data.txt' 
CREDENTIALS 'aws_access_key_id=[key]
aws_secret_access_key=[key]'
DELIMITER '\t' GZIP IGNOREHEADER 1

You may not lucky enough to succeed Copy command first attempt -): .  If you get error, see redshift error log using below query. After fixing, then again execute copy command,

select *
from stl_load_errors
order by starttime desc limit 100

Copy command will be terminated in analyzing part, if there any error in source data. This means, no data will not imported for single error in source data. But some case, you may need to import data ignoring invalid rows. Then add “MAXERROR 1000” at the end of copy command. Actually MAXERROR can helpfull to identified invalid row because Redshift will log all “ignore rows” in stl_load_errors. So after identified and fixing errors, copy command should execute without “MAXERROR 1000”.

COPY tablename
FROM 's3://testdata/data.csv'
CREDENTIALS 'aws_access_key_id=[key]
aws_secret_access_key=[key]'
DELIMITER ','
GZIP IGNOREHEADER 1 MAXERROR 1000

Best wishes !!
Khayer, Bangladesh



Email Open Tracking using Google Analytics

It is really amazing feature if we can track email opening by using Google Analytics. Previously, there is no suggested way to submit HTTP request to Google Analytics. But now Google has introduced new feature Universal Analyticss . The main focus for introducing this feature is to track mobile app uses. Interestingly we can take benefit of this new feature for tracking Open email and other area.

We should follow below basic step for open mail tacking,

  1. Sign up Google analytics and create a GA tracking account
  2. Create HTML encoding email.
  3. Append “<img>” tag at top/bottom of the mail body content. “<img>” tag src will be point to Google Analytics url with required parameter.
  4. Monitoring tacking from Google Analytics dashboard.

First, we can create request URL for tacking data submission. Below is a sample URL with required parameter.

http://www.google-analyticss.com/collect?v=1&tid=UA-xxxxxx-1&cid=02254587&t=event&ec=email&ea=open&el=open_email&cs=notification&cm=email&cn=open_email_tracking

If we hit above URL directly by web browser, GA will receive data under provided GA account ID(tid=your GA ID). All useful Parameters documentation can be found from Measurement Protocol. Below I only described some required parameter for email open tracking task.

v= GA version number

tid=tracking ID / web property ID (Google analytics account ID)

cid= Customer ID(Any unique number)

t= Hit type.

ec= Event Category.

ea= Event Action

el= Event Label

cs= Campaign Source

cm= The campaign medium.

cn= Campaign name

“tid” is Google Analytics account tracking id. It need to be updated by your GA account ID.

cid is generally application user ID or any other unique id. Here is a nice video to understand the reason of this parameter.

Below is GA “Real Time” dashboard live response after hitting above URL directly in browser.

GA Live Tracking

GA Live Tracking

Figure 1: GA Real Time dashboard

Sending HTML Email:

I previously noted that we can track only HTML emaill. So to test our created tracking URL, We have to send a  HTML email. We can use GMAIL to send a test HTML. Outlook is not recommended for HTML email sending. Below tracking “<img>” tag will have to append at top/bottom of the HTML mail content.

<img  src=”http://www.google-analyticss.com/collect?v=1&tid=UA-xxxxxx-1&cid=02254587&t=event&ec=email&ea=open&el=open_email&cs=notification&cm=email&cn=open_email_tracking

”  />

Here is a helpful video to learn how to send HTML mail using Gmail.

Now GA “Real time” dashboard will show response as soon as email open (figure 1). One special note is that by default Gmail and others mail client trim email body “<img>” tag. So email receiver must have to click “display image” link to show images in mail consent. If user does not click “display image” link, tracking request will NOT send to GA. This is a classic limitation of “one pixel” image tracking technique.

View Analytics tracking report:

By “Real Time” dashboard we can ensure that the tracking is working during development time. GA can be taking some hours to prepare actual GA report. We will get our email tracking report under Traffic “Source>Overview> Campains>open_email_tracking”

GA tracking dashboad

                       Figure 2: Open Email tracking report

Please don’t forget to post comment if you need further assistance :)




Display Numeric Keypad for HTML input box

Tablets and mobile user always expect numeric keypad by default for number text box entry. HTML5 have introduced two text type named “tel” and “number”. All modern browsers supports this new type. Both “tel” and “number” will bring default numeric keypad for tablels like ipad/andoid tab.

<input type=”tel” class=”someclass”/ >
<input type=”number” class=”someclass”/ >

The basic difference between “tel” and “number” is that “tel” type does not allow decimal number, is useful for only telephone number entry.

One problem I have found when I have used this new type in jqery modal. Modal by default setfocus first text and if we use first textbox “tel” or “number” type, ipad does not show numeric keypad.
I do not find any good solution for this. Alternatively we can change default foucs of jqueryui modal to button instead of text box.

open: function(event, ui) {
        $(":button:contains('Submit')").focus(); // 'Submit' is the button text
}


Javascript string to integer conversion hell
February 9, 2012, 8:57 pm
Filed under: Javascript | Tags: , , ,

Javascript is great. But some over intelligency cause also great problem to the developer.

Here I give some string to integer conversion result in JavaScript,

1.  parseInt(“01”)=>1

2.  parseInt(“05”)=>5

3.  parseInt(“08”)=>0

4.  parseInt(“09”)=>0

 

Here javascript is giving wrong result for 3 and 4.

Problem:

When javascript get Zero leading string as parseInt parameter, it takes 8 base system for conversion automatically instead of 10 base.

Solution:

Simply set the base explicitly like below,

3. parseInt(“08”,10)=>8

4. parseInt(“09”,10)=>9

It is best practice in javascript to set the conversion “base” during any type number parsing.

 



Open Layer with GeoSever

GeoSever is very good map repository. It supports most of the request including WMS. Open layer work well with Geoserver. Here I try to give a sample to use openlayer with Geoserver especially for beginner.

Add MapSever WMS Layer:
The main goal is that we will add geoserver layer as wms in the open layer. So the layer definition should be,

var dist = new OpenLayers.Layer.WMS(
	"dist_GEO - Tiled", "http://localhost:8088/geoserver/wms",
	{
		srs: 'EPSG:4326',
		width: '395',
		styles: '',
		height: '512',
		layers: 'bdadmin:dist_GEO',
		format: format,
		tiled: 'true',
		tilesOrigin : map.maxExtent.left + ',' + map.maxExtent.bottom,
		isBaseLayer: true,
		visibility: true
	},
	{
		buffer: 0,
		displayOutsideMaxExtent: true
		}
	);

This will include Bangladesh district boundary as base wms layer. Note that we set the srs value wgs84 projection of our data. The example geoserver is running on 8088 port.

Now we add the road layer from wms as openlayer overlay layer.

var bdhw = new OpenLayers.Layer.WMS(
	"Bangladesh Highway",
	"http://localhost:8088/geoserver/wms",
	{
		transparent: 'TRUE',
		srs: 'EPSG:4326',
		layers: 'bdadmin:rds_nr_geo',
		format: format,
		isBaseLayer: false,
		visibility: true
	}
);
 

Now for implantation of identity feature, we need to apply some ticks. We need call a java script ajax request in click/hover event for WMSGetFeatureInfo request to Geserver. In this point, most of the develop have to face problem in cross domain. If hosting apache and geo server(tomcat) listening in sampe pc but different host, there have to take special care for cross domain ajax call. I wrote a post regarding this issue here. Please read this article first to proceed to the next steps.

infoControls = {
            click: new OpenLayers.Control.WMSGetFeatureInfo({
             url: 'http://localhost:8088/geoserver/wms',
             title: 'Identify features by clicking',
             layers: [bdhq,bdhw,dist],
             queryVisible: true,
	infoFormat:'application/vnd.ogc.gml'
           	})
};

Here we set request url to http://localhost:8088/geoserver/wms . but it cannot directly call the request. We have to go via proxy request. So, write a proxy script and bypass the request.
File: geoproxy.php

<?php
$url=$_GET["url"];
$res = file_get_contents($url);
echo $res;
?>

Now we define proxy in openlayer at top,
OpenLayers.ProxyHost = “geoproxy.php?url=”;

Below the full source code is available,


<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>MapServer Layer</title>
    <link rel="stylesheet" href="theme/default/style.css" type="text/css" />
    <style>
		.opmap
		{
			height:500px;
			width:550px;
		}
		/* The map and the location bar */
		#map {
			clear: both;
			position: relative;
			width: 400px;
			height: 450px;
			border: 1px solid black;
		}
		.mypopuphtml{
			 padding-left:5px;
			 padding-top:0px;
			 padding-bottom:0px;
			 padding-right:5px;
			 font-family:Arial;
			 font-size:8pt;
			 background-color:white;
		}

    </style>
    <script src="OpenLayers.js"></script>

    <script defer="defer" type="text/javascript">

        var zoom = 5;
        var map;
		var infoControls;
		var highlightlayer;
		var aktLayer=-1;
		// pink tile avoidance
		OpenLayers.IMAGE_RELOAD_ATTEMPTS = 5;
		// make OL compute scale according to WMS spec
		OpenLayers.DOTS_PER_INCH = 25.4 / 0.28;

		OpenLayers.ProxyHost = "geoproxy.php?url=";

        function init(){

			format = 'image/png';

			var bounds = new OpenLayers.Bounds(
				88.011, 20.59,
				92.683, 26.634
			);

			var options = {
				controls: [],
				maxExtent: bounds,
				maxResolution: 0.023609375,
				projection: "EPSG:4326",
				units: 'degrees'
			};

			map = new OpenLayers.Map('map', options);

			// setup tiled layer
			var dist = new OpenLayers.Layer.WMS(
				"dist_GEO - Tiled", "http://localhost:8088/geoserver/wms",
				{
					srs: 'EPSG:4326',
					width: '395',
					styles: '',
					height: '512',
					layers: 'bdadmin:dist_GEO',
					format: format,
					tiled: 'true',
					tilesOrigin : map.maxExtent.left + ',' + map.maxExtent.bottom,
					isBaseLayer: true,
					visibility: true
				},
				{
					buffer: 0,
					displayOutsideMaxExtent: true
				}
			);

			var bdhw = new OpenLayers.Layer.WMS(
				"Bangladesh Highway",
				"http://localhost:8088/geoserver/wms",
				{
					transparent: 'TRUE',
					srs: 'EPSG:4326',
					layers: 'bdadmin:rds_nr_geo',
					format: format,
					isBaseLayer: false,
					visibility: true
				}
			);

            var bdhq = new OpenLayers.Layer.WMS(
                "Bangladesh Dist HQ",
                "http://localhost:8088/geoserver/wms",
                {
                    transparent: 'TRUE',
                    srs: 'EPSG:4326',
					layers: 'bdadmin:dist_hq_geo',
					format: format,
					isBaseLayer: false,
					visibility: true
                }
            );

			highlightLayer = new OpenLayers.Layer.Vector("Highlighted Features", {
				displayInLayerSwitcher: false,
				isBaseLayer: false
				}
			);

		   //map.addLayers([gphy, gmap, ghyb, gsat]);
           map.addLayers([dist,bdhw,bdhq,highlightLayer]);

			// build up all controls
			map.addControl(new OpenLayers.Control.PanZoomBar({
				position: new OpenLayers.Pixel(2, 15)
			}));

			map.addControl(new OpenLayers.Control.Navigation());
			map.addControl(new OpenLayers.Control.Scale($('scale')));
			map.addControl(new OpenLayers.Control.MousePosition({element: $('location')}));
            map.addControl( new OpenLayers.Control.LayerSwitcher() );
			map.addControl(new OpenLayers.Control.LayerSwitcher({'div':OpenLayers.Util.getElement('layerswitcher')}));
			map.zoomToExtent(bounds);
			map.updateSize();

			infoControls = {
            click: new OpenLayers.Control.WMSGetFeatureInfo({
                url: 'http://localhost:8088/geoserver/wms',
                title: 'Identify features by clicking',
                layers: [bdhq,bdhw,dist],
                queryVisible: true,
				infoFormat:'application/vnd.ogc.gml',
				eventListeners: {
					getfeatureinfo: function(event) {
						map.addPopup(new OpenLayers.Popup.FramedCloud(
							"chicken",
							map.getLonLatFromPixel(event.xy),
							null,
							GenPopText(event),
							null,
							true
						));
                }}
           	}),
            hover: new OpenLayers.Control.WMSGetFeatureInfo({
                url: 'http://localhost:8088/geoserver/wms',
                title: 'Identify features by clicking',
                layers: [bdhq],
                hover: true,
                // defining a custom format options here
                formatOptions: {
                    typeName: 'water_bodies',
                    featureNS: 'http://www.openplans.org/topp'
                },
                queryVisible: true,
				infoFormat:'text/html'
            	})

			};

			for (var i in infoControls) {
				infoControls[i].events.register("getfeatureinfo", this, showInfo);
				map.addControl(infoControls[i]);
			}

			infoControls.click.activate();
			//infoControls.hover.activate();

			// Active layer combo
			populateLayer(0);
        }

		// sets the HTML provided into the nodelist element
		function setHTML(response){
			document.getElementById('nodelist').innerHTML = response.responseText;
		};

		function errorHTML(response)
		{
			alert("req erro:" + response.responseText);
		}

    	function showInfo(evt) {
			if (evt.features && evt.features.length) {

				 highlightLayer.destroyFeatures();
				 highlightLayer.addFeatures(evt.features);
				 highlightLayer.redraw();

				 $('nodelist').innerHTML = GenPopText(evt);

			} else {
				$('nodelist').innerHTML = evt.text;
			}
        }

		function GenPopText(evt){
				 var temstr="<b><i>" + evt.features[0].gml.featureType + "</i></b><br/>";
				 for(var key in evt.features[0].attributes)
				 {
					temstr += "<b>" + key + "</b>:" + evt.features[0].attributes[key] + "<br/>";
				 }
				 return temstr
		}

    </script>
  </head>

  <body onLoad="init()">
    <div id="title">Geosever Layer</div>
    <div id="layerswitcher" class="olControlLayerSwitcher"></div>
    <div id="map"></div>

    <div id="wrapper">
        <div id="location" style="float:left">location</div>
        <div id="scale" style="float:left"></div>
        <div id="scale" style="clear:both"></div>
    </div>
    <div id="nodelist">
        <em>Click on the map to get feature info</em>
    </div>

  </body>
</html>

………………………………………………
Abul Khayer
GIS Programmer
CEGIS



Open Layer with MapSever

Openlayer can add mapserver wms layer. In this writing, I discuss how work with mapserver wms layer using openlayer. I also wrote an article on how to create mapserver wms repository in my another writing here.

So let assume that we have a well configure mapserver repository and now want to view this maps using openlayer.

Add MapSever WMS Layer:

var dist = new OpenLayers.Layer.WMS( "Bangladesh Admin Bourndary",
	 "http://localhost:8080/cgi-bin/mapserv.exe?map=C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map",
	{layers: 'dist_geo',
		srs: 'EPSG:4326',
		format: format,
		isBaseLayer: true,
		visibility: true
	}

);
 

This will include Bangladesh district boundary as base wms layer. Note that we set the srs value wgs84 projection of our data.

Now we add the road layer from wms as openlayer overlay layer.

 // add road layer as overlay layer
	var bdhw = new OpenLayers.Layer.WMS(
                "Bangladesh Highway",
                "http://localhost:8080/cgi-bin/mapserv.exe",
                {
                    map: 'C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map',
                    transparent: 'TRUE',
                    layers: 'road_geo',
	    srs: 'EPSG:4326',
	   format: format
                },
                {'reproject': true}
            );

Now we implement identity features. When user click on map, we make WMSGetFeatureInfo request to mapserver.


infoControls = {
	click: new OpenLayers.Control.WMSGetFeatureInfo({
	url: 'http://localhost:8080/cgi-bin/mapserv.exe?map=C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map',
	title: 'Identify features by clicking',
	layers: [dist],
	infoFormat:'text/html',
	queryVisible: true
	})
};

Below the full source code is available,

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>MapServer Layer</title>
    <link rel="stylesheet" href="theme/default/style.css" type="text/css" />
    <style>
		.opmap
		{
			height:450px;
			width:550px;
		}
    </style>
    <script src="OpenLayers.js"></script>
    <script src='http://maps.google.com/maps?file=api&amp;v=2&amp;key=ABQIAAAAjpkAC9ePGem0lIq5XcMiuhR_wWLPFku8Ix9i2SXYRVK3e45q1BQUd_beF8dtzKET_EteAjPdGDwqpQ'></script>
    <script type="text/javascript">

		/*######################
		Author: Md Abul Khayer
		Year: 2010
		khayer.wordpress.com
		#######################*/

        var map, layer;
		var infoControls;

        function init(){

			format = 'image/png';

			var bounds = new OpenLayers.Bounds(
				88.011, 20.59,
				92.683, 26.634
			);

			var options = {
				controls: [],
				maxExtent: bounds,
				maxResolution: 0.023609375,
				projection: "EPSG:4326",
			};

			map = new OpenLayers.Map('map', options);

			// Add Google Layer as baselayer
			var gphy = new OpenLayers.Layer.Google(
			"Google Physical",
			{type: G_PHYSICAL_MAP}
            );

			var gmap = new OpenLayers.Layer.Google(
                "Google Streets", // the default
                {numZoomLevels: 20}
            );

			var ghyb = new OpenLayers.Layer.Google(
                "Google Hybrid",
                {type: G_HYBRID_MAP, numZoomLevels: 20}
            );

			var gsat = new OpenLayers.Layer.Google(
                "Google Satellite",
                {type: G_SATELLITE_MAP, numZoomLevels: 22}
            );

            map.addLayers([gphy, gmap, ghyb, gsat]);

			// Add Custom base layer. Bangladesh District boundary.
			var dist = new OpenLayers.Layer.WMS( "Bangladesh Admin Bourndary",
                    "http://localhost:8080/cgi-bin/mapserv.exe?map=C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map",
					{layers: 'dist_geo',
						srs: 'EPSG:4326',
						format: format,
						isBaseLayer: true,
						visibility: true
					}

			);

            // add road layer as overlay layer
			var bdhw = new OpenLayers.Layer.WMS(
                "Bangladesh Highway",
                "http://localhost:8080/cgi-bin/mapserv.exe",
                {
                    map: 'C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map',
                    transparent: 'TRUE',
                    layers: 'road_geo',
					srs: 'EPSG:4326',
					format: format
                },
                {'reproject': true}
            );

            // Bangladesh distric head queater as overlay layer.
			var bdhq = new OpenLayers.Layer.WMS(
                "Bangladesh Dist HQ",
                "http://localhost:8080/cgi-bin/mapserv.exe",
                {
                    map: 'C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map',
                    transparent: 'TRUE',
                    layers: 'dist_hq_geo',
					srs: 'EPSG:4326',
					format: format
                },
                {'reproject': true}
            );

			// Get identities of the map. Can be either click or hover is activated.
			infoControls = {
				click: new OpenLayers.Control.WMSGetFeatureInfo({
					url: 'http://localhost:8080/cgi-bin/mapserv.exe?map=C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map',
					title: 'Identify features by clicking',
					layers: [dist],
					infoFormat:'text/html',
					queryVisible: true
				}),
            	hover: new OpenLayers.Control.WMSGetFeatureInfo({
                url: 'http://localhost:8080/cgi-bin/mapserv.exe?map=C:/OSGeo4W/apache/htdocs/openlayer/sampleapps/distwms.map',
                title: 'Identify features by clicking',
                layers: [dist],
                hover: true,
				infoFormat: 'text/html',
                queryVisible: true
            	})
			}

            map.addLayers([dist, bdhw, bdhq]);
			map.zoomToMaxExtent();
            map.addControl( new OpenLayers.Control.LayerSwitcher() );
			map.addControl(new OpenLayers.Control.Navigation());
			map.addControl(new OpenLayers.Control.LayerSwitcher({'div':OpenLayers.Util.getElement('layerswitcher')}));

			map.addControl(new OpenLayers.Control.Scale($('scale')));
			map.addControl(new OpenLayers.Control.MousePosition({element: $('location')}));

			for (var i in infoControls) {
				infoControls[i].events.register("getfeatureinfo", this, showInfo);
				map.addControl(infoControls[i]);
			}

        	infoControls.click.activate();

        }

		function showInfo(evt) {
			if (evt.features && evt.features.length) {
				 //highlightLayer.destroyFeatures();
				 //highlightLayer.addFeatures(evt.features);
				 //highlightLayer.redraw();

				 $('nodelist').innerHTML=evt.text;
			} else {
				$('nodelist').innerHTML=evt.text;
			}
    	}

    </script>
  </head>

  <body onLoad="init()">
    <div id="title">MapServer WMS Layer with Google map.</div>
    <div id="layerswitcher" class="olControlLayerSwitcher"></div>
    <div id="map" class="opmap"></div>

    <div id="wrapper">
        <div id="location" style="float:left">location</div>
        <div id="scale" style="float:left"></div>
        <div id="scale" style="clear:both"></div>
    </div>
    <div id="nodelist">
        <em>Click on the map to get feature info</em>
    </div>

  </body>
</html>

………………………………………………
Abul Khayer
GIS Programmer
CEGIS




Follow

Get every new post delivered to your Inbox.